データベース接続!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よりもパフォーマンス、開発生産性、保守性に優れているため、こちらでの開発が推奨されるようです。
新しい項目の追加でデータセットを選択します。
データセットに適当な名前を付けて追加。
デザイナー上でTableAdapterを追加すると、構成ウィザードが起動します。
接続文字列の設定をして、次へ。
接続文字列のApp.Config上の名前を指定して、次へ。
SQLを選択して、次へ。
全件データセットへ流し込むクエリを書いて、詳細オプションボタンで全部チェックして、次へ。
FillメソッドとGetDataメソッドの名前はデフォルトのままで、次へ。
ウィザードによってTableAdapterが作成されるので、完了!
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を使用したほうがよさそうですね。