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 D will also be automatically installed 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;
// Main types of Database Access
Protected system. Data. sqlclient. sqlcommand sqlselectcommand1;
// SQL statement processing class
Protected system. Data. sqlclient. sqlconnection sqlconnection1;
// Database 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 ();
// Open the database connection
Dataset objdataset;
// Create a dataset for data storage.
Objdataset = new dataset ();
Sqldataadapter1.fill (objdataset, "Address ");
// Enter the data in Dataset
Datagrid1.datasource = objdataset. Tables ["Address"]. defaultview;
// Associate dataset and DataGrid
Datagrid1.databind ();
// Bind data
Sqlconnection1.close ();
// Close the 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;
// Assign the textbox value to the corresponding parameter
Sqlinsertcommand1.parameters ["@ email"]. value = textbox2.text;
Sqlinsertcommand1.parameters ["@ age"]. value = textbox3.text;
Sqlinsertcommand1.parameters ["@ address"]. value = textbox4.text;
Sqlinsertcommand1.connection. open ();
// Open the connection
Sqlinsertcommand1.executenonquery ();
// Execute the insert statement
Sqlinsertcommand1.connection. Close ();
// Close the connection
Sqlconnection1.open ();
Dataset objdataset;
// The following program segment updates the 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 ();
// Declare an SQL command object
This. sqldataadapter1.deletecommand = sqldeletecommand1;
Sqldeletecommand1.commandtext = "delete from address where name = '" + textbox5.text + "'";
// SQL statement
Sqldeletecommand1.connection = This. sqlconnection1;
// Declare the data connection used
Sqldeletecommand1.connection. open ();
Sqldeletecommand1.executenonquery ();
// Execute 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.

To:

Http://www.th7.cn/Article/bc/Cy/200703/13034.html