public static void CreateDatabase(string serverName, string databaseName, bool drop)
{
if (drop)
{
DropDatabaseIfExists(serverName, databaseName);
}
string createDatabaseScript = "IF (SELECT DB_ID('" + databaseName + "')) IS NULL "
+ " CREATE DATABASE " + databaseName;
string connectionString = DbConnectionInfo.GetMsSqlServerConnectionString(serverName, "master");
SimpleDataAccess.ExecuteSql(createDatabaseScript, connectionString);
}
public static void DropDatabase(string serverName, string databaseName)
{
DropUserConnections(serverName, databaseName);
var connectionInfo = new DbConnectionInfo {
DatabaseName = "master", ServerName = serverName
};
string sql = String.Format("DROP DATABASE {0}", databaseName);
SimpleDataAccess.ExecuteSql(sql, DbConnectionInfo.GetMsSqlServerConnectionString(connectionInfo));
//Necessary so as to avoid a connection to the database that just been dropped being being reused from the pool
//Not clearing the pool, leads to your app throwing an exception when it tried to access the dropped database
//from a pooled connection that was no longer open
SqlConnection.ClearAllPools();
}
private static void CreateSeedInsertStoredProc(SqlAdminQueries.DbInstance db)
{
string createInsertSeedProcSql =
string.Format(
@"CREATE PROC [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@seed INT = 0
)
AS
INSERT INTO dbo.tbl_Ids_Identities ( ObjectType, CurrentId )
VALUES ( @objectType, @seed )",
InsertSeedProcName);
SimpleDataAccess.ExecuteSql(createInsertSeedProcSql, db.ConnectionString);
}
public int NextId()
{
string sql = string.Format("EXEC dbo.{0} {1}, {2}",
GetNextIdProcName,
WhereStatement.FormatSqlValue(_objectType),
WhereStatement.FormatSqlValue(1));
DataTable result = SimpleDataAccess.ExecuteDataTable(command => {
command.CommandType = CommandType.Text;
command.CommandText = sql;
},
_connection);
DataRow row = result.AsEnumerable().Single();
return(row.Field <int>(0));
}
private static void CreateSeedUpdateStoredProc(SqlAdminQueries.DbInstance db)
{
string createUpdateSeedProcSql =
string.Format(
@"CREATE PROC [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@seed INT
)
AS
UPDATE dbo.tbl_Ids_Identities SET CurrentId = @seed WHERE ObjectType = @objectType
IF @@ROWCOUNT = 0 BEGIN
RAISERROR('No rows were updated.', 16, 1)
END",
UpdateSeedProcName);
SimpleDataAccess.ExecuteSql(createUpdateSeedProcSql, db.ConnectionString);
}
/// <summary>
/// Can we connect to the specified database
/// </summary>
/// <param name="connectionString">The SQL connection string</param>
/// <param name="databaseName">The server name</param>
/// <returns>true if we can connect to the database</returns>
public static bool DatabaseExists(string connectionString, string databaseName)
{
try
{
DataTable dt = SimpleDataAccess.ExecuteDataTable(cmd => {
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ISNULL(HAS_DBACCESS('" + databaseName + "'),-2) AS ReturnValue";
},
connectionString);
if (dt.Rows.Count == 0)
{
return(false);
}
DataRow dr = dt.Rows[0];
if (dr.ItemArray.GetValue(0).ToString() == "-2")
{
return(false);
}
else if (dr.ItemArray.GetValue(0).ToString() == "0")
{
return(false);
}
else if (dr.ItemArray.GetValue(0).ToString() == "1")
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
}
private static void CreateGetNextIdStoredProc(SqlAdminQueries.DbInstance db)
{
string createGetNextIdProcSql =
string.Format(
@"CREATE PROCEDURE [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@blockSize AS INT = 1
)
AS
SET NOCOUNT ON
-- ensures even if command times out or batch is cancelled tx will rollback and locks released
SET XACT_ABORT ON
DECLARE @isOwnerOfTransaction INT
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state TINYINT
DECLARE @resultId INT
BEGIN TRY
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRAN
SET @isOwnerOfTransaction = 1
END
-- sanitize bad inputs
IF ( ISNULL(@blockSize, 0) = 0 )
BEGIN
SET @blockSize = 1
END
SELECT @resultId = CurrentId + @blockSize
FROM dbo.tbl_Ids_Identities WITH (UPDLOCK)
WHERE ObjectType = @objectType
UPDATE dbo.tbl_Ids_Identities
SET CurrentId = @resultId
WHERE ObjectType = @objectType
SELECT @resultId AS NextId
ExitLabel:
IF @isOwnerOfTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
END
END TRY
BEGIN CATCH
IF @isOwnerOfTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
SELECT @errmsg = error_message(),
@severity = error_severity(),
@state = error_state()
RAISERROR ( @errmsg, @severity, @state )
RETURN -1
END CATCH",
GetNextIdProcName);
SimpleDataAccess.ExecuteSql(createGetNextIdProcSql, db.ConnectionString);
}
