private void InsertGroups(DataTable dataTable)
{
try
{
var fieldNames = $"{DestinationDbField},";
var columnDictionary = new Dictionary <string, string>();
// make "field1,field2,..."
var i = 0;
foreach (var item in DataItems)
{
columnDictionary.Add(((DBFieldBindingItem)item).DBFieldName,
((DBFieldBindingItem)item).DBFieldType.ToLower());
fieldNames += ((DBFieldBindingItem)item).DBFieldName;
if (i == DataItems.Count() - 1)
{
continue;
}
fieldNames += ",";
i++;
}
var strSql = string.Empty;
foreach (DataRow dataTableRow in dataTable.Rows)
{
var fieldValues = DestinationBindingFieldType == "int"
? $"{dataTableRow[DestinationDbField]},"
: $"'{dataTableRow[DestinationDbField]}',";
i = 0;
foreach (var columnInfo in columnDictionary)
{
if (columnInfo.Value == "string" || columnInfo.Value == "datetime")
{
fieldValues += $"'{dataTableRow[columnInfo.Key]}'";
}
else
{
fieldValues += $"{dataTableRow[columnInfo.Key]}";
}
if (i != DataItems.Count() - 1)
{
fieldValues += ",";
}
i++;
}
strSql += $"insert into {TableName}({fieldNames}) values({fieldValues});\n";
}
var db = new DataBaseHelper(DbType, ConnectionString);
db.ExecuteNonQuery(CommandType.Text, strSql);
}
catch (Exception ex)
{
Log.Error("GroupBindingDBFields插入记录失败", ex);
throw new Exception("BindingDBFields写入失败");
}
}
private void InsertToDb(IReadOnlyList <IBasicValue> values)
{
try
{
var fieldNames = string.Empty;
var fieldValues = string.Empty;
// make "field1,field2,..."
var i = 0;
foreach (BindingDataItem item in DataItems)
{
fieldNames += ((DbFieldBindingItem)item).DbFieldName;
if (i != (DataItems.Count() - 1))
{
fieldNames += ",";
}
var strTagValue = values[i].ToString();
switch (((DbFieldBindingItem)item).DbFieldType.ToLower())
{
case "string":
fieldValues += $"'{strTagValue}'";
break;
case "bool":
//sunjian 2019-12-20 bool值在数据库中应为0,1, PLC读取出来的true，false需要转换为0,1再写入数据库。
if (int.TryParse(strTagValue, out _))
{
fieldValues += $"{strTagValue}";
}
else
{
fieldValues += $"{(strTagValue.ToLower() == "true" ? 1 : 0)}";
}
break;
default:
fieldValues += strTagValue;
break;
}
if (i != (DataItems.Count() - 1))
{
fieldValues += ",";
}
i++;
}
// if update, need add compare field
if (_destOperateType == DestOperateType.Update)
{
fieldNames += ($",{_destConditionDBField}");
fieldValues += ($",{_destConditionValue}");
}
var strSql = $"insert into {TableName}({fieldNames}) values({fieldValues})";
var db = new DataBaseHelper(DbType, ConnectionString);
db.ExecuteNonQuery(CommandType.Text, strSql);
}
catch (Exception ex)
{
Log.Error("BindingDBFields插入记录失败:{0}", ex);
throw new Exception("BindingDBFields写入失败");
}
}
//public override void AddItem(BindingDataItem Item)
//{
// //_fieldNameList.Add(Item.Name);
// _dataItems.Add(Item);
//}
public override List <IBasicValue> GetValues()
{
try
{
var fieldNames = string.Empty;
// make "field1,field2,..."
var i = 0;
foreach (var item in DataItems)
{
fieldNames += ((DbFieldBindingItem)item).DbFieldName;
if (i != (DataItems.Count() - 1))
{
fieldNames += ",";
}
i++;
}
// condition SQL
string sqlWhere;
switch (SourceConditionType)
{
case SourceConditionType.Equal:
sqlWhere = $"where {SourceConditionDbField} = {SourceConditionValue}";
break;
case SourceConditionType.Max:
if ((SourceConditionValue as IBasicValue)?.SimpleType == "string")
{
throw new Exception("元数据DBField是字符串类型，执行Max操作将导致排序不准确，请选择数字/日期/bool等类型字段进行Max操作。");
}
/*执行最大最小操作时需要保证字段为数字格式，否则字符串格式的数字排序最大值一定是9开头的数字。 jiansun 2019-11-18*/
sqlWhere = $"order by {SourceConditionDbField} desc" /*sunjian 2019-11-16 ，最大最小条件写反了*/;
break;
case SourceConditionType.Min:
if ((SourceConditionValue as IBasicValue)?.SimpleType == "string")
{
throw new Exception("元数据DBField是字符串类型，执行Max操作将导致排序不正确，请选择数字/日期/bool类型字段进行Max操作。");
}
/*执行最大最小操作时需要保证字段为数字格式，否则字符串格式的数字排序最大值一定是9开头的数字。 jiansun 2019-11-18*/
sqlWhere = $"order by {SourceConditionDbField} asc" /*sunjian 2019-11-16, 最大最小条件写反了*/;
break;
default:
throw new ArgumentOutOfRangeException();
}
var values = new List <IBasicValue>();
string strSql = null;
switch (DbType)
{
case DatabaseType.Oracle:
strSql = $"select * from (select {fieldNames} from {TableName} {sqlWhere}) where rownum = 1";
break;
case DatabaseType.Sqlserver:
strSql = $"select top 1 {fieldNames} from {TableName} {sqlWhere}"; //sunjian 2019-12-15 修复sql语句bug
break;
case DatabaseType.Mysql:
strSql = $"select {fieldNames} from {TableName} {sqlWhere} limit 1";
break;
case DatabaseType.Access:
strSql = $"select top 1 {fieldNames} from {TableName} {sqlWhere}";
break;
case DatabaseType.Unknown:
break;
default:
throw new ArgumentOutOfRangeException();
}
DataBaseHelper db = new DataBaseHelper(DbType, ConnectionString);
DataSet set = db.GetDataSet(CommandType.Text, strSql);
if (set.Tables[0].Rows.Count != 0)
{
foreach (object data in set.Tables[0].Rows[0].ItemArray)
{
var theValue = Parameter.CreateBasicValue(data.GetType().ToString(), data.ToString());
values.Add(theValue);
}
}
GetValueSuccessCounts++;
return(values);
}
catch (Exception ex)
{
Log.Error("BindingDBFields获取数值失败:{0}", ex);
GetValueFailedCounts++;
return(null);
}
}
/// <summary>
/// 编辑选择的数据
/// </summary>
/// <param name="o"></param>
public void EditData(object o)
{
// update data
EditItemData.title = Title;
EditItemData.detail = Detail;
EditItemData.work_date = (WorkDateTime);
EditItemData.type = this.Type;
EditItemData.state = this.Status;
EditItemData.begin_time = Common.GetTimeSecond(this.Begin_time);
EditItemData.end_time = Common.GetTimeSecond(this.End_time);
EditItemData.spend =
(long.Parse(Common.GetTimeSecond(this.End_time)) - (long.Parse(Common.GetTimeSecond(this.Begin_time))))
.ToString();
for (int i = 0; i < DataItems.Count(); i++)
{
if (EditItemData.GetID() == DataItems[i].GetID())
{
DataItems[i] = EditItemData;
}
}
var result = DialogHost.Show(new LoadingDialog(), "RootDialog", delegate(object sender, DialogOpenedEventArgs args)
{
// 获取编辑数据
WorkTimeData postWorkTimeData = new WorkTimeData()
{
__ID__ = EditItemData.GetID(),
work_date = Common.GetTimeSecond(WorkDateTime),
title = this.Title,
detail = this.Detail,
type = this.Type,
state = this.Status,
begin_time = Common.GetTimeSecond(this.Begin_time),
end_time = Common.GetTimeSecond(this.End_time),
spend = (long.Parse(Common.GetTimeSecond(this.End_time)) - (long.Parse(Common.GetTimeSecond(this.Begin_time)))).ToString()
};
string temp = NetHelper.GETProperties(postWorkTimeData);
string addUrl = "https://api.bobdong.cn/time_manager/data/update?access_token=" + MainStaticData.AccessToken;
var datas = NetHelper.HttpCall(addUrl, temp, HttpEnum.Post);
var returnData = JsonHelper.Deserialize <ReturnData <WorkTimeData> >(datas);
ThreadStart start = delegate()
{
Mainthread.BeginInvoke((Action) delegate()// 异步更新界面
{
args.Session.Close(false);
if (returnData.code != 0)
{
MessageShow(o, returnData.message);
}
else
{
CleanData(null);
MessageShow(o, "Edit Success !");
}
});
};
new Thread(start).Start(); // 启动线程
});
}
public override DataTable GetDataTable()
{
try
{
var fieldNames = $"{SourceDbField},";
// make "field1,field2,..."
var i = 0;
foreach (var item in DataItems)
{
fieldNames += ((DBFieldBindingItem)item).DBFieldName;
if (i != DataItems.Count() - 1)
{
fieldNames += ",";
}
i++;
}
var strSql = string.Empty;
switch (DbType)
{
case DatabaseType.ORACLE:
/* strSQL = $"select * from (select {fieldNames} from {TableName} where rownum = 1";
* break;*/
throw new ArgumentOutOfRangeException();
case DatabaseType.ACCESS:
strSql = SourceBindingFieldType == "int"
? $"select {fieldNames} from {TableName} where {SourceDbField}>={SourceStartNumber} and {SourceDbField}<={SourceEndNumber} order by {SourceDbField}"
: $"select {fieldNames} from {TableName} where {SourceDbField}>=#{SourceStartDateTime:d}# and {SourceDbField}<=#{SourceEndDateTime:d}# order by {SourceDbField}";
break;
case DatabaseType.SQLSERVER:
strSql = SourceBindingFieldType == "int"
? $"select {fieldNames} from {TableName} where {SourceDbField}>={SourceStartNumber} and {SourceDbField}<={SourceEndNumber} order by {SourceDbField}"
: $"select {fieldNames} from {TableName} where {SourceDbField}>='{SourceStartDateTime:d}' and {SourceDbField}<='{SourceEndDateTime:d}' order by {SourceDbField}";
break;
case DatabaseType.MYSQL:
throw new ArgumentOutOfRangeException();
/*strSQL = $"select {fieldNames} from {_tableName} {sqlWhere} limit 1";
* break;*/
case DatabaseType.UNKNOWN:
break;
default:
throw new ArgumentOutOfRangeException();
}
var db = new DataBaseHelper(DbType, ConnectionString);
var set = db.GetDataSet(CommandType.Text, strSql);
return(set.Tables[0]);
}
catch (Exception e)
{
Log.Error("GroupBinding获取捆绑单位失败", e);
return(null);
}
}
public int Count()
{
return(DataItems.Count());
}
public int Count(string criteria = null)
{
return(DataItems.Count(GetExpression(criteria)));
}
