C# 如何用SQL表填充DataTable

How to fill DataTable with SQL Table

提问by AhabLives

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

我目前正在我的 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.

我想知道如何转换此代码以便它从 SQL 查询中读取？我正在试验下面的代码，但我不确定如何连接它们，以便我的页面加载中的数据表填充下面的 SQL 命令。

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

我希望它是 t1 = (DataTable)Session["AllFeatures1];

采纳答案by banging

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

您需要修改该方法GetData()并在那里添加您的“实验”代码，然后返回t1.

回答by HenryZhang

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

的SqlDataReader是为有效的数据源DataTable。因此，您只需要这样做：

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

回答by Akash KC

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

您可以制作返回给定sql查询数据表的方法：

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();

回答by Fandango68

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 SqlDataAdapteris quick and simple, but only works if you're filling a table with a static request ie: a simple SELECTwithout parameters.

这SqlDataAdapter是快速而简单的，但仅当您使用静态请求填充表时才有效，即：一个SELECT没有参数的简单请求。

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.

这是我执行相同操作的方法，但使用参数来控制我在表中需要的数据。我用它来填充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

享受

回答by Faisal Ansari

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.

您可以像下面的代码一样填充数据表。我还在运行时使用包含所有连接的预定义 XML 文件获取连接。

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