locked
stored procedure to run successfully RRS feed

  • Question

  • User1182587605 posted

    I have the following stored procedure, I need a best code to run it in C#. Please help me in this.

    ALTER PROCEDURE [dbo].[ItemDetailsPicker]
    	-- Add the parameters for the stored procedure here	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	insert into TempTest(itemID,description,cost)
    		select itemnmbr,itemdesc,cast(round(AVG(unitcost),2) as decimal(10,2)) as unitcost from AFS.dbo.POP10110 where DEX_ROW_TS >=DATEADD(day,-60, GETDATE()) group by itemnmbr,itemdesc
    END

    I am running it with the following code,

    protected void BtnGetErPData_Click(object sender, EventArgs e)
            {
                string confirmValue = Request.Form["confirm_value"];
                if (confirmValue == "Yes")
                {
                    string afsconstring = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices1"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(afsconstring))
                    {
                        using (SqlCommand cmd = new SqlCommand("ItemDetailsPicker", con))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;                        
                            con.Open();
                            cmd.ExecuteNonQuery();
                            lblMessage.CssClass= "alert alert-success";
                            lblMessage.Text = "Msg 23: Data updated successfully";
                        }
                    }
                }

    Can this be done better both in code and the stored procedure to check the existing rows in DB and entering only non-existent records.

    Regards,

    Deepak


    Thursday, December 1, 2016 6:26 PM

Answers

  • User283571144 posted

    Hi acmedeepak,

    Can you please give me an exact query as per my requirement. I am looking into it but having trouble as I am new.

    According to your description, I suggest you could try below stored procedure.

    Create PROCEDURE procCustomerSave
    @Name nvarchar,
    @Inactive Bit =1
    AS
    if exists(select * from [dbo].[Employee] where [Name] = @Name)
    begin
    //Record exists RETURN 0 end else begin
    //Record not exists then insert insert into [dbo].[Employee] values (@Name,@Inactive) return 1 end GO

    More details, you could refer to follow codes:

    My Database:

    ASPX codes:

        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br/>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <hr/>
            Result:
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>

    Code-behind:

       protected void Button1_Click(object sender, EventArgs e)
            {
                string afsconstring = System.Configuration.ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(afsconstring))
                {
                    using (SqlCommand cmd = new SqlCommand("procCustomerSave", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Name", TextBox1.Text);
    //Get Return Value SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); con.Open(); cmd.ExecuteNonQuery();
    //Check if ((int)returnValue.Value == 0) { Label1.Text = "The record is already exists"; } else { Label1.Text = "Insert Success"; } } } }

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 4, 2016 11:49 AM

All replies

  • User283571144 posted

    Hi acmedeepak,

    Can this be done better both in code and the stored procedure to check the existing rows in DB and entering only non-existent records.

    According to your codes, I think your code is very good now.

    But I suggest you could consider more about your requirement.

    According to your SP, I couldn't see any code about check non-existent records.

    I found you directly insert the record into the table.

    Besides, if user inserted existent records, I suggest you could return a suggestion to suggest user enter again.

    More details about how to check the non-existent records SP, I suggest you could refer to follow codes:

    IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
    BEGIN
        --Return select value
    END
    ELSE
    BEGIN
       -- INSERT HERE
    END

    Best Regards,

    Brando

    Friday, December 2, 2016 2:24 AM
  • User1182587605 posted

    Brando,

    Can you please give me an exact query as per my requirement. I am looking into it but having trouble as I am new.

    Regards,

    Deepak

    Friday, December 2, 2016 4:41 AM
  • User283571144 posted

    Hi acmedeepak,

    Can you please give me an exact query as per my requirement. I am looking into it but having trouble as I am new.

    According to your description, I suggest you could try below stored procedure.

    Create PROCEDURE procCustomerSave
    @Name nvarchar,
    @Inactive Bit =1
    AS
    if exists(select * from [dbo].[Employee] where [Name] = @Name)
    begin
    //Record exists RETURN 0 end else begin
    //Record not exists then insert insert into [dbo].[Employee] values (@Name,@Inactive) return 1 end GO

    More details, you could refer to follow codes:

    My Database:

    ASPX codes:

        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br/>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <hr/>
            Result:
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>

    Code-behind:

       protected void Button1_Click(object sender, EventArgs e)
            {
                string afsconstring = System.Configuration.ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(afsconstring))
                {
                    using (SqlCommand cmd = new SqlCommand("procCustomerSave", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Name", TextBox1.Text);
    //Get Return Value SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); con.Open(); cmd.ExecuteNonQuery();
    //Check if ((int)returnValue.Value == 0) { Label1.Text = "The record is already exists"; } else { Label1.Text = "Insert Success"; } } } }

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 4, 2016 11:49 AM