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