PageRenderTime 34ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 0ms

/src/WebMatrix.Data/Database.cs

https://bitbucket.org/mdavid/aspnetwebstack
C# | 302 lines | 247 code | 46 blank | 9 comment | 24 complexity | 43c384017adac7e144c0ccb9f65d9be5 MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Common;
  5. using System.Diagnostics.CodeAnalysis;
  6. using System.Globalization;
  7. using System.IO;
  8. using System.Linq;
  9. using Microsoft.Internal.Web.Utils;
  10. using WebMatrix.Data.Resources;
  11. namespace WebMatrix.Data
  12. {
  13. public class Database : IDisposable
  14. {
  15. internal const string SqlCeProviderName = "System.Data.SqlServerCe.4.0";
  16. internal const string SqlServerProviderName = "System.Data.SqlClient";
  17. private const string DefaultDataProviderAppSetting = "systemData:defaultProvider";
  18. internal static string DataDirectory = (string)AppDomain.CurrentDomain.GetData("DataDirectory") ?? Directory.GetCurrentDirectory();
  19. private static readonly IDictionary<string, IDbFileHandler> _databaseFileHandlers = new Dictionary<string, IDbFileHandler>(StringComparer.OrdinalIgnoreCase)
  20. {
  21. { ".sdf", new SqlCeDbFileHandler() },
  22. { ".mdf", new SqlServerDbFileHandler() }
  23. };
  24. private static readonly IConfigurationManager _configurationManager = new ConfigurationManagerWrapper(_databaseFileHandlers);
  25. private Func<DbConnection> _connectionFactory;
  26. private DbConnection _connection;
  27. internal Database(Func<DbConnection> connectionFactory)
  28. {
  29. _connectionFactory = connectionFactory;
  30. }
  31. public static event EventHandler<ConnectionEventArgs> ConnectionOpened
  32. {
  33. add { _connectionOpened += value; }
  34. remove { _connectionOpened -= value; }
  35. }
  36. private static event EventHandler<ConnectionEventArgs> _connectionOpened;
  37. public DbConnection Connection
  38. {
  39. get
  40. {
  41. if (_connection == null)
  42. {
  43. _connection = _connectionFactory();
  44. }
  45. return _connection;
  46. }
  47. }
  48. public void Close()
  49. {
  50. Dispose();
  51. }
  52. public void Dispose()
  53. {
  54. Dispose(true);
  55. GC.SuppressFinalize(this);
  56. }
  57. protected virtual void Dispose(bool disposing)
  58. {
  59. if (disposing)
  60. {
  61. if (_connection != null)
  62. {
  63. _connection.Close();
  64. _connection = null;
  65. }
  66. }
  67. }
  68. public dynamic QuerySingle(string commandText, params object[] args)
  69. {
  70. if (String.IsNullOrEmpty(commandText))
  71. {
  72. throw ExceptionHelper.CreateArgumentNullOrEmptyException("commandText");
  73. }
  74. return QueryInternal(commandText, args).FirstOrDefault();
  75. }
  76. public IEnumerable<dynamic> Query(string commandText, params object[] parameters)
  77. {
  78. if (String.IsNullOrEmpty(commandText))
  79. {
  80. throw ExceptionHelper.CreateArgumentNullOrEmptyException("commandText");
  81. }
  82. // Return a readonly collection
  83. return QueryInternal(commandText, parameters).ToList().AsReadOnly();
  84. }
  85. [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Users are responsible for ensuring the inputs to this method are SQL Injection sanitized")]
  86. private IEnumerable<dynamic> QueryInternal(string commandText, params object[] parameters)
  87. {
  88. EnsureConnectionOpen();
  89. DbCommand command = Connection.CreateCommand();
  90. command.CommandText = commandText;
  91. AddParameters(command, parameters);
  92. using (command)
  93. {
  94. IEnumerable<string> columnNames = null;
  95. using (DbDataReader reader = command.ExecuteReader())
  96. {
  97. foreach (DbDataRecord record in reader)
  98. {
  99. if (columnNames == null)
  100. {
  101. columnNames = GetColumnNames(record);
  102. }
  103. yield return new DynamicRecord(columnNames, record);
  104. }
  105. }
  106. }
  107. }
  108. private static IEnumerable<string> GetColumnNames(DbDataRecord record)
  109. {
  110. // Get all of the column names for this query
  111. for (int i = 0; i < record.FieldCount; i++)
  112. {
  113. yield return record.GetName(i);
  114. }
  115. }
  116. [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Users are responsible for ensuring the inputs to this method are SQL Injection sanitized")]
  117. public int Execute(string commandText, params object[] args)
  118. {
  119. if (String.IsNullOrEmpty(commandText))
  120. {
  121. throw ExceptionHelper.CreateArgumentNullOrEmptyException("commandText");
  122. }
  123. EnsureConnectionOpen();
  124. DbCommand command = Connection.CreateCommand();
  125. command.CommandText = commandText;
  126. AddParameters(command, args);
  127. using (command)
  128. {
  129. return command.ExecuteNonQuery();
  130. }
  131. }
  132. [SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate", Justification = "This makes a database request")]
  133. public dynamic GetLastInsertId()
  134. {
  135. // This method only support sql ce and sql server for now
  136. return QueryValue("SELECT @@Identity");
  137. }
  138. [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Users are responsible for ensuring the inputs to this method are SQL Injection sanitized")]
  139. public dynamic QueryValue(string commandText, params object[] args)
  140. {
  141. if (String.IsNullOrEmpty(commandText))
  142. {
  143. throw ExceptionHelper.CreateArgumentNullOrEmptyException("commandText");
  144. }
  145. EnsureConnectionOpen();
  146. DbCommand command = Connection.CreateCommand();
  147. command.CommandText = commandText;
  148. AddParameters(command, args);
  149. using (command)
  150. {
  151. return command.ExecuteScalar();
  152. }
  153. }
  154. private void EnsureConnectionOpen()
  155. {
  156. // If the connection isn't open then open it
  157. if (Connection.State != ConnectionState.Open)
  158. {
  159. Connection.Open();
  160. // Raise the connection opened event
  161. OnConnectionOpened();
  162. }
  163. }
  164. private void OnConnectionOpened()
  165. {
  166. if (_connectionOpened != null)
  167. {
  168. _connectionOpened(this, new ConnectionEventArgs(Connection));
  169. }
  170. }
  171. private static void AddParameters(DbCommand command, object[] args)
  172. {
  173. if (args == null)
  174. {
  175. return;
  176. }
  177. // Create numbered parameters
  178. IEnumerable<DbParameter> parameters = args.Select((o, index) =>
  179. {
  180. var parameter = command.CreateParameter();
  181. parameter.ParameterName = index.ToString(CultureInfo.InvariantCulture);
  182. parameter.Value = o ?? DBNull.Value;
  183. return parameter;
  184. });
  185. foreach (var p in parameters)
  186. {
  187. command.Parameters.Add(p);
  188. }
  189. }
  190. public static Database OpenConnectionString(string connectionString)
  191. {
  192. return OpenConnectionString(connectionString, providerName: null);
  193. }
  194. public static Database OpenConnectionString(string connectionString, string providerName)
  195. {
  196. if (String.IsNullOrEmpty(connectionString))
  197. {
  198. throw ExceptionHelper.CreateArgumentNullOrEmptyException("connectionString");
  199. }
  200. return OpenConnectionStringInternal(providerName, connectionString);
  201. }
  202. public static Database Open(string name)
  203. {
  204. if (String.IsNullOrEmpty(name))
  205. {
  206. throw ExceptionHelper.CreateArgumentNullOrEmptyException("name");
  207. }
  208. return OpenNamedConnection(name, _configurationManager);
  209. }
  210. internal static IConnectionConfiguration GetConnectionConfiguration(string fileName, IDictionary<string, IDbFileHandler> handlers)
  211. {
  212. string extension = Path.GetExtension(fileName);
  213. IDbFileHandler handler;
  214. if (handlers.TryGetValue(extension, out handler))
  215. {
  216. return handler.GetConnectionConfiguration(fileName);
  217. }
  218. throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture,
  219. DataResources.UnableToDetermineDatabase, fileName));
  220. }
  221. private static Database OpenConnectionStringInternal(string providerName, string connectionString)
  222. {
  223. return OpenConnectionStringInternal(new DbProviderFactoryWrapper(providerName), connectionString);
  224. }
  225. private static Database OpenConnectionInternal(IConnectionConfiguration connectionConfig)
  226. {
  227. return OpenConnectionStringInternal(connectionConfig.ProviderFactory, connectionConfig.ConnectionString);
  228. }
  229. internal static Database OpenConnectionStringInternal(IDbProviderFactory providerFactory, string connectionString)
  230. {
  231. return new Database(() => providerFactory.CreateConnection(connectionString));
  232. }
  233. internal static Database OpenNamedConnection(string name, IConfigurationManager configurationManager)
  234. {
  235. // Opens a connection using the connection string setting with the specified name
  236. IConnectionConfiguration configuration = configurationManager.GetConnection(name);
  237. if (configuration != null)
  238. {
  239. // We've found one in the connection string setting in config so use it
  240. return OpenConnectionInternal(configuration);
  241. }
  242. throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture,
  243. DataResources.ConnectionStringNotFound, name));
  244. }
  245. internal static string GetDefaultProviderName()
  246. {
  247. string providerName;
  248. // Get the default provider name from config if there is any
  249. if (!_configurationManager.AppSettings.TryGetValue(DefaultDataProviderAppSetting, out providerName))
  250. {
  251. providerName = SqlCeProviderName;
  252. }
  253. return providerName;
  254. }
  255. }
  256. }