Author: Matt Simner

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. 

Create SQL Server User and Role Script to execute Stored Procedures Only

Matt Simner SQL Server, Tools , , ,

I really thought I’d posted this before, but here’s a script I use quite often when I need to create a SQL user that needs to just have ‘execute’ access to Stored Procs. 

This is usually used for web apps where the server is going through as a single user.  I’d encourage anyone to ensure that you always start off with granting the minimum access required and only add more when actually needed.  If you’ve got different roles in the application (viewer, administrator etc) then create different roles, with a user for each.

The intention is to remove the need to give direct table access (as we only call Stored Procs right?).  The script doesn’t actually do the ‘grants’ for you, but will generate another batch that you can execute if you like.  I prefer to put the grants in the script for each proc/function anyway, just so it’s a little easier to maintain, and you can specifically grant access to the ‘admin’ procs to the ‘admin’ role, whilst limiting any ‘normal’ users to the read-only/everyday ones. 

There’s satisfaction to be had from an app that connects to the database via an appropriate user, which is in a role that can only do what is necessary for the role, and can’t access tables directly.  Not only is it neat, it’s also (relatively) secure.

Here’s the script…

— MSIMNER: This script will generate a role and user for SQL Access to a single database
— The role will have no specific access to read or write tables but will be granted access
— through execution of stored procedures ONLY

USE <databasename, sysname, MyDB>

DECLARE @UserName VARCHAR(20)
DECLARE @Password VARCHAR(20)
DECLARE @DBName   VARCHAR(20)
DECLARE @Role     VARCHAR(20)

–SET PASSWORD HERE
SET @Password = ‘ixFg2tfk’

SET @UserName = ‘<databasename, sysname, MyDB>User’
SET @Role     = ‘db_exec_<databasename, sysname, MyDB>’
SET @DBName   = ‘<databasename, sysname, MyDB>’

IF EXISTS(SELECT * FROM master.dbo.syslogins WHERE name = @UserName)
BEGIN
    exec sp_revokedbaccess @name_in_db = @UserName
    exec sp_droplogin @loginame = @UserName
    exec sp_droprole @rolename = @Role

END

–Create User
exec sp_addlogin @loginame = @UserName, @passwd = @Password
exec sp_grantdbaccess @loginame = @UserName
exec sp_defaultdb @loginame = @UserName, @defdb = @DBName
exec sp_addrole @rolename = @Role, @ownername = ‘dbo’
exec sp_addrolemember @rolename = @Role, @membername = @UserName

— The output of this command will need to be run separately – i.e. paste the output into a new window and run… 

–SELECT ‘GRANT EXECUTE ON ‘ + name + ‘ TO ‘ + @Role , *
–FROM sysobjects
–WHERE type = ‘P’

Colorized by: CarlosAg.CodeColorizer

Removing references to HttpModules from ASP.NET SubFolders in web.config

Matt Simner ASP.NET, Web , , ,

If you have ASP.NET applications that live as subfolders of a larger site, you may find yourself with issues when it tries to find assemblies and httpmodules that are referenced in the parent’s web.config.

Fortunately this is something you can work around.  Matthew Nolton goes through how you ‘remove’ these references at the subfolder level using the <remove> element.

This is all fine until you get an even tastier situation like I encountered the other day…

ASP.NET application ‘A’ lives as subfolder ‘B’ of both parent site ‘C’ and ‘D’.  The configuration of ‘C’ and ‘D’ is slightly different (modules, handlers, assemblies etc).  Why is this a problem you ask?  We were trying to be a bit clever (and failed 🙂 ) by only deploying application ‘A’ once.  Virtual directories in site ‘C’ and ‘D’ both point to the same physical ‘A’ folder.  This effectively means that the stuff that needs to be removed from ‘A’ varies depending on which parent site you’re accessing.

OK – I could just fix this by duplicating the installation, and varying the configuration but….

You can also remove all modules by adding a ‘clear’ element as follows…

<httpModules>
    

<clear/>

</httpModules>

This is fine, BUT if you’re using Session State or any other in-built features that are implemented as httpModules then you’ll get exceptions as ASP.NET will give you a ‘null’ session for instance.

The following is probably a safe list of modules you’d normally need (maybe even only the session for simple apps), so just add them back in after the ‘clear’….

<httpModules>
    
<clear/>
    
<add name=”OutputCache” type=”System.Web.Caching.OutputCacheModule”/>
    
<add name=”Session” type=”System.Web.SessionState.SessionStateModule”/>
    
<add name=”WindowsAuthentication” type=”System.Web.Security.WindowsAuthenticationModule”/>
    
<add name=”FileAuthorization” type=”System.Web.Security.FileAuthorizationModule”/>
</httpModules>

This is nicer for a couple of reasons

  1. It shows what dependencies the application has on ASP.NET/external features, and…
  2. It gives you the power back to have the application consumed by multiple sites as you’ve effectively decoupled yourself from the parent’s dependencies.