using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data; // all versions of SQL Server
using System.Data.SqlClient; // for SQL 7 and later
using System.Data.SqlTypes; // SQL data types for parameters
namespace RunProcs
{
///
/// Summary description for Form1.
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button TenMostExpensive;
private System.Windows.Forms.DataGrid grdNorthwind;
private System.Windows.Forms.TextBox textBoxPAF;
private System.Windows.Forms.Label labelNbrPrices;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.Button UpdatePrices;
private System.Windows.Forms.Label labelPAF;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.textBoxPAF = new System.Windows.Forms.TextBox();
this.labelPAF = new System.Windows.Forms.Label();
this.labelNbrPrices = new System.Windows.Forms.Label();
this.UpdatePrices = new System.Windows.Forms.Button();
this.grdNorthwind = new System.Windows.Forms.DataGrid();
this.TenMostExpensive = new System.Windows.Forms.Button();
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.groupBox2 = new System.Windows.Forms.GroupBox();
((System.ComponentModel.ISupportInitialize)(this.grdNorthwind)).BeginInit();
this.groupBox1.SuspendLayout();
this.groupBox2.SuspendLayout();
this.SuspendLayout();
//
// textBoxPAF
//
this.textBoxPAF.Location = new System.Drawing.Point(120, 16);
this.textBoxPAF.Name = "textBoxPAF";
this.textBoxPAF.TabIndex = 0;
this.textBoxPAF.Text = "";
//
// labelPAF
//
this.labelPAF.Location = new System.Drawing.Point(8, 16);
this.labelPAF.Name = "labelPAF";
this.labelPAF.Size = new System.Drawing.Size(112, 32);
this.labelPAF.TabIndex = 2;
this.labelPAF.Text = "Enter Price Adjustment Factor";
//
// labelNbrPrices
//
this.labelNbrPrices.Location = new System.Drawing.Point(8, 80);
this.labelNbrPrices.Name = "labelNbrPrices";
this.labelNbrPrices.Size = new System.Drawing.Size(216, 16);
this.labelNbrPrices.TabIndex = 5;
//
// UpdatePrices
//
this.UpdatePrices.Location = new System.Drawing.Point(8, 48);
this.UpdatePrices.Name = "UpdatePrices";
this.UpdatePrices.Size = new System.Drawing.Size(88, 23);
this.UpdatePrices.TabIndex = 6;
this.UpdatePrices.Text = "Update Prices";
this.UpdatePrices.Click += new
System.EventHandler(this.UpdatePrices_Click);
//
// grdNorthwind
//
this.grdNorthwind.DataMember = "";
this.grdNorthwind.HeaderForeColor =
System.Drawing.SystemColors.ControlText;
this.grdNorthwind.Location = new System.Drawing.Point(8, 48);
this.grdNorthwind.Name = "grdNorthwind";
this.grdNorthwind.Size = new System.Drawing.Size(296, 160);
this.grdNorthwind.TabIndex = 7;
//
// TenMostExpensive
//
this.TenMostExpensive.Location = new System.Drawing.Point(8, 16);
this.TenMostExpensive.Name = "TenMostExpensive";
this.TenMostExpensive.Size = new System.Drawing.Size(168, 23);
this.TenMostExpensive.TabIndex = 8;
this.TenMostExpensive.Text = "Ten Most Expensive Products";
this.TenMostExpensive.Click += new
System.EventHandler(this.TenMostExpensive_Click);
//
// groupBox1
//
this.groupBox1.Controls.AddRange(new System.Windows.Forms.Control[] {
this.labelPAF,
this.textBoxPAF,
this.UpdatePrices,
this.labelNbrPrices});
this.groupBox1.Location = new System.Drawing.Point(8, 8);
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(240, 112);
this.groupBox1.TabIndex = 9;
this.groupBox1.TabStop = false;
//
// groupBox2
//
this.groupBox2.Controls.AddRange(new System.Windows.Forms.Control[] {
this.grdNorthwind,
this.TenMostExpensive});
this.groupBox2.Location = new System.Drawing.Point(8, 128);
this.groupBox2.Name = "groupBox2";
this.groupBox2.Size = new System.Drawing.Size(312, 216);
this.groupBox2.TabIndex = 10;
this.groupBox2.TabStop = false;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(328, 350);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.groupBox2,
this.groupBox1});
this.Name = "Form1";
this.Text = "Stored Procedures";
((System.ComponentModel.ISupportInitialize)(this.grdNorthwind)).EndInit();
this.groupBox1.ResumeLayout(false);
this.groupBox2.ResumeLayout(false);
this.ResumeLayout(false);
}
#endregion
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void UpdatePrices_Click(object sender, System.EventArgs e)
{
// The following code is the step-by-step code shown in the article.
//
#region version of code showing every single step
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=(local);Integrated Security=SSPI;"
+ "database=Northwind";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdatePrices";
SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;
SqlParameter paramPAF = new SqlParameter();
paramPAF.ParameterName = "@PriceMultiplier";
paramPAF.SqlDbType = SqlDbType.Decimal;
paramPAF.Direction = ParameterDirection.Input;
paramPAF.Value = textBoxPAF.Text;
SqlParameter paramNbrRows = new SqlParameter();
paramNbrRows.ParameterName = "@NbrRows";
paramNbrRows.SqlDbType = SqlDbType.Int;
paramNbrRows.Direction = ParameterDirection.Output;
#endregion
// The following code is the shortened version of the above code.
#region shortened version of code using constructor overloads
// SqlConnection conn = new SqlConnection("server=(local);Integrated
Security=SSPI;database=Northwind");
//
// SqlCommand cmd = new SqlCommand("UpdatePrices",conn);
// cmd.CommandType = CommandType.StoredProcedure;
//
// SqlParameter paramReturnValue = new
SqlParameter("@return_value",SqlDbType.Int);
// paramReturnValue.Direction = ParameterDirection.ReturnValue;
//
// SqlParameter paramPAF = new
SqlParameter("@PriceMultiplier",SqlDbType.Decimal);
// paramPAF.Direction = ParameterDirection.Input; // optional
// paramPAF.Value = textBoxPAF.Text;
//
// SqlParameter paramNbrRows = new SqlParameter("@NbrRows",SqlDbType.Int);
// paramNbrRows.Direction = ParameterDirection.Output;
#endregion
cmd.Parameters.Add(paramReturnValue); // must be added first, parameter
0
cmd.Parameters.Add(paramPAF); // parameter 1
cmd.Parameters.Add(paramNbrRows); // parameter 2
conn.Open();
// Lines 1 and 2 of 3 new lines added for explicit transaction support.
SqlTransaction trans = conn.BeginTransaction(); // connection must be
open
cmd.Transaction = trans;
cmd.ExecuteNonQuery(); // debugger can step into stored proc here
int returnValue = (int) cmd.Parameters["@return_value"].Value;
labelNbrPrices.Text = cmd.Parameters["@NbrRows"].Value.ToString()
+ " prices updated";
// Line 3 of 3 new lines added for explicit transaction support.
trans.Commit(); // or trans.Rollback(), must be before connection is
closed
conn.Close(); // alternatively, conn.Dispose
}
private void TenMostExpensive_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=Northwind");
SqlDataAdapter da = new SqlDataAdapter("[Ten Most Expensive
Products]",conn);
da.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.
DataSet dsNorthwind = new DataSet("Northwind");
conn.Open();
da.Fill(dsNorthwind); // debugger can step into stored proc here
// You could call a second stored procedure by using
// da.SelectCommand.CommandText followed by da.Fill
conn.Close(); // alternatively, conn.Dispose
grdNorthwind.DataSource = dsNorthwind.Tables[0];
}
}
}