How do I get started?

[chrisguk]

Hi all,

JMC will probably remember some of my new posts and this is just another one of those beginner questions.

I do apologise if my questions are very basic, but I am just starting out and would really appreciate some in depth advice.

My end goal:

I want to create a very simple (to begin with) database driven application. The company I work for have very kindly said they will host any SQL database I create, they have a remote server that we all connect to for files etc.

So I want to be able to create an SQL database that they can host and have my colleagues and I install the application local on our machines.

How to do I get started with this?

I have the following installed on my laptop:

VB Express 2010
SQL 2008 with tools
Also I have SQL R2 2008

My initial task would be to create an SQL database that is portable and I can use VB express to connect to it for the application design. How is this achieved?

If there are any good you tube tutorials you know of that will also be helpful.

 

[Cobalt]

I found the key to starting out was initially having an idea you really wanted to see through. While struggling through issues and bugs not having a final goal can really sap the enthusiasm. Sounds like you have something of a plan, so on a technical side, I would say: (only my opinions, many others might say different!)

1). Build a rough DB first, sketch the data structures you think you might need and build up your basic tables. Use something like SQL Management Studio to give fuller control than inside the VS environment

2). Create Stored procedures for Viewing, Inserting, Deleting and Updating data. I find keeping Data manipulation in the database makes development much easier, no need to code SQL directly into the Application

3). I normally import the SQLClient Namespace into the Application code and use the following: SQLConnection, SQLCommand, SQLAdaptor, Datatables. Research each of these a little bit. They are pretty easy, SQLConnection holds data and methods around the connection (Connection String, Timeouts, Open/Close connections). SQLCommand holds your SQL query or Stored Procedure name (plus much more) and uses the SQLConnection. SQLAdaptor is a container for data brought back from any query, Datatables will actually hold the data and allow you to loop through them and work with the data etc

You can, in time, maybe create a generic Database connection class that holds all this kind of data and you can reference it simply in this project or others. Early on I would suggest keeping things simple and have the database interaction inside the form/page you are building

4). Make sure you have access to the SQL database, if you can create it from your development machine you should be fine, but if you deploy the application you'll need to understand how it is secured and how the application should be hosted to allow connection to the system. How the Connection String is made up will have a big impact on this.

5). Keep the connection between the application and the database simple, and using the Stored Procedures. This will allow you to make both the DB and Code as portable as possible.

6). Put your connection string in the web.config/app.config file. Again keeps things tidy, and if your SQL database moves there is no need to recompile your code, just update the XML reference.

Other than that, it's just about looking at each step of your program and dealing with each one in turn. I don't know your programming background, but I often start by putting my comments in to the code first, then build the actual code around it - a bit like a to-do list.

' Connect to the database
' Pull all the data from my 'Customer' table
' Loop through each item
' If the Customer name is 'John Doe' show a message on screen
' Save the note to a text file

With each step split out you can then search and query around each one in turn, keeping it all small and simple.

Others might have views on VS and SQL connectivity but I've never really used the built in Wizards and controls inside VS, I just prefer the code way and I now have a library of code to fall back on for any project.

Keeping asking questions (as specific as possible), and good luck!

 

[chrisguk]

First of all I wish to thank you for your in depth reply

Historically I have worked on PHP, ASP and VBA, although I understand that some of the code is present in VB, VBA is very much different in other ways.

Gettting around the compilation of the application is not my issue by far, I always seem to get by on that note. My main issue is SQL databases, I am completely new to this and dont really understand how they work, although I am very familiar with table structure etc from my MySQL days and Access.

One of the things that trouble me most is how do I move my database around as its not so transparent as lets say MS Access because you make you tables and have a physical file to move around.

My reason for choosing SQL is primarily down to performance. We operate on a WAN via VPN to our UK base and I found that Access DB just isnt fast enough. I wonder actually am I going to have the same issue with any database that has a local application running which requires to access the data across WAN/VPN. Maybe I should think of something more simple and create some local file sharing at this office?

 

[Cobalt]

I'll leave the coding bit for the moment, since you know what's going on with that one.

SQL DB's are, in the background just files (data and log). These are hosted by the SQL Server instance and made available to external users (applications or people using management tools). The SQL instance can host multiple database and give huge amounts of control and options (as I'm sure you know).

Portability can be done in a couple of ways. The first way would be a backup and restore. Once a SQL Database file is being hosted by a SQL instance it can be backed up to a .bak file. This single flat file can be copied to any other SQL server and restored (there are some limitations, e.g moving a 2008 backup onto a 2005 SQL server).

You can also restore to the same box, just renaming the destination DB. This is good for things like Dev and UAT databases.

At this point you could have copies of the database running in multiple SQL servers under the same name, or at the same SQL server with each DB having a unique name. Inside your application code you would then just change the connection string to the correct version. You might have one for connection string to Server A for development, but another connection string to Server B for the live instance.

The second way would be to take the DB offline on the one server, move(or copy) the underlying .mdf files to another server and bring it online on another server.

The key difference with SQL, is that you need a SQL server to host the DB. With Access you could just send a small database to your client machines and run it locally, this will not be the case for full SQL, it needs to be hosted in a SQL server instance.

Performance wise, you will not see much (if any) network improvement from using SQL over Access. Assuming you know the delay is network based, and not the design/processing power of the database you might be a bit stuck. The only thing that could be looked at is the Timeout settings on your Connection string or Application configuration.

In an ideal world you would have your SQL server and your client applications on the same physical network and see no network delays at all. The more separate you have the DB and Application the more network delays will cause problems. Some efficiencies could be made, but its always a battle.

In general, SQL server would the way to go. Access is not the right solution 95% of the time, and nothing should be done writing to files (like csv or text for example), thats just asking for trouble!

 

[cjard]

"One of the things that trouble me most is how do I move my database around "

You dont: google don't move the files their web servers serve up around.. SQLS is no different. There's a data file on disk in your company HQ, and a server (SQLS) that reads in and accepts commands.. Your app says "select * from person where name = 'smith'" and the server looks in the data file and sends your client only the data that matches the query - you dont move the data file around, you dont put it on the client machine any more than microsoft send you every page on their site when you ask for the home page

You should start with something really simple, like SQLS Express running on your machine.. Then deploy your app (but not sqls!) to another machine on your LAN and get that machine to conenct to yours.. Then take the step of putting the SQLS at HQ and getting 20 of your clients to access it over VPN

Keep in mind when you design your app; only request the data you need.. DONT say "select * from person", download a million rows into the client and then use a local For Each loop to find the first one named smith.. Same goes for the .Filter or .Select() methods of datatables/dataviews

For a good tutorial on working with databases, read the DW4 link in my signature, section Creating a Simple Data App