C#教程之C#数据库操作小结

当前位置:

首页 > 编程开发 > 汇编语言 >

C#教程之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类型
       查询：

复制代码代码如下:

	
	       using (SqlConnection connection = new SqlConnection(connectionString))

	       {

	             try

	             {

	                    SqlCommand command = new SqlCommand(selectStr, connection);

	                    command.Connection.Open();

	                    SqlDataReader reader = command.ExecuteReader();

	                     while (reader.Read())

	                             label1.Text = "name：" + reader["name"].ToString();    //数据读取

	                     command.Connection.Close();

	               }

	              catch (SqlException ex)

	              {

	                    throw ex;

	              }

	       }

插入、修改、删除：

复制代码代码如下:

	
	       using (SqlConnection connection = new SqlConnection(connectionString))

	       {

	             try

	             {

	                    SqlCommand command = new SqlCommand(cmdStr, connection);

	                    command.Connection.Open();

	                    command.ExecuteNonQuery();

	                    command.Connection.Close();

	              }

	              catch (SqlException ex)

	              {

	                    throw ex;

	              }

	      }

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

复制代码代码如下:

	
	      using (SqlConnection connection = new SqlConnection(connectionString))

	      {

	             try

	             {

	                    SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

	                    DataTable dataTable = new DataTable();

	                    adapter.Fill(dataTable);

	                    //数据读取

	                    label1.Text = dataTable.Rows[0][0].ToString();

	              }

	             catch (SqlException ex)

	             {

	                     throw ex;

	              }

	      }

添加：

复制代码代码如下:

	
	      using (SqlConnection connection = new SqlConnection(connectionString))

	      {

	           try

	           {

	                 SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

	                 DataTable dataTable = new DataTable();

	                 adapter.Fill(dataTable);

	                  //添加数据

	                 DataRow newRow = dataTable.NewRow();

	                 newRow["id"] = "tesr";

	                 newRow["name"] = "111";

	                 dataTable.Rows.Add(newRow);

	                 SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

	                 adapter.Update(dataTable); //更新到数据库

	            }

	            catch (SqlException ex)

	            {

	                 throw ex;

	            }

	      }

修改：

复制代码代码如下:

	
	      using (SqlConnection connection = new SqlConnection(connectionString))

	      {

	            try

	            {

	                  SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

	                  DataTable dataTable = new DataTable();

	                  adapter.Fill(dataTable);

	                  //修改数据

	                 DataRow updateRow = dataTable.Rows[0];

	                 updateRow["id"] = "update";

	                 updateRow["name"] = "222";

	                 SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

	                 adapter.Update(dataTable); //更新到数据库

	            }

	           catch (SqlException ex)

	           {

	                 throw ex;

	           }

	      }

删除：

复制代码代码如下:

	
	      using (SqlConnection connection = new SqlConnection(connectionString))

	      {

	            try

	            {

	                   SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

	                   DataTable dataTable = new DataTable();

	                   adapter.Fill(dataTable);

	                   dataTable.Rows[0].Delete(); //删除记录

	                   SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

	                   adapter.Update(dataTable); //更新到数据库

	             }

	            catch (SqlException ex)

	            {

	                   throw ex;

	            }

	      }

5、DataSet类型
DataSet操作跟DataTabel操作基本是一样的，只是DataSet可以储存有多个表格，所以就多做介绍了

栏目列表