if you want to have the rows in your DataGirdView to be reflected in the database, you simply have to use the methods of the DataAdapter object.
The DataAdapter and DataReader objects, simply put, provide you with an easy and efficient way of reading from and writing to your database. Apart from that, they also do all of this without affecting the actual database table so that means everything is untouched until you say so.
For this example, imagine we have an SQL table named contacts with three columns namely fname, mname and lname.
To get things started, we'll need a function we could use to get the data from the "contacts" table.
protected DataSet GetData()
{
// our select query to obtain all the rows from the contacts table
string selectQuery = "SELECT * FROM contacts";
// Where the data from the underlying table will be stored
DataSet ds = new DataSet();
// Connect to the database and get the data from the "contacts" table
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlDataAdapter da = new SqlDataAdapter(selectQuery, conn))
{
da.Fill(ds, "contacts"); // Add the rows from the "contacts" table to our dataset
}
}
return ds;
}
You can then bind the DGV to the table stored in the returned dataset object by doing
DGV_Items.DataSource = GetData();
In your form_load event.
Now we have finished setting up our method for getting the data from the database, we now set up a method for manipulating all that data we now have.
protected void UpdateTable(DataSet ds)
{
SqlConnection conn = new SqlConnection(connString);
// Insert, update and delete queries
string updateQuery = "UPDATE contacts SET fname=@first,mname=@middle,lname=@last WHERE ID=@id";
string deleteQuery = "DELETE FROM contacts WHERE ID=@id";
string insertQuery = "INSERT INTO contacts VALUES(@first,@middle,@last)";
// Create the parameters for the queries above
SqlParameter[] insertParams = new SqlParameter[]
{
// the first parameter (e.g. @first) has to match with the declaration in the query
// the second parameter (e.g.SqlDbType.NVarChar) is the data type of the actual column in the source table
// the third paramter (e.g. 100) is the length of the data in the database table's column
// the last parameter (e.g. "fname") is the DataPropertyName of the source column which is
// basically the name of the database table column that the DGV column represents
new SqlParameter("@first", SqlDbType.NVarChar, 100, "fname"),
new SqlParameter("@middle", SqlDbType.NVarChar, 100, "mname"),
new SqlParameter("@last", SqlDbType.NVarChar, 100, "lname")
};
SqlParameter[] updateParams = new SqlParameter[]
{
new SqlParameter("@first", SqlDbType.NVarChar, 100, "fname"),
new SqlParameter("@middle", SqlDbType.NVarChar, 100, "mname"),
new SqlParameter("@last", SqlDbType.NVarChar, 100, "lname"),
new SqlParameter("@id", SqlDbType.Int, 100, "id")
};
SqlParameter[] DeleteParams = new SqlParameter[]
{
new SqlParameter("@id", SqlDbType.Int, 100, "id")
};
// Create the SqlCommand objects that will be used by the DataAdapter to modify the source table
SqlCommand insertComm = new SqlCommand(insertQuery, conn);
SqlCommand updateComm = new SqlCommand(updateQuery, conn);
SqlCommand deleteComm = new SqlCommand(deleteQuery, conn);
// Associate the parameters with the proper SqlCommand object
insertComm.Parameters.AddRange(insertParams);
updateComm.Parameters.AddRange(updateParams);
deleteComm.Parameters.AddRange(DeleteParams);
// Give the DataAdapter the commands it needs to be able to properly update your database table
SqlDataAdapter dataAdapter = new SqlDataAdapter()
{
InsertCommand = insertComm,
UpdateCommand = updateComm,
DeleteCommand = deleteComm
};
// A DataTable and a DataSet are basically the same. Except the DataSet is a collection of DataTables
// Here, you can see that we've accessed a specific DataTable in the DataSet.
// Calling the Update method executes the proper command based on the modifications to the specified
// DataTable object then commits these changes to the database
dataAdapter.Update(ds.Tables["contacts"]);
}
The above method will handle all the data manipulation. It will be acting based on the changes done to the DataTable object bound to your DGV. Finally, you can call all the methods we've made in your update button's event handler.
private void Btn_Update_Click(object sender, EventArgs e)
{
// Grab the DGV's data source which contains the information shown in the DGV
DataSet ds = (DataSet)dgv_items.DataSource;
// Have any updates to the said dataset committed to the database
UpdateTable(ds);
// rebind the DGV
dgv_items.DataSource = GetData();
}
EDIT
Along with the suggestions from Crowcoder, here is a better way to write all of the stuff I wrote above:
/// <summary>
/// A collection of methods for easy manipulation of the data in a given SQL table
/// </summary>
class DBOps
{
// The connection string contains parameters that dictate how we connect to the database
private string connString = ConfigurationManager.ConnectionStrings["contactsConnectionString"].ConnectionString;
// The table the instance of the class will be interacting with
private string srcTable;
// The SqlConnection Object that we will be using to connect to the database
SqlConnection conn;
// The DataAdapter object that we will be using to interact with our database
SqlDataAdapter da;
// The DataSet that we will be storing the data retrieved from the database
DataSet ds;
// The queries we would be using to manipulate and interact with the data in the database
private string selectQuery;
private string updateQuery;
private string deleteQuery;
private string insertQuery;
// The collection of parameters for the queries above
private SqlParameter[] insertParams;
private SqlParameter[] updateParams;
private SqlParameter[] DeleteParams;
// The command objects that will be used by our data adapter when
// interacting with the database
private SqlCommand insertComm;
private SqlCommand updateComm;
private SqlCommand deleteComm;
/// <summary>
/// Initialize a new instance of the DBOps class
/// </summary>
/// <param name="tableName">The name of the table that the object will be interacting with</param>
public DBOps(string tableName)
{
// Initialize the SqlConnection object
conn = new SqlConnection(connString);
// Initialize our collection of DataTables
ds = new DataSet();
srcTable = tableName;
// initialize the query strings
selectQuery = string.Format("SELECT * FROM {0}", srcTable);
insertQuery = string.Format("INSERT INTO {0}(fname, mname, lnmae) VALUES(@first, @middle, @last", srcTable);
updateQuery = string.Format("UPDATE {0} SET fname=@first, mname=@middle, lname=@last WHERE ID=@id", srcTable);
deleteQuery = string.Format("DELETE FROM {0} WHERE ID=@id", srcTable);
// Initialize the collection of parameters for each query above
insertParams = new SqlParameter[]
{
// new SqlParameter(@paramName, paramDataType, paramValueLength, DGVDataPropertyName);
new SqlParameter("@first", SqlDbType.NVarChar, 100, "fname"),
new SqlParameter("@middle", SqlDbType.NVarChar, 100, "mname"),
new SqlParameter("@last", SqlDbType.NVarChar, 100, "lname")
};
updateParams = new SqlParameter[]
{
new SqlParameter("@first", SqlDbType.NVarChar, 100, "fname"),
new SqlParameter("@middle", SqlDbType.NVarChar, 100, "mname"),
new SqlParameter("@last", SqlDbType.NVarChar, 100, "lname"),
new SqlParameter("@id", SqlDbType.Int, 100, "id")
};
DeleteParams = new SqlParameter[]
{
new SqlParameter("@id", SqlDbType.Int, 100, "id")
};
// Initialize the SqlCommand objects that will be used by the DataAdapter to modify the source table
insertComm = new SqlCommand(insertQuery, conn);
updateComm = new SqlCommand(updateQuery, conn);
deleteComm = new SqlCommand(deleteQuery, conn);
// Associate the parameters with the proper SqlCommand object
insertComm.Parameters.AddRange(insertParams);
updateComm.Parameters.AddRange(updateParams);
deleteComm.Parameters.AddRange(DeleteParams);
// Give the DataAdapter the commands it needs to be able to properly update your database table
da = new SqlDataAdapter()
{
InsertCommand = insertComm,
UpdateCommand = updateComm,
DeleteCommand = deleteComm
};
}
/// <summary>
/// Retrieve the data from the SQl table
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds = new DataSet();
// Connect to the database and get the data from the "contacts" table
using (conn)
{
conn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(selectQuery, conn))
{
da.Fill(ds); // Add the rows from the "contacts" table to our dataset
}
}
return ds;
}
/// <summary>
/// Commit the changes present in the object's DataSet to the Database
/// </summary>
public void UpdateData(DataSet ds)
{
// Calling the Update method executes the proper command based on the modifications to the specified
// DataTable object
da.Update(ds.Tables[srcTable]);
}
To use this class, just create an instance of it by writing:
DBOps ops = new DBOps("contacts");
In your Update Button's click event handler, you can commit all the changes done to your DGV's underlying data source by calling the UpdateData method.
private void Btn_Update_Click(object sender, EventArgs e)
{
// Grab the DGV's data source which contains the information shown in the DGV
DataSet ds = (DataSet)dgv_items.DataSource;
// Have any updates to the said dataset committed to the database
ops.UpdateData(ds);
// rebind the DGV
dgv_items.DataSource = ops.GetData();
}
To sum it all up:
- The
DataAdapter and DataReader objects provide you methods that will allow you to interact with the database in safe, efficient and easy manner.
DataTable and DataSet are pretty much the same. Except the DataTable is only one table and the DataSet is a collection of DataTables. On the other hand, each of these also have specific methods that the other doesn't have.
