SQL Server database connection programming in Visual C #

When C # involves database programming, new users are always confused. In fact, you can easily connect to the database and bind fields to the TextBox or Datagrid you want to bind according to the following methods. Now, let's get started:

First, we need to install SQL Server 2000 in the application system, and Microsoft ole db Provider for SQL Server (SQL OLE D will also be installed automatically at the same time, then, use the Enterprise Manager of SQL Server to create a database named wjjdb (wjj is my name, and you can change it to the name you want, 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 will add a new data connection, select Microsoft ole db Provider for SQL server as the connection property, select the server and database wjjdb, and confirm the connection after testing. Use this tool to quickly and accurately generate the required database connection string. Create a WEB. NET project or WinForm. NET application to connect to the database because the database access programs of the two are the same. We use WinForm. for example, you can 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 = wjdb; 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 wjjdb data connection, 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 class for database access
Protected System. Data. SqlClient. SqlCommand sqlSelectCommand1; // class for SQL statement Processing
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 data-stored DataSet.
DataSet objDataset = new DataSet ();
SqlDataAdapter1.Fill (objDataset, "address"); // enter data
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, you only need to add a TextBox with the number of 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.

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 database data, you must add TextBox5 and a button on the Web Form to add the following code for the button:

SqlCommand sqlDeleteCommand1 = new System. Data. SqlClient. SqlCommand (); // declare the SQL Command Class 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 all developed in C #. In Chinese Windows XP (I am used to using XP, but note that SQL2000 Enterprise Edition cannot be installed in XP, but only the Personal Edition can be installed, but I think there are no functional limitations), SQL Server 2000, Visual Studio. NET Chinese in Beta 2 environment, compilation and normal operation, Chinese display normal.