private List <TreeNode> drillMembersComm(List <string> memberPath)
{
if (levels == null || levels.Count < 1)
{
throw new NoDefineLevelException();
}
StringBuilder sbSql = new StringBuilder();
int memberPathCount;
StringBuilder sbWhere = getDrillWhere(memberPath, out memberPathCount);
if (memberPathCount > levels.Count - 1)
{
throw new OlapLevelOverException();
}
string drillField = levels[memberPathCount].column;
sbSql.Append(" Select ");
sbSql.Append(drillField);
sbSql.Append(" From ");
sbSql.Append(table.name);
if (sbWhere.Length > 0)
{
sbSql.Append(" Where ");
sbSql.Append(sbWhere);
}
sbSql.Append(" Group By ");
sbSql.Append(drillField);
DatabaseAdmin dba = DatabaseAdmin.getInstance(_table.connection);
DataTable tb = dba.executeTable(sbSql.ToString());
List <TreeNode> nodes = new List <TreeNode>();
foreach (DataRow row in tb.Rows)
{
TreeNode node = new TreeNode();
node.text = row[drillField].ToString();
node.attr.Add("memberField", drillField);
if (memberPathCount + 1 < levels.Count)
{
List <string> subMembers = new List <string>();
if (memberPath != null && memberPath.Count > 0)
{
subMembers = new List <string>(memberPath);
}
subMembers.Add(node.text);
node.children = drillMembers(subMembers);
}
nodes.Add(node);
}
return(nodes);
}
public static DataSourceSchema BuildTableSchema(string connName, string tableName)
{
string defCon = ConnectionAdmin.getDefaultConnName();
if (!string.IsNullOrEmpty(connName) && connName.Equals(defCon, StringComparison.OrdinalIgnoreCase))
{
connName = null;
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
DataSourceSchema ret = new DataSourceSchema();
ret.ConnectionName = connName;
ret.TableName = tableName;
ret.SelectCommand = new CommandSchema();
ret.SelectCommand.CommandText = tableName;
ret.SelectCommand.CommandType = CommandType.TableDirect;
BuildFields(ret);
ret.PrimaryKeys = dba.GetPrimaryKeys(tableName);
BuildSchemaCommand(ret);
//string dsId = tableName;
//if (!string.IsNullOrEmpty(connName))
// dsId = connName + "." + dsId;
// DataSourceSchemaContainer.Instance().AddItem(dsId, ret);
return(ret);
}
internal static List <FilterRule> getTableFilters(string name)
{
string[] ary = name.Split(',');
string conn = "";
string tbName = name;
if (ary.Length > 1)
{
conn = ary[0];
tbName = ary[1];
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(conn);
TableDef tbDef = dba.getTableDef(tbName);
List <FilterRule> ret = new List <FilterRule>();
foreach (FieldDef fldDef in tbDef.FieldDefs)
{
FilterRule fr = new FilterRule();
fr.field = fldDef.Name;
ret.Add(fr);
}
return(ret);
}
public void refreshFields()
{
DatabaseAdmin dba = DatabaseAdmin.getInstance(_schema.ConnectionName);
DataSet ds = getDataSet();
if (ds == null || ds.Tables.Count < 1)
{
return;
}
foreach (DataColumn col in ds.Tables[0].Columns)
{
FieldSchema fs = _schema.Fields.FindItem(col.ColumnName);
if (fs == null)
{
fs = new FieldSchema();
fs.Id = col.ColumnName;
fs.DataType = dba.getDbType(col.DataType);
_schema.Fields.Add(fs);
}
}
if (DataSourceSchemaContainer.Instance().Contains(_name))
{
DataSourceSchemaContainer.Instance().UpdateItem(_name, _schema);
}
}
public override bool Check(string value)
{
//XDatabaseFactory dbfact = XDatabaseFactory.Instance;
DatabaseAdmin db = DatabaseAdmin.getInstance(connect);
object o = null;
try
{
o = db.Database.ExecuteScalar("if " + expression + " select 1 else select 0");
}
catch (Exception e)
{
throw new XException("表达式校验器在执行[" + expression + "]时发生错误," + e.Message);
}
if (o is bool)
{
return((bool)o);
}
string s = o.ToString();
if (string.IsNullOrEmpty(s))
{
return(false);
}
return(s != "0");
}
private void setQueryParameters(CommandSchema cmdSchema, DbCommand cmd)
{
DatabaseAdmin dbAdmin = DatabaseAdmin.getInstance(_schema.ConnectionName);
foreach (ParameterSchema p in cmdSchema.QueryParams)
{
switch (p.Direction)
{
case ParameterDirection.Input:
case ParameterDirection.InputOutput:
string value = p.DefaultValue;
if (Request.QueryString.AllKeys.Contains(p.Id))
{
value = Request[p.Id];
}
if (!string.IsNullOrEmpty(value) && value.StartsWith("(") && value.EndsWith(")"))
{
value = null;
object obj = Umc.invoke(value, null).ToString();
if (obj != null)
{
value = obj.ToString();
}
}
break;
}
}
}
public static EasyUiGridData createGrid(string connName, string tableName)
{
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
TableDef tableDef = dba.getTableDef(tableName);
string title = string.IsNullOrEmpty(tableDef.Title)?tableName:tableDef.Title;
return(EUGridUtils.getGrid(connName, title, tableDef.FieldDefs));
}
public override bool Check(string value)
{
//XDatabaseFactory dbfact = XDatabaseFactory.Instance;
DatabaseAdmin dba = DatabaseAdmin.getInstance(connectString);
object o = dba.Database.ExecuteScalar("");
return(o != null);
// db.ExecuteTable()
}
private static void BuildFields(DataSourceSchema schema)
{
DsAdapterCustomer dsa = new DsAdapterCustomer(schema);
DataSet ds = dsa.getDataSet();
DataTable netTable = ds.Tables[0];
// schema.Fields.Clear();
SchemaList <FieldSchema> tmpFieldSchema = new SchemaList <FieldSchema>();
//da.FillSchema(netTable, SchemaType.Source);
// schema.PrimaryKeys = new List<string>();
// foreach (DataColumn col in netTable.PrimaryKey)
// {
// schema.PrimaryKeys.Add(col.ColumnName);
// }
foreach (DataColumn col in netTable.Columns)
{
FieldSchema field = new FieldSchema();
field.Id = col.ColumnName;
field.Title = col.Caption;
if (col.ExtendedProperties.ContainsKey(DataSourceConst.ExProDescription))
{
if (col.ExtendedProperties[DataSourceConst.ExProDescription] != null)
{
field.Description = col.ExtendedProperties[DataSourceConst.ExProDescription].ToString();
}
}
field.ReadOnly = col.ReadOnly;
field.ReadOnly = col.AutoIncrement;
if (netTable.PrimaryKey.Contains(col))
{
field.IsKey = true;
}
if (col.ExtendedProperties.ContainsKey(DataSourceConst.ExProDbType))
{
field.DataType = (DbType)col.ExtendedProperties[DataSourceConst.ExProDbType];
}
else
{
field.DataType = DatabaseAdmin.getInstance().getDbType(col.DataType);
}
// schema.Fields.Add(field);
if (schema.Fields.FindItem(field.Id) == null)
{
schema.Fields.Add(field);
}
if (tmpFieldSchema.FindItem(field.Id) == null)
{
tmpFieldSchema.Add(field);
}
}
}
public EasyUiGridData drillTableToGrid(List <string> parentMembers)
{
DatabaseAdmin dba = DatabaseAdmin.getInstance(table.connection);
TableDef tbf = dba.getTableDef(table.name);
string title = string.IsNullOrEmpty(tbf.Title) ? table.name : tbf.Title;
EasyUiGridData ret = EUGridUtils.getGrid(table.connection, title, tbf.FieldDefs);
return(ret);
}
public static string getPrimaryKey(string connName, string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
return("");
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
TableDef tbDef = dba.getTableDef(tableName);
return(tbDef.MainKeys[0].Name);
}
public static List <string> getConstraintNames(string connName, string tableName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
DataTable dt = da.getConstraintTable(tableName);
List <string> ret = new List <string>();
foreach (DataRow row in dt.Rows)
{
ret.Add(row[0].ToString());
}
return(ret);
}
public static Dictionary <string, string> getTables(string connName)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
List <string> tables = dba.getTableNames();
foreach (string table in tables)
{
string text = dba.getTableTitle(table);
if (string.IsNullOrEmpty(text))
{
text = table;
}
ret.Add(table, text);
}
return(ret);
}
private List <ListDataRow> getSubTableRows(Dictionary <string, string> pks, SubTableSchema subSchema)
{
DatabaseAdmin dba = DatabaseAdmin.getInstance();
DataSource subDs = new DataSource(subSchema.Name);
DataSourceSchema dss = subDs.getSchema();
if (dss.SelectCommand.CommandType != CommandType.TableDirect)
{
throw new XException(string.Format(Lang.SubTableSelCommandTypeOnlyIsTable, subSchema.Name));
}
StringBuilder sb = new StringBuilder("select * from ");
sb.Append(dss.SelectCommand.CommandText);
sb.Append(" ");
sb.Append(" where ");
Hashtable ps = new Hashtable();
for (int i = 0; i < subSchema.Fks.Count; i++)
{
string fk = subSchema.Fks[i];
string pk = _schema.PrimaryKeys[i];
sb.Append(fk);
sb.Append("=@");
sb.Append(pk);
sb.Append(" and ");
ps.Add("@" + pk, pks[pk].ToString());
}
sb.Remove(sb.Length - 5, 5);
DbCommand cmd = dba.getSqlStringCommand(sb.ToString());
foreach (string key in ps.Keys)
{
dba.addInParameter(cmd, key, DbType.String, ps[key]);
}
DataTable tb = dba.executeTable(cmd);
List <ListDataRow> rows = new List <ListDataRow>();
foreach (DataRow row in tb.Rows)
{
rows.Add(DataSourceComm.readRow(tb, dss, row));
}
return(rows);
}
public string GetInsertCommand()
{
string tableName = GetFrom();
string insertCmd = "INSERT INTO " + tableName + " (";
string values = "";
DatabaseAdmin db = DatabaseAdmin.getInstance(schema.ConnectionName);
foreach (FieldSchema fldsch in schema.Fields)
{
if (!fldsch.ReadOnly && !db.isIdentityField(tableName, fldsch.Id) && !db.isRowGuidField(tableName, fldsch.Id))
{
insertCmd += " [" + fldsch.Id + "]" + XTableSchemaConst.FieldSplitor;
values += "@" + fldsch.Id + XTableSchemaConst.FieldSplitor;
}
}
values = values.Remove(values.LastIndexOf(XTableSchemaConst.FieldSplitor));
insertCmd = insertCmd.Remove(insertCmd.Length - 1);
insertCmd += ")";
insertCmd += " VALUES (" + values + ")";
if (schema.PrimaryKeys != null && schema.PrimaryKeys.Count > 0)
{
List <string> pks = schema.PrimaryKeys;
string id = "";
foreach (string fieldName in pks)
{
if (db.isIdentityField(tableName, fieldName))
{
id = fieldName;
break;
}
}
if (!string.IsNullOrEmpty(id))
{
insertCmd += ";SELECT * FROM " + tableName + " WHERE [" + id + "]=SCOPE_IDENTITY()";
}
}
// db.Close();
return(insertCmd);
}
public static Dictionary <string, string> getFields(string connName, string tableName)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
if (string.IsNullOrEmpty(tableName))
{
return(ret);
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
TableDef tbDef = dba.getTableDef(tableName);
ret.Add("", "");
foreach (FieldDef field in tbDef.FieldDefs)
{
string title = string.IsNullOrEmpty(field.Title) ? field.Name : field.Title;
ret.Add(field.Name, title);
}
return(ret);
}
private static List <DataListColumn> getColumns(List <FieldDef> fields)
{
List <DataListColumn> ret = new List <DataListColumn>();
foreach (FieldDef col in fields)
{
DbType type = DatabaseAdmin.getInstance().getDbType(col.Type);
if (type.Equals(DbType.Binary))
{
continue;
}
DataListColumn listCol = new DataListColumn();
string fldName = col.Name;
listCol.field = fldName;
listCol.title = col.Title;
listCol.resizable = true;
listCol.hidden = false;
listCol.editor = "text";
ret.Add(listCol);
}
return(ret);
}
private static List <FilterInput> getFilterInputs(string connName, List <FieldDef> fields)
{
List <FilterInput> ret = new List <FilterInput>();
foreach (FieldDef col in fields)
{
if (DatabaseAdmin.getInstance(connName).getDbType(col.Type) == DbType.Binary)
{
continue;
}
//if (col.DataType.Equals(typeof(System.String))) continue;
FilterInput fi = new FilterInput();
fi.field = col.Name;
fi.options = new FilterOption();
fi.op = FilterInput.BigTextOP;
ret.Add(fi);
}
return(ret);
}
public void import()
{
using (TransactionScope ts = new TransactionScope())
{
DatabaseAdmin dba = DatabaseAdmin.getInstance(_connName);
if (!dba.containsTableName(_tableName))
{
TableDef tabDef = getTableDef();
dba.createTable(tabDef);
}
eachReadRow(0, 0, delegate(ListDataRow row)
{
if (!row.isBlank())
{
dba.compareUpdate(_tableName, row);
}
});
ts.Complete();
}
}
public string GetUpdateCommand()
{
DatabaseAdmin db = DatabaseAdmin.getInstance(schema.ConnectionName);
List <string> pks = schema.PrimaryKeys;
if (pks == null || pks.Count < 1)
{
throw new E_SQLBuilder_NoPrimaryKey();
}
string tableName = GetFrom();
string update = "UPDATE " + tableName + " SET ";
foreach (FieldSchema fldsch in schema.Fields)
{
if (!fldsch.ReadOnly && !db.isIdentityField(tableName, fldsch.Id) && !db.isRowGuidField(tableName, fldsch.Id))
{
update += " [" + fldsch.Id + "]=@" + fldsch.Id + ",";
}
}
update = update.Remove(update.Length - 1);
update += " WHERE ";
foreach (string pk in pks)
{
update += " [" + pk + "]=@" + OLD_VERSION_PIX + pk + " And ";
}
update = update.Remove(update.LastIndexOf(" And "));
return(update);
}
/// <summary>
/// 删除字段
/// </summary>
/// <param name="connName">连接名</param>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
public static void deleteField(string connName, string tableName, string fieldName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.deleteField(tableName, fieldName);
}
/// <summary>
/// 获取过滤表单的输入组件定义
/// </summary>
/// <returns></returns>
private DataSet updateSourceTableRow(ListDataRow row)
{
DatabaseAdmin dba = DatabaseAdmin.getInstance(_schema.ConnectionName);
// Database db = dba.Database;
DbCommand cmd = null;
cmd = dba.getSqlStringCommand(" update " + _schema.TableName);
StringBuilder sb = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
sb.Append(" update [");
sb.Append(_schema.TableName);
sb.Append("] set ");
bool first = true;
foreach (string field in row.Keys)
{
string paramName = "@" + field.Replace(' ', '_');
FieldSchema fldSchema = null;
if (field.StartsWith(XSqlBuilder.OLD_VERSION_PIX))
{
string keyField = field.Replace(XSqlBuilder.OLD_VERSION_PIX, "");
fldSchema = _schema.Fields.GetItem(keyField);
sbWhere.Append(" and [");
sbWhere.Append(keyField);
sbWhere.Append("]=");
sbWhere.Append(paramName);
}
else
{
fldSchema = _schema.Fields.GetItem(field);
if (readOnlyFields.Contains(field))
{
continue;
}
if (first)
{
first = false;
}
else
{
sb.Append(",");
}
sb.Append("[");
sb.Append(field);
sb.Append("]");
sb.Append("=");
sb.Append(paramName);
}
dba.addInParameter(cmd, paramName, fldSchema.DataType, string.IsNullOrEmpty(row[field]) ? null : row[field]);
}
sbWhere.Remove(0, 5);
if (sbWhere.Length < 2)
{
throw new XException(Lang.UpdateNoKey);
}
sb.Append(" where ");
sb.Append(sbWhere.ToString());
cmd.CommandText = sb.ToString();
return(dba.executeDateSet(cmd));
}
public static void modifyViewScript(string connName, string viewName, string script)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.modifyViewScript(viewName, script);
}
public static DatabaseAdmin getDBA()
{
return(DatabaseAdmin.getInstance(_settings.ConnectionName));
}
public static string getSP(string connName, string spName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
return(da.getProcText(spName));
}
private static void BuildFields(DataSourceSchema schema)
{
string connectionName = schema.ConnectionName;
string selectSql = schema.SelectCommand.CommandText;
DatabaseAdmin dbAdmin = DatabaseAdmin.getInstance(connectionName);
XSql xSql = new XSql(selectSql);
string tableName = xSql.GetFrom();
// schema.Fields.Clear();
SchemaList <FieldSchema> tmpfs = new SchemaList <FieldSchema>();
DbConnection con = dbAdmin.Database.CreateConnection();
try
{
DataTable netTable = new DataTable();
DbDataAdapter da = dbAdmin.Database.GetDataAdapter();
DbCommand cmd = con.CreateCommand();
cmd.CommandText = selectSql;
string[] paramNames = xSql.GetParamNames();
for (int i = 0; i < paramNames.Count(); i++)
{
string pName = paramNames[i];
if (!string.IsNullOrEmpty(pName))
{
pName = pName.Trim();
}
SqlParameter p = new SqlParameter();
p.ParameterName = "@" + pName;
//p.SourceColumn = pName;
p.Value = "";
cmd.Parameters.Add(p);
}
da.SelectCommand = cmd;
da.Fill(netTable);
//da.FillSchema(netTable, SchemaType.Source);
foreach (DataColumn col in netTable.Columns)
{
FieldSchema field = new FieldSchema();
field.Id = col.ColumnName;
field.DisplayName = col.Caption;
// schema.Fields.Add(field);
if (schema.Fields.FindItem(field.Id) == null)
{
schema.Fields.Add(field);
}
if (tmpfs.FindItem(field.Id) == null)
{
tmpfs.Add(field);
}
}
//从原是表中获取，字段标题等信息；
if (xSql.IsFromSourceTable(tableName))
{
List <string> pks = dbAdmin.GetPrimaryKeys(tableName);
schema.KeyField = JsonConvert.SerializeObject(pks);
//if (pks.Count > 0)
// keyfield = pks[0];
//if (!string.IsNullOrEmpty(keyfield))
// schema.KeyField = keyfield;
var tbName = tableName.Trim('[', ']');
List <FieldDef> fieldDefs = dbAdmin.getTableDef(tbName).FieldDefs;
for (int i = 0; i < fieldDefs.Count; i++)
{
FieldDef fieldDef = fieldDefs[i];
FieldSchema field = schema.Fields.FindItem(fieldDef.Name);
// field.Id =;
if (field != null)
{
field.IsKey = pks.Contains(field.Id);
field.DisplayName = fieldDef.Alias;//未用
if (string.IsNullOrEmpty(field.Title))
{
field.Title = fieldDef.Alias;
}
if (fieldDef.IsIdentity)
{
field.IsAutoInc = true;
}
}
// if (field == null)
// schema.Fields.Add(field);
// if (tmpfs.FindItem(field.Id) == null)
// tmpfs.Add(field);
}
}
//删除不存在的字段
for (int i = schema.Fields.Count - 1; i >= 0; i--)
{
FieldSchema fld = schema.Fields[i];
FieldSchema newFld = tmpfs.FindItem(fld.Id);
if (newFld == null)
{
schema.Fields.Remove(fld);
}
}
}
finally
{
con.Close();
}
}
public static void modifySP(string connName, string spName, string script)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.modifyProc(spName, script);
}
public static Dictionary <string, string> getFieldTypes(string connName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
return(da.getFieldTypeList());
}
/// <summary>
/// 删除约束
/// </summary>
/// <param name="connName">连接名</param>
/// <param name="tableName">表名</param>
/// <param name="ctrName">约束名</param>
public static void deleteConstraint(string connName, string tableName, string ctrName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.deleteConstraint(tableName, ctrName);
}
public static void deleteTrigger(string connName, string triggerName)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.deleteTrigger(triggerName);
}