locked
SQL: Best way to read a row from a DB? RRS feed

  • Question

  • Hi,

    I'm new to C#. Could someone tell me the best way to do the folowing:

     - Read a specific row from a DB table
     - Get a specific columns information from that data

    Do I read the data into a record set? Or is better to use while() loop and an SqlDataReader to read the information?

    Thanks.

    Thursday, April 30, 2009 3:57 PM

Answers

  • The answer to this question completely depends on what you're going to do with the data. Personally, I like the SqlDataReader approach, but I've also been known to use DataSets and DataTables, especially when binding to a grid or some other tabular display.  The block I typically use looks like this. (I have a few variations, but this is the general idea)

    using (SqlConnection cn = new SqlConnection(connectionstring))
    {
         cn.Open();
         using (SqlCommand cm = cn.CreateCommand())
         {
             cm.CommandText = "Sql_WidgetGetById";
             cm.CommandType = CommandType.StoredProcedure;
             cm.Parameters.AddWithValue("@Id", id);
    
             using (SqlDataReader dr = cm.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     Widget w = new Widget();
                     w.Value = dr.GetString(dr.GetOrdinal("Value"));
                     widgetList.Add(w);
                 }
             }
         }
    }



    David Morton - http://blog.davemorton.net/
    • Proposed as answer by JohnGrove Thursday, April 30, 2009 4:52 PM
    • Marked as answer by Jimmy Collins Friday, May 1, 2009 8:45 AM
    Thursday, April 30, 2009 4:37 PM

All replies

  • The answer to this question completely depends on what you're going to do with the data. Personally, I like the SqlDataReader approach, but I've also been known to use DataSets and DataTables, especially when binding to a grid or some other tabular display.  The block I typically use looks like this. (I have a few variations, but this is the general idea)

    using (SqlConnection cn = new SqlConnection(connectionstring))
    {
         cn.Open();
         using (SqlCommand cm = cn.CreateCommand())
         {
             cm.CommandText = "Sql_WidgetGetById";
             cm.CommandType = CommandType.StoredProcedure;
             cm.Parameters.AddWithValue("@Id", id);
    
             using (SqlDataReader dr = cm.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     Widget w = new Widget();
                     w.Value = dr.GetString(dr.GetOrdinal("Value"));
                     widgetList.Add(w);
                 }
             }
         }
    }



    David Morton - http://blog.davemorton.net/
    • Proposed as answer by JohnGrove Thursday, April 30, 2009 4:52 PM
    • Marked as answer by Jimmy Collins Friday, May 1, 2009 8:45 AM
    Thursday, April 30, 2009 4:37 PM
  • using(SqlConnection conn = new SqlConnection(connectionString))
    {
    conn.Open();
    
    String query = "Select * from table where column1 = value11";
    
    SqlCommand cmd = new SqlCommand(query, conn);
    SqlDataReader dr = cmd.ExecuteReader();
    
    while(dr.Read())
    {
    /// dr["ColumnName1"]
    /// dr["ColumnName2"]
    
    }
    
    dr.Close();
    
    }


    Thanks, A.m.a.L | [Remember to click "mark as answered" when you get a correct reply to your question]
    Thursday, April 30, 2009 5:02 PM
  • A DataAdapter will also call the DataReader internally when you fill the data into a dataset. 
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Thursday, April 30, 2009 5:27 PM
  • Using David Morton's approach is a fast way to fetch results using the DataReader.

    David Sceppa in his acclaimed book on ADO.NET has a paragraph titled, "Fetching Faster" in his chapter on "Querying Your Database" which he mentions improving performance of the code in two ways.

    1.) Using ordinal-based lookups
    2.) Using the appropriate type-specific Get method

    //Example
    OleDbDataReader dr = cmd.ExecuteReader();
    int customerID = dr.GetOrdinal("CustomerID");
    int companyName = dr.GetOrdinal("CompanyName");
    while (dr.Read())
        Console.WriteLine("{0} - {1}", dr.GetString(customerID), dr.GetString(companyName));
    Console.ReadLine();

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 30, 2009 6:01 PM
  • Thanks a lot guys, you've been a great help
    Friday, May 1, 2009 8:45 AM