Passing Second Parameter to Stored Procedure in C#

Question

In the following code, I'm trying to retrieve the ID of the manager which then has to be passed as the second parameter to the SQL Stored procedure as shown in the code below:

Currently the code uses only one parameter (EmpID).

 void EmpProfileLists(string EmpId,string EmpName)
    {
        if (EmpId == "0")
        {
            Label2.Text = "There is no Emp associated with your account";
            Label2.Visible = true;
        }
        else Label2.Visible = false;

        try
        {
        Session["EmpId"] = EmpId;
        Label1.Text = EmpId;

                if (Session["MgrName"] != null) Session.Remove("MgrName");

        var claimsIdentity = Context.Emp.Identity as ClaimsIdentity;

        foreach (var claim in claimsIdentity.Claims)
        {
            {
                Session.Add("MgrName", "micro\\"+Session["MgrName"].Substring(Session["MgrName"].LastIndexOf("/")));
            }
        }  
        string MgrName = Session["MgrName"].ToString();
        LoadProfiles(EmpId);
        }
        catch (Exception)
        {
      }
    }

    private void LoadProfiles(string EmpId)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("Exec EmpReports " + EmpId); 
                DataTable dt = new DataTable();
                da.Fill(dt);
                RadGrid1.DataBind();
            }
            catch (Exception)
            {
            }
        }

The code works fine with just the employee id when passed, but I'm trying to add Manager ID as well.

Can someone please help?

https://stackoverflow.com/questions/15569860/passing-parameter-to-stored-procedure-in-c-sharp — Dale K, Sep 16 '19 at 09:52
`Exec EmpReports " + EmpId,` Please read up on SQL Injection. — mjwills, Sep 16 '19 at 09:52
Rather than just using a string to call a procedure with parameters, you should use the `Parameters` collection of the `SqlCommand`, as in the question Dale found. This might feel long-winded, but it avoids all sorts of data type conversion errors, and protects you from SQL injection attacks. — Robin Bennett, Sep 16 '19 at 10:15

score 1 · Accepted Answer · answered Sep 16 '19 at 10:27

1

SqlCommand command = new SqlCommand("EmpReports", WebConfigurationManager.ConnectionStrings["EmpReportConnectionString"].ToString());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@EmpId", SqlDbType.Int).Value = EmpId;
command.Parameters.Add("@ManId", SqlDbType.Int).Value = ManId;
command.ExecuteNonQuery();

answered Sep 16 '19 at 10:27

Stefan

652
5
19

Thanks Stefan. Where in the code section do I retrieve the value for ManId? – Karu3103 Sep 16 '19 at 10:42
1

@Karu3103 are you saying that `@ManId` is an `output` parameter? Because thats not clear from your question. – Dale K Sep 16 '19 at 10:51
Use this for output parameters: command.Parameters.Add("@Result ", SqlDbType.Bit).Direction = ParameterDirection.Output; – Stefan Sep 16 '19 at 11:07
I'm lost as to what you want to know. Could you please rephrase your question and specify where ManId is retrieved and what input and output parameters you use in your stored procedure? – Stefan Sep 16 '19 at 11:31
Hi Stefan, I have added the stored proc as part of the question. ManId is nothing but the manager Id which needs to be the second parameter that needs to be passed to the stored procedure. If you look at line 23 in the c# code, it tells how ManId is combined with "micro\\". – Karu3103 Sep 16 '19 at 11:44
You collect the manager id in the foreach, so this means you want to run the SP every iteration? – Stefan Sep 16 '19 at 11:48
Yes please, is it possible to retrieve the MgrId from that part of the code, store it in a variable and pass it to the function which calls the stored proc? – Karu3103 Sep 16 '19 at 11:57
That is possible if the situation allows this. I mean, if you for instance want to use the first manId found, than you can break the foreach and use that value to call you SP. Create a variable outside the foreach to hold the manId value, then use it afterwards, after you check if the variable contains a valid manId value. – Stefan Sep 16 '19 at 12:00

Passing Second Parameter to Stored Procedure in C#

1 Answers1