How to call a CLR Stored Procedure from codebehind of an aspx page?

Question

Hi all

How to call a CLR Stored Procedure from codebehind of an aspx page?

I am using SqlCommand with commandtext as CLR Stored Procedure name and SqlHelper.ExecuteDataSet() as below.

string connectionString = ConfigurationManager.AppSettings["ConnectDB"];
SqlConnection sn = new SqlConnection(connectionString);

SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@query", SqlDbType.Int);
sqlParameters[0].Value = "SELECT * FROM Inventory";
DataSet ds=new DataSet();
try
{
ds = SqlHelper.ExecuteDataset(sn, "[dbo].[prc_clr]", sqlParameters);
}
catch (Exception ex)
{
throw;
}

But I am getting exception "The stored procedure '[dbo].[prc_clr]' doesn't exist.".

Please help.

Answer 1

It looks like you are trying to mix command text and stored procedure. I don't believe the query can be placed into a parameter and used in that way, so try out either of the two ways below:

string connectionString = ConfigurationManager.AppSettings["ConnectDB"];
SqlConnection sn = new SqlConnection(connectionString);

DataSet ds=new DataSet();
try
{
ds = SqlHelper.ExecuteDataset(sn, CommandType.StoredProcedure, "[dbo].[prc_clr]");
}
catch (Exception ex)
{
throw;
}

or

string connectionString = ConfigurationManager.AppSettings["ConnectDB"];
SqlConnection sn = new SqlConnection(connectionString);

string strsql = "SELECT * FROM Inventory";
DataSet ds=new DataSet();
try
{
ds = SqlHelper.ExecuteDataset(sn, CommandType.Text, strSql);
}
catch (Exception ex)
{
throw;
}

And when you have a parameter(s) do something like:

ds = SqlHelper.ExecuteDataset(sn, CommandType.StoredProcedure, "[dbo].[prc_clr]", new SqlParameter("@CategoryID", 1));