Database operations using c# – Creating a database

In this post i am going to explain how to create a DB using #. So lets get down with the code

This class is the control class through which we are going to call the helper methods. I have started coding directly as it is easier to understand it via running code. If you dont understand it or have problems running it just say the word and will try to add to Git so its easily accessible to download.

 

class Program
	{
		private static ExecuteSql _executeMaster;
 
		private static string _connectionString =
			"Data Source=.;Integrated Security=SSPI;Initial Catalog=testme;Application Name=Manager.Test;Connection Timeout=300;
User ID=sa;Password=password;";
 
		private static string _masterDatabaseName = "master";
 
		static void Main(string[] args)
		{
			_executeMaster = new ExecuteSql(openConnectionToMaster);
			createDatabase("testme");
			Console.WriteLine("done");
			Console.ReadLine();
		}
 
		 
		private static SqlConnection openConnectionToMaster()
		{
			var connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);
			connectionStringBuilder.InitialCatalog = _masterDatabaseName;
			var conn = new SqlConnection(connectionStringBuilder.ConnectionString);
			conn.Open();
			return conn;
		}
 
		private static void createDatabase(string databaseName)
		{
			//check if the DB exists or not
			if (!Exists(databaseName))
			{
				try
				{
					var script = File.ReadAllText("CreateDB.sql");
					script = ReplaceScriptTags(script, databaseName);
					_executeMaster.ExecuteTransactionlessNonQuery(script, 10800);
				}
				catch (Exception exception)
				{
					string msg = exception.StackTrace;
				}
			}
 
		}
 
		private static string ReplaceScriptTags(string script, string name)
		{
			script = script.Replace("$(DBNAME)", name);
			return script;
		}
 
		public static bool Exists(string databaseName)
		{
			return
				Convert.ToBoolean(_executeMaster.ExecuteScalar("SELECT database_id FROM sys.databases WHERE Name = @databaseName",
																			  parameters:
																				  new Dictionary<string, object> { { "@databaseName", databaseName } }));
		}
 
		public static void Drop(string databaseName)
		{
			_executeMaster.ExecuteTransactionlessNonQuery(string.Format("DROP DATABASE [{0}]", databaseName), 120);
		}
 
		public static void SetOnline(string databaseName)
		{
			_executeMaster.ExecuteTransactionlessNonQuery(string.Format("ALTER DATABASE [{0}] SET ONLINE", databaseName));
		}
 
	}

This class is the helper class being used. We will use this class to execute the script.

public class ExecuteSql
	{
		private readonly Func<SqlConnection> _openConnection;
 
		public ExecuteSql(Func<SqlConnection> openConnection)
		{
			_openConnection = openConnection;
		}
 
		public void ExecuteCustom(Action<SqlConnection> action)
		{
			using (var connection = _openConnection())
			{
				action(connection);
			}
		}
 
		public int ExecuteScalar(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
		{
			parameters = parameters ?? new Dictionary<string, object>();
 
			var result = 0;
 
			using (var connection = _openConnection())
			{
				using (var transaction = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.Transaction = transaction;
 
						foreach (var parameter in parameters)
						{
							command.Parameters.AddWithValue(parameter.Key, parameter.Value);
						}
						command.CommandTimeout = timeout;
 
						{
							command.CommandText = sql;
							command.CommandType = CommandType.Text;
							result = (int) (command.ExecuteScalar() ?? default(int));
						}
 
						transaction.Commit();
					}
				}
			}
 
			return result;
		}
 
		public void ExecuteTransactionlessNonQuery(string sql, int timeout = 30)
		{
			using (var connection = _openConnection())
			{
				using (var command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandTimeout = timeout;
					command.CommandText = sql;
					command.ExecuteNonQuery();
				}
			}
		}
 
		public void ExecuteNonQuery(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
		{
			parameters = parameters ?? new Dictionary<string, object>();
 
			using (var connection = _openConnection())
			{
				using (var transaction = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.Transaction = transaction;
						command.CommandTimeout = timeout;
 
						{
							command.Parameters.Clear();
							foreach (var parameter in parameters)
							{
								if (sql.Contains(parameter.Key))
								{
									command.Parameters.AddWithValue(parameter.Key, parameter.Value);
								}
							}
							command.CommandText = sql;
							command.CommandType = CommandType.Text;
							command.ExecuteNonQuery();
						}
 
						transaction.Commit();
					}
				}
			}
		}
 
 
	}

This is the script file that is used to create the script. The script is well commented so that you can see the details of the application

 

BEGIN TRY

/*==============================================
Prepare statements according to server settings
and given input
==============================================*/

	-- DECLARES
	DECLARE @rc				int
	DECLARE @DataDir		nvarchar(4000)
	DECLARE @LogDir 		nvarchar(4000)
	DECLARE @LogFileName	nvarchar(4000)
	DECLARE @DataFileName	nvarchar(4000)
	DECLARE @CMD 			nvarchar(4000)

	-- Read reg values to get default datapath
	EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @DataDir output, 'no_output' 
	
	-- Check if value is NULL. In that case no changes has been done to default setup. Read from SQLDataRoot instead
	IF (@DataDir is null) 
	BEGIN
		EXEC	@rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @DataDir output, 'no_output'
		SELECT	@DataDir = @DataDir + N'\Data'
	END

	-- Read reg values to get default logpath
	EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @LogDir output, 'no_output' 

	-- Check if value is NULL. In that case no changes has been done to default setup. Read from SQLDataRoot instead
	IF (@LogDir is null)
	BEGIN
		EXEC	@rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @LogDir output, 'no_output'
		SELECT	@LogDir = @LogDir + N'\Data'
	END

	-- Add filenames to the folders
	SET @DataFileName	= @DataDir + '\$(DBNAME)_Data.mdf'
	SET @LogFileName	= @LogDir + '\$(DBNAME)_Log.ldf'
	
	-- Prepare statement
	SELECT @CMD =
	'CREATE DATABASE $(DBNAME)
	ON
		(
		NAME = $(DBNAME)_Data,
		FILENAME = ''' + @DataFileName + ''',
		SIZE = 50,
		FILEGROWTH = 10
		)
	LOG ON	(
		NAME = $(DBNAME)_Log,
		FILENAME = ''' + @LogFileName + ''',
		SIZE = 100,
		FILEGROWTH = 20
		)
	'
/*==============================================
This is were the action starts
==============================================*/
	PRINT	'Adding database $(DBNAME). Working...'
	
	-- Create database
	EXEC (@CMD)

	PRINT	'Adding database $(DBNAME). Finished!'

	PRINT	'Adding settings and properties for $(DBNAME). Working...'
	-- Add DBTYPE as extended property
	EXEC $(DBNAME).sys.sp_addextendedproperty @name=N'DatabaseType', @value=N''

	--Disable fulltext
	IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
	BEGIN
		EXEC $(DBNAME).[dbo].[sp_fulltext_database] @action = 'disable'
	END

	-- Set recovery model
	ALTER DATABASE $(DBNAME) SET RECOVERY FULL

	ALTER DATABASE $(DBNAME) SET MULTI_USER
	
	--All other database settings are based on the "model" database in local instance
	--see: sp_configure

	-- Set owner = sa (cosmetics only)
	EXEC $(DBNAME).dbo.sp_changedbowner @loginame = N'sa', @map = false

	PRINT	'Adding settings and properties for $(DBNAME). Finished'

END TRY

BEGIN CATCH
	DECLARE	@ErrorMessage			NVARCHAR(4000)
	DECLARE	@ErrorNumber			INT
	DECLARE	@ErrorSeverity			INT
	DECLARE	@ErrorState				INT
	DECLARE	@ErrorLine				INT

	IF ERROR_NUMBER() IS NOT NULL
	BEGIN
		SET	@ErrorNumber	= ERROR_NUMBER()
		SET	@ErrorSeverity	= ERROR_SEVERITY()
		SET	@ErrorState		= ERROR_STATE()
		SET	@ErrorLine		= ERROR_LINE()

		-- Return an error with state 127 since it will abort SQLCMD
		SET @ErrorMessage = 'Error %d, Severity %d, State %d, Line %d, Message: '+ ERROR_MESSAGE()
		RAISERROR (@ErrorMessage, 16, 127, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine)
	END

END CATCH
PRINT	'Finished'

/*******************************************************************************/