C#数据库操作小结

每次做项目都会用到数据库，对数据库的操作都是糊里糊涂从书里找代码用。通过昨天晚上与今天早上的努力，把数据库的操作整理了一下，下面把整理结果做个小结。
1、常用的T-Sql语句
 查询：SELECT * FROM tb_test WHERE ID='1' AND name='xia'
SELECT * FROM tb_test
插入：INSERT INTO tb_test VALUES('xia','123')
INSERT INTO tb_test(name) VALUES('xia')
更新：UPDATE tb_test SET password='234' WHERE ID='1'
删除：DELETE FROM tb_test WHERE ID='1'
DELETE tb_test WHERE ID='1'
2、在vs2010中获取数据库连接字符串
 string connectionString = Properties.Settings.Default.DatabaseTestConnectionString;
3、SqlCommand类型
查询：
 1 using (SqlConnection connection = new SqlConnection(connectionString))
 2        {
 3              try
 4              {
 5                     SqlCommand command = new SqlCommand(selectStr, connection);
 6                     command.Connection.Open();
 7                     SqlDataReader reader = command.ExecuteReader();
 8                      while (reader.Read())
 9                              label1.Text = "name：" + reader["name"].ToString();    //数据读取
10                      command.Connection.Close();
11                }
12               catch (SqlException ex)
13               {
14                     throw ex;
15               } 
16        }

插入、修改、删除：

 1 using (SqlConnection connection = new SqlConnection(connectionString))
 2        {
 3              try
 4              {
 5                     SqlCommand command = new SqlCommand(cmdStr, connection);
 6                     command.Connection.Open();
 7                     command.ExecuteNonQuery();
 8                     command.Connection.Close();
 9               }
10               catch (SqlException ex)
11               {
12                     throw ex;
13               }
14       }

4、DataTable类型，查询、添加、修改、删除
 DataTable使用查询、添加、删除、修改时，需要用到SqlDataAdapter类
string selectStr = "SELECT * FROM tb_test2";
查询：

 1  using (SqlConnection connection = new SqlConnection(connectionString))
 2       {
 3              try
 4              {
 5                     SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
 6                     DataTable dataTable = new DataTable();
 7                     adapter.Fill(dataTable);
 8                     //数据读取
 9                     label1.Text = dataTable.Rows[0][0].ToString();
10               }
11              catch (SqlException ex)
12              {
13                      throw ex;
14               }
15       }

添加：

 1 using (SqlConnection connection = new SqlConnection(connectionString))
 2       {
 3            try
 4            {
 5                  SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
 6                  DataTable dataTable = new DataTable();
 7                  adapter.Fill(dataTable);
 8                   //添加数据
 9                  DataRow newRow = dataTable.NewRow();
10                  newRow["id"] = "tesr";
11                  newRow["name"] = "111";
12                  dataTable.Rows.Add(newRow);
13                  SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
14                  adapter.Update(dataTable); //更新到数据库
15             }
16             catch (SqlException ex)
17             {
18                  throw ex;
19             }
20       }

修改：

 1  using (SqlConnection connection = new SqlConnection(connectionString))
 2       {
 3             try
 4             {
 5                   SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
 6                   DataTable dataTable = new DataTable();
 7                   adapter.Fill(dataTable);
 8                   //修改数据
 9                  DataRow updateRow = dataTable.Rows[0];
10                  updateRow["id"] = "update";
11                  updateRow["name"] = "222";
12                  SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
13                  adapter.Update(dataTable); //更新到数据库
14             }
15            catch (SqlException ex)
16            {
17                  throw ex;
18            }
19       }

 删除：

 1 using (SqlConnection connection = new SqlConnection(connectionString))
 2       {
 3             try
 4             {
 5                    SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
 6                    DataTable dataTable = new DataTable();
 7                    adapter.Fill(dataTable);
 8                    dataTable.Rows[0].Delete(); //删除记录
 9                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
10                    adapter.Update(dataTable); //更新到数据库
11              }
12             catch (SqlException ex)
13             {
14                    throw ex;
15             } 
16       }

5、DataSet类型
 DataSet操作跟DataTabel操作基本是一样的，只是DataSet可以储存有多个表格，所以就多做介绍了
6、个人总结
 个人感觉，用 SqlCommand比较灵活，而DataSet是实现ADO.NET断开式连接的核心，比较安全 

posted @ 2015-01-22 10:38  sirzhang  阅读(231)  评论(0)    收藏  举报
AmazingCounters.com