SqlHelper and sqlhelper

SqlHelper and sqlhelper

1 // database connection string 2 public static readonly string constr = ConfigurationManager. connectionStrings ["connstr"]. connectionString; 3 4 // Open Database 5 public static SqlConnection OpenConnection () 6 {7 SqlConnection conn = new SqlConnection (constr); 8 conn. open (); 9 return conn; 10} 11 12 // execute an SQL statement that does not return results for insertion and update. delete 13 public static int ExecuteNonQuery (string plain text, params SqlParameter [] parameters) 14 {15 using (SQL Connection conn = new SqlConnection (constr) 16 {17 conn. open (); 18 return ExecuteNonQuery (conn, plain text, parameters); 19} 20} 21 22 // overload method 23 public static int ExecuteNonQuery (SqlConnection conn, string plain text, params SqlParameter [] parameters) 24 {25 using (SqlCommand cmd = conn. createCommand () 26 {27 cmd. commandText = plain text; 28 cmd. parameters. addRange (parameters); 29 return cmd. executeNonQuery (); 30} 31} 32 33 // return an SQL query of the object type, a value in a single data record of the object, or the result calculated by count, it is often used in paging. 34 public static object ExecuteScalar (string plain text, params SqlParameter [] parameters) 35 {36 using (SqlConnection conn = new SqlConnection (constr) 37 {38 conn. open (); 39 return ExecuteScalar (conn, plain text, parameters); 40} 41} 42 43 // method of overloading the above 44 public static object ExecuteScalar (SqlConnection conn, string plain text, params SqlParameter [] parameters) 45 {46 using (SqlCommand cmd = conn. createCommand () 47 {48 cmd. commandText = plain text; 49 cmd. parameters. addRange (parameters); 50 return cmd. executeScalar (); 51} 52} 53 54 // query the data table 55 public static DataTable ExecuteDataTable (string parameter text, params SqlParameter [] parameters) 56 {57 using (SqlConnection conn = new SqlConnection (constr) 58 {59 conn. open (); 60 return ExecuteDataTable (conn, plain text, parameters); 61} 62} 63 64 // overload 65 public static DataTable ExecuteDataTable (SqlConnection conn, string plain text, params SqlParameter [] parameters) 66 {67 using (SqlCommand cmd = conn. createCommand () 68 {69 cmd. commandText = plain text; 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 // query the total records in the Table 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", "successful", "total records" + DataSource. toString () + "entries ")); 11} 12 // <summary> 13 // query the data in the table by page 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", "successful", JsonString )); 26} 27 /// <summary> 28 // query all data in the table 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", "successful", JsonString )); 43} 44 // <summary> 45 // Insert a new data record in the table. 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", "success ","")); 61} 62 return ReturnJson (new ResponseModel ("9999", "processing failed ","")); 63} 64 // <summary> 65 // modify the table data 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", "success ","")); 86} 87 return ReturnJson (new ResponseModel ("9999", "processing failed ","")); 88} 89 // <summary> 90 // Delete the table data 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", "successful ","")); 104} 105 return ReturnJson (new ResponseModel ("9999", "processing failed", ""); 106} 107 108 public JsonResult ReturnJson (ResponseModel response) 109 {110 return this. json (response, JsonRequestBehavior. allowGet); 111}

View Code

1  public class ContactModel2     {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