SQL Server programming in Visual C #

Visual Studio. NET Chinese Version Beta 2 has been released for some time. Compared with the early version of Beta 1, the new version has undergone great changes in various aspects, including SQL Server programming, in Chinese Beta 2, database access is generally switched to ole db. As database programming is the core of enterprise-level application development, this article will illustrate the SQL Server programming method in Chinese Bete 2.

Initial settings

First, we need to install SQL Server 2000 in the application system. Microsoft ole db Provider for SQL Server (SQL OLE DB) will also be installed automatically at the same time, create a database named tyjdb using Enterprise Manager of SQL Server, and create a data table named address, which contains four fields: name, email, age, and address.

Open the Server resource manager in the View menu item of the VS development environment. This manager can manage the database connections of SQL Server and other OLE databases and manage the data. Next, we add a new data connection, select Microsoft ole db Provider for SQL server as the connection property, select the server and database tyjdb, and confirm the connection after testing. Use this tool to quickly and accurately generate the required database connection string.

Connect to database

Create an ASP. NET project or a Windows. NET application because the database access programs are the same. Drag the data connection in the server resource manager to the new Web Form. A connection string is automatically generated as follows:

This. sqlConnection1.ConnectionString
= "Data source = WHOAMI;
Initial catalog = tyjdb;
Integrated security = SSPI;
Persist security info = False;
Workstation id = WHOAMI;
Packet size = 4096 ";
WHOAMI is the author's server name.

Select the SqlDataAdapter in the toolbox and drag it to the Web Form. When prompted, select the data connection of tyjdb, select the SQL statement to access the database, and enter the selectfrom address when generating the SQL statement. The code generated by the program is as follows:

Protected System. Data. SqlClient. SqlDataAdapter sqlDataAdapter1;
File: // saveMain database fetch classes
Protected System. Data. SqlClient. SqlCommand sqlSelectCommand1;
File: // SQLStatement processing class
Protected System. Data. SqlClient. SqlConnection sqlConnection1;
File: // connectDatabase Connection class
The following declaration is contained in InitializeComponent:
This. sqlConnection1 = new System. Data. SqlClient. SqlConnection ();
This. sqlDataAdapter1 = new System. Data. SqlClient. SqlDataAdapter ();
This. sqlSelectCommand1 = new System. Data. SqlClient. SqlCommand ();
This. sqlDataAdapter1.SelectCommand = this. sqlSelectCommand1;
This. sqlSelectCommand1.CommandText = "SELECT name, email, age, address FROM address ";
This. sqlSelectCommand1.Connection = this. sqlConnection1;

 

To enable the Table data to be displayed in Web Form, add a DataGrid Control to Web Form and add the following statement to Page_Init:

SqlConnection1.Open ();
File: //Open Database Connection
DataSet objDataset;
File: // newCreate a DataSet for data storage
ObjDataset = new DataSet ();
SqlDataAdapter1.Fill (objDataset, "address ");
File: // setEnter data in DataSet
DataGrid1.DataSource = objDataset. Tables ["address"]. DefaultView;
File: // closeJoin DataSet and DataGrid
DataGrid1.DataBind ();
File: // bindSet Data
SqlConnection1.Close ();
File: // closeClosed database connection

After compilation and execution, the Web Form can display data in the database in the DataGrid.

Add data

To add database data, we only need to add a TextBox with the number of corresponding fields on the Web Form, add a button, and then add a Click event for the button. The Code is as follows:

SqlInsertCommand1.Parameters ["@ name"]. Value = TextBox1.Text;
File: // setTextBox value to corresponding parameter
SqlInsertCommand1.Parameters ["@ email"]. Value = TextBox2.Text;
SqlInsertCommand1.Parameters ["@ age"]. Value = TextBox3.Text;
SqlInsertCommand1.Parameters ["@ address"]. Value = TextBox4.Text;
SqlInsertCommand1.Connection. Open ();
File: //Open connection
SqlInsertCommand1.ExecuteNonQuery ();
File: // ExecuteRow Insert statement
SqlInsertCommand1.Connection. Close ();
File: // closeClosed connection
SqlConnection1.Open ();
DataSet objDataset;
File: //Area program segment update DataGrid
ObjDataset = new DataSet ();
SqlDataAdapter1.Fill (objDataset, "address ");
DataGrid1.DataSource = objDataset. Tables ["address"]. DefaultView;
DataGrid1.DataBind ();

When executing this program, you only need to enter the value of the record field to be added in the TextBox, and then press this button to perform the Add function.

Delete data

To delete the database data, you must add TextBox5 and a button on the Web Form to add the following code:

SqlCommand sqlDeleteCommand1 = new System. Data. SqlClient. SqlCommand ();
File: // soundExplicit SQL Command Class Object
This. sqlDataAdapter1.DeleteCommand = sqlDeleteCommand1;
SqlDeleteCommand1.CommandText = "delete from address where name =" + TextBox5.Text + "";
File: // SQLStatement
SqlDeleteCommand1.Connection = this. sqlConnection1;
File: // soundData connection used
SqlDeleteCommand1.Connection. Open ();
SqlDeleteCommand1.ExecuteNonQuery ();
File: // ExecuteLine this SQL statement
SqlDeleteCommand1.Connection. Close ();

When executing this program, you only need to enter the value of the Record name field to be deleted in TextBox5, and then press this button to perform the delete function.

The update operation principles are similar. In specific development, there are many skills to improve the above procedures, such as adding error handling. Here we will not describe them in detail.

To sum up, we can fully utilize the Visual Studio. NET development environment to simplify program design, which is very good for improving programming efficiency and quality.

The above programs are developed in C #. In the Chinese Windows 2000 Server, SQL Server 2000, Visual Studio. NET Chinese Beta 2 environment, the programs are compiled and run properly, and the Chinese display is normal.