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];
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;
}
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
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();
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;
}
}
Source: Stackoverflow.com