public static bool TypeExists(SqlConnection connection, SqlTransaction transaction, string quotedTypeName)
{
bool typeExist;
var columnName = ParserName.Parse(quotedTypeName).ToString();
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@typeName", columnName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
typeExist = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(typeExist);
}
public static bool TableExists(SqlConnection connection, SqlTransaction transaction, string quotedTableName)
{
bool tableExist;
var tableName = ParserName.Parse(quotedTableName).ToString();
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0";
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tableName
};
dbCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName))
};
dbCommand.Parameters.Add(sqlParameter);
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
tableExist = (int)dbCommand.ExecuteScalar() != 0;
}
return(tableExist);
}
public static string DropTableIfExistsScriptText(string quotedTableName)
{
var tableName = ParserName.Parse(quotedTableName).ToString();
object[] escapedString = new object[] { tableName, SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName)), quotedTableName };
return(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'{0}' AND s.name = N'{1}') DROP TABLE {2}\n", escapedString));
}
public static bool ProcedureExists(SqlConnection connection, SqlTransaction transaction, string quotedProcedureName)
{
bool flag;
var procedureName = ParserName.Parse(quotedProcedureName).ToString();
using (var sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@procName", procedureName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedProcedureName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
flag = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(flag);
}
public static bool TriggerExists(SqlConnection connection, SqlTransaction transaction, string quotedTriggerName)
{
bool triggerExist;
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new SqlCommand("IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
triggerExist = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(triggerExist);
}
public static void DropTypeIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTypeName)
{
var typeName = ParserName.Parse(quotedTypeName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) DROP TYPE {0}", quotedTypeName), connection))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@typeName", typeName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
sqlCommand.ExecuteNonQuery();
if (!alreadyOpened)
{
connection.Close();
}
}
}
public static async Task <bool> TypeExistsAsync(SqlConnection connection, SqlTransaction transaction, string quotedTypeName)
{
bool typeExist;
var columnName = ParserName.Parse(quotedTypeName).ToString();
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@typeName", columnName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
var result = await sqlCommand.ExecuteScalarAsync().ConfigureAwait(false);
typeExist = (int)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(typeExist);
}
public static async Task DropTriggerIfExistsAsync(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTriggerName)
{
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) DROP TRIGGER {0}", quotedTriggerName), connection))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
}
public static async Task DropProcedureIfExistsAsync(SqlConnection connection, SqlTransaction transaction, int commandTimout, string quotedProcedureName)
{
var procName = ParserName.Parse(quotedProcedureName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) DROP PROCEDURE {0}", quotedProcedureName), connection))
{
sqlCommand.CommandTimeout = commandTimout;
sqlCommand.Parameters.AddWithValue("@procName", procName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedProcedureName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
}
public static void DropTriggerIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTriggerName)
{
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) DROP TRIGGER {0}", quotedTriggerName), connection, transaction))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
sqlCommand.ExecuteNonQuery();
}
}
public static void DropProcedureIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimout, string quotedProcedureName)
{
var procName = ParserName.Parse(quotedProcedureName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) DROP PROCEDURE {0}", quotedProcedureName), connection, transaction))
{
sqlCommand.CommandTimeout = commandTimout;
sqlCommand.Parameters.AddWithValue("@procName", procName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedProcedureName)));
sqlCommand.ExecuteNonQuery();
}
}
public static void DropTypeIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTypeName)
{
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTypeName);
using (SqlCommand sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) DROP TYPE {0}", quotedTypeName), connection, transaction))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@typeName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
sqlCommand.ExecuteNonQuery();
}
}
public static bool ProcedureExists(SqlConnection connection, SqlTransaction transaction, string quotedProcedureName)
{
bool flag;
ObjectNameParser objectNameParser = new ObjectNameParser(quotedProcedureName);
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@procName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
flag = (int)sqlCommand.ExecuteScalar() != 0;
}
return(flag);
}
public static async Task <bool> TableExistsAsync(SqlConnection connection, SqlTransaction transaction, string quotedTableName)
{
bool tableExist;
var tableName = ParserName.Parse(quotedTableName).ToString();
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0";
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tableName
};
dbCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName))
};
dbCommand.Parameters.Add(sqlParameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false);
tableExist = (int)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(tableExist);
}
public static bool TypeExists(SqlConnection connection, SqlTransaction transaction, string quotedTypeName)
{
bool typeExist;
ObjectNameParser objectNameParser = new ObjectNameParser(quotedTypeName);
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@typeName", objectNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(objectNameParser));
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
typeExist = (int)sqlCommand.ExecuteScalar() != 0;
}
return(typeExist);
}
