/// <summary> /// 更新记录 /// </summary> /// <param name="model">BizProcess.Data.Model.DBConnection实体类</param> public int Update(BizProcess.Data.Model.DBConnection model) { string sql = @"UPDATE DBConnection SET Name=:Name,Type=:Type,ConnectionString=:ConnectionString,Note=:Note WHERE ID=:ID" ; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":Name", OracleDbType.Varchar2, 500) { Value = model.Name }, new OracleParameter(":Type", OracleDbType.Varchar2, 500) { Value = model.Type }, new OracleParameter(":ConnectionString", OracleDbType.Clob) { Value = model.ConnectionString }, model.Note == null ? new OracleParameter(":Note", OracleDbType.Clob) { Value = DBNull.Value } : new OracleParameter(":Note", OracleDbType.Clob) { Value = model.Note }, new OracleParameter(":ID", OracleDbType.Char, 36) { Value = model.ID } }; return(dbHelper.Execute(sql, parameters)); }
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)); }
/// <summary> /// 添加记录 /// </summary> /// <param name="model">BizProcess.Data.Model.DBConnection实体类</param> /// <returns>操作所影响的行数</returns> public int Add(BizProcess.Data.Model.DBConnection model) { string sql = @"INSERT INTO DBConnection (ID,Name,Type,ConnectionString,Note) VALUES(:ID,:Name,:Type,:ConnectionString,:Note)" ; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ID", OracleDbType.Char, 36) { Value = model.ID }, new OracleParameter(":Name", OracleDbType.Varchar2, 500) { Value = model.Name }, new OracleParameter(":Type", OracleDbType.Varchar2, 500) { Value = model.Type }, new OracleParameter(":ConnectionString", OracleDbType.Clob) { Value = model.ConnectionString }, model.Note == null ? new OracleParameter(":Note", OracleDbType.Clob) { Value = DBNull.Value } : new OracleParameter(":Note", OracleDbType.Clob) { Value = model.Note } }; return(dbHelper.Execute(sql, parameters)); }
/// <summary> /// 更新记录 /// </summary> /// <param name="model">BizProcess.Data.Model.DBConnection实体类</param> public int Update(BizProcess.Data.Model.DBConnection model) { string sql = @"UPDATE DBConnection SET Name=@Name,Type=@Type,ConnectionString=@ConnectionString,Note=@Note WHERE ID=@ID" ; MySqlParameter[] parameters = new MySqlParameter[] { new MySqlParameter("@Name", MySqlDbType.VarChar, 500) { Value = model.Name }, new MySqlParameter("@Type", MySqlDbType.VarChar, 500) { Value = model.Type }, new MySqlParameter("@ConnectionString", MySqlDbType.Text, -1) { Value = model.ConnectionString }, model.Note == null ? new MySqlParameter("@Note", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@Note", MySqlDbType.Text, -1) { Value = model.Note }, new MySqlParameter("@ID", MySqlDbType.VarChar, 36) { Value = model.ID } }; return(dbHelper.Execute(sql, parameters)); }
/// <summary> /// 添加记录 /// </summary> /// <param name="model">BizProcess.Data.Model.DBConnection实体类</param> /// <returns>操作所影响的行数</returns> public int Add(BizProcess.Data.Model.DBConnection model) { string sql = @"INSERT INTO DBConnection (ID,Name,Type,ConnectionString,Note) VALUES(@ID,@Name,@Type,@ConnectionString,@Note)" ; MySqlParameter[] parameters = new MySqlParameter[] { new MySqlParameter("@ID", MySqlDbType.VarChar, 36) { Value = model.ID }, new MySqlParameter("@Name", MySqlDbType.VarChar, 500) { Value = model.Name }, new MySqlParameter("@Type", MySqlDbType.VarChar, 500) { Value = model.Type }, new MySqlParameter("@ConnectionString", MySqlDbType.Text, -1) { Value = model.ConnectionString }, model.Note == null ? new MySqlParameter("@Note", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@Note", MySqlDbType.Text, -1) { Value = model.Note } }; return(dbHelper.Execute(sql, parameters)); }
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())); }
public ActionResult Edit(FormCollection collection) { string editid = Request.QueryString["id"]; BizProcess.Platform.DBConnection bdbConn = new BizProcess.Platform.DBConnection(); BizProcess.Data.Model.DBConnection dbconn = null; if (editid.IsGuid()) { dbconn = bdbConn.Get(editid.ToGuid()); } bool isAdd = !editid.IsGuid(); string oldXML = string.Empty; if (dbconn == null) { dbconn = new BizProcess.Data.Model.DBConnection(); dbconn.ID = Guid.NewGuid(); } else { oldXML = dbconn.Serialize(); } if (collection != null) { string Name = Request.Form["Name"]; string LinkType = Request.Form["LinkType"]; string ConnStr = Request.Form["ConnStr"]; string Note = Request.Form["Note"]; dbconn.Name = Name.Trim(); dbconn.Type = LinkType; dbconn.ConnectionString = ConnStr; dbconn.Note = Note; if (isAdd) { bdbConn.Add(dbconn); BizProcess.Platform.Log.Add("添加了应用程序库", dbconn.Serialize(), BizProcess.Platform.Log.Types.角色应用); ViewBag.Script = "alert('添加成功!');new BPUI.Window().reloadOpener();new BPUI.Window().close();"; } else { bdbConn.Update(dbconn); BizProcess.Platform.Log.Add("修改了应用程序库", "", BizProcess.Platform.Log.Types.角色应用, oldXML, dbconn.Serialize()); ViewBag.Script = "alert('修改成功!');new BPUI.Window().reloadOpener();new BPUI.Window().close();"; } bdbConn.ClearCache(); } ViewBag.TypeOptions = bdbConn.GetAllTypeOptions(dbconn.Type); return(View(dbconn)); }
/// <summary> /// 将DataRedar转换为List /// </summary> private List <BizProcess.Data.Model.DBConnection> DataReaderToList(OracleDataReader dataReader) { List <BizProcess.Data.Model.DBConnection> List = new List <BizProcess.Data.Model.DBConnection>(); BizProcess.Data.Model.DBConnection model = null; while (dataReader.Read()) { model = new BizProcess.Data.Model.DBConnection(); model.ID = dataReader.GetString(0).ToGuid(); model.Name = dataReader.GetString(1); model.Type = dataReader.GetString(2); model.ConnectionString = dataReader.GetString(3); if (!dataReader.IsDBNull(4)) { model.Note = dataReader.GetString(4); } List.Add(model); } return(List); }
/// <summary> /// 根据连接实体得到数据表 /// </summary> /// <param name="linkID"></param> /// <returns></returns> private System.Data.DataTable GetDataTable(BizProcess.Data.Model.DBConnection dbconn, string sql) { if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty()) { return(null); } DataTable dt = new DataTable(); switch (dbconn.Type) { #region SqlServer case "SqlServer": using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString)) { try { conn.Open(); using (SqlDataAdapter dap = new SqlDataAdapter(sql, conn)) { dap.Fill(dt); } } catch (SqlException ex) { Platform.Log.Add(ex); } } break; #endregion #region Oracle case "Oracle": using (OracleConnection conn = new OracleConnection(dbconn.ConnectionString)) { try { conn.Open(); using (OracleDataAdapter dap = new OracleDataAdapter(sql, conn)) { dap.Fill(dt); } } catch (OracleException ex) { Platform.Log.Add(ex); } } break; #endregion #region MySql case "MySql": using (MySqlConnection conn = new MySqlConnection(dbconn.ConnectionString)) { try { conn.Open(); using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, conn)) { dap.Fill(dt); } } catch (MySqlException ex) { Platform.Log.Add(ex); } } break; #endregion } return(dt); }