Insert Select with Same Store Procedure

Question

I am working on website with C#.I have Store Registration Details in two table that is login and registration.Data is inserted in table. Now when i am on login page this email_id and pasword is selected from Database.So, for that i have created one Store procedure which is`

ALTER PROCEDURE [dbo].[P_M_Login] 


    @Email_id nvarchar(50),
    @Password nvarchar(50),
    @Tran_Type nvarchar(1)
AS
 BEGIN

SET NOCOUNT ON;

IF @Tran_Type='I'

 if not exists(select 1 from M_Login where Email_id=@Email_id)


  BEGIN   

                  INSERT INTO M_Login ( Email_id, Password) 
                  VALUES(@Email_id,@Password);
        end

ELSE
 IF @Tran_Type='S'
 BEGIN

        Select Email_id,Password from M_Login;
 END
END`

Now i want to use this Store Procedure in my three tier architecture how can i pass tran type so it will be perfect for me.

This is my DAL Class coding for calling store procedure.

     public Int32 Login(BALRegistration objBEL)
        {

            int result;
            try
            {
                SqlCommand cmd1 = new SqlCommand("P_M_Login", con);
                cmd1.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.AddWithValue("@Regisration_Id", objBEL.Registration_Id); 
                cmd1.Parameters.AddWithValue("@Email_id", objBEL.Email_id);
                cmd1.Parameters.AddWithValue("@Password", objBEL.Password);




                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                result = cmd1.ExecuteNonQuery();
                cmd1.Dispose();
                if (result > 0)
                {
                    return result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }

        }
    }
}

check [this](https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — boop_the_snoot, Jul 17 '17 at 05:29
Can you provide your existing code of calling this store procedure? — Thang Pham, Jul 17 '17 at 05:29
https://www.aspsnippets.com/Articles/GridView-CRUD-Select-Insert-Edit-Update-Delete-using-Single-Stored-Procedure-in-ASPNet.aspx — Jitendra Tiwari, Jul 17 '17 at 05:48
thanks But i need to call it from DataAccess Class three tier architecture. — Nimisha Prajapati, Jul 17 '17 at 05:54
What does your `@Tran_type` do, well I see what it does - but what does it mean, and where does it come from? — Niels Berglund, Jul 17 '17 at 05:59
it gives selection for actions. if i want to perform select operation or insert operation select "I" for insert and "S" for selection. — Nimisha Prajapati, Jul 17 '17 at 06:02

score 0 · Answer 1 · edited Nov 18 '17 at 05:43

0

Try this. Since you have parameter to be accepted in stored procedure, you just need to pass the parameter from the DAL

    string type="s";
    SqlCommand cmd1 = new SqlCommand("P_M_Login", con);
    cmd1.CommandType = CommandType.StoredProcedure;
    cmd1.Parameters.AddWithValue("@Email_id", objBEL.Email_id);
    cmd1.Parameters.AddWithValue("@Password", objBEL.Password);
    cmd1.Parameters.AddWithValue("@Tran_Type ", type);

edited Nov 18 '17 at 05:43

Dani Mathew

808
10
18

answered Jul 17 '17 at 06:13

jithin john

552
4
12

you need to assign type, like string Type='S'; – jithin john Jul 17 '17 at 09:22

Insert Select with Same Store Procedure

1 Answers1