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}	content:{1}	belong:{2}	state:{3}	time:{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}	content:{1}	belong:{2}	state:{3}	time:{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.

最后运行结果：