ADO. NET review-write your own SqlHelper class, ado. netsqlhelper

ADO. NET review-write your own SqlHelper class, ado. netsqlhelper

Today, I reviewed the basics of ADO. NET and sorted out the points I think:

Compile the SqlHelper class to facilitate the execution of database statements. In this case, you can directly call the method encapsulated in the SqlHelper class. At present, most companies require you to write a SqlHelper class for your interview questions. This test is based on your own basic skills. If a developer has a weak foundation, the code you write is definitely not good enough.

 

The following is the Demo code of SqlHelper, which must be skillful:

1 public static SqlHelper 2 {3 // define a string variable here, assign the database connection string to it, or add the connection string to the configuration file, so that the entire project can be easily accessed, here, to clearly assign values to the string variable 4 static string connStr = "Data Source = 127.0.0.1 \ SQL2012; Initial Catalog = Test; User ID = test; Password = test "; 5 6 7 // execute the query statement and return the 8 public DataTable ExecuteDataTable (string SQL, params SqlParameter [] parameters) of a data table in the memory. 9 // The SQL statement parameters are unknown, so the variable length parameter is used, but it must be placed in the following 10 {11 12 using (SqlConnection conn = new SqlConnection (connStr) // connection to the database 13 {14 conn. open (); // Open the database connection 15 using (SqlCommand cmd = conn. createCommand () // create the execution object 16 {17 cmd. commandText = SQL; // assign an SQL statement 18 to cmd. parameters. addRange (parameters); // Add SQL statement parameters to cmd19 DataSet dataset = new DataSet (); // create a DataSet object, used to save the query result 20 SqlDataAdapter adapter = new SqlDataAdapter (cmd); // run cmd to update the data result to the adapter object 21 adapter. fill (dataset); // The Fill method of the adapter object adds the result to the DataSet object 22 return dataset. tables [0]; // return a query result for a table 23} 24} 25} 26 27 // execute the query statement to return the first column 28 public Object ExecuteScalar (string SQL, params SqlParameter [] parameters) 29 {30 using (SqlConnection conn = new SqlConnection (connStr) // connection to the database 31 {32 conn. open (); // Open the database connection 33 using (SqlCommand cmd = conn. createCommand () // create the execution object 34 {35 cmd. commandText = SQL; // assign an SQL Statement 36 to cmd. parameters. addRange (parameters); // Add SQL statement parameters to limit 37 return cmd. executeScalar (); // execute the query and return the first column 38} 39} 40} 41 42 of the first row of the query result. // execute the parameterized SQL statement, returns 43 public int ExecuteNonQuery (string SQL, params SqlParameter [] parameters) 44 {45 46 using (SqlConnection conn = new SqlConnection (connStr )) // connection to database 47 {48 conn. open (); // Open the database connection 49 using (SqlCommand cmd = conn. createCommand () // create the execution object 50 {51 cmd. commandText = SQL; // assign the SQL statement 52 to cmd. parameters. addRange (parameters); // Add the 53 return cmd parameter in the SQL statement. executeNonQuery (); // execute the database statement and return the affected rows 54} 55} 56} 57}

 

 

 

2. After the above SqlHelper class is created, we can call it in the project. The following shows the call code:

Call the SqlHerlper Class E method to insert data:

1 class Program 2 {3 static void Main (string [] args) 4 {5 // input data 6 Console. writeLine ("enter the name of the database to be saved:"); 7 string name = Console. readLine (); 8 Console. writeLine ("Enter age:"); 9 int age = Console. readLine (); 10 11 // call the ExecuteNonQuery (string SQL, params SqlParameter [] parameters) function to insert the obtained data to the database 12 int rows = sqlhelper. executeNonQuery ("insert into T_Test (Name, Age) vaules (@ Name, @ Age)", new SqlParameter ("@ Name", name), new SqlParameter ("@ Age ", age); 13 14 Console. writeLine ("successfully inserted {0} pieces of data", rows); // display the execution result 15 Console. readKey (); 16} 17 18}

 

Delete data

1 1 class Program 2 2 {3 3 static void Main (string [] args) 4 4 {5 5 string name = "Zhang San"; 6 6 7 7 int result = sqlhelper. executeNonQuery ("delete from T_Test where Name = @ Name", new SqlParameter ("@ Name", name); // delete the data 8 Console named zhangsan. writeLine ("{0} data deleted successfully. ", result); 9 9 10 10 Console. readKey (); 11 11} 12 13}

Query data

1. Create a winform with a control dataGridView

2. Add a method to call the SqlHelper class in the loading event of the form, and assign the result to the dataGridView. The Code is as follows:

1 private void Form1_Load (object sender, EventArgs e) 2 {3 // Add a method to call the SqlHelper class in the form loading event, and assign the result to dataGridView4 dataGridView1.DataSource = sqlhelper. executeDataTable ("select Name from T_Test"); 5}

 

3. Run the form and the result is the data queried from the database.

 

 

This is the key point I have reviewed today. Although it is a good dish, this is the first time I have written a blog with code. I have finally taken this step and will write more blogs in the future, share your learning experience.

Don't try it out ......