Lets Have a fun with Technology.

BTemplates.com

Follow by Email

Saturday, November 15, 2008

Execute Store Procedure from Asp.net


Execute Store Procedure from Asp.net

In this Example I Show How to Set Parameter from Asp.Net application and execute Store Procedure from asp.net Application.
First Declare Private Variable for Store Procedure

private const string SP_USER_NEWDESG = "IDBadge_DesignationInsert";


Method to Set Parameter and Execute Store Procedure

public string Save_Designation(Desg desg_code)
    {
     
        // Execute SQL Command
        SqlCommand sqlCmd = new SqlCommand();
        AddParamToSQLCmd(sqlCmd, "@desg_code", SqlDbType.Int, 
256, ParameterDirection.Input, desg_code.desg_code);
        AddParamToSQLCmd(sqlCmd, "@desg_name", SqlDbType.NText,
256, ParameterDirection.Input, desg_code.desg_name);
        SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_NEWDESG);
        return ExecuteScalarCmd(sqlCmd);
        
    }
  
private void AddParamToSQLCmd(SqlCommand sqlCmd, string paramId, SqlDbType sqlType, 
int paramSize, ParameterDirection paramDirection, object paramvalue)
    {
        // Validate Parameter Properties
        if (sqlCmd == null)
            throw (new ArgumentNullException("sqlCmd"));
        if (paramId == string.Empty)
            throw (new ArgumentOutOfRangeException("paramId"));

        // Add Parameter
        SqlParameter newSqlParam = new SqlParameter();
        newSqlParam.ParameterName = paramId;
        newSqlParam.SqlDbType = sqlType;
        newSqlParam.Direction = paramDirection;

        if (paramSize > 0)
            newSqlParam.Size = paramSize;

        if (paramvalue != null)
            newSqlParam.Value = paramvalue;

        sqlCmd.Parameters.Add(newSqlParam);
    }
  private string ExecuteScalarCmd(SqlCommand sqlCmd)
    {
        string result = "";
        using (SqlConnection cn = new SqlConnection(this.ConnectionString))
        {
            try
            {
                cn.Open();
                sqlCmd.Connection = cn;
                result = Convert.ToString(sqlCmd.ExecuteScalar());
                return result;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        
    } 
 
 
PLEASE WRITE YOUR VIEW ON SP IN BELOW COMMENT BOX.
 
 
  
 
 First page SP   Second Page SP   Third Page SP      Fourth Page SP 




Prev Post                                                      All Post                                                           Next Post

0 comments:

Post a Comment