Example #1
0
 private void LoadExtendedPropertyData()
 {
     if (ExtendedProperties == null)
     {
         ExtendedProperties = new DataTable();
         var cmdExtProps = new SqlCommand(EXTENDED_PROPERTIES_ALL);
         ExtendedProperties.Load(cmdExtProps.GetReader());
     }
 }
Example #2
0
        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);
                    }
                }
            }
        }
Example #3
0
 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;
 }
Example #4
0
 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;
 }
Example #5
0
 private IDataReader GetScalarFunctionRdr()
 {
     var cmd = new SqlCommand("/* GetScalarFunctionNameList() */ " + SCALER_FUNCTION_SQL);
     return cmd.GetReader();
 }
Example #6
0
 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;
 }
Example #7
0
        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();
        }