. NET connection to SQL Server database and basic additions and deletions

First, write in front

Because the. NET course is going on the computer exam this semester, summarize the. NET operation of SQL Server data. (Because I'm in Java, I don't know much about. NET, but the following code has been tested successfully)

Ii.. NET Connection Database

Configuring database connections in the Web. config file, code written in <configuration></configuration>

  <appSettings>    <add key="connstring" value="user Id=sa; password=sa123456; Database=dataset; server= (local)" />  </appSettings>

Where SA is SQL Server database account, sa123456 is the database account password, DataSet bit database name.

It is not difficult to see through the above code that this is connected via the SQL Server database account. So what if you need to connect through Windows authentication?

  <appSettings>    <add key="connstring" value="Data source=.;i Nitial catalog=dataset;integrated security=true"/>  </appSettings>

Where the dataset is the database name.

Once configured, use the Connection object connection in the CS background file

String constr = configurationmanager.appsettings["connstring"]. ToString ();          New SqlConnection (CONSTR);

Obviously, the first sentence gets the string of information for the database that we configured in Web. config, and then new a SqlConnection object.

At this point, you might think, since it is to get the string in the configuration file, then you can write directly in the CS background file without the need to configure the configuration file to get it? The answer is yes.

" Data Source=.;i Nitial catalog=dataset;integrated security=true"new SqlConnection (CONSTR);

By carefully comparing the previously written configuration files, you will know the similarities between the two methods.

So now simply say what the Connection object is. The main purpose of the Connection object in ADO is to open and close the database connection, through which the database can be accessed and manipulated.

" Data Source=.;i Nitial catalog=dataset;integrated security=true"new  SqlConnection (CONSTR); MyConn. Open (); // related database Operation myconn. Close ();

Open is opening the database, close is shutting down the database.

Iii.. NET Operational Database

So how do you manipulate the database after you open the database with the Connection object? The command object is provided in Dao.net, which is mainly used for the operation of adding, deleting, changing and checking the database.

Let's look at an example of a lookup operation:

String Constr ="Data Source=.;i Nitial catalog=dataset;integrated security=true"; SqlConnection myconn=NewSqlConnection (CONSTR); MyConn.            Open (); stringsql ="SELECT * FROM Custom"; SqlCommand cmd=NewSqlCommand (SQL, sqlConnection); SqlDataReader DataReader=cmd.            ExecuteReader (); Show.text="<tr><td> Study number </td><td> name </td><td> College </td><td> age </td>< Td> Nickname </td></tr>";  while(Datareader.read ()) {Show.text= Show.text +"<tr><td>"+ datareader[0]. ToString ()+"</td><td>"+ datareader[1]. ToString ()+"</td><td>"+ datareader[2]. ToString ()+"</td><td>"+ datareader[3]. ToString ()+"</td><td>"+ datareader[4]. ToString () +"</td></tr>"; } sqlconnection.close ();

From the above code, it is not difficult to find that after opening the connection object, we first need to write a query SQL statement:

            string " ";

Then, new is a SqlCommand object, and the arguments are SQL statements and Connection objects.

            New SqlCommand (SQL, sqlConnection);

Then we execute the query statement by SqlCommand the ExecuteReader method of the object and return a DataReader object.

            SqlDataReader dataReader = cmd. ExecuteReader ();

Then, we read the data in the DataReader object (that is, the data we queried).

             while(Datareader.read ()) {Show.text= Show.text +"<tr><td>"+ datareader[0]. ToString ()+"</td><td>"+ datareader[1]. ToString ()+"</td><td>"+ datareader[2]. ToString ()+"</td><td>"+ datareader[3]. ToString ()+"</td><td>"+ datareader[4]. ToString () +"</td></tr>"; }

Finally, of course, we are shutting down our Connection object.

            Sqlconnection.close ();

The query operation is through the Command object's ExecuteReader method to operate, then adding and deleting changes?

If you change the words, you have to use the Command object's ExecuteNonQuery method. This method returns the number of rows affected, which means that if the operation fails, 0 is returned.

Let's look at an example of adding data:

myconn.            Open (); String SQL="INSERT into custom values ('"+ UID. Text +"', '"+ uname. Text +"', '"+Udepart. Text+"',"+ Uage. Text +", '"+ Uename. Text +"', '"+ Upassword. Text +"')"; SqlCommand cmd=NewSqlCommand (SQL, myconn); if(CMD. ExecuteNonQuery () >0)            {                 This. Response.Write ("<script language= ' javascript ' >alert (' added success! ') </script>"); }            Else            {                 This. Response.Write ("<script language= ' javascript ' >alert (' Add failed! ') </script>"); } myconn. Close ();

Through the above code, it is not difficult to find, in fact, the pattern and we write the same query, that is, the SQL statement is different from the command object called.

Of course, the changes and deletions and additions are more similar.

. NET connection to SQL Server database and basic additions and deletions