DataSet

  • Thread starter Thread starter shapper
  • Start date Start date
S

shapper

Hello,

Does anyone knows how can I work with a Dataset created by an SQL
procedure with an Inner Join that returns data from 2 related tables?

I need to get the data from that dataset and add it to classes.

Thanks,
Miguel
 
Does anyone knows how can I work with a Dataset created by an SQL
procedure with an Inner Join that returns data from 2 related tables?

I need to get the data from that dataset and add it to classes.
Click to expand...

I'm sure there are better ways to do this, but here is a sample:

sub Page_Load(sender as object, e as eventargs) handles mybase.load
dim MyDataSet = RunSQLProc(Param1Value, Param2Value)
MyDataGrid.DataSource = MyDataSet.Tables(0)
MyDataGrid.DataBind()
'If your SQL procedure returns multiple result sets, you can assign
each result set to its own datagrid:
'MyDataGrid.Tables(1), MyDataGrid.Tables (2), etc.
end sub

function RunSQLProc(ByVal Param1Value As String, ByVal Param2Value As
Date) As DataSet
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(MyConnectionString)
Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = "MyStoredProcedureName"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Connection = dbConnection

Dim param_1 As IDataParameter = New SqlParameter
param_1.DbType = DbType.String
param_1.ParameterName = "@Param1"
param_1.Value = Param1Value
dbCommand.Parameters.Add(param_1)
Dim param_2As IDataParameter = New SqlParameter
param_2.DbType = DbType.Date
param_2.ParameterName = "@Param2"
param_2.Value = Param2Value
dbCommand.Parameters.Add(param_2)

dbConnection.Open()
Dim DA As SqlDataAdapter = New SqlDataAdapter(dbCommand)
RunSQLProc = New DataSet
DA.Fill(RunSQLProc)
dbConnection.Close()
End Function

HTH,

-E
 
You can find a complete example at:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry


A couple of things.

Since you're putting the info into your own classes, having 2 resultsets
might be preferrable over 1 inner-joined resultset.
You can do the single inner-joined, I only mention it to be complete.

While I use an IDataReader, you can use a DataSet. The mentality is the
same, you loop over resultset#1, and put that into classes.
Since you have an inner join (1 resultset), you can loop over it again I
guess, and add data to the second set of classes.


Finally, in my example above I do this:
List<BusinessObjects.Order> _orders = new List<BusinessObjects.Order> ();

I actually now prefer to make a formal class to do this

//Start Code
public class OrderCollection : List<BusinessObjects.Order>
{

}
//End Code


Then everywhere (else) that I have List<BusinessObjects.Order> , you would
use OrderCollection
 
Back
Top