How to fill DataTable with SQL Table

Question

I am currently creating and reading a DataTable with the following code in my Page_Load

protected void Page_Load(object sender, EventArgs e)
{
    if (Session["AllFeatures1"] == null)
    {
        Session["AllFeatures1"] = GetData();
    }
    table = (DataTable)Session["AllFeatures1"];
    DayPilotCalendar1.DataSource = Session["AllFeatures1"];
    DayPilotNavigator1.DataSource = Session["AllFeatures1"];

    if (!IsPostBack)
    {
        DataBind();
        DayPilotCalendar1.UpdateWithMessage("Welcome!");
    }

    if (User.Identity.Name != "")
    {
        Panel1.Visible = true;
    }
}

I would like to know how to convert this code so that it reads from a SQL query? I am experimenting with the code below but I'm not sure how to connect them so that datatable in my page load fills with the SQL command below.

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BarManConnectionString"].ConnectionString);
conn.Open();
string query = "SELECT * FROM [EventOne]";

SqlCommand cmd = new SqlCommand(query, conn);

DataTable t1 = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
{
    a.Fill(t1);
}

I am stuck at:

table = (DataTable)Session["AllFeatures1"];

I would like it to be t1 = (DataTable)Session["AllFeatures1];

Andrew C · Accepted Answer · 2017-09-20 16:04:48Z

46

The SqlDataReader is a valid data source for the DataTable. As such, all you need to do its this:

public DataTable GetData()
{
    SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BarManConnectionString"].ConnectionString);
    conn.Open();
    string query = "SELECT * FROM [EventOne]";
    SqlCommand cmd = new SqlCommand(query, conn);

    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    conn.Close();
    return dt;
}

edited Sep 20, 2017 at 16:04

Andrew C

251 silver badge7 bronze badges

answered Aug 16, 2012 at 18:33

HenryZhang

1,3688 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xpt Over a year ago

This answers the question "How to fill DataTable with SQL Table" perfectly. If it is not specific for OP, then OP didn't title his question good enough.

Jettero Over a year ago

Better if you not use the DataReader.Load() method for read all type of SQL Select Query. I used it for read out a complex SELECT with some LEFT JOIN and I got "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints" exception. After I started to use DataAdapter.Fill(DataTable) type of Query and now no exception.

Akash KC · Accepted Answer · 2012-08-16 18:35:20Z

12

You can make method which return the datatable of given sql query:

public DataTable GetDataTable()
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BarManConnectionString"].ConnectionString);
conn.Open();
string query = "SELECT * FROM [EventOne] ";

SqlCommand cmd = new SqlCommand(query, conn);

DataTable t1 = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
{
    a.Fill(t1);
}
return t1;
}

and now can be used like this:

table = GetDataTable();

answered Aug 16, 2012 at 18:35

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Comments

TylerH · Accepted Answer · 2019-06-20 20:08:57Z

7

You need to modify the method GetData() and add your "experimental" code there, and return t1.

edited Jun 20, 2019 at 20:08

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Aug 16, 2012 at 18:32

banging

2,5601 gold badge23 silver badges26 bronze badges

1 Comment

AhabLives Over a year ago

Yeah...you are correct I got caught in a brain lock with Page_Load the moment you said put your work code in GetData() I popped out of my "put everything in Page_Load" freeze. Thanks for the nudge.

Fandango68 · Accepted Answer · 2015-06-24 03:56:39Z

1

The answers above are correct, but I thought I would expand another answer by offering a way to do the same if you require to pass parameters into the query.

The SqlDataAdapter is quick and simple, but only works if you're filling a table with a static request ie: a simple SELECT without parameters.

Here is my way to do the same, but using a parameter to control the data I require in my table. And I use it to populate a DropDownList.

//populate the Programs dropdownlist according to the student's study year / preference
DropDownList ddlPrograms = (DropDownList)DetailsView1.FindControl("ddlPrograms");
if (ddlPrograms != null)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ATCNTV1ConnectionString"].ConnectionString))
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT ProgramID, ProgramName FROM tblPrograms WHERE ProgramCatID > 0 AND ProgramStatusID = (CASE WHEN @StudyYearID = 'VPR' THEN 10 ELSE 7 END) AND ProgramID NOT IN (23,112,113) ORDER BY ProgramName";
            cmd.Parameters.Add("@StudyYearID", SqlDbType.Char).Value = "11";
            DataTable wsPrograms = new DataTable();
            wsPrograms.Load(cmd.ExecuteReader());

            //populate the Programs ddl list
            ddlPrograms.DataSource = wsPrograms;
            ddlPrograms.DataTextField = "ProgramName";
            ddlPrograms.DataValueField = "ProgramID";
            ddlPrograms.DataBind();
            ddlPrograms.Items.Insert(0, new ListItem("<Select Program>", "0"));
        }
        catch (Exception ex)
        {
            // Handle the error
        }
    }
}

Enjoy

answered Jun 24, 2015 at 3:56

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

1 Comment

Kyllian Mobley Over a year ago

You've got a couple issues. First, while not creating a new SqlDataAdapter if you don't have to is desirable, SqlDataAdapter.Fill does actually work with complex SELECT commands (with JOINs and parameters). Second, the SqlParameterCollection.Add method (referenced by cmd.Parameters here) returns a new SqlParameter object. This means that when you assign to that object's Value property, it's not going to make it to the SqlParameter in cmd.Parameters. Cheers!

Faisal Ansari · Accepted Answer · 2018-05-15 08:51:32Z

You can fill your data table like the below code.I am also fetching the connections at runtime using a predefined XML file that has all the connection.

  public static DataTable Execute_Query(string connection, string query)
    {
        Logger.Info("Execute Query has been called for connection " + connection);
        connection = "Data Source=" + Connections.run_singlevalue(connection, "server") + ";Initial Catalog=" + Connections.run_singlevalue(connection, "database") + ";User ID=" + Connections.run_singlevalue(connection, "username") + ";Password=" + Connections.run_singlevalue(connection, "password") + ";Connection Timeout=30;";
        DataTable dt = new DataTable();
        try
        {
            using (SqlConnection con = new SqlConnection(connection))
            {
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    con.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.SelectCommand.CommandTimeout = 1800;
                        da.Fill(dt);
                    }
                    con.Close();
                }
            }
            Logger.Info("Execute Query success");
            return dt;
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
            return null;
        }
    }

Collectives™ on Stack Overflow

How to fill DataTable with SQL Table

5 Answers 5

2 Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related