Hi,

How to insert, edit, update and delete the datas in datagrid? Pls explain with a simple example. Thanks in advance.

Hi friend,
Here is the Example that i used also. Try this:

I create a table in the database, named 'Student'. The table has three
columns:
ID int Not Null (Primary Key),
Name varchar(50) Null,
Age int Null

The following is the code in my test. It requires that you add a
DataGridView and a Button on the form.

public partial class Form1 : Form
{
BindingSource bs = new BindingSource();
DataTable table = new DataTable();
SqlConnection conn = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();

private void Form1_Load(object sender, EventArgs e)
{
bs.DataSource = table;
this.dataGridView1.DataSource = bs;

string connstr = "Data Source = .\\sqlexpress; integrated
security = true;Initial catalog = TestDataBase";
conn.ConnectionString = connstr;
string selectsql = "select * from student";
da.SelectCommand = new SqlCommand(selectsql,conn);

SqlCommand insercommand = new SqlCommand("insert into
student(id,name,age) values(@id,@name,@age)", conn);
insercommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
insercommand.Parameters.Add("@name", SqlDbType.VarChar, 50,
"Name");
insercommand.Parameters.Add("@age", SqlDbType.Int, 4, "Age");
da.InsertCommand = insercommand;

SqlCommand updatecommand = new SqlCommand("update student set
name=@name,age=@age where (id=@id)", conn);
updatecommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");

updatecommand.Parameters.Add("@name",SqlDbType.VarChar,50,"Name");
updatecommand.Parameters.Add("@age",SqlDbType.Int,4,"Age");
da.UpdateCommand = updatecommand;

SqlCommand deletecommand = new SqlCommand("delete student where
id=@id", conn);
deletecommand.Parameters.Add("@id", SqlDbType.Int, 4, "ID");
da.DeleteCommand = deletecommand;

conn.Open();
da.Fill(table);
conn.Close();
}
// click the button to save the changes to the database
private void button1_Click(object sender, EventArgs e)
{
da.Update(table);
}
}


Regards,

---

Varun Kumar

I thought about this for a minute and came up withfour solutions for DbConcurrencyException in this scenario using DataSets (though the firsttwo are essentially the same and differ only by who actually implements it). I'm sure there are others, but this should do for starters.

1. Use stored procedures created by a competent DBA that utilizes parameters for the original and new column state. This means that you check each field with a "OR (<ds.originalValue> = <ds.updateValue>)". This solution passes the same two parameters per field as an "optimistic" pre-generated update statement but it makes the update statement larger byadding this new "OR" condition for each field.
2. You can do the same by alteringa rawupdate generated from the DataSet designer. This means sending a longer select to the database each update though this can be offset by setting your batch size higher if you have lots of updates you're sending (uh, you'd need ADO.NET 2.0 for that). I'd hesitate to use this method but that's mainly a personal taste issue than anything else (because I'd prefer using stored procedures and recognize that internal network traffic generally isn't the bottleneck in these kinds of transactions, though on-the-fly statement execution plan creation could be).
3. Override the OnUpdating for the adapter to alter the command sent based on which fields have actually changed. This is probably the closest in effect to the OLTP solution envisioned by Udi. This solution is problematic for me simply because I've never actually tried to do it and I'm not sure you can hook into the base adapter updates each execution. If you can't,analternative (in ADO.NET 2.0) would beto create a base class for the table adapters and create an alternative Update function in derived partial classes. In this case, you'd have "AcceptFineGrainedChanges" or some such function that you'd call. Once the alternative base class was created, custom programming per table adapter would be a matter of a couple moments. I've done something similar for using the designer for SyBase table adapters and it worked out pretty well. I'd have to actually try this to make sure it'd work though. Call thistwo half-solutions if you're feeling stern about it.
4. This last would be usefulif I have a relatively well-defined use case that isn't going to morph much or require stringent concurrency resolution. In this one, you deliberately break the one-for-one relationship from your dataset and database (i.e. one database table can be represented by multiple dataset tables). In Udi's concurrency example, the dataset would have a CustomerAddress table and a CustomerStatus table. Creating the dataset with custom selects would generate the tables pretty painlessly with appropriate paranoia. Now, this only really pushes his concern down a little, making it less likely to be an issue. It doesn't eliminate it. It'd probably handle most of the concurrency problems people are likely to run into. Or at least, push them out beyond where most people will ever experience it (not quite the same thing). It could be taken to a rediculous extreme where each field was it's own datatable (which is just silly, but I've seen sillier things happen) so a little balance and logical separation would be needed.

---

Varun Kumar

Hi friend,
Here is the Example that i used also. Try this:

I create a table in the database, named 'Student'. The table has three
columns:
ID int Not Null (Primary Key),
Name varchar(50) Null,
Age int Null

The following is the code in my test. It requires that you add a
DataGridView and a Button on the form.

public partial class Form1 : Form
{
BindingSource bs = new BindingSource();
DataTable table = new DataTable();
SqlConnection conn = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();

private void Form1_Load(object sender, EventArgs e)
{
bs.DataSource = table;
this.dataGridView1.DataSource = bs;

string connstr = "Data Source = .\\sqlexpress; integrated
security = true;Initial catalog = TestDataBase";
conn.ConnectionString = connstr;
string selectsql = "select * from student";
da.SelectCommand = new SqlCommand(selectsql,conn);

SqlCommand insercommand = new SqlCommand("insert into
student(id,name,age) values(@id,@name,@age)", conn);
insercommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
insercommand.Parameters.Add("@name", SqlDbType.VarChar, 50,
"Name");
insercommand.Parameters.Add("@age", SqlDbType.Int, 4, "Age");
da.InsertCommand = insercommand;

SqlCommand updatecommand = new SqlCommand("update student set
name=@name,age=@age where (id=@id)", conn);
updatecommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");

updatecommand.Parameters.Add("@name",SqlDbType.VarChar,50,"Name");
updatecommand.Parameters.Add("@age",SqlDbType.Int,4,"Age");
da.UpdateCommand = updatecommand;

SqlCommand deletecommand = new SqlCommand("delete student where
id=@id", conn);
deletecommand.Parameters.Add("@id", SqlDbType.Int, 4, "ID");
da.DeleteCommand = deletecommand;

conn.Open();
da.Fill(table);
conn.Close();
}
// click the button to save the changes to the database
private void button1_Click(object sender, EventArgs e)
{
da.Update(table);
}
}


Regards,

---

Varun Kumar

Hi,

Thanks for ur response. I used ur coding but i'm getting an error. Error Message "dbconcurrencyexception was unhandled". How to rectify this?

I thought about this for a minute and came up withfour solutions for DbConcurrencyException in this scenario using DataSets (though the firsttwo are essentially the same and differ only by who actually implements it). I'm sure there are others, but this should do for starters.

1. Use stored procedures created by a competent DBA that utilizes parameters for the original and new column state. This means that you check each field with a "OR (<ds.originalValue> = <ds.updateValue>)". This solution passes the same two parameters per field as an "optimistic" pre-generated update statement but it makes the update statement larger byadding this new "OR" condition for each field.
2. You can do the same by alteringa rawupdate generated from the DataSet designer. This means sending a longer select to the database each update though this can be offset by setting your batch size higher if you have lots of updates you're sending (uh, you'd need ADO.NET 2.0 for that). I'd hesitate to use this method but that's mainly a personal taste issue than anything else (because I'd prefer using stored procedures and recognize that internal network traffic generally isn't the bottleneck in these kinds of transactions, though on-the-fly statement execution plan creation could be).
3. Override the OnUpdating for the adapter to alter the command sent based on which fields have actually changed. This is probably the closest in effect to the OLTP solution envisioned by Udi. This solution is problematic for me simply because I've never actually tried to do it and I'm not sure you can hook into the base adapter updates each execution. If you can't,analternative (in ADO.NET 2.0) would beto create a base class for the table adapters and create an alternative Update function in derived partial classes. In this case, you'd have "AcceptFineGrainedChanges" or some such function that you'd call. Once the alternative base class was created, custom programming per table adapter would be a matter of a couple moments. I've done something similar for using the designer for SyBase table adapters and it worked out pretty well. I'd have to actually try this to make sure it'd work though. Call thistwo half-solutions if you're feeling stern about it.
4. This last would be usefulif I have a relatively well-defined use case that isn't going to morph much or require stringent concurrency resolution. In this one, you deliberately break the one-for-one relationship from your dataset and database (i.e. one database table can be represented by multiple dataset tables). In Udi's concurrency example, the dataset would have a CustomerAddress table and a CustomerStatus table. Creating the dataset with custom selects would generate the tables pretty painlessly with appropriate paranoia. Now, this only really pushes his concern down a little, making it less likely to be an issue. It doesn't eliminate it. It'd probably handle most of the concurrency problems people are likely to run into. Or at least, push them out beyond where most people will ever experience it (not quite the same thing). It could be taken to a rediculous extreme where each field was it's own datatable (which is just silly, but I've seen sillier things happen) so a little balance and logical separation would be needed.

---

Varun Kumar