Example 1:
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
Model.BlogUsers obj = new Model.BlogUsers();
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
string cmdtext = "";
cmdtext = "select * from blogUsers where username ='" + Login1.UserName + "' and password='" + Login1.Password + "'";
DataSet dt;
using (SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand(cmdtext, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
dt = new DataSet();
da.Fill(dt);
}
// In above block the connection is closed, However the dataset dt contains the data. Because dataAdapter is a connection less.
if (dt.Tables[0].Rows.Count > 0)
Server.Transfer("Welcome.aspx");
}
Example 2:
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
Model.BlogUsers obj = new Model.BlogUsers();
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
string cmdtext = "";
cmdtext = "select * from blogUsers where username =@username and password=@password";
using (SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand(cmdtext, con);
cmd.Parameters.AddWithValue("@username", Login1.UserName);
cmd.Parameters.AddWithValue("@password", Login1.Password);
con.Open();
SqlDataReader sr = cmd.ExecuteReader();
// The below read() works only within the "using" block. As its scope is connection oriented.
if (sr.Read())
Server.Transfer("Welcome.aspx");
}
}
SQLdataReader - Connection oriented. Dipose the data in the reader if connected in closed.
SQLDataAdapter - Acts as a source to bind data to dataset
DataSet - Collection of datatables. Holds data even if Connection is closed.
DataTable - Same like the dataset.
