Hello My Fellow Programmers,
My name is Arinzechukwu. I have been using vb.net to develop desktop (windows form) application with MS Access as database. But recently I decided to move a step further.

Now, I want to develop an accounting software for a mini Super-Store, this program will be able to run on a network with a central database; in other words a network application.

My problem is that I do not have any idea on how to achieve this. I have not used sql sever before neither have I written any application that will run on a network. Please I need your help on how to achieve this. Any contribution would be highly appreciated.
I, will also like you to recommend the best DBMS for me to use, a friend suggested SQL Server compact. Do I use this?

Arinze

Your end solution will depend heavily on the scenario you are creating a solution for... that said here is some hints I can give you from my experience. I primarily work with SQL Server databases behind VB.Net Applications and ASP.Net Web Applications.

Buying a license for a full fledged version of SQL Server may be worth the money, I have never worked with the lightweight versions.

Creating the database, and designing the software to interact with it, in terms of SQL Server, is very very similar to connecting to Access Databases. Instead of using the OleDb.OleDbConnection you will use a SQLClient.SqlConnection and provide a connections string which includes Server,Port,DBName,Username,Password (connectionstrings.com for more information about all connection strings)...

The most important part of creating a database application is defining all of the data BEFORE YOU START CODING. I stress that a lot when I tutor and when I am starting a new project. If you create the database structure entirely on paper first, you can see clearly what issues you will run into and you will have a better foundation to start from when you begin building your software.

Many users can connect to a database and query/update data in it, but it is best to close your connection to the data when you don't need it open (or you run the risk of severely slowing down when more than 15 people connect to the db).

Generally, I query the Database for data a need, then refresh it on the screen, when the form loads or an action requires the data to be refreshed. Unless the user is doing a transaction, I disconnect from the database.

uhh... thats about all the tips I can give you. Good design in the beginning always shows at the end,
Sean Campbell
firesickle.com

Hello Sean,

I am very grateful for your reply. but I still need to be clarified a little. I hope you will help me, because I am a self taught VB.net programmer, So, I do not know most of the basic things I suppose to know.

I want to develop this application as a general one and do not know the name of the server in which the application will be used.
Since the connection string will require the server name, port number etc. Could you advice me or give me a code on how to determine this server-name during installation so that my programs will be able to use their server-name and also how to change connection string at run time.

Also, during installation, do I need to install SQL SERVER on the target machines, OR do i need to some DLLs only.

Arinze.

[i]
I want to develop this application as a general one and do not know the name of the server in which the application will be used.
Since the connection string will require the server name, port number etc. Could you advice me or give me a code on how to determine this server-name during installation so that my programs will be able to use their server-name and also how to change connection string at run time.
[/i]
SQL Server needs to be installed on a computer (server or regular PC) that is visible on the network inwhich you want to access it across. For instance, if I set up a computer with SQL server that is plugged into the Same Router as another computer I am running the client from, I can use the IP address that the router served up (probably 192.168.0.101) and a port/username/password that I set up during the creation of my Databases, and I can connect to that Database and it will work like gravy.... However, in this scenario, a computer that is on the internet, at some other location, won't be able to just type that Same IP address and connect to that database because the 192.168.0.101 address is local to that network, under that router (not visible outside of the that closed network)...

That said... There are definitely ways to make data visible across the internet, but a general rule of thumb is that you do not make Databases visible outside of internal networks, instead you create a Web service which you can send commands and "serve up" data from the database (acts as a middle man) so that someone isn't directly able to touch that super important core data.

[i]
Also, during installation, do I need to install SQL SERVER on the target machines, OR do i need to some DLLs only.
[/i]

I am going to refer back to my last post: "The most important part of creating a database application is defining all of the data BEFORE YOU START CODING." and rephrase to "Knowing the full scope of what you want your application to do should be what drives you to select a solution rather than selecting a solution and making it fit your scenario"

In your case, I couldn't say for sure if using a SQL server database is going to give you any advantage, and I certainly wouldn't want to steer you down the wrong road as a programmer. So consider some things before you decide to go with a SQL Server Database:

Does the data that your application will collect need to be accessble by many different people?

If so, do these many different people reside in many different locations or at one central location?

If centralized data is a must
If many people, many locations then you would need to expose some database across the internet. As I stated above, SQL Server databases work great but when it is across the internet best practice is to use a Web Service in the middle that can receive commands and get/post data to and from the database, and keep the database from being exposed to the net.

If many people, one location, A SQL Server database can reside within the network and it would be fine to connect to directly. Infact, in a corporate environment, often many locations are still connected together through the same network via a WAN.

If each application maintains it's own data
In this case, each installation of the software will have it's own data, then you would be storing the data locally... To use SQL Server you would have to install it on the computer you install the software, and that would get cumbersome to the end-user, so In this case a lighter weight solution like MS Access DB or XML DB or FlatFiles would be better.

From what I understand, one of the two scenarios I typed out fit what you want to do. In either case, you have SQL Server installed on a machine that runs on a network that the application is also running on. If it is not across the internet, then things are pretty easy, SQL Server's Installation program is really straight forward, if you read all of the prompts thoroughly it explains everything you will need to know to get started. Create a database, and our ready to rock and roll...

[i]in closing[/i]
whew, I wrote a book up there! I'd never be able to teach you SQL, or Database design, or how to interact with the data, over a forum. These kinds of things are best if learned in school, by a mentor who guides you through creating some programs, or from your standard experience of trail and error/researching google for things you need.

Everything I said is based on my own personal experience with creating databases in a corporate environment, it is in no way the "accepted best way" just my opinion of what is. I hope that this helps you find a solution,

Sean Campbell
Firesickle.com

Hello,
You are really a nice guy. In fact I do not know how to thank you. I you have made almost everything I want clear.

I have written many good applications with MS Access as a local database an it worked. So my problem is not for a local database rather a network central database.
The database I want to create will be accessed by at least 5 people connected in LAN. I am not creating it to be accessed from internet if so, I would use ASP.net.

I just want to create a desktop app. ( an accounting software )
for a super Market. which will be accessed by the cashiers(clients), Manager(server).

My problem are these:
How do I make the database central?
How do I install this app to Server and client systems?
How do I access the database from the client machines?

Arinze

Hello,
You are really a nice guy. In fact I do not know how to thank you. I you have made almost everything I want clear.

I have written many good applications with MS Access as a local database an it worked. So my problem is not for a local database rather a network central database.
The database I want to create will be accessed by at least 5 people connected in LAN. I am not creating it to be accessed from internet if so, I would use ASP.net.

I just want to create a desktop app. ( an accounting software )
for a super Market. which will be accessed by the cashiers(clients), Manager(server).

My problem are these:
How do I make the database central?
How do I install this app to Server and client systems?
How do I access the database from the client machines?

Arinze

How do I make the database central?
By installing SQL Server or similar product on a dedicated computer that will reside within the network your application will be running in.

How do I install this app to Server and client systems?
In this instance, you will probably not have a "Server" application which receives data and sticks it into the database, instead your Client program will directly insert data into the DB and query data from the DB. When you build a solution in Visual Studio, an executable is created and run. In Debug mode, debugging hooks are added to the build so Visual studio can execute the code 1 line at a time and provide information inside the IDE when exceptions are thrown...

You can compile/build a solution into a "Release" format, in which case you will have a slightly smaller .exe (won't have the debugging hooks in it) that can be distributed to your Client machines to execute. (The computer that will run the client must have the proper framework installed or an exception will be thrown immediately when you run the application, and it likely won't say anything about the framework not being installed).

There are solutions inside of VisualStudio that allow you to create installation stuff for your project, I have never used this. Microsoft offers some execelent information about this in the MSDN, I suggest hopping over to it (here are some links for you):
Windows Installer Deployment http://msdn.microsoft.com/en-us/library/206sadcd.aspx
Troubleshooting Windows Installer Deployments http://msdn.microsoft.com/en-us/library/kz0ke5xt.aspx

How do I access the database from the client machines?
Once you have a dedicated computer with SQL Server installed on it, and you have it on the same network that the client machine is on, you can connect to the database using the proper connection information... Your Dedicated SQL Server install will have an IP Address, that is the ServerName. Your SQL Server Install will have databases that you have created, that is the DatabaseName. Your SQL Server install will also have users set up that can access some or all of the databases on it, those are the Username and Passwords you use. And lastly there should be a security setting which blocks incoming connections that are not on a specific port, and that is why you can supply a port with the connection string...

So, the short answer to your last question is, once you have SQL Server installed on a dedicated computer that is on the network the client machine is on, you should be able to directly access that database using a proper connection string:

Need to know the proper format for your connection string? No worries!! Go here - I use this site often because I am a forgetful programmer http://connectionstrings.com/

Hope this helps,
Sean C
Firesickle.com

One other note I wanted to make after I reread my response:

I just want to create a desktop app. ( an accounting software )
for a super Market. which will be accessed by the cashiers(clients), Manager(server).

You are not really going to have a server application in this scenario. When you say "Server" you are usually referring to a System you connect to and send information to, it deligates what needs to be done and sends messages to clients as needed. In your solution, the Database is going to act as a server, but instead of sending data down to the clients, the clients will analyze the data in the DB and make descisions based on it.

In my experience, Management functions and screens are built directly into the core program, and some form of Username/Password/Locking system is used to keep non-managers from accessing these routines... This way you have 1 application that everyone uses, and if you need to make a change to something, you don't have to update and recompile to applications.

Just a Tip, because this isn't a "best practice" thing, just a "this is what I do" thing.
-Sean C
-firesickle.com

Hello My good friend,

I think I have finally understood all you have been saying. From what I understand, you are telling me that everything centers on the connection string.

I have checked ConnectionString.com and I learnt a lot from there; they said that you will provide the name of the computer where your database is residing in the connection string.

This is what I got:
"
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;


Use serverName\instanceName as Data Source to connect to a specific SQL Server instance.

Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.
"

My question is that for the server name- Is it the name of the computer containing the database and the path to my DB? If yes how do I get the computer name that will contain my db, because I may not know during development. Is there any code to determine the server name or IP?

Or

Is the server name the name of the sql database I created alone?
Or
Do I create a server name for my the data base which will be used by my app?

Or

Will the server name(or IP) be provided by the application user during installation?

Please help me with this again, I know I am disturbing you a lot. But I need to complete this application because I have already started. And this is what is holding me for now. That is how to know this server name or IP.

Understanding computer networking basics is pretty important, I suggest brushing up on your computer networking knowledge as it seems you may be a bit rusty in this area. Here are some tutorials on installing SQL Server 2005, might help you to understand this better once you have gone through the complete installation.

http://www.youtube.com/watch?v=5Yq75lkXtL4
http://articles.techrepublic.com.com/5100-10878_11-6102265.html

And here is a description of the parts of that connection string for you to understand:

Data Source - In visual basic you will see the words DataSource in a lot of controls and components. In this case, the datasource is referring to the Server/Computer that the data resides on. In most cases this is an IP address, but as connectionstrings.com stated, SQLServer 2005 Express would like you to stick the computer name of the computer that has SQL Server installed in for DataSource

Initial Catalog - This tells SQL Server which Database you are attempting to access. A SQL Server install can have multiple databases, each of which have their own tables and data. Each database can be set up with varying settings and parameters and have specific user access configured. For This value you will specify the Database in which you want to connect to.

User Id & Password - As I stated under Initial Catalog, each database can have varying settings and users configured to use it. You must specify a username and password of a user who has permission to access that database. This is NOT a windows username, it is a username and password configured in your SQL Server installation.

My question is that for the server name- Is it the name of the computer containing the database and the path to my DB? Simply put, no... The computer name is just that, the computer name. (XP) Rightclick mycomputer, select Properties. Move to the Computer Name tab.

Is there any code to determine the server name or IP? Set the SQL Server computer up to have a static IP address, so it doesn't change. Then you can put the connection information into a flat file (text file) that is loaded by the application to connect to the database. That way if the database name, or server IP, or user info changes, you can change that text file and be done with it rather than recompiling and redistributing your code. If you are making an application you want to sell to multiple customers, you need the information to be very "Dynamic" so "hardcoding" connection values is something you want to avoid at all costs.

Is the server name the name of the sql database I created alone? The server name is either the Computer Name or the IP Address of that computer that SQL Server 2005 Express was installed on. It sounds like you want to use Computer Name with SQL Server 2005 Express.

Do I create a server name for my the data base which will be used by my app? Once you have installed SQL Server, you will need to create a database and name it. That will be the "Initial Catalog" or DatabaseName.

Will the server name(or IP) be provided by the application user during installation? Probably not unless you specify this. How you provide the information to your application is entirely up to you as the developer, and very dependent on how you go about things. If it is directly programmed in, you have to recompile and redistribute the application to change it. If it is in a text file somewhere, any random user can open that file and see the connection info of that database, and a malicious/knowledgeable user could do bad bad things then... You could encrypt the data or hash the data that is stuck into that file though and make it much harder/nearly impossibly to break. But I digress...

Hope this helps,
Mr. Campbell
firesickle.com

Hello my very good friend, I think I have understood all you have been saying thanks.

I will contact you anytime I have any problems.

Arinze.

hello friends....
i need ur help...
i am new at vb.net, i have learn it a little, i need a project in vb.net, it may b any kind of web application or desktop application. but i m interested in database application, will u please give my any project.. plz help me i am so confused. thanks

hello friends....
i need ur help...
i am new at vb.net, i have learn it a little, i need a project in vb.net, it may b any kind of web application or desktop application. but i m interested in database application, will u please give my any project.. plz help me i am so confused. thanks