Posted by: Dale Franks on Wednesday, October 25, 2006 My day-to-work consists mainly of building software applications that add, edit, and delete data from databases. Most often, I use SQL Server or Microsoft Access as a data store. Since we switched over to a .Net shop a few years ago, I created a standard data access class to handle all of the heavy lifting of grabbing adding, or changing the data. I know there are a lot of developers, both professional and hobbyist, who do programming who read the blog, so I thought I'd offer the class to anyone who wanted it. The class assumes that you prefer to operate disconnected from the database, in order to increase scaleability. In the environment in which I work, there are limited number of concurrent database connections, so we always open the database connection, extract or modify the data, then close the database connection. This allows a large number of users to utilize a relatively small number of concurrent database connections. This also works well for web applications, where the number of potential users is far higher than the number of concurrent database connections you can afford to buy. So, I've uploaded this zip file containing the source code for the class. There are two files in the zip archive, one of which contains the VB.NET version of the class, and the other contains the C# version. Alternatively, you can simply copy and paste the source code from these text files: The VB.NET version is here, and the C# version is here. The C# version, by the way, is already commented using the appropriate XML tags for automatically creating documentation for the code in an XML file. There's nothing really spectacular about them, I've just found that they make accessing databases easier, and they are generic enough to fit into any application. Please feel free to use them if you think they might have some utility for you. Each class contains the following elements for both SQL Server and OleDb databases: PROPERTIES: SQLString: A public string to which you pass the SQL statement you wish to execute. TargetTable: A public string containing the name of the dataset table into which you want to add data using the CreateDataset procedure. ConnWD: Publicly accessible connection to the database. adocWD: Publicly accessible ADO command. dtrWD: Publicly accessible Data Reader. daWD: Publicly accessible Data Adapter. dsWD: Publicly accessible Dataset. METHODS: OpenBasicReader: Opens the database and creates a DataReader to transmit the data that corresponds to the SQLString property. CloseBasicReader: Closes the DataReader and closes the database connection. OpenParameterSet: Creates a database connection and command object so that the user can set parameters for database queries. OpenParameterReader: Opens a DataReader using the connection and command that were created by the OpenParameterSet procedure, and the command text of the SQLString property. In general, you call the OpenParameterSet procedure to open the database connection and command, add your required parameters to the command object, then call the OpenParameterReader procedure. CloseParameterReader: Closes the parameterized DataReader opened by the OpenParameterReader procedure. ExecuteDataUpdater: Executes a non-query SQL statement from the SQLString Property on the command and connection created by the OpenParameterSet procedure. In general, you call the OpenParameterSet procedure to open the database connection and command, add your required parameters to the command object, then call the ExecuteDataUpdater procedure to run UPDATE, INSERT, or DELETE SWL statements. CreateDataSet: Creates a dataset, and fills the table named in the TargetTable property with data. This is especially useful for filling a combo box or list box with data. SQLDate: Converts a DateTime value into the standardized UTC DateTime string format used by Microsoft SQL Server. INPUT PARAMETERS: TestDate - The DateTime value that must be converted to the SQL Server UTC format. RETURNS: The String value containing the SQL UTC-format date. |
|||||||||||||||||||
| TrackBacks | |||||||||||||||||||
| Return to Main Blog Page | |||||||||||||||||||
| Comments For This Post | |||||||||||||||||||
Previous Comments to this Post |
|||||||||||||||||||
|
|||||||||||||||||||
| Add Your Comment | |||||||||||||||||||
|
|
| Vicious Capitalism |
Buy Dale's Book!
|
