public static void InsertGameInfoSql(Models.Game.Game game)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var commandStringBuilderFirstPart = new StringBuilder("INSERT INTO GameInfo (");
var commandStringBuilderSecondPart = new StringBuilder(") VALUES (");
var properties = game.GetType().GetProperties();
// 抓取属性名称生成SQL语句
var cur = 1;
foreach (var property in properties)
{
commandStringBuilderFirstPart.Append(property.Name);
if (property.PropertyType.Name == "String")
{
commandStringBuilderSecondPart
.Append(ConvertStringToSql((string)game.GetPropertyValue(property.Name)));
}
else
{
commandStringBuilderSecondPart.Append((int)game.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>
/// 比对两个Game实例生成SQL语句
/// </summary>
/// <param name="origin">旧实例</param>
/// <param name="target">新实例</param>
public static void UpdateGameInfoSql(Models.Game.Game origin, Models.Game.Game 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 GameInfo SET ");
var cur = 1;
foreach (var targetProperty in updateList)
{
if (targetProperty.PropertyType.Name == "String")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
ConvertStringToSql((string)target
.GetPropertyValue(targetProperty.Name)));
}
else if (targetProperty.PropertyType.Name == "Genres")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)(Genres)target.GetPropertyValue(targetProperty.Name));
}
else if (targetProperty.PropertyType.Name == "Boolean")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
Convert.ToInt32((bool)target.GetPropertyValue(targetProperty.Name)));
}
else if (targetProperty.PropertyType.Name == "DateTime")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
ConvertStringToSql(target.GetPropertyValue(targetProperty.Name).ToString()));
}
else
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)target.GetPropertyValue(targetProperty.Name));
}
if (cur++ < updateList.Count)
{
commandStringBuilder.Append(", ");
}
}
commandStringBuilder.Append(" WHERE GameNumber = " + ConvertStringToSql(origin.GameNumber));
// 执行
try
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand(commandStringBuilder.ToString(), sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
// 留了一个Catch，记得处理
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine("无变化");
}
}
