C#使用Ado.Net读写数据库

1.使用DataReader方式读取资料
 

String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;

 //如果数据库连接字符串有加密,可以经过解密后重新获得连接字符串
 DbConnectionStringBuilder connBuilder = new DbConnectionStringBuilder();
 connBuilder.ConnectionString = connString;
 connBuilder["Data Source"] = connBuilder["Data Source"];  //可加上解密方法
 connBuilder["User ID"] = connBuilder["User ID"];          //可加上解密方法
 connBuilder["Password"] = connBuilder["Password"];        //可加上解密方法
 connString = connBuilder.ConnectionString;

 using (OleDbConnection conn = new OleDbConnection(connString))
 {
     try
     {
         conn.Open();

         using (OleDbCommand cmd = new OleDbCommand())
         {
             cmd.CommandText = "select * from s_userm where rownum <= ?";
             cmd.CommandType = CommandType.Text;
             cmd.Connection = conn;
             cmd.Parameters.Add("?", OleDbType.Integer).Value = 5;  //cmd.Parameters.Add(new OleDbParameter("?", 5));

             using (OleDbDataReader reader = cmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     MessageBox.Show(reader.GetString(1), "资料");
                 }
             }
         }
         conn.Close();
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "error");
         return;
     }
 }

 

2.使用DataAdapter与DataSet方式读取资料.
 

String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;
 using (OleDbConnection conn = new OleDbConnection(connString))
 {
     try
     {
         using (OleDbCommand cmd = new OleDbCommand())
         {
             cmd.CommandText = "select * from s_userm where rownum <= ?";
             cmd.CommandType = CommandType.Text;
             cmd.Connection = conn;
             cmd.Parameters.Add("?", OleDbType.Integer).Value = 5;  //cmd.Parameters.Add(new OleDbParameter("?", 5));

             DataSet ds = new DataSet();
             OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

             adapter.Fill(ds, "s_userm");

             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 MessageBox.Show(Convert.ToString(dr["user_nm"]), "资料");
             }

             //listBox1.DataSource = ds.Tables[0].DefaultView;
             //listBox1.DisplayMember = "user_nm";
             //listBox1.ValueMember = "user_no";
             listBox1.DataSource = ds;
             listBox1.DisplayMember = "s_userm.user_nm";
             listBox1.ValueMember = "s_userm.user_no";

             dataGridView1.DataSource = ds.Tables[0].DefaultView;
             dataGridView1.ReadOnly = true;
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "error");
         return;
     }
 }

 

3.使用DataAdapter与DataSet对单表的增删改查，根据Command的查询语法，使用CommandBuilder自动生成增删改的语法.

String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;

using (OleDbConnection conn = new OleDbConnection(connString))
{
    OleDbCommand cmd = new OleDbCommand();
    cmd.CommandText = "select * from s_userm where rownum <= ?";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = conn;
    cmd.Parameters.Add("?", OleDbType.Integer).Value = 5;
    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

    //使用DataAdapter更新单表，可以使用CommandBuilder来自动生成InsertCommand,UpdateCommand,DeleteCommand
    OleDbCommandBuilder cmdbuilder = new OleDbCommandBuilder(adapter); //将自动生成InsertCommand,UpdateCommand,DeleteCommand
    MessageBox.Show(cmdbuilder.GetUpdateCommand().CommandText, "updcommand");

    //select
    DataSet ds = new DataSet();
    adapter.Fill(ds,"s_userm");
    dataGridView1.DataSource = ds.Tables["s_userm"].DefaultView;

    //insert
    DataRow updateRow = ds.Tables["s_userm"].NewRow();
    updateRow["user_no"] = "TEST1";
    updateRow["user_nm"] = "test1";
    ds.Tables["s_userm"].Rows.Add(updateRow);
    adapter.Update(ds, "s_userm");

    MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[0]["user_no"]), "user");

    //update
    ds.Tables[0].Rows[1]["EMAIL"] = "gymsoft@163.com";
    adapter.Update(ds, "s_userm");

    //delete
    ds.Tables["s_userm"].Rows[0].Delete();
    adapter.Update(ds, "s_userm");
}

 

4.使用DataAdapter与DataSet进行增删改查，手动指定增删改查的语法。
 

String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;

 using (OleDbConnection conn = new OleDbConnection(connString))
 {
     DataSet ds = new DataSet();
     OleDbDataAdapter adapter = new OleDbDataAdapter();

     //指定DataAdapter的select语句
     OleDbCommand selectCmd = new OleDbCommand();
     selectCmd.CommandText = "select * from s_userm where rownum <= ?";
     selectCmd.CommandType = CommandType.Text;
     selectCmd.Connection = conn;
     selectCmd.Parameters.Add("?", OleDbType.Integer).Value = 5;
     adapter.SelectCommand = selectCmd;

     //指定DataAdapter的Insert语句
     OleDbCommand insertCmd = new OleDbCommand();
     insertCmd.CommandText = "insert into s_userm(user_no,user_nm) values(?,?)";
     insertCmd.CommandType = CommandType.Text;
     insertCmd.Connection = conn;
     insertCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     insertCmd.Parameters.Add("?", OleDbType.VarChar, 80, "user_nm");
     adapter.InsertCommand = insertCmd;

     //指定DataAdapter的Update语句
     OleDbCommand updateCmd = new OleDbCommand();
     updateCmd.CommandText = "update s_userm set email = ? where user_no = ?";
     updateCmd.CommandType = CommandType.Text;
     updateCmd.Connection = conn;
     updateCmd.Parameters.Add("?", OleDbType.VarChar, 50, "email");
     OleDbParameter parmUpdPk = updateCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     parmUpdPk.SourceVersion = DataRowVersion.Original;
     adapter.UpdateCommand = updateCmd;

     //指定DataAdapter的Delete语句
     OleDbCommand deleteCmd = new OleDbCommand();
     deleteCmd.CommandText = "delete from s_userm where user_no = ?";
     deleteCmd.CommandType = CommandType.Text;
     deleteCmd.Connection = conn;
     OleDbParameter parmDelPk = deleteCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     parmDelPk.SourceVersion = DataRowVersion.Original;
     adapter.DeleteCommand = deleteCmd;

     MessageBox.Show(adapter.SelectCommand.CommandText, "command");

     //select
     adapter.Fill(ds, "s_userm");
     dataGridView1.DataSource = ds.Tables["s_userm"].DefaultView;

     //insert
     DataRow updateRow = ds.Tables["s_userm"].NewRow();
     updateRow["user_no"] = "TEST2";
     updateRow["user_nm"] = "test2";
     ds.Tables["s_userm"].Rows.Add(updateRow);
     adapter.Update(ds, "s_userm");

     MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[3]["user_no"]), "user");

     //update
     ds.Tables[0].Rows[3]["EMAIL"] = "gymsoft@163.com";
     adapter.Update(ds, "s_userm");

     //delete
     ds.Tables["s_userm"].Rows[3].Delete();
     adapter.Update(ds, "s_userm");
 }

 

5.使用DataGridView绑定数据源进行编辑，使用DataAdapter与DataSet进行增删改查，手动指定增删改查的语法。
 

public partial class Form1 : Form
 {
     String connString;
     OleDbConnection conn;
     DataSet ds;
     OleDbDataAdapter adapter;

     public Form1()
     {
         InitializeComponent();
     }

     private void buttonQuery_Click(object sender, EventArgs e)
     {
         connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;
         conn = new OleDbConnection(connString);

         ds = new DataSet();
         adapter = new OleDbDataAdapter();

         //指定DataAdapter的select语句
         OleDbCommand selectCmd = new OleDbCommand();
         selectCmd.CommandText = "select * from s_userm where rownum <= ?";
         selectCmd.CommandType = CommandType.Text;
         selectCmd.Connection = conn;
         selectCmd.Parameters.Add("?", OleDbType.Integer).Value = 5;
         adapter.SelectCommand = selectCmd;

         //指定DataAdapter的Insert语句
         OleDbCommand insertCmd = new OleDbCommand();
         insertCmd.CommandText = "insert into s_userm(user_no,user_nm) values(?,?)";
         insertCmd.CommandType = CommandType.Text;
         insertCmd.Connection = conn;
         insertCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
         insertCmd.Parameters.Add("?", OleDbType.VarChar, 80, "user_nm");
         adapter.InsertCommand = insertCmd;

         //指定DataAdapter的Update语句
         OleDbCommand updateCmd = new OleDbCommand();
         updateCmd.CommandText = "update s_userm set email = ? where user_no = ?";
         updateCmd.CommandType = CommandType.Text;
         updateCmd.Connection = conn;
         updateCmd.Parameters.Add("?", OleDbType.VarChar, 50, "email");
         OleDbParameter parmUpdPk = updateCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
         parmUpdPk.SourceVersion = DataRowVersion.Original;
         adapter.UpdateCommand = updateCmd;

         //指定DataAdapter的Delete语句
         OleDbCommand deleteCmd = new OleDbCommand();
         deleteCmd.CommandText = "delete from s_userm where user_no = ?";
         deleteCmd.CommandType = CommandType.Text;
         deleteCmd.Connection = conn;
         OleDbParameter parmDelPk = deleteCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
         parmDelPk.SourceVersion = DataRowVersion.Original;
         adapter.DeleteCommand = deleteCmd;

         //MessageBox.Show(adapter.SelectCommand.CommandText, "command");

         //select
         adapter.Fill(ds, "s_userm");
         dataGridView1.DataSource = ds.Tables["s_userm"].DefaultView;
     }

     private void buttonSave_Click(object sender, EventArgs e)
     {
         adapter.Update(ds, "s_userm");

         ds.Clear();
         adapter.Fill(ds, "s_userm");
         dataGridView1.DataSource = ds.Tables["s_userm"].DefaultView;
     }
 }

 

6.使用DataAdapter与DataSet，以事务的方式进行增删改查。
 

String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;

 using (OleDbConnection conn = new OleDbConnection(connString))
 {
     DataSet ds = new DataSet();
     OleDbDataAdapter adapter = new OleDbDataAdapter();

     //指定DataAdapter的select语句
     OleDbCommand selectCmd = new OleDbCommand();
     selectCmd.CommandText = "select * from s_userm where rownum <= ?";
     selectCmd.CommandType = CommandType.Text;
     selectCmd.Connection = conn;
     selectCmd.Parameters.Add("?", OleDbType.Integer).Value = 5;
     adapter.SelectCommand = selectCmd;

     //指定DataAdapter的Insert语句
     OleDbCommand insertCmd = new OleDbCommand();
     insertCmd.CommandText = "insert into s_userm(user_no,user_nm) values(?,?)";
     insertCmd.CommandType = CommandType.Text;
     insertCmd.Connection = conn;
     insertCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     insertCmd.Parameters.Add("?", OleDbType.VarChar, 80, "user_nm");
     adapter.InsertCommand = insertCmd;

     //指定DataAdapter的Update语句
     OleDbCommand updateCmd = new OleDbCommand();
     updateCmd.CommandText = "update s_userm set email = ? where user_no = ?";
     updateCmd.CommandType = CommandType.Text;
     updateCmd.Connection = conn;
     updateCmd.Parameters.Add("?", OleDbType.VarChar, 50, "email");
     OleDbParameter parmUpdPk = updateCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     parmUpdPk.SourceVersion = DataRowVersion.Original;
     adapter.UpdateCommand = updateCmd;

     //指定DataAdapter的Delete语句
     OleDbCommand deleteCmd = new OleDbCommand();
     deleteCmd.CommandText = "delete from s_userm where user_no = ?";
     deleteCmd.CommandType = CommandType.Text;
     deleteCmd.Connection = conn;
     OleDbParameter parmDelPk = deleteCmd.Parameters.Add("?", OleDbType.VarChar, 30, "user_no");
     parmDelPk.SourceVersion = DataRowVersion.Original;
     adapter.DeleteCommand = deleteCmd;

     //MessageBox.Show(adapter.SelectCommand.CommandText, "command");

     conn.Open();
     using (OleDbTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
     {
         adapter.SelectCommand.Transaction = tran;
         adapter.InsertCommand.Transaction = tran;
         adapter.UpdateCommand.Transaction = tran;
         adapter.DeleteCommand.Transaction = tran;

         try
         {
             //select
             adapter.Fill(ds, "s_userm");
             dataGridView1.DataSource = ds.Tables["s_userm"].DefaultView;

             //insert
             DataRow updateRow = ds.Tables["s_userm"].NewRow();
             updateRow["user_no"] = "TEST2";
             updateRow["user_nm"] = "test2";
             ds.Tables["s_userm"].Rows.Add(updateRow);
             adapter.Update(ds, "s_userm");

             //MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[3]["user_no"]), "user");

             //update
             ds.Tables[0].Rows[3]["EMAIL"] = "gymsoft@163.com";
             adapter.Update(ds, "s_userm");

             //delete
             ds.Tables["s_userm"].Rows[3].Delete();
             adapter.Update(ds, "s_userm");

             tran.Commit();
         }
         catch (Exception ex)
         {
             try
             {
                 tran.Rollback();
             }
             catch (Exception exc)
             {
                 MessageBox.Show(exc.Message, "error");
             }
             MessageBox.Show(ex.Message, "error");
         }
     }
     conn.Close();
 }