public static int Delete <T>(this DbContext connInfo, RequestBase param, string tableName = "")
where T : new()
{
if (0 == (param?.Count ?? 0))
{
throw new ArgumentNullException("param");
}
string sql = connInfo.GetDeleteSql(typeof(T), tableName, (from p in param select p.Key));
return(connInfo.Db.Execute(sql, param, connInfo.Transaction, commandType: CommandType.Text));
}
public static int DeleteWithDataTable(this DbContext connInfo, DataTable dtData, bool ThrowExceptionIfNotAffected, Func <DbContext, DML, DataRow, bool> checkFunc)
{
if (null == checkFunc)
{
return(DeleteWithDataTable(connInfo, dtData, ThrowExceptionIfNotAffected));
}
if (null == dtData || 0 == dtData.Rows.Count)
{
return(0);
}
string TableName = dtData.TableName;
if (string.IsNullOrEmpty(TableName))
{
throw new Exception("No tablename settled on datatable!");
}
string[] PrimaryFields = dtData.GetPrimaryFields();
if (null == PrimaryFields || 0 == PrimaryFields.Length)
{
throw new Exception("No fields or no primary key defined!");
}
RequestBase pkPars = new RequestBase();
string deleteStr = connInfo.GetDeleteSql(null, tableName: TableName, matchFields: PrimaryFields);
int ret = 0;
foreach (DataRow dr in dtData.Select("", "", DataViewRowState.CurrentRows))
{
bool opOK = false;
if (checkFunc(connInfo, DML.DELETE, dr))
{
opOK = DeleteDataRow2(connInfo, dr, PrimaryFields, pkPars, deleteStr);
}
if (!opOK && ThrowExceptionIfNotAffected)
{
throw new Exception("No rows affected!");
}
if (opOK)
{
++ret;
}
}
return(ret);
}
public static List <object> UpdateDataTableToDB(this DbContext connInfo, DataTable dtData, bool NoOpWhenExists, bool ThrowExceptionIfNotAffected, string OpExptRetrunsField, Func <DbContext, DML, DataRow, bool> checkFunc)
{
if (null == checkFunc)
{
return(UpdateDataTableToDB(connInfo, dtData,
NoOpWhenExists, ThrowExceptionIfNotAffected, OpExptRetrunsField));
}
if (null == dtData || 0 == dtData.Rows.Count)
{
return(null);
}
string TableName = dtData.TableName;
if (string.IsNullOrEmpty(TableName))
{
throw new Exception("No tablename settled on datatable!");
}
string[] Fields = dtData.GetFields(OpExptRetrunsField)?.ToArray();
string[] PrimaryFields = dtData.GetPrimaryFields();
string[] updateFields = PutArr2ToLast(Fields, PrimaryFields);
if (null == Fields || 0 == Fields.Length ||
null == PrimaryFields || 0 == PrimaryFields.Length)
{
throw new Exception("No fields or no primary keys defined!");
}
RequestBase pars = new RequestBase();
RequestBase pkPars = new RequestBase();
RequestBase parsInsert = new RequestBase();
string sqlUpdate = connInfo.DbType.GetUpdateSqlStr(TableName, Fields, PrimaryFields);
string fieldStr = SqlBuilder.GetFieldStringViaColumns(Fields);
string parStr = connInfo.DbType.GetParaStringViaColumns(Fields);
string deleteStr = connInfo.GetDeleteSql(null, tableName: TableName, matchFields: PrimaryFields);
List <object> ret = new List <object>();
bool singleKey = (1 == PrimaryFields.Length); //单主键
/*
* foreach (DataRow dr in dtData.Select("", "", DataViewRowState.Deleted))
* {
* bool opOK = false;
* if (checkFunc(connInfo, DML.DELETE, dr))
* {
* opOK = DeleteDataRow(connInfo, dr, PrimaryFields, pkPars, deleteStr, lstDataType);
* }
* if (!opOK && ThrowExceptionIfNotAffected)
* {
* throw new Exception("No rows affected!");
* }
* if (opOK) ret.Add(dr[OpExptRetrunsField, DataRowVersion.Original]);
* }
*/
foreach (DataRow dr in dtData.Select("", "", DataViewRowState.CurrentRows))
{
bool opOK = false, Modified = false;
if (ExistsDataRow(connInfo, TableName, dr, PrimaryFields, pkPars))
{
if (!NoOpWhenExists)
{
if (checkFunc(connInfo, DML.UPDATE, dr))
{
opOK = UpdateDataRow(connInfo, TableName, dr, pars,
updateFields, PrimaryFields, sqlUpdate);
}
if (opOK)
{
Modified = true;
}
}
else
{
opOK = true;
Modified = false;
}
}
else
{
if (checkFunc(connInfo, DML.INSERT, dr))
{
opOK = AddDataRow(connInfo, TableName, dr, parsInsert, Fields, fieldStr, parStr);
}
if (opOK)
{
Modified = true;
}
}
if (!opOK && ThrowExceptionIfNotAffected)
{
throw new Exception("No rows affected!");
}
if (opOK && Modified)
{
ret.Add(dr[OpExptRetrunsField, DataRowVersion.Original]);
}
}
return(ret);
}
/// <summary>
/// 更新记录集到数据库
/// </summary>
/// <param name="Conn"></param>
/// <param name="dtData"></param>
/// <param name="NoOpWhenExists"></param>
/// <param name="ThrowExceptionIfNotAffected"></param>
/// <returns></returns>
public static int UpdateDataTableToDB(this DbContext connInfo, DataTable dtData, bool NoOpWhenExists, bool ThrowExceptionIfNotAffected)
{
if (null == dtData || 0 == dtData.Rows.Count)
{
return(0);
}
string TableName = dtData.TableName;
if (string.IsNullOrEmpty(TableName))
{
throw new Exception("No tablename settled on datatable!");
}
var Fields = SqlBuilder.GetFields(dtData)?.ToArray();
string[] PrimaryFields = dtData.GetPrimaryFields();
string[] updateFields = PutArr2ToLast(Fields, PrimaryFields);
if (null == Fields || 0 == Fields.Length ||
null == PrimaryFields || 0 == PrimaryFields.Length)
{
throw new Exception("No fields or no primary key defined!");
}
RequestBase pars = new RequestBase();
RequestBase parsInsert = new RequestBase();
RequestBase pkPars = new RequestBase();
string sqlUpdate = connInfo.DbType.GetUpdateSqlStr(TableName, Fields, PrimaryFields);
string fieldStr = SqlBuilder.GetFieldStringViaColumns(Fields);
string parStr = connInfo.DbType.GetParaStringViaColumns(Fields);
string deleteStr = connInfo.GetDeleteSql(null, tableName: TableName, matchFields: PrimaryFields);
int ret = 0;
bool singleKey = (1 == PrimaryFields.Length); //单主键
foreach (DataRow dr in dtData.Select("", "", DataViewRowState.CurrentRows))
{
bool opOK = false, Modified = false;
if (ExistsDataRow(connInfo, TableName, dr, PrimaryFields, pkPars))
{
if (!NoOpWhenExists)
{
opOK = UpdateDataRow(connInfo, TableName, dr, pars, updateFields, PrimaryFields, sqlUpdate);
if (opOK)
{
Modified = true;
}
}
else
{
opOK = true;
Modified = false;
}
}
else
{
opOK = AddDataRow(connInfo, TableName, dr, parsInsert, Fields, fieldStr, parStr);
if (opOK)
{
Modified = true;
}
}
if (!opOK && ThrowExceptionIfNotAffected)
{
throw new Exception("No rows affected!");
}
if (opOK && Modified)
{
++ret;
}
}
return(ret);
}
public static int DeletePrimitive <T>(this DbContext connInfo, T record, IEnumerable <string> matchFields = null, string tableName = "") where T : new()
{
return(connInfo.Db.Execute(connInfo.GetDeleteSql(typeof(T), tableName, matchFields), record, connInfo.Transaction, commandType: CommandType.Text));
}
public static int Delete <T>(this DbContext connInfo, IEnumerable <T> records, string tableName = "") where T : new()
{
return(connInfo.Db.Execute(connInfo.GetDeleteSql(typeof(T), tableName), records, connInfo.Transaction, commandType: CommandType.Text));
}