深入理解ADO.NET友情链接：http://www.cnblogs.com/liuhaorain/category/352388.html
小白手把手：VS2017 SQL Server 2014
第一步：新建项目，选择Visual C#--ASP.NET Web Application(.NET Framework)，键入工程名。确认后选择MVC模式，确认。
此时可看到工程目录包含Controllers,Modles,Views三层。运行程序即可看到VS自带的MVC模板。
第二步，新建工程完成后，进行数据库的连接，若还没有在数据库建好表，则先行建表，建好后进行以下操作。
点击工具栏的Tools--Connect to Database，填写你所连接的数据库的Server name，选择身份验证，输入用户名和密码，然后选择所要连接的数据库名称。
1.右键Models选择新建项目，选择Data--ADO.NET Entity Data Model，然后确认；
2.选择Code First from database，点击Next
3.在连接处选择起初我们建立好的连接，或者略过起初建立连接，在此处选择新建连接。
或
选择Yes,include the sensitive data in the connection string.点击Next，选择将要使用的数据库对象，包括“表”、“视图”、“存储过程和函数”。点击Finish.
完成后会看到Models文件夹添加了实体数据模型，Web.config文件添加了连接数据库配置。
<connectionStrings><add name="Joke" connectionString="data source=PA181;user id=sa;password=**;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /></connectionStrings>
第三步，新建Controller，右键Controllers文件add--Controller，选择MVC5 Controller with read/write actions点击add，命名是后面Controller不要改动。
生成的controller中包含action：
第四步，在controllers文件中新建DBConnect类，用来进行数据库的连接读写操作。
using ADO.NET.test.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web; namespace ADO.NET.test.Controllers
{
public class DB
{
protected SqlConnection conn;
//打开连接
public bool OpenConnection()
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Joke"].ConnectionString);
try
{
bool result = true;
if (conn.State.ToString() != "Open")
{
conn.Open();
}
return result;
}
catch (SqlException ex)
{
return false;
}
}
//关闭连接
public bool CloseConnection()
{
try
{
conn.Close();
return true;
}
catch (SqlException ex)
{
return false;
}
}
//显示
public List<Joke> Select()
{
SqlDataReader sdr;
List<Joke> list = new List<Joke>();
try
{
if (conn.State.ToString() == "Open")
{
SqlCommand cmd = new SqlCommand("Select * from Jokes", conn);
sdr = cmd.ExecuteReader();
while (sdr.Read())
{
Joke j = new Joke();
System.Diagnostics.Debug.WriteLine("ID:{0}\tcontent:{1}\tbelong:{2}\tstate:{3}\ttime:{4}", sdr["ID"], sdr["Content"], sdr["Belong"], sdr["State"], sdr["AddDate"]);
j.ID = Convert.ToInt32(sdr["ID"]);
j.Content = sdr["Content"].ToString();
j.Belong = sdr["Belong"].ToString();
j.State = Convert.ToInt32(sdr["State"]);
j.AddDate = Convert.ToDateTime(sdr["AddDate"]); list.Add(j);
}
}
}
catch (Exception e)
{
Console.WriteLine("Joke details wrong:{0}", e);
}
finally
{
conn.Close();
}
return list;
}
//Detail
public Joke Detail(int? id)
{
SqlDataReader sdr;
Joke j = new Joke();
System.Diagnostics.Debug.WriteLine("编号：{0}", id);
string sql = "Select * from Jokes where ID = @ID";
SqlParameter[] paras = new SqlParameter[]{//参数数组
new SqlParameter("@ID",System.Data.SqlDbType.Int)};
paras[].Value = id;//绑定ID
try
{
if (conn.State.ToString() == "Open")
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
sdr = cmd.ExecuteReader();
while (sdr.Read())
{ System.Diagnostics.Debug.WriteLine("ID:{0}\tcontent:{1}\tbelong:{2}\tstate:{3}\ttime:{4}", sdr["ID"], sdr["Content"], sdr["Belong"], sdr["State"], sdr["AddDate"]);
j.ID = Convert.ToInt32(sdr["ID"]);
j.Content = sdr["Content"].ToString();
j.Belong = sdr["Belong"].ToString();
j.State = Convert.ToInt32(sdr["State"]);
j.AddDate = Convert.ToDateTime(sdr["AddDate"]);
}
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Joke select wrong:{0}", e);
}
finally
{
conn.Close();
}
return j; }
//Create
public void Create(Joke joke)
{
//SqlDataReader sdr;
//string sql = "insert into Jokes(Content,Belong,State,AddDate)values('" + joke.Content + "','" + joke.Belong + "','" + joke.State + "','" + joke.AddDate + "')";
string sql = "insert into Jokes(Content,Belong,State,AddDate)values(@content,@belong,@state,@time)";
SqlParameter[] paras = new SqlParameter[]{//参数数组
new SqlParameter("@content",System.Data.SqlDbType.VarChar),
new SqlParameter("@belong",System.Data.SqlDbType.VarChar),
new SqlParameter("@state",System.Data.SqlDbType.Int),
new SqlParameter("@time",System.Data.SqlDbType.DateTime)};
paras[].Value = joke.Content;//绑定内容
paras[].Value = joke.Belong;//绑定署名
paras[].Value = joke.State;//绑定状态
paras[].Value = joke.AddDate;//绑定时间
try
{
if (conn.State.ToString() == "Open")
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
cmd.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine("插入成功！");
}
}
catch (Exception e)
{
Console.WriteLine("Joke create wrong:{0}", e);
}
finally
{
conn.Close();
}
}
//Delete
public int Delete(int? id)
{
string sql = "delete from Jokes where ID= @ID";
SqlParameter[] paras = new SqlParameter[]{//参数数组
new SqlParameter("@ID",System.Data.SqlDbType.Int)};
paras[].Value = id;//绑定ID
int i = ;
try
{
if (conn.State.ToString() == "Open")
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
i = cmd.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine("插入成功！");
}
}
catch (Exception e)
{
Console.WriteLine("Joke create wrong:{0}", e);
}
finally
{
conn.Close();
}
return i;
}
//update
public int Update(Joke joke)
{
string sql = "update Jokes set Content = @content,Belong = @belong where ID= @ID";
SqlParameter[] paras = new SqlParameter[]{//参数数组
new SqlParameter("@content",System.Data.SqlDbType.VarChar),
new SqlParameter("@belong",System.Data.SqlDbType.VarChar),
new SqlParameter("@ID",System.Data.SqlDbType.Int)};
paras[].Value = joke.Content;//绑定内容
paras[].Value = joke.Belong;//绑定署名
paras[].Value = joke.ID;//绑定ID
System.Diagnostics.Debug.WriteLine("SQL语句：{0}", sql);
int i = ;
try
{
if (conn.State.ToString() == "Open")
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
i = cmd.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine("更新成功！");
}
}
catch (Exception e)
{
Console.WriteLine("Joke update wrong:{0}", e);
}
finally
{
conn.Close();
}
return i;
}
}
}
第五步：在Views--Joke文件夹下建立对应的Controller返回显示页面。
第六步，App_Start--RouteConfig.cs修改路由，使初始页面指向Joke--Index.cshtml.
最后运行结果：
