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);
}
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();
}
}
internal DataSet getSourceDataSet(string where, string orderBy, PaginationInfo pi)
{
string connName = _schema.ConnectionName;
string tableName = _schema.TableName;
Database db = dbAdmin.Database;
List <string> pks = dbAdmin.GetPrimaryKeys(tableName);
DbCommand cmd = null;
if (!dbAdmin.ExistsSp(DataSourceConst.PaginationSpName))
{
dbAdmin.modifyProc(DataSourceConst.PaginationSpName, DataSourceConst.PaginationSpText);
}
if (pks.Count() == 1)
{
pi.isStoreProcessPagination = true;
cmd = db.GetStoredProcCommand(DataSourceConst.PaginationSpName);
cmd.CommandType = CommandType.StoredProcedure;
db.AddInParameter(cmd, "@TableName", DbType.String, tableName);
db.AddInParameter(cmd, "@PrimaryKey", DbType.String, pks[0]);
db.AddInParameter(cmd, "@PageNo", DbType.Int32, pi.page);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pi.pageSize);
db.AddInParameter(cmd, "@Where", DbType.String, where);
db.AddOutParameter(cmd, "@PageCount", DbType.Int32, sizeof(Int32));
db.AddOutParameter(cmd, "@Total", DbType.Int32, sizeof(Int32));
}
else
{
pi.isStoreProcessPagination = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("Select * From ");
sbSql.Append(tableName);
if (!string.IsNullOrEmpty(where))
{
sbSql.Append(" Where ");
sbSql.Append(where);
}
if (!string.IsNullOrEmpty(orderBy))
{
sbSql.Append(" Order By ");
sbSql.Append(orderBy);
}
cmd = db.GetSqlStringCommand(sbSql.ToString());
}
DataSet ds = db.ExecuteDataSet(cmd);
TableDef tableDef = dbAdmin.getTableDef(tableName);
DataTable table = ds.Tables[0];
if (pks.Count > 0)
{
DataColumn[] primaryKey = new DataColumn[2];
for (int i = 0; i < pks.Count; i++)
{
primaryKey[i] = table.Columns[pks[i]];
}
table.PrimaryKey = primaryKey;
}
foreach (FieldDef fieldDef in tableDef.FieldDefs)
{
DataColumn col = table.Columns[fieldDef.Name];
if (!string.IsNullOrEmpty(fieldDef.Alias))
{
col.Caption = fieldDef.Alias;
}
if (fieldDef.IsIdentity)
{
col.AutoIncrement = true;
col.ReadOnly = true;
}
}
if (pi.isStoreProcessPagination)
{
pi.pageCount = (int)cmd.Parameters["@PageCount"].Value;
pi.total = (int)cmd.Parameters["@Total"].Value;
}
else
{
pi.total = table.Rows.Count;
pi.pageCount = pi.total / pi.pageSize;
if (pi.total % pi.pageSize != 0)
{
pi.pageCount++;
}
}
return(ds);
}
internal static DataSet getSourceDataSet(string dsName, string where, string orderBy, PaginationInfo pi)
{
if (string.IsNullOrEmpty(dsName))
{
throw new XException(Lang.DataSourceNameIsNull);
}
string connName = "";
string tableName = dsName;
string[] namePath = dsName.Split('.');
if (namePath.Length > 1)
{
connName = namePath[0];
tableName = namePath[1];
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
Database db = dba.Database;
List <string> pks = dba.GetPrimaryKeys(tableName);
DbCommand cmd = null;
if (!dba.ExistsSp(DataSourceConst.PaginationSpName))
{
dba.modifyProc(DataSourceConst.PaginationSpName, DataSourceConst.PaginationSpText);
}
if (pks.Count() == 1)
{
pi.isStoreProcessPagination = true;
cmd = db.GetStoredProcCommand(DataSourceConst.PaginationSpName);
cmd.CommandType = CommandType.StoredProcedure;
db.AddInParameter(cmd, "@TableName", DbType.String, tableName);
db.AddInParameter(cmd, "@PrimaryKey", DbType.String, pks[0]);
db.AddInParameter(cmd, "@PageNo", DbType.Int32, pi.page);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pi.pageSize);
db.AddInParameter(cmd, "@Where", DbType.String, where);
db.AddOutParameter(cmd, "@PageCount", DbType.Int32, sizeof(Int32));
db.AddOutParameter(cmd, "@Total", DbType.Int32, sizeof(Int32));
}
else
{
pi.isStoreProcessPagination = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("Select * From ");
sbSql.Append(tableName);
if (!string.IsNullOrEmpty(where))
{
sbSql.Append(" Where ");
sbSql.Append(where);
}
if (!string.IsNullOrEmpty(orderBy))
{
sbSql.Append(" Order By ");
sbSql.Append(orderBy);
}
cmd = db.GetSqlStringCommand(sbSql.ToString());
}
DataSet ds = db.ExecuteDataSet(cmd);
TableDef tableDef = dba.getTableDef(tableName);
DataTable table = ds.Tables[0];
foreach (FieldDef fieldDef in tableDef.FieldDefs)
{
if (string.IsNullOrEmpty(fieldDef.Alias))
{
continue;
}
table.Columns[fieldDef.Name].Caption = fieldDef.Alias;
}
if (pi.isStoreProcessPagination)
{
pi.pageCount = (int)cmd.Parameters["@PageCount"].Value;
pi.total = (int)cmd.Parameters["@Total"].Value;
}
else
{
pi.total = table.Rows.Count;
pi.pageCount = pi.total / pi.pageSize;
if (pi.total % pi.pageSize != 0)
{
pi.pageCount++;
}
}
return(ds);
}
