Month: December 2008

Using local SQL 2005 Express databases, |DataDirectory| setting and SQLBulkCopy with SemiColon delimited text files

Matt Simner .NET Framework, C#, SQL Server , , , ,

Had some fun with this today as I was utterly confused why I wasn’t able to save data to my SQL2005 express DB (linked within a Windows Forms VS 2005 project), even though I wasn’t getting any errors from a SQLBulkCopy operation.  The connection string was set (from the wizard) to use ‘|DataDirectory|\dbname.mdf.

It turned out of course that the reason there wasn’t an error was because the data was being written fine, but just not to the database I was looking at.  By default any database linked in your project file will be copied to the output folder (bin\debug) when you build.  It does this ‘every time‘.  I thought I was going mad as I wasn’t getting primary key violations trying to insert the same data over and over again!

The useful info is in this MSDN blog entry.  It talks about different options for working around the local databases, but I plumped for a subfolder (called data) in my project, then setting ‘Copy if newer’ on the MDF file rather than ‘do not copy’.  This just means that if you make a schema change then it will blow everything away (probably what I normally want in debug-land).

The reason I was using the SQLBulkCopy class was so I could take in a dataset picked from a semicolon-delimited text file. 
The first thing to do was read the text file into a dataset – and after some mucking about with the Jet OleDb provider and avoiding changing registry entries (to get it to recognise ‘;’ as the delimiter) I found that a schema.ini file is what’s required.

Here’s some quick code I then used to get a DataSet from a semicolon-delimited Text File:

       

       /// <summary>
        /// Import from a given file to database
        /// </summary>
        /// <param name=”fileName”>File to import from (CSV)</param>
        public void Import(string fileName)
        {
            if (File.Exists(fileName))
            {
                SqlConnection connection = new SqlConnection(Properties.Settings.Default.RBStatsConnectionString);
                //Get Dataset from file
                DataSet importData = GetDataSetFromFile(fileName);

                //Import using SQLBulkCopy
                SqlBulkCopy copyManager = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                copyManager.DestinationTableName = “TShirtStatistic”;
                copyManager.BatchSize = importData.Tables[0].Rows.Count;
                try
                {
                    copyManager.WriteToServer(importData.Tables[0]);
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception);
                }
                copyManager.Close();
                connection.Close();
            }

        }

        public DataSet GetDataSetFromFile(string fileName)
        {
            //Ensure we’ve got a schema file to fudge the semicolon delimited text
            string schemaFile = Path.Combine(Path.GetDirectoryName(fileName), “schema.ini”);
            File.Delete(schemaFile);
            string schemaContents = “[” + Path.GetFileName(fileName) + “]\nColNameHeader=True\nCharacterSet=ANSI\nFormat=Delimited(;)”;
            StreamWriter writer = File.CreateText(schemaFile);
            writer.Write(schemaContents);
            writer.Close();

            string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + Path.GetDirectoryName(fileName) + “\\;Extended Properties=’text;HDR=Yes;FMT=Delimited'”;
            string query = “SELECT * FROM ” + Path.GetFileName(fileName);
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand command = new OleDbCommand(query, connection);

            connection.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            return ds;

        }

How to extract only changed files from SubVersion for partial deployment using TortoiseSVN

Matt Simner SubVersion , ,

I was really surprised how long it took me to find out how to do this – as it’s quite a simple operation, but if your deployment folder structure matches your SVN repository (e.g. classic ASP site, PHP etc) and you want to do a partial deployment of only files changed in a particular revision (or revisions) then here’s how to get the files (complete with folder structure) using TortoiseSVN:



  1. First you need to work out how you’re going to identify your revision range.  There’s a number of ways to go about this:


    1. Version comments are your friend

    2. You could just note the revision number when you commit.

    3. You could use tags to help identify the range.  I use CruiseControl.NET and so all ‘builds’ are automatically tagged.  If you always release from tags then you can just ‘show log’ on the tag and see the max revision number. 

    4. You could just use a date range.

  2. Once you’ve decided ‘how’ you’re going to identify the revisions (it may be as simple as the ‘last commit’) then either through your working copy, or from Repo Browser ‘show log’ on the root of your ‘deployment’ folder structure.  The files you’re interested in may be within a ‘code’ or ‘source’ folder or similar so you only want to get the files you want to deploy. 

  3. Select the revision/s based on the method you’re using (step 1) and do ‘Compare revisions’.  If you’re just looking at one revision then do ‘Compare with previous revision’.

  4. Review the list of files and select all (assuming the list looks correct) then right-click and ‘Export selection to…’

  5. The file structure will be created at the folder you specify, and you can then zip or do whatever you normally do to deploy.

If you only want the list of files then you can choose ‘Save list of selected files to…’ (text file).


This is all cool and simple, but it’s still quite a manual process.  If you’re certain of the revisions or you automatically keep track of the last deployment revision then it would probably make sense to automate this further with something like NAnt (which is what I’ll attempt to eventually get round to). 


This process also doesn’t cater for deletions (as such), so special care needs to be taken when reviewing the list of files (the action column shows the nature of the changes).

Removing the scrollbar Page Shift from Firefox

Matt Simner Miscellaneous, Tools, Web ,

This had bugged me for a while.  A lot of sites (including some of the ones I develop) tend to have a fixed width layout these days and some browsers (IE particularly) ‘always’ has a visible scrollbar.  This means that the available screen width is constant whether the page scrolls or not. 

Firefox on the other hand (and Chrome/Opera/Safari) seem to have this off by default.  This of course seems reasonable until you have a fixed width, centred layout that ‘shifts’ when you switch from a non-scrolling to a scrolling page.  It’s just a bit off-putting.

Fortunately Firefox is configurable and the following will fix that up for you. (I’m sure the other browsers are capable of something similar but I’m not using them much 🙂 )

  1. Find your profile directory (it’s bound to be the ‘default’ one unless you’re developing Firefox addons.  You’ll normally find it in c:\documents and settings\username\application data\Mozilla\Profiles\xxxxx.default\
  2. Go to the ‘chrome’ subfolder and create a file called userContent.css (you’ll probably find there’s a couple of ‘example’ files there already.
  3. Add the following (Firefox-specfic) line to the file:

    html { overflow: -moz-scrollbars-vertical !important; }

  4. Save the file, exit Firefox and start her up again.  You should now have a permanent scrollbar which eliminates the page shift.