SQL Server Programming for Visual C # (RPM)

Server|visual| programming Visual Studio.NET's Chinese Beta 2 has been around for some time, and the new version has changed significantly from earlier Beta 1 versions, including SQL Server programming, in Chinese Beta 2, Access to the database has generally been replaced by OLE DB. Since database programming is the core of enterprise-level application development, this article will illustrate the SQL Server programming method in Chinese Bete version 2.

Initial settings
First, we need to install SQL Server 2000,microsoft OLE DB Provider for SQL Server in the application system (SQL OLE D will also be installed automatically, followed by SQL Server Enterprise Manage R creates a database called Tyjdb, and creates a new data table for address that contains the name, email, age, and address four fields.

Open the Server Explorer in the view selection item in the VS development environment, which manages database connections for SQL Server and various other OLE DB, and manages the data in them. Then we add a new data connection, the connection properties select Microsoft OLE DB Provider for SQL Server, and then choose the server and database Tyjdb, and then when the test connection succeeds, press OK. Using this tool, you can quickly and accurately generate the database connection string that you want.

Connecting to a database
Create a new ASP.net project or windows.net application because the database access program for both is the same. When you drag a data connection from Server Explorer 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 ";
Where WhoAmI is the author's server name.

Select the SqlDataAdapter in the Toolbox and drag to the Web Form, prompt for a TYJDB data connection, choose to use SQL statements to access the database, and only fill in the Selectfrom address when generating the SQL statement, confirming that it is complete. The program generates code as follows:

protected System.Data.SqlClient.SqlDataAdapter SqlDataAdapter1;
Main classes for accessing databases
protected System.Data.SqlClient.SqlCommand SqlSelectCommand1;
Classes processed by SQL statements
protected System.Data.SqlClient.SqlConnection SqlConnection1;
Classes that connect to a database
In InitializeComponent () there are the following declarations:
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";
This.sqlSelectCommand1.Connection = This.sqlconnection1;

To make the data in the table appear in the Web form, add a DataGrid control to the Web form, and add the following statement to the Page_Init:

Sqlconnection1.open ();
Open a database connection
DataSet Objdataset;
Create a new dataset to put data
Objdataset=new DataSet ();
SqlDataAdapter1.Fill (Objdataset, "address");
Filling data into a dataset
datagrid1.datasource=objdataset.tables["Address"]. DefaultView;
Associated Datasets and DataGrid
Datagrid1.databind ();
Binding Data
Sqlconnection1.close ();
To close a database connection

The Web form has been able to display the data in the database in the DataGrid after compilation execution.

Add data
To increase the database data, we simply add a textbox to the number of fields on the Web form, add a key, and then add the click event for the key, which is as follows:

sqlinsertcommand1.parameters["@name"]. Value=textbox1.text;
Assigning a textbox to a corresponding parameter
sqlinsertcommand1.parameters["@email"]. Value=textbox2.text;
sqlinsertcommand1.parameters["@age"]. Value=textbox3.text;
sqlinsertcommand1.parameters["@address"]. Value=textbox4.text;
SqlInsertCommand1.Connection.Open ();
Open connection
Sqlinsertcommand1.executenonquery ();
Execute INSERT statement
SqlInsertCommand1.Connection.Close ();
Close 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 you execute this program, just fill in the TextBox with the value of the record field you want to add, and then press the key to perform the add function.

Delete data
To delete the database data, we need to add a TextBox5 and a key button on the Web form, adding the following code for the key:

SqlCommand sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand ();
Declaring SQL command class objects
This.sqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
Sqldeletecommand1.commandtext= "DELETE from address WHERE name= '" +textbox5.text+ "";
SQL statement
Sqldeletecommand1.connection = This.sqlconnection1;
Data connection used by the Declaration
SqlDeleteCommand1.Connection.Open ();
Sqldeletecommand1.executenonquery ();
Execute the SQL statement
SqlDeleteCommand1.Connection.Close ();

When you execute this program, you simply fill in the value of the Record name field that you want to delete in TextBox5, and then press the key to perform the deletion function.

Update operating principles are similar, specific development can have a lot of skills to enrich the above procedures, such as increasing error handling, here is limited to the length of the narrative.

In summary, it is very useful to improve programming efficiency and quality by making full use of Visual Studio.NET development environment to simplify program design.

These programs are developed in C #, and are compiled and run correctly in Chinese Windows Server, SQL Server 2000, Visual Studio.NET Chinese Beta 2, and the Chinese display is normal.