none
MDF file - correct way to use it RRS feed

  • Question

  • Hello,
    big problem.
    I developed it under VS2017, C#. Everything works with the studio. Not on the client side anymore.
    I use this connectionstring. Is not the file.

    Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True;Connect
    Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;
    MultiSubnetFailover=False

    All OK, I get the data.

    On Client site nothing work.
    What is the correct connection string?

    I have to copy the file and then place the link. How do I do this correctly? How do I get the connection string. This is
    from google.

    Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|TestDB.mdf;
    Integrated Security=True

    My goal is to make a connection to the TestDB.mdf file on client PC.
    What is there to do? What do I need to install?
    The easiest way is I create the database with VS2017 and however I can copy the connection string to clipboard.

    C:\Users\Freitag\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB
    I have SQL Express, so I develop the application. Afterwards it goes to the client. What and how must I deliver?
    Is there a straightforward description anywhere?

    I've asked in the past, now I have to change something and I can't do it anymore. Please help me again. Thanks in advance.

    Monday, March 2, 2020 5:29 PM

Answers

  • What we do for one of our internal apps is to place the database under

    C:\Users\UserName\AppData\Roaming\AppName\Data.

    In the connection class the DataDirectory is set e.g.

    public SqlServerConnectionsLocal()
    {
        AppDomain.CurrentDomain.SetData("DataDirectory",
            Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
    
    }

    Then create a connection string.

    public string ConnectionStringLocalDb => 
        "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" +
        "|DataDirectory|\\TAPS\\Data\\TAPS_local.mdf; " +
        "Integrated Security=True;MultipleActiveResultSets=True";

    Then work with an installer such as this one

    https://marketplace.visualstudio.com/items?itemName=VisualStudioClient.MicrosoftVisualStudio2017InstallerProjects


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 3, 2020 7:45 PM
    Moderator
  • I want to install these files on the client PC.
    What exactly do I have to install? And how do I get the connection to my database?
    SQL Server 2019 Express or 2017 Express is free and nearly the same for my small table not relevant, right?

    Is there a clear guideline of what to install, how the connection must be made. There are so many hits, links but no clear procedure.

    You simply make a folder and put the LDF and MDF files in the and make shure the folder has all access rights for CRUD.

    Again you have to install Localdb on the client's machine, which has to be the version of SQL Server Express used by Visual Stuido.

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

    https://stackoverflow.com/questions/35186375/how-do-i-know-what-version-of-sql-server-express-my-database-is-in

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

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

    Tuesday, March 3, 2020 7:52 PM
  • I don't know anything about some wizard in VS that's make a stored procedure. It's uaually done with SSMS.

    Hello,

    OK, thanks. With SSMS, Do you know a good tutorial? 

    Greetings Markus


    I should ask the question in the MS SQL Server forums.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?forum=sqlexpress

    Thursday, March 5, 2020 1:22 PM

All replies

  • LocalDB is a local instance of SQL that can be installed (and is by VS). If you want to use a database on a client machine then they need to have SQL Server installed as well. For a client machine you'll like use SQL Express. If there is a network instance of SQL running you can use it instead. For SE you can point it to a MDF file that is shipped as part of your app. The connection string is almost identical to your existing one but you have to modify the server name because it isn't using LocalDB anymore. Unfortunately SQL Express allows a user to name the instance so you'll have to get the client to give you the instance name (or they need to know it). SE ships with a default one so here's what it might look like for that as defined here.

    Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|TestDB.mdf;
    Integrated Security=True;User Instance=True

    Note that user instances on SE were deprecated for 2012+.

    LocalDB is usable on client machines and is the newer version of Express but it has to be explicitly selected by the client as discussed here. Once installed you will again need to change the Data Source value to match whatever the installation used. It is generally of the form `(LocalDB)\<name>` but, again, that is determined at installation time.

    Unfortunately we have no way of telling you what the actual server/instance name is on a client because it can vary by client. If you are using an installer then you can install the SQL instance yourself and control that information but otherwise you'll have to get it from the client.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, March 2, 2020 6:52 PM
    Moderator
  • Did you install Localdb on the client machine based on the version of MS SQL Express the MDF file originated from? The are MSI installation files for versions of Localdb base on the MS SQL Server version the MDF belongs to. VS2017 uses a certain version of MS SQL Server Express, which you can find out the version.

    You can make a directory not in Programs Directory, recommended, and deploy the MDF file to the directory and use Localdb on the connectionstring and point to the location of the MDF file.

     
    • Edited by DA924x Monday, March 2, 2020 9:50 PM
    Monday, March 2, 2020 6:59 PM
  • Hi Markus,

    Thank you for posting here.

    Based on my search, I found some posts similar to yours and see if the responses there are helpful to you.

    How to access .mdf file from client

    How To Access .mdf File From Client machine

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 3, 2020 7:29 AM
  • Hi Markus Freitag,

    First of all you need to install SQL Server Management Studio to make it easier to deal with Sql Server DataBase you can find it at link below

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

    After SSMS has installed create an account with a password for your database if you want more security !

    In the clients applications use below connection string you can putting Server name or IP address to Data Source part with your account security information

    <add name="YouConnectionStringName" connectionString="Data Source=10.0.0.5\SQLEXPRESS;Initial 

    Catalog=YourDataBaseName;Persist Security Info=True; User ID=YourUserID;Password=YourPassword" providerName="System.Data.SqlClient"/>
    I have a server that has SQLSERVER EXPRESS installed on and  Clients application can easily exchange their data.


    Please remember to mark the replies as answers if they helped you :) ~


    Tuesday, March 3, 2020 7:58 AM
  • First of all you need to install SQL Server Management Studio to make it easier to deal with Sql Server DataBase you can find it at link below

    One can use SSMS or one can use Server Explorer in Visual Stuido to access any MS SQL Sever database table including one in Localdb.

    Tuesday, March 3, 2020 8:05 AM
  • Dear All,
    Thank you very much for the answers.
    I created the database in VS2017, on my PC it runs very well,

    The database is located in this directory.1

    What have I done?

    I close all, only then I can copy the two files to the project folder.
    Like this.2

    The best thing for me would be if this database could also be located at this place during development.
    What would I have to do there?

    3

    I want to install these files on the client PC.
    What exactly do I have to install? And how do I get the connection to my database?
    SQL Server 2019 Express or 2017 Express is free and nearly the same for my small table not relevant, right?
    Is there a clear guideline of what to install, how the connection must be made. There are so many hits, links but no clear procedure.

    // ----------------------44

    Or do I have to install the Express Version on the client PC and copy the database files into the directory. They must be there, there is no other place.
    On my development PC is still the higher SQL version installed.
    I can copy this two files and should be enough?

    This is too big.

    https://www.mssqltips.com/sqlservertip/5654/sql-server-2017-step-by-step-installation-guide/

    I can use this, is compatible with SQL Server 2012

    https://go.microsoft.com/fwlink/?linkid=866658

    Thanks for your help. Hope now is clear what I'm looking for.
    Greetings Markus.

    Tuesday, March 3, 2020 6:00 PM
  • What we do for one of our internal apps is to place the database under

    C:\Users\UserName\AppData\Roaming\AppName\Data.

    In the connection class the DataDirectory is set e.g.

    public SqlServerConnectionsLocal()
    {
        AppDomain.CurrentDomain.SetData("DataDirectory",
            Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
    
    }

    Then create a connection string.

    public string ConnectionStringLocalDb => 
        "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" +
        "|DataDirectory|\\TAPS\\Data\\TAPS_local.mdf; " +
        "Integrated Security=True;MultipleActiveResultSets=True";

    Then work with an installer such as this one

    https://marketplace.visualstudio.com/items?itemName=VisualStudioClient.MicrosoftVisualStudio2017InstallerProjects


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 3, 2020 7:45 PM
    Moderator
  • I want to install these files on the client PC.
    What exactly do I have to install? And how do I get the connection to my database?
    SQL Server 2019 Express or 2017 Express is free and nearly the same for my small table not relevant, right?

    Is there a clear guideline of what to install, how the connection must be made. There are so many hits, links but no clear procedure.

    You simply make a folder and put the LDF and MDF files in the and make shure the folder has all access rights for CRUD.

    Again you have to install Localdb on the client's machine, which has to be the version of SQL Server Express used by Visual Stuido.

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

    https://stackoverflow.com/questions/35186375/how-do-i-know-what-version-of-sql-server-express-my-database-is-in

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

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

    Tuesday, March 3, 2020 7:52 PM
  • Hello DA924x,

    Yes looks good.
    Which variant do you prefer? I think the silence is sufficient. I want to read and write data, nothing more.prefer ....... ? Local ?

    Good to know!good - thanks!

    //1) Click on server explorer and found your database
    //2) right click on the database of your solution
    //3) on the menu click for a new query
    //4) on the new query type: select @@version
    //5) read for your db version
    
    //Silent installation of Microsoft SQL Server Express LocalDB
    
    //Microsoft SQL Server Express LocalDB supports silent installation.
    //A user should download SqlLocalDB.msi and run the Command Prompt 
    //window as an administrator. Then, they should paste the following command:
    
    //msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES
    //The next step is pressing the Enter key from the keyboard: 
    
    //A user should make sure that the path in which SqlLocalDB.msi is 
    //located is correct, otherwise the following error may appear: 
    
    https://www.sqlshack.com/install-microsoft-sql-server-express-localdb/

    Maybe you can give me a quick answer and then we can close the thread.

    Last question, maybe new thread.
    Can I create a query or a stored procedure via VS2017 using a wizard?

    Greetings Markus


    Wednesday, March 4, 2020 5:38 PM
  • I would just go with option #3. 

    Last question, maybe new thread.
    Can I create a query or a stored procedure via VS2017using a wizard?

    I don't know anything about some wizard in VS that's make a stored procedure. It's uaually done with SSMS.

    Wednesday, March 4, 2020 8:14 PM
  • I don't know anything about some wizard in VS that's make a stored procedure. It's uaually done with SSMS.

    Hello,

    OK, thanks. With SSMS, Do you know a good tutorial? 

    Greetings Markus


    Thursday, March 5, 2020 11:07 AM
  • I don't know anything about some wizard in VS that's make a stored procedure. It's uaually done with SSMS.

    Hello,

    OK, thanks. With SSMS, Do you know a good tutorial? 

    Greetings Markus


    I should ask the question in the MS SQL Server forums.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?forum=sqlexpress

    Thursday, March 5, 2020 1:22 PM