Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Saving modified data in gridview on clicking SaveButton
I am struggling to do a save button in C# Windows Form Application where I have a GridView and I want to save all modifications I do in GridView Table(I connected it to SQL). This is the data source for my database DataTable dtCustomers = new DataTable()
;
I did this so far and got stuck.
private void SaveButton_Click(object sender, EventArgs e)
{
string connectionString = Properties.Settings.Default.dbConnectionString;
SqlConnection con = new SqlConnection(connectionString);
using (con)
{
SqlCommand cmd = con.CreateCommand();
using (cmd)
{
cmd.CommandText = @"UPDATE CUSTOMERS SET " +
"CustomerId = @CustomerId, " +
"FirstName = @FirstName, " +
"LastName = @LastName, " +
"Email = @Email, " +
"Height = @Height, " +
"DateOfBirth = @DateOfBirth ";
cmd.Parameters.Add("@CustomerId", SqlDbType.Int, 5, "StudentID");
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 1, "Email");
cmd.Parameters.Add("@Height", SqlDbType.Int, 50, "Height");
cmd.Parameters.Add("@DateOfBirth", SqlDbType.Date, 10, "DateOfBith");
cmd.Connection = con;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.UpdateCommand = cmd;
// da.Update();
}
}
}
Thank you for any suggestion!
1 answer
Well, actually I didn't use SqlAdapter
to work with DB since I prefer using ORM or write everything by myself (without DataSet
, DataTable
, SqlAdapter
, etc) but I still can give some advices.
Here, you have mixed 2 separate questions:
- How to update data in a db from C#
- How to update UI with respect to changes in a db
When you split your "big" task apart it gets easier to find an answer separately.
Comments regarding the first "part":
- You have to open connection:
con.Open();
- To apply
Update
command you can useExecuteNonQuery
method:
cmd.ExecuteNonQuery(); // ignore the result for now
That's it!
And now we can move further to populating GridView
. Check docs to read how to do it via SqlDataAdapter class.
// update db
var connectionString = Properties.Settings.Default.dbConnectionString;
using (var con = new SqlConnection(connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = @"UPDATE CUSTOMERS SET " +
"CustomerId = @CustomerId, " +
"FirstName = @FirstName, " +
"LastName = @LastName, " +
"Email = @Email, " +
"Height = @Height, " +
"DateOfBirth = @DateOfBirth ";
cmd.Parameters.Add("@CustomerId", SqlDbType.Int, 5, "StudentID");
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 1, "Email");
cmd.Parameters.Add("@Height", SqlDbType.Int, 50, "Height");
cmd.Parameters.Add("@DateOfBirth", SqlDbType.Date, 10, "DateOfBith");
cmd.ExecuteNonQuery();
}
}
// update ui
var sql = "SELECT * FROM CUSTOMERS";
using (var con = new SqlConnection(connectionString))
{
con.Open();
var adapter = new SqlDataAdapter(sql, con);
var ds = new DataSet();
adapter.Fill(ds);
gridView1.DataSource = ds.Tables[0];
}
Side note: I've suggest you moving db related code into a separate project and call methods from there. With current approach when everything in UI-layer your code become messy very quickly.
0 comment threads