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