ASP.NET MySQL WebApp Architecture

I'd like to know the best architecture.

We have a web application running different domains. Each domain has its own MySQL database but the db structure is the same for all of them.

• We have a web application for the visible part of the application.
• We have a dataLogic project
• We have a dataEntities project
• We have a dataAccess that contains only the methods to connect to the data base.开发者_开发技巧

Before we called stored procedures on a database. But we had to change it because the performance was bad. Also, the problem was that every change we made we had in a stored procedure we had to copy to every database.

We are thinking in using a WebService to retrieve the data. Every domain can call the web service with a connection string and connect its database to retrieve data. This way when we change a SQL query we only have to compile the webService and change it, we don't have to change versions on multiples domains.

Also, what do you think about the SQL queries? Since we don't want to keep using stored procedures, what is the best way to do it? Directly from code?

Thanks

T

If you have multiple Database servers you will have to make Structural changes from one DB to another one way or another. There are many tools to change Database structures. These tools will look for differences between Schema, and will either generate the SQL code for you, or do the changes by itself (it depends a lot in the tool, there are powerful ones and not so powerful ones). Please do take a look at Toad for MySql. Now, for the Data changes, you may want to replicate the data from one Database to another. This is done through Replication.

We are thinking in using a WebService to retrieve the data. Every domain can call the web service with a connection string and connect its database to retrieve data.

This sounds like a good idea and since you already have "dataAccess" and "dataLogic" projects, it should not be too hard to make the services.

Also, what do you think about the SQL queries? Since we don't want to keep using stored procedures, what is the best way to do it? Directly from code?

I don't think it is a good practice to have the SQL queries directly into your code, but it depends in a lot of things, so I would suggest Stored Procedure vs Hard-Coding the queries, or LinQ (Entity Framework 4.1).

Good luck with your project and I will take a look at this thread frequently to see what you end up doing.

Have fun!

Hanlet