复制代码 代码如下:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace MyStudy.DAL
{
/// <summary>
/// Excel文件处理类
/// </summary>
public class ExcelHelper
{
private static string fileName = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @\"/ExcelFile/ExcelDB.xlsx\";

private static OleDbConnection connection;
public static OleDbConnection Connection
{
get
{
string connectionString = \"\";
string fileType = System.IO.Path.GetExtension(fileName);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == \".xls\")
{
connectionString = \"Provider=Microsoft.Jet.OLEDB.4.0;\" + \"Data Source=\" + fileName + \";\" + \";Extended Properties=\\\"Excel 8.0;HDR=YES;IMEX=2\\\"\";
}
else
{
connectionString = \"Provider=Microsoft.ACE.OLEDB.12.0;\" + \"Data Source=\" + fileName + \";\" + \";Extended Properties=\\\"Excel 12.0;HDR=YES;IMEX=2\\\"\";
}
if (connection == null)
{
connection = new OleDbConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}

/// <summary>
/// 执行无参数的SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}

/// <summary>
/// 执行有参数的SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <param name=\"values\">参数集合</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}

/// <summary>
/// 返回单个值无参数的SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}

/// <summary>
/// 返回单个值有参数的SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <param name=\"parameters\">参数集合</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql, params OleDbParameter[] parameters)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(parameters);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}

/// <summary>
/// 执行查询无参数SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
DataSet ds = new DataSet();
da.Fill(ds, \"UserInfo\");
connection.Close();
return ds;
}

/// <summary>
/// 执行查询有参数SQL语句
/// </summary>
/// <param name=\"sql\">SQL语句</param>
/// <param name=\"parameters\">参数集合</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql, params OleDbParameter[] parameters)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
da.SelectCommand.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds;
}
}
}

复制代码 代码如下:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MyStudy.Model
{
/// <summary>
/// 用户信息实体类
/// </summary>
public class UserInfo
{
public int UserId { get; set; }
public string UserName { get; set; }
public int? Age { get; set; }
public string Address { get; set; }
public DateTime? CreateTime { get; set; }

/// <summary>
/// 将DataTable转换成List数据
/// </summary>
public static List<UserInfo> ToList(DataSet dataSet)
{
List<UserInfo> userList = new List<UserInfo>();
if (dataSet != null && dataSet.Tables.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
UserInfo user = new UserInfo();
if (dataSet.Tables[0].Columns.Contains(\"UserId\") && !Convert.IsDBNull(row[\"UserId\"]))
user.UserId = Convert.ToInt32(row[\"UserId\"]);

if (dataSet.Tables[0].Columns.Contains(\"UserName\") && !Convert.IsDBNull(row[\"UserName\"]))
user.UserName = (string)row[\"UserName\"];

if (dataSet.Tables[0].Columns.Contains(\"Age\") && !Convert.IsDBNull(row[\"Age\"]))
user.Age = Convert.ToInt32(row[\"Age\"]);

if (dataSet.Tables[0].Columns.Contains(\"Address\") && !Convert.IsDBNull(row[\"Address\"]))
user.Address = (string)row[\"Address\"];

if (dataSet.Tables[0].Columns.Contains(\"CreateTime\") && !Convert.IsDBNull(row[\"CreateTime\"]))
user.CreateTime = Convert.ToDateTime(row[\"CreateTime\"]);

userList.Add(user);
}
}
return userList;
}
}
}

复制代码 代码如下:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MyStudy.Model
{
/// <summary>
/// 订单实体类
/// </summary>
public class Order
{
public string OrderNo { get; set; }
public string ProductName { get; set; }
public int? Quantity { get; set; }
public decimal? Money { get; set; }
public DateTime? SaleDate { get; set; }

/// <summary>
/// 将DataTable转换成List数据
/// </summary>
public static List<Order> ToList(DataSet dataSet)
{
List<Order> orderList = new List<Order>();
if (dataSet != null && dataSet.Tables.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
Order order = new Order();
if (dataSet.Tables[0].Columns.Contains(\"OrderNo\") && !Convert.IsDBNull(row[\"OrderNo\"]))
order.OrderNo = (string)row[\"OrderNo\"];

if (dataSet.Tables[0].Columns.Contains(\"ProductName\") && !Convert.IsDBNull(row[\"ProductName\"]))
order.ProductName = (string)row[\"ProductName\"];

if (dataSet.Tables[0].Columns.Contains(\"Quantity\") && !Convert.IsDBNull(row[\"Quantity\"]))
order.Quantity = Convert.ToInt32(row[\"Quantity\"]);

if (dataSet.Tables[0].Columns.Contains(\"Money\") && !Convert.IsDBNull(row[\"Money\"]))
order.Money = Convert.ToDecimal(row[\"Money\"]);

if (dataSet.Tables[0].Columns.Contains(\"SaleDate\") && !Convert.IsDBNull(row[\"SaleDate\"]))
order.SaleDate = Convert.ToDateTime(row[\"SaleDate\"]);

orderList.Add(order);
}
}
return orderList;
}
}
}

复制代码 代码如下:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MyStudy.Model;
using MyStudy.DAL;
using System.Data.OleDb;

namespace MyStudy.BLL
{
/// <summary>
/// 用户信息业务类
/// </summary>
public class UserInfoBLL
{
/// <summary>
/// 查询用户列表
/// </summary>
public List<UserInfo> GetUserList()
{
List<UserInfo> userList = new List<UserInfo>();
string sql = \"SELECT * FROM [UserInfo$]\";
DataSet dateSet = ExcelHelper.GetReader(sql);
userList = UserInfo.ToList(dateSet);
return userList;
}

/// <summary>
/// 获取用户总数
/// </summary>
public int GetUserCount()
{
int result = 0;
string sql = \"SELECT COUNT(*) FROM [UserInfo$]\";
result = ExcelHelper.GetScalar(sql);
return result;
}

/// <summary>
/// 新增用户信息
/// </summary>
public int AddUserInfo(UserInfo param)
{
int result = 0;
string sql = \"INSERT INTO [UserInfo$](UserId,UserName,Age,Address,CreateTime) VALUES(@UserId,@UserName,@Age,@Address,@CreateTime)\";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter(\"@UserId\", param.UserId),
new OleDbParameter(\"@UserName\", param.UserName),
new OleDbParameter(\"@Age\", param.Age),
new OleDbParameter(\"@Address\",param.Address),
new OleDbParameter(\"@CreateTime\",param.CreateTime)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}

/// <summary>
/// 修改用户信息
/// </summary>
public int UpdateUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string sql = \"UPDATE [UserInfo$] SET UserName=@UserName,Age=@Age,Address=@Address WHERE UserId=@UserId\";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter(\"@UserId\",param.UserId),
new OleDbParameter(\"@UserName\", param.UserName),
new OleDbParameter(\"@Age\", param.Age),
new OleDbParameter(\"@Address\",param.Address)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}

/// <summary>
/// 删除用户信息
/// </summary>
public int DeleteUserInfo(UserInfo param)
{
int result = 0;
if (param.UserId > 0)
{
string sql = \"DELETE [UserInfo$] WHERE UserId=@UserId\";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter(\"@UserId\",param.UserId),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}
}

复制代码 代码如下:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MyStudy.Model;
using MyStudy.DAL;
using System.Data.OleDb;

namespace MyStudy.BLL
{
/// <summary>
/// 订单业务类
/// </summary>
public class OrderBLL
{
/// <summary>
/// 查询订单列表
/// </summary>
public List<Order> GetOrderList()
{
List<Order> orderList = new List<Order>();
string sql = \"SELECT * FROM [Order$]\";
DataSet dateSet = ExcelHelper.GetReader(sql);
orderList = Order.ToList(dateSet);
return orderList;
}

/// <summary>
/// 获取订单总数
/// </summary>
public int GetOrderCount()
{
int result = 0;
string sql = \"SELECT COUNT(*) FROM [Order$]\";
result = ExcelHelper.GetScalar(sql);
return result;
}

/// <summary>
/// 新增订单
/// </summary>
public int AddOrder(Order param)
{
int result = 0;
string sql = \"INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)\";
OleDbParameter[] oleDbParam = new OleDbParameter[]
{
new OleDbParameter(\"@OrderNo\", param.OrderNo),
new OleDbParameter(\"@ProductName\", param.ProductName),
new OleDbParameter(\"@Quantity\", param.Quantity),
new OleDbParameter(\"@Money\",param.Money),
new OleDbParameter(\"@SaleDate\",param.SaleDate)
};
result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
return result;
}

/// <summary>
/// 修改订单
/// </summary>
public int UpdateOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = \"UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo\";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter(\"@OrderNo\",param.OrderNo),
new OleDbParameter(\"@ProductName\",param.ProductName),
new OleDbParameter(\"@Quantity\", param.Quantity),
new OleDbParameter(\"@Money\", param.Money)
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}

/// <summary>
/// 删除订单
/// </summary>
public int DeleteOrder(Order param)
{
int result = 0;
if (!String.IsNullOrEmpty(param.OrderNo))
{
string sql = \"DELETE [Order$] WHERE OrderNo=@OrderNo\";
OleDbParameter[] sqlParam = new OleDbParameter[]
{
new OleDbParameter(\"@OrderNo\",param.OrderNo),
};
result = ExcelHelper.ExecuteCommand(sql, sqlParam);
}
return result;
}
}
}