returning dataset from stored procedure

[lfc77]

Is it possible to return a dataset from a stored procedure, or would you need to write the SQL in your C# file to return the dataset?


Any assistance would be really appreciated.


Cheers,

lfc77

 

[cappmgr]

Yes.
You can use the DataAdapter and fill method. I only have oracle code to show you. I am sure many can post SQL Server code.
Marty

 

[obislavu]

// SqlConnection m_SQLConnection is a connection to the database
// Retrieve a DataSet 
public System.Data.DataSet GetDataSet(string vSQLStatement)
{
	try
	{
		System.Data.SqlClient.SqlDataAdapter vSQLAdapter = new SqlDataAdapter(vSQLStatement,m_SQLConnection); 
		System.Data.DataSet vReturn = new System.Data.DataSet();   
		vSQLAdapter.Fill(vReturn); 
		return(vReturn);
	}
	catch(Exception vException)
	{
		return(new System.Data.DataSet());
	}

}

How to call a stored procedure which expect one parameter:

DataSet vDataSet;
string ObjectName = "AllItems";
vDataSet = GetDataSet("EXEC dbo.sp_GetReport '" + ObjectName + "'");
if (vDataSet.Tables.Count > 0)
{
   // process here the the returned DataSet
}

For Oracle use OleDb* instead of Sql*.
obislavu

 

[cappmgr]

Obislavu:
First time I have seen someone pass EXEC sproc argument to a DataAdapter. What are the pro's and con's between what you posted and

OracleCommand myCommand = new OracleCommand("DBF_GetSubmissions",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new OracleParameter("a_i_YearNo", OracleType.Number)).Value = 2004;
myCommand.Parameters.Add(new OracleParameter("r_o_ReturnCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
OracleDataAdapter MyDA = new OracleDataAdapter(myCommand);
try 
{
	myConnection.Open();
	MyDA.Fill(ds);
	return ds;
}
catch.....

Marty

 

[obislavu]

Yes, that is working very well and your example gives the same results.
EXEC or EXECUTE
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.
Pro's and con's ? One is permissions.
>>EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.
>>The difference is your example is using Parameters which is recomended when you send SQL strings but there is no danger if I didn't use Parameters in such case.
-obislavu-