ADO.NET/CommandBuilder/Stored Procedure/Dataset

[RicoCali]

I have a situation where I am processing my dataset through commandbuilder. I also have a situation where I can only call stored-procedures and have no access to tables from the connection of my app. The SQL-Server was design this way for security reasons. How do I force the CommandBuilder to use stored-procedures?

 

[Joulius]

Here's an idea. It's not 100% full-proof but I think will get its job done:

A test stored procedure:

CREATE PROCEDURE sp_test(
@PID int,
@PName varchar(255),
@PDescription varchar(2000)
)
AS
...


Method #1:

SqlConnection oCon=new SqlConnection("connection string");
			
SqlCommand oCmd=new SqlCommand("sp_test", oCon);
oCmd.CommandType=CommandType.StoredProcedure;
			
SqlParameter pID=new SqlParameter("@PID", SqlDbType.Int);
pID.Value=1;
oCmd.Parameters.Add(pID);
			
SqlParameter pName=new SqlParameter("@PName", SqlDbType.VarChar,255);
pName.Value="something";
oCmd.Parameters.Add(pName);
			
SqlParameter pDesc=new SqlParameter("@PDescription", SqlDbType.VarChar, 2000);
pDesc.Value="whatever";
oCmd.Parameters.Add(pDesc);
            
DataSet ds=new DataSet("test");
			
try
{
        oCon.Open();
	oCmd.ExecuteNonQuery();
	SqlDataAdapter oDA=new SqlDataAdapter(oCmd);
	oDA.Fill(ds);
}
catch
{
}
finally
{
	oCon.Close();
}

Method #2:

SqlConnection oCon=new SqlConnection("connection string");

StringBuilder sbSQL=new StringBuilder();
sbSQL.AppendFormat("exec sp_test {0}, {1}, {2}", 1, "something", "whatever");

SqlCommand oCmd=new SqlCommand(sbSQL.ToString(), oCon);

DataSet ds=new DataSet("test");

try
{
	oCon.Open();
	oCmd.ExecuteNonQuery();
	SqlDataAdapter oDA=new SqlDataAdapter(oCmd);
	oDA.Fill(ds);
}
catch
{
}
finally
{
	oCon.Close();
}

Hope this helps!