private void LoadExtendedPropertyData() { if (ExtendedProperties == null) { ExtendedProperties = new DataTable(); var cmdExtProps = new SqlCommand(EXTENDED_PROPERTIES_ALL); ExtendedProperties.Load(cmdExtProps.GetReader()); } }
public void FillTableSchema(Table tbl, TableType tableType) { TableColumnCollection columns = new TableColumnCollection(); DataSet dsColumns; tbl.ForeignKeys = new ForeignKeyCollection(); if (tableType == TableType.Function) { if (dsColumns2.Tables[Name] == null) { var cmdColumns = new SqlCommand(ROUTINE_COLUMN_SQL_ALL); var dt = new DataTable(Name); dt.Load(cmdColumns.GetReader()); dsColumns2.Tables.Add(dt); } dsColumns = dsColumns2; } else { if (dsColumns1.Tables[Name] == null) { var cmdColumns = new SqlCommand(TABLE_COLUMN_SQL_ALL); var dt = new DataTable(Name); dt.Load(cmdColumns.GetReader()); dsColumns1.Tables.Add(dt); } dsColumns = dsColumns1; } DataRow[] drColumns = dsColumns.Tables[Name].Select("TableName ='" + tbl.Name + "'", "OrdinalPosition ASC"); for (int i = 0; i < drColumns.Length; i++) { TableColumn column = new TableColumn(tbl); column.ColumnName = drColumns[i][SqlSchemaVariable.COLUMN_NAME].ToString(); column.NativeDataType = drColumns[i][SqlSchemaVariable.DATA_TYPE].ToString(); if (column.NativeDataType == "numeric") column.NativeDataType = string.Format("Decimal({0},{1})", drColumns[i]["precision"], drColumns[i]["scale"]); column.DataType = GetDbType(column.NativeDataType); if (drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT] != DBNull.Value) { string defaultSetting = drColumns[i][SqlSchemaVariable.COLUMN_DEFAULT].ToString().Trim(); if (defaultSetting.ToLower().IndexOf("newsequentialid()") > -1) column.DefaultSetting = SqlSchemaVariable.DEFAULT; else column.DefaultSetting = defaultSetting; } column.AutoIncrement = Convert.ToBoolean(drColumns[i][SqlSchemaVariable.IS_IDENTITY]); int maxLength; int.TryParse(drColumns[i][SqlSchemaVariable.MAX_LENGTH].ToString(), out maxLength); column.MaxLength = maxLength; if (maxLength > 0) column.NativeDataType += "(" + maxLength + ")"; column.IsNullable = drColumns[i][SqlSchemaVariable.IS_NULLABLE].ToString() == "YES"; bool isComputed = (drColumns[i][SqlSchemaVariable.IS_COMPUTED].ToString() == "1"); column.IsReadOnly = (column.NativeDataType == "timestamp" || isComputed); columns.Add(column); tbl.SchemaName = drColumns[i]["Owner"].ToString(); } tbl.Columns = columns; if (dsIndex.Tables[Name] == null) { var cmdIndex = new SqlCommand(INDEX_SQL_ALL); DataTable dt = new DataTable(Name); dt.Load(cmdIndex.GetReader()); dsIndex.Tables.Add(dt); } DataRow[] drIndexes = dsIndex.Tables[Name].Select("TableName ='" + tbl.Name + "'"); for (int i = 0; i < drIndexes.Length; i++) { string colName = drIndexes[i][SqlSchemaVariable.COLUMN_NAME].ToString(); string constraintType = drIndexes[i][SqlSchemaVariable.CONSTRAINT_TYPE].ToString(); TableColumn column = columns.GetColumn(colName); if (Utility.IsMatch(constraintType, SqlSchemaVariable.PRIMARY_KEY)) column.IsPrimaryKey = true; else if (Utility.IsMatch(constraintType, SqlSchemaVariable.FOREIGN_KEY)) column.IsForeignKey = true; //HACK: Allow second pass naming adjust based on whether a column is keyed column.ColumnName = column.ColumnName; } if (dtFKR == null) { var cmdFKR = new SqlCommand(FOREIGN_KEY_RELATIONSHIPS); dtFKR = new DataTable(); dtFKR.Load(cmdFKR.GetReader()); var cmdFK = new SqlCommand(FOREIGN_KEYS); dtFK = new DataTable(); dtFK.Load(cmdFK.GetReader()); } DataRow[] drfkr; drfkr = dtFKR.Select("PkTable ='" + tbl.Name + "'"); for (int i = 0; i < drfkr.Length; i++) { Relationship rel = new Relationship(); rel.Name = drfkr[i]["RelName"].ToString(); Table fktable = Db.Service.GetSchema(drfkr[i]["FkTable"].ToString()); rel.ClassNameOne = tbl.ClassName; rel.ClassNameMany = fktable.ClassName; string[] a = Regex.Split(rel.Name, "__"); if (a.Length == 2) { rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table rel.PropertyNameOne = a[1]; // named used for foreign key in this table } else { rel.PropertyNameOne = tbl.ClassName; rel.PropertyNameMany = fktable.ClassNamePlural; } DataRow[] drfk = dtFK.Select("PkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'"); for (int ii = 0; ii < drfk.Length; ii++) { Relationship.KeyPair kp = new Relationship.KeyPair(); TableColumn fkcol = fktable.Columns.GetColumn(drfk[ii]["FkColumn"].ToString()); TableColumn pkcol = columns.GetColumn(drfk[ii]["PkColumn"].ToString()); kp.vartype = fkcol.VarType; kp.ForeignKey = fkcol.Name; kp.PrimaryKey = pkcol.Name; rel.KeyPairs.Add(kp); if (ii > 0) { rel.ForeignKey += ","; rel.PrimaryKey += ","; } rel.PrimaryKey += kp.PrimaryKey; rel.ForeignKey += kp.ForeignKey; } tbl.ForeignKeyTables.Add(rel); } drfkr = dtFKR.Select("FkTable ='" + tbl.Name + "'"); for (int i = 0; i < drfkr.Length; i++) { Relationship rel = new Relationship(); rel.Name = drfkr[i]["RelName"].ToString(); Table pktable = Db.Service.GetSchema(drfkr[i]["PkTable"].ToString()); rel.ClassNameOne = pktable.ClassName; rel.ClassNameMany = tbl.ClassName; string[] a = Regex.Split(rel.Name, "__"); if (a.Length == 2) { rel.PropertyNameMany = a[0]; // name used in primary table to fetch many of this table rel.PropertyNameOne = a[1]; // named used for foreign key in this table } else { rel.PropertyNameOne = pktable.ClassName; rel.PropertyNameMany = tbl.ClassNamePlural; } DataRow[] drfk = dtFK.Select("FkTable = '" + tbl.Name + "' and RelName = '" + rel.Name + "'"); for (int ii = 0; ii < drfk.Length; ii++) { Relationship.KeyPair kp = new Relationship.KeyPair(); TableColumn fkcol = columns.GetColumn(drfk[ii]["FkColumn"].ToString()); TableColumn pkcol = pktable.Columns.GetColumn(drfk[ii]["PkColumn"].ToString()); kp.vartype = fkcol.VarType; kp.ForeignKey = fkcol.Name; kp.PrimaryKey = pkcol.Name; rel.KeyPairs.Add(kp); if (ii > 0) { rel.ForeignKey += ","; rel.PrimaryKey += ","; } rel.PrimaryKey += kp.PrimaryKey; rel.ForeignKey += kp.ForeignKey; fkcol.PropertyNameOne = rel.PropertyNameOne; } tbl.ForeignKeys.Add(rel); } if (dsManyToManyCheck.Tables[Name] == null) { var cmdM2M = new SqlCommand(MANY_TO_MANY_CHECK_ALL); var dt = new DataTable(Name); dt.Load(cmdM2M.GetReader()); dsManyToManyCheck.Tables.Add(dt); } DataRow[] drs = dsManyToManyCheck.Tables[Name].Select("PK_Table = '" + tbl.Name + "'"); if (drs.Length > 0) { for (int count = 0; count < drs.Length; count++) { string mapTable = drs[count]["FK_Table"].ToString(); string localKey = drs[count]["FK_Column"].ToString(); if (dsManyToManyMap.Tables[Name] == null) { var cmdM2MMap = new SqlCommand(MANY_TO_MANY_FOREIGN_MAP_ALL); DataTable dt = new DataTable(Name); dt.Load(cmdM2MMap.GetReader()); dsManyToManyMap.Tables.Add(dt); } DataRow[] drMap = dsManyToManyMap.Tables[Name].Select("FK_Table = '" + mapTable + "' AND PK_Table <> '" + tbl.Name + "'"); for (int i = 0; i < drMap.Length; i++) { ManyToManyRelationship m = new ManyToManyRelationship(mapTable); m.ForeignTableName = drMap[i]["PK_Table"].ToString(); m.ForeignPrimaryKey = drMap[i]["PK_Column"].ToString(); m.MapTableLocalTableKeyColumn = localKey; m.MapTableForeignTableKeyColumn = drMap[i]["FK_Column"].ToString(); tbl.ManyToManys.Add(m); } } } }
public string[] GetTableNameList() { if (TableNames == null || !CurrentConnectionStringIsDefault) { var cmd = new SqlCommand("/* GetTableNameList() */ " + TABLE_SQL); var sList = new StringBuilder(); using (IDataReader rdr = cmd.GetReader()) { while (rdr.Read()) { sList.Append(rdr[SqlSchemaVariable.NAME]); sList.Append("|"); } rdr.Close(); } string strList = sList.ToString(); string[] tempTableNames = strList.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); Array.Sort(tempTableNames); if (CurrentConnectionStringIsDefault) { TableNames = tempTableNames; } else { return tempTableNames; } } return TableNames; }
public string[] GetFunctionNameList() { if (FunctionNames == null || !CurrentConnectionStringIsDefault) { var cmd = new SqlCommand("/* GetFunctionNameList() */ " + FUNCTION_SQL); var sList = new StringBuilder(); using (IDataReader rdr = cmd.GetReader()) { while (rdr.Read()) { string functionName = rdr[SqlSchemaVariable.NAME].ToString(); string schemaName = rdr[SqlSchemaVariable.SCHEMA_NAME].ToString(); sList.Append(schemaName + "." + functionName); sList.Append("|"); } rdr.Close(); } string strList = sList.ToString(); string[] tempFunctionNames = strList.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); Array.Sort(tempFunctionNames); if (CurrentConnectionStringIsDefault) FunctionNames = tempFunctionNames; else return tempFunctionNames; } return FunctionNames; }
private IDataReader GetScalarFunctionRdr() { var cmd = new SqlCommand("/* GetScalarFunctionNameList() */ " + SCALER_FUNCTION_SQL); return cmd.GetReader(); }
public List<StoredProcedure> GetSPList() { var cmd = new SqlCommand("/* GetSPList() */ " + SP_SQL); var sps = new List<StoredProcedure>(); using (IDataReader rdr = cmd.GetReader()) { while (rdr.Read()) { var s = rdr.GetString(0); string schema = rdr[SqlSchemaVariable.SCHEMA_NAME].ToString(); var sp = new StoredProcedure(schema, s); if (rdr["ReturnType"] != DBNull.Value) sp.ReturnType = rdr["ReturnType"].ToString(); sps.Add(sp); } rdr.Close(); } return sps; }
public IDataReader GetSPParams(string spName) { if (dtParamSql == null) { var cmdSP = new SqlCommand(SP_PARAM_SQL_ALL); dtParamSql = new DataTable(); dtParamSql.Load(cmdSP.GetReader()); } DataView dv = new DataView(dtParamSql); dv.RowFilter = "SPName = '" + spName + "'"; dv.Sort = "OrdinalPosition"; DataTable dtNew = dv.ToTable(); return dtNew.CreateDataReader(); }