C# 如何动态制作数据表并动态生成列和行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16305859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to dynamically make DataTables and with Columns and Rows being dynamically generated as well?
提问by chad
Say I have a database with 2 tables with the following names (tbl1and tbl2).
Each of the table above have different number of columns, tbl1has 3 while tbl2has 4 columns.
假设我有一个包含 2 个表的数据库，其名称如下（tbl1和tbl2）。上面的每个表都有不同的列数，tbl1有 3列，而tbl2有 4 列。
I need to copy each of the above tables in a DataTable.
我需要将上述每个表复制到DataTable.
Of course I can do it manually like the following codes below:
当然，我可以像下面的代码一样手动完成：
class
{
public Main()
{
// for tbl1 with 3 columns.
string sql = "select * from tbl1"
MySqlCommand com = new MySqlCommand(sql, con);
MySqlDataReader dr = com.ExecuteDataReader();
DataTable dt = GetDataTable(3);
while (dr.Read())
{
if (dr.HasRows)
{
dt.Rows.Add(dr[0], dr[1], dr[2]); <---- Point of interest
}
}
// for tbl2 with 4 columns.
string sql = "select * from tbl2";
MySqlCommand com = new MySqlCommand(sql, con);
MySqlDataReader dr = com.ExecuteDataReader();
DataTable dt = GetDataTable(4);
while (dr.Read())
{
if (dr.HasRows)
{
dt.Rows.Add(dr[0], dr[1], dr[2], dr[3]); <---- Point of interest
}
}
}
public DataTable GetDataTable(int columnCount)
{
DataTable dt = new DataTable();
if (columnCount > 0)
{
for (int i = 0; i < length; i++)
{
dt.Columns.Add(i.ToString(), typeof(object));
}
}
return dt;
}
}
But what I would like to do is to make the process above in an automated way, especially on the part where I indicated an arrow.
但是我想做的是以自动化的方式进行上述过程，尤其是在我指示箭头的部分。
Is there a way I could dynamically add rows like what I did on the columns?
有没有办法像我在列上所做的那样动态添加行？
I was thinking I could add rows dynamically by using a function that generates the process of adding rows as string and call that string as a command but I am really really lost and don't know what to do... pls see the code below.
我想我可以通过使用一个函数来动态添加行，该函数生成将行添加为字符串的过程并将该字符串作为命令调用，但我真的很迷茫，不知道该怎么做......请看下面的代码.
EX:
String generated from a function base on number of columns:
"dt.Rows.Add(dr[0], dr[1], dr[2], dr[3])"
Then, use the string as a command to add rows...
采纳答案by gabnaim
Use the GetSchemaTable method of the datareader to find out how many columns you have.
使用数据读取器的 GetSchemaTable 方法找出您有多少列。
Something like this (non-tested code, meant for example):
像这样的东西（例如，未经测试的代码）：
public DataTable ReadTable(string sql, params object[] parameters) {
using (var cmd = CreateCommand(sql, parameters))
{
var reader = cmd.ExecuteReader();
if (reader == null)
{
return null;
}
var schemaTable = reader.GetSchemaTable();
DataTable dt = GetTable(schemaTable);
while (reader.Read())
{
var values = new object[reader.FieldCount];
reader.GetValues(values);
dt.Rows.Add(values);
}
}
}
private DataTable GetTable(DataTable schemaTable) {
if (schemaTable == null || schemaTable.Rows.Count == 0)
{
return null;
}
var dt = new DataTable();
foreach (DataRow schemaRow in schemaTable.Rows)
{
var col = new DataColumn
{
ColumnName = schemaRow["ColumnName"].ToString(),
DataType = schemaRow["DataType"]
// use the debugger to find out the name of the type column in the schematable,
// and any other properties you need
};
dt.Columns.Add(col);
}
return dt;
}
Note: I use code like this not to create a DataTable, but to convert the read rows into an IEnumerable object (we are using some DB2 Entity Framework libraries that don't do this automatically using a sql statement).
注意：我使用这样的代码不是为了创建 DataTable，而是为了将读取的行转换为 IEnumerable 对象（我们正在使用一些不会使用 sql 语句自动执行此操作的 DB2 实体框架库）。
As many mentioned, if you just want to read a DataTable, create a DataAdapter and use the Fill() method.
正如许多人提到的，如果您只想读取 DataTable，请创建一个 DataAdapter 并使用 Fill() 方法。
// Assumes that connection is a valid SqlConnection object.
string queryString =
"SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");
http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.80).aspx
http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.80).aspx
回答by DarrenMB
This is a VB example of Filling a DataSet to get your results (modified from one of my wrappers).
这是填充数据集以获取结果的 VB 示例（从我的包装器之一修改）。
There is no population of rows at all (it is done for you by the data adapter)
根本没有行数（由数据适配器为您完成）
Sub ExampleUsage()
Dim ds as new dataset
ExecuteDataSetFill(AnSqlConnection, "SELECT * FROM Something; SELECT * from SomethingElse", ds)
Dim Tbl1 as datatable=ds.Tables(0)
Dim Tbl2 as datatable=ds.Tables(1)
' both tables will ALREADY HAVE all the rows in them, there is no reader involved.
End Sub
''' <summary>
''' Performs a FILL operation on an adapter, populating the passed in dataset for the current "OpenConnection", returns the return value of the FILL command.
''' </summary>
''' <param name="sSQL">SQL to use for the command to issue</param>
''' <param name="dsToFill">a DataSet to FILL</param>
''' <returns>The Return Value of the FILL operation</returns>
Public Overridable Function ExecuteDataSetFill(ByVal con As SqlClient.SqlConnection, ByVal sSQL As String, ByVal dsToFill As DataSet) As Integer
Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
Dim com As SqlClient.SqlCommand = con.CreateCommand
com.CommandText = sSQL
da.SelectCommand = com
Dim iOut As Integer
dsToFill.EnforceConstraints = False
Dim sw As Stopwatch = Stopwatch.StartNew
Try
iOut = da.Fill(dsToFill)
Catch ex As Exception
Throw New Exception("DataSet Error. " & vbCrLf & "SQL = " & sSQL & vbCrLf & "Error=" & ex.ToString, ex)
End Try
sw.Stop()
Return iOut
End Function
