In this article we will see how we can use the database directly from our application.For this we will be using App_Data folder.
Attaching it in .NET app to use the database directly from App.
Introduction
Hello Friends,
Recently while working on a small project we had a requirement where we needed to embed the SQL Database in the application itself,it might seem tricky but believe me its really quite easy.Today we will see how we can go about embedding the SQL Database and its components to App_data Folder.
Requirements:-
Sql Server Express or any Version.
Existing database with Tables.
Note :- You will require SQL Server Express installed at the client machine if you are going to deploy the app at the client end.
Objective
The main motto of this article is if you are working on a small scale project and if the client cannot afford puchasing license for SQL Server then we can use SQL Server Express Version i.e. freeware by microsoft and this article will help a lot in the App Development.
Using the code
Start -> Right Click on MS Visual Studio 2010 -> Run as Administrator
File -> New WebSite -> Right Click the Website Name in Solution Explorer -> Add ASP.NET Folder - > App_Data.
Now go to Sql Server Management Studio.Connect to the Database Engine.
Go to the database that you want to embed in the Application.
In this article i am going to embed testdb database.
Right click the database that you want to embed in your .NET Application.
Go to Task -> Click Detach.... it is the very first option in the Task Menu that you get after right clicking on the database.
Once you click on Detach you will have a window opened up Known as Detach Database.
In this window we need to check mark the drop connections and Update statistics Checkbox.
After that Click OK.
Once it is done we will no more see the database in our Server Management Studio.
Now come back to Visual Studio where we have created and we have already added the App_data folder.
Now right Click on the App_data folder -> Add Existing Item -> Go to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
Once you reach this folder select the .mdf file of your database.In my case it is testdb.mdf
Now if you see the testdb.mdf file is seen in the App_Data Folder.
Now to access the database from app.config the connectionstring should be in this form
<connectionStrings>
<add name="ConnectionName"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|testdb.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
To use the database you should at-least have sql server express.
To attach the database back to sql -> go to sql management studio -> Right Click on Database -> Click Attach -> You will get an attach Database window as below and Click on Add.
Once click on Add it will take you to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA now select the same database you have detached,In my case it is testdb and click on OK.
Conclusion
I hope this will help the developers who have the same requirement.