SQL 你如何命名你在存储过程中返回的数据集的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/589976/
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 can you name the Dataset's Tables you return in a stored proc?
提问by Brann
I've got the following stored procedure
我有以下存储过程
Create procedure psfoo ()
AS
select * from tbA
select * from tbB
I'm then accessing the data this way :
然后我以这种方式访问数据：
Sql Command mySqlCommand = new SqlCommand("psfoo" , DbConnection)
DataSet ds = new DataSet();
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
mySqlDataAdapter.Fill(ds);
Now, when I want to access my tables, I have to do this :
现在，当我想访问我的表时，我必须这样做：
DataTable datatableA = ds.Tables[0];
DataTable datatableB = ds.Tables[1];
the dataset Tables property also got an accessor by string (instead of int).
dataset Tables 属性也有一个按字符串（而不是 int）的访问器。
Is it possible so specify the name of the tables in the SQL code, so that I can instead write this :
是否有可能在 SQL 代码中指定表的名称，以便我可以改为这样写：
DataTable datatableA = ds.Tables["NametbA"];
DataTable datatableB = ds.Tables["NametbB"];
I'm using SQL server 2008, if that makes a difference.
我正在使用 SQL Server 2008，如果这有区别的话。
回答by David Wengier
As far as I know, from the stored proc, you can't do that. You can, however, set the names once you have retrieved the DataSet, and then use them from then on.
据我所知，从存储过程中，你不能这样做。但是，您可以在检索到 DataSet 后设置名称，然后从那时起使用它们。
ds.Tables[0].TableName = "NametbA";
回答by Thangamani Palanisamy
Stored procedure :
存储过程：
select 'tbA','tbB','tbC'
select * from tbA
select * from tbB
select * from tbC
front-end:
前端：
int i = 1;
foreach (string tablename in dsEmailData.Tables[0].Rows[0][0].ToString().Split(','))
{
dsEmailData.Tables[i++].TableName = tablename;
}
Hope this helps
希望这可以帮助
回答by Matt Hamilton
Is there any reason you can't name them manually after filling the DataSet?
是否有任何原因不能在填充数据集后手动命名它们？
mySqlDataAdapter.Fill(ds);
ds.Tables[0].TableName = "NametbA";
ds.Tables[1].TableName = "NametbB";
I don't know of any way to name the DataTables that are returned as part of multiple result sets from a stored procedure, but if you know what the stored proc is returning then manually naming them should work fine.
我不知道有什么方法可以命名作为存储过程的多个结果集的一部分返回的数据表，但是如果您知道存储过程返回的是什么，那么手动命名它们应该可以正常工作。
Edit
编辑
Knowing that you have control over the stored procedure, one alternative might be to add a column to the result sets which represents the table name. Then you might be able to do something like:
知道您可以控制存储过程，一种替代方法可能是向结果集中添加一列代表表名的列。然后你也许可以做这样的事情：
foreach (DataTable table in ds.Tables)
{
table.TableName = table.Rows[0]["TableName"].ToString();
}
However, this relies on the result sets coming back from the stored procedures actually containing rows. If they don't contain rows then you'd have to wrap it in an "if" statement and not every table would get a name.
但是，这依赖于从实际包含行的存储过程返回的结果集。如果它们不包含行，则您必须将其包装在“if”语句中，并且并非每个表都会获得名称。
回答by DeveloperX
good idea ,its better your procedure returns three resultset instead of adding table name on
好主意，你的程序最好返回三个结果集，而不是在上面添加表名
firs trow of each table
select 'tbA' FirstTableName,'tbB' SecondTableName
select * from tbA
select * from tbB
everytime you executing procedure the tables[0] will have a row that keeps follwing select tablenames
每次执行过程时，表 [0] 将有一行保持以下选择表名
Another idea can be passing table name as output paramter of procedure
另一个想法可以将表名作为过程的输出参数传递
Create procedure psfoo (@tb1 varchar(50) output,@tb2 varchar(50) output)
AS
set @tb1='tbA'
set @tb2 'tbB'
select * from tbA
select * from tbB
if this methods limits you and selects are variable you can create procedure like this
如果这种方法限制了你并且选择是可变的，你可以创建这样的过程
Create procedure psfoo ()
AS
select * from tbA
select * from tbB
return 'tbA,tbB'
and by splitting procedure retrun value into string[] you can get the name of tables ordinally
并通过将过程重运行值拆分为 string[] 可以按顺序获取表的名称
[email protected](new char[]{','});
string tablenam1=returnvalue[0];
string tablenam2=returnvalue[1];
回答by Ramzan Mohideen
In order to give names to all table in a dataset, we need to write one more select query at last of the SP or T-Sql.
为了给数据集中的所有表命名，我们需要在 SP 或 T-Sql 的最后再写一个选择查询。
lsqry = New System.Text.StringBuilder
lsqry.AppendLine("select * from Bill_main")
lsqry.AppendLine("select * from serviceonly")
lsqry.AppendLine("select * from PendingService")
lsqry.AppendLine("select * from T_servicebillhd")
lsqry.AppendLine("select 'Bill_Main','serviceonly','PendingService','T_servicebillhd'")
Using con As New SqlConnection(lsConnection)
Try
con.Open()
Using cmd As SqlCommand = con.CreateCommand
With cmd
.CommandText = lsqry.ToString
.CommandType = CommandType.Text
End With
Using da As New SqlDataAdapter(cmd)
da.Fill(DS)
End Using
End Using
For li As Integer = 0 To DS.Tables.Count - 2
DS.Tables(li).TableName = DS.Tables(DS.Tables.Count - 1).Rows(0).Item(li)
Next
Catch ex As Exception
End Try
End Using
In the above example, i am using t-sql statement instead of SP. In which i wrote 4 select query and last one is table name of those tables. And fetch last table which contains names of tables and assign it to table using TableName property.
在上面的例子中，我使用 t-sql 语句而不是 SP。我在其中写了 4 个选择查询，最后一个是这些表的表名。并获取包含表名称的最后一个表，并使用 TableName 属性将其分配给表。
回答by Anil Pal
You can try this:
你可以试试这个：
add below line in your procedure
在您的程序中添加以下行
Select 'TableName1','TableName2';
Using C#:
使用 C#：
for (var i=0;i<ds.Tables[0].Columns.Count;i++)
{
ds.Tables[i + 1].TableName = ds.Tables[0].Columns[i].ColumnName;
}
回答by R4n0n
Try this
尝试这个
YourDataAdapter.Fill(ds, "Table");
回答by sushil.agarwal
Try this. In MS SQL 2008 R2, I always use this.
尝试这个。在 MS SQL 2008 R2 中，我总是使用它。
mySqlDataAdapter.Fill(ds,"NameTableA");
回答by James Randy
should use select into "yourtablename" from originaltable, this is just an example, find the correct syntax of select into statement.
应该使用 select into "yourtablename" from originaltable，这只是一个例子，找到 select into 语句的正确语法。
This is the correct way to do it for your requirement. Thankyou.
这是根据您的要求执行此操作的正确方法。谢谢你。
回答by MaGnumX
This question is an old one but, it's at the top of google search, hehe. Maybe it will help somebody ...
这个问题很老了，但它在谷歌搜索的顶部，呵呵。也许它会帮助某人......
And the answer is yes, it's possible to specify the name of the tables in the code so that you can access DataTable in DataSet by its name.
答案是肯定的，可以在代码中指定表的名称，以便您可以通过名称访问DataSet中的DataTable。
First, I must explain how TableMapping works. If we don't specify TableMappings with SqlDataAdapter (SqlDataAdapter that will fill our DataSet) then by default first table will be named "Table", second will be named "Table1", third will be named "Table2" etc.
首先，我必须解释 TableMapping 是如何工作的。如果我们不使用 SqlDataAdapter 指定 TableMappings（SqlDataAdapter 将填充我们的数据集），那么默认情况下第一个表将命名为“Table”，第二个将命名为“Table1”，第三个将命名为“Table2”等。
So, when we want to name DataTable in DataSet, we use it like this:
因此，当我们想在 DataSet 中命名 DataTable 时，我们像这样使用它：
//...
System.Data.DataSet myDataSet = new System.Data.DataSet();
using (System.Data.SqlClient.SqlDataAdapter dbAdapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand))
{
dbAdapter.TableMappings.Add("Table", "MyFirstTitleForTableOne");
dbAdapter.TableMappings.Add("Table1", "MyFirstTitleForTableTwo");
dbAdapter.TableMappings.Add("Table2", "MyFirstTitleForTableThree");
dbAdapter.TableMappings.Add("Table3", "MyFirstTitleForTableFour");
//...
dbAdapter.Fill(myDataSet);
}
//...
And, now we can access DataTable by our title:
而且，现在我们可以通过我们的标题访问 DataTable：
System.Data.DataTable firstTable = myDataSet.Tables["MyFirstTitleForTableOne"];
System.Data.DataTable secondTable = myDataSet.Tables["MyFirstTitleForTableTwo"];
Sorry, but I didn't write code that will check for null (myDataSet) or set it in try/catch block because I think that is not irrelevant to this question.
抱歉，但我没有编写将检查 null (myDataSet) 或将其设置在 try/catch 块中的代码，因为我认为这与此问题无关。
