The attached zip file contains two projects - a database component (DLL) project and a Windows forms project called Test. Both projects are written and compiled using Visual Studio .NET version 1.0.
Database project is a DLL Project.
Test project is a windows forms application that demonstrates the use of Database component. You'll have to run the S.sql file to build the SQL database that the Test program uses. Use the attached "Supplier" database for Access.
The Databases DLL allows you to connect to any MS SQL or MS Access database, execute SQL commands (Insert, Update, Delete, Select, Create, Alter,...) over Tables, and most important of all, it allows you to insert Serializable objects (Bitmap,...) or files into table cells defined as {Binary or Image} in SQL or {OLE Object} in Access, and also Retrieve these data from cells later.
The code in the DLL is heavily commented and XML documentation (CodeCommentReport) is used, so it's very easy to understand.
The constructor parameters are :
- DataProvider type:
DataBaseObject.dataBaseProviders.MS_Access or DataBaseObject.dataBaseProviders.MS_SQL
- DataSource:
The .mdb file path in case of Access or the <workstation ID>\<SQL Server Name>.<database> name in case of MS_SQL
- Table and/or Columns names:
string containing the names of Tables and Columns (seperated by commas) to be selected from the database and populated in the dataset.
- Select statement:
string containing the select command that defines the Tables and Columns to be selected from the database and populated in the dataset.
- primary keys:
optional parameter. ( null in most cases). to be used in future functions
InsertFileDataToCell(string fullFilePath, string columnName, object [] rowData, int [] rowColumnIndex )
Inserts file data to a cell in the DB
fullFilePath: the path of the file to insert
columnName: the dest. column to insert into
rowData: array of objects that Identify the row, into which the file should be inserted
rowColumnIndex: the indexes of the columns, corresponding to the rowData entered
To be able to add any binary objet to a DB, it must be in the form of Byte []. The only way I Could find to translate an object to a Byte[] was to serialze it in a MemoryStream, then call the To Array() method of the memory stream to return a byte array that can be save in the DB.
The opposite take place in the retrieve.
Similar to this function is the InsertObjectDataToCell(...)
and the RetrieveBinaryDataFromCell (...)
The code comments explain how everything else is done.
- Sometimes, the Access databases throw an exception when trying to insert a File/Object into it
- The SQL colums to which a file/object is inserted , should better be defined as image not binary
- Sometimes the Retrieve function that returns an object throws an exception on deserialization while the one that writes to a file works OK.