Contents tagged with database

• 03Dec

  Database versioning updated

  by Mikael Lundin

  database, powershell

  I have been getting some feedback on my blog post about database change management, and I thought it was about time to make some updates to the database change management powershell script that I posted there. Let's go through this, step by step.

  My change management procedure means that I will rebuild the database from scratch for each build. Before building the database, I need to drop the old one.

  # Will drop the database if it exists
  Function Drop-Database ([string]$sql_server, [string]$database_name)
  {
  	try {
  		[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
  		$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sql_server
  		$s.Refresh();
  		$s.KillDatabase($database_name);
  		Write-Host "Killed database $databaseName"
  	}
  	catch {
  		Write-Error "Tried to delete database $databaseName but failed, probably because it did not exist"
  	}
  }

  We could check if the database exists before we try to drop.

  # Determine if database exists
  Function Exists-Database ([string]$sql_server, [string]$database_name)
  {
  	$exists = $FALSE
  	try {
  		# Connect and run a command using SMO 
  		[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
  		
  		# Get server reference
  		$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sql_server
  		
  		foreach($db in $s.databases) 
  		{
  			# Database exists?
  			if ($db.name -eq $database_name) {
  				$exists = $TRUE
  			}
  		}
  	}
  	catch {
  		Write-Error "Failed to connect to $sql_server"
  	}
  
  	# Return
  	$exists
  }

  Start by creating a new database and add a versioning table to it, where we keep track of the database version.

  # Will create a new database and add table to manage versions
  Function Build-Database ([string]$sqlServer, [string]$databaseName)
  {
  	# http://sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx
  	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
  	$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
  	$dbname = $databaseName
  	
  	# Instantiate the database object and create database
  	$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
  	$db.Create()
  	
  	# Create table and column for handling database version
  	$db.ExecuteNonQuery("CREATE TABLE [$databaseName].[dbo].[Settings] ([DatabaseVersion] int NOT NULL)");
  	$db.ExecuteNonQuery("INSERT INTO [$databaseName].[dbo].[Settings] ([DatabaseVersion]) VALUES (0)");
  }

  And now we can look through the directory that contains the change scripts and execute them on the database, one by one.

  # Will update the database to the most current version in the database directory
  Function Update-Database ([string]$connection_string, [string]$database_directory)
  {
  
  	$databaseVersion = Get-Database-Version $connection_string
  	
  	# Get all source files that have higher database version number
  	$files = Get-ChildItem "$database_directory\*.sql" | Where { [int]::Parse($_.name.Substring(0, 4)) -gt $databaseVersion }
  	
  	# For each of those files, run query on database
  	foreach ($file in $files)
  	{
  		$fileName = $file.name
  		Write-Host "Apply update script: $fileName"
  		
  		# Get-Content returns a string array of all the lines. We join that into a single string
  		$fileContents = Get-Content "$file"
  		$sql = [string]::Join([Environment]::NewLine, $fileContents);
  		Execute-Sql-Query $connectionString $sql
  		
  		# Get this version number
  		$version = [int]::Parse($fileName.Substring(0, 4))
  	
  		# Update the settings database with current version number
  		Execute-Sql-Query $connectionString "UPDATE [Settings] SET [DatabaseVersion] = $version"
  	}
  }

  Last, there's some helper functions for the functions used above.

  Function Get-Database-Version ([string]$connectionString)
  {
  	[System.Data.SqlClient.SqlConnection]::ClearAllPools()
  
  	$sql = "SELECT TOP 1 [DatabaseVersion] FROM [Settings]"
  	## Connect to the data source and open it
  	$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  	$connection.Open()
  
  	$command = New-Object System.Data.SqlClient.SqlCommand $sql,$connection
  	$version = $command.ExecuteScalar();
  	
  	$connection.Close()
  	$version
  }
  
  Function Validate-Connection ([string]$connectionString)
  {
  	[System.Data.SqlClient.SqlConnection]::ClearAllPools()
  	
  	try	{
  		## Connect to the data source and open it
  		$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  		$connection.Open()
  		$connection.Close()
  		$TRUE
  	}
  	catch {
  		$FALSE
  	}
  }
  
  Function Execute-Sql-Query ([string]$connectionString, [string]$sql)
  {
  	[System.Data.SqlClient.SqlConnection]::ClearAllPools()
  	
  	## Connect to the data source and open it
  	$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  	$connection.Open()
  
  	$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
  	$server.ConnectionContext.ExecuteNonQuery($sql) | out-null
  	
  	$connection.Close()
  }

  Thanks to Gary Murphy for his contribution to Execute-Sql-Query, and getting it to work properly with GO-statements. You will find his blog at http://garyjmurphy.com/.

  The whole script can be found in its repository on bitbucket.

  Thank you for recommending this blog post to others:
• 16Mar

  Hand rolling your datalayer

  by Mikael Lundin

  data-access, database, datalayer

  There are higher and higher demands on you as a developer to learn more and more frameworks that I sometimes get lost in the djungle out there. If you ask me to store some data into a database my mind will automatically go into an NHibernate, Entity Framework, Linq 2 SQL, Castle Active Record mantra and start discussing with itself what is the perfect framework for this specific problem. When you have four entities you don't really need an ORM to handle persistance. In fact, using an ORM in that case is same as "stealing from your clients". (I'm aware that the link goes to a post that states the opposite of my argument) I suggest we take a moment here to reflect on how you hand roll a data layer that will suit for up to four entities. First we need some sort of provider that may deliver us data connections and commands. Some sort of object that will get the plumbing out of the way.

  /// <summary>
  /// Provider class for the database connection and command
  /// </summary>
  public class DataProvider : IDataProvider
  {
      private readonly ConnectionStringSettings settings;
  
      /// <summary>
      /// Create new instance of DataProvider
      /// </summary>
      /// <param name="connectionStringName">Name of the active connectionString in the App.Config/Web.Config</param>
      public DataProvider(string connectionStringName)
      {
          settings = ConfigurationManager.ConnectionStrings[connectionStringName];
  
          if (settings == null)
          {
              throw new ConfigurationErrorsException("Expected a connectionString with the name " + connectionStringName);
          }
  
          DbProviderFactory = DbProviderFactories.GetFactory(settings.ProviderName);
      }
  
      /// <summary>
      /// Gets the DbProviderFactory
      /// </summary>
      public DbProviderFactory DbProviderFactory { get; private set; }
  
      /// <summary>
      /// Opens a new connection to the database. Make sure you close it before you're done.
      /// </summary>
      /// <returns>An open DbConnection</returns>
      public DbConnection OpenConnection()
      {
          var connection = DbProviderFactory.CreateConnection();
          connection.ConnectionString = settings.ConnectionString;
          connection.Open();
  
          return connection;
      }
  
      /// <summary>
      /// Creates a new DbCommand
      /// </summary>
      /// <param name="connection">The connection we utilize for this command</param>
      /// <param name="query">The SQL query to run</param>
      /// <returns>The DbCommand</returns>
      public DbCommand CreateCommand(DbConnection connection, string query)
      {
          var command = DbProviderFactory.CreateCommand();
          command.Connection = connection;
          command.CommandText = query;
  
          return command;
      }
  
      /// <summary>
      /// Creates a DbParameter for parameterized database calls
      /// </summary>
      /// <param name="name">Name of the parameter</param>
      /// <param name="type">Type of the parameter</param>
      /// <param name="value">Value of the parameter</param>
      /// <returns>A DbParameter</returns>
      public DbParameter CreateParameter(string name, DbType type, object value)
      {
          var parameter = DbProviderFactory.CreateParameter();
          parameter.ParameterName = name;
          parameter.DbType = type;
          parameter.Value = value;
  
          return parameter;
      }
  }

  And this would be an example of how you can use that provider to talk to the database.

  public class BookRepository
  {
      private const string GetByIdQuery = "SELECT title, author FROM books WHERE id=@id";
      private readonly IDataProvider provider;
  
      public BookRepository(IDataProvider provider)
      {
          this.provider = provider;
      }
  
      public Book GetById(int id)
      {
          using (var connection = provider.OpenConnection())
          using (var command = provider.CreateCommand(connection, GetByIdQuery))
          {
              var idParameter = provider.CreateParameter("id", DbType.Int32, id);
              command.Parameters.Add(idParameter);
  
              var reader = command.ExecuteReader();
  
              if (!reader.Read())
              {
                  throw new DataException("Expected Book entity with id " + id + " in the database");
              }
  
              var result = new Book
              {
                  Id = id,
                  Title = reader["title"] as string,
                  Author = reader["author"] as string
              };
  
              return result;
          }
      }
  }

  From your presenter or controller (or whatever) you may now use your data layer like this.

  // Could use a DI framework here, but what the heck!
  var repository = new BookRepository(new DataProvider("MyDatabaseConnection"));
  var book = repository.GetById(1);

  There's nothing wrong with going back to the basics, sometimes.

  Thank you for recommending this blog post to others:
• 30Aug

  Virtual Path Provider

  by Mikael Lundin

  database, virtual-path-provider

  A user in Mint should be able to upload his own files. He should upload images, css-files, xsl-files and maybe even video clips. In order to support this I will build a Virtual Path Provider where user files will be located. Building a Virtual Path Provider is very easy. All you have to do is to override three classes in the System.Web.Hosting namespace.

  • VirtualPathProvider
  • VirtualFile
  • VirtualDirectory

  All those have perfectly comprehensible examples on MSDN for implementation. It is a quite cool technology where you can have most parts of a website (including aspx pages) inside a virtual place, which could mean "stored in the database" or some xml-format, or i a zip-file. I'm just going to use it for user files, so my implementation will first look for a "real" file on the hard drive, and after that, try to get the VirtualFile. A list of files and directories will be stored in a SQL database, and I think I will place the data on the actual hard drive as a file. I think it will be both faster and more manageable than having them as a blob in the database. The following database schema came into mind

  

  One of my collaeges att work told me that I should think about merging file and directory table because a file and directory is basically the same. It is just the relation that is different ie. a file can't have another file as a parent. He's probably right and I might have to think about changing the database model.

  Thank you for recommending this blog post to others: