読者です 読者をやめる 読者になる 読者になる

1.21 jigowatts

Great Scott!

データベース接続!ADO.NET(DataSet)でSQLServerに接続する?

概要

.NET Framework初期のテクノロジであるADO.NETの非接続型と呼ばれる方法でSQLServerに接続してみます。

ADO.NET 非接続型

DataSet(クライアントのインメモリオブジェクト)にデータを流し込む際にデータベースに接続し、終わると接続を切ります。データベースをメモリ内に展開する感じですね。
接続型と比べると最低限の接続で済むためサーバリソースは効率的に運用できる一方、更新処理はデータベースとの同期をとる必要があるためオーバーヘッドが発生します。
LINQとEntity Frameworkを使用するとより簡単に機能を実装することができるため、今は第一線から退きましたが、これらがリリースされるまでは主流でした。

SELECT
public List<WorkData> Select()
{
    using (SqlConnection con = new SqlConnection(
        System.Configuration.ConfigurationManager.ConnectionStrings["ADONetConnection"].ConnectionString))
    {
        SqlDataAdapter sqlda = new SqlDataAdapter("Select * from dbo.T_Work", con);

        DataSet ds = new DataSet();
        sqlda.Fill(ds, "Works");
        List<WorkData> list = new List<WorkData>();
        foreach (DataRow item in ds.Tables["Works"].Rows)
        {
            var rec = new WorkData();
            rec.ID = item["ID"] as int? ?? 0;
            rec.ProductCode = item["ProductCode"] as string;
            rec.ProductName = item["ProductName"] as string;
            rec.CategoryNo = item["CategoryNo"] as string;
            rec.Type = item["Type"] as string;
            list.Add(rec);
        }
        return list;
    }
}
INSERT
public void Insert(WorkData data) 
{
    using (SqlConnection con = new SqlConnection(
        System.Configuration.ConfigurationManager.ConnectionStrings["ADONetConnection"].ConnectionString))
    {
        SqlDataAdapter sqlda = new SqlDataAdapter("Select * from dbo.T_Work", con);                
        DataSet ds = new DataSet();
        sqlda.Fill(ds, "Works");
        ds.Tables["Works"].Rows.Add(data.ID, data.ProductCode, data.ProductName, data.CategoryNo, data.Type);

        string insertStr
            = "Insert into T_Work (ProductCode, ProductName,CategoryNo,Type) Values (@ProductCode, @ProductName,@CategoryNo,@Type)";

        SqlCommand insertCmd = new SqlCommand(insertStr, con);
        SqlParameter p1 = new SqlParameter();
        p1.ParameterName = "@ProductCode";
        p1.SourceColumn = "ProductCode";
        insertCmd.Parameters.Add(p1);               
        SqlParameter p2 = new SqlParameter();
        p2.ParameterName = "@ProductName";
        p2.SourceColumn = "ProductName";
        insertCmd.Parameters.Add(p2);
        SqlParameter p3 = new SqlParameter();
        p3.ParameterName = "@CategoryNo";
        p3.SourceColumn = "CategoryNo";
        insertCmd.Parameters.Add(p3);
        SqlParameter p4 = new SqlParameter();
        p4.ParameterName = "@Type";
        p4.SourceColumn = "Type";
        insertCmd.Parameters.Add(p4);

        sqlda.InsertCommand = insertCmd;
        sqlda.Update(ds, "Works");
    }        
}
UPDATE
public void Update(WorkData data)
{
    using (SqlConnection con = new SqlConnection(
        System.Configuration.ConfigurationManager.ConnectionStrings["ADONetConnection"].ConnectionString))
    {
        SqlDataAdapter sqlda = new SqlDataAdapter("Select * from dbo.T_Work", con);
        DataSet ds = new DataSet();
        sqlda.Fill(ds, "Works");
        var dt = ds.Tables["Works"];
        dt.PrimaryKey = new DataColumn[] { dt.Columns["ID"] };
        var r = dt.Rows.Find(data.ID);
        r["ProductCode"] = data.ProductCode;
        r["ProductName"] = data.ProductName;
        r["CategoryNo"] = data.CategoryNo;
        r["Type"] = data.Type;
        string updateStr
            = "Update T_Work  Set ProductCode=@ProductCode, ProductName=@ProductName,CategoryNo=@CategoryNo,Type=@Type Where ID=@Id";

        SqlCommand updateCmd = new SqlCommand(updateStr, con);
        SqlParameter p1 = new SqlParameter();
        p1.ParameterName = "@ProductCode";
        p1.SourceColumn = "ProductCode";
        updateCmd.Parameters.Add(p1);
        SqlParameter p2 = new SqlParameter();
        p2.ParameterName = "@ProductName";
        p2.SourceColumn = "ProductName";
        updateCmd.Parameters.Add(p2);
        SqlParameter p3 = new SqlParameter();
        p3.ParameterName = "@CategoryNo";
        p3.SourceColumn = "CategoryNo";
        updateCmd.Parameters.Add(p3);
        SqlParameter p4 = new SqlParameter();
        p4.ParameterName = "@Type";
        p4.SourceColumn = "Type";
        updateCmd.Parameters.Add(p4);
        SqlParameter p5 = new SqlParameter();
        p5.ParameterName = "@Id";
        p5.SourceColumn = "ID";
        updateCmd.Parameters.Add(p5);

        sqlda.UpdateCommand = updateCmd;
        sqlda.Update(ds, "Works");
    }
}
DELETE
public void Delete(int id) 
{
    using (SqlConnection con = new SqlConnection(
        System.Configuration.ConfigurationManager.ConnectionStrings["ADONetConnection"].ConnectionString))
    {
        SqlDataAdapter sqlda = new SqlDataAdapter("Select * from dbo.T_Work", con);
        DataSet ds = new DataSet();
        sqlda.Fill(ds, "Works");
        var dt = ds.Tables["Works"];
        dt.PrimaryKey = new DataColumn[] { dt.Columns["ID"] };
        var r = dt.Rows.Find(id);
        r.Delete();

        string updateStr = "Delete from T_Work  Where ID=@Id";

        SqlCommand deleteCmd = new SqlCommand(updateStr, con);
        SqlParameter p1 = new SqlParameter();
        p1.ParameterName = "@Id";
        p1.SourceColumn = "ID";
        deleteCmd.Parameters.Add(p1);

        sqlda.DeleteCommand = deleteCmd;
        sqlda.Update(ds, "Works");
    }
}
接続文字列はApp/Web.configに設定
<configuration>
  <connectionStrings>
    <add name="ADONetConnection" connectionString="server=(localdb)\v11.0;database=LocalDB;Integrated Security=True"/>
  </connectionStrings>
</configuration>

厳密に型付けされたDataSet

厳密に型付けされたDataSetを使用するとコード量を減らすことができます。
また、一般的に通常のDataSetよりもパフォーマンス、開発生産性、保守性に優れているため、こちらでの開発が推奨されるようです。


新しい項目の追加でデータセットを選択します。
データセットに適当な名前を付けて追加。
f:id:sh_yoshida:20150204224620p:plain
デザイナー上でTableAdapterを追加すると、構成ウィザードが起動します。
接続文字列の設定をして、次へ。
f:id:sh_yoshida:20150204224636p:plain
接続文字列のApp.Config上の名前を指定して、次へ。
f:id:sh_yoshida:20150204224645p:plain
SQLを選択して、次へ。
f:id:sh_yoshida:20150204224650p:plain
全件データセットへ流し込むクエリを書いて、詳細オプションボタンで全部チェックして、次へ。
f:id:sh_yoshida:20150204224655p:plain
FillメソッドとGetDataメソッドの名前はデフォルトのままで、次へ。
f:id:sh_yoshida:20150204224701p:plain
ウィザードによってTableAdapterが作成されるので、完了!
f:id:sh_yoshida:20150204224706p:plain

SELECT
public List<WorkData> Select() 
{
    SampleDataSet ds = new SampleDataSet();
    T_WorkTableAdapter ta = new T_WorkTableAdapter();
    ta.Fill(ds.T_Work);

    List<WorkData> list = new List<WorkData>();
    foreach (SampleDataSet.T_WorkRow row in ds.T_Work)
    {
        var rec = new WorkData();
        rec.ID = row.ID;
        rec.ProductCode = row.IsProductCodeNull() ? string.Empty : row.ProductCode;
        rec.ProductName = row.IsProductNameNull() ? string.Empty : row.ProductName;
        rec.CategoryNo = row.IsCategoryNoNull() ? string.Empty : row.CategoryNo;
        rec.Type = row.IsTypeNull() ? string.Empty : row.Type;
        list.Add(rec);
    }
    return list;
}
INSERT
public void Insert(WorkData data) 
{
    SampleDataSet ds = new SampleDataSet();
    T_WorkTableAdapter ta = new T_WorkTableAdapter();
    ta.Fill(ds.T_Work);

    SampleDataSet.T_WorkRow row = ds.T_Work.NewT_WorkRow();
    row.ProductCode = data.ProductCode;
    row.ProductName = data.ProductName;
    row.CategoryNo = data.CategoryNo;
    row.Type = data.Type;
    ds.T_Work.AddT_WorkRow(row);

    ta.Update(ds.T_Work);
}
UPDATE
public void Update(WorkData data)
{
    SampleDataSet ds = new SampleDataSet();
    T_WorkTableAdapter ta = new T_WorkTableAdapter();
    ta.Fill(ds.T_Work);

    var row = ds.T_Work.FindByID(data.ID);
    row.ProductCode = data.ProductCode;
    row.ProductName = data.ProductName;
    row.CategoryNo = data.CategoryNo;
    row.Type = data.Type;

    ta.Update(ds.T_Work);
}
DELETE
public void Delete(int id) 
{
    SampleDataSet ds = new SampleDataSet();
    T_WorkTableAdapter ta = new T_WorkTableAdapter();
    ta.Fill(ds.T_Work);

    var row = ds.T_Work.FindByID(id);
    row.Delete();

    ta.Update(ds.T_Work);
}

大量データには不向き?

インメモリデータベースという特性上、データベースに登録された大量のデータを一度に取得、処理しようとするとメモリ不足に。。。職場の環境では250万件程度でOutOfMemoryExceptionが発生して落ちました。大量データを保有するテーブルを扱う際は、一度に取得しないようFillメソッドでデータ量を調整するようなクエリを組むなど工夫をするか、DataReaderを使用したほうがよさそうですね。