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 = "(e-mail address removed)"

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=CreateDatabase+DbCommand+"AddInParameter"



which is where I pulled the example from

 

[JM]

Hi,

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

Thanks
JM

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=CreateDatabase+DbCommand+"AddI...

which is where I pulled the example from

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:

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 -

 

[Guest]

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