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可以储存有多个表格，所以就多做介绍了
6、个人总结
      个人感觉，用 sqlcommand比较灵活，而dataset是实现ado.net断开式连接的核心，比较安全