/// <summary>
///
/// </summary>
public void OpenConnection()
{
if (SqlConn.State != ConnectionState.Open)
{
SqlConn.Open();
}
}
public void AddProducts(List <Product> products)
{
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = InsertProductsSp;
DataTable productsDataTable = products.ToDataTable(p => new
{
p.Name,
p.SupplierId,
p.CategoryId,
MeasureUnit = ColumnSettings.Build(() => p.MeasureUnit).Name("Quantity Per Unit"),
UnitPrice = p.Price * 1.5m,
p.UnitsInStock,
p.UnitsOnOrder,
p.ReorderLevel,
p.Discontinued
});
sqlCommand.Parameters.AddWithValue("@Products", productsDataTable);
sqlCommand.ExecuteNonQuery();
}
}
finally
{
SqlConn.Close();
}
}
public List <Product> GetTopExpensiveProducts(int count)
{
List <Product> products = new List <Product>();
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandText = GetTopExpensiveProductsSql;
sqlCommand.Parameters.AddWithValue("@Count", count);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
var product = new Product()
{
Id = dataReader.GetInt32(0),
Price = dataReader.GetDecimal(1)
};
products.Add(product);
}
}
}
finally
{
SqlConn.Close();
}
return(products);
}
/// <summary>
///
/// </summary>
public void BeginTransac()
{
if (SqlConn.State != ConnectionState.Open)
{
SqlConn.Open();
}
SqlTransac = SqlConn.BeginTransaction();
}
/// <summary>
/// 用于执行增加和删除语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameter">参数化查询</param>
/// <returns>有多少语句执行成功</returns>
public int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
{
using (SqlCommand cmd = SqlConn.CreateCommand())
{
SqlConn.Open();
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
return(cmd.ExecuteNonQuery());
}
}
/// <summary>
/// 获取SQL数据
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public System.Data.DataTable GetSQLDataTable(string SQL)
{
DataTable dt = new DataTable();
using (SqlConn)
{
SqlCommand cmd = new SqlCommand(SQL, SqlConn);
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandTimeout = 60;
da.SelectCommand = cmd;
if (SqlConn.State != System.Data.ConnectionState.Open)
{
SqlConn.Open();
}
da.Fill(dt);
}
return(dt);
}
public List <Order> GetOrdersWithSpecificProducts(List <Product> products)
{
List <Order> orders = new List <Order>();
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = GetOrdersWithSpecificProductsSp;
sqlCommand.Parameters.AddWithValue("@Ids", products.ToDataTable(p => p.Id));
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
var order = new Order()
{
Id = dataReader.GetInt32(0),
PlacedDate = dataReader.GetDateTime(1)
};
orders.Add(order);
}
}
}
finally
{
SqlConn.Close();
}
return(orders);
}
//Login function
//There's only one user with xxx name and xxx password so the given result of the record can be 0 or 1
//However we need the user rank so we cannot just give back a true or false
public int Login(string Name, string Password)
{
const int Error = -1;
const int NoUserFound = 0;
const int UserIsAdmin = 1;
const int UserIsReceptionist = 2;
const int UserIsMember = 3;
//Database gives back strings, so we first need to store in in the StrUserId variable then convert it to int.
//Before converting we need to check if the value is larger than 0
string StrUserId;
try
{
SqlConn.Open();
string Sql = "SELECT Count(user_id), user_id, user_rank_id FROM user WHERE user_name = '" + Name + "' AND user_password= '******' ";
Dt = new DataTable();
Adapter = new MySqlDataAdapter(Sql, SqlConn);
Adapter.Fill(Dt);
//We need to check if the dataTable actually has rows
if (Dt.Rows.Count > 0)
{
if (Convert.ToInt32(Dt.Rows[0][0]) != 0)
{
StrUserId = Dt.Rows[0][1].ToString();
UserId = Convert.ToInt32(StrUserId);
}
if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "1")
{
return(UserIsAdmin);
}
else if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "2")
{
return(UserIsReceptionist);
}
else if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "3")
{
return(UserIsMember);
}
else
{
return(NoUserFound);
}
}
else
{
return(Error);
}
}
catch (MySqlException e)
{
e.GetType();
return(Error);
}
finally
{
SqlConn.Close();
}
}
/// <summary>
/// 向目标表中添加数据
/// </summary>
/// <param name="dt">目标数据</param>
/// <param name="pairs">目标表字段信息</param>
/// <param name="isRollack">错误时是否回滚，true表示回滚，否则跳过错误行，并提供错误报告</param>
/// <returns></returns>
public ResultModel AddData(DataTable dt, Dictionary <string, Type> pairs, bool isRollack)
{
var columns = Common.GetColumnNamesFromDt(dt);
if (columns.Count <= 0)
{
return(new ResultModel {
IsHaveReport = false, ErrMsg = "没有数据导入!"
});
}
dt.Columns.Add(new DataColumn("ErrMsg"));
//复制一个新的表格
DataTable newDt = new DataTable();
foreach (var item in columns)
{
newDt.Columns.Add(new DataColumn(item));
}
newDt.Columns.Add(new DataColumn("ErrMsg"));
int num = 0;
int errNum = 0;
string fileName = "";
using (SqlConn)
{
using (SqlCommand cmd = SqlConn.CreateCommand())
{
try
{
SqlConn.Open();
//开启一个事务
SqlTransaction myTrans = null;
if (isRollack)
{
myTrans = SqlConn.BeginTransaction();
cmd.Transaction = myTrans;
}
foreach (DataRow dr in dt.Rows)
{
//构造语句
string sqlStr = Common.GetSqlStrByDataRow(dr, columns, pairs, TableName);
cmd.CommandText = sqlStr;
try
{
//执行语句
num += cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (isRollack)
{
if (myTrans != null)
{
myTrans.Rollback();
}
throw ex;
}
else
{
errNum++;
//不回滚，产生上传报告
dr["ErrMsg"] = ex.Message;
newDt.Rows.Add(dr.ItemArray);
}
}
}
//如果选择跳过，并且期间产生异常，产生异常报告
if (!isRollack && newDt.Rows.Count > 0)
{
fileName = ExcelHelper.DataTable2File(newDt); //仅出错的在报告中体现
//fileName = ExcelHelper.DataTable2File(dt); //所有数据都在报告中
}
if (isRollack)
{
if (myTrans != null)
{
myTrans.Commit();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
if (errNum > 0)
{
return(new ResultModel {
IsHaveReport = true, ErrMsg = $"导入成功！成功导入{num}条,失败{errNum}条！详见错误报告：{fileName},立即查看报告?", FileName = fileName
});
}
else
{
return(new ResultModel {
IsHaveReport = false, ErrMsg = $"导入成功！共计 {num} 条数据被导入！"
});
}
}
