C# DataGrid with DataSet

This is a simple C# Program that illustrate the usage of DataGrid with DataSet.

Create Database and Table accordingly.


/*
* Simple C# example to illustrate the usage of Dataset with DataGrid
* Need to change the url and query accordingly
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

public class DataGridSample:Form{
DataGrid myGrid;

SqlConnection con;
SqlDataAdapter adapter;
DataSet ds;
Button ok, cancel;

SqlParameter workParam = null;

// apply to the columns in the table
string query = "select CardNo,CardType,CardAmount, CardHolderName from CardTest";

// change the Server ,uid, pwd and database accordingly
string url = "server=TR4;uid=sa;pwd= ;database=RBSGTest";



static void Main(){
Application.Run(new DataGridSample());
}

public DataGridSample(){
InitializeComponent();
}

public void InitializeComponent(){
this.ClientSize = new System.Drawing.Size(550, 450);
myGrid = new DataGrid();
myGrid.Location = new Point (10,10);
myGrid.Size = new Size(500, 350);
this.Text = "C# DataGrid with DataSet - Example";
this.Controls.Add(myGrid);

ok = new Button();
ok.Location = new Point(10, 375);
ok.Size = new Size(70, 30);
ok.TabIndex = 1;
ok.Text = "OK";
this.Controls.Add(ok);
ok.Click += new System.EventHandler(button_Click);

cancel = new Button();
cancel.Location = new Point(95, 375);
cancel.Size = new Size(70, 30);
cancel.TabIndex = 1;
cancel.Text = "Cancel";
this.Controls.Add(cancel);
cancel.Click += new System.EventHandler(button_Click);

ConnectToData(); // establish database connection and create DataSet
myGrid.SetDataBinding(ds, "CardTest");
DataTable t = ds.Tables["CardTest"];
t.RowChanged += new DataRowChangeEventHandler(Row_Changed);

}

public void ConnectToData(){
ds = new DataSet();
con = new SqlConnection(url);
adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query, con);
adapter.Fill(ds, "CardTest");
insertCommand();
updateCommand();
}

public void updateCommand()
{
string query = "Update CardTest Set CardHolderName = @CardHolderName, CardType = @CardType, CardAmount = @CardAmount WHERE CardNo = @CardNo";

adapter.UpdateCommand = new SqlCommand(query, con);

workParam = adapter.UpdateCommand.Parameters.Add("@CardNo", SqlDbType.NChar);
workParam.SourceColumn = "CardNo";
workParam.SourceVersion = DataRowVersion.Original;

workParam = adapter.UpdateCommand.Parameters.Add("@CardType", SqlDbType.NChar, 50);
workParam.SourceVersion = DataRowVersion.Current;
workParam.SourceColumn = "CardType";

workParam = adapter.UpdateCommand.Parameters.Add("@CardAmount", SqlDbType.Int);
workParam.SourceColumn = "CardAmount";
workParam.SourceVersion = DataRowVersion.Current;

workParam = adapter.UpdateCommand.Parameters.Add("@CardHolderName", SqlDbType.NChar, 50);
workParam.SourceColumn = "CardHolderName";
workParam.SourceVersion = DataRowVersion.Current;
}


public void button_Click(object sender, EventArgs evArgs)
{
if (sender==ok){
UpdateValue(); // update the database once everything done.
}
if (sender==cancel) {
this.Dispose();
}
}


private void Row_Changed(object ob, DataRowChangeEventArgs e)
{
DataTable t = (DataTable) ob;
Console.WriteLine("RowChanged " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]);
}

public void insertCommand()
{
string insertQuery = "Insert into CardTest VALUES (@CardNo, @CardType, @CardAmount, @CardHolderName)";
adapter.InsertCommand = new SqlCommand(insertQuery, con);

workParam = adapter.InsertCommand.Parameters.Add("@CardNo", SqlDbType.NChar);
workParam.SourceColumn = "CardNo";
workParam.SourceVersion = DataRowVersion.Current;

workParam = adapter.InsertCommand.Parameters.Add("@CardType", SqlDbType.NChar, 50);
workParam.SourceVersion = DataRowVersion.Current;
workParam.SourceColumn = "CardType";

workParam = adapter.InsertCommand.Parameters.Add("@CardAmount", SqlDbType.Int);
workParam.SourceColumn = "CardAmount";
workParam.SourceVersion = DataRowVersion.Current;

workParam = adapter.InsertCommand.Parameters.Add("@CardHolderName", SqlDbType.NChar, 50);
workParam.SourceVersion = DataRowVersion.Current;
workParam.SourceColumn = "CardHolderName";
}


public void UpdateValue()
{
try
{
adapter.Update(ds, "CardTest");
Console.Write("Updating DataSet succeeded!");
}
catch(Exception e)
{
Console.Write(e.ToString());
}
}

}