/// <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(); } }