C # development and learning-SqlHelper applications,

C # development and learning-SqlHelper applications,

 

Use the App. config configuration file to encapsulateConnection stringTo facilitate reuse
---> Add the App. conifg configuration file
---> Add: ConnectionString:
---> Add reference

<?xml version="1.0" encoding="utf-8" ?><configuration>    <startup>         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />    </startup>  <connectionStrings>    <add name="conStr" connectionString="Data Source=.;Initial Catalog=;User ID=;Password="/>  </connectionStrings></configuration>

Encapsulate a SQLHelper class for ease of use

1 using System. configuration; 2 using System. data; // DatSet .. table SqlDataAdapter 3 using System. data. sqlClient; // SqlConnection Command DataReader 4 namespace Common 5 {6 public class SqlHelper 7 {8 // connection string 9 // 1, add reference 2, import namespace in order to use ConfigurationManager 10 private static string conStr = ConfigurationManager. connectionStrings ["conStr"]. connectionString; 11 12 // add, delete, modify, and query 13 // query data ExecuteScalar () returns the first ExecuteReader () in the first row () dataTable 14 15 16 /// <summary> 17 // return DataTable 18 /// </summary> 19 /// <param name = "SQL"> the SQL statement used </param> 20 // <param name = "param"> variable, parameters can be passed or not </param> 21 // <returns> </returns> 22 public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] param) 23 {24 DataTable dt = new DataTable (); 25 using (SqlConnection con = new SqlConnection (conStr) 26 {27 using (SqlDataAdapter adapter = new SqlDataAdapter (SQL, con )) 28 {29 // Add the parameter 30 adapter. selectCommand. parameters. addRange (param); 31 // 1. open the link. If the connection is not opened, it will open it for you; if it is opened, it will calculate 32 // 2. execute the SQL statement and read the database 33 // 3. sqlDataReader: Fill in the read data to 34 adapters in the memory table. fill (dt); 35} 36} 37 return dt; 38} 39 40 // <summary> 41 // execute the query, the first column of the First row is 42 /// </summary> 43 /// <param name = "SQL"> </param> 44 /// <param name = "param"> </param> 45 // <returns> </returns> 46 public static object ExecuteScalar (string SQL, params SqlParameter [] param) 47 {48 object o = null; 49 using (SqlConnection con = new SqlConnection (conStr) 50 {51 using (SqlCommand cmd = new SqlCommand (SQL, con) 52 {53 cmd. parameters. addRange (param); 54 con. open (); 55 56 o = cmd. executeScalar (); 57} 58} 59 return o; 60} 61 62 63 // <summary> 64 // execute the query, returns the SqlDataReader object 65 /// </summary> 66 /// <param name = "SQL"> </param> 67 /// <param name = "param"> </param> 68 // <returns> </returns> 69 public static SqlDataReader ExecuteReader (string SQL, params SqlParameter [] param) 70 {71 SqlDataReader reader = null; 72 using (SqlConnection con = new SqlConnection (conStr) 73 {74 using (SqlCommand cmd = new SqlCommand (SQL, con) 75 {76 cmd. parameters. addRange (param); 77 con. open (); 78 79 reader = cmd. executeReader (); 80} 81} 82 return reader; 83} 84 85 // <summary> 86 // execute add, delete, modify, return the number of affected rows: 87 /// </summary> 88 /// <param name = "SQL"> </param> 89 /// <param name = "param"> </param> 90 // <returns> </returns> 91 public static int ExecuteNonQuery (string SQL, params SqlParameter [] param) 92 {93 int n =-1; 94 using (SqlConnection con = new SqlConnection (conStr) 95 {96 using (SqlCommand cmd = new SqlCommand (SQL, con) 97 {98 cmd. parameters. addRange (param); 99 con. open (); 100 n = cmd. executeNonQuery (); 101} 102} 103 return n; 104} 105 106} 107}

C # using three ways to use: http://www.cnblogs.com/fashui/archive/2011/09/29/2195061.html C # namespace: to prevent duplicate names. For example, you can have the Student class in two different namespaces. This namespace range allows you to organize code and provides you with a method to create a globally unique type. C # Sqlparamater usage: http://blog.csdn.net/zzp_403184692/article/details/8092408 below this is the application SqlHelperAnd ComboBoxTo display Provincial/Municipal Linkage:

Public partial class Form1: Form {public Form1 () {InitializeComponent ();} private void Form1_Load (object sender, EventArgs e) {// when the application is loaded, go to the database to find the province data and send the cboPro DataTable dt = SqlHelper. executeDataTable ("select * from promary"); // use the returned DataTable as the data source of cboPro. // make cboPro display the value of the proName field, which is generally displayed to the customer.DisplayMember= "ProName"; // Let valueMemberID, bind the corresponding value, bindHandler IDFor programmers. CboPro.ValueMember= "ProID"; cboPro.DataSource= Dt;} private void cboPro_SelectedIndexChanged (object sender, EventArgs e) {// previous statement // MessageBox. show (cboPro. text); // obtain the selected Text in cbo // MessageBox. show (cboPro. selectedValue. toString (); // obtain the associated data // string SQL = "select * from city where proID =" + cboPro. selectedValue. toString (); // The SQL statement with parameters string SQL = "select * from city where proID = @ proID "; // prepare an SQL parameter SqlParameter p = new SqlParameter ("@ proID", cboPro. selectedValue. toString (); // set the data cboCity to be displayed in cboCity. displayMember = "cityName"; // The cboCity dataset queried Based on the SQL statement. dataSource = SqlHelper. executeDataTable (SQL, p );}}