none
what dll is to be included for SQL server connection to localdb MDF file RRS feed

  • Question

  • I was still using the Server CE included file in my apps for SDF database. With MDF I have a connection string to SQL server database file. What can/ should I include in my project?
    Friday, February 22, 2019 3:11 AM

All replies

  • https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-2016-express-localdb?view=sql-server-2017

    Localdb is cutdown version of the MS SQL Server Express database engine, which is used by software developer to program against and access a detached MDF file from the database engine.

    Each version of MS SQL Server Express has its own Localdb that must be installed. Visual Stuido should have installed Localdb, becuase VS's Service Based Database feature creates a  MSSE detached MDF file. 

    Your program must have project reference to the below DLL to use any version of MS SQL Server or Express. 

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.7.2

    • Edited by DA924x Friday, February 22, 2019 6:29 AM
    Friday, February 22, 2019 6:21 AM
  • Thanks. 

    I thought the connection string was created from the connection wizard.   What seems to be the trouble is I don't have SQL Server Express avaible on another machine and need to include a reference to it so it can be downloaded if necessary.

    When I was in 2012 I was using SQL CE and included it.  The new install prompted the download and it works great.

    • Edited by -tE Friday, February 22, 2019 9:18 AM
    Friday, February 22, 2019 7:49 AM
  • I thought the connection string was created from the connection wizard.

    I don't know. I don't use Mr. Wizard. I make the connectionstring manually that I am going to use for the program.

    What seems to be the trouble is I don't have SQL Server Express avaible on another machine and need to include a reference to it so it can be downloaded if necessary.

    I don't understand. Yes you can download the Localdb MSI and install Localdb on a machine, which is limited to the machine you have installed Localdb as to a program running on the machine can access the MDF file based on the Localdb being part of the connectionstring configuration. 

    Friday, February 22, 2019 12:42 PM
  • Thanks. 

    I thought the connection string was created from the connection wizard.   What seems to be the trouble is I don't have SQL Server Express available on another machine and need to include a reference to it so it can be downloaded if necessary.

    When I was in 2012 I was using SQL CE and included it.  The new install prompted the download and it works great.

    That is what I was talking about in regards to using the "connection wizard", it does not understand SQL-Server 2017 that way but when you create your own connection in code that will work in regards to reading, writing, removal operations. 

    Using the following page search for localdb for options for connection strings.

    https://www.connectionstrings.com/sql-server/

    You could try my wizard which is 99% Microsoft code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 22, 2019 1:05 PM
    Moderator
  • This is the connection string that was generated by attaching the MDF.

    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="F:\Visual Basic\Payroll\Payroll\ShippingLabels.mdf";Integrated Security=True;Connect Timeout=30

    I have written many connection strings in PHP,  but I have no idea how the designer is not doing this for me.  It doesn't seem like the connection string is the problem,  but that the target PC might not have the correct SQL available. 

    Other apps using SQL Server CE and SDF files asked to download the plugin when they are installed.  Once one app did this,  the others just installed.  And I assume they are being updated if need be. 

    If I am to write the actual connection strings,  why even have the designer,  binding, and tableadpaters?

     

    As before,  I have no reference to any SQL in my imported references.  I DID with SQL Server CE.  It copied to local path,  and then would ask to be installed from the web.  Isn't that what I need here?
    • Edited by -tE Friday, February 22, 2019 5:47 PM
    Friday, February 22, 2019 5:25 PM
  • In regards to the target PC not having the correct version of SQL-Server that is very possible. With a bit of expertise more than one version can be installed as on my machine yet it was not a quick operation either.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 22, 2019 6:54 PM
    Moderator
  • So how should I approach this?  How do I know what the user has and location etc.  if I don't ask for it to install.  I understood how VS 2010  or 2012 embedded the SDF.  But that is  not an option now.  As I said,  I don't get that it is MORE challenging.  I would think there is an SDK for SQL Server Express that has the appropriate dll.  As VS installed everything IT needs so you are immediately presuming that it should embed this.

    All I did was build a dataset and it chose what it used to connect to it.  Then built the tables and used the designer to create the table adapters and binding sources.  Should be simple.

    Another point.  I thought that the SDF dataset was embedded in the ClickOnce App.  It was using the SQL program to connect but that all the queries were written "inside".  The process I have gone through to create the MDF was the same.  But will not allow use of SQL Server CE.

    When designing,  should I reference the Server Compact Edition before I create a db?  I have that sdk,  but it isn't an option in connecting to my dataset.

    I see the namespace for System.Data.SQLclient.  I have to edit a few lines of code but it excepts it.  But,  nothing is in my reference window pointing to that dll.  This is my confusion.  Seems like it would be added after the dataset designer builds the MDF.

    • Edited by -tE Friday, February 22, 2019 8:22 PM
    Friday, February 22, 2019 7:55 PM
  •   I would think there is an SDK for SQL Server Express that has the appropriate dll.  As VS installed everything IT needs so you are immediately presuming that it should embed this.

    What VS is doing, what MS SQL Server is doing and what ADO.NET is doing are different things.

    https://en.wikipedia.org/wiki/ADO.NET

    All I did was build a dataset and it chose what it used to connect to it.  Then built the tables and used the designer to create the table adapters and binding sources.  Should be simple.

    It would be simpler if you were not using Mr. Wizard to do it, becuase anything you are having Mr.Wizard do, you can do the same thing in code manually yourself.

    Another point.  I thought that the SDF dataset was embedded in the ClickOnce App.  It was using the SQL program to connect but that all the queries were written "inside".  The process I have gone through to create the MDF was the same.  But will not allow use of SQL Server CE.

    A MDF file can be standalond where the MDF is accesable programming wise through LocalDB, which you where given the link about Localdb. Localdb is a single user instance of MS SQL Server Express, a limited DB engine that can obly be used for a MDF file that is setting locally on a computer. LocalDB is only usable locally on a computer, and it can only be used by the single user logoged on to the computer. LocalDB with the MDF file that is deteached from the MS SQL Server DB engine is a standalone single user solution that is only viable locally on the computer.

    As opposed to the MDF file that can be attached to the MS SQL Server or MS SQL Server Express DB engine making the MDF access concurrently by multiple users remotely, becuase MS SQL Server was installed on a Windows server classed O/S or MS SQL Server Express was installed on a Windows workstation classed O/S. 

    When designing,  should I reference the Server Compact Edition before I create a db?  I have that sdk,  but it isn't an option in connecting to my dataset.

    The Sever Compact Edition has nothing to do with MS SQL Server Express LocalDb that is installed on a machine for local software developer usage, MS SQL Server Express that is installed on a workstation classed computer or MS SQL Server that is installed on a server classed computer.

     Many times a software developer will just install the full version of MS SQL Server Express on their machine, create a database, an attached MDF file to the DB engine, deteach the MDF file, and have it moved to a location where a DBA will take the detached MDF file and attach it to MS SQL Server on a server classed O/S machine in the test, QA and production environments.  I see the namespace for System.Data.SQLclient.  I have to edit a few lines of code but it excepts it.  But,  nothing is in my reference window pointing to that dll.  This is my confusion.  Seems like it would be added after the dataset designer builds the MDF.

    If System.Data is there, then the program has reference to System.Data.SQLClinet, which is part of the System.Data namespace that is controlled by ADO.NET. All database providers provide drivers/DLL(s) that are controlled by ADO.NET, which can be under a namespace that the DB provider makes.

    Friday, February 22, 2019 10:18 PM
  • OK.  I have imported MySQL and used it to connect to a web server.  I have been so coosh from the Data designer I was a little unprepared to build this.  I have a start of a connection but it isn't even throwing an error in a try/catch.

    Imports System.Data.SQLClient
    
            Dim sqlConn As System.Data.SqlClient.SqlConnection
            Dim Server As String = "(localdB)"
            Dim ID As String = ""
            Dim PasswordDb As String = ""
            sqlConn = New SqlConnection()
            sqlConn.ConnectionString = "server=" & Server & ";" & "user id=" & ID & ";" & "password=" & PasswordDb & ";" & "Integrated Security=true;AttachDbFileName=MDFname"
    
    
            Try
    
                sqlConn.Open()
    
                If sqlConn.State = ConnectionState.Open Then 
    
                Dim stm As String = "SELECT *FROM TableName"
                Dim cmd As SqlCommand = New SqlCommand(stm, sqlConn)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                While reader.Read()
    
    
                    MsgBox(reader.GetString(0))
    
                End While
    
    
                sqlConn.Close()
    
                 end if
    
            Catch myerror As SqlException
    
                MsgBox(myerror)
    
            End Try

    There is no id or password.  I just left them blank when I built the MDF

    Do I need to copy the MDF to a location like such or create it by copying it?  This is what I am confused about.  What does the client side need? 




    • Edited by -tE Saturday, February 23, 2019 7:05 PM
    Saturday, February 23, 2019 5:42 PM
  • OK.  I have imported MySQL and used it to connect to a web server.

    I doubt that you are going to find someone that knows MySQL in a MS forum. Maybe someone will respond that knows MySQL.

    I don't understand how you could start using MySQL as opposed to MS SQL Server Express.

    Anyway, the MySQL forum is below. 

    https://forums.mysql.com/

    On the other hand, I don't know what a MySQL database file type is, but I doubt that it's a MDF file type that is a dedicated to MS SQL Server usage.

    To me, you seem to have leaped out here with no water in the pool. Water in the pool would be that you do some tutorials on MS SQL Server, or if using MySQL, then MySQL tutorials so that you have some kind of basic knowledge on how to program against either database platform.


    Saturday, February 23, 2019 9:04 PM
  • I was bad in my comment.  I have used a MySQL dll in other apps.  So I understand writing all the connection strings.  My topic though was I don't understand where this MDF is actually located now.   As before I was including a dll for SQL Server Compact,  which I had downloaded the SDK.  And when the app was being installed it asked to download the latest version of it from the reference I had added.  All was created in the VS engine. I was assuming that the SDF was embedded in the application itself and not external.

    Now,  with the MDF,  and SQL Express,  I am looking for the same process.  Is that wrong?  I didn't have to tell VS Express 2012 to attach the dataset to a server. And write all my own strings.  Why would the MDF creator and table designer even be in here if I have to do all that?  I am looking for the solution to get the client side the right SQL downloaded.  My app runs great, but installed on a client,  is failing and asking for SQL Express.  I have located the link to download it,  but thought it has to already  be installed here in development because I am already obviously using it. 

    • Edited by -tE Saturday, February 23, 2019 10:58 PM
    Saturday, February 23, 2019 10:55 PM
  • Now,  with the MDF,  and SQL Express,  I am looking for the same process.  Is that wrong? 

    Yes you are wrong, becuase MS SQL Server Express is not MS SQL Server Compact.

    https://www.neovera.com/sql-server-express-use/

    <copied>

    SQL Server Express LocalDB – Do you need to embed SQL Server Express into an application? LocalDB is a lightweight version of Express that has all its programmability features yet runs in user mode and has a fast, zero-configuration installation.

    <end>

    https://blogs.msdn.microsoft.com/jerrynixon/2012/02/26/sql-express-v-localdb-v-sql-compact-edition/

    All of the recent versions of MS SQL Server Express have a Localdb installation option,  or one can find the MSI and install LocalDB.

    https://www.sqlshack.com/install-microsoft-sql-server-express-localdb/

    https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

    https://www.sqlshack.com/how-to-connect-and-use-microsoft-sql-server-express-localdb/

    I didn't have to tell VS Express 2012 to attach the dataset to a server.

    I suggest that you stop using VS Express that I stopped using long ago starting with VS2012 Community. which is free too. Now I am using VS 2017 Community.

    Click the install button and go to VS 2017 Community install process.

    https://docs.microsoft.com/en-us/visualstudio/install/install-visual-studio?view=vs-2017

    VS Community like the professional versions of Visual Stuido has what is known as Service Based Database item that can be added to the VS VB.NET project that is no more than a standalone   MS SQL Server Express MDF file that is using Localdb that is detached from the MS SQL Server Express DB engine, which means VS Community will install Localdb.

    https://www.youtube.com/watch?v=2qeq5hGZvEc

    I suggest that you read the information in the links, stop pussy-footing around and understand what you are dealing with here.



    • Edited by DA924x Sunday, February 24, 2019 12:06 AM
    Sunday, February 24, 2019 12:03 AM
  • I dig it.  I am using VS Community 2017 and have been.  But this is my first attempt setting up an MDF.  When I had to make dB changes I just used 2012.  Sorry bout not making this understandable. I have download the install exe for Server Express.  Can I install Express localdb and get the dll I need?  I recently reinstalled VS 2017 and may have not installed all I could use. It would seem I did since I can use the SQLClient.  Installing it is the only way to get to the dll?  VS let my reference the Install.exe,  but I would think I could reference just the locadb dll  and would prompt the client to just download that component. Its been quite some time since I add reference from start. Have just been referencing what I needed.



    Append:  Just use the VS Installer to see what was already installed.  It says the 2016 express localdb is there.  Which I figured.  I can't locate that folder.
    • Edited by -tE Sunday, February 24, 2019 2:16 AM
    Sunday, February 24, 2019 1:31 AM
  • and get the dll I need?  

    https://www.nuget.org/packages/System.Data.SqlClient/

    Append:  Just use the VS Installer to see what was already installed.  It says the 2016 express localdb is there.  Which I figured.  I can't locate that folder.

    Just know that Localdb is there no matter how it is installed. I'll assume that you are now seeing the 3 ways Localdb can me installed on a computer, which is via Visual Stuido, the Localdb.MSI file or through an option that is selected when installing MS SQL Server Express on a computer.

    You  best figure out where the MDF files is going to be located for Localdb in case you ever want to move the file to the MS SQL Server Data directory and use SSMS to 'Attach' the file to the MS SQL Server DB engine, which using this process stops the usage of Localdb,  or you want the MDF file moved to another location and use the Localdb 'Attach file'  in the connectionstring giving the path where the MDF file is located, which would be done if you deployed the detached MDF file to another computer with your program that used the embedded MDF file. Of course,  Localdb would have to be installed on the computer too in order for your program that was deployed/installed to another computer along with the MDF file it uses. 

    https://www.sqlshack.com/install-microsoft-sql-server-express-localdb/

    <copied>

    The system database files for the database are, by default, stored under this location: C:\Users\<User_Name>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances

    <end>

    https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-getting-started-with-the-database-engine?view=sql-server-2017


    • Edited by DA924x Sunday, February 24, 2019 3:22 AM
    Sunday, February 24, 2019 3:22 AM