SqlDataAdapter issue

julius · April 12th, 2009, 09:28 PM

Hi people. I am currently facing a problem over here. Coming to know that SqlDatareader does not support paging, but SqlDataAdapter supports, i tried to convert the codes and i am quite stucked.

I want to do a count for the records in my database, but i did it using SqlDatareader, and i am not sure how to implement it with SqlDataAdapter.

which is this part
<hr />
string what = null;
while (reader.Read())
{
what = reader["TotalMessages"].ToString();
lblNoOfMsg.Text = "(" + what + ")";
}
reader.Close();
con.Close();
}
<hr />
USING SqlDataAdapter

privatevoid CountNoOfMessages()
{
string UserEmail = Session["UserEmail"].ToString();
string strConString = ConfigurationManager.ConnectionStrings["SocialSystemConnectionString"].ConnectionString;
SqlConnection myConnect = newSqlConnection(strConString);
//Create Adapter
string strCommandText = "SELECT COUNT(MessageID) AS TotalMessages FROM [Message] WHERE [Message].RecipientEmail= @Email AND TypeID = 'Inbox'";
SqlDataAdapter myAdapter = newSqlDataAdapter(strCommandText, myConnect);
//Create Dataset to store results of query
DataSet myDS = newDataSet();
myAdapter.Fill(myDS, "Message");
GridView2.DataSource = myDS.Tables["Message"];
GridView2.DataBind();

}

USING SqlDataReader

privatevoid CountNoOfMessages(){
SqlConnection con = newSqlConnection(strConString);
SqlCommand cmd = newSqlCommand("SELECT COUNT(MessageID) AS TotalMessages FROM [Message] WHERE [Message].RecipientEmail= @Email AND TypeID = 'Inbox'", con);
cmd.Parameters.Add("@Email", SqlDbType.VarChar);
cmd.Parameters["@Email"].Value = UserEmail;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
string what = null;
while (reader.Read())
{
what = reader["TotalMessages"].ToString();
lblNoOfMsg.Text = "(" + what + ")";
}
reader.Close();
con.Close();
}

Lee Dumond · April 12th, 2009, 11:15 PM

You don't need to use an adapter OR a reader for this.

Since you only need a count, which is a single number, you can just retrieve a scalar value.

Code:

private void CountNoOfMessages()
{
  SqlConnection con = newSqlConnection(strConString);
  SqlCommand cmd = newSqlCommand("SELECT COUNT(MessageID) AS   TotalMessages FROM [Message] WHERE [Message].RecipientEmail= @Email AND TypeID = 'Inbox'", con);
  cmd.Parameters.Add("@Email", SqlDbType.VarChar);
  cmd.Parameters["@Email"].Value = UserEmail;
  con.Open();
   lblNoOfMsg.Text = cmd.ExecuteScalar.ToString();
  con.Close();
}

julius · April 13th, 2009, 12:31 AM

Infact, another error occurs....

privatevoid CountNoOfMessages()
{
string UserEmail = Session["UserEmail"].ToString();
string strConString = ConfigurationManager.ConnectionStrings["SocialSystemConnectionString"].ConnectionString;
SqlConnection con = newSqlConnection(strConString);
SqlCommand cmd = newSqlCommand("SELECT COUNT(MessageID) AS TotalMessages FROM [Message] WHERE [Message].RecipientEmail= @Email AND TypeID = 'Inbox'", con);
cmd.Parameters.Add("@Email", SqlDbType.VarChar);
cmd.Parameters["@Email"].Value = UserEmail;
con.Open();
lblNoOfMsg.Text = cmd.ExecuteScalar.ToString();
con.Close();
}

CS0119: 'System.Data.Common.DbCommand.ExecuteScalar()' is a 'method', which is not valid in the given context

Lee Dumond · April 13th, 2009, 12:38 AM

Whoops, sorry!

lblNoOfMsg.Text = cmd.ExecuteScalar().ToString();