. NET database connection and basic addition, deletion, modification, and query operations tutorial,. net addition and Deletion

. NET database connection and basic addition, deletion, modification, and query operations tutorial,. net addition and Deletion

I. Preface

Because the. net Course selected this semester is about to take the computer examination, so summarize the. net method of operating SQL server data. (Because I am directed to Java, so right. net does not know much about it, but the code written below is successfully tested.) let's take a look at the details below.

Ii... net connection to the database

Configure the database connection in the web. config file. The code is written in<configuration></configuration>Internal

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

Sa is the SQL Server database account, sa123456 is the database account password, and dataset database name.

Through the above code, we can easily see that this is connected through the account of the SqlServer database. So what if you need to connect through Windows identity authentication?

 <appSettings> <add key="connstring" value="Data Source=.;Initial Catalog=dataset;Integrated Security=True"/> </appSettings>

Dataset indicates the database name.

After configuration, use the Connection object in the cs background file to connect

String constr = ConfigurationManager.AppSettings["connstring"].ToString();  SqlConnection myconn = new SqlConnection(constr);

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

At this time, you may wonder, since it is a string in the configuration file, can you directly write it in the cs background file without configuring it in the configuration file? The answer is yes.

String constr = "Data Source=.;Initial Catalog=dataset;Integrated Security=True";SqlConnection myconn = new SqlConnection(constr);

By carefully comparing the previously written configuration files, we can see the similarities between the two methods.

Now let's briefly talk about what the Connection object is. In ADO. NET, the main purpose of the Connection object is to open and close the database Connection. Through this object, you can access and operate the database.

String constr = "Data Source = .; initial Catalog = dataset; Integrated Security = True "; SqlConnection myconn = new SqlConnection (constr); myconn. open (); // related database operations myconn. close ();

Open is to Open the database, Close is to Close the database.

Iii.. net database operations

So how do I operate the database after opening the database through the Connection object? Dao. Net provides Command objects, which are used to add, delete, modify, and query databases.

Here is an example of a search operation:

String constr = "Data Source = .; initial Catalog = dataset; Integrated Security = True "; SqlConnection myconn = new SqlConnection (constr); myconn. open (); string SQL = "select * from custom"; SqlCommand cmd = new SqlCommand (SQL, sqlConnection); SqlDataReader dataReader = cmd. executeReader (); Show. text = "<tr> <td> Student ID </td> <td> name </td> <td> school </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 ();

Through the above Code, it is not difficult to find that after the Connection object is enabled, we first need to write a query SQL statement:

   string sql = "select * from custom ";

Then, a new SqlCommand object is provided. The parameters are SQL statements and Connection objects.

   SqlCommand cmd = new SqlCommand(sql, sqlConnection);

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

   SqlDataReader dataReader = cmd.ExecuteReader();

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

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, our Connection object is closed.

   sqlConnection.Close();

The query operation is performed through the ExecuteReader method of the Command object. What about addition, deletion, and modification?

Add, delete, and modify the ExecuteNonQuery method of the Command object. This method returns the affected number of rows, that is, 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 = new SqlCommand (SQL, myconn); if (cmd. executeNonQuery ()> 0) {this. response. write ("<script language = 'javascript '> alert (' added successfully! ') </Script> ");} else {this. Response. Write (" <script language = 'javascript'> alert ('addition failed! ') </Script> ");} myconn. Close ();

Through the above Code, it is not difficult to find that the routine is similar to the query we write, that is, the SQL statement is different from the Command object called.

However, you will find that the code is spliced using SQL statements. This write method is messy (double quotation marks and single quotation marks), and may be injected by SQL. At this time, I will think, can there be placeholders? The answer is yes.

Myconn. open (); SqlCommand cmd = myconn. createCommand (); cmd. commandText = "insert into custom values (@ uid, @ uname, @ udepart, @ uage, @ uename, @ upassword)"; cmd. parameters. addWithValue ("@ uid", uid. text); cmd. parameters. addWithValue ("@ uname", uname. text); cmd. parameters. addWithValue ("@ udepart", udepart. text); cmd. parameters. addWithValue ("@ uage", uage. text); cmd. parameters. addWithValue ("@ uename", uename. tex T); cmd. parameters. addWithValue ("@ upassword", upassword. text); if (cmd. executeNonQuery ()> 0) {this. response. write ("<script language = 'javascript '> alert (' added successfully! ') </Script> ");} else {this. Response. Write (" <script language = 'javascript'> alert ('addition failed! ') </Script> ");} myconn. Close (); ShowAll ();

The difference between this code and the previous code is:

1. Create a SqlCommand object using the CreateCommand method of the Connection object.

   SqlCommand cmd = myconn.CreateCommand();

2. The CommandText attribute of the Command object is used to write the SQL statement text. It can also be the name of the stored procedure.

   cmd.CommandText = "insert into custom values(@uid,@uname,@udepart,@uage,@uename,@upassword)";

3. input Parameters using the Parameters attribute of the Command object.

cmd.Parameters.AddWithValue("@uid", uid.Text);   cmd.Parameters.AddWithValue("@uname", uname.Text);   cmd.Parameters.AddWithValue("@udepart", udepart.Text);   cmd.Parameters.AddWithValue("@uage", uage.Text);   cmd.Parameters.AddWithValue("@uename", uename.Text);   cmd.Parameters.AddWithValue("@upassword", upassword.Text);

Well, it looks quite comfortable with this change. ^_^

The process of modification, deletion, and addition is similar. No code is posted here.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.