C#操作Excel数据增删改查示例(2)

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

3、创建业务逻辑类

3.1 创建UserInfoBLL.cs类，用户信息业务类。

复制代码 代码如下:

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

3.2 创建OrderBLL.cs类，订单业务类

复制代码 代码如下: