Database

Re: Using the database from C#

25-Jun-09 11:22

kikeman wrote:
My question is:
Would there be any problem if more than one application would be reading and writing to this database AT THE SAME TIME?

Yes. It's not designed with the intention of serving lots of users at the same time, and it doesn't perform well in that scenario. Microsoft Access is ideal for desktop-use, but rather a pain when you're trying to use it with some people (some being more than one).

kikeman wrote:
Should I setup an special configuration on the database for this kind of scenario?

Depending on your particular Office-installation, you may have a Wizard to upsize your Microsoft Access database to a SQL Server database. SQL Express is a free download, and would perform better on that network drive.

The wizard doesn't convert forms or reports. You can, however, import a link (in the Microsoft Access database) to the tables in SQL Server/SQL Express and base your reports and/or forms on that. That way you can still benefit from the advantages of developing in Access Smile | :)

I are troll Smile | :)

Mycroft Holmes25-Jun-09 17:22

Mycroft Holmes

25-Jun-09 17:22

I vote with Eddy, chuck Access, it is the wrong tool for the job. Use SQL Server, it is ideal for departmental processing.

Never underestimate the power of human stupidity
RAH

Re: Using the database from C#

kikeman26-Jun-09 7:30

kikeman

26-Jun-09 7:30

Thanks for your reply guys,

The problem would be now to "import" the ACCESS_2003 tables to the SQL Server Express Architecture.
Do you know if is there any tool for this convertion?
Does this tool comes with the SQL Server Express?

I think it would be better to move all database to SQL Server architecture, now that the database is not that bit at the moment. The forms and reports will be replaced with some Visual C# GUIs anyway.

What way of datastorage should be use for small aaplication

Member 442053425-Jun-09 1:47

Member 4420534

25-Jun-09 1:47

Hi all,

I never really played with small applications.Now I want to write an application, really small.Its only going to be used by a homeuser.

Therefore its not really going to be that smart using SQL server.
But I need some suggestions.What will be the best technology?
I thought of XML, but Im not that clued up with it and it gives me the idea it will get tricky with big functionality.And then theres MS Access, but on the net it looks like its not really being used as much anymore.What will be the practical thing to use?
Thank you

MS-Access

David Mujica25-Jun-09 2:55

David Mujica

25-Jun-09 2:55

My vote is for MS-Access. It isn't going away anytime soon. It will work fine if it is a single user application. Keep things simple. Cool | :cool:

Re: What way of datastorage should be use for small aaplication

Niladri_Biswas25-Jun-09 5:07

Niladri_Biswas

25-Jun-09 5:07

Either go with MS Access or MySql
Smile | :)

Niladri Biswas

Re: What way of datastorage should be use for small aaplication

J4amieC25-Jun-09 5:52

J4amieC

25-Jun-09 5:52

I would go with SQL Express a million times over MS-Access.

Re: What way of datastorage should be use for small aaplication

David Skelly25-Jun-09 6:22

David Skelly

25-Jun-09 6:22

You don't say what technology you are using for the application as that may offer you different choices.

For example, if it's Java you could look at JavaDB, or HSQLDB (H2), or DB4O. Or, if you don't need a full-fledged database you could simply serialise your objects out to disk (or maybe use the built-in XMLEncoder class). I think DB4O might also have .NET support as well, I'm not sure.

For C or C++ you have SQLite among others.

Python also supports SQLite, and also has various options like pickle, shelve, gadfly.

SQL Server Express might actually a reasonable choice: it can be used embedded from a number of different languages and it is free (as far as I am aware if you are using it embedded in a single user application there are no licensing costs). FireBird is also an option for this sort of thing if you don't like SQL Server for whatever reason. I would say Access is not a bad choice for a stand-alone single user system, I would not agree that it is "not being used much anymore". I've seen several .NET applications that use it internally as a local data-store on a user's PC.

FoxPro is a complete all-in-one database and development environment (I haven't seen it in years myself but as far as I am aware it is still going strong with an army of loyal fans).

Or if all else fails you could simply write the data out to a text file on the disk in some sort of structured format. If your application is small and data volumes will be low, that might be the easiest option. If security is a concern you will need to work something out to encrypt the data.

I guess the short answer is, "It depends..."

Re: What way of datastorage should be use for small aaplication

riced25-Jun-09 6:25

riced

25-Jun-09 6:25

I think it boils down to a choice between Access and SQL Server Express.
Off the top of my head here's some factors to consider.
Access pros: self contained, built in form designer, query wizards, reports designer etc. Can use VBA to enhance functionality.
Access cons: cost money (unless you already have Office Pro), can be slow over networks (even LANs). Not as extensible as SSE - but not an issue for home use.

SSE pros: 'proper' database. Easily extensible. Lots of help on internet/MSDN forums.
It's a free download. Good performance on networks.
SSE cons: need a front end (VB.NET Express or C#? - both free) to construct forms etc.
Needs a bit more management than Access.

There are also some free/open source databases you might consider (e.g. SQLLite) but I'd avoid MySQL because it's fairly heavy duty so probably over the top for what you need.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Re: What way of datastorage should be use for small aaplication

Eddy Vluggen25-Jun-09 6:26

Re: What way of datastorage should be use for small aaplication

25-Jun-09 6:26

The program "MSAccess" costs money, but you're not required to purchase the GUI Smile | :)

I are troll Smile | :)

Eddy Vluggen25-Jun-09 6:25

Re: What way of datastorage should be use for small aaplication

25-Jun-09 6:25

SQL Server Express is free, just like MySQL. Both are database-servers, usefull if you want to work with large amounts of data. Oracle 10g Express would be the third option if you require a "real" database.

SQLite, SQLCE and MSAccess are good options for smaller databases. MSAccess has been around for some years, and the drivers are usually found on most systems. What's more; Access-databases can contain reports (fully integrated with Office, viewable in MSWord) and you can use the MSAccess-runtime to show them.

SQLCE is relative new, feels faster and more stable than MSAccess. It doesn't have embedded reports, and the drivers are probably not installed on the host-machine.

I haven't used SQLite intensively, so I haven't formed an opinion on that one yet. Seems to be a viable alternative for SQLCE though.

If you don't need an actual database, but still want to store "some" data, look at XML and LINQ-to-XML.

Hope this helps Smile | :)

I are troll Smile | :)

David Skelly25-Jun-09 22:39

David Skelly

25-Jun-09 22:39

I may be wrong, but I think that LINQ-to-XML only works with .NET 3.5. Not many home users will have that installed yet. It requires a 250Mb download just to install the .NET 3.5 framework, which might be a problem for some people especially since this is just a small home-user app. ("Our new application is just 100K, which you can download and install in seconds... after you spend an hour and a half trying to download and install all this other garbage that you don't really want.")

SQLite is fast and has a very small memory footprint. That's its main advantage.

SQLite datatypes are a bit weird because you don't tell it in advance what the datatype of a column will be. SQLite figures it out itself dynamically. This is mostly OK but every now and again it can surprise you (among other things, SQLite thinks it's OK to store string values in a column intended for integers without warning, it will happily convert from floating point numbers to integers according to its own internal logic, and so on). Also, there is no support for date/time values. If you want to store these, you have to figure out your own solution.

The biggest potential problem is that it is a single user database. Every time you update something, it locks the entire database (yes, the entire database not just the table being updated). May not be a problem for this particular application if it's a small home-user app but SQLite does not scale in terms of number of users.

Personally, I don't like SQLite. It's popular with C and C++ people and some Pythonistas but I just don't see the point of using it when there are better lightweight databases available these days.

Re: What way of datastorage should be use for small aaplication

Eddy Vluggen26-Jun-09 0:17

Re: What way of datastorage should be use for small aaplication

26-Jun-09 0:17

You're right, that's a large download if you just want to save some application-data.

David Skelly wrote:
SQLite datatypes are a bit weird because you don't tell it in advance what the datatype of a column will be.

Like I said, I haven't used it much just noticed that it converts all datetime values to string.

David Skelly wrote:
The biggest potential problem is that it is a single user database.

That's also true for SQLCE. MSAccess has some more options, but isn't intended for this scenario either. They can however, be used as readonly-datastores by multiple users.

David Skelly wrote:
Personally, I don't like SQLite

Me neither, but it is still an option that may be considered. I'd be using SQL Server Express Smile | :)

I are troll Smile | :)

Andy_L_J26-Jun-09 19:14

Andy_L_J

26-Jun-09 19:14

You could alsways try www.sqlite.org[^]

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

Split table

sujithkumarsl24-Jun-09 20:46

24-Jun-09 20:46

Hi i want to split one large table into two. But here my problem is, i need to query from both the tables, which should give the data same as the parent table.
For example If the large table is like

COLA    COLB
     1       A
     2       B
     3       C
     4       D
     5       E

and if i split this into
FIRST table

COLA   COLB
     1      A
     3      C
     5      E

Second table as

COLA   COLB
     2      B
     4      D

Now i want to query the two tables, so that i should give the value order by COLA. Result shold be like

COLA    COLB
    1       A
    2       B
    3       C
    4       D
    5       E

My small attempt...

WoutL24-Jun-09 21:55

WoutL

24-Jun-09 21:55

This should do the trick:

select cola, colb
from(
   select cola, colb from table1
   union all
   select cola, colb from table2
) d
order by cola

But I don't see why you should split a table in two.

Wout Louwers

sujithkumarsl24-Jun-09 22:01

24-Jun-09 22:01

Actually i have one billion records in this table all are logs from a server machine. But only some specific error logs are used to process. To get better performance i am planning to do the same

My small attempt...

J4amieC25-Jun-09 0:26

J4amieC

25-Jun-09 0:26

You'd be better off archiving out old records and properly indexing your main table.

sujithkumarsl25-Jun-09 0:30

25-Jun-09 0:30

i am creating some reports by parsing the error logs in that tables. SO i need all the error logs in the table. That y planed to move irrelevant data.

The table is indexed...

Any other technique?

My small attempt...

Mycroft Holmes25-Jun-09 0:54

Mycroft Holmes

25-Jun-09 0:54

Look into partitioning the table by periods.

Never underestimate the power of human stupidity
RAH

sujithkumarsl25-Jun-09 1:15