Switch app from SQL Server 2008 DB to LocalDb

Can I at least see their answer?  They may have solved my problem.

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;

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" />

You need to specify the file name for LocalDB.

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?

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.

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?

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

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.

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

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?

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.

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.

Can you show me how that form works?  What is the important code that pulls information?

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

Select allOpen in new window

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

Without looking through all the code, what is SqlDataSourceEnumerator?

it lists all the available servers (localdb does not show up in the list) and I use it to populate the combobox

Thanks for all of the help.  You were very responsive and I appreciate the effort you put in.
Thank You!

Does the SqlDataSourceEnumerator use a Microsoft library, like Server Management Objects (SMO)?