How to embed sql database in App_Data Folder?

Raj.Trivedi
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 19099 red flag

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.

We will be detaching and attaching the database for this purpose.

Detach is to remove the database instance from Sql Management Studio.

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.




Page copy protected against web site content infringement by Copyscape

About the Author

Raj.Trivedi
Full Name: Raj Trivedi
Member Level:
Member Status: Member,MVP
Member Since: 6/16/2012 2:04:41 AM
Country: India
Regard's Raj.Trivedi "Sharing is Caring" Please mark as answer if your Query is resolved
http://www.dotnetfunda.com/profile/raj.trivedi.aspx
Raj Trivedi i.e. me started my career as Support Professional and then moved on the Software development eventually reached at these skills Software Development | Enthusiastic Blogger | Content Writer | Technical Writer | Problem Solver | Lecturer on Technology Subjects | Runnerup Award Winner on www.dotnetfunda.com and firm believer in Sharing as a way of Caring Yet this much achieved its still a long way to go and there is biggest dream lying to be one of the best entrepreneurs of India in Technology Department. The Dream has just started and i hope it follows. Highlights are mentioned in details in my profile at http://in.linkedin.com/pub/raj-trivedi/30/61/b30/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)