10/13/2008 12:59:43 PM # by

Yordan Georgiev

Might be obvious for many , but herewith an example of how-to call stored procedure . I spent couple of hours figuring out how-to get out parameters correctly ...




        public bool UpdateProject(string procedureName , ref string msg, string domainName,
            string projectId ,  string prsUser , string prsPassword  )

        {
            int ret = 1 ;    //assume false
            
            try
            {
                
                using (Database db = new Database(_ConnectionString))
                {
                    //create command with the procedure name
                    DbCommand cmd = db.GetStoredProcedureCommand(procedureName);
          
          //add here in parameters
                    db.AddInParameter(cmd,"@domain_user", DbType.String, (object)domainName);
                    db.AddInParameter(cmd,      "@project_id", DbType.Int16, (object)projectId);
                    //add out parameters
                    db.AddInOutParameter(cmd,   "@msg", DbType.String,  (object)msg);
                    db.AddInOutParameter(cmd,   "@ret", DbType.Int16,  (object)ret);

          //execute the actual procedure
                    db.ExecuteNonQuery(cmd);
          
          
          //get the output parameter values from the procedure
                    ret = Int32.Parse( cmd.Parameters[ "@ret" ].Value.ToString());
                    msg = cmd.Parameters[ "@msg" ].Value.ToString();

          //returns true if ret == 0 and false if ret == 1
                    return !System.Convert.ToBoolean((int)ret);
                    
                } //eof using
            } //eof try
            catch (Exception e)
            {
                msg = "An error in the application occurred. Report the following error code:mlcv1" + e.Message;
                return false;
            } //eof catch
        }  //eof method