public IEnumerable <CustOrderHist> GetCustOrderHist(string customerID)
{
var result = new List <CustOrderHist>();
using (var connection = _providerFactory.CreateConnection())
{
connection.ConnectionString = _connectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"exec dbo.CustOrderHist @pCustomerID";
command.Parameters.Clear();
var pCustomerID = command.CreateParameter();
pCustomerID.ParameterName = "@pCustomerID";
pCustomerID.Value = customerID;
command.Parameters.Add(pCustomerID);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var custOrderHist = new CustOrderHist();
custOrderHist.ProductName = reader.GetString(0);
custOrderHist.Total = reader.GetInt32(1);
result.Add(custOrderHist);
}
}
}
}
return(result);
}
/// <summary>
/// Execute stored procedure CustOrderHist for customerId and return result
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public CustOrderHist GetCustOrderHist(string customerId)
{
try
{
var result = new CustOrderHist();
result.ProductName = new List <string>();
result.Total = new List <int>();
result.CustomerID = customerId;
using (DbConnection connection = this.providerFactory.CreateConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
var command = (SqlCommand)connection.CreateCommand();
command.Parameters.AddWithValue("@custId", customerId);
command.CommandText = "EXECUTE [dbo].[CustOrderHist] @CustomerID = @custId";
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.ProductName.Add((string)reader[CustOrderHistFields.ProductName]);
result.Total.Add((int)reader[CustOrderHistFields.Total]);
}
}
}
return(result);
}
catch (SqlException exc)
{
// ...
return(null);
}
catch (InvalidCastException exc)
{
// ...
return(null);
}
catch (Exception exc)
{
this.UnresolvedExceptions.Add(exc);
return(null);
}
}
public OrderHist GetOrderHists(string id)
{
using (var connection = providerFactory.CreateConnection())
{
connection.ConnectionString = connectString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "CustOrderHist";
command.CommandType = CommandType.StoredProcedure;
var paramId = command.CreateParameter();
paramId.ParameterName = "@CustomerID";
paramId.Value = id;
command.Parameters.Add(paramId);
using (var reader = command.ExecuteReader())
{
var orderHist = new OrderHist
{
CustHist = new List <CustOrderHist>()
};
if (!reader.HasRows)
{
return(null);
}
while (reader.Read())
{
var order = new CustOrderHist
{
ProductName = (string)reader["ProductName"],
Total = (int)reader["Total"]
};
orderHist.CustHist.Add(order);
}
return(orderHist);
}
}
}
}