Client Server Installation Package .NET

Question

I guess this is a basic problem which every beginner to Client-Server is looking for: I have developed a C#.NET Client-Server Software.

A basic overview of the application

Application contains a built-in Database, so that if server is not available user can use the database that is available their with their local System. And if the Database is available at Server then the Server database will be used. Both the database synchronize with each other on a click of button. So I have two connection strings, one is for Local System and another one is for Server. They both are getting dynamically created.

For : Client Setup -> Right now I am using Click One Application

1. My client system uses a Local Database which I use to install along with my application.

PROBLEM 1:

I have a SQL Server Express based database deployment. I created a clickone application with prerequisites like SQL Server 2008. When I install my clickone my application gets installed properly without any errors, but while starting the application, it gives me error that my application is unable to find database at C:\Users\Xnor\AppData\Local\Apps\2.0\Data\N24R9574.9H1\V0Q1MX0C.0X9\....

I surfed stackoverflow and found that copying my .mdf to this location will fix my problem. But why is this happening. Why not it copies itself?

Isn't there any way to attach the DB permanently to my application, so that i can avoid this manual work?

Everywhere its just instructions to copy .mdf file to that location and that's it. So is there any way that could work, maybe if it is possible and suggestible that .mdf would be attached in resources and could be used from there. Looking for expert advice, the best way to accomplish it.

---

For Server:

I use to install Microsoft SQL Server Management Studio Express and then I use to create Database there for my Server and then IT use to work along with my application.

PROBLEM 2

How can I take these steps down. As telling non-IT people and teaching them to install Microsoft SQL Server Management Studio Express all the time on server and then creating a Database on that and after that giving its connection string to the application to make it work brings lot many issues.

---

So my basic requirement is just to give a smooth experience to user while installation. As the guys doing installations are not a tall geeks. They can do Next Next Next but they don't like to do so many configurations. Is there any professional way of doing this. As I guess this is the most important thing with Client-Server softwares and I have never seen such softwares to ask you to do so many configurations.

I am not willing to use Click-one Deployment as it looks very unprofessional and I feel its very limited with features. So kindly suggest some other alternate which can serve to this requirement.

Also I am wondering that this is a very important thing, and there is no ready-made tutorial I was able to find on google and youtube, and I am helpless on finding a right direction on How to automatize this thing, or may be a way so that I can reduce the steps.

I am ready to deploy two setups: One for client and one for server

Kindly let me know if any other information is required. Looking forward for a great support. Regards.

Lamdin

What exactly is to be installed in the server side? Only the DB server that every client connects to? Or there is a part of your program to be deployed as a service or something like that? – Baerl

Answer 1

Problem 1: Adding SQL database to application - One should do it using the visual studio solution.
Click on the project that uses the database (or better, create a folder first for that), Ctrl+Shift+A>Data>Local Database / Service-Based Database.
then you should populate the database, change the connection strings, etc.
The project will provide you a connection string that can be tempered and be changed in the installation, as the file path can vary.
This way, it'll copy the database among the project installation using your clickonce.

Note: Choosing a database type (SDF vs. MDF) is not such a hard question.
If you can use MDF as it has better performence, better capabilities, but requires local installation of SQLExpress.

Problem 2: SSMS (Menegment Studio) is not required or something like that.
Now, I tend to believe that you are installing the database on the server that run the application - you SHOULDN'T do that. use a dedicated server for the database.
However, for your answer, you can do two things to make it better:
1. You can create a SQL Database named model - which is ideal for database creational as it copies itself to newly databases, and make things easier.
Then, your application can run (in the installation part) SQLConnection.CreateDatabase, which will copy the model and create database for your needs.
2. Yoy can migrate to a newer solution like using EntityFramework, which there you develop your classes and the EF is responsable for the storage.

I havn't used Clickonce for a while, but I know you can make a customized version of it with your texts, your logo... If you want to automate, one of the steps is running your code, if you provide it. you can create installation files. Simply create (Ctrl-Shift-A) Installation file (its there), where you can override methods and run what you want (File copying, adjustments, requirement checks..)

Consequential

Answer 2

For deploying at the client, I would rethink the strategy for its local database. Problem is that, as I understood, you're using SQL Server too on each and every client with a local .mdf file, which I consider more of a problem than a solution, since it works like any normal DB server but installed locally, with all the headaches it produces. And since clients should not receive external connections (correct me if I'm wrong here) a full blown server would be an overkill. My idea is to choose a different DB engine for the client-side:

• SQLite would be my prime choice. It's an embedded DB engine that's just a single DLL in its most minimal form, and a few more with its .NET wrappers. It stores data in a file of your choice and access it with standard SQL language. Deployment would be as easy as copy a few more libraries into your installation folder, which fits your desired next/next/next install procedure. The drawback is that it support only a subset of the features that SQL Server has, which may or may nor be a problem for your particular case.
• SQL Server Compact Edition is very similar, running totally local without a real server and accessing a local file. Its features are somewhat similar to SQLite, and still well below from SQL Server, but again for a local copy it seems enough for me. Deployment again is simply copying a bunch of files to the program folder. This article has some hints for installing this DB engine
• If you need the full power of a real SQL Server, an alternative option would be to make use of a new feature of SQL 2012 called "LocalDB". It's basically the same DB engine from SQL Express, but it runs on-demand without the need to install and maintain a service, just right under the user context on the first connection from the application. That lets you use almost the same feature set, but with a much simpler management. For deployment, you have to run a separate installer, which don't really requires too much knowledge beyond local admin rights, and then connection and usage is almost the same. This would be a nice trade-off if you're using more function than a simple dumb datastore.

For the client-side DB itself, I would not include in the installer at all. Instead, when the program is running and it first needs to use it, it will try to open it, and if not found, create it from scratch. That can be done though by bundling the DB creation scripts inside the app itself, which will run all of them in sequence to create an empty DB (basically sending a bunch of CREATE TABLE/CREATE VIEW/CREATE PROCEDURE/create whatever). Some ORMs can even automate that step (but I doubt that's a good idea) As this would happen only once, it should not have a big performance impact, and also would make the program resistant if the DB gets deleted somehow. All that must be done on each and every client computer, but I think it may be well possible to create a very dumb next/next/next installer.

Now for server-side. It's substantially more difficult to get an easy way to install a server without intervention. Problem basically is that there are a number of configurations that go well beyond your system, but yet can influence it. The DB engine itself is one of such things, as it needs to configure permissions, setup databases, allow external remote connections, enable protocols and configure automatic backups. Also, servers may include firewalls whose ports need to be opened for accepting incoming connections, maybe antivirus messing with network activities and so on.

This is the sort of things that I think go beyond what your app can and should ever do, even if that means that the IT people needs to do that manually (which is not much of a burden, as it will be done only once). I don't have much of a nice suggestion here, the suggestion from Sharpeye500 is nice in that it allows an unattended SQL Server installation, but only if the right conditions are present, which might not be always the case.

What you can automate is the creation of the server-side DB. You can use an "installer" that does the same as I proposed for client-side, that is, send the CREATE DATABASE/CREATE TABLEs statements to build an empty database from scratch. The only required parameters would be the server name/DB name/user/password. The installer can build the connection string from there. The client app may use a similar approach for locating the server DB, and those parameters may be as well included in the client-side installer.

Baerl

Answer 3

I would like to recommed inno setup for your Client and Server side smooth (next >next > next) installations. Currently I'm using inno setup installer for my client installations.

Ref: 1. How to build the installer with inno setup 2. Inno Setup for .NET application

Problem 1: - You can use the SQL Server attached database (Solution Explorer > Add new item > SQL Server Database (OR) You can install the SQL Server (Standard/Express) in client system and create the database and database objects through Inno Setup.

Please follow the below steps to create a Client installer.

1. Windows App - Create an exe with Visual Studio Deployment Projects
2. SQL Server Installation Setup
3. DB Scripts as .SQL file (Create database, Create tables, Create Stored Procedures)

Download Inno Setup > Create a project > Refer the path > Build the inno setup

Note: You can include other pre-requisites in your inno setup folder and you can order the installation steps e.g. 1.NET Framework, 2. Windows App EXE, 3. SQL Server Express, 4. DB Scripts restore

Once build the inno setup, you will be having only one setup for the client side installation. Samething you can create an another one setup for Server Side Installation.

Slave

Answer 4

Can't the sql server express be installed silently part of the application?

public bool InstallExpress()
            {

                //In both cases, we run Setup because we have the file.
                Process myProcess = new Process();
                myProcess.StartInfo.FileName = sqlExpressSetupFileLocation;
                myProcess.StartInfo.Arguments = "/qb " + BuildCommandLine();
                /*      /qn -- Specifies that setup run with no user interface.
                        /qb -- Specifies that setup show only the basic 
user interface. Only dialog boxes displaying progress information are 
displayed. Other dialog boxes, such as the dialog box that asks users if 
they want to restart at the end of the setup process, are not displayed.
                */
                myProcess.StartInfo.UseShellExecute = false;

                return myProcess.Start();

            }

This link might be helpful

If you can detect whether you app is going to connect to local or server database, then behind the scenes on opening of the app, try installing sql express local one, part of your code, so that you can avoid the configurations that your non-IT people need to do.

Lanner

Answer 5

Is there any specific reason for , even If you have a Client - Server approach and the network is local lan then why dont you install Sever application and database on Server and other clients on clients.

Nevels

Answer 6

This has a simple answer, as eluded to by Balamurugan, PAY for a good 3rd party installer. They are usually programmable so you can do exactly what you want.

Either that, or learn more about the MS Setup project and creating custom installer services. This is a LOT more work but it is free (except your time).

Bicentennial