/// <summary>
/// 生成一个Insert语句
/// </summary>
/// <returns></returns>
public static MySql.Data.MySqlClient.MySqlCommand CreateInsert(object value, string table)
{
if (value == null || string.IsNullOrEmpty(table))
{
throw new ArgumentException();
}
string sql = "INSERT INTO {0}({1}) VALUES({2})";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
MySql.Data.MySqlClient.MySqlParameterCollection args = cmd.Parameters;
PropertyInfo[] props = (value.GetType()).GetProperties();
int len = props.Length - 1;
string fileds = null, values = null;
for (int i = 0; i <= len; i++)
{
PropertyInfo prop = props[i];
if (i >= len)
{
values += ("@" + prop.Name);
fileds += string.Format("[{0}]", prop.Name);
}
else
{
values += string.Format("@{0},", prop.Name);
fileds += string.Format("[{0}],", prop.Name);
}
object val = prop.GetValue(value, null);
if (val == null)
{
val = DBNull.Value;
}
args.Add(new MySql.Data.MySqlClient.MySqlParameter(string.Format("@{0}", prop.Name), val));
}
sql = string.Format(sql, table, fileds, values);
cmd.CommandText = sql;
return(cmd);
}
/// <summary>
/// 创建更新执行语句(动态)
/// </summary>
/// <param name="value">映射的对象</param>
/// <param name="table">表</param>
/// <param name="key">主键</param>
/// <param name="where">条件</param>
/// <returns></returns>
public static MySql.Data.MySqlClient.MySqlCommand CreateUpdate(object value, string table, string key, string where)
{
if (value == null || string.IsNullOrEmpty(table) || string.IsNullOrEmpty(key))
{
throw new ArgumentException();
}
string when = string.Empty, sql = string.Format("UPDATE {0} SET", table);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
MySql.Data.MySqlClient.MySqlParameterCollection args = cmd.Parameters;
PropertyInfo[] props = (value.GetType()).GetProperties();
int len = props.Length - 1;
for (int i = 0; i <= len; i++)
{
PropertyInfo prop = props[i];
if (prop.Name != key)
{
sql += string.Format(i >= len ? "[{0}]=@{1}" : " [{0}]=@{1},", prop.Name, prop.Name);
}
else
{
when += string.Format(" WHERE {0}=@{1}", key, key);
}
object val = prop.GetValue(value, null);
if (val == null)
{
val = DBNull.Value;
}
args.Add(new MySql.Data.MySqlClient.MySqlParameter(string.Format("@{0}", prop.Name), val));
}
if (!string.IsNullOrEmpty(where))
{
when += string.Format(" AND {0} ", where);
}
cmd.CommandText = (sql += when);
return(cmd);
}
