public JTable(DBTable dbTable, string connStr)
: this()
{
this.TableName = dbTable.TableName;
this.Fields = new List <JField>();
foreach (var item in dbTable.Columns)
{
JField field = new JField(item);
this.Fields.Add(field);
}
if (dbTable.PrimaryKey != null)
{
this.Fields.Add(new JField(dbTable.PrimaryKey));
}
foreach (var fk in dbTable.ForeignKeys)
{
this.Fields.Add(new JField(fk));
}
this.ConnStr = connStr;
}
public JField(DBColumn column)
{
this.FieldName = column.ColumnName;
this.ValueType = JField.GetJValueType(column.DbType);
this.FirstOperand = new JOperateNum(this.FieldName, this.ValueType);
this.AllowNull = column.AllowNull;
if (column is DBPrimaryKey)
{
DBPrimaryKey pk = column as DBPrimaryKey;
this.FirstOperand.ValueCategroy = JValueCategroy.Sequence;
this.FirstOperand.MinValue = pk.CurrentValue + pk.Step;
this.FirstOperand.Step = pk.Step;
}
if (column is DBForeignKey)
{
DBForeignKey fk = column as DBForeignKey;
this.FirstOperand.ValueCategroy = JValueCategroy.FromTable;
this.FirstOperand.ReferenceTableName = fk.ReferenceTableName;
this.FirstOperand.ReferenceColumnName = fk.ReferenceColumnName;
}
this.Visible = false;
this.Order = 0;
}
public JTable(DBTable dbTable, string connStr)
: this()
{
this.TableName = dbTable.TableName;
this.Fields = new List<JField>();
foreach (var item in dbTable.Columns)
{
JField field = new JField(item);
this.Fields.Add(field);
}
if (dbTable.PrimaryKey != null)
this.Fields.Add(new JField(dbTable.PrimaryKey));
foreach (var fk in dbTable.ForeignKeys)
{
this.Fields.Add(new JField(fk));
}
this.ConnStr = connStr;
}
public void Process(string connStr = "")
{
if (string.IsNullOrEmpty(connStr))
{
connStr = this.ConnStr;
}
using (SqlConnection conn = new SqlConnection(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();
}
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);
object value2 = field.SecondOperand == null ? null : field.SecondOperand.GetValue(conn, fieldValuesOfCurrentRow);
object value = value1;
if (field.Operator != null && field.SecondOperand != null && field.SecondOperand.ValueType == JValueType.Numeric)
{
switch (field.FirstOperand.ValueType)
{
case JValueType.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 JValueType.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 JValueType.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 == JValueType.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(builder, this);
builder.Clear();
}
}
}
