/// <summary>
/// 将DataRedar转换为List
/// </summary>
private List <BizProcess.Data.Model.DBExtract> DataReaderToList(MySqlDataReader dataReader)
{
List <BizProcess.Data.Model.DBExtract> List = new List <BizProcess.Data.Model.DBExtract>();
BizProcess.Data.Model.DBExtract model = null;
while (dataReader.Read())
{
model = new BizProcess.Data.Model.DBExtract();
model.ID = dataReader.GetGuid(0);
model.Name = dataReader.GetString(1);
if (!dataReader.IsDBNull(2))
{
model.Comment = dataReader.GetString(2);
}
if (!dataReader.IsDBNull(3))
{
model.DBConnID = dataReader.GetGuid(3);
}
if (!dataReader.IsDBNull(4))
{
model.DesignJSON = dataReader.GetString(4);
}
model.ExtractType = dataReader.GetBoolean(5);
if (!dataReader.IsDBNull(6))
{
model.RunTime = dataReader.GetString(6);
}
model.OnlyIncrement = dataReader.GetBoolean(7);
List.Add(model);
}
return(List);
}
public int Transfer(BizProcess.Data.Model.DBExtract dbe)
{
//check if the table exists
if (!TableExists(dbe.Name))
{
return(-1); //table doesn't exist
}
//read data
BizProcess.Platform.DBConnection bdbConn = new BizProcess.Platform.DBConnection();
BizProcess.Data.Model.DBConnection conn = bdbConn.Get(dbe.DBConnID);
List <string> keys = null;
if (dbe.OnlyIncrement)
{
//get all the primary keys stored in the system database
keys = GetAllKeys(dbe.Name, dbe.DesignJSON);
//get the sql which selects records except all the inserted primary keys
}
string sql = GetSelectSql(dbe.DesignJSON, keys);
System.Data.DataTable dt = GetDataTable(conn, sql);
//transfer data
List <string> sqlList = GetInsertSql(dbe.Name, dbe.DesignJSON, dt);
if (sqlList.Count == 0)
{
return(1);
}
return(dataDBExtract.ExecuteStatement(sqlList));
}
public string Transfer()
{
string dbeid = Request.QueryString["id"];
if (!dbeid.IsGuid())
{
return("Invalid ID is required.");
}
BizProcess.Platform.DBExtract bdbConn = new BizProcess.Platform.DBExtract();
BizProcess.Data.Model.DBExtract dbe = null;
if (dbeid.IsGuid())
{
dbe = bdbConn.Get(dbeid.ToGuid());
}
int ret = bdbConn.Transfer(dbe);
if (ret <= 0)
{
BizProcess.Platform.Log.Add("An unkown error occured while transfering data.", dbe.Serialize(), BizProcess.Platform.Log.Types.流程相关);
return("An unkown error occured while transfering data.");
}
dbe.LastRunTime = DateTime.Now;
bdbConn.Update(dbe, false);
return("Transfer is finished successfully.");
}
/// <summary>
/// 删除
/// </summary>
public int Delete(Guid id)
{
BizProcess.Data.Model.DBExtract model = dataDBExtract.Get(id);
dropTable(model);
int i = dataDBExtract.Delete(id);
ClearCache();
return(i);
}
private int createTable_Oracle(BizProcess.Data.Model.DBExtract dbe)
{
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.AppendFormat("CREATE TABLE {0}(", dbe.Name);
//sql.AppendFormat("", dbe.Comment);
var jsonData = LitJson.JsonMapper.ToObject(dbe.DesignJSON);
BizProcess.Platform.DBConnection bdbConn = new BizProcess.Platform.DBConnection();
BizProcess.Data.Model.DBConnection conn = bdbConn.Get(dbe.DBConnID);
using (System.Data.IDbConnection iconn = bdbConn.GetConnection(conn))
{
try
{
if (iconn.State == ConnectionState.Closed)
{
iconn.Open();
}
System.Data.DataTable schemaDt = bdbConn.GetTableSchema(iconn, jsonData["table"].ToString(), conn.Type);
//primary key field
string fieldname = jsonData["primarykey"].ToString();
System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", fieldname));
if (schemaDrs.Length == 0)
{
return(0);
}
sql.Append(getFieldString_Oracle(schemaDrs));
//fields
var fields = LitJson.JsonMapper.ToObject(jsonData["fields"].ToJson());
foreach (LitJson.JsonData field in fields)
{
fieldname = field["field"].ToString();
schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", fieldname));
if (schemaDrs.Length == 0)
{
return(0);
}
sql.Append(getFieldString_Oracle(schemaDrs));
}
sql.AppendFormat("CONSTRAINT {0}_pk PRIMARY KEY ({1}))",
dbe.Name,
jsonData["primarykey"].ToString());
}
catch (OracleException ex)
{
}
}
//create table into the system database
return(dataDBExtract.ExecuteStatement(sql.ToString()));
}
private int createTable_SqlServer(BizProcess.Data.Model.DBExtract dbe)
{
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.AppendFormat("CREATE TABLE {0}(", dbe.Name);
//sql.AppendFormat("", dbe.Comment);
var jsonData = LitJson.JsonMapper.ToObject(dbe.DesignJSON);
BizProcess.Platform.DBConnection bdbConn = new BizProcess.Platform.DBConnection();
BizProcess.Data.Model.DBConnection conn = bdbConn.Get(dbe.DBConnID);
using (System.Data.IDbConnection iconn = bdbConn.GetConnection(conn))
{
try
{
if (iconn.State == ConnectionState.Closed)
{
iconn.Open();
}
System.Data.DataTable schemaDt = bdbConn.GetTableSchema(iconn, jsonData["table"].ToString(), conn.Type);
//primary key field
string fieldname = jsonData["primarykey"].ToString();
System.Data.DataRow[] schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", fieldname));
if (schemaDrs.Length == 0)
{
return(0);
}
sql.Append(getFieldString_SqlServer(schemaDrs));
//fields
var fields = LitJson.JsonMapper.ToObject(jsonData["fields"].ToJson());
foreach (LitJson.JsonData field in fields)
{
fieldname = field["field"].ToString();
schemaDrs = schemaDt.Select(string.Format("f_name='{0}'", fieldname));
if (schemaDrs.Length == 0)
{
return(0);
}
sql.Append(getFieldString_SqlServer(schemaDrs));
}
sql.AppendFormat("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{1}] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]",
dbe.Name,
jsonData["primarykey"].ToString());
}
catch (SqlException ex)
{
}
}
//create table into the system database
return(dataDBExtract.ExecuteStatement(sql.ToString()));
}
/// <summary>
/// 更新记录
/// </summary>
/// <param name="model">BizProcess.Data.Model.DBExtract实体类</param>
public int Update(BizProcess.Data.Model.DBExtract model)
{
string sql = @"UPDATE DBExtract SET
Name=@Name,Comment=@Comment,DBConnID=@DBConnID,DesignJSON=@DesignJSON,ExtractType=@ExtractType,RunTime=@RunTime,OnlyIncrement=@OnlyIncrement
WHERE ID=@ID" ;
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("@Name", MySqlDbType.VarChar, 50)
{
Value = model.Name
},
model.Comment == null ? new MySqlParameter("@Comment", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@Comment", MySqlDbType.VarChar, -1)
{
Value = model.Comment
},
model.DBConnID == null ? new MySqlParameter("@DBConnID", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@DBConnID", MySqlDbType.VarChar, -1)
{
Value = model.DBConnID
},
model.DesignJSON == null ? new MySqlParameter("@DesignJSON", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@DesignJSON", MySqlDbType.VarChar, -1)
{
Value = model.DesignJSON
},
new MySqlParameter("@ExtractType", MySqlDbType.Bit, -1)
{
Value = model.ExtractType
},
model.RunTime == null ? new MySqlParameter("@RunTime", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@RunTime", MySqlDbType.VarChar, -1)
{
Value = model.RunTime
},
new MySqlParameter("@OnlyIncrement", MySqlDbType.Bit, -1)
{
Value = model.OnlyIncrement
},
new MySqlParameter("@ID", MySqlDbType.VarChar, -1)
{
Value = model.ID
}
};
return(dbHelper.Execute(sql, parameters));
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="model">BizProcess.Data.Model.DBExtract实体类</param>
/// <returns>操作所影响的行数</returns>
public int Add(BizProcess.Data.Model.DBExtract model)
{
string sql = @"INSERT INTO DBExtract
(ID,Name,Comment,DBConnID,DesignJSON,ExtractType,RunTime,OnlyIncrement)
VALUES(@ID,@Name,@Comment,@DBConnID,@DesignJSON,@ExtractType,@RunTime,@OnlyIncrement)" ;
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("@ID", MySqlDbType.VarChar, -1)
{
Value = model.ID
},
new MySqlParameter("@Name", MySqlDbType.VarChar, 50)
{
Value = model.Name
},
model.Comment == null ? new MySqlParameter("@Comment", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@Comment", MySqlDbType.VarChar, -1)
{
Value = model.Comment
},
model.DBConnID == null ? new MySqlParameter("@DBConnID", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@DBConnID", MySqlDbType.VarChar, -1)
{
Value = model.DBConnID
},
model.DesignJSON == null ? new MySqlParameter("@DesignJSON", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@DesignJSON", MySqlDbType.VarChar, -1)
{
Value = model.DesignJSON
},
new MySqlParameter("@ExtractType", MySqlDbType.Bit, -1)
{
Value = model.ExtractType
},
model.RunTime == null ? new MySqlParameter("@RunTime", MySqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new MySqlParameter("@RunTime", MySqlDbType.VarChar, -1)
{
Value = model.RunTime
},
new MySqlParameter("@OnlyIncrement", MySqlDbType.Bit, -1)
{
Value = model.OnlyIncrement
}
};
return(dbHelper.Execute(sql, parameters));
}
/// <summary>
/// 更新
/// </summary>
public int Update(BizProcess.Data.Model.DBExtract model, bool tableOperation = true)
{
int i = dataDBExtract.Update(model);
if (i > 0 && tableOperation)
{
i = dropTable(model);
i = CreateTable(model);
}
ClearCache();
return(i);
}
/// <summary>
/// 新增
/// </summary>
public int Add(BizProcess.Data.Model.DBExtract model)
{
int i = dataDBExtract.Add(model);
if (i > 0)
{
i = CreateTable(model);
}
ClearCache();
return(i);
}
public int CreateTable(BizProcess.Data.Model.DBExtract dbe)
{
int ret = 0;
switch (BizProcess.Utility.Config.DataBaseType)
{
case "MSSQL":
ret = createTable_SqlServer(dbe);
break;
case "ORACLE":
ret = createTable_Oracle(dbe);
break;
case "MYSQL":
//ret = createTable_MySql(dbe);
break;
}
return(ret);
}
/// <summary>
/// 将DataRedar转换为List
/// </summary>
private List <BizProcess.Data.Model.DBExtract> DataReaderToList(OracleDataReader dataReader)
{
List <BizProcess.Data.Model.DBExtract> List = new List <BizProcess.Data.Model.DBExtract>();
BizProcess.Data.Model.DBExtract model = null;
while (dataReader.Read())
{
model = new BizProcess.Data.Model.DBExtract();
model.ID = dataReader.GetString(0).ToGuid();
model.Name = dataReader.GetString(1);
if (!dataReader.IsDBNull(2))
{
model.Comment = dataReader.GetString(2);
}
if (!dataReader.IsDBNull(3))
{
model.DBConnID = dataReader.GetString(3).ToGuid();
}
if (!dataReader.IsDBNull(4))
{
model.DesignJSON = dataReader.GetString(4);
}
model.ExtractType = dataReader.GetInt16(5) == 1?true:false;
if (!dataReader.IsDBNull(6))
{
model.RunTime = dataReader.GetString(6);
}
model.OnlyIncrement = dataReader.GetInt16(7) == 1?true:false;
if (!dataReader.IsDBNull(8))
{
model.LastRunTime = dataReader.GetDateTime(8);
}
List.Add(model);
}
return(List);
}
public int dropTable(BizProcess.Data.Model.DBExtract dbe)
{
string sql = string.Format("drop table {0}", dbe.Name);
return(dataDBExtract.ExecuteStatement(sql.ToString()));
}
/// <summary>
/// 更新记录
/// </summary>
/// <param name="model">BizProcess.Data.Model.DBExtract实体类</param>
public int Update(BizProcess.Data.Model.DBExtract model)
{
string sql = @"UPDATE DBExtract SET
Name=:Name,""COMMENT""=:Comment1,DBConnID=:DBConnID,DesignJSON=:DesignJSON,ExtractType=:ExtractType,RunTime=:RunTime,OnlyIncrement=:OnlyIncrement,LastRunTime=:LastRunTime
WHERE ID=:ID" ;
OracleParameter[] parameters = new OracleParameter[] {
new OracleParameter(":Name", OracleDbType.Varchar2, 50)
{
Value = model.Name
},
model.Comment == null ? new OracleParameter(":Comment1", OracleDbType.Varchar2)
{
Value = DBNull.Value
} : new OracleParameter(":Comment1", OracleDbType.Varchar2)
{
Value = model.Comment
},
model.DBConnID.IsEmptyGuid() ? new OracleParameter(":DBConnID", OracleDbType.Char, 36)
{
Value = DBNull.Value
} : new OracleParameter(":DBConnID", OracleDbType.Char, 36)
{
Value = model.DBConnID
},
model.DesignJSON == null ? new OracleParameter(":DesignJSON", OracleDbType.Clob)
{
Value = DBNull.Value
} : new OracleParameter(":DesignJSON", OracleDbType.Clob)
{
Value = model.DesignJSON
},
new OracleParameter(":ExtractType", OracleDbType.Int16)
{
Value = model.ExtractType?1:0
},
model.RunTime == null ? new OracleParameter(":RunTime", OracleDbType.Varchar2)
{
Value = DBNull.Value
} : new OracleParameter(":RunTime", OracleDbType.Varchar2)
{
Value = model.RunTime
},
new OracleParameter(":OnlyIncrement", OracleDbType.Int16)
{
Value = model.OnlyIncrement?1:0
},
model.LastRunTime == null ? new OracleParameter(":LastRunTime", OracleDbType.Date)
{
Value = DBNull.Value
} : new OracleParameter(":LastRunTime", OracleDbType.Date)
{
Value = model.LastRunTime
},
new OracleParameter(":ID", OracleDbType.Char, 36)
{
Value = model.ID
}
};
return(dbHelper.Execute(sql, parameters));
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="model">BizProcess.Data.Model.DBExtract实体类</param>
/// <returns>操作所影响的行数</returns>
public int Add(BizProcess.Data.Model.DBExtract model)
{
string sql = @"INSERT INTO DBExtract
(ID,Name,""COMMENT"",DBConnID,DesignJSON,ExtractType,RunTime,OnlyIncrement,LastRunTime)
VALUES(:ID,:Name,:Comment1,:DBConnID,:DesignJSON,:ExtractType,:RunTime,:OnlyIncrement,:LastRunTime)" ;
OracleParameter[] parameters = new OracleParameter[] {
new OracleParameter(":ID", OracleDbType.Char, 36)
{
Value = model.ID
},
new OracleParameter(":Name", OracleDbType.Varchar2, 50)
{
Value = model.Name
},
model.Comment == null ? new OracleParameter(":Comment1", OracleDbType.Varchar2)
{
Value = DBNull.Value
} : new OracleParameter(":Comment1", OracleDbType.Varchar2)
{
Value = model.Comment
},
model.DBConnID.IsEmptyGuid() ? new OracleParameter(":DBConnID", OracleDbType.Char, 36)
{
Value = DBNull.Value
} : new OracleParameter(":DBConnID", OracleDbType.Char, 36)
{
Value = model.DBConnID
},
model.DesignJSON == null ? new OracleParameter(":DesignJSON", OracleDbType.Clob)
{
Value = DBNull.Value
} : new OracleParameter(":DesignJSON", OracleDbType.Clob)
{
Value = model.DesignJSON
},
new OracleParameter(":ExtractType", OracleDbType.Int16)
{
Value = model.ExtractType?1:0
},
model.RunTime == null ? new OracleParameter(":RunTime", OracleDbType.Varchar2)
{
Value = DBNull.Value
} : new OracleParameter(":RunTime", OracleDbType.Varchar2)
{
Value = model.RunTime
},
new OracleParameter(":OnlyIncrement", OracleDbType.Int16)
{
Value = model.OnlyIncrement?1:0
},
model.LastRunTime == null ? new OracleParameter(":LastRunTime", OracleDbType.Date)
{
Value = DBNull.Value
} : new OracleParameter(":LastRunTime", OracleDbType.Date)
{
Value = model.LastRunTime
}
};
return(dbHelper.Execute(sql, parameters));
}
public ActionResult Edit(FormCollection collection)
{
string editid = Request.QueryString["id"];
BizProcess.Platform.DBExtract bdbConn = new BizProcess.Platform.DBExtract();
BizProcess.Data.Model.DBExtract dbe = null;
if (editid.IsGuid())
{
dbe = bdbConn.Get(editid.ToGuid());
}
bool isAdd = !editid.IsGuid();
string oldXML = string.Empty;
if (dbe == null)
{
dbe = new BizProcess.Data.Model.DBExtract();
dbe.ID = Guid.NewGuid();
}
else
{
oldXML = dbe.Serialize();
}
if (collection != null)
{
string Name = Request.Form["Name"];
string Comment = Request.Form["Comment"];
string DBConnID = Request.Form["DBConnID"];
string ExtractType = Request.Form["ExtractType"];
string RunTime = Request.Form["RunTime"];
string OnlyIncrement = Request.Form["OnlyIncrement"];
string DesignJSON = "";
string db_table = Request.Form["db_table"];
string db_primarykey = Request.Form["db_primarykey"];
bool bSchemaChanged = false;
if (dbe.DBConnID != DBConnID.ToGuid())
{
bSchemaChanged = true;
}
dbe.Name = Name.Trim();
dbe.Comment = Comment;
dbe.DBConnID = DBConnID.ToGuid();
System.Text.StringBuilder json = new System.Text.StringBuilder();
json.Append("{");
json.AppendFormat("\"table\":\"{0}\",", db_table.Trim());
json.AppendFormat("\"primarykey\":\"{0}\",", db_primarykey.Trim());
json.Append("\"fields\":[");
String[] fields = Request.Form.GetValues("link_field[]");
foreach (String field in fields)
{
json.Append("{");
json.AppendFormat("\"field\":\"{0}\"", field.Trim());
if (fields.Last() != field)
{
json.Append("},");
}
else
{
json.Append("}");
}
}
json.AppendFormat("]");
json.Append("}");
DesignJSON = json.ToString();
if (bSchemaChanged && (dbe.DesignJSON == null || !dbe.DesignJSON.Equals(DesignJSON)))
{
bSchemaChanged = true;
}
dbe.DesignJSON = DesignJSON;
dbe.ExtractType = ExtractType == "Auto" ? true : false;
dbe.RunTime = RunTime;
dbe.OnlyIncrement = OnlyIncrement == "OnlyIncrement" ? true : false;
if (isAdd)
{
bdbConn.Add(dbe);
BizProcess.Platform.Log.Add("添加了数据抽取", dbe.Serialize(), BizProcess.Platform.Log.Types.流程相关);
ViewBag.Script = "alert('添加成功!');new BPUI.Window().reloadOpener();new BPUI.Window().close();";
}
else
{
bdbConn.Update(dbe, bSchemaChanged);
BizProcess.Platform.Log.Add("修改了数据抽取", "", BizProcess.Platform.Log.Types.流程相关, oldXML, dbe.Serialize());
ViewBag.Script = "alert('修改成功!');new BPUI.Window().reloadOpener();new BPUI.Window().close();";
}
bdbConn.ClearCache();
}
return(View(dbe));
}
