Sunday, October 26, 2014

Connect LocalDB using Visual Studio

Today let me answer few interview question and answer about local DB. We are going to cover, What is LocalDB? How to start LocalDB? How to stop LocalDB? How to connect LocalDB from visual Studio? How to create tables in LocalDB using Visual Studio.
Lets start with what is Local DB?
In earlier version Visual Studio installation SQL express edition gets installed automatically. People were really confused with this database and was having difficult time in connecting and managing this database. So from Visual Studio 2012 onwards Microsoft decided to have LocalDB engine in place of SQL Server express. This is a very light weight database with a minimum set of files required to create a database and it will directly tied up with the project it is attached to.
Lets see how to start LocalDB.

You may use command prompt to start and stop the LocalDB. Windows + R or click Start and Run and then type cmd to open command prompt.
If you have multiple version Visual Studio you may have different version LocalDB in your system so first we will get the LocalDB version by running below command
>SqlLocalDB.exe info

image  
Now we will start this localDB using another command.
>SqlLocalDB.exe start v11.0

image
Same way if you wanted to stop the LocalDB then you have to run below command,
>SqlLocalDB.exe stop v11.0
image

We have another very useful command to get complete information about the LocalDB. Below command will give you the LocalDB Name, Version, Owner, State and most importantly Instance Pipe Name.
>SqlLocalDB.exe info v11.0
image
Now you have all the essential command in your command to use LocalDB. Next step is to connect this to the Visual Studio project and creating a sample table.
Lets open Visual Studio first and see how we can connect to the localDB. Once you open Visual Studio go to TOOLS and then click on “Connect to Database…” option
image
You will get a Choose database dialogue where you have to select the Data Source. In this case of course it will be “Microsoft SQL Server”
image
Click on Continue button to provide the Server Name. In place of Server name we have to provide the Instance pipe name which we have collected by running LocalDB info command.
image
Enter the Instance pipe name and click on Test Connection to make sure connection is established successfully.
image
Select the Database from the “Select or enter a database name” and Click OK to complete this operation. Once you click on in Visual Studio Server explorer will open automatically and will show you the Data Connection.
image
So your project is now connected to your LocalDB. Now you can create Table, View, Stored Procedure etc as you require. Just right click on Table and select Add New Table to create the tables. Same method you may follow to create View or Stored Procedure as well.
image  
Rest of the operations are purely depends on the project you are developing. So I am stopping today. I hope now you know how to add LocalDB to your project and how to use it for your project.
Thank you for reading my article.

No comments: