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));
}
//
// GET: /DBConnection/
public ActionResult Index()
{
string query1 = string.Format("&appid={0}&tabid={1}", Request.QueryString["appid"], Request.QueryString["tabid"]);
BizProcess.Platform.DBConnection bdbconn = new BizProcess.Platform.DBConnection();
if (!Request.Form["DeleteBut"].IsNullOrEmpty())
{
string deleteID = Request.Form["checkbox_app"];
System.Text.StringBuilder delxml = new System.Text.StringBuilder();
foreach (string id in deleteID.Split(','))
{
Guid gid;
if (id.IsGuid(out gid))
{
delxml.Append(bdbconn.Get(gid).Serialize());
bdbconn.Delete(gid);
}
}
bdbconn.ClearCache();
BizProcess.Platform.Log.Add("删除了数据连接", delxml.ToString(), BizProcess.Platform.Log.Types.流程相关);
}
var connList = bdbconn.GetAll();
ViewBag.Query1 = query1;
return(View(connList));
}
/// <summary>
/// 测试连接的sql条件
/// </summary>
/// <returns></returns>
public string TestLineSqlWhere()
{
string connid = Request["connid"];
string table = Request["table"];
string tablepk = Request["tablepk"];
string where = Request["where"];
BizProcess.Platform.DBConnection dbconn = new BizProcess.Platform.DBConnection();
if (!connid.IsGuid())
{
return("流程未设置数据连接!");
}
var conn = dbconn.Get(connid.ToGuid());
if (conn == null)
{
return("未找到连接!");
}
string sql = "SELECT * FROM " + table + " WHERE 1=1 AND " + where;
if (dbconn.TestSql(conn, sql))
{
return("SQL条件正确!");
}
else
{
return("SQL条件错误!");
}
}
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));
}
public string GetTables()
{
Response.Charset = "utf-8";
string connID = Request.QueryString["connid"];
if (!connID.IsGuid())
{
return("[]");
}
List <string> tables = new BizProcess.Platform.DBConnection().GetTables(connID.ToGuid());
System.Text.StringBuilder sb = new System.Text.StringBuilder("[", 1000);
foreach (string table in tables)
{
sb.Append("{\"name\":");
sb.AppendFormat("\"{0}\"", table);
sb.Append("},");
}
return(sb.ToString().TrimEnd(',') + "]");
}
/// <summary>
/// 根据一个连接字符串得到一个字段值
/// </summary>
/// <param name="linkString">连接ID.表.主键</param>
/// <param name="field">字段</param>
/// <param name="instanceID">实例ID</param>
/// <returns></returns>
public string GetLinkFieldValue(string linkString, Guid instanceID)
{
string value = "";
string[] array = linkString.Split('.');
if (array.Length == 3)
{
var pkvalues = GetAll(instanceID);
Dictionary <string, string> key = new Dictionary <string, string>();
foreach (var pkvalue in pkvalues)
{
if (array[0].ToGuid() == pkvalue.LinkID && array[1] == pkvalue.TableName)
{
key.Add(pkvalue.FieldName, pkvalue.Value);
}
}
value = new BizProcess.Platform.DBConnection().GetFieldValue(array[2], key);
}
return(value);
}
public string GetFields()
{
string table = Request.QueryString["table"];
string connid = Request.QueryString["connid"];
if (table.IsNullOrEmpty() || !connid.IsGuid())
{
return("[]");
}
Dictionary <string, string> fields = new BizProcess.Platform.DBConnection().GetFields(connid.ToGuid(), table);
System.Text.StringBuilder sb = new System.Text.StringBuilder("[", 1000);
foreach (var field in fields)
{
sb.Append("{");
sb.AppendFormat("\"name\":\"{0}\",\"note\":\"{1}\"", field.Key, field.Value);
sb.Append("},");
}
return(sb.ToString().TrimEnd(',') + "]");
}
public string TestSql()
{
string sql = Request["sql"];
string dbconn = Request["dbconn"];
if (sql.IsNullOrEmpty() || !dbconn.IsGuid())
{
return("SQL语句为空或未设置数据连接");
}
BizProcess.Platform.DBConnection bdbconn = new BizProcess.Platform.DBConnection();
var dbconn1 = bdbconn.Get(dbconn.ToGuid());
if (bdbconn.TestSql(dbconn1, sql))
{
return("SQL语句测试正确");
}
else
{
return("SQL语句测试错误");
}
}
