Advertisement
Guest User

Untitled

a guest
Feb 27th, 2017
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.80 KB | None | 0 0
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.Common;
  4. using System.Data.SqlClient;
  5. using System.IO;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Text.RegularExpressions;
  9. using System.Threading.Tasks;
  10.  
  11. namespace LocalDBHelper
  12. {
  13. class LocalDBHelper
  14. {
  15. private const string ForceDropAndCreateWithFilenameDBSqlTemplate = @"
  16.  
  17. IF EXISTS(SELECT * FROM sysdatabases WHERE name = '{0}')
  18. BEGIN
  19. --try catch becasue if MDF files have been deleted DROP DATABASE works but errors
  20. BEGIN TRY
  21. DROP DATABASE [{0}]
  22. END TRY
  23. BEGIN CATCH
  24. END CATCH
  25. END
  26.  
  27. CREATE DATABASE [{0}] ON (name='{0}', filename='{1}')
  28.  
  29. ";
  30.  
  31. private const string DropDBSqlTemplate = @"
  32. IF EXISTS(SELECT * FROM sys.databases WHERE name='{0}')
  33. BEGIN
  34. ALTER DATABASE [{0}]
  35. SET SINGLE_USER
  36. WITH ROLLBACK IMMEDIATE
  37. DROP DATABASE [{0}]
  38. END
  39. ";
  40.  
  41. private const string CreateDBSqlTemplate = @"
  42. IF NOT EXISTS(SELECT * FROM sysdatabases WHERE name = '{0}')
  43. BEGIN
  44. CREATE DATABASE [{0}]
  45. END
  46. ";
  47.  
  48. public bool IsLocalDB { get; private set; }
  49. public string AttachDBFilename { get; private set; }
  50. public string DatabaseName { get; private set; }
  51. public string MasterConnectionString { get; private set; }
  52.  
  53. public LocalDBHelper(string connectionString)
  54. {
  55. var connectionStringBuilder = new SqlConnectionStringBuilder();
  56. connectionStringBuilder.ConnectionString = connectionString;
  57.  
  58. IsLocalDB = connectionStringBuilder.DataSource.ToLower().Contains("localdb");
  59.  
  60. var attachFileName = connectionStringBuilder.AttachDBFilename;
  61. attachFileName = Regex.Replace(attachFileName, @"\|DataDirectory\|", GetDataDirectory(), RegexOptions.IgnoreCase);
  62. attachFileName = attachFileName.Replace(@"/", @"\");
  63. attachFileName = attachFileName.Replace(@"\\", @"\");
  64. AttachDBFilename = attachFileName;
  65.  
  66. DatabaseName = connectionStringBuilder.InitialCatalog;
  67.  
  68. connectionStringBuilder.InitialCatalog = "master";
  69. connectionStringBuilder.AttachDBFilename = "";
  70. MasterConnectionString = connectionStringBuilder.ToString();
  71. }
  72.  
  73.  
  74. public void EnsureDatabaseExists()
  75. {
  76. EnsureIsLocalDb();
  77.  
  78. //if attach file exists then the database exists, we are done
  79. if (!string.IsNullOrWhiteSpace(AttachDBFilename) && File.Exists(AttachDBFilename))
  80. return;
  81.  
  82.  
  83. var sql = string.IsNullOrWhiteSpace(AttachDBFilename)
  84. ? string.Format(CreateDBSqlTemplate, DatabaseName)
  85. : string.Format(ForceDropAndCreateWithFilenameDBSqlTemplate, DatabaseName, AttachDBFilename);
  86.  
  87. ExecuteSql(sql, MasterConnectionString);
  88. }
  89.  
  90. public void DropDatabase()
  91. {
  92. EnsureIsLocalDb();
  93. var sql = string.Format(DropDBSqlTemplate, DatabaseName);
  94. ExecuteSql(sql, MasterConnectionString);
  95. }
  96.  
  97.  
  98. private void EnsureIsLocalDb()
  99. {
  100. if (!IsLocalDB)
  101. throw new Exception("connecting string is not for a LocalDB instance");
  102. }
  103.  
  104.  
  105. private static string GetDataDirectory()
  106. {
  107. var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
  108. if (dataDirectory == null)
  109. dataDirectory = AppDomain.CurrentDomain.BaseDirectory;
  110. return dataDirectory;
  111. }
  112.  
  113. private static void ExecuteSql(string sql, string connectionString)
  114. {
  115. using (var connection = new SqlConnection(connectionString))
  116. {
  117. connection.Open();
  118. using (var command = new SqlCommand(sql, connection))
  119. {
  120. command.ExecuteNonQuery();
  121. }
  122. }
  123. }
  124.  
  125. }
  126. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement