Working with Sample Databases

Question

I have been working with SQL-Server for awhile as part of my weekly routine, and since everything was setup in advance, I take everything for granted.

Start SQL Server, pick a Server name, pick SQL Server Authentication, then type my login and password
or from an application just use a given connection string and everything works like charm

Until all the sudden, a third party sent me an application sample that needs to be reviewed, and it includes a Northwind database, and from that point on I am having difficulty getting this sample to work.

Changing the connection name and pass to match SQL Server instance credential does not solve the problem

This is how my string usually looks like

I have SQL Server 2014 installed and also this is how the application folder structured

In Server Explorer in Visual Studio the connection is recognized and when I attempt to access, I get

How to think about this problem in order to solve it and get the sample running? There is a missing gap that I cannot point my finger to.

Why wouldn't you just use your usual connection string except put `Initial Catalog = NWIND`? — Pat Jones, Dec 19 '17 at 20:03
@AlyEl-Haddad, no, but what if someone does not have permission to do so? — usefulBee, Dec 19 '17 at 20:04
Are you saying you did not attach the sample database to your instance because you don't have permission? It is pretty tough to help here because we don't have a lot of detail to work with. — Sean Lange, Dec 19 '17 at 20:07
@SeanLange, my concern is that I would like to keep the Database local to my machine, I don't want it to appear along a list of server databases. — usefulBee, Dec 19 '17 at 20:19
Then put it on your local instance. Do you have a local instance installed? — Sean Lange, Dec 19 '17 at 20:21
@SeanLange, I have SQL Server 2014 installed on my machine, is that what you mean by local instance? — usefulBee, Dec 19 '17 at 20:24
Yes. Attach/restore that database to your local instance and only connection on that sql server will see it. — Sean Lange, Dec 19 '17 at 20:26
@SeanLange, but does this mean there is a default/local credentials and a local/default server name to use? Because if I use other server credentials, the sample db appears as if it will be added to a list of production databases which I want to avoid in first place. — usefulBee, Dec 19 '17 at 20:34
What??? I think you need to spend some reading about the architecture of sql server. SQL Server is just an application, in that application you can have a number of databases. And you can connect to that application using a username/password combination. If you attach the database to your production server then it will be available on that server. If you attach it to your local it will be available there. — Sean Lange, Dec 19 '17 at 20:41
What??? Of course I need to spend some time reading about the architecture of sql server :) "If you attach it to your local it will be available there" This is where I am puzzled, how to attache it to my local? — usefulBee, Dec 19 '17 at 20:51
you can google how to attach databases to a sql server. Depends if it's in the form of an mdf (database file) or a bak (backup) file. — ADyson, Dec 19 '17 at 21:42

score 1 · Answer 1 · answered Dec 19 '17 at 22:05

I have two solutions for you.

In Visual Studio, create a class library project. Delete the default class file. Right click the project name -> Add -> New item -> Select "ADO.NET Entity Data Model" name it what you want and click Add. Make sure "EF Designer from database" is selected and click Next. Click New Connection. Next to "Data Source" click Change. Select "Microsoft SQL Server Database File", click OK. Click Browse and select the .mdf file for the database. Select "Use SQL Server Authentication" and provide your login credentials, then click Test Connection. If the connection fails, your login is wrong (minus a random super rare occurrence). Click OK and finish out the menus. Go into the App.Config and copy the entire xml tag. Go into your application project, right click, the project name -> Manage NuGet Packages -> Browse -> Search for EntityFramework, select it and click Add on the right hand side. Accept the terms it provides. Go into your application projects App.Config and paste the xml tag you copied into the xml tag. To use it, copy the value of the name attribute from the tag from earlier and do the following
```
using (var db = new NameAttributeFromAddTag())
{
    // Use standard link notation
    var item = db.TableName.Where(i => i.Id == idYouWant);
}
```

(You can find more info about adding the connection here: https://msdn.microsoft.com/en-us/library/jj206878(v=vs.113).aspx)

Open up SQL Server Management Studio and connect wherever you want to (Such as computerName/SQLExpress (your default local DB server)). Right click Databases -> Attach -> select .mdf file for the database. Click Ok, click Ok again. Connect to that database as you would any other database. If it still says your login is incorrect, In SQL Server Manager look at Databases -> yourDatabase -> Security -> Users and check the properties of the user you are trying to use.