まるまるこふこふ

数々の次元が崩壊し、全ての生命が塵と化すのを見てきた。私ほどの闇の心の持ち主でも、そこには何の喜びも無かった。

C#.NETでのSQL接続方法

おっはー、さいです。
C#.NETでSQLに接続したかったんだけど、MVCの観点から、データベース接続部分だけでクラス１つ作りたかったので、テンプレみたいなモノ作りました。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data; //DataTable用
using System.Data.SqlClient; //データベース接続関係

namespace WindowsFormsApplication1
{
    class MyModel
    {
        //データベース接続のための情報を入れましょう
        private const string DATABASE_SERVER = "";
        private const string DATABASE_NAME = "";
        private const string DATABASE_USER = "";
        private const string DATABASE_PASSWORD = "";

        private SqlConnection sqlCon;
        private SqlTransaction sqlTran;

        public MyModel()
        {
            // 接続文字列を生成する
            string connectString =
            "Data Source = " + DATABASE_SERVER
            + ";Initial Catalog = " + DATABASE_NAME
            + ";User ID = " + DATABASE_USER
            + ";Password = " + DATABASE_PASSWORD;

            // SqlConnection の新しいインスタンスを生成する (接続文字列を指定)
            this.sqlCon = new System.Data.SqlClient.SqlConnection(connectString);

            // データベース接続を開く
            this.sqlCon.Open();


            //トランザクションの開始
            this.sqlTran = this.sqlCon.BeginTransaction();
        }

        public void Close()
        {
            //トランザクションをコミット
            if (this.sqlTran.Connection != null)
            {
                this.sqlTran.Commit();
                this.sqlTran.Dispose();
            }

            // データベース接続を閉じる 
            this.sqlCon.Close();
            this.sqlCon.Dispose();
        }


        public DataTable ReadTable1()
        {
            //クエリーの生成
            SqlCommand sqlCom = new SqlCommand();

            //クエリー送信先及びトランザクションの指定
            sqlCom.Connection = this.sqlCon;
            sqlCom.Transaction = this.sqlTran;

            //クエリー文の指定
            sqlCom.CommandText = "SELECT * FROM テーブル名;";

            //データテーブルを作成するためのアダプタ
            SqlDataAdapter sqlAda = new SqlDataAdapter();
            sqlAda.SelectCommand = sqlCom;


            //dsにテーブルデータを代入
            DataTable ds = new DataTable();
            sqlAda.Fill(ds);

            //DataTableにも主キーを設定する
            ds.PrimaryKey = new DataColumn[] { ds.Columns["EMP_CODE"] };

            return ds;
        }



        public void UpdateTable1(DataTable ds)
        {
            //コマンド作成
            SqlCommand sqlCom = new SqlCommand();
            sqlCom.Connection = this.sqlCon;
            sqlCom.Transaction = this.sqlTran;

            //ReadTable1と同じクエリー文を設定
            sqlCom.CommandText = "SELECT * FROM テーブル名;";

            //データテーブルを作成するためのアダプタ
            SqlDataAdapter sqlAda = new SqlDataAdapter();
            sqlAda.SelectCommand = sqlCom;

            //アップデート用のコマンド取得
            SqlCommandBuilder builder = new SqlCommandBuilder(sqlAda);
            builder.GetUpdateCommand();

            sqlAda.Update(ds);
        }

        public void UpdateTable1BySelf(DataTable ds)
        {
            StringBuilder query = new StringBuilder();

            foreach (DataRow r in ds.Rows)
            {
                switch (r.RowState)
                {
                    case DataRowState.Added:        // 追加された状態
                        query.Append("insert into テーブル名 VALUES(");
                        query.Append("'" + r["EMP_CODE"] + "', ");
                        query.Append("'" + r["EMP_NAME"] + "', ");
                        query.Append("'" + r["EMP_NAME_K"] + "', ");
                        query.Append("'" + r["EMP_MAIL"] + "', ");
                        query.Append("'" + r["EMP_POST"] + "'");
                        query.Append(");");

                        break;

                    case DataRowState.Deleted:      // 削除された状態
                        query.Append("delete from テーブル名 where 主キー = '" + r["EMP_CODE", DataRowVersion.Original] + "';");
                        break;

                    case DataRowState.Modified:     // 変更された状態
                        query.Append("update テーブル名 set ");
                        query.Append("EMP_NAME = '" + r["EMP_NAME"] + "', ");
                        query.Append("EMP_NAME_K = '" + r["EMP_NAME_K"] + "', ");
                        query.Append("EMP_MAIL = '" + r["EMP_MAIL"] + "', ");
                        query.Append("EMP_POST = '" + r["EMP_POST"] + "' ");
                        query.Append("where EMP_CODE = '" + r["EMP_CODE"] + "';");

                        break;

                    case DataRowState.Unchanged:    // 変更なしの状態
                        break;

                    case DataRowState.Detached:     // 作成直後で、DataTableに追加されていない状態
                        break;
                }

            }

            //クエリー送信
            SqlCommand sqlCom = new SqlCommand();

            sqlCom.Connection = this.sqlCon;
            sqlCom.Transaction = this.sqlTran;

            sqlCom.CommandText = query.ToString();
            sqlCom.ExecuteNonQuery();
        }

        public void RollBack() {
            this.sqlTran.Rollback();
            this.sqlTran.Dispose();
        }


    }
}

UpdateTable1BySelfメソッドですが、ReadTableする時に、クエリーで表結合するクエリーを送ると、sqlAdapterのUpdateメソッドで、DataTableから手軽にデータベースをアップロードできないので、その場合は自作のUpdateメソッドを作って更新する必要があります。


ADO.NET入門記-010 (CommandBuilderの使用(Insert,Update,Delete) (CommandBuilder, DataAdapter, Fill, Update)) - いろいろ備忘録日記

CommandBuilderが各変更系のコマンドを自動生成するには、以下の条件を満たさないといけません。

1. クエリがただ一つのテーブルからデータを取得するようになっていること。


selectするテーブルがテーブル一つなら、UpdateTable1BySelfは要らないし、テーブルを結合させて読み込むなら
UpdateTable1の方は要りません。


今回の例ではテーブル接続先を一つにしてますが、接続先テーブルを増やすなら、ReadTable1と、UpdateTable1もしくはUpdateTable1BySelfを増やしてあげてください～。

以下、使い方。

//表を読み込むだけ
MyModel con1 = new MyModel();
try{
    DataTable datatable1 = con1.ReadTable1();

    //DataGridViewにバインドする
    dataGridView1.DataSource = datatable1;
    con1.Close();
}catch(Exception ex){
    con1.RollBack();
    con1.Close(); //Closeを忘れない
 }

//一度の接続で表を二つ読み出す
MyModel con2 = new MyModel();
try {
    DataTable datatable1 = con2.ReadTable1();
    DataTable datatable2 = con2.ReadTable2(); //一度の接続で二つテーブルを読み出せる
                                                          //ReadTable2は自分で実装してね
    con2.Close();

}catch(Exception ex){
    con2.RollBack();
    con2.Close(); //Closeを忘れない
}

//表更新
MyModel con3 = new MyModel();
try
{
    DataTable datatable1 = con3.ReadTable1();

    //DataTableの主キー0005のEMP_NAMEをUpdate
    datatable1.Rows.Find("0005")["EMP_NAME"] = "テスト太郎";
    con.UpdateTable1(datatable1);

    con3.Close();
}catch(Exception ex){
    con3.RollBack();
    con3.Close(); //Closeを忘れない
}


課題

  • Disposeを実装して、using文で接続できるようにしたい！
  • SqlAdapterのUpdateメソッドを使うとき、SqlAdapterにまたSELECT文を指定するのオシャレじゃない!