/// <summary>
/// This function bind the Cached Students and Student Emails dataset.
/// </summary>
private void GetDataFromCache()
{
if (Cache["DATASET"] != null)
{
DataSet dataSet1 = (DataSet)Cache["DATASET"];
GrdvStudents.DataSource = dataSet1.Tables["Students"]; //First DataSet
GrdvStudents.DataBind();
GrdvStudentEmails.DataSource = dataSet1.Tables["Emails"]; //Second DataSet
GrdvStudentEmails.DataBind();
}
}
/// <summary>
/// This function is responsible for -
/// - Seting up the connection to the DB
/// - Using SqlDataAdapter to fetch the Students and Student Emails DataSet
/// - Cashe the DataSet for ofline use (disconnected from DB)
/// - Bind the Data Set to the GridView
/// </summary>
private void GetDataFromDB()
{
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection connection1 = new SqlConnection(ConnectionString))
{
// First set the Query in the SqlDataAdapter.
string strSelectQuery = "SELECT * FROM [Students]; SELECT * FROM [Emails]";
SqlDataAdapter dataAdapter1 = new SqlDataAdapter(strSelectQuery, connection1);
// Create DataSet to hold the data set returned by the SqlDataAdapter.
DataSet dataSet1 = new DataSet();
dataAdapter1.Fill(dataSet1);
// It is always good to name the tables with approtriate names to make the code more readable.
dataSet1.Tables[0].TableName = "Students";
dataSet1.Tables[1].TableName = "Emails";
// Identify and mark the Primarykey data column in the tables.
// When you identify the data column as the PrimaryKey, the table automatically sets AllowDBNull property of the column to false.
// Also sets the Unique property of the data column to true.
dataSet1.Tables["Students"].PrimaryKey = new DataColumn[] { dataSet1.Tables["Students"].Columns["Id"] };
dataSet1.Tables["Emails"].PrimaryKey = new DataColumn[] { dataSet1.Tables["Emails"].Columns["Id"] };
dataSet1.Tables["Students"].Columns["Id"].AutoIncrement = true;
dataSet1.Tables["Emails"].Columns["Id"].AutoIncrement = true;
// Get the largest primery key
SqlCommand getLargestStudentsPrimeryKey = new SqlCommand("SELECT MAX(Id) FROM [Students]", connection1);
SqlCommand getLargestStudentEmailsPrimeryKey = new SqlCommand("SELECT MAX(Id) FROM [Emails]", connection1);
connection1.Open();
long studentsTableSeedValue = 0;
long studentEmailsTableSeedValue = 0;
long.TryParse(getLargestStudentsPrimeryKey.ExecuteScalar().ToString(), out studentsTableSeedValue);
long.TryParse(getLargestStudentEmailsPrimeryKey.ExecuteScalar().ToString(), out studentEmailsTableSeedValue);
dataSet1.Tables["Students"].Columns["Id"].AutoIncrementSeed = ++studentsTableSeedValue;
dataSet1.Tables["Emails"].Columns["Id"].AutoIncrementSeed = ++studentEmailsTableSeedValue;
// Identify the student id column in the student emails table as readonly. So that user cannot change it using the edit option in grid view.
dataSet1.Tables["Emails"].Columns["StudentId"].AllowDBNull = false;
dataSet1.Tables["Emails"].Columns["StudentId"].ReadOnly = true;
// DataSet is cached for 24 hours in the memory. with label "DATASET"
Cache.Insert("DATASET", dataSet1, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration);
// Set the data source and bind the data set for the Grid view control.
GrdvStudents.DataSource = dataSet1.Tables["Students"]; //First DataSet
GrdvStudents.DataBind();
GrdvStudentEmails.DataSource = dataSet1.Tables["Emails"]; //Second DataSet
GrdvStudentEmails.DataBind();
}
}
