DAO层数据操作的封装



创建一个数据库操作的类：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace GTBS
{
    public static class SqlHelper
    {
        public static readonly string connstr =
            System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;                         //获取来自web.config中的<connectionString>中的字符串

        public static SqlConnection OpenConnection()                                                                                               //打开数据连接
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            return conn;
        }

        public static int ExecuteNonQuery(string cmdText,                                                                           //插入和删除，修改可以使用此函数
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                return ExecuteNonQuery(conn, cmdText, parameters);
            }
        }

        public static object ExecuteScalar(string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                return ExecuteScalar(conn, cmdText, parameters);
            }
        }

        public static DataTable ExecuteDataTable(string cmdText,                                                          //查询多用这个函数
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                return ExecuteDataTable(conn, cmdText, parameters);
            }
        }

        public static int ExecuteNonQuery(SqlConnection conn, string cmdText,
           params SqlParameter[] parameters)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(SqlConnection conn, string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }

        public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.Parameters.AddRange(parameters);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        public static object ToDBValue(this object value)
        {
            return value == null ? DBNull.Value : value;
        }

        public static object FromDBValue(this object dbValue)
        {
            return dbValue == DBNull.Value ? null : dbValue;
        }
    } 
}

使用案例：

插入使用：

  [HttpPost]
        [ValidateInput(false)]
        public ActionResult Admin_add()
        {
            string grade = Request["grade"];
            string subjects=Request["subjects"];
            string difficu = Request["difficu"];
            string topic = Request["topic"];
            //判断添加试题的题型
            if (topic == "选择题")
            {
               
                try
                {
                    string title=Request["title"];
                    string a=Request["a"];
                    string b=Request["b"];
                    string c=Request["c"];
                    string d=Request["d"];
                    string correct=Request["correct"];
                    SqlHelper.ExecuteNonQuery("insert into T_Choice(title,a,b,c,d,level,grade,subjects,correct,time) values(@title,@a,@b,@c,@d,@difficu,@grade,@subjects,@correct,getdate())", new SqlParameter("@title", title), new SqlParameter("@a", a), new SqlParameter("@b", b), new SqlParameter("@c",c), new SqlParameter("@d", d), new SqlParameter("@difficu", difficu), new SqlParameter("@grade", grade), new SqlParameter("@subjects", subjects), new SqlParameter("@correct", correct));
                }
                catch
                {
                    return Content("error");
                }
                return Content("success");
 
            }
            else if (topic == "填空题")
            {
                try
                {
                    string editor = Request["editor"];                 //编辑的题目
                    string correct = Request["correct"];               //编辑的题目答案
                    SqlHelper.ExecuteNonQuery("insert into T_Completion(title,level,grade,subjects,correct,time) values(@editor,@difficu,@grade,@subjects,@correct,getdate())", new SqlParameter("@editor", editor), new SqlParameter("@difficu", difficu), new SqlParameter("@grade", grade), new SqlParameter("@subjects", subjects), new SqlParameter("@correct", correct));
                }
                catch
                {
                    return Content("error");                           //数据库写入异常
                }
                return Content("success");

 
            }
            else
            {
                try
                {
                    string editor = Request["editor"];                 //编辑的题目
                    string correct = Request["correct"];               //编辑的题目答案
                    SqlHelper.ExecuteNonQuery("insert into T_Questions(title,level,grade,subjects,correct,time) values(@editor,@difficu,@grade,@subjects,@correct,getdate())", new SqlParameter("@editor", editor), new SqlParameter("@difficu", difficu), new SqlParameter("@grade", grade), new SqlParameter("@subjects", subjects), new SqlParameter("@correct", correct));
                }
                catch
                {
                    return Content("error");
                }
                return Content("success");
 
            }

        }

删除使用：

  public ActionResult Admin_remove()
        {
            string topic=Request["topic"];
            string num=Request["num"];
            try
            {
                if (topic == "选择题")
                {
                    SqlHelper.ExecuteNonQuery("delete from T_Choice where num=@num", new SqlParameter("@num", num));
                    return Content("success");

                }
                else if (topic == "填空题")
                {
                    SqlHelper.ExecuteNonQuery("delete from T_Completion where num=@num", new SqlParameter("@num", num));
                    return Content("success");
                }
                else  //if topic =   'questions'
                {
                    SqlHelper.ExecuteNonQuery("delete from T_Questions where num=@num", new SqlParameter("@num", num));
                    return Content("success");
                }
            }
            catch
            {
                return Content("error");
            }

        }
       

查询：

 int num_c = Convert.ToInt32(Request["num_c"]);
            if (num_c != 0)
            {
                result += "<br/><label>填空题答案</label><br/>";
                DataTable table = SqlHelper.ExecuteDataTable("select * from T_Completion wheresubjects=@subjects and grade=@grade",new SqlParameter("@subjects",subjects),new SqlParameter("@grade",level_detailed));
                if (table.Rows.Count > 0)
                {
                    DataRow row;
                    string correct = string.Empty;
                    for (int i = 0; i < num_c; i++)
                    {
                        j = i + 1;
                        row = table.Rows[0];
                        correct = row["correct"].ToString();
                        result += j.ToString() + "、" + correct + "<br/>";
                    }
                }
            }