Using MS Data Access application block to call stored procedure with parameters

[JM]

Hi,

I am using SQL Server 2000 and ASP.NET 2.0 and want to call a stored
procedure using Latest Enterprise Library 2.0. My stored procedure has
3 input parameters: CustId (int), RefId(int) and EmailId(varchar 200)
and it returns a dataset.

This is how I am trying to do:
-------------
int CustId = 1
int RefId = 1
string EmailId = "so******@email.com"

Database db = DatabaseFactory.CreateDatabase("ConnectionString") ;
//Need help in following line
DataSet ds = db.ExecuteDataSet("get_CustData", params object[]
parameterValues);
-------------------

Can anybody tell me how I can create this params object with the
values of all 3 parameters. And whats the best way to use enterprise
library for SQL Server database.

Thanks
JM

[sloan]

You have to get hold of the instance of hte new 2.0 Abstract class
DbCommand
public DataSet GetOrdersByCustomerID(string customerID)
{
Database northwind = DatabaseFactory.CreateDatabase();

DbCommand command = northwind.GetSqlStringCommand ("SELECT
[OrderID],[OrderDate] FROM [Orders] WHERE [CustomerID] =
@CustomerID"); //OR DbCommand command =
northwind.GetStoredProcedure("dbo.uspGetAllOrders" )
northwind.AddInParameter(command, "@CustomerID",
DbType.String, customerID);
DataSet orders = new DataSet();

northwind.LoadDataSet(command, orders, "orders");

return orders;
}

I prefer the LoadDataSet over the ExecuteDataSet method.

You can find more examples by going here:

http://www.google.com/search?hl=en&q...InParameter%22

which is where I pulled the example from


"JM" <jm******@gmail.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...

Hi,

I am using SQL Server 2000 and ASP.NET 2.0 and want to call a stored
procedure using Latest Enterprise Library 2.0. My stored procedure has
3 input parameters: CustId (int), RefId(int) and EmailId(varchar 200)
and it returns a dataset.

This is how I am trying to do:
-------------
int CustId = 1
int RefId = 1
string EmailId = "so******@email.com"

Database db = DatabaseFactory.CreateDatabase("ConnectionString") ;
//Need help in following line
DataSet ds = db.ExecuteDataSet("get_CustData", params object[]
parameterValues);
-------------------

Can anybody tell me how I can create this params object with the
values of all 3 parameters. And whats the best way to use enterprise
library for SQL Server database.

Thanks
JM

[JM]

Hi,

Thanks for the prompt reply. I am able to use sp using DBCommand
object.

Thanks
JM

On Feb 13, 11:23 am, "sloan" <[e-mail]:

You have to get hold of the instance of hte new 2.0 Abstract class
DbCommand

public DataSet GetOrdersByCustomerID(string customerID)
{
Database northwind = DatabaseFactory.CreateDatabase();

DbCommand command = northwind.GetSqlStringCommand ("SELECT
[OrderID],[OrderDate] FROM [Orders] WHERE [CustomerID] =
@CustomerID"); //OR DbCommand command =
northwind.GetStoredProcedure("dbo.uspGetAllOrders" )
northwind.AddInParameter(command, "@CustomerID",
DbType.String, customerID);
DataSet orders = new DataSet();

northwind.LoadDataSet(command, orders, "orders");

return orders;

}

I prefer the LoadDataSet over the ExecuteDataSet method.

You can find more examples by going here:

http://www.google.com/search?hl=en&q...ommand+%22AddI...

which is where I pulled the example from

"JM" <[e-mail] in message

news:11**********************@v33g2000cwv.googlegr oups.com...

Hi,

I am using SQL Server 2000 and ASP.NET 2.0 and want to call a stored
procedure using Latest Enterprise Library 2.0. My stored procedure has
3 input parameters: CustId (int), RefId(int) and EmailId(varchar 200)
and it returns a dataset.

This is how I am trying to do:
-------------
int CustId = 1
int RefId = 1
string EmailId = "[e-mail]"

Database db = DatabaseFactory.CreateDatabase("ConnectionString") ;
//Need help in following line
DataSet ds = db.ExecuteDataSet("get_CustData", params object[]
parameterValues);
-------------------

Can anybody tell me how I can create this params object with the
values of all 3 parameters. And whats the best way to use enterprise
library for SQL Server database.

Thanks
JM- Hide quoted text -

- Show quoted text -

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

For informational purposes, the params object[] array of parameter values can
be constructed like this:

object[] myParams = { "Abc", 23, DateTime.Now, "etc."};

Pass "myParams" in the last position of the method signature.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"JM" wrote:

Hi,

I am using SQL Server 2000 and ASP.NET 2.0 and want to call a stored
procedure using Latest Enterprise Library 2.0. My stored procedure has
3 input parameters: CustId (int), RefId(int) and EmailId(varchar 200)
and it returns a dataset.

This is how I am trying to do:
-------------
int CustId = 1
int RefId = 1
string EmailId = "so******@email.com"

Database db = DatabaseFactory.CreateDatabase("ConnectionString") ;
//Need help in following line
DataSet ds = db.ExecuteDataSet("get_CustData", params object[]
parameterValues);
-------------------

Can anybody tell me how I can create this params object with the
values of all 3 parameters. And whats the best way to use enterprise
library for SQL Server database.

Thanks
JM