ASP.NET MVC使用ADO.NET连接数据库
深入理解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[0].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[0].Value = joke.Content;//绑定内容 paras[1].Value = joke.Belong;//绑定署名 paras[2].Value = joke.State;//绑定状态 paras[3].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[0].Value = id;//绑定ID int i = 0; 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[0].Value = joke.Content;//绑定内容 paras[1].Value = joke.Belong;//绑定署名 paras[2].Value = joke.ID;//绑定ID System.Diagnostics.Debug.WriteLine("SQL语句：{0}", sql); int i = 0; 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.
最后运行结果：
