Bot user Database

[ObsidianWolf]

I was thinking of using an access database instead of just a text file for keep records and such.  Does anyone have any remarks, suggestions or comments on either?

[MyndFyre]

Depends on how you plan to implement it and how extensible you want it to be.  It's important to plan ahead what kinds of fields you want your database to contain, how to prepare for upgrades, and that sort of thing.  These kinds of things are done easiest with SQL Server, IMHO; however, provided that you know SQL, you should be able to run an Access database fairly easily.  SQL basics are relatively easy, anyway.  Something to pay attention to, that I've struggled with, is the handling of strings with the apostrophe (') in them.  In C#, which is my primary language (this is true of Java as well, and IIRC, C++), string literals must be enclosed with double-quotes.  SQL is not so demanding, and we typically find this kind of SQL statement:

Code Select Expand


SELECT * FROM Members WHERE MemberName="It'sABadStatement";


This statement, in a language such as I mentioned, has to be enclosed in double-quotes:

Code Select Expand


// generates a compile-time error
string strSel = "SELECT * FROM Members WHERE MemberName="It'sABadStatement";";


and so we can use single-quotes (the apostrophe):

Code Select Expand


// generates a SQL runtime error
string strSel = "SELECT * FROM Members WHERE MemberName='It'sABadStatement';";


or we can use escape sequences:

Code Select Expand


// runs correctly
string strSel = "SELECT * FROM Members WHERE MemberName=\"It'sABadStatement\";";


So, if you're going to use apostrophes and SQL syntax like I do (the second type is my preferred way), be sure to guard against apostrophes that might end up in your code.  For example, if I want to insert a record into my database, I prevent anything that might contain an apostrophe from entering the apostrophe, as in this example:

Code Select Expand


// bad code
string strIns = "INSERT INTO Members (MemberName) VALUES ('" + this.tbMemberName.Text + "');";

// good code:
string strIns = "INSERT INTO Members (MemberName) VALUES ('" + this.tbMemberName.Text.Replace("'", "__sql_apos__") + "');";


Then, when you're retrieving the value, since it's nearly impossible that someone will have inserted the same "__sql_apos__" string as you did to replace apostrophes, you can just reverse the replacement when you select the data from a database.

I hope this helps you out.  I've found that an actual database is much easier to maintain and upgrade, as well as enforce constraints and rules, whereas a text file is easier to get started with.

I would go the database route.  Good luck - I'll be around if you want any help.

--Rob

[ObsidianWolf]

Well *chuckle*, That was quite imformative, thanks for the advice and direction.  I know a bit of sql, though your comments on the apostrophe were awesome.  Thanks for your help.  I shall keep you in mind when I start going into caffiene withdraws and my brain begins to shutdown.