C # sqlserver2000

Server|sqlserver Visual Studio.NET's Chinese Beta 2 version 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 (SQL OLE db) in the application system and then install it automatically, then use SQL Server Enterprise Mana GER Create a database called Tyjdb and a new data table for address with 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.