初学SqlHelper - 实现增删改查

 1 //数据库连接字符串
 2       public static readonly string constr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
 3 
 4       //打开数据库
 5       public static SqlConnection OpenConnection()
 6       {
 7         SqlConnection conn = new SqlConnection(constr);
 8         conn.Open();
 9         return conn;
10       }
11 
12       //执行不返回结果的sql，用于插入和更新，删除
13       public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters)
14       {
15         using (SqlConnection conn = new SqlConnection(constr))
16         {
17           conn.Open();
18           return ExecuteNonQuery(conn, cmdText, parameters);
19         }
20       }
21 
22       //上面方法的重载方法
23       public static int ExecuteNonQuery(SqlConnection conn, string cmdText, params SqlParameter[] parameters)
24       {
25         using (SqlCommand cmd = conn.CreateCommand())
26         {
27           cmd.CommandText = cmdText;
28           cmd.Parameters.AddRange(parameters);
29           return cmd.ExecuteNonQuery();
30         }
31       }
32 
33       //返回对象类型的sql查询，对象单个数据记录的中的某个值，或者count(*)计算的结果，在分页中会经常用到。
34       public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
35       {
36         using (SqlConnection conn = new SqlConnection(constr))
37         {
38           conn.Open();
39           return ExecuteScalar(conn, cmdText, parameters);
40         }
41       }
42 
43       //以上方法的重载方法
44       public static object ExecuteScalar(SqlConnection conn, string cmdText, params SqlParameter[] parameters)
45       {
46         using (SqlCommand cmd = conn.CreateCommand())
47         {
48           cmd.CommandText = cmdText;
49           cmd.Parameters.AddRange(parameters);
50           return cmd.ExecuteScalar();
51         }
52       }
53 
54       //获得数据表格的查询
55       public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
56       {
57         using (SqlConnection conn = new SqlConnection(constr))
58         {
59           conn.Open();
60           return ExecuteDataTable(conn, cmdText, parameters);
61         }
62       }
63 
64       //以上方法的重载
65       public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText, params SqlParameter[] parameters)
66       {
67         using (SqlCommand cmd = conn.CreateCommand())
68         {
69           cmd.CommandText = cmdText;
70           cmd.Parameters.AddRange(parameters);
71           using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
72           {
73             DataTable dt = new DataTable();
74             adapter.Fill(dt);
75             return dt;
76           }
77         }
78       }

  1 /// <summary>
  2         /// 查询表中总记录
  3         /// </summary>
  4         /// <param name="data"></param>
  5         /// <returns></returns>
  6         public async Task<ActionResult> ScalarData(ContactModel data)
  7         {
  8             string sql = "select count(*) from Contact";
  9             var DataSource = SqlHelper.ExecuteScalar(sql);
 10             return ReturnJson(new ResponseModel("0000", "成功", "共有记录"+DataSource.ToString()+"条"));
 11         }
 12         /// <summary>
 13         /// 分页查询表中数据
 14         /// </summary>
 15         /// <param name="data"></param>
 16         /// <returns></returns>
 17         public async Task<ActionResult> SelectPageData(ContactModel data)
 18         {
 19             int pageindex = data.pageindex;
 20             int pagesize = data.pagesize;
 21             string sql = "select top " + pagesize + " * from Contact where id not in (select top (" + pagesize * (pageindex - 1) + ") id from Contact)";
 22             var DataSource = SqlHelper.ExecuteDataTable(sql);
 23             string JsonString = string.Empty;
 24             JsonString = JsonConvert.SerializeObject(DataSource);
 25             return ReturnJson(new ResponseModel("0000", "成功", JsonString));
 26         }
 27         /// <summary>
 28         /// 查询表中所有数据
 29         /// </summary>
 30         /// <param name="data"></param>
 31         /// <returns></returns>
 32         public async Task<ActionResult> SelectData(ContactModel data)
 33         {
 34             string sql = "select * from Contact";
 35             //string sql = "select * from Contact where Name=ISNULL('" + data.name + "',Name)";
 36             var DataSource = SqlHelper.ExecuteDataTable(sql);
 37 
 38             //string sql = "select * from Contact where Name=ISNULL(@Name,Name)";
 39             //var DataSource = SqlHelper.ExecuteDataTable(sql, new SqlParameter("@Name", data.name));
 40             string JsonString = string.Empty;
 41             JsonString = JsonConvert.SerializeObject(DataSource);
 42             return ReturnJson(new ResponseModel("0000","成功", JsonString));
 43         }
 44         /// <summary>
 45         /// 在表中插入一条新的数据
 46         /// </summary>
 47         /// <param name="data"></param>
 48         /// <returns></returns>
 49         public async Task<ActionResult> InsertData(ContactModel data)
 50         {
 51             //string sql = "INSERT INTO Contact VALUES ('"+ data.name+"','"+data.enrollmentDate+"')";
 52             string sql = "INSERT INTO Contact VALUES (@name, @enrollmentDate)";
 53             SqlParameter[] param = new SqlParameter[] {
 54                 new SqlParameter("@name",data.name),
 55                 new SqlParameter("@enrollmentDate",data.enrollmentDate),
 56             };
 57             var DataSource = SqlHelper.ExecuteNonQuery(sql, param);
 58             if (DataSource == 1)
 59             {
 60                 return ReturnJson(new ResponseModel("0000", "成功", ""));
 61             }
 62             return ReturnJson(new ResponseModel("9999", "处理失败", ""));
 63         }
 64         /// <summary>
 65         /// 根据id修改表中的数据
 66         /// </summary>
 67         /// <param name="data"></param>
 68         /// <returns></returns>
 69         public async Task<ActionResult> UpdateData(ContactModel data)
 70         {
 71             if (data.id == null)
 72             {
 73                 return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
 74             }
 75             //string sql = "UPDATE Contact SET Name = '"+ data.name + "',EnrollmentDate = '" + data.enrollmentDate + "' WHERE ID = " + data.id;
 76             string sql = "UPDATE Contact SET Name = @name , EnrollmentDate = @enrollmentDate  WHERE ID = @id";
 77             SqlParameter[] param = new SqlParameter[] {
 78                 new SqlParameter("@id",data.id),
 79                 new SqlParameter("@name",data.name),
 80                 new SqlParameter("@enrollmentDate",data.enrollmentDate),
 81             };
 82             var DataSource = SqlHelper.ExecuteNonQuery(sql, param);
 83             if (DataSource == 1)
 84             {
 85                 return ReturnJson(new ResponseModel("0000", "成功", ""));
 86             }
 87             return ReturnJson(new ResponseModel("9999", "处理失败", ""));
 88         }
 89         /// <summary>
 90         /// 根据id删除表中的数据
 91         /// </summary>
 92         /// <param name="data"></param>
 93         /// <returns></returns>
 94         public async Task<ActionResult> DeleteData(ContactModel data)
 95         {
 96             string sql = "DELETE FROM Contact WHERE ID = @id";
 97             SqlParameter[] param = new SqlParameter[] {
 98                 new SqlParameter("@id",data.id)
 99             };
100             var DataSource = SqlHelper.ExecuteNonQuery(sql, param);
101             if (DataSource == 1)
102             {
103                 return ReturnJson(new ResponseModel("0000", "成功", ""));
104             }
105             return ReturnJson(new ResponseModel("9999", "处理失败", ""));
106         }
107 
108         public JsonResult ReturnJson(ResponseModel response)
109         {
110             return this.Json(response, JsonRequestBehavior.AllowGet);
111         }

1  public class ContactModel
2     {
3         public int id { get; set; }
4         public string name { get; set; }
5         public string enrollmentDate { get; set; }
6         public int pageindex { get; set; }
7         public int pagesize { get; set; }
8     }