ASP.NET操作数据库经典代码

public class DataLayer
{
private static OleDbConnection _connection;

private static OleDbConnection Connection
{
get
{
if (DataLayer._connection == null)
{
DataLayer._connection = new OleDbConnection();
DataLayer._connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NorthWindConnectionString"].ConnectionString;
}

return DataLayer._connection;
}
}

#region Helper

private static OleDbParameter CreateParameter(string key, object value)
{
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = key;
parameter.Value = value;

return parameter;
}

private static int ExecuteNonQuery(OleDbCommand command)
{
int affectedRecords;
try
{
command.Connection.Open();
affectedRecords = command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
}

return affectedRecords;
}

#endregion

#region Customers DataLayer

private static Customer CreateCustomerData(OleDbDataReader dataReader)
{
Customer customer = new Customer();
customer.CustomerID = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
customer.CompanyName = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
customer.ContactName = dataReader.IsDBNull(2) ? string.Empty : dataReader.GetString(2);
customer.ContactTitle = dataReader.IsDBNull(3) ? string.Empty : dataReader.GetString(3);
customer.Address = dataReader.IsDBNull(4) ? string.Empty : dataReader.GetString(4);
customer.City = dataReader.IsDBNull(5) ? string.Empty : dataReader.GetString(5);
customer.Region = dataReader.IsDBNull(6) ? string.Empty : dataReader.GetString(6);
customer.PostalCode = dataReader.IsDBNull(7) ? string.Empty : dataReader.GetString(7);
customer.Country = dataReader.IsDBNull(8) ? string.Empty : dataReader.GetString(8);
customer.Phone = dataReader.IsDBNull(9) ? string.Empty : dataReader.GetString(9);
customer.Fax = dataReader.IsDBNull(10) ? string.Empty : dataReader.GetString(10);

return customer;
}

public static CustomerCollection GetCustomers()
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax From Customers";
command.Connection = DataLayer.Connection;

CustomerCollection customers = new CustomerCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Customer customer = DataLayer.CreateCustomerData(dataReader);
customers.Add(customer);
}
}
finally
{
command.Connection.Close();
}

return customers;
}

public static Customer GetCustomer(string customerID)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax From Customers WHERE CustomerID = ?";
command.Parameters.Add(DataLayer.CreateParameter("CustomerID", customerID));
command.Connection = DataLayer.Connection;

try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
if (dataReader.Read())
{
Customer customer = DataLayer.CreateCustomerData(dataReader);
return customer;
}
}
finally
{
command.Connection.Close();
}

return null;
}

public static int InsertCustomer(Customer customer)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("CustomerID", customer.CustomerID));
command.Parameters.Add(DataLayer.CreateParameter("CompanyName", customer.CompanyName));
command.Parameters.Add(DataLayer.CreateParameter("ContactName", customer.ContactName));
command.Parameters.Add(DataLayer.CreateParameter("ContactTitle", customer.ContactTitle));
command.Parameters.Add(DataLayer.CreateParameter("Address", customer.Address));
command.Parameters.Add(DataLayer.CreateParameter("City", customer.City));
command.Parameters.Add(DataLayer.CreateParameter("Region", customer.Region));
command.Parameters.Add(DataLayer.CreateParameter("PostalCode", customer.PostalCode));
command.Parameters.Add(DataLayer.CreateParameter("Country", customer.Country));
command.Parameters.Add(DataLayer.CreateParameter("Phone", customer.Phone));
command.Parameters.Add(DataLayer.CreateParameter("Fax", customer.Fax));

return DataLayer.ExecuteNonQuery(command);
}

public static int UpdateCustomer(Customer customer)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "UPDATE Customers SET CustomerID = ?, CompanyName = ?, ContactName = ?, ContactTitle = ?, " +
"Address = ?, City = ?, Region = ?, PostalCode = ?, Country = ?, Phone = ?, Fax = ? WHERE CustomerID = ?";

command.Parameters.Add(DataLayer.CreateParameter("CustomerID", customer.CustomerID));
command.Parameters.Add(DataLayer.CreateParameter("CompanyName", customer.CompanyName));
command.Parameters.Add(DataLayer.CreateParameter("ContactName", customer.ContactName));
command.Parameters.Add(DataLayer.CreateParameter("ContactTitle", customer.ContactTitle));
command.Parameters.Add(DataLayer.CreateParameter("Address", customer.Address));
command.Parameters.Add(DataLayer.CreateParameter("City", customer.City));
command.Parameters.Add(DataLayer.CreateParameter("Region", customer.Region));
command.Parameters.Add(DataLayer.CreateParameter("PostalCode", customer.PostalCode));
command.Parameters.Add(DataLayer.CreateParameter("Country", customer.Country));
command.Parameters.Add(DataLayer.CreateParameter("Phone", customer.Phone));
command.Parameters.Add(DataLayer.CreateParameter("Fax", customer.Fax));
command.Parameters.Add(DataLayer.CreateParameter("OriginalCustomerID", customer.CustomerID));
command.Connection = DataLayer.Connection;

return DataLayer.ExecuteNonQuery(command);
}

public static int DeleteCustomer(Customer customer)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "DELETE FROM Customers WHERE CustomerID = ?";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("OriginalCustomerID", customer.CustomerID));

return DataLayer.ExecuteNonQuery(command);
}

#endregion

#region Orders DataLayer

private static Order CreateOrderData(OleDbDataReader dataReader)
{
Order order = new Order();
order.OrderID = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0);
order.CustomerID = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
order.EmployeeID = dataReader.IsDBNull(2) ? 0 : dataReader.GetInt32(2);
order.OrderDate = dataReader.IsDBNull(3) ? DateTime.MinValue : dataReader.GetDateTime(3);
order.RequiredDate = dataReader.IsDBNull(4) ? DateTime.MinValue : dataReader.GetDateTime(4);
order.ShippedDate = dataReader.IsDBNull(5) ? DateTime.MinValue : dataReader.GetDateTime(5);
order.ShipVia = dataReader.IsDBNull(6) ? 0 : dataReader.GetInt32(6);
order.Freight = dataReader.IsDBNull(7) ? 0.0M : dataReader.GetDecimal(7);
order.ShipName = dataReader.IsDBNull(8) ? string.Empty : dataReader.GetString(8);
order.ShipAddress = dataReader.IsDBNull(9) ? string.Empty : dataReader.GetString(9);
order.ShipCity = dataReader.IsDBNull(10) ? string.Empty : dataReader.GetString(10);
order.ShipRegion = dataReader.IsDBNull(11) ? string.Empty : dataReader.GetString(11);
order.ShipPostalCode = dataReader.IsDBNull(12) ? string.Empty : dataReader.GetString(12);
order.ShipCountry = dataReader.IsDBNull(13) ? string.Empty : dataReader.GetString(13);

return order;
}

public static OrderCollection GetOrders()
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders";
command.Connection = DataLayer.Connection;

OrderCollection orders = new OrderCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Order order = DataLayer.CreateOrderData(dataReader);
orders.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orders;
}

public static OrderCollection GetOrders(string customerID)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE CustomerID = ?";
command.Parameters.Add(DataLayer.CreateParameter("CustomerID", customerID));
command.Connection = DataLayer.Connection;

OrderCollection orders = new OrderCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Order order = DataLayer.CreateOrderData(dataReader);
orders.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orders;
}

public static OrderCollection GetOrders(int orderID)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE OrderID = ?";
command.Parameters.Add(DataLayer.CreateParameter("OrderID", orderID));
command.Connection = DataLayer.Connection;

OrderCollection orders = new OrderCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Order order = DataLayer.CreateOrderData(dataReader);
orders.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orders;
}

public static int InsertOrder(Order order)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("CustomerID", order.CustomerID));
command.Parameters.Add(DataLayer.CreateParameter("EmployeeID", order.EmployeeID));
command.Parameters.Add(DataLayer.CreateParameter("OrderDate", order.OrderDate));
command.Parameters.Add(DataLayer.CreateParameter("RequiredDate", order.RequiredDate));
command.Parameters.Add(DataLayer.CreateParameter("ShippedDate", order.ShippedDate));
command.Parameters.Add(DataLayer.CreateParameter("ShipVia", order.ShipVia));
command.Parameters.Add(DataLayer.CreateParameter("Freight", order.Freight));
command.Parameters.Add(DataLayer.CreateParameter("ShipName", order.ShipName));
command.Parameters.Add(DataLayer.CreateParameter("ShipAddress", order.ShipAddress));
command.Parameters.Add(DataLayer.CreateParameter("ShipCity", order.ShipCity));
command.Parameters.Add(DataLayer.CreateParameter("ShipRegion", order.ShipRegion));
command.Parameters.Add(DataLayer.CreateParameter("ShipPostalCode", order.ShipPostalCode));
command.Parameters.Add(DataLayer.CreateParameter("ShipCountry", order.ShipCountry));

return DataLayer.ExecuteNonQuery(command);
}

public static int UpdateOrder(Order order)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "UPDATE Orders SET CustomerID = ?, EmployeeID = ?, OrderDate = ?, RequiredDate = ?, ShippedDate = ?, " +
"ShipVia = ?, Freight = ?, ShipName = ?, ShipAddress = ?, ShipCity = ?, ShipRegion = ?, ShipPostalCode = ?, ShipCountry = ? WHERE OrderID = ?";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("CustomerID", order.CustomerID));
command.Parameters.Add(DataLayer.CreateParameter("EmployeeID", order.EmployeeID));
command.Parameters.Add(DataLayer.CreateParameter("OrderDate", order.OrderDate));
command.Parameters.Add(DataLayer.CreateParameter("RequiredDate", order.RequiredDate));
command.Parameters.Add(DataLayer.CreateParameter("ShippedDate", order.ShippedDate));
command.Parameters.Add(DataLayer.CreateParameter("ShipVia", order.ShipVia));
command.Parameters.Add(DataLayer.CreateParameter("Freight", order.Freight));
command.Parameters.Add(DataLayer.CreateParameter("ShipName", order.ShipName));
command.Parameters.Add(DataLayer.CreateParameter("ShipAddress", order.ShipAddress));
command.Parameters.Add(DataLayer.CreateParameter("ShipCity", order.ShipCity));
command.Parameters.Add(DataLayer.CreateParameter("ShipRegion", order.ShipRegion));
command.Parameters.Add(DataLayer.CreateParameter("ShipPostalCode", order.ShipPostalCode));
command.Parameters.Add(DataLayer.CreateParameter("ShipCountry", order.ShipCountry));
command.Parameters.Add(DataLayer.CreateParameter("OriginalOrderID", order.OrderID));

return DataLayer.ExecuteNonQuery(command);
}

public static int DeleteOrder(Order order)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "DELETE FROM Orders WHERE OrderID = ?";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("OriginalOrderID", order.OrderID));

return DataLayer.ExecuteNonQuery(command);
}

#endregion

#region Order Details DataLayer

private static OrderDetail CreateOrderDetailData(OleDbDataReader dataReader)
{
OrderDetail orderDetail = new OrderDetail();
orderDetail.OrderID = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0);
orderDetail.ProductID = dataReader.IsDBNull(1) ? 0 : dataReader.GetInt32(1);
orderDetail.UnitPrice = dataReader.IsDBNull(2) ? 0.0M : dataReader.GetDecimal(2);
orderDetail.Quantity = dataReader.IsDBNull(3) ? (short)0 : dataReader.GetInt16(3);
orderDetail.Discount = dataReader.IsDBNull(4) ? 0 : dataReader.GetFloat(4);

return orderDetail;
}

public static OrderDetailCollection GetOrderDetails()
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details]";
command.Connection = DataLayer.Connection;

OrderDetailCollection orderDetails = new OrderDetailCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
OrderDetail order = DataLayer.CreateOrderDetailData(dataReader);
orderDetails.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orderDetails;
}

public static OrderDetailCollection GetOrderDetails(int orderID, int productID)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] WHERE OrderID = ? AND ProductID = ? ";
command.Parameters.Add(DataLayer.CreateParameter("OrderID", orderID));
command.Parameters.Add(DataLayer.CreateParameter("ProductID", productID));
command.Connection = DataLayer.Connection;

OrderDetailCollection orderDetails = new OrderDetailCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
OrderDetail order = DataLayer.CreateOrderDetailData(dataReader);
orderDetails.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orderDetails;
}

public static OrderDetailCollection GetOrderDetails(int orderID)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] WHERE OrderID = ?";
command.Parameters.Add(DataLayer.CreateParameter("OrderID", orderID));
command.Connection = DataLayer.Connection;

OrderDetailCollection orderDetails = new OrderDetailCollection();
try
{
command.Connection.Open();

OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
OrderDetail order = DataLayer.CreateOrderDetailData(dataReader);
orderDetails.Add(order);
}
}
finally
{
command.Connection.Close();
}

return orderDetails;
}

public static int InsertOrderDetail(OrderDetail orderDetail)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?)";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("OrderID", orderDetail.OrderID));
command.Parameters.Add(DataLayer.CreateParameter("ProductID", orderDetail.ProductID));
command.Parameters.Add(DataLayer.CreateParameter("UnitPrice", orderDetail.UnitPrice));
command.Parameters.Add(DataLayer.CreateParameter("Quantity", orderDetail.Quantity));
command.Parameters.Add(DataLayer.CreateParameter("Discount", orderDetail.Discount));

return DataLayer.ExecuteNonQuery(command);
}

public static int UpdateOrderDetail(OrderDetail orderDetail)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "UPDATE [Order Details] SET ProductID = ?, UnitPrice = ?, Quantity = ?, Discount = ? WHERE OrderID = ?";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("ProductID", orderDetail.ProductID));
command.Parameters.Add(DataLayer.CreateParameter("UnitPrice", orderDetail.UnitPrice));
command.Parameters.Add(DataLayer.CreateParameter("Quantity", orderDetail.Quantity));
command.Parameters.Add(DataLayer.CreateParameter("Discount", orderDetail.Discount));
command.Parameters.Add(DataLayer.CreateParameter("OrderID", orderDetail.OrderID));

return DataLayer.ExecuteNonQuery(command);
}

public static int DeleteOrderDetail(OrderDetail orderDetail)
{
OleDbCommand command = new OleDbCommand();
command.CommandText = "DELETE FROM [Order Details] WHERE OrderID = ?";
command.Connection = DataLayer.Connection;
command.Parameters.Add(DataLayer.CreateParameter("OriginalOrderID", orderDetail.OrderID));

return DataLayer.ExecuteNonQuery(command);
}
#endregion

}