How to pass parameters in proper manner in C#?

Question

I have stored procedure as below:

ALTER PROCEDURE [dbo].[Insert_tblCustomer] 
-- Add the parameters for the stored procedure here
@Username varchar(20) = null, 
@Password varchar(20)= null,
@UserType varchar(20)='User',
@FirstName varchar(50)=null,
@LastName varchar(50)=null,
@DateOfBirth varchar(50)=null,
@Gender varchar(10)=null,
@Unit_No int = null,
@St_No int=null,
@St_Name varchar(20)=null,
@Suburb varchar(20)=null,
@State varchar(20)=null,
@Postcode int=null,
@Email varchar(50)='',
@Phone varchar(15)=null

AS... and There are 5 fields i.e. username,password,firstname,lastname and email are must fields. Now from c#, I am trying this :

dbConnection target = new dbConnection(); // TODO: Initialize to an appropriate value
string _query = string.Empty; // TODO: Initialize to an appropriate value

SqlParameter[] param = new SqlParameter[5]; // TODO: Initialize to an appropriate value
param[0] = new SqlParameter("@Username", "hakoo");
param[1] = new SqlParameter("@Password", "hakoo");
param[2] = new SqlParameter("@FirstName", "Hakoo");
param[3] = new SqlParameter("@LastName", "Hakoo");
param[4] = new SqlParameter("@Email", "haks@abc.com");
bool expected = true; // TODO: Initialize to an appropriate value
bool actual;
actual = target.Execute_InsertQuery("dbo.Insert_tblCustomer", param);

But, I am getting Assertfail exception. I am newbie to use stored procedures with test. Can anyone correct me where I am wrong?

"Added Assert part"

bool expected = true; // TODO: Initialize to an appropriate value
bool actual;
actual = target.Execute_InsertQuery("dbo.Insert_tblCustomer", param);

dbConnection is my created core class, where I am putting generic query :

public bool Execute_InsertQuery(string _query, SqlParameter[] param) 
{

    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = OpenConnection();
        cmd.CommandText = _query;

        cmd.Parameters.AddRange(param);
        cmd.ExecuteNonQuery();
        return true;
    }
    catch (Exception ex)
    {
        Console.Write(ex.StackTrace.ToString());
        return false;
    }
}

Update for getting Error : I tried to pass all parameters in this manner :

dbConnection c = new dbConnection();
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[15];
param[0] = new SqlParameter("@Username", "hakoo");

param[1] = new SqlParameter("@Password", "hakoo");
param[2] = new SqlParameter("@UserType", "User");
param[3] = new SqlParameter("@FirstName", "Hakoo");
param[4] = new SqlParameter("@LastName", "Hakoo");
param[5] = new SqlParameter("@DateOfBirth", "02/11/88");
param[6] = new SqlParameter("@Gender", "");
param[7] = new SqlParameter("@Unit_No", null);
param[8] = new SqlParameter("@St_No", 25);
param[9] = new SqlParameter("@St_Name", "anc st");
param[10] = new SqlParameter("@Suburb", "ancst");
param[11] = new SqlParameter("@State", "assadd@few");
param[12] = new SqlParameter("@Postcode", 2615);
param[13] = new SqlParameter("@Email", "abc@def.com.au");
param[14] = new SqlParameter("@Phone", "165103548");
c.Execute_InsertQuery("dbo.Insert_tblCustomer", param);

But for this, I am getting this error :

The parameterized query '(@Username nvarchar(5),@Password nvarchar(5),@UserType nvarchar(' expects the parameter '@Unit_No', which was not supplied. I can execute this Store procedure in SQL Server.

they all are varchar. Just getting AssertFailException. I expect True upon insertion but getting Actual value is False. — Hakoo Desai, May 16 '13 at 17:52
What is "target.Execute_InsertQuery"? It would appear that has the interesting code here — Marc Gravell, May 16 '13 at 17:55
Also, typically speaking "must" fields should not have a default specified — fnostro, May 16 '13 at 17:58
Please post the entire code block which sets up these parameters, especially the parts that set up the query and execute it. Like, post the code from the Execute_InsertQuery() function. What you're doing here isn't the "normal" way of calling procs - see here: http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp — Jasmine, May 16 '13 at 17:58

score 5 · Answer 1 · answered May 16 '13 at 17:59

5

There is no single best way to pass parameters. What I would say is that it is a bad idea to have something like SqlParameter a long way away from the command (SqlCommand) - if you aren't dealing with the database yet I would just pass them as strings/ints/etc, or as members on an object.

As for what is wrong in this case: without seeing the command-related code all I can say is: 42

answered May 16 '13 at 17:59

Marc Gravell

1,026,079
266
2,566
2,900

I noted your suggestion, I removed null from MUST field, What, I am trying to do here is, say I have parameters with SP like this (p1,p2,p3,p4,p5) so by using SqlParameter[] p , I am assigning only p[0] = (p1,"p1"), p[1] = (p2,"p2") and p[2]=(p4,"p4"). My confusion is does index of p refer no of parameter? like : 0 refer p1, 1 refer to p2, 2 refer to p3.... so on? – Hakoo Desai May 16 '13 at 18:10
@Hakoo that depends on the provider. SqlClient passes parameters by name. Some providers pass by index. Either way, making the caller give you SqlParameter[] is pretty ugly. I strongly recommend you take a look at the elegance of "dapper" and how it passes parameters around. Indeed, you can call stored procedures with dapper trivially. – Marc Gravell May 16 '13 at 18:52
Hi Marc, I feeling tough to understand you. Can you give some example? I am using Store Procedure only and passing parameters. – Hakoo Desai May 16 '13 at 19:00
@Hakoo I'll explain via code. If that was me, my entire code would be (via "dapper"): `conn.Execute("dbo.Insert_tblCustomer", new { Username = "hakoo", ... , Email = "haks@abc.com" }, commandType: CommandType.StoredProcedure);` - and nothing else whatsoever. – Marc Gravell May 16 '13 at 19:35

score 1 · Accepted Answer · edited May 16 '13 at 19:11

1

One thing that might help is to specify the command type.

   SqlCommand cmd = new SqlCommand();
   cmd.Connection = OpenConnection();
   cmd.CommandText = _query;
   cmd.CommandType = CommandType.StoredProcedure;

edited May 16 '13 at 19:11

John Saunders

160,644
26
247
397

answered May 16 '13 at 18:39

Loren Shaw

445
4
16

Glad to help. Odds were, it was treating the stored procedure command as just a text query, which is the default. When creating a sql command, it's best practice to specify the command type. – Loren Shaw May 16 '13 at 19:23
I did it at first place, but afterwards I removed it by thinking that I can process Query(Putting parameters in query) as well Stored Procedure with same function. – Hakoo Desai May 16 '13 at 19:25

How to pass parameters in proper manner in C#?

2 Answers2