【C#】【ACCESS】ACCESSデータベースを使う

■ ACCESSデータベースを使う

https://blogs.yahoo.co.jp/dk521123/18975082.html

の前準備を行うこと

 * App.ConfigからConnectionStringを取得するために、
   参照の追加の[.Net]-[System.Configuration]を追加する必要がある
   (詳細は、参考資料を参考のこと)

■ 追加/INSERT

サンプル

private void button4_Click(object sender, EventArgs e)
{
    System.Data.OleDb.OleDbConnection conn = null;
    try
    {
        string connectString
            = System.Configuration.ConfigurationManager.ConnectionStrings[
            "SampleForLinq.Properties.Settings.testDbConnectionString"
            ].ConnectionString;
        conn = new System.Data.OleDb.OleDbConnection(
            connectString);
        conn.Open();

        System.Data.OleDb.OleDbCommand cmdInsert
            = new System.Data.OleDb.OleDbCommand();
        cmdInsert.CommandText =
            "INSERT INTO Person(Name, BirthDay, Address, Tel) VALUES ('Gaga', '1985/11/14', 'USA 2-2', 747464644)";
        cmdInsert.Connection = conn;

        int iRet = cmdInsert.ExecuteNonQuery();

        label1.Text = iRet + "件：成功";
    }
    catch (Exception)
    {
        label1.Text = "失敗";
    }
    finally
    {
        // DBの接続を閉じる
        if (conn != null)
        {
            conn.Close();
            conn.Dispose();
        }
    }

    testDbDataSet.PersonDataTable dt
        = new testDbDataSet.PersonDataTable();
    testDbDataSetTableAdapters.PersonTableAdapter ta
        = new testDbDataSetTableAdapters.PersonTableAdapter();

    ta.Fill(dt);

    dataGridView1.DataSource = dt;
}

■ 更新/UPDATE

サンプル

private void button2_Click(object sender, EventArgs e)
{
    System.Data.OleDb.OleDbConnection conn = null;
    try
    {
        string connectString
            = System.Configuration.ConfigurationManager.ConnectionStrings[
            "SampleForLinq.Properties.Settings.testDbConnectionString"
            ].ConnectionString;
        conn = new System.Data.OleDb.OleDbConnection(
            connectString);
        conn.Open();

        System.Data.OleDb.OleDbCommand cmdUpdate
            = new System.Data.OleDb.OleDbCommand();

        int id = Int32.Parse(textBox1.Text);

        cmdUpdate.CommandText =
            "UPDATE Person SET Name = 'Tommy', BirthDay = '1985/11/14', Address = 'USA 2-2', Tel = 747464644 WHERE ID="
            + id.ToString();
        cmdUpdate.Connection = conn;

        int iRet = cmdUpdate.ExecuteNonQuery();

        label1.Text = iRet + "件：成功";
    }
    catch (Exception)
    {
        label1.Text = "失敗";
    }
    finally
    {
        // DBの接続を閉じる
        if (conn != null)
        {
            conn.Close();
            conn.Dispose();
        }
    }

    testDbDataSet.PersonDataTable dt
        = new testDbDataSet.PersonDataTable();
    testDbDataSetTableAdapters.PersonTableAdapter ta
        = new testDbDataSetTableAdapters.PersonTableAdapter();

    ta.Fill(dt);

    dataGridView1.DataSource = dt;
}

■ 削除/DELETE

サンプル

private void button6_Click(object sender, EventArgs e)
{
    System.Data.OleDb.OleDbConnection conn = null;
    try
    {
        string connectString
            = System.Configuration.ConfigurationManager.ConnectionStrings[
            "SampleForLinq.Properties.Settings.testDbConnectionString"
            ].ConnectionString;
        conn = new System.Data.OleDb.OleDbConnection(
            connectString);
        conn.Open();

        System.Data.OleDb.OleDbCommand cmdUpdate
            = new System.Data.OleDb.OleDbCommand();

        int id = Int32.Parse(textBox1.Text);

        cmdUpdate.CommandText =
            "DELETE FROM Person WHERE ID="
            + id.ToString();
        cmdUpdate.Connection = conn;

        int iRet = cmdUpdate.ExecuteNonQuery();

        label1.Text = iRet + "件：成功";
    }
    catch (Exception)
    {
        label1.Text = "失敗";
    }
    finally
    {
        // DBの接続を閉じる
        if (conn != null)
        {
            conn.Close();
            conn.Dispose();
        }
    }

    testDbDataSet.PersonDataTable dt
        = new testDbDataSet.PersonDataTable();
    testDbDataSetTableAdapters.PersonTableAdapter ta
        = new testDbDataSetTableAdapters.PersonTableAdapter();

    ta.Fill(dt);

    dataGridView1.DataSource = dt;
}

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="SampleForLinq.Properties.Settings.testDbConnectionString"
            connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\testDb.mdb"
            providerName="System.Data.OleDb" />
    </connectionStrings>
</configuration>

参考資料

* app.configからのConnectionStringを取得
http://d.hatena.ne.jp/waritohutsu/20090115/1231992331
* 削除
http://itnandemolab.blog70.fc2.com/blog-entr

関連記事

ACCESS

ユーザから選択されたACCESSファイル(.mdb)を使う
https://blogs.yahoo.co.jp/dk521123/20090986.html