lzg

2308 积分	0 好友	259 主题

发消息

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

楼主

发表于 2015-11-30 21:02:50 | 查看: 3794| 回复: 0

记性不好，记几个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();
         }

回复 支持0 反对0

收藏0 回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			立即注册