/// <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)); }