private static void UpdateRow(DataRow dr)
{
string setFields = string.Empty;
string condition = string.Empty;
DataTable dt = dr.Table;
PrimaryKeySchema pks = DataBaseSchemaProvider.GetTablePrimaryKey(DatabaseSchema.Tables[dt.TableName]);
if (pks == null || pks.MemberColumns.Count == 0)
{
throw new Exception(dt.TableName + "没有设置主键！");
}
for (int i = 0; i < dt.Columns.Count; i++)
{
bool isPKColumn = false;
foreach (MemberColumnSchema mcs in pks.MemberColumns)
{
//Equals(dt.Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase))
if (mcs.Name.Equals(dt.Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase))
{
isPKColumn = true;
break;
}
}
if (!isPKColumn)
{
setFields += (dt.Columns[i].ColumnName + "=@" + dt.Columns[i].ColumnName + ",");
}
}
setFields = setFields.Remove(setFields.Length - 1);
foreach (MemberColumnSchema mcs in pks.MemberColumns)
{
condition += string.Format(@" {0}='{1}' AND", mcs.Name, dr[mcs.Name].ToString());
}
condition = condition.Remove(condition.Length - 3);
string sql = string.Format(@"UPDATE {0} SET {1} WHERE {2}", dr.Table.TableName, setFields, condition);
DbCommand updateCommand = SysDataBase.CreateCommandByCommandType(CommandType.Text, sql);
ColumnSchema[] css = DataBaseSchemaProvider.GetTableColumns(DatabaseSchema.Tables[dt.TableName]);
foreach (DataColumn dc in dt.Columns)
{
foreach (ColumnSchema cs in css)
{
if (cs.Name.Equals(dc.ColumnName, StringComparison.OrdinalIgnoreCase))
{
SysDataBase.AddInParameter(updateCommand, dc.ColumnName, cs.DataType, dr[dc.ColumnName]);
break;
}
}
}
SysDataBase.ExecuteDataSet(updateCommand);
}
private static void InsertRow(DataRow dr)
{
string fields = string.Empty;
string values = string.Empty;
int fieldNo = 0;
DataTable dt = dr.Table;
for (int i = 0; i < dt.Columns.Count; i++)
{
fields += (dt.Columns[i].ColumnName + ",");
fieldNo++;
}
fields = fields.Remove(fields.Length - 1);
for (int i = 0; i < fieldNo; i++)
{
values += string.Format("@{0},", dt.Columns[i].ColumnName);
}
values = values.Remove(values.Length - 1);
string sql = string.Format(@"INSERT INTO {0} ({1}) values ({2})", dt.TableName, fields, values);
DbCommand insertCommand = SysDataBase.CreateCommandByCommandType(CommandType.Text, sql);
ColumnSchema[] css = DataBaseSchemaProvider.GetTableColumns(DatabaseSchema.Tables[dt.TableName]);
foreach (DataColumn dc in dt.Columns)
{
foreach (ColumnSchema cs in css)
{
if (cs.Name.ToLower() == dc.ColumnName.ToLower())
{
SysDataBase.AddInParameter(insertCommand, dc.ColumnName, cs.DataType, dr[dc.ColumnName]);
break;
}
}
}
SysDataBase.ExecuteDataSet(insertCommand);
}
private static void DeleteRow(DataRow dr)
{
string condition = string.Empty;
PrimaryKeySchema pks = DataBaseSchemaProvider.GetTablePrimaryKey(DatabaseSchema.Tables[dr.Table.TableName]);
if (pks == null || pks.MemberColumns.Count == 0)
{
throw new Exception(dr.Table.TableName + "没有设置主键！");
}
foreach (MemberColumnSchema mcs in pks.MemberColumns)
{
condition += string.Format(@" {0}='{1}' AND", mcs.Name, dr[mcs.Name].ToString());
}
condition = condition.Remove(condition.Length - 3);
string sql = string.Format(@"DELETE FROM {0} WHERE {1}", dr.Table.TableName, condition);
DbCommand deleteCommand = SysDataBase.CreateCommandByCommandType(CommandType.Text, sql);
SysDataBase.ExecuteDataSet(deleteCommand);
}
public static int ExecuteNonQuery(string sql)
{
return(SysDataBase.ExecuteNonQuery(CommandType.Text, sql));
}
public static DataTable ExecuteDataSet(string sql)
{
return(SysDataBase.ExecuteDataSet(CommandType.Text, sql).Tables[0]);
}