Need for a general description of how to create multi-user soft that works with DB

[LianaN]

Hi!

I need to make multi-user software that will allow accessing a DB from different PCs and working with this DB. Could someone pliz just generally (conceptually, but in step-wise manner) describe me how this task can be implemented? Maybe, there are some good tutorials or free examples on this topic?

Any comments will be appreciated! Thanks!

[steelshark]

You probably want to go for a web application in ASP,PHP or likewise...
That way you have one server, that hosts the application & database...
Next you just have to create a login functionality with different users, and thats pretty much it.
If it has to be a desktop application you'd have to write an application that connects to a server on the network , and logs in to that to get access to the DB

[SNK111]

I agree with steelshark; it seems this will have to be web application which will be connecting to a database. You can use a language such as C# and a database such as Microsoft SQL Server.

To connect to SQL Server from C#.NET, you need to create a connection string such as below:

private SqlConnection connection;
private string connectionString =
@"Server=(local);Database=Embedding_SQL_Test;User ID=sa;Password=123";
connection = new SqlConnection( connectionString );

Next, you use the SqlConnection object created above to create a 'SqlCommand', as shown below:
SqlCommand cmd = new SqlCommand( "select * from Customer where CustomerID = @Cid", connection);

The SQL query shown here can be replaced by a SELECT, INSERT, UPDATE queries etc.

Next to execute the SQL queries in the database, you use the following methods:
ExecuteReader - to execute SELECT queries
ExecuteNonQuery - to execute INSERT, DELETE, UPDATE, and SET statements.

This is a very short description of how to connect to SQL Server database from C# and execute SQL queries in the database.
For details about the connection string, the methods and their parameters check the following link: ( <<snip>> )
Here you will also find details about how to pass parameters to the SQL queries as well as calling stored procedures and much more.

[LianaN]

Thanks to all of you for fruitful advices.

Well, actually I'm thinking about a desktop application, not web application. I'll try to be more specific. I need to create an application that will be installed on different PCs. There will be a remote database. By running the application, a user will be able to connect to the database (with his/her login & pass) and read/write/delete the data depending on his/her rights.

As far as I understand, I'll have to implement the following steps (using JAVA DB):
1. Load a driver
2. Set a connection to the remote database
3. Execute a query
4. Output the results

Do I understand correctly the concept? An important aspect is that I need the most easiest solution for the future user...so that the user wan't have to install lots of soft and drivers. In my example, as I understand, the user will just need to install the application, JDK and DB driver. Is it right?

[LianaN]

Better to say:
1. Develop an interface of the application using Java SE (?)
2. Create a class that will allow to:
2.1. Load a driver
2.2 Set a connection to the remote database
2.3. Execute a query
2.4. Output the results

Will this solution be FREE OF CHARGE?

[steelshark]

You are right about the install things yes :)
if you include the jdbc driver jar in your program then the user wont have to install anything additional.
For the development part you are right aswell...
To break it down, you'll have a User Interface which needs to be able to let the user connect & execute queries..
In order to achieve this you'll have to use a driver (jdbc for example),and thats basicly all there is...

However if you just want the user to execute queries, i don't really understand the need to create a program..In microsoft SQL server you can just access a network server if you have an account on it, and run queries on the database..

[LianaN]

Thanks for the answer!

Well, I'll also need some operations to be performed with the data. Therefore I would like to create an application.

But I don't fully understand how to implement the following. For instance, the User_A & User_B have installed my application on their computers. The user_A has rights to only read the data, whereas the USER_B is allowed to read and write the data. So, how could I check which action right the user has?

Also, is it possible to connect to some remote open source MySQL databases, which exist just for learning purposes?

[peter_budo]

But I don't fully understand how to implement the following. For instance, the User_A & User_B have installed my application on their computers. The user_A has rights to only read the data, whereas the USER_B is allowed to read and write the data. So, how could I check which action right the user has?

Ever heard of user groups??? User/Moderator/Administrator/Owner...

[LianaN]

Probably, I've formulated my question incorrectly. Yes, sure I know about user grups and privileges like "grant create, delete...to.." Ok, I've already come clear with my question.

But maybe someone knows if it is possible to connect to some remote open source MySQL database, which exists just for learning purposes?

[peter_budo]

Liana that is highly unlikely. You have following options
1. Host it at home and open, made it accessible through IP and port
2. Ask your school if they have database hosting available to students
3. Get (buy) your self hosting on some server

[LianaN]

Ok, thank you! I guess I'll try one of these options.