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

User · Accepted Answer

You need to modify the method GetData   and add your  experimental  code there  and return t1

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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  gt  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   lt Select Program gt     0                       catch  Exception ex                           Handle the error                     Enjoy

[c#] How to fill DataTable with SQL Table

Examples related to c#

Examples related to .net

Examples related to sql

Examples related to datatable