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