How to fill DataTable with SQL Table

user_1178802 0
Viewed 190006

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];

datatable sql .net c#
5 Answers

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;
        }
    }
user_68987600
Related