Best database to use with vb.net applications

[Ultrawhack]

Hi,

I'd like everyone's opinions on this please. What would be the best backend database to adopt with a vb.net application - assuming the app is going to be deployed on a per-user basis, 1 user 1 backend - no backend sharing via intranet, etc.

I'd also like to know your opinions on strengths/pitfalls of various db formats.

Thanks !

 

[cjard]

here's another one:

http://www.devguru.com/technologies/jetsql/16879.asp


both articles suggest that you CANNOT modify the size or type of a column in access through DDL but there's nothing stopping you doing this:

CREATE TABLE tbl(col TEXT(50));
'fill col with data, release db into wild, find out 100 chars needed
ALTER TABLE tbl ADD COLUMN tmpCol TEXT(50);
UPDATE tbl SET tmpCol = col;
ALTER TABLE tbl DROP COLUMN col;
ALTER TABLE tbl ADD COLUMN col TEXT(100);
UPDATE tbl SET col = tmpCol;
ALTER TABLE tbl DROP COLUMN tmpCol;
'now also recreate any inexes, relationship and keys col might have had

I just know someone is going to pipe up and say "but then that means the columns are out of order if there were more than 1 column in the table"..

..Well, yeah.. Bet you wished you'd used column names instead of ordinals in your app now huh?
-> always use column names in code, not ordinals (or if you must use ordinals, calculate them at app startup from the names ) because code with magic numbers in is hard to read

 

[cjard]

Well dang... shoot.... Looks like I'm wrong on this one....
Now, for the record, I know it can be done with SQL Server using the .ExecuteNonQuery of the SQLClient class....
And I do know that Access can run DDL queries (ALTER TABLE and the like).... BUT I couldn't find a way to run such queries in the OLEDB class....

I did it something like this:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim x As New OleDb.OleDbConnection(My.Settings.ddhubConnectionString)
        If x.State <> Data.ConnectionState.Open Then x.Open()

        Dim y As New OleDb.OleDbCommand(TextBox1.Text, x)

        Try
            MessageBox.Show("" & y.ExecuteNonQuery())
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

This app has:
Form3 - a form
Textbox1 - a textbox on Form3
Button1 - a button on form3
An access MDB added with the .NET DP for OLEDB
The conenction string saved in the app settings

 

[cjard]

If making structural changes to end users backend db is possible, then I may consider keeping Grandma's record collection.mdb a little longer. Look forward to your sample TechGnome.

Shoulda kept my mouth shut!

 

[Administrator]

Bottom line is that MS Access is "trying" to be phased out by Microsoft. I honestly don't see that happening unless they figure out a way to put SQL Express under the UI of Access and rename the product. However, I invite your opinions on VistaDB v3 as it moves towards production. Right now 2.1 is their latest, but v3 looks like it may be the time for me to further investigate. I'd personally really like to deal with nothing but SQL Server/SQL Express, but people like Microsoft forget people still have dialup connections and shareware vendors cannot have 30-50 MB downloads for their shareware apps!

 

[Ultrawhack]

So, as this thread-starter, let me ask...
are we all in agreement SQL Server 2005 is the way to go ?

I'm looking to futureproof our apps for 2 years with easy patching, updates, not to mention structural changes to end users backends.

I heard that we can package the 100Mb frameworks overheads using ClickOnce as these are legally redistributable. So that will be of some consolation to our dial-up end users.

 

[Administrator]

In my opinion, if you are not worried about deploying a 30+ MB installation, i.e. dialup users, I would use nothing but SQL 2005. No ands ifs butts! However, as a shareware developer myself and serving an International community, I have a different problem!

 

[Ultrawhack]

Phasing out Access ?

Bottom line is that MS Access is "trying" to be phased out by Microsoft.

Well the new Access 12 is almost out and millions are excited but...

Check out the new Access to SQL Server 2005 Tool straight from the MS Access Lead Program Manager's mouth. Let's make Grandma's makeover easier...
http://www.utteraccess.com/forums/showflat.php?Number=1195958
Now if only it would work !

 

[cjard]

So, as this thread-starter, let me ask...
are we all in agreement SQL Server 2005 is the way to go ?

I'm looking to futureproof our apps for 2 years with easy patching, updates, not to mention structural changes to end users backends.

I heard that we can package the 100Mb frameworks overheads using ClickOnce as these are legally redistributable. So that will be of some consolation to our dial-up end users.

I think perhaps that you (in respect to your dialup users) are thinking of things in slightly the wrong way.

Think of SQL Server Express (SSX) as like the .net framework.. Once you have it, you can use it to run apps that require it. FOr a long time Jet drivers came bundled with the operating system. If SSX drivers did, there wouldnt be this complaining about 35 - 89 megs download. You can present to your users that if SSX is missing from their system, then it's like Java is missing, or .NETFW is missing; they have to download it!

Dont distribute it with your app and make your app a 100meg downlaod for the same reasons you dont distribute .NETFW with your app. Tell users to go to MS and get it or make setup do it etc

The other thing i wanted to point out was that SSX is a heavyweight database with a good pedigree. If youre writing an app for grandmas record colelction, then keep it with access. If youre marketing an accounts package to companies, and hence want a decent database, then youre dealing with a target market that left dialup way behind long ago. Increasingly the web is fobbing people off if they are still on dialup; pages are more intense and broadband is becoming so cheap and ubiquitous that dialup is making less and less sense. The user who has pay-as-you-go dialup to check their hotmails once a week probably isnt going to be the sort of user who will download a record collection app at all.. Save yourself the headache of trying to be considerate to those who dont move with the times. Harsh.

 

[cjard]

In my opinion, if you are not worried about deploying a 30+ MB installation, i.e. dialup users, I would use nothing but SQL 2005. No ands ifs butts! However, as a shareware developer myself and serving an International community, I have a different problem!

Point well noted, but.. Do you develop in .NET? Probably. Hence, do you worry about having to tell users to go get the .NET2 Framework? Probably not.
If they want to use your, and other, apps they will get .NET FW
If your, and other, apps need SSX then they will go get it too

Point it out on your download page alongside the "This app needs .NET2. IF you dont have it you can d/l it from MS here" box

 

[dellman]

Why Use a database?

For a one user one database system I actually do not use a database at all. Depends on the number of tables but if you have only a couple of tables and no relationships to speak of (other than those you maintain by virtue of design) then why not use a DataSet object that you serialize and deserialize from XML? It is simple, elegent and requires absolutely nothing more than .NET. Also, you can alter the structure at will and when you deserialize an older object you can detect if it has the correct structure and if not, just update it so that when you serialize it again, it has the correct structure. You will not run into deserialization headache unless you try to use a Typed Dataset. You have nothing to install (ie: SSX, jet drivers,etc.), You have no services to run (ie: SSX), no memory overhead, the serialization and deserialization process is fast, ie: 1000 records in less than a second. Comments anyone? If you need working code on the serialization and deserialization for .NET 2005 I can post it.

Cheers.

 

[TechGnome]

I've thought about that in the past, but usualy I need some kind of querying or reporting system that makes XML unviable.

But.... I've wondered.... for cases where I have lookup info that will never change (like a list of states or something) it would be nice to the have the XML serialized into a resource item in the project, then load the datatable from there... have any idea if that can be done?

-tg

 

[Ultrawhack]

I'd like to hear about that. I started another thread as it seems another topic.
http://www.vbdotnetforums.com/showthread.php?t=11823

 

[dellman]

In response to TechGnome: If your using a dataset to store your data and you just serialize it to your XML and back again you are all set for reporting. As you know Crystal which is the tried and true standard of reporting for vb has a nifty little method called SetDataSource and the parameter it takes is a dataset. The dataset can be typed or non-typed as long as the report was created using that method. Although I have never personally tried it, I have heard that Crystal accepts XML data sources as well.... however, if your just using the dataset itself you do not have to worry about crystal and XML. The only hard part would be to get the actual report created since you don't have an actual datasource to build the report onto, but I am certain that with a little ingenuity and perseverence that little problem could be worked out too, since most of us have SSX, MSDE AND Sql server 2000 AND SQL server 2005 installed on our respective dev boxes.

 

[ashishnaicker]

I think that the XML idea is awesome. There is no problem in creating a Crystal Report based on strongly typed dataset as this is very well managed by Crystal Reports. Just select dataset from projects in the select database dialog box and after that you wouldn't even realize that you are not using a database...

 

[dellman]

I think that the XML idea is awesome. There is no problem in creating a Crystal Report based on strongly typed dataset as this is very well managed by Crystal Reports. Just select dataset from projects in the select database dialog box and after that you wouldn't even realize that you are not using a database...

However, getting your strongly typed dataset to exist in the first place without a database, I have never tried this. I assume that its easy, I just never bothered. Besides, with SQL 2005 or SSX with the new SQL Server Mgmt Studio creating the shell of a database to base your Strongly Typed dataset on is childs play. Then, just cut the database out of the scene and use XML exclusively. Again, it can be a pain if you have a large number of relationships and you want to keep them intact, but overall, its a great way to store small amounts of data that need to be kept local and not sent to the server.

Another idea for using XML serialized datasets are if you do have a server and you do connect to a server. Why? well, lets just say you have some data you want to store on your database on the server but your users network has gone "down" and you no longer have a connection to your ever reliable database. You could create methods in the shutdown and startup of your application (of course your write to the server failed gracefully because we all use try...catches ... right?).... and this code detects if there are any pending writes to the server. If there are, and it cannot flush them to the server, it serializes an XML file to the local machine and informs the user that their data was saved, but it was not saved to the server. This works for any environment where local saving of data is not a no no (ie: health care industry).