public static IDbCommand CreateUpdate(object value, string table, string key, string where, DatabaseAccessAdapter adapter)
{
if (value == null || string.IsNullOrEmpty(table) || string.IsNullOrEmpty(key))
{
throw new ArgumentException();
}
string when = string.Empty, sql = string.Format("UPDATE [{0}] SET", table);
var cmd = adapter.CreateCommand();
var 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 (!Regex.IsMatch(prop.Name, key, RegexOptions.IgnoreCase))
{
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(adapter.CreateParameter(string.Format("@{0}", prop.Name), val));
}
if (!string.IsNullOrEmpty(where))
{
when += string.Format(" AND {0} ", where);
}
cmd.CommandText = (sql += when);
return(cmd);
}
public static IDbCommand CreateInsert(object value, string table, DatabaseAccessAdapter adapter)
{
if (value == null || string.IsNullOrEmpty(table))
{
throw new ArgumentException();
}
string sql = "INSERT INTO [{0}]({1}) VALUES({2})";
IDbCommand cmd = adapter.CreateCommand();
IDataParameterCollection 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(adapter.CreateParameter(string.Format("@{0}", prop.Name), val));
}
sql = string.Format(sql, table, fileds, values);
cmd.CommandText = sql;
return(cmd);
}
