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); } } } }