Call a stored procedure in Code first approach? [Resolved]

Hi,

I have a stored procedure as below:
CREATE PROCEDURE ImportOrders
(
@AmazonOrdersTemp AmazonOrderType READONLY
)
AS
BEGIN
CREATE TABLE AmazonOrders
(
[ID] [int] IDENTITY(1,1) primary key,
[order-id] [nvarchar](500) NULL,
[order-item-id] [nvarchar](500) NULL,
[purchase-date] [nvarchar](500) NULL,
[payments-date] [nvarchar](500) NULL,
[buyer-email] [nvarchar](500) NULL,
[buyer-name] [nvarchar](500) NULL,
[buyer-phone-number] [nvarchar](500) NULL,
[sku] [nvarchar](500) NULL,
[product-name] [nvarchar](500) NULL,
[quantity-purchased] [nvarchar](500) NULL,
[currency] [nvarchar](500) NULL,
[item-price] [decimal](18, 0) NULL,
[item-tax] [nvarchar](500) NULL,
[shipping-price] [decimal](18, 0) NULL,
[shipping-tax] [nvarchar](500) NULL,
[ship-service-level] [nvarchar](500) NULL,
[recipient-name] [nvarchar](500) NULL,
[ship-address-1] [nvarchar](500) NULL,
[ship-address-2] [nvarchar](500) NULL,
[ship-address-3] [nvarchar](500) NULL,
[ship-city] [nvarchar](500) NULL,
[ship-state] [nvarchar](500) NULL,
[ship-postal-code] [nvarchar](500) NULL,
[ship-country] [nvarchar](500) NULL,
[ship-phone-number] [nvarchar](500) NULL
)

INSERT INTO AmazonOrders
SELECT *
FROM @AmazonOrdersTemp

END

Step 1:
Open IDbContext class and declare the following property:

int ExecuteStoredProcedure(string commandText, DataTable parameters);

Step 2:
Open ObjectContext.cs and write the following code:

public int ExecuteStoredProcedure(string commandText, DataTable parameters)
{
bool hasOutputParameters = false;

var context = ((IObjectContextAdapter)(this)).ObjectContext;


//var connection = context.Connection;
var connection = this.Database.Connection;
//Don't close the connection after command execution


//open the connection for use
if (connection.State == ConnectionState.Closed)
connection.Open();
//create a command object
using (var cmd = connection.CreateCommand())
{
//command to execute
cmd.CommandText = commandText;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter();
param.ParameterName = "AmazonOrdersTemp";
param.SqlDbType = SqlDbType.Structured;
param.Value = parameters;
param.Direction = ParameterDirection.Input;

// move parameters to command object
cmd.Parameters.Add(param);
int result = cmd.ExecuteNonQuery();
return result;
}

}

Step 3:
Declare a field,
private readonly IDbContext _dbContext;

Open your controller, in that the ActionMethod where you want to call the stored procedure, write the following code:
var import = _dbContext.ExecuteStoredProcedure("ImportOrders", objdt);

Thats it...

Mark as Answer if satisfied..........

Regards,
Shree M.

Kavya Shree Mandapalli

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Thank you for your reply..

Thanks,
Varun.

Varun66, if this helps please login to Mark As Answer. | Alert Moderator