Ado. NET review--Write your own SqlHelper class

Today, I reviewed the basics of ADO, and sorted out what I thought was the focus:

Write the SqlHelper class so that we can execute the database statements, and then directly call the methods encapsulated in the SqlHelper class. Most companies now interview, give you the face of the question will be your own handwriting a SqlHelper class, this test is their own basic skills, if a foundation is not strong developers, you write code definitely not where to go.

Here is the demo code for SqlHelper, which must be proficient:

1  Public StaticSqlHelper2  {3          //This defines a string variable, assigns the database connection string to it, or adds the connection string to the configuration file, so that the entire project can be easily accessed, where it is straightforward to assign a value to a string variable4          Static stringConnStr ="Data source=127.0.0.1\\sql2012; Initial catalog=test; User id=test; Password=test";5  6  7          //executes a query statement and returns an in-memory data table8           PublicDataTable executedatatable (stringSqlparamssqlparameter[] Parameters)9              //because there are several arguments to the SQL statement, variable length parameters are used, but must be placed behindTen         { One  A             using(SqlConnection conn =NewSqlConnection (CONNSTR))//connection to the database -             { -Conn. Open ();//Open a database connection the                 using(SqlCommand cmd = conn.) CreateCommand ())//To create an execution object -                 { -Cmd.commandtext = SQL;//assigning a SQL statement to cmd -Cmd. Parameters.addrange (Parameters);//Add SQL statement parameters to cmd +DataSet DataSet =NewDataSet ();//new DataSet object for saving query results -SqlDataAdapter adapter =NewSqlDataAdapter (CMD);//execute cmd, update data results to adapter object +Adapter. Fill (DataSet);//the Fill method of the adapter object adds the result to the DataSet object A                     returnDataSet. tables[0];//returns a table for a query result at                 } -             } -         } -  -         //executes a query statement that returns the first column of the first row of the result set -          PublicObject ExecuteScalar (stringSqlparamssqlparameter[] Parameters) in         { -             using(SqlConnection conn =NewSqlConnection (CONNSTR))//connection to the database to             { +Conn. Open ();//Open a database connection -                 using(SqlCommand cmd = conn.) CreateCommand ())//To create an execution object the                 { *Cmd.commandtext=sql;//assigning a SQL statement to cmd $Cmd. Parameters.addrange (Parameters);//Add SQL statement parameters to cmdPanax Notoginseng                     returnCmd. ExecuteScalar ();//executes the query, returning the first column of the first row of the query results -                 } the             } +         } A  the         //executes a parameterized SQL statement that returns the number of rows affected +          Public intExecuteNonQuery (stringSqlparamssqlparameter[] Parameters) -         { $  $             using(SqlConnection conn =NewSqlConnection (CONNSTR))//connection to the database -             { -Conn. Open ();//Open a database connection the                 using(SqlCommand cmd= Conn. CreateCommand ())//To create an execution object -                 {WuyiCmd.commandtext= SQL;//assigning a SQL statement to cmd theCmd. Parameters.addrange (Parameters);//adding parameters in an SQL statement -                     returnCmd. ExecuteNonQuery ();//executes the database statement and returns the number of rows affected Wu                 } -             } About         } $}

Second, the above SqlHelper class is created so that we can invoke it within the project, the following shows the calling code:

Call the Sqlherlper class E method to insert the data:

1     class Program2     {3         Static voidMain (string[] args)4         {5             //input Data6Console.WriteLine ("Please enter the name you want to save to the database:");7             stringName =console.readline ();8Console.WriteLine ("Please enter your age:");9int age =console.readline ();Ten  One             //Call the ExecuteNonQuery (string sql,params sqlparameter[] parameters) function to insert the obtained data into the database A             introws = SqlHelper. ExecuteNonQuery ("INSERT INTO T_test (name,age) vaules (@Name, @Age)",NewSqlParameter ("@Name", name),NewSqlParameter ("@Age", age)); -  -Console.WriteLine ("successfully inserted {0} bar data", rows);//Show Execution Results the Console.readkey (); -         } -  -}

Delete data

1  1     class Program2  2     {3  3         Static voidMain (string[] args)4  4         {5  5             stringName ="Zhang San";6  6 7  7             intresult = SqlHelper. ExecuteNonQuery ("Delete from t_test where [email protected]",NewSqlParameter ("@Name", name));//Delete the data named Zhang San8  8Console.WriteLine ("The {0} data was successfully deleted.", result);9  9 Ten TenConsole.readkey (); One  One         } A  -}

Querying data

1. Create a WinForm form and place a control DataGridView

2. Add a method to call the SqlHelper class in the loading event of the form and assign the result to DataGridView with the following code:

1          Private void Form1_Load (object  sender, EventArgs e)2         {3              // Add a method that calls the SqlHelper class in the Load event of the form and assign the result to DataGridView 4              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 what I have reviewed today is the focus, although very food ah, this is my first time to write a blog with code, I finally out of this step, I will also write more blog, share their own learning in the drip.

The great god do not spray ha ...

Ado. NET review--Write your own SqlHelper class