public TableConfigurator(JTable table, string connStr = "")
: this()
{
this.tableConfigCtrl1.TableSetting = table;
this.ConnStr = !string.IsNullOrEmpty(table.ConnStr) ? table.ConnStr : connStr;
this.Text = table.TableName;
}
public void Process(string connStr = "")
{
if (string.IsNullOrEmpty(connStr))
{
connStr = this.ConnStr;
}
using (OleDbConnection conn = new OleDbConnection(connStr))
{
JTable table = this;
StringBuilder builder = new StringBuilder();
if (!string.IsNullOrEmpty(table.BeforeSQL))
{
builder.Append(Constants.SQLParagraphStartFlag).AppendLine();
builder.Append(table.BeforeSQL).AppendLine();
builder.Append(Constants.SQLParagraphEndFlag).AppendLine();
}
List <ColumnDataCache> caches = new List <ColumnDataCache>();
table.Fields.ForEach(op =>
{
string tableName = op.FirstOperand.ReferenceTableName;
string columnName = op.FirstOperand.ReferenceColumnName;
if (op.FirstOperand.ValueCategroy == JValueCategroy.FromTable && !HaveParameter(op.FirstOperand.RefFilter) && caches.Count(row => row.Table == TableName && row.Column == columnName) < 1)
{
List <object> datas = CommonDAL.GetValues(conn, tableName, columnName, op.FirstOperand.RefFilter);
caches.Add(new ColumnDataCache()
{
Table = tableName, Column = columnName, Datas = datas
});
}
if (op.SecondOperand != null)
{
string tableName1 = op.FirstOperand.ReferenceTableName;
string columnName1 = op.FirstOperand.ReferenceColumnName;
if (op.SecondOperand.ValueCategroy == JValueCategroy.FromTable && !HaveParameter(op.SecondOperand.RefFilter) && caches.Count(row => row.Table == tableName1 && row.Column == columnName1) < 1)
{
List <object> datas = CommonDAL.GetValues(conn, tableName1, columnName1, op.FirstOperand.RefFilter);
caches.Add(new ColumnDataCache()
{
Table = tableName1, Column = columnName1, Datas = datas
});
}
}
});
for (int i = 0; i < table.DataCount; i++)
{
#region 生成每一条数据
string format = "insert into {0}({1}) values({2});";
StringBuilder fieldNameBuilder = new StringBuilder();
StringBuilder fieldValueBuilder = new StringBuilder();
Dictionary <string, object> fieldValuesOfCurrentRow = new Dictionary <string, object>();
JField[] fields = table.Fields.Where(row => row.Visible == true).OrderBy(row => row.Order).ToArray();
for (int f = 0; f < fields.Count(); f++)
{
JField field = fields[f];
try
{
fieldNameBuilder.AppendFormat("{0},", field.FieldName);
object value1 = field.FirstOperand.GetValue(conn, fieldValuesOfCurrentRow, caches);
object value2 = field.SecondOperand == null ? null : field.SecondOperand.GetValue(conn, fieldValuesOfCurrentRow, caches);
object value = value1;
if (field.Operator != null && field.SecondOperand != null && field.SecondOperand.ValueType == JFieldType.Numeric)
{
switch (field.FirstOperand.ValueType)
{
case JFieldType.DateTime:
DateTime dtValue1 = DateTime.Parse(value1.ToJString(DateTime.Now.ToString()));
double dtParameter = double.Parse(value2.ToJString("0"));
switch (field.Operator)
{
case "+": value = dtValue1.AddDays(dtParameter); break;
case "-": value = dtValue1.AddDays(-dtParameter); break;
case "*": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "*");
case "/": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "/");
case "%": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "%");
}
break;
case JFieldType.Numeric:
double numericParameter1 = double.Parse(value1.ToJString("0"));
double numericParameter2 = double.Parse(value2.ToJString("0"));
switch (field.Operator)
{
case "+": value = numericParameter1 + numericParameter2; break;
case "-": value = numericParameter1 - numericParameter2; break;
case "*": value = numericParameter1 * numericParameter2; break;
case "/": value = numericParameter1 / numericParameter2; break;
case "%": value = (int)numericParameter1 % (int)numericParameter2; break;
}
break;
case JFieldType.String:
string strValue1 = value1.ToJString("");
string strValue2 = value2.ToJString("");
switch (field.Operator)
{
case "+": value = strValue1 + strValue2; break;
case "-": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "-");
case "*": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "*");
case "/": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "/");
case "%": throw new FieldValueTypeNotSupportOperatorException(field.FieldName, field.ValueType, "%");
}
break;
}
}
fieldValueBuilder.AppendFormat(
GetFileValueFormat(field.ValueType)
, field.ValueType == JFieldType.DateTime ? DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") : value
);
fieldValuesOfCurrentRow.Add(field.FieldName, value);
}
catch (Exception ex)
{
throw new Exception(string.Format("Data：{0}-{1},Filed:{2} ErrorMessage:{3} ", field.ValueType.ToString(), field.FirstOperand.ValueCategroy.ToString(), field.FieldName, ex.ToString()));
}
}
#endregion
builder.AppendFormat(format,
table.TableName,
fieldNameBuilder.ToString(0, fieldNameBuilder.Length - 1),
fieldValueBuilder.ToString(0, fieldValueBuilder.Length - 1)).AppendLine();
if (builder.Length > Constants.SqlBufferSize && SqlProcess != null)
{
SqlProcess(builder, this);
builder.Clear();
}
}
if (!string.IsNullOrEmpty(table.AfterSQL))
{
builder.Append(Constants.SQLParagraphStartFlag).AppendLine();
builder.Append(table.AfterSQL).AppendLine();
builder.Append(Constants.SQLParagraphEndFlag).AppendLine();
}
if (builder.Length > 0 && SqlProcess != null)
{
SqlProcess(builder, this);
builder.Clear();
}
}
}
public object GetValue(OleDbConnection conn, Dictionary <string, object> fieldValuesOfCurrentRow, List <ColumnDataCache> caches)
{
if (this == null)
{
return(null);
}
object value = "";
switch (this.ValueCategroy)
{
case JValueCategroy.List:
#region List不管是DateTime、Numeric、String处理方式都一样
object[] values = this.Values.ToArray();
int listCount = this.Values.Count();
int randNum = rd.Next(0, listCount);
value = values[randNum];
break;
#endregion
case JValueCategroy.Range:
#region Range
switch (this.ValueType)
{
case JFieldType.DateTime:
#region Range
DateTime minDate = DateTime.Parse(this.MinValue.ToJString());
DateTime maxDate = DateTime.Parse(this.MaxValue.ToJString());
int minutesDiff = (int)(maxDate - minDate).TotalMinutes;
int randMinutes = rd.Next(0, minutesDiff);
value = minDate.AddMinutes(randMinutes);
break;
#endregion
case JFieldType.Numeric:
#region Range
decimal maxSeed = decimal.Parse(this.MaxValue.ToJString());
decimal minSeed = decimal.Parse(this.MinValue.ToJString());
if (maxSeed <= 1)
{
maxSeed = maxSeed * 10000;
minSeed = minSeed * 10000;
value = (decimal)rd.Next((int)minSeed, (int)maxSeed) / 10000;
}
else
{
value = rd.Next((int)minSeed, (int)maxSeed);
}
break;
#endregion
case JFieldType.String:
value = String.Format(string.IsNullOrEmpty(this.Format) ? "{0}" : this.Format, rd.Next((Int32)this.MinValue, (Int32)this.MaxValue));
break;
}
break;
#endregion
case JValueCategroy.Sequence:
#region Sequence
switch (this.ValueType)
{
case JFieldType.DateTime:
#region Sequence
throw new FieldValueTypeNotSupportValueCategroyException(this.FieldName, this.ValueType, this.ValueCategroy);
#endregion
case JFieldType.Numeric:
#region Sequence
value = this.MinValue;
this.MinValue = int.Parse(this.MinValue.ToJString("0")) + int.Parse(this.Step.ToJString("0"));
break;
#endregion
case JFieldType.String:
value = String.Format(string.IsNullOrEmpty(this.Format) ? "{0}" : this.Format, this.MinValue);
this.MinValue = int.Parse(this.MinValue.ToJString("0")) + int.Parse(this.Step.ToJString("0"));
break;
}
break;
#endregion
case JValueCategroy.FromTable:
#region FromTable
string refTableName = this.ReferenceTableName;
string refColumnName = this.ReferenceColumnName;
List <string> parameters = new List <string>();
string filterFormat = this.RefFilter.ToParameters(parameters);
List <object> parameterValues = new List <object>();
foreach (var item in parameters)
{
parameterValues.Add(fieldValuesOfCurrentRow[item]);
}
string refFilter = string.Format(filterFormat, parameterValues.ToArray());
var tableCache = caches.Where(row => row.Table.Equals(refTableName) && row.Column.Equals(refColumnName)).FirstOrDefault();
List <object> cacheData = tableCache == null ? new List <object>() : tableCache.Datas;
object tempValue = !JTable.HaveParameter(this.RefFilter) ? cacheData[rd.Next(0, cacheData.Count - 1)] : CommonDAL.GetValue(conn, refTableName, refColumnName, refFilter);
SourceFieldData tempData = new SourceFieldData()
{
fieldName = refColumnName,
TableName = refTableName,
Value = tempValue
};
value = tempData.Value;
break;
#endregion
case JValueCategroy.OtherField:
#region OtherField 数据准备
if (!fieldValuesOfCurrentRow.ContainsKey(this.OtherFiledName))
{
throw new FieldValueTypeNotSupportValueCategroyException(this.FieldName, this.ValueType, this.ValueCategroy);
}
value = fieldValuesOfCurrentRow[this.OtherFiledName];
#endregion
break;
}
return(value);
}
private void TranslateMenuItemOfTVSource_Click(object sender, EventArgs e)
{
this.CheckConnStringAssigned(() =>
{
DBTable dbTable = GetDBTableBySourceTreeNode(tvSource.SelectedNode); ;
if (dbTable != null)
{
JTable table = new JTable(dbTable, this.ConnStr);
ShowConfigTableForm(table);
this.ShowMessage("已翻译表【{0}】为配置文件", dbTable.TableName);
}
});
}
private void ShowConfigTableForm(JTable table)
{
TableConfigurator form = new TableConfigurator(table, this.ConnStr);
form.MdiParent = this.MdiParent;
form.ShowStatus = true;
form.Show();
}