public static DataSourceSchema buildTableSchema(string connName, string tableName, string dsId)
{
DataSourceSchema ret = DataSourceSchemaBuilder.BuildTableSchema(connName, tableName);
DataSourceSchemaContainer.Instance().AddItem(dsId, ret);
return(ret);
}
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);
}
public static Dictionary <string, string> getDsNames()
{
Dictionary <string, string> ret = new Dictionary <string, string>();
string[] dataSources = container.GetSchemaIds();
for (int i = 0; i < dataSources.Count(); i++)
{
string dsId = dataSources[i];
DataSourceSchema ts = container.GetItem(dsId);
string text = ts.Title;
if (string.IsNullOrEmpty(text))
{
text = dsId;
}
ret.Add(dsId, text);
}
//ret
Dictionary <string, string> tables = DataExplore.getTables();
foreach (string key in tables.Keys)
{
ret.Add(key, tables[key]);
}
return(ret);
}
/// <summary>
/// 根据数据源的Select命令和列配置等，创建数据源配置的其他SQL命令
/// </summary>
/// <param name="schema"></param>
public static void BuildSchemaCommand(DataSourceSchema schema)
{
XSqlBuilder cmdBuild = new XSqlBuilder(schema);
try
{
// schema.UpdateCommand = cmdBuild.GetUpdateCommand(true).CommandText;
schema.UpdateCommand.CommandText = cmdBuild.GetUpdateCommand();
}
catch
{
schema.UpdateCommand.CommandText = "";
}
try
{
//schema.InsertCommand = cmdBuild.GetInsertCommand(true).CommandText;
schema.InsertCommand.CommandText = cmdBuild.GetInsertCommand();
}
catch (Exception)
{
schema.InsertCommand.CommandText = "";
}
try
{
//schema.DeleteCommand = cmdBuild.GetDeleteCommand(true).CommandText;
schema.DeleteCommand.CommandText = cmdBuild.GetDeleteCommand();
}
catch (Exception)
{
schema.DeleteCommand.CommandText = "";
}
}
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 static SubTableSchema getSubTableSchema(string subDsName, DataSourceSchema dsSchema)
{
foreach (SubTableSchema ss in dsSchema.SubTables)
{
if (ss.Name.Equals(subDsName))
{
return(ss);
}
}
throw new XException(string.Format(Lang.SubTableNoDefine, subDsName, dsSchema.Id));
}
public static ListDataRow readRow(DataTable tb, DataSourceSchema dsSchema, DataRow dRow)
{
List <DataListColumn> fColumns = EUGridUtils.getColumns(dsSchema.Fields);
ListDataRow row = new ListDataRow();
foreach (DataListColumn dcol in fColumns)
// foreach (FieldSchema fldSchema in _schema.Fields)
//foreach (DataColumn col in tb.Columns)
{
string fName = dcol.field;
if (fName.StartsWith(XSqlBuilder.OLD_VERSION_PIX))
{
fName = fName.Remove(0, XSqlBuilder.OLD_VERSION_PIX.Length);
}
if (!dsSchema.Fields.ContainsId(fName))
{
row.Add("ck", "false");
continue;
}
FieldSchema fldSchema = dsSchema.Fields.GetItem(fName);
if (fldSchema.DataType == DbType.Binary)
{
continue;
}
DataColumn col = tb.Columns[fName];
// if (!string.IsNullOrEmpty(fldSchema.Alias))
// fName = fldSchema.Alias;
//if (col == null)
// throw new Exception(string.Format(Lang.FieldNotFind, fName));
string value = "";
if (col != null)
{
value = GetCellString(fldSchema, col, dRow);
}
row.Add(dcol.field, value);
// if (tb.PrimaryKey.Contains(col))
// {
// row.Add(dcol, value);
//row.Pk.Add(fName, value);
// }
}
return(row);
}
private static DataSourceSchema CreateTableSchema(string tableId, string connectionName, string selectSql)
{
DataSourceSchema schema = new DataSourceSchema();
schema.Id = tableId;
schema.SelectCommand.CommandText = selectSql;
schema.SelectCommand.CommandType = (int)CommandType.Text;
schema.ConnectionName = connectionName;
schema.PageSize = DEF_PAGE_SIZE;
BuildFields(schema);
BuildSchemaCommand(schema);
return(schema);
}
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);
}
private void updateSubTables(ListDataRow row, List <SubTable> subTables)
{
if (subTables == null || subTables.Count < 1)
{
return;
}
for (int c = 0; c < subTables.Count; c++)
{
string subTable = subTables[c].Name;
DataSourceSchema ds = DataSourceSchemaContainer.Instance().GetItem(subTable);
if (ds.SelectCommand.CommandType != CommandType.TableDirect)
{
throw new XException(string.Format(Lang.SubTableSelCommandTypeOnlyIsTable, subTable));
}
SubTableSchema sds = DataSourceComm.getSubTableSchema(subTable, _schema);
Dictionary <string, string> parametes = new Dictionary <string, string>();
for (int i = 0; i < sds.Fks.Count; i++)
{
string fk = sds.Fks[i];
parametes.Add("@" + fk, row[_schema.PrimaryKeys[i]]);
}
DsAdapter dsa = new DsAdapterCustomer(ds);
List <ListDataRow> subRows = subTables[c].Rows;
for (int i = 0; i < subRows.Count; i++)
{
ListDataRow subRow = subRows[i];
if (DataSourceComm.isNewRow(subRow))
{
dsa.insert(subRow, parametes);
}
else
{
dsa.update(subRow, parametes);
}
}
}
}
public static string getFields(DataSourceSchema schema)
{
if (schema.Fields == null || schema.Fields.Count < 1)
{
return("*");
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < schema.Fields.Count; i++)
{
FieldSchema fs = schema.Fields[i];
if (i > 0)
{
sb.Append(',');
}
sb.Append(fs.Id);
}
return(sb.ToString());
}
public static DataSourceSchema BuildTableSchema(string connName, string tableName)
{
string defCon = ConnectionAdmin.getDefaultConnName();
if (connName.Equals(defCon, StringComparison.OrdinalIgnoreCase))
{
connName = null;
}
string sql = "Select top " + DEF_PAGE_SIZE.ToString() + " * From [" + tableName + "]";
DataSourceSchema ret = CreateTableSchema(tableName, connName, sql);
string dsId = tableName;
if (!string.IsNullOrEmpty(connName))
{
dsId = connName + "." + dsId;
}
DataSourceSchemaContainer.Instance().AddItem(dsId, ret);
return(ret);
}
private void loadSchema()
{
_ds = null;
_fieldColumns = null;
_pagination = new PaginationInfo();
if (DataSourceSchemaContainer.Instance().Contains(name))
{
_schema = DataSourceSchemaContainer.Instance().GetItem(name);
_schema.ChangedEvent += schemaChanged;
if (_schema.SelectCommand == null)
{
throw new XException(Lang.DataSourceNotSelectCommand);
}
_isSourceTable = false; //_schema.SelectCommand.CommandType == CommandType.TableDirect;
}
else
{
_isSourceTable = true;
_schema = DataSourceSchemaBuilder.BuildTableSchema(_name);
}
}
private static void loadSubNodes(List <TreeNode> subNodes, string path, ref int id)
{
string[] folders = container.GetSchemaFolders(path);
for (int i = 0; i < folders.Length; i++)
{
string subFolder = folders[i];
TreeNode node = new TreeNode();
node.label = subFolder;
node.path = path;
node.nodeType = (int)DsExploreNodeType.folder;
node.id = id + "";
node.text = subFolder;
subNodes.Add(node);
id++;
loadSubNodes(node.children, path + "\\" + subFolder, ref id);
}
string[] dataSources = container.GetIDsByFolder(path);
for (int i = 0; i < dataSources.Count(); i++)
{
string dsId = dataSources[i];
DataSourceSchema ts = container.GetItem(path + "\\" + dsId);
TreeNode node = new TreeNode();
node.text = ts.Title + "(" + dsId + ")";
node.label = node.text;
node.title = node.text;
if (string.IsNullOrEmpty(node.label))
{
node.label = dsId;
}
node.name = node.text;
node.id = dsId;
node.path = path;
node.nodeType = (int)DsExploreNodeType.dataSource;
//node.id = id + "";
subNodes.Add(node);
id++;
}
}
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();
}
}
/// <summary>
/// //用物理数据库中的字段及表信息，重新更新XTableSchema信息
/// </summary>
/// <param name="tableId"></param>
/// <param name="schema"></param>
public static void RefreshSchemaFields(string tableId, DataSourceSchema schema)
{
BuildFields(schema);
DataSourceSchemaContainer.Instance().UpdateItem(tableId, schema);
}
internal static DataSet getSchemaDataSet(DatabaseAdmin dba, DataSourceSchema _schema, Dictionary <string, string> _queryParams, int p, int p_2)
{
throw new NotImplementedException();
}
public XSqlBuilder(DataSourceSchema schema)
{
this.schema = schema;
}
public DsAdapterCustomer(DataSourceSchema schema)
{
this._schema = schema;
dbAdmin = DatabaseAdmin.getInstance(schema.ConnectionName);
}
public static void updateDsSchema(string dsId, DataSourceSchema dss)
{
container.UpdateItem(dsId, dss);
}
