How I can use SqlDataReader?

Hi..

Every time I want to read any record from data base I read it in dataset for example:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataAdapter adp =newSqlDataAdapter();

adp.SelectCommand = cmd;

DataSet ds =newDataSet();

adp.Fill(ds,"UserID");

foreach (DataRow drin ds.Tables["UserID"].Rows)

{

user_type = dr[0].ToString();

Session.Add("User_AuthorityID", user_type);

.......

Is there easier way to read data from data base?

How I can use SqlDataReader to do that?

Thanks..

it looks like your returning a single value:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

string value = cmd.ExecuteScalar().ToString();

or

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

Session.Add("User_AuthorityID",cmd.ExecuteScalar().ToString(), ;

|||

Example data reader:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataReader reader = cmd.ExecuteReader();

string value =string.Empty;

while (reader.Read())

{

value = reader["User_AuthorityID"].ToString();

}

Session.Add("User_AuthorityID", value);

|||

Thanks for that but what should I use if I have more than one value reture from the query?

|||

You can go ahead with the above approach suggested by David.

manal.m.k:

what should I use if I have more than one value reture from the query?

This is pretty straight forward. In the post the while loop goes through all the rows that are returned from the query in your reader. The below example can fetch the column values for each column returned in a row.

while (reader.Read()){ value1 = reader["column1"].ToString(); value2 = reader["column2"].ToString(); . . . valueN = reader["columnN"].ToString();}