Stitch.Database Sample Code

Stitch.Database puts four classes at your disposal. These are:

DBConnection - Initialise the connection to the database.
DBResult - Retrieve results from a query.
DBRow - Contains an associative array of column names to cells.
DBCell - Basic unit of DBRow - Contains the value for a column.

In addition, there is one Enumeration, DatabaseType, used to decide which type of database is being used (Access or SQL Server).

Below is a detailed description of each of these classes, with infomation on the properties and methods you can use.

Unfortunately at the time of writing this reference manual is incomplete, and so only discusses the important methods avaliable for the classes. However, please check the latest documentation online, as it may have been updated.

DBConnection
DBConnection creates a connection to a database, and is used for querying the database.

Constructor (3 Overloads):
Overload 1 (Use for Microsoft Access databases only):
DBConnection (DatabaseType DBType, string DatabaseFile)
Parameters:

DBType - This must be set to DatabaseType.Access

DatabaseFile - The path and filename of the Access database file you wish to use.

Overload 2 (Use for SQL Server only):
DBConnection (DatabaseType DBType, string Server, string Username, string Password, string DatabaseName)
Parameters:

DBType - This must be set to DatabaseType.MSSQL

Server - Name of the server, usually "localhost".

Username - Username to use.

Password - Password to use

DatabaseName - Name of the database you wish to connect to.

Overload 3 (Use for SQL Server only):
DBConnection (DatabaseType DBType, string Server, string DatabaseName)
Parameters:

DBType - This must be set to DatabaseType.MSSQL

Server - Name of the server, usually "localhost".

DatabaseName - Name of the database you wish to connect to.

Notes: This will assume use of a trusted connection, using windows authentication.

Close (All databases):
bool Close ()
Parameters: None
Notes: This closes the connection to the database. While Close() is called by the destructor, you should also call it yourself to keep things clean.

SelectQuery:
DBResult SelectQuery (string QueryString)
Parameters:

QueryString - A string that contains the SQL query.

Notes: This method should not be used for queries such as UPDATE, INSERT, CREATE, etc. It is designed only for SELECT based queries.

NonSelectQuery:
int NonSelectQuery (string QueryString)
Parameters:

QueryString - A string that contains the SQL query.

Notes: This method should be used for queries such as UPDATE, INSERT, CREATE, etc. It returns the number of rows affected by the query, and is unsuitable for SELECT based queries.

StoredProcedure:
DBResult NonSelectQuery (string SPName, params DBParameters[] dbParams)
Parameters:

SPName - The name of the stored procedure.

dbParams - An optional list of parameters to be used for the query. See the Samples page for more details.

Notes: Returns a DBResult full of all the returned columns and values. This method is experimental, and is designed for SELECT based queries. However, it may work for all stored procedures. Please use our Feedback form to report any problems with this function.

DBResult
DBResult stores the results obtained from a call to DBConnection.SelectQuery or DBConnection.StoredProcedure, and uses FetchArray to iterate through the rows returned.

FetchArray:
DBRow FetchArray ()
Parameters: None
Notes: A call to FetchArray() returns a DBRow containing columns and values for a single row. Use a loop to call FetchArray multiple times to get all the rows returned. See the Samples page for an example.

Close :
bool Close ()
Parameters: None
Notes: This closes the current DataReader used on this connection. You MUST call Close() on a DBResult that has been used if you wish to use it again.

DBRow
DBRow inherits directly from NamedObjectCollectionBase, and is just a fancy collection of DBCells.

Indexer:
DBCell Index [ColumnName]
Parameters: None
Notes: Use the indexers ([]) to access a DBCell at the specified column name. For example:

DBRow row = dbConn.SelectQuery(strSQLQuery);
DBCell authorNameCell = row["AuthorName"];

DBCell
DBCell is a single cell from a DBRow. By providing implicit operators for different data types, you can access values without needing to convert to different types explicitly. For example, you can do:

string UserID = dbRow["UserID"];
int UserID = dbRow["UserID"];

And UserID will be assigned the right type of value (int or string) each time. This feature works for String, Int32, Int64, Decimal and System.DateTime. However, if you you need to be certain the right type will be returned (in case there is ambiguity between function calls), you may do the following:

string UserID = dbRow["UserID"].String;
int UserID = dbRow["UserID"].Int32;

Again, this feature works for System.String, Int32, Int64, Decimal and System.DateTime.

DatabaseType
DatabaseType is a simlpe enum that denotes the type of database you wish to use - access or SQL server.

DatabaseType:
enum DatabaseType
Values:

Access - Microsoft Access

MSSQL - Microsoft SQL Server