Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Collections.Generic;
- using System.Data.Common;
- using System.Data.SqlClient;
- using System.IO;
- using System.Linq;
- using System.Text;
- using System.Text.RegularExpressions;
- using System.Threading.Tasks;
- namespace LocalDBHelper
- {
- class LocalDBHelper
- {
- private const string ForceDropAndCreateWithFilenameDBSqlTemplate = @"
- IF EXISTS(SELECT * FROM sysdatabases WHERE name = '{0}')
- BEGIN
- --try catch becasue if MDF files have been deleted DROP DATABASE works but errors
- BEGIN TRY
- DROP DATABASE [{0}]
- END TRY
- BEGIN CATCH
- END CATCH
- END
- CREATE DATABASE [{0}] ON (name='{0}', filename='{1}')
- ";
- private const string DropDBSqlTemplate = @"
- IF EXISTS(SELECT * FROM sys.databases WHERE name='{0}')
- BEGIN
- ALTER DATABASE [{0}]
- SET SINGLE_USER
- WITH ROLLBACK IMMEDIATE
- DROP DATABASE [{0}]
- END
- ";
- private const string CreateDBSqlTemplate = @"
- IF NOT EXISTS(SELECT * FROM sysdatabases WHERE name = '{0}')
- BEGIN
- CREATE DATABASE [{0}]
- END
- ";
- public bool IsLocalDB { get; private set; }
- public string AttachDBFilename { get; private set; }
- public string DatabaseName { get; private set; }
- public string MasterConnectionString { get; private set; }
- public LocalDBHelper(string connectionString)
- {
- var connectionStringBuilder = new SqlConnectionStringBuilder();
- connectionStringBuilder.ConnectionString = connectionString;
- IsLocalDB = connectionStringBuilder.DataSource.ToLower().Contains("localdb");
- var attachFileName = connectionStringBuilder.AttachDBFilename;
- attachFileName = Regex.Replace(attachFileName, @"\|DataDirectory\|", GetDataDirectory(), RegexOptions.IgnoreCase);
- attachFileName = attachFileName.Replace(@"/", @"\");
- attachFileName = attachFileName.Replace(@"\\", @"\");
- AttachDBFilename = attachFileName;
- DatabaseName = connectionStringBuilder.InitialCatalog;
- connectionStringBuilder.InitialCatalog = "master";
- connectionStringBuilder.AttachDBFilename = "";
- MasterConnectionString = connectionStringBuilder.ToString();
- }
- public void EnsureDatabaseExists()
- {
- EnsureIsLocalDb();
- //if attach file exists then the database exists, we are done
- if (!string.IsNullOrWhiteSpace(AttachDBFilename) && File.Exists(AttachDBFilename))
- return;
- var sql = string.IsNullOrWhiteSpace(AttachDBFilename)
- ? string.Format(CreateDBSqlTemplate, DatabaseName)
- : string.Format(ForceDropAndCreateWithFilenameDBSqlTemplate, DatabaseName, AttachDBFilename);
- ExecuteSql(sql, MasterConnectionString);
- }
- public void DropDatabase()
- {
- EnsureIsLocalDb();
- var sql = string.Format(DropDBSqlTemplate, DatabaseName);
- ExecuteSql(sql, MasterConnectionString);
- }
- private void EnsureIsLocalDb()
- {
- if (!IsLocalDB)
- throw new Exception("connecting string is not for a LocalDB instance");
- }
- private static string GetDataDirectory()
- {
- var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
- if (dataDirectory == null)
- dataDirectory = AppDomain.CurrentDomain.BaseDirectory;
- return dataDirectory;
- }
- private static void ExecuteSql(string sql, string connectionString)
- {
- using (var connection = new SqlConnection(connectionString))
- {
- connection.Open();
- using (var command = new SqlCommand(sql, connection))
- {
- command.ExecuteNonQuery();
- }
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement