G
Guest
I've modified one of the MS walkthrough examples featuring a parameterized
query to use stored procedures to select, update, insert, and delete records
(using the Data Adapter Wizard to create the four stored procedures) from the
author table of the pubs database. My form loads and displays the data from
the dataset correctly when I click the Show button; however I have no idea on
how to wire the Update, Insert, & Delete buttons I've added to the project.
Any help would be greatly appreciated.
C# Code Follows
=====================================================
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace DataModel
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private DataModel.dsDataSet dsDataSet1;
private System.Windows.Forms.TextBox txtStateParameter;
private System.Windows.Forms.TextBox txtAuthorID;
private System.Windows.Forms.TextBox txtAuthorFName;
private System.Windows.Forms.TextBox txtAuthorLName;
private System.Windows.Forms.TextBox txtAuthorState;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnShow;
private System.Windows.Forms.Button btnPrevious;
private System.Windows.Forms.Button btnNext;
private System.Windows.Forms.TextBox txtPosition;
private System.Windows.Forms.Button btnUpdate;
private System.Windows.Forms.Button btnInsert;
private System.Windows.Forms.Button btnDelete;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dsDataSet1 = new DataModel.dsDataSet();
this.txtStateParameter = new System.Windows.Forms.TextBox();
this.txtAuthorID = new System.Windows.Forms.TextBox();
this.txtAuthorFName = new System.Windows.Forms.TextBox();
this.txtAuthorLName = new System.Windows.Forms.TextBox();
this.txtAuthorState = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnShow = new System.Windows.Forms.Button();
this.btnPrevious = new System.Windows.Forms.Button();
this.btnNext = new System.Windows.Forms.Button();
this.txtPosition = new System.Windows.Forms.TextBox();
this.btnUpdate = new System.Windows.Forms.Button();
this.btnInsert = new System.Windows.Forms.Button();
this.btnDelete = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).BeginInit();
this.SuspendLayout();
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"authors", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("au_id", "au_id"),
new
System.Data.Common.DataColumnMapping("au_lname", "au_lname"),
new
System.Data.Common.DataColumnMapping("au_fname", "au_fname"),
new
System.Data.Common.DataColumnMapping("state", "state")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "[DataModelSelectCommand]";
this.sqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.VarChar,
2, "state"));
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "[DataModelInsertCommand]";
this.sqlInsertCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar,
11, "au_id"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar,
2, "state"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = "[DataModelUpdateCommand]";
this.sqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar,
11, "au_id"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar,
2, "state"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_id",
System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_fname",
System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_lname",
System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_state",
System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "state",
System.Data.DataRowVersion.Original, null));
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = "[DataModelDeleteCommand]";
this.sqlDeleteCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_id",
System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_fname",
System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_lname",
System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_state",
System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "state",
System.Data.DataRowVersion.Original, null));
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=COLOSSUS;packet
size=4096;user id=sa;data source=QUEEN01;persist s" +
"ecurity info=True;initial catalog=pubs;password=\"SNWb@Ckd0Re\"";
//
// dsDataSet1
//
this.dsDataSet1.DataSetName = "dsDataSet";
this.dsDataSet1.Locale = new System.Globalization.CultureInfo("en-US");
//
// txtStateParameter
//
this.txtStateParameter.Location = new System.Drawing.Point(136, 24);
this.txtStateParameter.Name = "txtStateParameter";
this.txtStateParameter.Size = new System.Drawing.Size(48, 20);
this.txtStateParameter.TabIndex = 0;
this.txtStateParameter.Text = "";
//
// txtAuthorID
//
this.txtAuthorID.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_id"));
this.txtAuthorID.Location = new System.Drawing.Point(120, 64);
this.txtAuthorID.Name = "txtAuthorID";
this.txtAuthorID.TabIndex = 1;
this.txtAuthorID.Text = "";
//
// txtAuthorFName
//
this.txtAuthorFName.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_fname"));
this.txtAuthorFName.Location = new System.Drawing.Point(120, 104);
this.txtAuthorFName.Name = "txtAuthorFName";
this.txtAuthorFName.TabIndex = 2;
this.txtAuthorFName.Text = "";
//
// txtAuthorLName
//
this.txtAuthorLName.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_lname"));
this.txtAuthorLName.Location = new System.Drawing.Point(120, 144);
this.txtAuthorLName.Name = "txtAuthorLName";
this.txtAuthorLName.TabIndex = 3;
this.txtAuthorLName.Text = "";
//
// txtAuthorState
//
this.txtAuthorState.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.state"));
this.txtAuthorState.Location = new System.Drawing.Point(120, 184);
this.txtAuthorState.Name = "txtAuthorState";
this.txtAuthorState.TabIndex = 4;
this.txtAuthorState.Text = "";
//
// label1
//
this.label1.Location = new System.Drawing.Point(40, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(88, 23);
this.label1.TabIndex = 5;
this.label1.Text = "Enter state code";
//
// label2
//
this.label2.Location = new System.Drawing.Point(40, 64);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(64, 23);
this.label2.TabIndex = 6;
this.label2.Text = "Author ID";
//
// label3
//
this.label3.Location = new System.Drawing.Point(40, 104);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(64, 23);
this.label3.TabIndex = 7;
this.label3.Text = "First Name";
//
// label4
//
this.label4.Location = new System.Drawing.Point(40, 144);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(64, 23);
this.label4.TabIndex = 8;
this.label4.Text = "Last Name";
//
// label5
//
this.label5.Location = new System.Drawing.Point(40, 184);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(48, 23);
this.label5.TabIndex = 9;
this.label5.Text = "State";
//
// btnShow
//
this.btnShow.Location = new System.Drawing.Point(192, 24);
this.btnShow.Name = "btnShow";
this.btnShow.TabIndex = 10;
this.btnShow.Text = "Show";
this.btnShow.Click += new System.EventHandler(this.btnShow_Click);
//
// btnPrevious
//
this.btnPrevious.Location = new System.Drawing.Point(48, 232);
this.btnPrevious.Name = "btnPrevious";
this.btnPrevious.TabIndex = 11;
this.btnPrevious.Text = "Previous";
this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click);
//
// btnNext
//
this.btnNext.Location = new System.Drawing.Point(224, 232);
this.btnNext.Name = "btnNext";
this.btnNext.TabIndex = 12;
this.btnNext.Text = "Next";
this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
//
// txtPosition
//
this.txtPosition.Enabled = false;
this.txtPosition.Location = new System.Drawing.Point(144, 232);
this.txtPosition.Name = "txtPosition";
this.txtPosition.Size = new System.Drawing.Size(56, 20);
this.txtPosition.TabIndex = 13;
this.txtPosition.Text = "";
//
// btnUpdate
//
this.btnUpdate.Location = new System.Drawing.Point(256, 64);
this.btnUpdate.Name = "btnUpdate";
this.btnUpdate.TabIndex = 14;
this.btnUpdate.Text = "Update";
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
//
// btnInsert
//
this.btnInsert.Location = new System.Drawing.Point(256, 104);
this.btnInsert.Name = "btnInsert";
this.btnInsert.TabIndex = 15;
this.btnInsert.Text = "Insert";
this.btnInsert.Click += new System.EventHandler(this.btnInsert_Click);
//
// btnDelete
//
this.btnDelete.Location = new System.Drawing.Point(256, 144);
this.btnDelete.Name = "btnDelete";
this.btnDelete.TabIndex = 16;
this.btnDelete.Text = "Delete";
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(368, 273);
this.Controls.Add(this.btnDelete);
this.Controls.Add(this.btnInsert);
this.Controls.Add(this.btnUpdate);
this.Controls.Add(this.txtPosition);
this.Controls.Add(this.btnNext);
this.Controls.Add(this.btnPrevious);
this.Controls.Add(this.btnShow);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.txtAuthorState);
this.Controls.Add(this.txtAuthorLName);
this.Controls.Add(this.txtAuthorFName);
this.Controls.Add(this.txtAuthorID);
this.Controls.Add(this.txtStateParameter);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void btnShow_Click(object sender, System.EventArgs e)
{
sqlDataAdapter1.SelectCommand.Parameters["@Param2"].Value =
txtStateParameter.Text;
dsDataSet1.Clear();
sqlDataAdapter1.Fill(dsDataSet1);
ShowPosition();
}
private void btnPrevious_Click(object sender, System.EventArgs e)
{
this.BindingContext[dsDataSet1, "authors"].Position -=1;
ShowPosition();
}
private void btnNext_Click(object sender, System.EventArgs e)
{
this.BindingContext[dsDataSet1, "authors"].Position +=1 ;
ShowPosition();
}
private void ShowPosition()
{
int iCnt;
int iPos;
iCnt = this.BindingContext[dsDataSet1, "authors"].Count;
iPos = this.BindingContext[dsDataSet1, "authors"].Position + 1;
if(iCnt == 0)
{
txtPosition.Text = "(No records)";
}
else
{
txtPosition.Text = iPos.ToString() + " of " + iCnt.ToString() ;
}
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
}
private void btnInsert_Click(object sender, System.EventArgs e)
{
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
}
}
}
query to use stored procedures to select, update, insert, and delete records
(using the Data Adapter Wizard to create the four stored procedures) from the
author table of the pubs database. My form loads and displays the data from
the dataset correctly when I click the Show button; however I have no idea on
how to wire the Update, Insert, & Delete buttons I've added to the project.
Any help would be greatly appreciated.
C# Code Follows
=====================================================
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace DataModel
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private DataModel.dsDataSet dsDataSet1;
private System.Windows.Forms.TextBox txtStateParameter;
private System.Windows.Forms.TextBox txtAuthorID;
private System.Windows.Forms.TextBox txtAuthorFName;
private System.Windows.Forms.TextBox txtAuthorLName;
private System.Windows.Forms.TextBox txtAuthorState;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnShow;
private System.Windows.Forms.Button btnPrevious;
private System.Windows.Forms.Button btnNext;
private System.Windows.Forms.TextBox txtPosition;
private System.Windows.Forms.Button btnUpdate;
private System.Windows.Forms.Button btnInsert;
private System.Windows.Forms.Button btnDelete;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dsDataSet1 = new DataModel.dsDataSet();
this.txtStateParameter = new System.Windows.Forms.TextBox();
this.txtAuthorID = new System.Windows.Forms.TextBox();
this.txtAuthorFName = new System.Windows.Forms.TextBox();
this.txtAuthorLName = new System.Windows.Forms.TextBox();
this.txtAuthorState = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnShow = new System.Windows.Forms.Button();
this.btnPrevious = new System.Windows.Forms.Button();
this.btnNext = new System.Windows.Forms.Button();
this.txtPosition = new System.Windows.Forms.TextBox();
this.btnUpdate = new System.Windows.Forms.Button();
this.btnInsert = new System.Windows.Forms.Button();
this.btnDelete = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).BeginInit();
this.SuspendLayout();
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"authors", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("au_id", "au_id"),
new
System.Data.Common.DataColumnMapping("au_lname", "au_lname"),
new
System.Data.Common.DataColumnMapping("au_fname", "au_fname"),
new
System.Data.Common.DataColumnMapping("state", "state")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "[DataModelSelectCommand]";
this.sqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.VarChar,
2, "state"));
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "[DataModelInsertCommand]";
this.sqlInsertCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar,
11, "au_id"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar,
2, "state"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = "[DataModelUpdateCommand]";
this.sqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar,
11, "au_id"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar,
2, "state"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_id",
System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_fname",
System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_lname",
System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_state",
System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "state",
System.Data.DataRowVersion.Original, null));
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = "[DataModelDeleteCommand]";
this.sqlDeleteCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_id",
System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_fname",
System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_fname",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_au_lname",
System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "au_lname",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_state",
System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "state",
System.Data.DataRowVersion.Original, null));
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=COLOSSUS;packet
size=4096;user id=sa;data source=QUEEN01;persist s" +
"ecurity info=True;initial catalog=pubs;password=\"SNWb@Ckd0Re\"";
//
// dsDataSet1
//
this.dsDataSet1.DataSetName = "dsDataSet";
this.dsDataSet1.Locale = new System.Globalization.CultureInfo("en-US");
//
// txtStateParameter
//
this.txtStateParameter.Location = new System.Drawing.Point(136, 24);
this.txtStateParameter.Name = "txtStateParameter";
this.txtStateParameter.Size = new System.Drawing.Size(48, 20);
this.txtStateParameter.TabIndex = 0;
this.txtStateParameter.Text = "";
//
// txtAuthorID
//
this.txtAuthorID.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_id"));
this.txtAuthorID.Location = new System.Drawing.Point(120, 64);
this.txtAuthorID.Name = "txtAuthorID";
this.txtAuthorID.TabIndex = 1;
this.txtAuthorID.Text = "";
//
// txtAuthorFName
//
this.txtAuthorFName.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_fname"));
this.txtAuthorFName.Location = new System.Drawing.Point(120, 104);
this.txtAuthorFName.Name = "txtAuthorFName";
this.txtAuthorFName.TabIndex = 2;
this.txtAuthorFName.Text = "";
//
// txtAuthorLName
//
this.txtAuthorLName.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.au_lname"));
this.txtAuthorLName.Location = new System.Drawing.Point(120, 144);
this.txtAuthorLName.Name = "txtAuthorLName";
this.txtAuthorLName.TabIndex = 3;
this.txtAuthorLName.Text = "";
//
// txtAuthorState
//
this.txtAuthorState.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dsDataSet1, "authors.state"));
this.txtAuthorState.Location = new System.Drawing.Point(120, 184);
this.txtAuthorState.Name = "txtAuthorState";
this.txtAuthorState.TabIndex = 4;
this.txtAuthorState.Text = "";
//
// label1
//
this.label1.Location = new System.Drawing.Point(40, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(88, 23);
this.label1.TabIndex = 5;
this.label1.Text = "Enter state code";
//
// label2
//
this.label2.Location = new System.Drawing.Point(40, 64);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(64, 23);
this.label2.TabIndex = 6;
this.label2.Text = "Author ID";
//
// label3
//
this.label3.Location = new System.Drawing.Point(40, 104);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(64, 23);
this.label3.TabIndex = 7;
this.label3.Text = "First Name";
//
// label4
//
this.label4.Location = new System.Drawing.Point(40, 144);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(64, 23);
this.label4.TabIndex = 8;
this.label4.Text = "Last Name";
//
// label5
//
this.label5.Location = new System.Drawing.Point(40, 184);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(48, 23);
this.label5.TabIndex = 9;
this.label5.Text = "State";
//
// btnShow
//
this.btnShow.Location = new System.Drawing.Point(192, 24);
this.btnShow.Name = "btnShow";
this.btnShow.TabIndex = 10;
this.btnShow.Text = "Show";
this.btnShow.Click += new System.EventHandler(this.btnShow_Click);
//
// btnPrevious
//
this.btnPrevious.Location = new System.Drawing.Point(48, 232);
this.btnPrevious.Name = "btnPrevious";
this.btnPrevious.TabIndex = 11;
this.btnPrevious.Text = "Previous";
this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click);
//
// btnNext
//
this.btnNext.Location = new System.Drawing.Point(224, 232);
this.btnNext.Name = "btnNext";
this.btnNext.TabIndex = 12;
this.btnNext.Text = "Next";
this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
//
// txtPosition
//
this.txtPosition.Enabled = false;
this.txtPosition.Location = new System.Drawing.Point(144, 232);
this.txtPosition.Name = "txtPosition";
this.txtPosition.Size = new System.Drawing.Size(56, 20);
this.txtPosition.TabIndex = 13;
this.txtPosition.Text = "";
//
// btnUpdate
//
this.btnUpdate.Location = new System.Drawing.Point(256, 64);
this.btnUpdate.Name = "btnUpdate";
this.btnUpdate.TabIndex = 14;
this.btnUpdate.Text = "Update";
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
//
// btnInsert
//
this.btnInsert.Location = new System.Drawing.Point(256, 104);
this.btnInsert.Name = "btnInsert";
this.btnInsert.TabIndex = 15;
this.btnInsert.Text = "Insert";
this.btnInsert.Click += new System.EventHandler(this.btnInsert_Click);
//
// btnDelete
//
this.btnDelete.Location = new System.Drawing.Point(256, 144);
this.btnDelete.Name = "btnDelete";
this.btnDelete.TabIndex = 16;
this.btnDelete.Text = "Delete";
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(368, 273);
this.Controls.Add(this.btnDelete);
this.Controls.Add(this.btnInsert);
this.Controls.Add(this.btnUpdate);
this.Controls.Add(this.txtPosition);
this.Controls.Add(this.btnNext);
this.Controls.Add(this.btnPrevious);
this.Controls.Add(this.btnShow);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.txtAuthorState);
this.Controls.Add(this.txtAuthorLName);
this.Controls.Add(this.txtAuthorFName);
this.Controls.Add(this.txtAuthorID);
this.Controls.Add(this.txtStateParameter);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dsDataSet1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void btnShow_Click(object sender, System.EventArgs e)
{
sqlDataAdapter1.SelectCommand.Parameters["@Param2"].Value =
txtStateParameter.Text;
dsDataSet1.Clear();
sqlDataAdapter1.Fill(dsDataSet1);
ShowPosition();
}
private void btnPrevious_Click(object sender, System.EventArgs e)
{
this.BindingContext[dsDataSet1, "authors"].Position -=1;
ShowPosition();
}
private void btnNext_Click(object sender, System.EventArgs e)
{
this.BindingContext[dsDataSet1, "authors"].Position +=1 ;
ShowPosition();
}
private void ShowPosition()
{
int iCnt;
int iPos;
iCnt = this.BindingContext[dsDataSet1, "authors"].Count;
iPos = this.BindingContext[dsDataSet1, "authors"].Position + 1;
if(iCnt == 0)
{
txtPosition.Text = "(No records)";
}
else
{
txtPosition.Text = iPos.ToString() + " of " + iCnt.ToString() ;
}
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
}
private void btnInsert_Click(object sender, System.EventArgs e)
{
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
}
}
}