C # Operations SQL Server database

In C # program development often need to deal with SQL Server database, below we understand the C # operation of SQL Server database method: 1. The first step is to add the necessary references and some data members to our program. Because our program involves access to the database, you have to apply the object to the data provider (Provider). Under the. NET Framework, we mainly use data providers in two categories, one for SQL type and the other for OLE DB type. They refer to the namespaces System.Data.SqlClient and System.Data.OleDb, respectively. Because our program accesses the SQL Server database, we use the data provider object to be of type SQL. The reason is simple, the object of this type is specifically designed for the SQL Server database, so the performance is greatly optimized. So, at the beginning of our program, add the following sentence: using System.Data.SqlClient; 1. After defining a variable to add a reference to the System.Data.SqlClient namespace, we then add some necessary data members to the program that can perform some operations on the database and its objects.private String ConnectionString = "Data source=192.168.0.10;initial catalog=test; Persist Security info=true; User Id=sa;   Password=test "; The connection string that contains the connection parameter private SqlConnection conn = null; The database connection object.  Implementation and database connection private SqlCommand cmd = null; The database connection command object.   Specifies the SQL statement to execute private string sql = null; That holds the SQL statement. 2. Connect to the database //Create a connection conn = new SqlConnection (ConnectionString);   ...//Other processing code  if (Conn. state = = ConnectionState.Open)Conn. Close ();3. Create a database   once the database connection object has been created, we can use it in the program. First, we create a database of type SQL Server dynamically in the program. We created the database in the C:mysql directory, so the reader to practice this instance must first create a folder named MySQL under C: or it will go wrong! The key to creating a database is the SQL object in the function, which we specify some basic properties of the database file. After that, we created a new SqlCommand object that we actually completed the operation of the database. The implementation of the function is as follows;   private void Button1_Click (object sender, System.EventArgs e) {//Open database connection if (conn. state! = ConnectionState.Open) Conn. 　Open (); String sql = "CREATE DATABASE mydb on PRIMARY" + "(name=test_data, filename = ' C:\mysql\mydb_data.mdf ', size=3," + "maxsi ze=5, filegrowth=10%) Log on "+" (name=mydbb_log,filename= ' C:\mysql\mydb_log.ldf ', size=3, "+" maxsize=20,filegrowth=1 　)"; 　cmd = new SqlCommand (SQL, conn); try {cmd. 　ExecuteNonQuery (); } catch (SqlException ae) {MessageBox.Show (AE. 　Message.tostring ()); }}4. Create a table and add records:

After the

database is created, we need to create a table for it, which is the base object in the database. We completed the creation of the table by creating the SQL statement, and after the table was created, we determined its schema. After that, we also added four records to the table using the INSERT statement for later use. The function is implemented as follows:

private void Button2_Click (object sender, System.EventArgs e) {//Open database connection if (conn. state = = ConnectionState.Open) Conn. 　Close ();　　　 　ConnectionString = "Integrated security=sspi;" + "Initial catalog=mydb;" + "Data source=localhost;"; Conn. 　ConnectionString = ConnectionString; Conn. 　Open (); sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT Pkeymyid PRIMARY KEY," + "MyName char (+), myAddress char (255), my 　Balancefloat) "; 　cmd = new SqlCommand (SQL, conn); try {cmd. 　　　　　ExecuteNonQuery (); Add records to the table sql = "INSERT into myTable (MyId, myname,myaddress, mybalance)" + "VALUES (1001, ' Puneet Nehra ', ' A 449 Sect 1 　　9, DELHI ', 23.98) "; 　　cmd = new SqlCommand (SQL, conn); Cmd. ExecuteNonQuery (); sql = "INSERT into myTable (MyId, myname,myaddress, mybalance)" + "VALUES (1002, ' Anoop Singh ', ' Lodi Road, DELHI ', 353.64) 　　" ; 　　cmd = new SqlCommand (SQL, conn); Cmd.　 ExecuteNonQuery (); sql = "INSERT into myTable (MyId, MyName, myAddress, mybalance)" + "VALUES (1003, ' Rakesh M ', ' Nag ChOwk, Jabalpur M.P ", 43.43)"; 　cmd = new SqlCommand (SQL, conn); Cmd.　 ExecuteNonQuery (); sql = "INSERT into myTable (MyId, MyName, myAddress, mybalance)" + "VALUES (1004, ' Madan Kesh ', ' 4th Street, Lane 3, DELHI ') 　　, 23.00) "; 　　cmd = new SqlCommand (SQL, conn); Cmd. 　ExecuteNonQuery (); } catch (SqlException ae) {MessageBox.Show (AE. 　Message.tostring ()); } 　}

5. To create a generic SQL statement execution function:

Considering the extensive use of SqlCommand objects to manipulate the database after the program, we have designed a general SQL statement execution function in the program. It creates a SqlCommand object based on the database connection object and the SQL object, and then completes the related operations on the database. The implementation of the function is as follows:

private void ExecuteSQLStmt(string sql) 　{ 　// 打开数据库连接 　if( conn.State == ConnectionState.Open) 　　conn.Close(); 　ConnectionString ="Integrated Security=SSPI;" + 　　"Initial Catalog=mydb;" + 　　"Data Source=localhost;";　　　 　conn.ConnectionString = ConnectionString; 　conn.Open(); 　cmd = new SqlCommand(sql, conn); 　try 　{ 　　cmd.ExecuteNonQuery(); 　} 　catch(SqlException ae) 　{ 　　MessageBox.Show(ae.Message.ToString()); 　} 　}

6. To create a stored procedure:

We created the stored procedure by creating procedure, which also called the general SQL statement execution function above. The implementation of the function is as follows:

private void button3_Click(object sender, System.EventArgs e) 　{ 　sql = "CREATE PROCEDURE myProc AS"+ 　　" SELECT myName, myAddress FROM myTable GO"; 　ExecuteSQLStmt(sql); 　}

After the stored procedure has been created, we can find its properties in SQL Server Enterprise Manager.

7. To create a view:

The process of creating a view is similar to the method above for creating a stored procedure, except that the SQL statement used is CREATE view. The implementation of the function is as follows:

private void button4_Click(object sender, System.EventArgs e) 　{ 　sql = "CREATE VIEW myView AS SELECT myName FROM myTable"; 　ExecuteSQLStmt(sql); 　}

Again, after the creation is complete, we can find its properties in Enterprise Manager.

8. To view the data in a table:

As we look at the data in the table, we apply the two important objects of SqlDataAdapter and the dataset in ADO. Where the SqlDataAdapter object is the data provider object of the previously mentioned SQL type, which is dedicated to the SQL Server database. The DataSet object is independent of the type of the data provider object and is suitable for any type of data provider object, which is included in the System.Data namespace and is at the heart of the ADO architecture. The access pattern for its data is non-connected (disconnected), where the DataSet object disconnects from the database (or XML data stream) when it is read to the DataSet object. After a series of operations are completed, the DataSet object is then connected to the database and the data is updated. The SqlDataAdapter object provides fill and update two basic methods to complete the read and update operation of the data.

So here's how the function is implemented:

private void button5_Click(object sender, System.EventArgs e) 　{ 　// 打开数据库连接 　if( conn.State == ConnectionState.Open) 　　conn.Close(); 　ConnectionString ="Integrated Security=SSPI;" + 　　"Initial Catalog=mydb;" + 　　"Data Source=localhost;";　　　 　conn.ConnectionString = ConnectionString; 　conn.Open(); 　// 创建数据适配器 SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM myTable", conn); // 创建一个数据集对象并填充数据，然后将数据显示在DataGrid控件中 DataSet ds = new DataSet("myTable"); da.Fill(ds, "myTable"); dataGrid1.DataSource = ds.Tables["myTable"].DefaultView; 　}

该操作的结果就是在程序的DataGrid控件中显示表中的数据。

9．查看存储过程中的数据：

This procedure is similar to the process of looking at the data in the table above, where the stored procedure that is called is the one we created earlier. The implementation of the function is as follows:

private void button6_Click(object sender, System.EventArgs e) 　{ 　// 打开数据库连接 　if( conn.State == ConnectionState.Open) 　　conn.Close(); 　ConnectionString ="Integrated Security=SSPI;" + 　　"Initial Catalog=mydb;" + 　　"Data Source=localhost;";　　　 　conn.ConnectionString = ConnectionString; 　conn.Open();　　　 　// 创建数据适配器 　SqlDataAdapter da = new SqlDataAdapter("myProc", conn); 　// 创建一个数据集对象并填充数据，然后将数据显示在DataGrid控件中 　DataSet ds = new DataSet("SP"); 　da.Fill(ds, "SP"); 　dataGrid1.DataSource = ds.DefaultViewManager; 　}10. To view the data in the view:

The process is similar to the above two processes, and the call view is the one we created earlier. The implementation of the function is as follows:

private void button7_Click(object sender, System.EventArgs e) 　{ 　// 打开数据库连接 　if( conn.State == ConnectionState.Open) 　　conn.Close(); 　ConnectionString ="Integrated Security=SSPI;" + 　　"Initial Catalog=mydb;" + 　　"Data Source=localhost;";　　　 　conn.ConnectionString = ConnectionString; 　conn.Open();　　　 　// 创建数据适配器 SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM myView", conn); // 创建一个数据集对象并填充数据，然后将数据显示在DataGrid控件中 DataSet ds = new DataSet(); da.Fill(ds); dataGrid1.DataSource = ds.DefaultViewManager; 　}

11．创建其他数据库对象：

In a relational database, the database objects that are commonly involved include indexes, constraints, and so on. They are necessary to efficiently implement database query operations and satisfy database integrity rules. We can create indexes by creating an index statement to create a constraint (also known as a rule) by creating a rule statement. The implementation of the function is as follows:

private void button8_Click(object sender, System.EventArgs e) 　{ 　sql = "CREATE UNIQUE INDEX "+ 　　"myIdx ON myTable(myName)";　　 　ExecuteSQLStmt(sql);　 　sql = "CREATE RULE myRule "+ 　　"AS @myBalance >= 32 AND @myBalance < 60"; 　ExecuteSQLStmt(sql); 　}

12. To modify a table:

Above we have created a table for the database, but the actual application process may also be to add some fields for the table or delete some fields, which will be done by the ALTER TABLE SQL statement. The method by which fields are added is alter TABLE ... ADD ..., and the way to delete a field is alter TABLE ... DROP ..., we have added a field to the table in this procedure. The implementation of the function is as follows:

private void button9_Click(object sender, System.EventArgs e) 　{ 　sql = "ALTER TABLE MyTable ADD "+ 　　"newCol TIMESTAMP";　　　　　　　　　　　 　ExecuteSQLStmt(sql); 　}

13．删除表：

Deleting a table is removing a table that is no longer useful from the database, which we can do with the drop TABLE statement. The implementation of the function is as follows:

private void button10_Click(object sender, System.EventArgs e) 　{ 　string sql = "DROP TABLE myTable"; 　ExecuteSQLStmt(sql); 　}

C # Operations SQL Server database