16

I have designed an accounts software for my clients. I used SQL Server 2008 database with Stored Procedures. It is developed in Visual Studio 2010, .NET Framework 3.0. I have more than 500 clients using Windows 7.

The major problem is:

Is SQL Server runtime automatically installed with .NET Framework? As MS Access database does not required Office to be installed on client.

I can not installed SQL Server 2008 on each client, it is a tough job. Also the clients are not having a good knowledge of installation process.

How to run SQL Server database on clients without installing its setup on clients? Is there any runtime files or setup?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Art and Artistic artandartisti
  • 345
  • 2
  • 3
  • 11
  • 1
    You can create a set up project and mark sql server express and dotnet framework as a prerequisite . – Reza Aghaei Oct 05 '15 at 19:19
  • 1
    Dot net framework is already installed on their system. because its a part of windows 7. But how can client download and install sql server ?? They reject my software and use some other other one, Just because of database. – Art and Artistic artandartisti Oct 05 '15 at 19:22
  • 1
    Is there any small and easy way to install sql server, so that client can easily install them ? – Art and Artistic artandartisti Oct 05 '15 at 19:24
  • 1
    Search for `SQL Server 2016 Express LocalDB`. I assume you want an SQL Server running on a local PC not shared with other PCs on a LAN. – rheitzman Oct 05 '15 at 19:28
  • 1
    Have u tried SQLLite or Compact SQL. SQL server may be heavy weight for you kinda application. – XtremeBytes Oct 05 '15 at 19:30
  • 1
    Yes, definitely its an account software and database can not be shared. Because accounts information is very sensitive. Is there any small setup of sql server that client can easily install without annoying me ? – Art and Artistic artandartisti Oct 05 '15 at 19:31
  • 1
    MSDN : If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB. I can not install dot net framework in clients. They are having dotnetFramework 3.5 is it possible to install it without upgrading dotnet framework ? – Art and Artistic artandartisti Oct 05 '15 at 19:34
  • 1
    @XtremeBytes Now i can not move to SQLCe because it have many stored Procedures. – Art and Artistic artandartisti Oct 05 '15 at 19:39
  • 1
    @ArtandArtisticartandartisti I posted an answer for you. It is the way that I create installation packages for applications since 2005. Hope you find it helpful:) – Reza Aghaei Oct 05 '15 at 19:42
  • 1
    @ArtandArtisticartandartisti I edited the answer and added another option (ClickOnce) that offered to a user and he used and accepted my [answer](http://stackoverflow.com/a/32839684/3110834), despite he used the option in VS 2015, by you can use it in VS 2010 too. – Reza Aghaei Oct 05 '15 at 20:04
  • 1
    LocalDB is the key. Here is the example – Fawaz Chughtai Nov 12 '18 at 13:03

3 Answers3

23

Option 1 - Setup Project

Using Visual Studio you can create a setup project and install prerequisites that you need during installation.

The installation process is very simple and the end user can install application and prerequisites after clicking next buttons.

Here are the steps for Creating a Setup Project:

1- Create a c# Windows Forms Application

  1. Create a C# Windows Forms Project
  2. Add New Item and Add SQL Server Database to your application
  3. Add a table to your application and fill some data in it
  4. Show the data in your main form.

2- Create a Setup Project

  1. Add new project → setup and deployment → setup project
  2. Right Click on Setup project and Add project Output and select primary output from your main project
  3. Right Click on Setup project and Add project Output and select content files from your main project
  4. Right CLick on setup project and Click Properties and click Prerequisites and select SQL Server Express
  5. Select .Net Framework
  6. Select Windows Installer
  7. Select radio button Download prerequisites from the same location as my application.
  8. Right Click on Users Desktop at left pane and add new Shortcut and select application folder, primary output from SampleApplication, and click ok and the rename the short cut to what you need.
  9. Rebuild solution.
  10. Rebuild Setup Project
  11. Go to Output directory of setup project and run setup.exe

It's that easy.

For more information take a look at following docs articles:

  1. How to: Create or Add a Setup Project
  2. How to: Install Prerequisites in Windows Installer Deployment
  3. Walkthrough: Using a Custom Action to Create a Database at Installation

Option 2 - ClickOnce

Using Visual Studio another option is using ClickOnce publishing.

To do so, in properties of your project, in publish tab, click prerequisites button, you can select SQL Express in prerequisites. This way, you only need to set your database files to copy in output directory, and use AttachDbFileName in connection string: Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Initial Catalog=Master".
For more information take a look at the following docs article:

Reza Aghaei
  • 120,393
  • 18
  • 203
  • 398
  • 1
    Clients may deny to use the software because of its larger size setup. – Art and Artistic artandartisti Oct 05 '15 at 19:59
  • 1
    @ArtandArtisticartandartisti Clients should accept your set up or install prerequisites themselves :) – Reza Aghaei Oct 05 '15 at 20:02
  • 2
    They can not install prerequisites themselves. Think of you, Will you install a software that requires a lot of Next, Next, Select, Next etc. Clients need a software that compact in size an quiet easy to install without having technical knowledge. – Art and Artistic artandartisti Oct 05 '15 at 20:14
  • 1
    @ArtandArtisticartandartisti You will see in action, that it is very very easy to install and users only should click a few next buttons. – Reza Aghaei Oct 05 '15 at 20:17
  • 2
    @ArtandArtisticartandartisti You should know each method you use to deploy your database in client will result in sql server installation, no difference between size :) – Reza Aghaei Oct 05 '15 at 20:18
  • 4
    @ArtandArtisticartandartisti anyway, I am here to help you and these options was the best things that came to my mind. Hope these options help you to overcome the problem and you can install your application on clients :) – Reza Aghaei Oct 05 '15 at 20:20
  • 1
    is there any way to install LocalDB of Sql server R2 ? – Art and Artistic artandartisti Oct 05 '15 at 20:23
  • 1
    Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91422/discussion-between-art-and-artistic-artandartisti-and-reza-aghaei). – Art and Artistic artandartisti Oct 05 '15 at 20:24
  • 1
    @ArtandArtisticartandartisti You should know at the heart of LocalDB is the same sqlservr.exe as in the regular SQL Express and other editions of SQL Server and it should be install. Size of SQL Server 2008 R2 Express Package to install is 74 MB and LocalDB 32 MB. to read more about comparison see this [link](http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx2) – Reza Aghaei Oct 05 '15 at 21:04
  • 1
    @ArtandArtisticartandartisti I took a look at one of my installers that I've created before, it installed the whole application and sql express in 3 next button click an a close button click. – Reza Aghaei Oct 06 '15 at 18:00
  • 1
    you can give me your software with database. and i can easily get ALL of your logics out of DATABASE. Can you protect that ? – Art and Artistic artandartisti Oct 06 '15 at 18:39
  • 1
    @ArtandArtisticartandartisti unfortunately it is a commercial software created with visual studio 2005 and the solution contains 11 projects and it's hard to decouple parts of application. But I think It's easy to create your setup simply using those links I provide. – Reza Aghaei Oct 06 '15 at 18:50
  • 1
    I got stuck in Security. Since its a software and can be copied and pasted ( mailed / uploaded on web ). Securing Tables, structures and Data is MUCH MORE Important for me. I think i should move back to SQLCE. – Art and Artistic artandartisti Oct 06 '15 at 19:09
  • 1
    @ArtandArtisticartandartisti I'll post a simple step by step guide to create a setup project. And about security, to create a username and password for your database, I think you should create a custom installer action using the link that I provided. – Reza Aghaei Oct 06 '15 at 19:13
  • 1
    But any one can copy that Database mdf file and load into his system. One can easily get all the Data inside the database file. How to protect that ? That username and password doest not Embed with mdf file. Like in Ms access or SQLCE. – Art and Artistic artandartisti Oct 06 '15 at 19:17
  • 1
    @ArtandArtisticartandartisti Surely you have options to encrypt database or some other options. But I think it is subject of other question, since I think **the options completely answer your original quetion** I hope you accept and vote for this good answer to make it more useful for other users and then ask your new question here in stackoverflow. I'll try to answer your new question, or maybe someonne else has some better idea about data protection. – Reza Aghaei Oct 06 '15 at 19:24
  • 1
    http://stackoverflow.com/questions/32973509/how-to-protect-sqllocaldb-database-file-with-custom-password Check it out ? – Art and Artistic artandartisti Oct 06 '15 at 19:29
  • 1
    Thanks for answer. I will go for LocalDb installation. It takes few seconds to install and quiet easy to use. Since i moved back to SQLCE. I wiil be glad if you go through http://stackoverflow.com/questions/32993018/what-is-the-fastest-way – Art and Artistic artandartisti Oct 07 '15 at 13:20
  • 1
    @ArtandArtisticartandartisti You should now: **- The SQL Server database experiences in Visual Studio 2010 are built on top of User Instances of SQL Server Express. Using LocalDB to power them is more complex than just changing a connection string to (localdb)\v11.0. It would require releasing some kind of a wide patch for Visual Studio 2010.** and also **- LocalDB was introduced in SQL Server 2012 CTP3. Visual Studio 2010 only supports SQL Server 2008 R2 and earlier.** – Reza Aghaei Oct 07 '15 at 13:27
  • 1
    @RezaAghaei: Right CLick on setup project and Click Properties and click Prerequisites and select SQL Server Express Select .Net Framework Select Windows Installer Select radio button Download prerequisites from the same location as my application. I followed these steps but when I am installing in other computer sql server 2005 express edition not support for the current processor architecture error is coming. Now what should I do? – Surabhi Pandey Jan 13 '17 at 06:50
  • 1
    @SurabhiPandey Probably the bitness of your sql server package doesn't match with your client OS. For example SQL Server Express 2005 is not supported on IA64 systems. – Reza Aghaei Jan 13 '17 at 11:49
5

LocalDB is Microsoft's current recommended solution. It allows you to connect to a database file directly, without having to install an instance of the Full SQL Server, or SqlExpress. It is fully compatible with the full version of SQL server. There are no installation requirements on the client end, as the libraries are packages along with your application when it is built.

You can read more about it here.

Bradley Uffner
  • 16,641
  • 3
  • 39
  • 76
  • 1
    OP is using Visual Studio 2010 and .Net Framewrok 3.0 and SQL Server 2008. – Reza Aghaei Oct 05 '15 at 19:40
  • 1
    Can we protect it with password as SQLCE and MsAcces. So that client's Financial information can be secured ? – Art and Artistic artandartisti Oct 05 '15 at 19:44
  • 1
    Yes, the password can be embedded in the application and passed in the connection string, Be aware that I'm currently researching whether this solution can even be used with the 3.5 framework based on Reza Aghaei's comment, this might not be an option for you. – Bradley Uffner Oct 05 '15 at 19:46
  • 1
    This Stackoverflow question seems to indicate that LocalDb CAN be used with the 3.5 framework, however installation may be more complex. https://stackoverflow.com/questions/24527528/localdb-connection-from-c-sharp-net-3-5 – Bradley Uffner Oct 05 '15 at 19:50
  • 1
    Clients having at least windows 7 installed. Database security matters a lot as it maintains financial information. As my friends suggest to move on SQLCE. But i can't because of stored procedures. SQLCE password can not (very very tough) be breaked. Is it provide same level security to database ? – Art and Artistic artandartisti Oct 05 '15 at 19:58
  • 1
    LocalDb uses the exact same engine as the full version of SQL, including all security systems. Anything you can do in SQLCE you can do in LocalDb as LocalDb is basically the "next version" of SQLCE. – Bradley Uffner Oct 06 '15 at 05:12
  • 2
    How can i attach password to the database. Because mdf file will be on user machine and He/She can easily see my logics and tables. Is there any way to protect it ? – Art and Artistic artandartisti Oct 06 '15 at 11:45
  • 1
    Does it allow Store precedures? – Sayed Muhammad Idrees Nov 01 '19 at 06:21
  • 1
    @S.M.Idrees Yes, LocalDB supports stored procs. – Bradley Uffner Nov 01 '19 at 14:31
2

you can instal sql express With silent installation in your setup this way not showing any wizard for install sql express Read this Link

Aliasghar Bahrami
  • 267
  • 3
  • 14