/// <summary>
/// 同步更新数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pKeyFlag"> ID=@ID </param>
/// <param name="pValueFlag"> value=@value,... </param>
/// <param name="dt">DataTable 数据源</param>
/// <param name="updateColumnsName">比对列名</param>
/// <returns></returns>
public OperationResult <bool> UpdateData(string tableName, string pKeyFlag, string pValueFlag, DataTable dt, Dictionary <string, string> updateColumnsName)
{
//wait
DbConnection con = null;
DbTransaction transcation = null;
try
{
using (con = context.CreateConnection())
{
con.Open();
transcation = con.BeginTransaction();
using (var command = con.CreateCommand())
{
command.Transaction = transcation;
string sql = " UPDATE {0} SET {1} ";
sql += " WHERE 1=1 ";
sql += " AND {2} ";
sql = string.Format(sql, tableName, pValueFlag, pKeyFlag);
command.Connection = con;
command.CommandText = sql;
for (int i = 0; i < dt.Rows.Count; i++)
{
command.Parameters.Clear();
foreach (var item in updateColumnsName)
{
DbParameter parms = command.CreateParameter();
parms.DbType = ChangeType.MappingDbType(dt.Columns[item.Value].DataType);
parms.Value = dt.Rows[i][item.Value];
parms.ParameterName = item.Value;
command.Parameters.Add(parms);
}
command.ExecuteNonQuery();
}
}
transcation.Commit();
}
return(new OperationResult <bool>(OperationResultType.Success, null, true));
}
catch (Exception ex)
{
if (transcation != null)
{
transcation.Rollback();
}
return(new OperationResult <bool>(OperationResultType.Error, ex.Message, false));
}
finally
{
con.Close();
}
}
public OperationResult <bool> InsertData(string tableName, string columnNames, string pColumnNames, DataTable dt, Dictionary <string, string> inserColumnsName)
{
DbConnection con = null;
DbTransaction transcation = null;
try
{
using (con = context.CreateConnection())
{
con.Open();
transcation = con.BeginTransaction();
using (var command = con.CreateCommand())
{
command.Transaction = transcation;
string sql = " INSERT INTO {0}({1}) ";
sql += "VALUES";
sql += " ({2}) ";
sql = string.Format(sql, tableName, columnNames, pColumnNames);
command.Connection = con;
command.CommandText = sql;
for (int i = 0; i < dt.Rows.Count; i++)
{
command.Parameters.Clear();
foreach (var item in inserColumnsName)
{
DbParameter parms = command.CreateParameter();
parms.DbType = ChangeType.MappingDbType(dt.Columns[item.Value].DataType);
parms.Value = dt.Rows[i][item.Value];
parms.ParameterName = item.Value;
command.Parameters.Add(parms);
}
command.ExecuteNonQuery();
}
}
transcation.Commit();
}
return(new OperationResult <bool>(OperationResultType.Success, null, true));
}
catch (Exception ex)
{
if (transcation != null)
{
transcation.Rollback();
}
return(new OperationResult <bool>(OperationResultType.Error, ex.Message, false));
}
finally
{
con.Close();
}
}