I am new to .NET 3/3.5 (C#) Programming. It may be simple for you but not for me. I have a task to executing stored procedure from c# application, using command and it returns 3 different data tables proc is already done in sql 2005,but I want to know how to get data in c#. exactly need c# code how to handle data tables form the result set,if result set returns more than one datatable. Thanks
1
-
I suggest you post your attempt so far...Mitch Wheat– Mitch Wheat2009-07-13 06:00:50 +00:00Commented Jul 13, 2009 at 6:00
Add a comment
|
4 Answers
You can use the SqlDataAdapter.Fill() method to fill a DataSet. This way you can access the multiple result tables using this -
ds.Tables[0].Rows[0]["Column X"];
ds.Tables[1].Rows[0]["Column Y"];
ds.Tables[2].Rows[0]["Column Z"];
...
Or, you can use a SqlDataReader (which is faster if you want to process your data in a forward-only fashion). For reading the records of the first result set, you can iterate a while loop and call the SqlDataReader.Read() method.
while(reader.Read())
{
//Process the first result set here.
}
For fetching the next result set, you call the SqlDataReader.NextResult() method.
reader.NextResult();
while(reader.Read())
{
//Process the next result set here.
}
Comments
Use NextResult in case of ExecuteReader
SqlDataReader reader = command.ExecuteReader();
while (reader.HasRows)
{
Console.WriteLine("\t{0}\t{1}", reader.GetName(0),reader.GetName(1));
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
reader.NextResult();
}
Comments
So you already have the DataSet? If so, it's easy. You get at a particular table using the Tables property, e.g.
DataTable users = dataSet.Tables["users"];
If that's not what you mean, please clarify the question.
4 Comments
Kirtan
I guess, he means to ask how to handle multiple result-sets returned from an SQL query using ADO.NET.
Jon Skeet
But we really need to know how far he's got before we can help further - if he's already got a DataSet, then explaining what to do with a DataReader doesn't help, and vice versa.
Kirtan
Hmmm. You are right. But, I answered on the basis of what I inferred from the question. I thought that he was stuck on HOW to get the multiple result set in his app, and use them; I showed him two ways in which this can be done. And btw, he is confused between datatable/result set, he should have referred it to result set actually.
Jon Skeet
Right - if he'd said that, I'd have given an answer more like yours. I inferred that the result sets were already there, and just needed to be extracted. This is the problem with very vague questions :( Anyway, I'm glad it looks like you've sorted him out now.
Dim db As Database = DatabaseFactory.CreateDatabase(ApplicationStringResource.DbConn)
Dim cmd As DbCommand = db.GetStoredProcCommand(DbStoreProc.GetFeeCodeStoreProc)
Dim sReader As SqlDataReader = db.ExecuteReader(cmd)
Try
While sReader.Read()
Dim feeCode As New FeeCode()
With feeCode
.FeeCode = sReader("FeeCode")
.Description = sReader("Description")
End With
feeCodeList.Add(feeCode)
End While
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
sReader.Close()
End Try
Return feeCodeList
I use MS Enterprise Library and the above is an code block that I use to get the data via SQLDataReader.
1 Comment
Rob Levine
That's not C#. The questioner wants it in C#, not VB.Net