none
get data after running an sql command. RRS feed

  • Question

  •  

     

    Hi,

     

    I am executing a select sql statement and i want to retrieve the data. Since i am just retrieving one column, I dont want to put it into a dataSet, so than I found I can do it using SQLDataReader, but I have to run a while loop to get the value. Is there a way to get the data without running a loop.

     

    Here is how I am getting the value, using SqlDataReader's while loop.

     

    tbl_userSelect="select userID from tbl_user where user='alex'";

    SqlCommand cmd = new SqlCommand(tbl_userSelect,con);

    SqlDataReader reader = null;

    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    while(reader.Read())

    Response.Write("==============>"+ reader.GetValue(0));

     

     

     

     

     

    Thank you.

    Thursday, June 21, 2007 8:52 PM

Answers

  • ExecuteReader - Used for reading through the results of your query ,one record at a time. Forward access only. Once youve looked at a record you cant go back to look at it again.

     

    ExecuteNonQuery - Used to execute sql that doesnt return any rows. Updates, inserts and deletes mainly.

     

    ExecuteScalar - Returns a single discrete value. If the SQL would return multiple results then ONLY the first records value will be returned.

     

    ExecuteXmlReader - Once again a forward only readonly stream of data in XML format. Basically the same thing as ExecuteReader except that its returns an XmlReader instead of a DataReader object.

     

    For what you want to do you are pretty much stuck using a dataadapter and a datatable / dataset.

    IE

     

    string sql = "select id from mytable";

    da = new DataAdapter(sql, connection);

    Datatable myTable =  new DataTable();

    if ( da.Fill(myTable) != 0 )

    {

        foreach ( Datarow dr in myTable.Rows)

        {

           Response.Write(dr[0].ToString());

        }

    }

    Friday, June 22, 2007 3:11 PM
  • First

    cmd.ExecuteReader() returns a DataReader, then you can use read() method of DataReader in while loop to get individual records and you can use get methods to get column values. This is basically used for executing select statements.

     

    cmd.ExecuteNonQuery() this method to execute insert, update and delete queries basically they are called commands. It returns an integer value indicating the number of rows affected by the command (query).

     

    cmd.ExecuteXmlReader() method you can use with SQL2005 which supports "XQuery". It returns a DataReader which you can use to get a XmlReader to handle Xml data.

     

    That's all.

    Friday, June 22, 2007 3:16 PM
  • cmd.ExecuteReader() = Get one row at a time and maintains the connection until you close the reader or connection

    cmd.ExecuteNonQuery() = Get the number of rows affected for an update etc.. No use to you here.

    cmd.ExecuteScalar() = Get the first column of the first row value as an object.

    cmd.ExecuteXmlReader() = Get a XmlReader object you can load into a dataset and extract from the dataset the datatable if you want.

     

    I am talking .net 1.1, .net 2.0 has some additional methods, one allowing the loading of a dataset from a reader.

     

    The Enterprise Library data wrappers include a cmd.ExecuteDataSet which returns a dataset.

     

    Loading a reader into a dataset isn't too shabby either just note it makes the data read only Smile.

     

    Depends on your version of .net

     

    You could use the reader to load the values into a collection and from then on pass the collection around if this makes more sense to you.

    Friday, June 22, 2007 3:16 PM

All replies

  • If you're looking for one column and a single row, the method you're looking for is SQLCommand.ExecuteScalar(). This returns the first column of the first row. Ideal for things like count results.

    Thursday, June 21, 2007 10:48 PM
  • tbl_userSelect="select userID from tbl_user where user='alex'";

    SqlCommand cmd = new SqlCommand(tbl_userSelect,con);

      

    Response.Write("==============>"+ cmd.ExecuteScalar.ToString();

     

    Like was said ExecuteScalar for a single value and cast to the type.

     

    -paul

    Friday, June 22, 2007 12:55 AM
  • Thank you ALL!

     

    --Want to make sure what those Execute stats do:--

    cmd.ExecuteReader() = gets all the rows, and can only go forward. and can be read using a while loop

    cmd.ExecuteNonQuery() = also gets all the rows? and do I need to place into a dataset, in order to read it? " or what it is used for?"

    cmd.ExecuteXmlReader() = gets data using XML format .. and do I also have to put it to the dataset in order to read it? or is it used for something else.

     

    Thank you for your help,

     

     

     

    Friday, June 22, 2007 2:20 PM
  • ExecuteReader - Used for reading through the results of your query ,one record at a time. Forward access only. Once youve looked at a record you cant go back to look at it again.

     

    ExecuteNonQuery - Used to execute sql that doesnt return any rows. Updates, inserts and deletes mainly.

     

    ExecuteScalar - Returns a single discrete value. If the SQL would return multiple results then ONLY the first records value will be returned.

     

    ExecuteXmlReader - Once again a forward only readonly stream of data in XML format. Basically the same thing as ExecuteReader except that its returns an XmlReader instead of a DataReader object.

     

    For what you want to do you are pretty much stuck using a dataadapter and a datatable / dataset.

    IE

     

    string sql = "select id from mytable";

    da = new DataAdapter(sql, connection);

    Datatable myTable =  new DataTable();

    if ( da.Fill(myTable) != 0 )

    {

        foreach ( Datarow dr in myTable.Rows)

        {

           Response.Write(dr[0].ToString());

        }

    }

    Friday, June 22, 2007 3:11 PM
  • First

    cmd.ExecuteReader() returns a DataReader, then you can use read() method of DataReader in while loop to get individual records and you can use get methods to get column values. This is basically used for executing select statements.

     

    cmd.ExecuteNonQuery() this method to execute insert, update and delete queries basically they are called commands. It returns an integer value indicating the number of rows affected by the command (query).

     

    cmd.ExecuteXmlReader() method you can use with SQL2005 which supports "XQuery". It returns a DataReader which you can use to get a XmlReader to handle Xml data.

     

    That's all.

    Friday, June 22, 2007 3:16 PM
  • cmd.ExecuteReader() = Get one row at a time and maintains the connection until you close the reader or connection

    cmd.ExecuteNonQuery() = Get the number of rows affected for an update etc.. No use to you here.

    cmd.ExecuteScalar() = Get the first column of the first row value as an object.

    cmd.ExecuteXmlReader() = Get a XmlReader object you can load into a dataset and extract from the dataset the datatable if you want.

     

    I am talking .net 1.1, .net 2.0 has some additional methods, one allowing the loading of a dataset from a reader.

     

    The Enterprise Library data wrappers include a cmd.ExecuteDataSet which returns a dataset.

     

    Loading a reader into a dataset isn't too shabby either just note it makes the data read only Smile.

     

    Depends on your version of .net

     

    You could use the reader to load the values into a collection and from then on pass the collection around if this makes more sense to you.

    Friday, June 22, 2007 3:16 PM
  • Thank you so much everyone for your help!!
    Friday, June 22, 2007 5:28 PM