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