Run Stored Procedure

Topics: Data Access Application Block, Enterprise Library Core
Feb 8, 2007 at 3:20 PM
Hello,

Could someone tell me how to run a simple SQL stored procedure using
the Enterprise Library 2.0 Data Access?

I have in my VB.NET code the following database:
Dim db As Database = DatabaseFactory.CreateDatabase

Thanks,
Miguel
Feb 8, 2007 at 5:31 PM
You don't say whether there are any procedure parameters, or what results you want back, but here is an example in VB.NET:

Public Shared Function GetEmployees(ByVal EmployeeID As Guid) As DataSet
	
	' Create the Database object, using the default database in configuration
	Dim db As Database = DatabaseFactory.CreateDatabase
	
	Dim sqlCommand As String = "daab_GetEmployees"
	Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
	
	' Add procedure parameters
	db.AddInParameter(dbCommand, "EmployeeID", DbType.Guid, EmployeeID)
	
	Return db.ExecuteDataSet(dbCommand)
	
End Function
May 27, 2007 at 7:47 AM
this in C# but there are plenty of examples

http://www.codeproject.com/useritems/Parameter_DiscoveryV292.asp
Jul 3, 2007 at 5:43 PM
Edited Jul 3, 2007 at 5:45 PM
How to call a SQL Server, Table Valued Function with DAAB?


I get the following exception when I use GetStoredProcCommand()

The request for procedure 'fnSeqNumbers' failed because 'fnSeqNumbers' is a function object.

If I use GetSqlStringCommand to call the same function, I get

Incorrect syntax near 'fnSeqNumbers'.

What is the right way to call it ?

Thank you.
Jul 3, 2007 at 6:55 PM
Edited Jul 3, 2007 at 6:57 PM
You cannot call a UDF directly with the DAAB. You have to use a SELECT statement and an OUTPUT parameter.

    Public Shared Function GetSeqNumbers() As Integer
 
        ' Create the Database object, using the default database in configuration
        Dim db As Database = DatabaseFactory.CreateDatabase
 
        Dim sqlCommand As String = "SELECT @result = fnSeqNumbers();"
        Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)
 
        ' Add procedure parameters
        db.AddOutParameter(dbCommand, "result", DbType.Int32, 0)
 
        db.ExecuteNonQuery(dbCommand)
 
        Return CType(db.GetParameterValue(dbCommand, "result"), Integer)
 
    End Function

___________________________________
Matthew Noonan
EasyObjects.NET -- The O/RM for the Enterprise Library
http://www.easyobjects.net
Jul 3, 2007 at 9:30 PM
Hello

If you only have input parameters, you can do this

int output = (int)DatabaseFactory.CreateDatabase().ExecuteScalar("StoreProcedureName", paramvalue1, paramvalue2, paramvalue3);

It'll use the parameter cache, so you don't have to create the SqlParameters individually.
I prefer a scalar return value over output parameters, just because it's simpler.