C# Access(accdb) 接続 – テックメモ

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.Security.Cryptography;
using System.Data.OleDb;

namespace PersonalManager
{
    public partial class Form1 : Form
    {
        private string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + @"\dat.accdb;" + "Jet OLEDB:Database Password=xxx";

        public Form1()
        {
            InitializeComponent();

            button1.Click += (s, e) =>
            {
                ExecuteNonQuery("create table employee(" +
                    "id autoincrement primary key," +
                    "name text" +
                    ")");

                ExecuteNonQuery("insert into employee (name) values ('taro')");

                DataTable dt = ExecuteQuery("select * from employee");

                List<Model> models = new List<Model>();
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    models.Add(new Model()
                    {
                        id = dt.Rows[r]["id"].ToString(),
                        name = dt.Rows[r]["name"].ToString()
                    });
                }
                dataGridView1.DataSource = models;
            };
        }

        private void ExecuteNonQuery(string commandText)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    try
                    {
                        cmd.Connection = con;
                        cmd.CommandText = commandText;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
        }

        private void ExecuteNonQuery(List<string> commandTextList)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Transaction = con.BeginTransaction();

                    try
                    {
                        foreach (string sql in commandTextList)
                        {
                            cmd.Connection = con;
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                        cmd.Transaction.Commit();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                        cmd.Transaction.Rollback();
                    }
                }
            }
        }

        private DataTable ExecuteQuery(string commandText)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                try
                {
                    con.Open();
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandText = commandText;

                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable();
                            dt.Load(reader);
                            return dt;
                        }
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return new DataTable();
                }
            }
        }
    }

    class Model
    {
        public string id { get; set; }
        public string name { get; set; }

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Security.Cryptography;

using System.Data.OleDb;

namespace PersonalManager

{

public partial class Form1 : Form

{

private string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + @"\dat.accdb;" + "Jet OLEDB:Database Password=xxx";

public Form1()

{

InitializeComponent();

button1.Click += (s, e) =>

{

ExecuteNonQuery("create table employee(" +

"id autoincrement primary key," +

"name text" +

")");

ExecuteNonQuery("insert into employee (name) values ('taro')");

DataTable dt = ExecuteQuery("select * from employee");

List<Model> models = new List<Model>();

for (int r = 0; r < dt.Rows.Count; r++)

{

models.Add(new Model()

{

id = dt.Rows[r]["id"].ToString(),

name = dt.Rows[r]["name"].ToString()

});

}

dataGridView1.DataSource = models;

};

}

private void ExecuteNonQuery(string commandText)

{

using (OleDbConnection con = new OleDbConnection(connectionString))

{

con.Open();

using (OleDbCommand cmd = new OleDbCommand())

{

try

{

cmd.Connection = con;

cmd.CommandText = commandText;

cmd.ExecuteNonQuery();

}

catch (Exception e)

{

MessageBox.Show(e.Message);

}

private void ExecuteNonQuery(List<string> commandTextList)

{

using (OleDbConnection con = new OleDbConnection(connectionString))

{

con.Open();

using (OleDbCommand cmd = new OleDbCommand())

{

cmd.Transaction = con.BeginTransaction();

try

{

foreach (string sql in commandTextList)

{

cmd.Connection = con;

cmd.CommandText = sql;

cmd.ExecuteNonQuery();

}

cmd.Transaction.Commit();

}

catch (Exception e)

{

MessageBox.Show(e.Message);

cmd.Transaction.Rollback();

}

private DataTable ExecuteQuery(string commandText)

{

using (OleDbConnection con = new OleDbConnection(connectionString))

{

try

{

con.Open();

using (OleDbCommand cmd = new OleDbCommand())

{

cmd.Connection = con;

cmd.CommandText = commandText;

using (OleDbDataReader reader = cmd.ExecuteReader())

{

DataTable dt = new DataTable();

dt.Load(reader);

return dt;

}

catch (Exception e)

{

MessageBox.Show(e.Message);

return new DataTable();

}

class Model

{

public string id { get; set; }

public string name { get; set; }

}