protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection connection1 = new SqlConnection(ConnectionString))
{
string strSelectQuery = "SELECT * FROM [Students]; SELECT * FROM [Emails]";
SqlDataAdapter dataAdapter1 = new SqlDataAdapter(strSelectQuery, connection1);
DataSet dataSet1 = new DataSet();
dataAdapter1.Fill(dataSet1);
dataSet1.Tables[0].TableName = "Students";
dataSet1.Tables[1].TableName = "StudentEmails";
Session["DATASET"] = dataSet1;
GrdvStudents.DataSource = from dataRowStudent in dataSet1.Tables["Students"].AsEnumerable()
// Here the program defines a Student class and its properties and,
// a collection of student object is created for each of the database table,
// where table colume are mapped to the Student Class properties.
select new Student
{
Id = Convert.ToInt32(dataRowStudent["Id"]),
FirstName = dataRowStudent["FirstName"].ToString(),
LastName = dataRowStudent["LastName"].ToString(),
Emails = GetStudentEmails(Convert.ToInt32(dataRowStudent["Id"])),
};
GrdvStudents.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection connection1 = new SqlConnection(ConnectionString))
{
SqlDataAdapter dataAdapter1 = new SqlDataAdapter("SELECT * FROM Students", connection1);
DataSet dataSet1 = new DataSet();
dataAdapter1.Fill(dataSet1); // Fill method, opens the connection, executes the command, fills the DataSet and then immediatly closes the connection. All in one.
GridView1.DataSource = dataSet1; // this SqlDataSet is available with no active open connection to data source. Unlike the SqlDataReader, which needs active open connection to data source.
GridView1.DataBind();
}
using (SqlConnection connection2 = new SqlConnection(ConnectionString))
{
SqlDataAdapter dataAdapter2 = new SqlDataAdapter("spGetStudentsAndEmails", connection2); // This stored procedure returns two datasets - Students and Emails.
dataAdapter2.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dataSet2 = new DataSet();
dataAdapter2.Fill(dataSet2); // Fill method, opens the connection, executes the command, fills the DataSet and then immediatly closes the connection. All in one.
dataSet2.Tables[0].TableName = "Students";
dataSet2.Tables[1].TableName = "Emails";
GrdvStudents.DataSource = dataSet2.Tables["Students"]; //First DataSet.
GrdvStudents.DataBind();
GrdvEmails.DataSource = dataSet2.Tables["Emails"]; //Second DataSet
GrdvEmails.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// In this example a strongly typed DataSet is used.
// In a strongly typed DataSet the table columns becomes properties and type (class) associated with each of the table columns is known at design time
// A seperate defination of the class for the DataSet table is not needed, also do not have to map the class properties to the DataSet table columns, Which is required for not strongly typed DataSet.
// This makes development much easier and reduces errors in coding.
StudentDataSetTableAdapters.StudentsTableAdapter StudentsTableAdapater = new StudentDataSetTableAdapters.StudentsTableAdapter();
StudentDataSet.StudentsDataTable StudentDataTable = new StudentDataSet.StudentsDataTable();
StudentDataSetTableAdapters.EmailsTableAdapter EmailsTableAdapter = new StudentDataSetTableAdapters.EmailsTableAdapter();
StudentDataSet.EmailsDataTable EmailDataTable = new StudentDataSet.EmailsDataTable();
Session["STUDENTDATATABLE"] = StudentDataTable;
Session["EMAILDATATABLE"] = EmailDataTable;
StudentsTableAdapater.Fill(StudentDataTable);
EmailsTableAdapter.Fill(EmailDataTable);
GrdvStudents.DataSource = from student in StudentDataTable
// As it is a strongly typed DataSet, the type (Class) of table is known at design time and,
// all table columns are properties.
select new
{
student.Id,
student.FirstName,
student.LastName,
studentEmail = GetStudentEmails(student.Id), // New property can be defined if additional information needs to be added as one more column programatically.
};
GrdvStudents.DataBind();
}
}
/// <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();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection connection1 = new SqlConnection(ConnectionString))
{
connection1.Open();
SqlCommand command1 = new SqlCommand("SELECT * FROM students", connection1);
using (SqlDataReader rdr1 = command1.ExecuteReader()) //Note that new cannot be used to create a SqlDataReader object. ExecuteReader return object of SqlDataReader.
{
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("FirstName");
table.Columns.Add("LastName");
table.Columns.Add("FullName");
while (rdr1.Read())
{
DataRow dataRow = table.NewRow();
dataRow["ID"] = rdr1["Id"];
dataRow["FirstName"] = rdr1["FirstName"];
dataRow["LastName"] = rdr1["LastName"];
dataRow["FullName"] = rdr1["FirstName"] + " " + rdr1["LastName"];
table.Rows.Add(dataRow);
}
GridView1.DataSource = table;
GridView1.DataBind();
}
SqlCommand command2 = new SqlCommand("SELECT * FROM students; SELECT * FROM Emails", connection1); // there are two SQL queries that return two seperate data sets.
using (SqlDataReader rdr2 = command2.ExecuteReader()) // both data sets are pointed by a single DataReader object.
{
GrdvStudents.DataSource = rdr2; //first result set displayed.
GrdvStudents.DataBind();
rdr2.NextResult(); //this will make the result move to the next result object.
GrdvEmails.DataSource = rdr2; //second result set displayed.
GrdvEmails.DataBind();
}
}
}
protected void BtnSeachStudent_Click(object sender, EventArgs e)
{
if (Session["DATASET"] != null)
{
DataSet dataSet1 = (DataSet)Session["DATASET"];
if (string.IsNullOrEmpty(TbxSearchStudent.Text))
{
GrdvStudents.DataSource = from dataRowStudent in dataSet1.Tables["Students"].AsEnumerable()
// Here the program defines a Student class and its properties and,
// a collection of student object is created for each of the database table,
// where table colume are mapped to the Student Class properties.
select new Student
{
Id = Convert.ToInt32(dataRowStudent["Id"]),
FirstName = dataRowStudent["FirstName"].ToString(),
LastName = dataRowStudent["LastName"].ToString(),
Emails = GetStudentEmails(Convert.ToInt32(dataRowStudent["Id"])),
};
GrdvStudents.DataBind();
}
else
{
GrdvStudents.DataSource = from dataRowStudent in dataSet1.Tables["Students"].AsEnumerable()
where dataRowStudent["FirstName"].ToString().ToUpper().StartsWith(TbxSearchStudent.Text.ToUpper())
// Here the program defines a Student class and its properties and,
// a collection of student object is created for each of the database table,
// where table colume are mapped to the Student Class properties.
select new Student
{
Id = Convert.ToInt32(dataRowStudent["Id"]),
FirstName = dataRowStudent["FirstName"].ToString(),
LastName = dataRowStudent["LastName"].ToString(),
Emails = GetStudentEmails(Convert.ToInt32(dataRowStudent["Id"])),
};
GrdvStudents.DataBind();
}
}
}
protected void BtnLoadData_Click(object sender, EventArgs e)
{
if (Cache["Data"] == null)
{
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection connection2 = new SqlConnection(ConnectionString))
{
SqlDataAdapter dataAdapter2 = new SqlDataAdapter("spGetStudentsAndEmails", connection2); // This stored procedure returns two datasets - Students and Emails.
dataAdapter2.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dataSet2 = new DataSet();
dataAdapter2.Fill(dataSet2); // Fill method, opens the connection, executes the command, fills the DataSet and then immediatly closes the connection. All in one.
dataSet2.Tables[0].TableName = "Students";
dataSet2.Tables[1].TableName = "Emails";
Cache["Data"] = dataSet2; // Cache is a global object. You can label your DataSet in the Cashe using a name (eg "Data").
// Every DataSet Stored in the Cache with a label is kept seperately and retrived with the help of label.
GrdvStudents.DataSource = dataSet2.Tables["Students"]; //First DataSet.
GrdvStudents.DataBind();
GrdvEmails.DataSource = dataSet2.Tables["Emails"]; //Second DataSet
GrdvEmails.DataBind();
LblMessage.Text = "Data Loaded from the Database";
}
}
else
{
DataSet dataSet1 = (DataSet)Cache["Data"]; // DateSet retrived with the help of the label, should be typecasted to the object type of the data.
GrdvStudents.DataSource = dataSet1.Tables["Students"]; //First DataSet.
GrdvStudents.DataBind();
GrdvEmails.DataSource = dataSet1.Tables["Emails"]; //Second DataSet
GrdvEmails.DataBind();
LblMessage.Text = "Data Loaded from the Cache";
}
}
protected void BtnSeachStudent_Click(object sender, EventArgs e)
{
if (Session["STUDENTDATATABLE"] != null)
{
StudentDataSet.StudentsDataTable StudentDataTable = (StudentDataSet.StudentsDataTable)Session["STUDENTDATATABLE"];
if (string.IsNullOrEmpty(TbxSearchStudent.Text))
{
GrdvStudents.DataSource = from student in StudentDataTable
// As it is a strongly typed DataSet, the type (Class) of table is known at design time and,
// all table columns are properties.
select new
{
student.Id,
student.FirstName,
student.LastName,
studentEmail = GetStudentEmails(student.Id), // New property can be defined if additional information needs to be added as one more column programatically.
};
GrdvStudents.DataBind();
}
else
{
GrdvStudents.DataSource = from student in StudentDataTable
where student.FirstName.ToUpper().StartsWith(TbxSearchStudent.Text.ToUpper())
// As it is a strongly typed DataSet, the type (Class) of table is known at design time and,
// all table columns are properties.
select new
{
student.Id,
student.FirstName,
student.LastName,
studentEmail = GetStudentEmails(student.Id), // New property can be defined if additional information needs to be added as one more column programatically.
};
GrdvStudents.DataBind();
}
}
}