How to Call C# Function in Stored Procedure

How to call C# function in stored procedure

Yes, it is possible to use .NET in a SQL Server 2005 database. Be aware that the .NET version supported by SQL Server 2005 is 2.0.

Here's a link for an introduction to Making a CLR stored procedure using Visual Studio

Call a stored procedure with parameter in c#

It's pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd variable, and never use it. Here, however, you will use that instead of da.InsertCommand.

Also, use a using for all disposable objects, so that you are sure that they are disposed properly:

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

Create public method to call a stored procedure in ASP.NET using C#

You need to have the method as following.

public void ExecuteProcedure(string procName, List<OleDbParameter> parameters)
{
    try
    {
        vbcon(); // connection
        cmd = new OleDbCommand(procName, ConnS);
        cmd.CommandType = CommandType. StoredProcedure;
        foreach(var parameter in parameters)
        {
            cmd.Parameters.Add(parameter);
        }
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        HttpContext.Current.Response.Write("<script>alert('Query error! 
         :" + ex.Message + "'</script>");
    }
    finally
    {
        GC.Collect();
    }
}

This is how you call the method

clasCon Crud = new clasCon();
var procName = "MyProcedure";
var parameters = new List<OleDbParameter>();
parameters.Add(new OleDbParameter("@FName", txtFname.Text.Trim()));
parameters.Add(new OleDbParameter("@MName", txtMname.Text.Trim()));
parameters.Add(new OleDbParameter("@LName", txtLname.Text.Trim()));

Crud.ExecuteProcedure(procName, parameters);

Call method in class that passes parameter to a stored procedure in DAL method

To pass the value of departmentID from main to your DAL method start changing the signature of the GetDeptData method to this one

public bool GetDeptData(string StartDate,string EndDate, string deptId, out DataTable DTDepartmentData)
{

     // The SqlConnection should be created here and destroyed 
     // here, everytime you call this method

     using(SqlConnection con = new SqlConnection(....connectionstring....))
     {
        // then use the deptId value in the creation 
        // of the parameter @DepartmentId
       .....
       cmd.Parameters.Add("@DepartmentID", SqlDbType.VarChar).Value = deptId; 
       adapter.Fill(DTDepartmentData);
       return true;

    }
 }
 catch(Exception ....)
 {
     ...
 }  

Now in your Program.cs you should create an instance of your DAL class and call the method passing the values required

 DataTable dtResult = null;
 string startDate = SomeCodeToGetTheStartDate();
 string endDate = SomeCodeToGetTheEndDate();
 string deptId = SomeCodeToGetTheDeptId();
 MyDALClass cs = new MyDALClass();
 if(cs.GetDeptData(startDate, endDate, deptId, out dtResult)
     .... success ....

Side note: Are you sure that the departmentId is a string value and not an integer?

Execute a stored procedure inside user defined function

That is not possible. Stored Procedures can be invoked from a client only, not from a user defined function.

You cannot chain multiple Stored procedure executions in the same transactional scope either.

The only alternative, depending on what your stored procedures do, would be to use Transactional Batch, so define a group of item operations as a single transaction unit, the limitation is that these are operations that cannot be feedback from one another (you cannot put a Read operation with a condition to perform a Write operation in the same batch, batches do not support conditions), so it really depends on the logic of your stored procedures. Transactional batch cannot be invoked from user defined functions, only from the client.

Calling SQL Defined function in C#

You can't just call the function name, you will need to write an inline SQL statement which makes use of the UDF:

SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);

And remove the CommandType, this isn't a Stored Procedure, its a User Defined Function.

In all:

public void TotalCupom(int cupom)
{ 
    float SAIDA;           
    SqlDataAdapter da2 = new SqlDataAdapter();
    if (conex1.State == ConnectionState.Closed)
    {
        conex1.Open();
    }
    SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);
    SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int);
    code1.Value = cupom;
    SAIDA = Totalf.ExecuteScalar();

    return SAIDA;
}

---

Related Topics