欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#数据库操作小结

程序员文章站 2023-12-03 15:36:40
1、常用的t-sql语句      查询:select * from tb_test where id='1' and n...
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断开式连接的核心,比较安全