C # connect to the SQL Server database for simple operations,

C # connect to the SQL Server database for simple operations,

Environment: VS2010 + SQL Server 2008

 

First, design a database operation tool class MyTool according to the object-oriented programming philosophy. cs, which encapsulates methods for database connection and operations. Each function module only needs to call the corresponding functions when database operations are required.

// Introduce the namespace using System. data. sqlClient; // The namespace using System used for SQL Sever data access. data; // The namespace using System of the DataSet class. windows. forms; // The namespace of the DataGridView control class // execute the specified SQL command statements (insert, delete, update, etc ), return the number of lines affected by the command: public static int executeCommand (string sqlStr) {SqlConnection sqlConnection1 = new SqlConnection ("server = dell-PC; database = 11071312 running sys; uid = sa; pwd = xiaoyi9421 "); // create a database connection (my personal database information in the string) sqlConnection1.Open (); // open the database connection SqlCommand sqlCommand1 = new SqlCommand (sqlStr, sqlConnection1); // execute the SQL command int Succnum = sqlCommand1.ExecuteNonQuery (); return Succnum;} // query the data records specified by select (multiple rows and multiple columns ), and fill it in the Data Control DataGridView public static void queryDataToGrid (string sqlStr, DataGridView datagridatagridatagri1) {SqlConnection sqlConnection1 = new SqlConnection ("server = dell-PC; database = 11071312 HotelSys; uid = sa; pwd = xiaoyi9421 "); // create a database connection SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter (sqlStr, sqlConnection1); // use the created sqlConnection1, create data adapter sqlDataAdapter1 DataSet dataSet1 = new DataSet (); // create a DataSet object sqlDataAdapter1.Fill (dataSet1); // execute the query, the query results are stored in the dataset dataGridView1.DataSource = dataSet1.Tables [0]; // bind the query results in the dataset to the data specified by dataGridView1} // select (single data, assume it is string type), and return public static string queryData (string sqlStr) {SqlConnection sqlConnection1 = new SqlConnection ("server = dell-PC; database = 11071312 HotelSys; uid = sa; pwd = xiaoyi9421 "); // create a database connection SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter (sqlStr, sqlConnection1); // use the created sqlConnection1, create data adapter sqlDataAdapter1 DataSet dataSet1 = new DataSet (); // create a DataSet object sqlDataAdapter1.Fill (dataSet1); // execute the query. The query results are stored in the DataSet. return dataSet1.Tables [0]. rows [0] ["column name"]. toString (); // return the data in the specified column in the first row of the query result in the string type}

 

To perform database operations in each function module, you only need to specify the SQL statement to be executed and call the methods in the database tool class, below are some basic SQL operations (single table)

// Add sqlStr = "insert into Table Name (column name 1, column name 2) values (insert value 1, insert value 2)"; // execute the specified SQL command statement, return the number of lines affected by the command int Succnum = MyTool.exe cuteCommand (sqlStr); if (Succnum> 0) MessageBox. show ("input successful"); // delete sqlStr = "delete from table name where delete condition"; int Succnum = MyTool.exe cuteCommand (sqlStr); if (Succnum> 0) MessageBox. show ("deleted successfully"); // change sqlStr = "update table name set column name 1 = update value 1, column name 2 = update value 2 "; int Succnum = MyTool.exe cuteCommand (sqlStr); if (Succnum> 0) MessageBox. show ("updated successfully"); // query a set of data sqlStr = "select column name 1, column name 2 from table name where query expression"; MyTool. queryDataToGrid (sqlStr, dataGridView1); // fill in the Data Control DataGridView // query a single data sqlStr = "select column name from table name where query expression"; textBox1.Text = MyTool. queryData (sqlStr); // fill in textBox1 in the text box