public static void InsertStaffInfoSql(Models.Staff.Staff staff)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var commandStringBuilderFirstPart = new StringBuilder("INSERT INTO StaffInfo (");
var commandStringBuilderSecondPart = new StringBuilder(") VALUES (");
var properties = staff.GetType().GetProperties();
// 抓取属性名称生成SQL语句
var cur = 1;
foreach (var property in properties)
{
commandStringBuilderFirstPart.Append(property.Name);
if (property.PropertyType.Name == "String")
{
commandStringBuilderSecondPart
.Append(ConvertStringToSql((string)staff.GetPropertyValue(property.Name)));
}
else
{
commandStringBuilderSecondPart.Append((int)staff.GetPropertyValue(property.Name));
}
if (cur++ < properties.Length)
{
commandStringBuilderFirstPart.Append(", ");
commandStringBuilderSecondPart.Append(", ");
}
}
var command = commandStringBuilderFirstPart + commandStringBuilderSecondPart.ToString() +
")";
var sqlCommand = new SqlCommand(command, sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 比对两个Staff实例，将更新的部分提交SQL数据库进行更新
/// </summary>
/// <param name="origin">旧Staff实例</param>
/// <param name="target">新Staff实例</param>
public static void UpdateStaffInfoSql(Models.Staff.Staff origin, Models.Staff.Staff target)
{
// 属性比对，生成的SQL语句只包含要修改的部分
var properties = origin.GetType().GetProperties();
var updateList = new List <PropertyInfo>();
foreach (var property in properties)
{
if (!Equals(origin.GetPropertyValue(property.Name), target.GetPropertyValue(property.Name)))
{
updateList.Add(property);
}
}
// 生成SQL语句
var commandStringBuilder = new StringBuilder("UPDATE StaffInfo SET ");
var cur = 1;
foreach (var targetProperty in updateList)
{
if (targetProperty.PropertyType.Name == "String")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
ConvertStringToSql((string)target
.GetPropertyValue(targetProperty.Name)));
}
// 用了Flags特性的枚举不能直接转int，不知道为什么
else if (targetProperty.PropertyType.Name == "Temperament")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)(Temperament)target.GetPropertyValue(targetProperty.Name));
}
else
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)target.GetPropertyValue(targetProperty.Name));
}
if (cur++ < updateList.Count)
{
commandStringBuilder.Append(", ");
}
}
commandStringBuilder.Append(" WHERE StaffNumber = " + ConvertStringToSql(origin.StaffNumber));
// 执行
try
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand(commandStringBuilder.ToString(), sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.WriteLine(e);
Debug.WriteLine("无变化！");
}
}
