How to convert SqlDataReader or DataReader to DataTable in Asp.Net

In many cases we need to convert or converting SqlDataReader or DataReader to a DataTableto meet some technical challenges. Here i will show you converting SqlDataReader to a DataTable in a simple way. The code sample is given below:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string sql = "SELECT B.Name [Brand Name],C.Name [Category Name], " +
                    "P.Name [Product Name] FROM " +
                    "Brand B, Category C, Product P " +
                    "WHERE B.ID=P.BrandID AND C.ID=P.CategoryID Order BY 1,2,3";
            DataTable dt = new DataTable();
            using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString))
            {
                oConn.Open();
                SqlCommand cmd = new SqlCommand(sql, oConn);
                System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }

            Response.Write("Datatable row count: "+dt.Rows.Count.ToString());
        }
    }

Look at the code its self explanatory. So i hope that no need any explanation.