ASP.NET MVC使用ADO.NET连接数据库

深入理解ADO.NET友情链接:

小白手把手:VS2017  SQL Server 2014

第一步:新建项目,选择Visual C#--ASP.NET Web Application(.NET Framework),键入工程名。确认后选择MVC模式,确认。

ASP.NET MVC使用ADO.NET连接数据库

此时可看到工程目录包含Controllers,Modles,Views三层。运行程序即可看到VS自带的MVC模板。

ASP.NET MVC使用ADO.NET连接数据库

第二步,新建工程完成后,进行数据库的连接,若还没有在数据库建好表,则先行建表,建好后进行以下操作。

点击工具栏的Tools--Connect to Database,填写你所连接的数据库的Server name,选择身份验证,输入用户名和密码,然后选择所要连接的数据库名称。

ASP.NET MVC使用ADO.NET连接数据库

1.右键Models选择新建项目,选择Data--ADO.NET Entity Data Model,然后确认;

ASP.NET MVC使用ADO.NET连接数据库

2.选择Code First from database,点击Next

ASP.NET MVC使用ADO.NET连接数据库

3.在连接处选择起初我们建立好的连接,或者略过起初建立连接,在此处选择新建连接。

ASP.NET MVC使用ADO.NET连接数据库

ASP.NET MVC使用ADO.NET连接数据库

选择Yes,include the sensitive data in the connection string.点击Next,选择将要使用的数据库对象,包括“表”、“视图”、“存储过程和函数”。点击Finish.

完成后会看到Models文件夹添加了实体数据模型,Web.config文件添加了连接数据库配置。

ASP.NET MVC使用ADO.NET连接数据库

<connectionStrings><add 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:

ASP.NET MVC使用ADO.NET连接数据库

第四步,在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; } } }

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zwyspf.html