CODEWITHSUNDEEP
c#sqlentity-framework
user1196985
user1196985

Reputation: 130

Execute stored procedure from c#

I have c# code which executes properly on my local machine but unable to execute it on server machine.

In my code, I am calling SQL stored procedure in c# as, (c# code 1)

 int res = db.Database.ExecuteSqlCommand("exec UpsertUser @OrgUnitId, @ExtUserId, @IntUserId, @IsTestUser, @KronosId",
                                    new SqlParameter("@OrgUnitId", Fac),
                                    new SqlParameter("@ExtUserId", id),
                                    new SqlParameter("@IntUserId", IntUserId),
                                    new SqlParameter("@IsTestUser", DBNull.Value),
                                    new SqlParameter("@KronosId", TenetId)
                                    );
                                }

(In above code I also tried 0 instead of DBNull.value.) Related stored procedure's parameter in SQL db is,

    ALTER procedure [dbo].[UpsertUser]
@OrgUnitId int = null,
@ExtUserId varchar(50) = null,
@IntUserId varchar(50) = null,
@IsTestUser int = NULL,
@KronosId VARCHAR(10) = NULL,
@MIPFlag TINYINT =NULL

AS

Both codes are also same on local and on server.

BUT, if I change the sequence of parameter in SQL db and put kronosId before IstestUser, and change stored procedure in c# to,

C# modification: code 2

 int res = db.Database.ExecuteSqlCommand("exec UpsertUser @OrgUnitId, @ExtUserId, @IntUserId, @KronosId",
                                new SqlParameter("@OrgUnitId", Fac),
                                new SqlParameter("@ExtUserId", id),
                                new SqlParameter("@IntUserId", IntUserId),
                                new SqlParameter("@KronosId", TenetId)
                                );

then it works on server as well.

Am I missing something in my c# code 1?

Thanks for help.

Upvotes: 3

Views: 2345

Answers (1)

Sergey L
Sergey L

Reputation: 1492

1) You can get an actual error on the server if you run the sql server profiler and trace the queries to your database. As you said you have SSMS, look at the tools menu to find the profiler. You may find the failed statement and see what an actual error is.

2) My guess is that you have a type conversion issue. ExecuteSqlCommand creates its own sql statement basing in the string your provided and it declares parameters your provided (like, declare @OrgUnitId int;) in this statement. The type of variable is defined explicitly in the parameter definition or induced from the parameter value. But it is not possible if you provide null or DBNull.Value, so the default nvarchar type is used. In your case it is in contradiction with type of @IsTestUser.

Use explicit type definition for your parameters:

new SqlParameter("@IsTestUser", SqlDbType.Int)
        {
            Value = YouValue
        };

Side notes:

The best practice is to always define the type for parameters.

You can use Bit type instead of Int for this parameter.

It is better not to allow NULL for values that are logically are not null-able. IsTestUser should be either true or false, probably, you don't need the null value here that in this case means - I don't know. You still may have default value 0 in your database if you don't want to specify value explicitly in inserts.

Upvotes: 1

Related Questions