How do I make software that preserves database integrity and correctness?

Question

I have made an application project in Visual Studio 2008 C#, SQL Server from Visual Studio 2008.

The database has like 20 tables and many fields in each.

I have made an interface for adding deleting editing and retrieving data according to predefined needs of the users.

Now I have to

1. Make to project into software which I can deliver to my professor. That is, he can just double click the icon and the software simply starts. No Visual Studio 2008 needed to start the debugging.

2. The database will be on one powerful computer (dual core latest everything Windows XP) and the user will access it from another computer connected using LAN. I am able to change the connection string to the shared database using Visual Studio 2008/ debugger whenever the server changes but how am I supposed to do that when it's software?

3. There will by many clients. Am I supposed to give the same software to every one, so they all can connect to the database? How will the integrity and correctness of the database be maintained? I mean the db.mdf file will be in a folder which will be shared with read and write access. So it's not necessary that only one user will write at a time. So is there any coding for this or?

Answer 1

1. Write an installer that copies the executable and supporting DLLs to the users computer. I believe there is an install project you can use in Visual Studio 2008.

2) You should use Windows 2000 server, Windows XP limits the number of connections. Read the connection string from the registry and provide a settings dialog to change the connection.

3) Don't use MS access, you should be using a real SQL Server that handles concurrent user access. (SQL Server, MySQL, PostgreSQL, Oracle, etc.) You need to rewrite your database access layer using SQL so you don't corrupt data.

Don't even think about deploying this project as is or you will be in for a maintenance nightmare. Are there any experienced developers or even your professor that you can ask for help? There is much to do here to make a functional application.

Answer 2

1) One option is to package and deploy your application with Installshield.

2) Have your application pull the connection string from an app config. I've seen (and done) this by having an XML file with general configuration settings (like database connection strings), that sits on the file system and is in the same directory as your executable, and is read as your executable is started.

3) Generally, SQL Server will handle most of the concurrent data reads/writes and keep the integrity of the data in-tact (as long as you've structured your tables decently).

Since you're going to deploy this app to multiple machines, it sounds like you could just install your application client on X machines, and they'll access the SQL Server database on the database machine. If you do this approach, you won't need to share the db.mdf file as it will only need to be accessible by the SQL Server, which the application will access.

I hope that helps.

Answer 3

The best way to maintain data integrity is to design it into the database:

• You need primary keys that are formally designed.

• You need primary key/foreign key relationships formally defined (and index those foreign keys; they aren't indexed automatically).

• You need unique constraints on natural keys if you are using surrogate keys.

• You need default values for fields that cannot be null and wehther they can allow nulls or not needs to be in the table definition.

• You need the correct datatype for the type of data (do not store dates as a varchar datatype for instance).

• If you have specific constraints on the values of some of your fields, then you need constraints to enforce those in the database or in the application.

Answer 4

how will the integrity and correctness of the database be maintained?

The database should maintain its own internal consistency using constraints.

Users should write to the database using SQL statements within a transaction, so that all related writes happen atomically (they all succeed or they all fail). Transactions also help with conflicts between users at higher levels of isolation, by maintaining locks at table, page or row level.

Update

As requested here is some code pretending to be C# (I don't use it much myself):

private Method(SqlConnection connection)
{
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // Use the connection here
            ....

            transaction.Commit();
        } 
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

If all of the statements in the try block complete successfully, they will be committed together. If any statement fails, the transaction will be rolled back, and the database won't be changed.

Strictly speaking the using statement will take care of the Rollback if the transaction isn't committed, so if you are not doing any error handling you could also write the same code like this:

private Method(SqlConnection connection)
{
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        // Use the connection here
        ....

        transaction.Commit();
    }
}