初学SqlHelper - 实现增删改查

//数据库连接字符串 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 } View Code - SqlHelper

  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         }

View Code

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     }

View Code

 

初学SqlHelper - 实现增删改查

标签：pst   public   cti   个数   values   常用   sql查询   serialize   data