C⻠はAdo.Netを使ってデータベースを読み書きします.

C⻠はAdo.Netを使ってデータベースを読み書きします.
    覚えが悪くて、いくつかのC〓〓を覚えてAdo.Netを使ってデータベースの方式を読んで書いて、いつでも使うためです.
1.データリーダー方式で資料を読み取る 

           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.Data Adapterと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.Data AdapterとDataSetを使って単表の添削を調べ、Commandの検索文法によって、Command Buiderを使って自動的に添削を加える文法を生成する.

            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"] = "[email protected]";
                adapter.Update(ds, "s_userm");

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

 
4.Data Adapterと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"] = "[email protected]";
                adapter.Update(ds, "s_userm");

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

 
5.DataGridViewバインディングデータソースを使って編集し、Data Adapterと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.Data Adapterと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"] = "[email protected]";
                        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();
            }