
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