Thursday, July 18, 2013

Insert, Update, Change Staus Stored Procedure


CREATE proc [dbo].[ProcSMOproject]     
(     
@ProjectID int, 
@ProjectTitle varchar (100), 
@ProjectDesc text, 
@UserID int, 
@Language varchar(50), 
@Status tinyint, 
@Action int  
)     
as     
     
begin     
SET NOCOUNT OFF     
--sp_help SMOProjectMaster 
--Action 1 - Insert 
if(@Action = 1) --insert 
 begin 
 insert into SMOProjectMaster(ProjectTitle,ProjectDesc,UserID,[Language],CreateDate,Status) 
 values(@ProjectTitle,@ProjectDesc,@UserID,@Language,getdate(),@Status) 
 end 
 
if(@Action = 2) --update 
 begin 
 update SMOProjectMaster set ProjectTitle=@ProjectTitle,ProjectDesc=@ProjectDesc,UserID=@UserID,[Language]=@Language, ModifiedDate = getdate() 
 where ProjectID=@ProjectID 
 end 
 
if(@Action = 3) --change status 
 begin 
 update SMOProjectMaster set status=@status , ModifiedDate = getdate() where ProjectID=@ProjectID 
 end 
end


How to Use Stored procedure, here i m using Sql parameter to pass values to stored procedre Example not the correct values i m passing


SqlParameter[] prmSEOContent = new SqlParameter[7];
prmSEOContent[0] = new SqlParameter("@UserId", 0);
prmSEOContent[1] = new SqlParameter("@FirstName", txtfname.Text.ToString().Trim().Replace("'", "''"));
prmSEOContent[2] = new SqlParameter("@Lastname", txtlname.Text.ToString().Trim().Replace("'", "''"));
prmSEOContent[3] = new SqlParameter("@UserEmail", txtemail.Text.ToString().Trim());
prmSEOContent[4] = new SqlParameter("@RoleID", Int32.Parse(ddlrole.SelectedItem.Value.ToString().Trim()));
prmSEOContent[5] = new SqlParameter("@status", "1");
prmSEOContent[6] = new SqlParameter("@action", 1);

int i = SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString().Trim(), CommandType.StoredProcedure, "User", prmSEOContent);
            if (i > 0)
            {
                lblmessage.Text = "User Added successfully. Default Password: 12345 ";
                FillGrid(10, 1);
                txtfname.Text = "";
                txtlname.Text = "";
                txtemail.Text = "";
               
                //Done
            }
            else
            {
                lblmessage.Text = "Please try again leter!";
            }



No comments:

Post a Comment