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