none
Proper way to use ExecuteReader() ? RRS feed

  • Question

  • I'm reviewing some code I wrote with VS2003/C#.  Now that I'm using VS2005, I'm wondering if it's the most efficient.  Specifically, do I need to go to all the trouble I've done with my "GetTable()" method?

    Robert W.

    Here's the code:


    public static DataTable DoSPGetDataTable(SqlConnection aConn, string procName, params SqlParameter[] theParams)
        {
          SqlDataReader reader;
          DataTable dataTable;

          SqlCommand aComm = new SqlCommand(procName, aConn);
          aComm.CommandType = CommandType.StoredProcedure;

          foreach(SqlParameter aParam in theParams)
          {
            aComm.Parameters.Add(aParam);
          }

          try
          {
            aConn.Open();
            reader = aComm.ExecuteReader();
            dataTable = GetTable(reader);
          }

          catch (Exception ex)
          {
            Debug.Fail("Error running SQL Query: " + ex.Message, "DBTools.DoSPGetDataTable");
            throw;
          }

          finally
          {
            aConn.Close();
          }

          return dataTable;
        }


        public static DataTable GetTable(SqlDataReader reader)
        {
          DataTable dataSchema = reader.GetSchemaTable();
          DataTable dataTable = new DataTable();
          DataColumn column;
          DataRow row;
          ArrayList arrayList = new ArrayList();

          for (int i = 0; i < dataSchema.Rows.Count; i ++)
          {
            column = new DataColumn();

            if (! dataTable.Columns.Contains(dataSchema.RowsIdea["ColumnName"].ToString()))
            {
              column.ColumnName = dataSchema.RowsIdea["ColumnName"].ToString();
              column.Unique = Convert.ToBoolean(dataSchema.RowsIdea["IsUnique"]);
              column.AllowDBNull = Convert.ToBoolean(dataSchema.RowsIdea["AllowDBNull"]);
              column.ReadOnly = Convert.ToBoolean(dataSchema.RowsIdea["IsReadOnly"]);
              column.DataType = System.Type.GetType(dataSchema.RowsIdea["DataType"].ToString());
              arrayList.Add(column.ColumnName);
              dataTable.Columns.Add(column);
            }
          }

          while (reader.Read())
          {
            row = dataTable.NewRow();
            for ( int i = 0; i < arrayList.Count; i++)
            {
              row[((System.String) arrayListIdea)] = reader[(System.String) arrayListIdea];
            }

            dataTable.Rows.Add(row);
          }

          return dataTable;
        }

    Sunday, October 28, 2007 12:37 AM

All replies

  • I do not think you need GetTable function at all. Why do you use DataReader and your own code to populate DataTable? Use SqlDataAdapter and Fill method of it to populate your DataTable with the data from database. DataAdapter uses reader anyway to load data, but I believe it is shorter code and most likely more efficient than custom code.

    Tuesday, October 30, 2007 10:52 AM
    Moderator