What Server would I need for this simple project?

[zack_falcon]

My professor has instructed me to make a simple project that requires the use of a database server on one computer, so that another can connect to it.

If my program makes a change in the database, the other program is updated automatically. As in, it is updated as soon as something is added in the database.

Example, if I use a program in computer A to update a certain number from one to two, the data in the database changes, and this will be shown in the program in computer B. I'm thinking perhaps I can keep a connection alive until a change is made, at which point it closes, although this might have some serious performance problems. Does anyone have a better idea?

My classmate has suggested using MySQL, however the latest version of MySQL requires .NET 4.0, and I cannot update the computers at school, so he also suggested SQL Server. Are there any speed differences between the two? And would an older version of MySQL work?

Thanks for the answers.

[hericles]

An older version of MySQL would work fine, the issue isn't what database you use but how you make the remote application update, unless I've missed the point of the exercise. I wouldn't think database speed would be issue, both MS SQL and MySQL are used as production servers for massive databases.

[prvnkmr194]

As hericles suggest database is not a issue you can use either MS SQL server or MySQL. Your question is that when we have to close the connection, as my knowledge .Net frame provide the facility of disconnected database, until you perform the update process your connection just after completing the update process you can close your connection.

[Ketsuekiame]

Hi Zack. This exercise sounds like a demonstration of your ability to create a CRUD proxy. (Create, Read, Update, Delete)

I guess what you're taught to do normally, is create a database and connect directly to it. However, this means that when you have two programs connected (Program A and Program B), if Program A updates the database, Program B won't know about it. Now it's possible for you to do a complicated set of triggers and COM calls, but this is far from easy.

So in comes my preferred solution, which is to have a "proxy" sat in front of the database.

Rather than connecting directly to your database, you connect to your "proxy" application. Personally, I suggest using WCF as it has a built in Callback system you can use. However, you can use normal sockets if you prefer.

Your "proxy" will have effectively, 4 methods. Create (create an entry in the DB), Read (Query the DB), Update (Update an existing entry in the DB) and Delete (Delete an existing entry in the DB).

Now you can choose what data you send over the wire, but this is the reason I suggested WCF. WCF allows you to send objects over the wire in a form called Data Contracts. This is like saying, I have an Employee class. I want to add a new Employee to the database, so I create an instance of the class, fill in the data and call WCFService.CreateEmployee(myEmployee); no messing about converting the data down to bytes for transmission by Sockets ^^

When your "proxy" receives the data, it goes off to the database and creates a new employee. Now at this point, you will need to issue what's called a "callback" to all your currently connected clients. A callback, is when your service calls a method on your remote machine. When you connect a client, you keep a copy of its connection context. This allows you, if the service is set up correctly, to allow two-way method calling between your client and your server (instead of just command-response)

So;

foreach(Client myClient in clientList)
{
    myClient.NotifyNewEmployee();
}

Is a simple example of how it would work.

I know WCF can seem daunting when you first look at it. But try your best as it is a very good framework.
Here is a beginner's guide to WCF callbacks. WCF will work under .net 3.5 and provided you have remote access to the database, doesn't even need to run on the database server (although that would be more ideal for security reasons)

So yeah, take a look at WCF and the Callback system, try out the beginner's tutorial, try and get some code down and come back if you need any more help. (Remember to post the code you have done)

EDIT: As for choice of Database. Neither really make any difference to the end product. SQL Express is good because it allows you to create a small footprint local database which doesn't require a database service. MySQL is a little bigger and requires the machine to be running the MySQL Services, but is sometimes said to be easier to use than MSSQL. If you're feeling adventurous/suicidal, use Oracle ;)

[zack_falcon]

Thanks for the answers guys. It helped a lot.