VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > 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可以储存有多个表格,所以就多做介绍了
相关教程