Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Public Class Form1 Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. 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 'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer 'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox Friend WithEvents labelPAF As System.Windows.Forms.Label Friend WithEvents labelNbrPrices As System.Windows.Forms.Label Friend WithEvents UpdatePrices As System.Windows.Forms.Button Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox Friend WithEvents TenMostExpensive As System.Windows.Forms.Button Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox Private Sub InitializeComponent() Me.GroupBox1 = New System.Windows.Forms.GroupBox() Me.labelPAF = New System.Windows.Forms.Label() Me.labelNbrPrices = New System.Windows.Forms.Label() Me.textBoxPAF = New System.Windows.Forms.TextBox() Me.UpdatePrices = New System.Windows.Forms.Button() Me.groupBox2 = New System.Windows.Forms.GroupBox() Me.TenMostExpensive = New System.Windows.Forms.Button() Me.grdNorthwind = New System.Windows.Forms.DataGrid() Me.GroupBox1.SuspendLayout() Me.groupBox2.SuspendLayout() CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'GroupBox1 ' Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices}) Me.GroupBox1.Location = New System.Drawing.Point(8, 8) Me.GroupBox1.Name = "GroupBox1" Me.GroupBox1.Size = New System.Drawing.Size(240, 112) Me.GroupBox1.TabIndex = 9 Me.GroupBox1.TabStop = False ' 'labelPAF ' Me.labelPAF.Location = New System.Drawing.Point(8, 16) Me.labelPAF.Name = "labelPAF" Me.labelPAF.Size = New System.Drawing.Size(112, 32) Me.labelPAF.TabIndex = 2 Me.labelPAF.Text = "Enter Price Adjustment Factor" ' 'labelNbrPrices ' Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80) Me.labelNbrPrices.Name = "labelNbrPrices" Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16) Me.labelNbrPrices.TabIndex = 5 ' 'textBoxPAF ' Me.textBoxPAF.Location = New System.Drawing.Point(120, 16) Me.textBoxPAF.Name = "textBoxPAF" Me.textBoxPAF.TabIndex = 0 Me.textBoxPAF.Text = "" ' 'UpdatePrices ' Me.UpdatePrices.Location = New System.Drawing.Point(8, 48) Me.UpdatePrices.Name = "UpdatePrices" Me.UpdatePrices.Size = New System.Drawing.Size(88, 23) Me.UpdatePrices.TabIndex = 6 Me.UpdatePrices.Text = "Update Prices" ' 'groupBox2 ' Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind}) Me.groupBox2.Location = New System.Drawing.Point(8, 128) Me.groupBox2.Name = "groupBox2" Me.groupBox2.Size = New System.Drawing.Size(312, 216) Me.groupBox2.TabIndex = 10 Me.groupBox2.TabStop = False ' 'TenMostExpensive ' Me.TenMostExpensive.Location = New System.Drawing.Point(8, 16) Me.TenMostExpensive.Name = "TenMostExpensive" Me.TenMostExpensive.Size = New System.Drawing.Size(168, 23) Me.TenMostExpensive.TabIndex = 8 Me.TenMostExpensive.Text = "Ten Most Expensive Products" ' 'grdNorthwind ' Me.grdNorthwind.DataMember = "" Me.grdNorthwind.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.grdNorthwind.Location = New System.Drawing.Point(8, 48) Me.grdNorthwind.Name = "grdNorthwind" Me.grdNorthwind.Size = New System.Drawing.Size(296, 160) Me.grdNorthwind.TabIndex = 7 ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(328, 350) Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.groupBox2, Me.GroupBox1}) Me.Name = "Form1" Me.Text = "Stored Procedures" Me.GroupBox1.ResumeLayout(False) Me.groupBox2.ResumeLayout(False) CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region Private Sub UpdatePrices_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdatePrices.Click ' version of code showing every single step 'Dim sqlConnection As SqlConnection = New SqlConnection() 'sqlConnection.ConnectionString = "server=(local);Integrated Security=SSPI;database=Northwind" 'Dim sqlCommand As SqlCommand = New SqlCommand() 'sqlCommand.Connection = sqlConnection 'sqlCommand.CommandType = CommandType.StoredProcedure 'sqlCommand.CommandText = "UpdatePrices" 'Dim sqlParameter1 As SqlParameter = New SqlParameter() 'sqlParameter1.ParameterName = "@return_value" 'sqlParameter1.SqlDbType = SqlDbType.Int 'sqlParameter1.Direction = ParameterDirection.ReturnValue 'Dim sqlParameter2 As SqlParameter = New SqlParameter() 'sqlParameter2.ParameterName = "@PriceMultiplier" 'sqlParameter2.SqlDbType = SqlDbType.Decimal 'sqlParameter2.Direction = ParameterDirection.Input 'sqlParameter2.Value = textBoxPAF.Text 'Dim sqlParameter3 As SqlParameter = New SqlParameter() 'sqlParameter3.ParameterName = "@NbrRows" 'sqlParameter3.SqlDbType = SqlDbType.Int 'sqlParameter3.Direction = ParameterDirection.Output ' shortened version of code using constructor overloads Dim sqlConnection As SqlConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Northwind") Dim sqlCommand As SqlCommand = New SqlCommand("UpdatePrices", sqlConnection) sqlCommand.CommandType = CommandType.StoredProcedure Dim sqlParameter1 As SqlParameter = New SqlParameter("@return_value", SqlDbType.Int) sqlParameter1.Direction = ParameterDirection.ReturnValue Dim sqlParameter2 As SqlParameter = New SqlParameter("@PriceMultiplier", SqlDbType.Decimal) sqlParameter2.Direction = ParameterDirection.Input sqlParameter2.Value = textBoxPAF.Text Dim sqlParameter3 As SqlParameter = New SqlParameter("@NbrRows", SqlDbType.Int) sqlParameter3.Direction = ParameterDirection.Output sqlCommand.Parameters.Add(sqlParameter1) ' must be added first, parameter 0 sqlCommand.Parameters.Add(sqlParameter2) ' parameter 1 sqlCommand.Parameters.Add(sqlParameter3) ' parameter 2 sqlConnection.Open() ' Lines 1 and 2 of 3 new lines added for explicit transaction support. Dim sqlTransaction As SqlTransaction = sqlConnection.BeginTransaction() sqlCommand.Transaction = sqlTransaction sqlCommand.ExecuteNonQuery() ' debugger can step into stored proc here Dim i As Integer = CInt(sqlCommand.Parameters("@return_value").Value) labelNbrPrices.Text = String.Concat(sqlCommand.Parameters("@NbrRows").Value.ToString(), " prices updated") ' Line 3 of 3 new lines added for explicit transaction support. sqlTransaction.Commit() ' or trans.Rollback(), must be before connection is closed sqlConnection.Close() ' alternatively, conn.Dispose End Sub Private Sub TenMostExpensive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TenMostExpensive.Click Dim sqlConnection As SqlConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Northwind") Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter("[Ten Most Expensive Products]", sqlConnection) sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure ' Pass the name of the DataSet through the overloaded constructor of ' the DataSet class. When the DataSet is represented as XML, this name ' is used as the name of the XML document element. Dim dataSet As DataSet = New DataSet("Northwind") sqlConnection.Open() sqlDataAdapter.Fill(dataSet) ' debugger can step into stored proc here ' You could call a second stored procedure by using ' da.SelectCommand.CommandText followed by da.Fill sqlConnection.Close() grdNorthwind.DataSource = dataSet.Tables(0) End Sub End Class