Convert DataReader To Datatable Manually

Convert DataReader To Datatable Manually

Use the following Code Snippet to convert DataReader to DataTable:

private void ConvertDataReaderToTableManually()

    {

        SqlConnection conn = null;

        try

        {

            string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;

            conn = new SqlConnection(connString);

            string query = "SELECT * FROM Customers";

            SqlCommand cmd = new SqlCommand(query, conn);

            conn.Open();

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            DataTable dtSchema = dr.GetSchemaTable();

            DataTable dt = new DataTable();

            // You can also use an ArrayList instead of List<>

            List<DataColumn> listCols = new List<DataColumn>();

            if (dtSchema != null)

            {

                foreach (DataRow drow in dtSchema.Rows)

                {

                    string columnName = System.Convert.ToString(drow["ColumnName"]);

                    DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));

                    column.Unique = (bool)drow["IsUnique"];

                    column.AllowDBNull = (bool)drow["AllowDBNull"];

                    column.AutoIncrement = (bool)drow["IsAutoIncrement"];

                    listCols.Add(column);

                    dt.Columns.Add(column);

                }

            }

            // Read rows from DataReader and populate the DataTable

            while (dr.Read())

            {

                DataRow dataRow = dt.NewRow();

                for (int i = 0; i < listCols.Count; i++)

                {

                    dataRow[((DataColumn)listCols[i])] = dr[i];

                }

                dt.Rows.Add(dataRow);

            }

            GridView2.DataSource = dt;

            GridView2.DataBind();

        }

        catch (SqlException ex)

        {

            // handle error

        }

        catch (Exception ex)

        {

            // handle error

        }

        finally

        {

            conn.Close();

        }

    }