private void SaveEvectionDet()
{
var sqlBuilder = new SQLBuilder(this.WorkFlowDbProvider);
sqlBuilder.BeginDelete("TestEvectionDetail");
sqlBuilder.SetWhere("WORKFLOWINSID", WorkFlowInsId);//首先删除已有的数据
sqlBuilder.EndDelete();
foreach (DataRow dr in evectionDetTable.Rows)
{
sqlBuilder.BeginInsert("TestEvectionDetail");
sqlBuilder.SetValue("Id", BusinessLogic.NewGuid());
sqlBuilder.SetValue("WorkFlowId", WorkFlowId);
sqlBuilder.SetValue("WorkTaskId", WorkTaskId);
sqlBuilder.SetValue("WorkFlowInsId", WorkFlowInsId);
sqlBuilder.SetValue("WorkTaskInsId", WorkTaskInsId);
sqlBuilder.SetValue("BillCode", dr["BillCode"].ToString());
sqlBuilder.SetValue("StartAddress", dr["StartAddress"].ToString());
sqlBuilder.SetValue("EndAddress", dr["EndAddress"].ToString());
sqlBuilder.SetValue("Vehicle", dr["Vehicle"].ToString());
sqlBuilder.SetValue("VehicleCost", BusinessLogic.ConvertToNullableDecimal(dr["VehicleCost"]));
sqlBuilder.SetValue("CityCost", BusinessLogic.ConvertToNullableDecimal(dr["CityCost"]));
sqlBuilder.SetValue("LiveDays", BusinessLogic.ConvertToNullableDecimal(dr["LiveDays"]));
sqlBuilder.SetValue("LivePrice", BusinessLogic.ConvertToNullableDecimal(dr["LivePrice"]));
sqlBuilder.SetValue("EvectionDays", BusinessLogic.ConvertToNullableDecimal(dr["EvectionDays"]));
sqlBuilder.SetValue("Allowance", BusinessLogic.ConvertToNullableDecimal(dr["Allowance"]));
sqlBuilder.SetValue("Others", BusinessLogic.ConvertToNullableDecimal(dr["Others"]));
sqlBuilder.EndInsert();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (lvAttachment.SelectedItems.Count <= 0)
{
MessageBoxHelper.ShowWarningMsg(RDIFrameworkMessage.MSGC023);
return;
}
if (MessageBoxHelper.Show(RDIFrameworkMessage.Format(RDIFrameworkMessage.MSG0016, lvAttachment.SelectedItems[0].SubItems[0].Text)) == DialogResult.Yes)
{
Cursor holdCursor = BeginWait();
var sqlBuilder = new SQLBuilder(this.WorkFlowDbProvider);
sqlBuilder.BeginDelete("ATTACHMENT");
sqlBuilder.SetWhere("ID", lvAttachment.SelectedItems[0].SubItems[1].Text);
sqlBuilder.EndDelete();
this.ShowEntity();
EndWait(holdCursor);
}
}
public override void SaveFormData(bool isDraft)
{
base.SaveFormData(isDraft);
var sqlBuilder = new SQLBuilder(this.WorkFlowDbProvider);
string sql = "DELETE TESTQINGJIA WHERE WORKFLOWINSID=@WORKFLOWINSID";//先删除原有数据
sqlBuilder.BeginDelete("testQingjia");
sqlBuilder.SetWhere("WORKFLOWINSID", WorkFlowInsId);
sqlBuilder.EndDelete();
sqlBuilder.BeginInsert("testQingjia");
sqlBuilder.SetValue("WorkFlowId", WorkFlowId);
sqlBuilder.SetValue("WorkTaskId", WorkTaskId);
sqlBuilder.SetValue("WorkFlowInsId", WorkFlowInsId);
sqlBuilder.SetValue("WorkTaskInsId", WorkTaskInsId);
sqlBuilder.SetValue("ID", BusinessLogic.NewGuid());
sqlBuilder.SetValue("userId", txtUserId.Text);
sqlBuilder.SetValue("userName", txtUserName.Text);
sqlBuilder.SetValue("dutyCaption", txtDuty.Text);
sqlBuilder.SetValue("archCaption", txtDepartment.Text);
if (this.WorkFlowDbProvider.CurrentDbType == CurrentDbType.Oracle)
{
sqlBuilder.SetValue("BeginTime", !string.IsNullOrEmpty(dtBeginTime.Text) ? BusinessLogic.GetOracleDateFormat(DateTimeHelper.ToDate(dtBeginTime.Text)) : BusinessLogic.ConvertToDateToString(dtBeginTime.Text));
sqlBuilder.SetValue("EndTime", !string.IsNullOrEmpty(dtEndTime.Text) ? BusinessLogic.GetOracleDateFormat(DateTimeHelper.ToDate(dtEndTime.Text)) : BusinessLogic.ConvertToDateToString(dtEndTime.Text));
}
else
{
sqlBuilder.SetValue("BeginTime", BusinessLogic.ConvertToDateToString(dtBeginTime.Text));
sqlBuilder.SetValue("EndTime", BusinessLogic.ConvertToDateToString(dtEndTime.Text));
}
sqlBuilder.SetValue("Days", txtDays.Text);
sqlBuilder.SetValue("QingJiaType", BusinessLogic.ConvertToString(cboQingJiaType.SelectedItem));
sqlBuilder.SetValue("QingJia", txtQingJia.Text);
sqlBuilder.EndInsert();
}
/// <summary>
/// 远程数据同步的工具类
/// </summary>
/// <param name="fromDataBase">从服务器的哪个数据库获取数据</param>
/// <param name="tableName">同步哪个表</param>
/// <param name="primaryKeys">表的主键是什么</param>
/// <param name="modifiedOn">同步的更新时间</param>
/// <param name="toDataBaseDbType">同步到本地什么类型的数据库里？</param>
/// <param name="dbConnection">同步的目标数据库连接方式？</param>
/// <param name="toTableName">本地表</param>
/// <param name="topLimit"></param>
/// <param name="delete"></param>
/// <returns>影响行数</returns>
public static int SynchronousTable(string fromDataBase, string tableName, string[] primaryKeys, DateTime?modifiedOn, CurrentDbType toDataBaseDbType, string dbConnection, string toTableName, int topLimit, bool delete = true)
{
int result = 0;
// 输入参数检查
if (primaryKeys == null)
{
return(result);
}
if (topLimit == 0)
{
topLimit = 200;
}
int synchronousCount = topLimit;
DataTable dataTable = null;
while (synchronousCount >= topLimit)
{
// 一批一批写入数据库，提高同步的性能
var dbHelper = DbHelperFactory.GetHelper(toDataBaseDbType, dbConnection);
SQLBuilder sqlBuilder = new SQLBuilder(dbHelper);
dbHelper.BeginTransaction();
// string url = BaseSystemInfo.WebHost + "WebAPIV42/API/Synchronous/GetTopLimitTable";
const string url = "http://userCenter.zt-express.com/WebAPIV42/API/Synchronous/GetTopLimitTable";
WebClient webClient = new WebClient();
NameValueCollection postValues = new NameValueCollection
{
{ "userInfo", BaseSystemInfo.UserInfo.Serialize() },
{ "systemCode", BaseSystemInfo.SystemCode },
{ "securityKey", BaseSystemInfo.SecurityKey },
{ "dataBase", fromDataBase },
{ "tableName", tableName },
{ "toTableName", toTableName },
{ "topLimit", topLimit.ToString() },
{ "modifiedOn", modifiedOn.Value.ToString(BaseSystemInfo.DateTimeFormat) }
};
// 向服务器发送POST数据
byte[] responseArray = webClient.UploadValues(url, postValues);
string response = Encoding.UTF8.GetString(responseArray);
if (!string.IsNullOrEmpty(response))
{
dataTable = (DataTable)JsonConvert.DeserializeObject(response, typeof(DataTable));
}
int r;
// 出错的日志都需要能保存起来，这样有问题的可以找出原因来。
for (r = 0; r < dataTable.Rows.Count; r++)
{
// 先删除数据，修改的、新增的、都删除后添加来处理，问题就简单化了
// dbHelper.ExecuteNonQuery("DELETE FROM " + tableName + " WHERE " + primaryKey + " = '" + dataTable.Rows[r][primaryKey].ToString() + "'");
if (delete)
{
sqlBuilder.BeginDelete(toTableName);
for (int i = 0; i < primaryKeys.Length; i++)
{
string primaryKey = primaryKeys[i];
if (!string.IsNullOrWhiteSpace(primaryKey))
{
sqlBuilder.SetWhere(primaryKey, dataTable.Rows[r][primaryKey].ToString());
}
}
sqlBuilder.EndDelete();
}
// 然后插入数据
sqlBuilder.BeginInsert(toTableName);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
// 这里能判断目标表里是否有这个字段存在就更完美了。
sqlBuilder.SetValue(dataTable.Columns[i].ColumnName, dataTable.Rows[r][dataTable.Columns[i].ColumnName]);
}
sqlBuilder.EndInsert();
if (DateTime.Parse(dataTable.Rows[r][BaseBusinessLogic.FieldModifiedOn].ToString()) > modifiedOn.Value)
{
modifiedOn = DateTime.Parse(dataTable.Rows[r][BaseBusinessLogic.FieldModifiedOn].ToString());
}
result++;
}
synchronousCount = dataTable.Rows.Count;
// 批量提交
try
{
dbHelper.CommitTransaction();
}
catch (Exception ex)
{
LogUtil.WriteException(ex);
dbHelper.RollbackTransaction();
}
finally
{
dbHelper.Close();
dbHelper.Dispose();
}
}
return(result);
}