Windows アプリケーションでDBを更新してみよう（VS2003 DataAdapter 編）
Windows アプリケーションでDBのデータを表示しよう（VS2003 DataAdapter 編）をちょっと改造して
データの更新をしてみましょう。
※解説に用いた Visual Studio 2003 Professional Edition です。
サンプルのダウンロード
■Form 上のコントロールをいじる
Form1 をデザイナで開くと↓こんなんでしたよね。
それをこんなんに変えます。
やり方は、DataGrid の Dock プロパティを Fill から None に変えて、高さを適宜変更。
Button をツール ボックスより配置します。
■コードを入力
ソリューション エクスプローラ（表示されていない場合は、メニューバーの[表示]→[ソリューション エクスプローラ]をクリックして表示させてくださいね）の
Form を 右クリックして、コードエディタを表示させて、以前との違いを確認してみてください。
Form1.vb
Public Class Form1 Inherits System.Windows.Forms.Form #Region " Windows フォーム デザイナで生成されたコード " Public Sub New() MyBase.New() ' この呼び出しは Windows フォーム デザイナで必要です。 InitializeComponent() ' InitializeComponent() 呼び出しの後に初期化を追加します。 End Sub ' Form は、コンポーネント一覧に後処理を実行するために dispose をオーバーライドします。 Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub ' Windows フォーム デザイナで必要です。 Private components As System.ComponentModel.IContainer ' メモ : 以下のプロシージャは、Windows フォーム デザイナで必要です。 'Windows フォーム デザイナを使って変更してください。 ' コード エディタを使って変更しないでください。 Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Friend WithEvents Customers1 As MyWinDBApp01.Customers Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Button1 As System.Windows.Forms.Button <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.Customers1 = New MyWinDBApp01.Customers Me.DataGrid1 = New System.Windows.Forms.DataGrid CType(Me.Customers1, System.ComponentModel.ISupportInitialize).BeginInit() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'SqlDataAdapter1 ' Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1 Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1 Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1 Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() { _ New System.Data.Common.DataTableMapping("Table", "Customers", _ New System.Data.Common.DataColumnMapping() { _ New System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), _ New System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"), _ New System.Data.Common.DataColumnMapping("ContactName", "ContactName"), _ New System.Data.Common.DataColumnMapping("ContactTitle", "ContactTitle"), _ New System.Data.Common.DataColumnMapping("Address", "Address"), _ New System.Data.Common.DataColumnMapping("City", "City"), _ New System.Data.Common.DataColumnMapping("Region", "Region"), _ New System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"), _ New System.Data.Common.DataColumnMapping("Country", "Country"), _ New System.Data.Common.DataColumnMapping("Phone", "Phone"), _ New System.Data.Common.DataColumnMapping("Fax", "Fax")})}) Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1 ' 'SqlSelectCommand1 ' Me.SqlSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region," & _ " PostalCode, Country, Phone, Fax FROM Customers" Me.SqlSelectCommand1.Connection = Me.SqlConnection1 ' 'SqlInsertCommand1 ' Me.SqlInsertCommand1.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address" & _ ", City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerID, @CompanyNa" & _ "me, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country" & _ ", @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Addr" & _ "ess, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (Custome" & _ "rID = @CustomerID)" Me.SqlInsertCommand1.Connection = Me.SqlConnection1 Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone")) Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax")) ' 'SqlUpdateCommand1 ' Me.SqlUpdateCommand1.CommandText = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, Contac" & _ "tName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @" & _ "City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @P" & _ "hone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Origi" & _ "nal_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Origi" & _ "nal_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Origina" & _ "l_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName" & _ " IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR " & _ "@Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Origina" & _ "l_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original" & _ "_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @" & _ "Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode" & _ " OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original" & _ "_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, Comp" & _ "anyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, " & _ "Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)" Me.SqlUpdateCommand1.Connection = Me.SqlConnection1 Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax")) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CustomerID", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Address", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "City", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CompanyName", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ContactName", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ContactTitle", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Country", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Fax", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Phone", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PostalCode", System.Data.DataRowVersion.Original, Nothing)) Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Region", System.Data.DataRowVersion.Original, Nothing)) ' 'SqlDeleteCommand1 ' Me.SqlDeleteCommand1.CommandText = "DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) AND (Address = @O" & _ "riginal_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @O" & _ "riginal_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Ori" & _ "ginal_CompanyName) AND (ContactName = @Original_ContactName OR @Original_Contact" & _ "Name IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle" & _ " OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Ori" & _ "ginal_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Orig" & _ "inal_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone " & _ "OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_Postal" & _ "Code OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Orig" & _ "inal_Region OR @Original_Region IS NULL AND Region IS NULL)" Me.SqlDeleteCommand1.Connection = Me.SqlConnection1 Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CustomerID", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Address", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "City", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CompanyName", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ContactName", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ContactTitle", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Country", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Fax", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Phone", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PostalCode", System.Data.DataRowVersion.Original, Nothing)) Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, _ System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Region", System.Data.DataRowVersion.Original, Nothing)) ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "workstation id=DELLPC;packet size=4096;integrated security=SSPI;data source=""(loc" & _ "al)"";persist security info=False;initial catalog=Northwind" ' 'Customers1 ' Me.Customers1.DataSetName = "Customers" Me.Customers1.Locale = New System.Globalization.CultureInfo("en-US") ' 'DataGrid1 ' Me.DataGrid1.DataBindings.Add(New System.Windows.Forms.Binding("DataSource", Me.Customers1, "Customers")) Me.DataGrid1.DataMember = "" Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location = New System.Drawing.Point(0, 0) Me.DataGrid1.Name = "DataGrid1" Me.DataGrid1.Size = New System.Drawing.Size(292, 208) Me.DataGrid1.TabIndex = 0 ' 'Button1 ' Me.Button1.Location = New System.Drawing.Point(112, 224) Me.Button1.Name = "Button1" Me.Button1.TabIndex = 1 Me.Button1.Text = "Button1" ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 12) Me.ClientSize = New System.Drawing.Size(292, 266) Me.Controls.Add(Me.DataGrid1) Me.Controls.Add(Me.Button1) Me.Name = "Form1" Me.Text = "Form1" CType(Me.Customers1, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region ''' ----------------------------------------------------------------------------- ''' <summary> ''' フォームが初めて表示される直前に発生します。 ''' </summary> ''' ----------------------------------------------------------------------------- Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.SqlConnection1.Open() Me.SqlDataAdapter1.Fill(Me.Customers1) End Sub ''' ----------------------------------------------------------------------------- ''' <summary> ''' フォームが閉じている間に発生します。 ''' </summary> ''' ----------------------------------------------------------------------------- Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing If Not Me.SqlConnection1 Is Nothing Then Me.SqlConnection1.Close() Me.SqlConnection1.Dispose() End If End Sub End Class
コードの最後に、以下のコードを付け足します。
Form1.vb
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim tran As SqlClient.SqlTransaction = Me.SqlConnection1.BeginTransaction()
Try
Me.SqlDataAdapter1.UpdateCommand.Transaction = tran
Me.SqlDataAdapter1.Update(Me.Customers1)
tran.Commit()
Catch ex As Exception
tran.Rollback()
Throw
End Try
End Sub
■デバッグ実行！
コードの入力が終わったら、メニューバーの[デバッグ]→[デバッグ実行]をクリックしましょう。
この状態で、SQL Server のデータをクエリアナライザで見てみます。
次に、自分の作成したプログラムで 1 行目のデータを以下の様に編集します。
Button1 を押下した後、再びクエリアナライザで SQL Server のデータを見てみます。
おっ。ちゃんと変わってますね。
確認できたら画面右上の×ボタンをクリックしてプログラムを終了させて下さい。
