Solved

Switch app from SQL Server 2008 DB to LocalDb

Posted on 2014-01-15
21
1,185 Views
Last Modified: 2014-01-18
I have a vb.net windows form application that currently attaches to a SQL Server 2008 database.
I have installed LocalDb and created a copy of the structure and data from the original db.
The app uses the app.config file to get the settings for connecting to the db.

How can I change the app so that it connects to the LocalDb database?
I have tried adding the connection string to the app.config but the app doesn't seem to be recognizing the instance or server.  I have use (localdb)\v11.0 and also tried using the named pipes as the server name, but again the application doesn't seem to be recognizing the instance or server.

Any help with samples would be appreciated.  Thanks in advance.
0
Comment
Question by:PSCTECH
  • 11
  • 9
21 Comments
 

Author Comment

by:PSCTECH
ID: 39784177
Can I at least see their answer?  They may have solved my problem.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39785301
Can you show me the connection string that you are using?

SQL Server Connection Strings
http://www.connectionstrings.com/sql-server/

Example:
Server=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyFolder\MyData.mdf;
0
 

Author Comment

by:PSCTECH
ID: 39786482
Here's the string that works with SQL Server 2008:
 connectionString="Data Source=MIKE-PC;Initial Catalog=ISISdemo;Integrated Security=True"
 providerName="System.Data.SqlClient" />

Here are the 2 strings I tried for LocalDb: (the 2nd one uses named pipes.)

connectionString="Data Source=np:\\.\pipe\LOCALDB#874972DC\tsql\query;Initial Catalog=ISISdemo;Integrated Security=True"
      providerName="System.Data.SqlClient"

connectionString="Data Source=(localdb)\v11.0;Initial Catalog=ISISdemo;Integrated Security=True"
      providerName="System.Data.SqlClient" />
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39786803
You need to specify the file name for LocalDB.
0
 

Author Comment

by:PSCTECH
ID: 39787051
Ok, I'll try that.
Does it matter that SSMS 2208 R2 required named pipes as the Server Name?  It wouldn't recognize (localdb)\v11.0.
Should I take that into consideration?
Does the instance need to be started before it can be recognized?

Also, I have a Database Configuration form that allows the user to choose from different instances/servers and databases.  This form doesn't show the localdb as an option.

On the Database Configuration form the code that I'm using to get the available instances and display them in a combobox is:  SqlDataSourceEnumerator.Instance.GetDataSources
Shouldn't the localdb be viewable?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39787140
Local DB SQL Server files work differently than standard SQL Server instances.

Reference:

SQL Server 2012 Express LocalDB
http://technet.microsoft.com/en-us/library/hh510202.aspx

Microsoft SQL Server 2012 Express LocalDB is an execution mode of SQL Server Express targeted to program developers. LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once LocalDB is installed, developers initiate a connection by using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex or time consuming configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server. An instance of SQL Server Express LocalDB is managed by using the SqlLocalDB.exe utility.
0
 

Author Comment

by:PSCTECH
ID: 39787261
OK,  That's why I'm asking for help.
So are you saying I can't incorporate it into my existing windows form app?
I've read the link you provided, but my question is can I use my existing forms for changing servers and dbs by including another connection string in my config file or do I have to do something different?
Why won't my app recognize the localdb?

Do I have to add it from the VS ide and include it in my project?
Will my n-tier directives recognize it?

I'm completely lost here.  I thought since it was an .mdf file all of the references/strings/etc in my app would follow suit.  

Why doesn't the server get listed?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39787507
SQL Server Express is the instance...Local DB is a special extension of Express.  You can connect to a .mdf file like it is a regular SQL Server database, without having to attach to it.  

In order to get the proper connection, you need to use the correct form of the connection string.  

Here is a walk-through from M$DN:

Walkthrough: Creating a Local Database File in Visual Studio
http://msdn.microsoft.com/en-us/library/ms233763.aspx
0
 

Author Comment

by:PSCTECH
ID: 39789259
I'm not trying to be a pain here, but the application uses a form which allows the user to choose the datasource at login time.
Can I have them attach to localdb instead and NOT have to recode the entire project?

If they change to another SS 2008 db, everything works just fine.  I want the same flexibility using Localdb.  So one user can login and use a SS 2008 db and the next user can login and choose Localdb and all of the existing forms, methods, functions etc will still work.
0
 

Author Comment

by:PSCTECH
ID: 39789581
OK, so here's what I am facing now.
I replaced the connection string in my app.config with:
  <add name="DefaultConnection"  
         connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename='|DataDirectory|ISISCommercial.mdf';Integrated Security=True"
providerName="System.Data.SqlClient"/>

This seems to be working, but I can only get my application to actually connect when I use the named pipes as the server name (localDB)\v11.0 doesn't work).
Sadly, the named pipes LOCALDB# changes everytime the instance starts so the value saved in my application is only valid for a one time use.  I hope that makes sense
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 39789662
I don't think that I asked what is the "driver" for this change?  What benefit are you hoping to realize with the Local DB?  It works differently than a standard instance of SQL Server, so a few of the tools won't recognize it.

"This seems to be working, but I can only get my application to actually connect when I use the named pipes as the server name (localDB)\v11.0 doesn't work)."

This sounds like a contradiction--is it working or not working with Local DB?
0
 

Author Comment

by:PSCTECH
ID: 39789748
Sorry about the confusion, I know I'm not being very clear.

The benefit we are looking for is a smaller footprint and simpler install for the customer.

The statement does sound like a contradiction (sorry about that).
I use an app.config file to store the connections strings in.  

Also, I have a form that the user can use to switch between databases.  
The form values are saved and used at next application run.

With the connection string I listed above in the app.config and the following values in the form, it allows me to connect, but only if I have the CURRENT named pipes value.  But I have to use the named pipes in the SQL Server Address combobox because using (LocalDB)\v11.0 doesn't work and the named pipes # changes every time the instance is started so the saved values from the previous session don't work anymore.

If I could get this to work using (LocalDB)\v11.0 or if the named pipes didn't change, I wouldn't even have to use this form unless I wanted to change databases since the saved data would be valid.

I appreciate your help and hope this helps clear up my issue.  Again, sorry for the confusion.

Database Config Form
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39789800
Is that database configuration form a hand-crafted form, or a Micro$oft dialog?  It doesn't look quite familiar to me.
0
 

Author Comment

by:PSCTECH
ID: 39789881
hand crafted, but using calls for available instances and dbs.  The settings entered are pulled from the available instances, then the available dbs in the instance and are then saved for the next login.

This is where the problem comes in.  Changing from a SS 2008 db to localdb works if I enter the named pipes and then it saves that info.

The next login pulls the saved info (the localdb named pipes), but since the named pipes # has changed, it won't connect.  I have tried to use (LocalDB)\v11.0 here, but no dice.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39789956
Can you show me how that form works?  What is the important code that pulls information?
0
 

Author Comment

by:PSCTECH
ID: 39790020
here's the code.  if you need more let me know.  You can disregard the Access and Oracle snippets (they haven't been implemented yet).

Imports System.Configuration
Imports BLL
Imports System.Data.Sql

Public Class frmDataConfig
    Dim strConn As String = ""
    Dim strTrusted As String = "true"
    Dim strProvider As String = ""

    Private Sub frmDataConfig_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Select Case AppConfigFileSettings.GetDTAppSettings("DBProviderType")
            Case "MSSQL"
                FillServerList()
                cbProvider.SelectedIndex = 0
                FillDatabaseList()

            Case "MSACCESS"
                cbProvider.SelectedIndex = 1

            Case "ORACLE"
                cbProvider.SelectedIndex = 2
        End Select
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        OpenFileDialog1.InitialDirectory = "c:\"
        OpenFileDialog1.Filter = "Access MDB (*.mdb)|*.mdb"
        OpenFileDialog1.FileName = ""

        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            txtACCdataPath.Text = OpenFileDialog1.FileName
        Else
            txtACCdataPath.Text = ""
        End If
    End Sub

    Private Sub rbSQLAuthWin_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbSQLAuthWin.CheckedChanged
        If rbSQLAuthWin.Checked Then
            txtSQLuid.Enabled = False
            txtSQLpass.Enabled = False
            txtSQLuid.Text = ""
            txtSQLpass.Text = ""
            strTrusted = "true"

        End If
    End Sub

    Private Sub rbSQLAuthServ_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbSQLAuthServ.CheckedChanged
        If rbSQLAuthServ.Checked Then
            txtSQLuid.Enabled = True
            txtSQLpass.Enabled = True
            strTrusted = "false"

        End If
    End Sub

    Private Sub LoadSQLinfo()
        cbSQLServs.Text = AppConfigFileSettings.GetDTAppSettings("SQLsource").ToString()
        cbSQLdbName.Text = AppConfigFileSettings.GetDTAppSettings("SQLdatabase")
        If AppConfigFileSettings.GetDTAppSettings("SQLtrusted") = "false" Then
            rbSQLAuthServ.Checked = True
            strTrusted = "false"
            txtSQLuid.Text = AppConfigFileSettings.GetDTAppSettings("SQLuid")
            txtSQLpass.Text = AppConfigFileSettings.GetDTAppSettings("SQLpass")
        Else
            rbSQLAuthWin.Checked = True
            strTrusted = "true"
        End If
    End Sub

    Private Sub LoadAccessInfo()
        txtACCdataPath.Text = AppConfigFileSettings.GetDTAppSettings("AccessDBpath")
    End Sub

    Private Sub cbProvider_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbProvider.SelectedIndexChanged
        Select Case cbProvider.SelectedIndex
            Case 0 'SQL Server
                strProvider = "MSSQL"
                gbSQL.Enabled = True
                gbAccess.Enabled = False
                LoadSQLinfo()
                cbSQLServs.Focus()

            Case 1 'Access
                strProvider = "MSACCESS"
                gbSQL.Enabled = False
                'gbAccess.Enabled = True
                'LoadAccessInfo()
                'btnBrowse.Focus()
                'change below after access class implemented
                MessageBox.Show("MS Access Database support has not been implemented at this time.", "Sorry...", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                cbProvider.SelectedIndex = 0

            Case 2 'Oracle
                strProvider = "ORACLE"
                gbAccess.Enabled = False
                gbSQL.Enabled = False
                'delete below after oracle classs implemented
                MessageBox.Show("Oracle Database support has not been implemented at this time.", "Sorry...", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                cbProvider.SelectedIndex = 0

        End Select
    End Sub

    Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
        Dim objBLLSystem As New BLLSystem

        'set conn string
        Select Case cbProvider.SelectedIndex
            Case 0 'SQL Server
                strConn = "data source=" & cbSQLServs.Text & ";database=" & cbSQLdbName.Text & ";trusted_connection=" & strTrusted & ";uid=" & txtSQLuid.Text & ";password=" & txtSQLpass.Text

            Case 1 'Access
                strConn = AppConfigFileSettings.GetDTAppSettings("AccessProvider") & txtACCdataPath.Text

            Case 2 'Oracle

        End Select

        'test conn string
        Try
            objBLLSystem.TestDataConn(strConn, strProvider)
            MessageBox.Show("Connection is Good!", "ISIS Data Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception
            MessageBox.Show("Connection Failed with the following Error: " & ex.Message, "ISIS Data Connection Test", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'set conn string
        Select Case cbProvider.SelectedIndex
            Case 0 'SQL Server
                strConn = "data source=" & cbSQLServs.Text & ";database=" & cbSQLdbName.Text & ";trusted_connection=" & strTrusted & ";uid=" & txtSQLuid.Text & ";password=" & txtSQLpass.Text
                AppConfigFileSettings.UpdateDTAppSettings("DBProviderType", strProvider)
                AppConfigFileSettings.UpdateDTAppSettings("SQLsource", cbSQLServs.Text)
                AppConfigFileSettings.UpdateDTAppSettings("SQLdatabase", cbSQLdbName.Text)
                AppConfigFileSettings.UpdateDTAppSettings("SQLtrusted", strTrusted)
                AppConfigFileSettings.UpdateDTAppSettings("SQLuid", txtSQLuid.Text)
                AppConfigFileSettings.UpdateDTAppSettings("SQLpass", txtSQLpass.Text)

            Case 1 'Access
                'strConn = ConfigurationManager.AppSettings("AccessProvider").ToString() & txtACCdataPath.Text
                'AppConfigFileSettings.UpdateAppSettings("DBProviderType", strProvider)
                'AppConfigFileSettings.UpdateAppSettings("AccessDBpath", txtACCdataPath.Text)

            Case 2 'Oracle
                'strConn = ""
                'AppConfigFileSettings.UpdateAppSettings("DBProviderType", strProvider)

        End Select
        AppConfigFileSettings.UpdateDTAppSettings("ConnectionString", strConn)

        'restart app
        Application.Restart()
    End Sub

    Private Sub FillServerList()
        Dim oTable As DataTable
        Try
            If cbSQLServs.Items.Count = 0 Then
                Cursor.Current = Cursors.WaitCursor
                oTable = SqlDataSourceEnumerator.Instance.GetDataSources
                For Each oRow As DataRow In oTable.Rows
                    If oRow("InstanceName").ToString = "" Then
                        cbSQLServs.Items.Add(oRow("ServerName"))
                    Else
                        cbSQLServs.Items.Add(oRow("ServerName").ToString & "\" & oRow("InstanceName").ToString)
                    End If
                Next
            End If
        Catch ex As Exception
        Finally
            Cursor.Current = Cursors.Default
        End Try
    End Sub

    Private Sub FillDatabaseList()
        Try
            Cursor.Current = Cursors.WaitCursor
            cbSQLdbName.Items.Clear()

            'set conn string
            Dim strCn As String
            strCn = "data source=" & cbSQLServs.Text & ";database=" & cbSQLdbName.Text & ";trusted_connection=" & strTrusted & ";uid=" & txtSQLuid.Text & ";password=" & txtSQLpass.Text

            Dim objSys As New BLLSystem()
            Dim oTable As DataTable = objSys.GetDataBaseList(strCn)

            For Each oRow As DataRow In oTable.Rows
                If oRow("database_name").ToString.Substring(0, 4) = "ISIS" Then
                    cbSQLdbName.Items.Add(oRow("database_name"))
                End If
            Next
        Catch ex As Exception
        Finally
            Cursor.Current = Cursors.Default
        End Try

    End Sub

    Private Sub btnRefreshDB_Click(sender As System.Object, e As System.EventArgs) Handles btnRefreshDB.Click
        cbSQLdbName.Text = ""
        FillDatabaseList()
        If cbSQLdbName.Items.Count > 0 Then cbSQLdbName.SelectedIndex = 0
        cbSQLdbName.Focus()
    End Sub

    Private Sub cbSQLServs_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbSQLServs.SelectedIndexChanged
        cbSQLdbName.Text = ""
        FillDatabaseList()
        If cbSQLdbName.Items.Count > 0 Then cbSQLdbName.SelectedIndex = 0
        cbSQLdbName.Focus()
    End Sub
End Class

Open in new window


if it helps heres the form
frmDataConfig.Designer.vbfrmDataConfig.resxfrmDataConfig.vb
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39790123
Without looking through all the code, what is SqlDataSourceEnumerator?
0
 

Author Comment

by:PSCTECH
ID: 39790769
it lists all the available servers (localdb does not show up in the list) and I use it to populate the combobox
0
 

Author Closing Comment

by:PSCTECH
ID: 39791470
Thanks for all of the help.  You were very responsive and I appreciate the effort you put in.
Thank You!
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39791607
Does the SqlDataSourceEnumerator use a Microsoft library, like Server Management Objects (SMO)?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now