SSDT With Different User Accounts Per Environment

29. December 2014 09:20

 

I really like SSDT (SQL Server Data Tools). It makes it a breeze to add all of the database code into source control and it generates update scripts to publish changes with ease. One problem I’ve run into is using different user accounts in different environments. We have different domains for development and production so the accounts in the environments are different. Here is an overview of one way to handle this situation.

First I added a security folder with three files to the SSDT project.

image

The Users_DEFAULT.sql should have the build action set to “Build” while the Users_DEV.sql and Users.PRODUCTION.sql should have the build action set to “None”.

Users_DEFAULT.sql can be empty to start while the DEV and PRODUCTION files should contain the necessary TSQL to create the user(s).

-- server login
CREATE LOGIN [csd\wbsmmservice] FROM WINDOWS
GO
	
-- DB login
CREATE USER [wbsmmservice]
	FOR LOGIN [csd\wbsmmservice]     WITH DEFAULT_SCHEMA = [db_owner];
GO
 
 
-- role membership
EXEC  sp_addrolemember @rolename='db_datareader', @membername='wbsmmservice'
GO
EXEC  sp_addrolemember @rolename='db_datawriter', @membername='wbsmmservice'
GO

 

The magic happens during build events. I unloaded the project (right click and unload) and then edited it to add the following.

  <Target Name="BeforeBuild">     <Message Text="Copy files task running for configuration: $(Configuration)" Importance="high" />     <Copy Condition=" '$(Configuration)' == 'PRODUCTION' " SourceFiles="$(ProjectDir)Security\Users_PRODUCTION.sql" DestinationFiles="$(ProjectDir)Security\Users_DEFAULT.sql" OverwriteReadOnlyFiles="true" />     <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="$(ProjectDir)Security\Users_DEV.sql" DestinationFiles="$(ProjectDir)Security\Users_DEFAULT.sql" OverwriteReadOnlyFiles="true" />   </Target>

 

So, now before the build, based on the project/solution configuration, either the DEV or PRODUCTION users file will be copied over the DEFAULT file which will then be used in the compile.

 

How to find ClickOnce Application Directory

21. July 2014 09:18

 

ClickOnce applications store their data in an obscure directory (i.e. C:\Users\username\AppData\Local\Apps\2.0\QH85L3CK.1O0\P6HV1J4X.59N\west..tion_945506851464d64d_0003.0002_df3408a05edbe3cf). Sometimes you want to get to this directory to take a look at a log file or check/test settings. There is an easy way to locate this directory when the application is running.

Open Task Manager

Under the Applications tab, right click the application and select go to process (or just locate the process under the Process tab if you know what it is).

Right click the process name and choose Open file location.

image

Like magic, you’ve found the obscure directory!

Auto Launch ClickOnce App From Web Page

16. June 2014 14:19

 

Recently ran into a situation where a web page was launching a ClickOnce application automatically (no interaction from the user). This worked fine on a machine running IE8, but not IE9 and greater. It would appear to download the manifest but never actually started the application without user intervention. Some research turned up a MSDN article (see the section on Activating ClickOnce Applications Through Browser Scripting) which indicates the “Automatic prompting for file downloads” under Security/Downloads needs to be enabled. Checked on the IE8 machine and sure enough it was enabled. Went to the IE9 machine, and there is no such setting! After some more researching I found a registry key to change on IE9 and greater to enable the “Automatic prompting for file downloads”:

hkcu\Software\Microsoft\Windows\CurrentVersion\internet Settings\Zones (zone 2 is trusted sites which is relevant for my companies intranet environment).

The key 2200 is Downloads: Automatic prompting for file downloads 0=enable, 3 = disable.

AutomaticPromptingForFileDownloads

It seemed a reboot was necessary to pick up this change, after which all seemed better.

CSS li Columns

24. October 2013 09:49

I ran across a neat trick to render a <ul> element’s list items (<li>) in columns. It was a lot simpler than I thought it would be. Just float the <li> with a width that will give you the number of columns desired.

 

 

For example to render a two column list set the width to 50%:

ul.twoColumns li { float:left; width:50%; }

For a five column list:

ul.fiveColumns li { float:left; width:20%; }

Check out the jsfiddle example.

64 Bit Detection

22. May 2012 17:53

 

I’ve used this code on a few projects which needed to know if they were running on a 64 bit host.

 

  Private Shared Function Is64BitOs() As Boolean
        If IntPtr.Size = 8 Or (IntPtr.Size = 4 And Is32BitProcessOn64BitProcessor()) Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Shared Function Is32BitProcessOn64BitProcessor() As Boolean
        Dim retVal As Boolean = False
        Try
            ' clear any exising error
            SetLastErrorEx(0, 0)
            If IsWow64Process(Process.GetCurrentProcess.Handle, retVal) Then
                Return retVal
            Else
                ' function call failed
                Throw New Exception(String.Format("IsWow64Process failed, lastError = {0}", Marshal.GetLastWin32Error))
            End If
        Catch ex As Exception
            Throw
        End Try
    End Function

    <DllImport("Kernel32.dll", SetLastError:=True, CallingConvention:=CallingConvention.Winapi)> _
    Private Shared Function IsWow64Process( _
                    ByVal hProcess As IntPtr, _
                    ByRef wow64Process As Boolean) As <MarshalAs(UnmanagedType.Bool)> Boolean

    End Function

    <DllImport("user32.dll")> _
    Private Shared Sub SetLastErrorEx(ByVal dwErrCode As UInteger, ByVal dwType As UInteger)
    End Sub

Initialize Object Instance From Dataset Using Reflection

14. April 2012 17:21

 

I was recently working on a project which has an existing legacy data access layer. The data access layer retrieves fifteen or so configuration and settings datatables which are then used throughout the system. There is a lot of code dealing with the dataset containing these tables retrieving a table, looping through the rows, etc. A decision was made to turn these tables into classes to reduce the effort of retrieving a single property as well as make the code more readable and easier to follow.

 

Creating the configuration classes is pretty straight forward. Populating them from the dataset also turned out to be pretty easy using reflection. But before going there, lets look at one less than ideal way to go about it. And stepping back just bit, here are a couple of simple example configuration classes used in the examples.

 

Public Class Square

    Public Property SideLength As Double

    Public Property FillColor As String

    Public Overrides Function ToString() As String
        Return String.Format("Square SideLength:{0}, FillColor:{1}", Me.SideLength, Me.FillColor)
    End Function

End Class

Public Class Circle

    Public Property RadiusLength As Double

    Public Property OutlineColor As String

    Public Overrides Function ToString() As String
        Return String.Format("Circle RadiusLength:{0}, OutlineColor:{1}", Me.RadiusLength, Me.OutlineColor)
    End Function

End Class

Now for a less than ideal way to populate the properties of a couple of instances of these classes from a dataset. In the below code, there is a basic loop through the data table and a case statement to set each property. In this simple example it isn’t a huge amount of code and hassle, but the real system has many more tables, with many more properties.

 

        Dim square As Square = New Square()
        Dim circle As Circle = New Circle()

        Dim ds As DataSet = CreateNameValueDataSet()

        For Each dr As DataRow In ds.Tables("Square").Rows
            Select Case dr("Name")
                Case "SideLength"
                    square.SideLength = CType(dr("Value"), Double)
                Case "FillColor"
                    square.FillColor = CType(dr("Value"), String)
                Case Else
                    Console.WriteLine("Unrecognized square property: {0}", dr("Name"))
            End Select
        Next

        For Each dr As DataRow In ds.Tables("Circle").Rows
            Select Case dr("Name")
                Case "RadiusLength"
                    circle.RadiusLength = CType(dr("Value"), Double)
                Case "OutlineColor"
                    circle.OutlineColor = CType(dr("Value"), String)
                Case Else
                    Console.WriteLine("Unrecognized circle property: {0}", dr("Name"))
            End Select
        Next

 

Now using a little reflection, the code is much simpler.

 

Dim square As Square = New Square()
Dim circle As Circle = New Circle()

Dim ds As DataSet = CreateNameValueDataSet()

PopulateInstanceFromDataTable(ds.Tables("Square"), square)

PopulateInstanceFromDataTable(ds.Tables("Circle"), circle)

And the PopulateInstanceFromDataTable is where the reflection fun comes in. The method uses the GetProperties method to retrieve the properties of the passed in type, and then assigns the value of the data row with a matching name to the property.

 

    Private Sub PopulateInstanceFromDataTable(dt As DataTable, ByRef nvInstance As Object)

        If dt Is Nothing Then Throw New ArgumentNullException("dt")

        Dim pi() As Reflection.PropertyInfo = 
nvInstance.GetType().GetProperties(Reflection.BindingFlags.DeclaredOnly 
Or Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance)

        If dt.Columns.Contains("Name") AndAlso dt.Columns.Contains("Value") Then

            For Each dr As DataRow In dt.Rows

                If IsDBNull(dr("Name")) = False AndAlso IsDBNull(dr("Value")) = False Then

                    Dim propName As String = dr("Name")

                    For Each i As Reflection.PropertyInfo In pi

                        If i.Name.Equals(propName, StringComparison.InvariantCultureIgnoreCase) Then

                            ' todo deal with other expected types here
                            If i.PropertyType.Equals(GetType(Double)) Then
                                i.SetValue(nvInstance, Double.Parse(dr("Value")), Nothing)
                            Else
                                i.SetValue(nvInstance, dr("Value"), Nothing)
                            End If

                            Exit For

                        End If

                    Next

                End If

            Next

        End If

    End Sub

This function (one very similar) to it saved me a bunch of time and code to solve the problem of populating all of the configuration classes from the dataset.

403 error using MVC on IIS6

17. November 2011 19:04

 

Here is an easy workaround that worked for me without any other changes on the server.

Open IIS snap in and select properties of virtual directory

Click Configuration... button on Virtual Directory tab

In the Mappings tab, click Insert... in the Wildcard application maps (order of implementation):

Enter the the full path to the 4.0 aspnet_isapi.dll (i.e. C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll)

make sure the 'Verify that file exists' is unchecked.

 

Starguy Online | Ramblings of a code slinger

About the author

I'm a .NET developer, a husband and a father of three beautiful girls.

Month List