Exemplo n.º 1
0
        internal void FindForeignKey(string tableName, string tableOwner, string columnName, out string foreignKeyTableName, out string foreignKeyTableOwner, out bool isForeignKey, out string foreignKeyColumnName)
        {
            isForeignKey         = false;
            foreignKeyTableOwner = string.Empty;
            foreignKeyTableName  = string.Empty;
            foreignKeyColumnName = string.Empty;
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.Append("SELECT KCU2.TABLE_NAME AS UNIQUE_TABLE_NAME,  KCU2.TABLE_SCHEMA AS UNIQUE_TABLE_SCHEMA, KCU2.COLUMN_NAME AS UNIQUE_COLUMN_NAME ");
            stringBuilder.Append("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ");
            stringBuilder.Append("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ");
            stringBuilder.Append("ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  ");
            stringBuilder.Append("AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA ");
            stringBuilder.Append("AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME ");
            stringBuilder.Append("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ");
            stringBuilder.Append("ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG ");
            stringBuilder.Append("AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA ");
            stringBuilder.Append("AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME ");
            stringBuilder.Append("AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION ");
            stringBuilder.Append("Where KCU1.TABLE_NAME = '" + tableName.Replace("'", "''") + "' ");
            stringBuilder.Append("And KCU1.TABLE_SCHEMA = '" + tableOwner.Replace("'", "''") + "' ");
            stringBuilder.Append("And KCU1.COLUMN_NAME = '" + columnName.Replace("'", "''") + "' ");
            SqlCommand command = Dbase.Command(stringBuilder.ToString());
            DataSet    dataSet = this.CreateDataSet(command);

            if (dataSet.Tables[0].Rows.Count > 0)
            {
                isForeignKey         = true;
                foreignKeyTableOwner = dataSet.Tables[0].Rows[0]["UNIQUE_TABLE_SCHEMA"].ToString().Trim();
                foreignKeyTableName  = dataSet.Tables[0].Rows[0]["UNIQUE_TABLE_NAME"].ToString().Trim();
                foreignKeyColumnName = dataSet.Tables[0].Rows[0]["UNIQUE_COLUMN_NAME"].ToString().Trim();
            }
            command.Dispose();
            dataSet.Dispose();
        }
Exemplo n.º 2
0
        internal DataSet GetAllForeignKeysByTable(string tableName, string tableOwner)
        {
            SqlCommand command = Dbase.Command("sp_fkeys @fktable_owner = [" + tableOwner + "], @fktable_name = [" + tableName + "]");
            DataSet    dataSet = this.CreateDataSet(command);

            command.Dispose();
            return(dataSet);
        }
Exemplo n.º 3
0
        internal int GetPrimaryKeyCount(string tableName, string tableOwner)
        {
            SqlCommand command = Dbase.Command("sp_pkeys @table_owner = [" + tableOwner + "], @table_name = [" + tableName + "]");
            DataSet    dataSet = this.CreateDataSet(command);
            int        count   = dataSet.Tables[0].Rows.Count;

            command.Dispose();
            dataSet.Dispose();
            return(count);
        }
Exemplo n.º 4
0
        internal bool IsForeignKey(string tableName, string columnName)
        {
            bool       flag    = false;
            SqlCommand command = Dbase.Command("SELECT KCU1.COLUMN_NAME AS FK_COLUMN_NAME " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC " + "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 " + "ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  " + "AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA " + "AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME " + "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 " + "ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG " + "AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA " + "AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME " + "AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION " + "Where KCU1.TABLE_NAME = '" + tableName + "' " + "And KCU1.COLUMN_NAME = '" + columnName + "' ");
            DataSet    dataSet = this.CreateDataSet(command);

            if (dataSet.Tables[0].Rows.Count > 0)
            {
                flag = true;
            }
            command.Dispose();
            dataSet.Dispose();
            return(flag);
        }
Exemplo n.º 5
0
 internal StoredProcedure(Table table, string apiNameDirectory, string connectionString, bool isCreateStoredProcInDbase, int spPrefixSuffixIndex, string storedProcPrefix, string storedProcSuffix, DatabaseObjectToGenerateFrom generateFrom, string storedProcedureErrorFilePath, bool isSqlVersion2012OrHigher = false)
 {
     this._table                        = table;
     this._apiNameDirectory             = apiNameDirectory + MyConstants.DirectoryDynamicSQL;
     this._path                         = apiNameDirectory;
     this._connectionString             = connectionString;
     this._dbase                        = new Dbase(connectionString, apiNameDirectory);
     this._isCreateStoredProcInDbase    = isCreateStoredProcInDbase;
     this._spPrefixSuffixIndex          = spPrefixSuffixIndex;
     this._storedProcPrefix             = storedProcPrefix;
     this._storedProcSuffix             = storedProcSuffix;
     this._generateFrom                 = generateFrom;
     this._storedProcedureErrorFilePath = storedProcedureErrorFilePath;
     this._isSqlVersion2012OrHigher     = isSqlVersion2012OrHigher;
     this.Generate();
 }
Exemplo n.º 6
0
        internal bool IsTableHaveXmlDataType(string tableName, string tableOwner)
        {
            bool       flag    = false;
            SqlCommand command = Dbase.Command("Select * From [" + tableOwner + "].[" + tableName + "]");
            DataSet    dataSet = this.CreateDataSet(command);

            foreach (DataColumn column in dataSet.Tables[0].Columns)
            {
                if (column.DataType.ToString().ToLower(CultureInfo.CurrentCulture) != "xml")
                {
                    flag = true;
                    break;
                }
            }
            command.Dispose();
            dataSet.Dispose();
            return(flag);
        }
Exemplo n.º 7
0
        internal void CreateStoredProcedure(string sql, string storedProcedureName, string sprocErrorFilePath)
        {
            SqlCommand    sqlCommand1   = Dbase.Command(sql);
            SqlCommand    sqlCommand2   = Dbase.Command("drop procedure " + storedProcedureName);
            SqlConnection sqlConnection = this.Connect();

            sqlCommand2.Connection = sqlConnection;
            sqlCommand1.Connection = sqlConnection;
            try
            {
                sqlCommand2.ExecuteNonQuery();
            }
            catch
            {
            }
            try
            {
                sqlCommand1.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    if (!File.Exists(sprocErrorFilePath))
                    {
                        using (new StreamWriter(sprocErrorFilePath))
                                                                      #pragma warning disable CS0642 // Possible mistaken empty statement
                            ;
#pragma warning restore CS0642                                                                       // Possible mistaken empty statement
                    }
                    StreamWriter streamWriter = File.AppendText(sprocErrorFilePath);
                    streamWriter.Write(sql);
                    streamWriter.WriteLine("");
                    streamWriter.WriteLine("");
                    streamWriter.Close();
                    streamWriter.Dispose();
                }
                catch
                {
                }
            }
            sqlCommand1.Dispose();
            sqlConnection.Dispose();
        }
Exemplo n.º 8
0
        internal void CreateTable(string sql, string tablenName)
        {
            SqlCommand sqlCommand1 = Dbase.Command(sql);
            SqlCommand sqlCommand2 = Dbase.Command("IF (EXISTS (SELECT * " +
                                                   "FROM INFORMATION_SCHEMA.TABLES " +
                                                   "WHERE TABLE_SCHEMA = 'dbo' " +
                                                   "AND  TABLE_NAME = '" + tablenName + "'))" +
                                                   "BEGIN " +
                                                   "    SELECT 'Exist'" +
                                                   "End " +
                                                   "ELSE " +
                                                   "BEGIN " +
                                                   "    SELECT 'Not Exist'" +
                                                   "END");

            SqlConnection sqlConnection = this.Connect();

            sqlCommand2.Connection = sqlConnection;
            sqlCommand1.Connection = sqlConnection;
            try
            {
                SqlDataReader dr = sqlCommand2.ExecuteReader();
                if (dr != null)
                {
                    while (dr.Read())
                    {
                        if (dr[0].ToString() == "Exist")
                        {
                            return;
                        }
                    }
                }
                dr.Close();
                sqlCommand1.ExecuteNonQuery();
            }
            catch (Exception) { }
            finally
            {
                sqlCommand2.Dispose();
                sqlCommand1.Dispose();
                sqlConnection.Dispose();
            }
        }
Exemplo n.º 9
0
        internal string GetForeignKey(string tableName, string tableOwner)
        {
            SqlCommand command = Dbase.Command("sp_fkeys @fktable_owner = [" + tableOwner + "], @fktable_name = [" + tableName + "]");
            DataSet    dataSet = this.CreateDataSet(command);
            string     str     = "";
            int        num     = 0;

            foreach (DataRow row in (InternalDataCollectionBase)dataSet.Tables[0].Rows)
            {
                ++num;
                str += row["fkcolumn_name"].ToString();
                if (num < dataSet.Tables[0].Rows.Count)
                {
                    str += ",";
                }
            }
            command.Dispose();
            dataSet.Dispose();
            return(str);
        }
Exemplo n.º 10
0
        internal Columns(string tableName, string tableOwner, Language language, string connectionString, string path, bool isUseJQueryValidation, string nameSpace)
        {
            this._dbase = new Dbase(connectionString, path);
            this._path  = path;
            this._isUseJQueryValidation = isUseJQueryValidation;
            this._nameSpace             = nameSpace;
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.Append("Exec sp_columns @table_owner = '" + tableOwner + "',  @table_name = '" + tableName + "' ");
            stringBuilder.Append("Exec sp_pkeys @table_owner = '" + tableOwner + "',  @table_name = '" + tableName + "' ");
            stringBuilder.Append("Exec sp_fkeys @fktable_owner = '" + tableOwner + "',  @fktable_name = '" + tableName + "' ");
            stringBuilder.Append("Select Top 1 * From [" + tableOwner + "].[" + tableName + "] ");
            stringBuilder.Append("select isc.column_name, isc.data_type, isc.datetime_precision, isc.numeric_precision, isc.numeric_precision_radix, isc.column_default, isc.numeric_scale, isc.character_maximum_length, ordinal_position, ");
            stringBuilder.Append("(select top 1 name from sys.computed_columns where name = isc.column_name and object_id in (select id from sysobjects where name = '" + tableName + "')) as computed_formula, ");
            stringBuilder.Append("(select top 1 value From sys.extended_properties where name = 'MS_Description' and class_desc = 'OBJECT_OR_COLUMN' and minor_id = ordinal_position and major_id in (select id from sysobjects where name = '" + tableName + "')) as column_description, ");
            stringBuilder.Append("isc.domain_name as user_defined_type ");
            stringBuilder.Append("From information_schema.columns isc ");
            stringBuilder.Append("where table_schema = '" + tableOwner + "' AND table_name = '" + tableName + "' ");
            stringBuilder.Append("select [name], column_id, [precision], scale, is_nullable, is_identity, is_computed ");
            stringBuilder.Append("from sys.columns where [object_id] = object_id('" + tableOwner + "." + tableName + "') ");
            DataSet dataSet1 = this._dbase.GetDataSet(stringBuilder.ToString(), true);

            if (dataSet1 == null)
            {
                return;
            }
            int       index1 = 0;
            DataTable table1 = dataSet1.Tables[0];
            DataTable table2 = dataSet1.Tables[1];
            DataTable table3 = dataSet1.Tables[2];
            DataTable table4 = dataSet1.Tables[3];
            DataTable table5 = dataSet1.Tables[4];
            DataTable table6 = dataSet1.Tables[5];

            foreach (DataRow row1 in (InternalDataCollectionBase)table1.Rows)
            {
                DataRow row2   = table5.Rows[index1];
                DataRow row3   = table6.Rows[index1];
                Column  column = new Column(table1, table2, table3, table4, row1, row2, row3, connectionString, language, this._path, this._isUseJQueryValidation, this._nameSpace);
                if (!string.IsNullOrEmpty(column.Name) && column.SQLDataType != SQLType.binary && (column.SQLDataType != SQLType.image && column.SQLDataType != SQLType.varbinary) && (column.SQLDataType != SQLType.varbinarymax && column.SQLDataType != SQLType.timestamp && (column.SQLDataType != SQLType.geography && column.SQLDataType != SQLType.geometry)) && (column.SQLDataType != SQLType.hierarchyid && column.SQLDataType != SQLType.sql_variant))
                {
                    DataSet dataSet2 = this._dbase.GetDataSet("select ReferencingTableName = quotename(object_name(pt.parent_object_id)), ReferencingColumnName = quotename(pc.name) from sys.foreign_key_columns as pt inner join sys.columns as pc on pt.parent_object_id = pc.[object_id] AND pt.parent_column_id = pc.column_id inner join sys.columns as rc on pt.referenced_column_id = rc.column_id AND pt.referenced_object_id = rc.[object_id] where quotename(object_schema_name(pt.referenced_object_id)) = '[" + tableOwner + "]' and quotename(object_name(pt.referenced_object_id)) = '[" + tableName + "]' ", true);
                    if (dataSet2 != null && dataSet2.Tables.Count > 0)
                    {
                        DataTable table7 = dataSet2.Tables[0];
                        if (table7 != null && table7.Rows.Count > 0)
                        {
                            column.ReferencingTableName  = new List <string>();
                            column.ReferencingColumnName = new List <string>();
                            for (int index2 = 0; index2 < table7.Rows.Count; ++index2)
                            {
                                column.ReferencingTableName.Add(Functions.ReplaceNoneAlphaNumericWithUnderscore(Functions.ConvertToPascal(table7.Rows[index2]["ReferencingTableName"].ToString().Trim().Replace("[", "").Replace("]", ""))));
                                column.ReferencingColumnName.Add(Functions.ReplaceNoneAlphaNumericWithUnderscore(Functions.ConvertToPascal(table7.Rows[index2]["ReferencingColumnName"].ToString().Trim().Replace("[", "").Replace("]", ""))));
                            }
                        }
                    }
                    DataSet dataSet3 = this._dbase.GetDataSet("select ind.name IndexName from sys.indexes ind inner join sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id inner join sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id inner join sys.tables t ON ind.object_id = t.object_id where ind.is_primary_key = 0 AND t.is_ms_shipped = 0 AND t.name = '" + column.TableNameOriginal + "' AND col.name = '" + column.NameOriginal + "' ", true);
                    if (dataSet1 != null && dataSet3.Tables.Count > 0)
                    {
                        DataTable table7 = dataSet3.Tables[0];
                        if (table7.Rows.Count > 0)
                        {
                            List <string> stringList = new List <string>();
                            foreach (DataRow row4 in (InternalDataCollectionBase)table7.Rows)
                            {
                                stringList.Add(row4["IndexName"].ToString());
                            }
                            column.IndexName = stringList;
                        }
                    }
                    this.Add(column);
                }
                ++index1;
            }
            dataSet1.Dispose();
            table1.Dispose();
            table2.Dispose();
            table3.Dispose();
            table4.Dispose();
            table5.Dispose();
            table6.Dispose();
        }