I am trying to insert data into database using stored procedure via ExecuteSqlRawAsync using the below code.
var param = new SqlParameter[] {
new SqlParameter() {
ParameterName = "@customerId",
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = obInfo.customerId
},
new SqlParameter() {
ParameterName = "@customerName",
SqlDbType = SqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input,
Value = obInfo.customerName
},
new SqlParameter() {
ParameterName = "@customerPhone",
SqlDbType = SqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input,
Value = obInfo.customerPhone
},
new SqlParameter() {
ParameterName = "@customerAddress",
SqlDbType = SqlDbType.VarChar,
Size = 1000,
Direction = System.Data.ParameterDirection.Input,
Value = obInfo.customerAddress
}
};
await _db.Database.ExecuteSqlRawAsync("exec sp_InsertData @customerId, @customerName, @customerPhone, @customerAddress", param);
My stored procedure has few more parameters having default value but does not need to add in the above code, ExecuteSqlRawAsync execute the stored procedure with parameter order, Is there any other way to execute stored procedure, so it will execute as per given parameters, the rest of parameters should have the default value.
My Stored procedure look like this
Create proc sp_InsertData
@customerId INT,
@customerName VARCHAR(100)=NULL,
@customerPhone VARCHAR(50)=NULL,
@refName VARCHAR(50)=NULL,
@refPhone VARCHAR(50)=NULL,
@customerAddress VARCHAR(500)=NULL,
@userId INT=0,
@changeIP VARCHAR(20)=NULL
AS
BGEIN
IF @customerId=0
BEGIN
INSERT INTO tbl_CustomerInfo(customerName, customerPhone, customerAddress)
VALUES(@customerName, @customerPhone, @customerAddress)
END
ELSE
BEGIN
///SOME STATEMENT HERE LIKE CALLING FROM SOME OTHER ROUTINE....
END
END
Thanks in advance...