There used to be Wizard in the Visual Studio IDE which wrote the CPP and H files of a CRecordset derived class just by pointing at a table in an Access database. No more, or I can't find it in the zillion options of the IDE, or I'm not good at searching on line. Anyway. In the end I decided that, since I was going to have to do the work on many tables I may as well write the wizard myself, in C# to give me more practice in that language.
Here's the overview of the application, RecSecMaker:
I'm now really glad I wrote it. When a database table has more than 3 or 4 columns doing this stuff by hand becomes long winded and error prone. Now (cliche warning) in just a few clicks the whole class is written for me immediately and automatically.
Here is an example of the output of the program, first the H file:
#pragma once
#include <afxdb.h>
class CCustomersRecSet: public CRecordset
{
public:
CCustomersRecSet(CDatabase* pdb = NULL);
int m_iCustomerID;
//(System.Int32)
CString m_csCompanyName;
//(System.String)
CString m_csContactFirstName;
//(System.String)
CString m_csContactLastName;
//(System.String)
CString m_csAddress1;
//(System.String)
CString m_csAddress2;
//(System.String)
CString m_csAddress3;
//(System.String)
CString m_csPostalCode;
//(System.String)
CString m_csTitle;
//(System.String)
CString m_csPhoneNumber;
//(System.String)
CString m_csFaxNumber;
//(System.String)
CString m_csEmailAddress;
//(System.String)
CString m_csNotes;
//(System.String)
BOOL m_bAcceptsSpam;
//(System.Boolean)
CString m_csWEBAddress;
//(System.String)
BYTE m_iSexId;
//(System.Byte)
int m_iCountryId;
//(System.Int32)
CString m_csContactMiddleName;
//(System.String)
CString m_csAddress4;
//(System.String)
int m_iCompanyID;
//(System.Int32)
virtual CString GetDefaultSQL();
virtual void DoFieldExchange(CFieldExchange* pFX);
virtual CString GetDefaultConnect();
};
And here is the CPP file:
#include "stdafx.h"
#include "CustomersRecSet.H"
CCustomersRecSet::CCustomersRecSet(CDatabase* pdb /*= NULL*/) : CRecordset(pdb)
{
m_nFields = 20;
m_nDefaultType = dynaset;
}
CString CCustomersRecSet::GetDefaultSQL()
{
CString SqlString = L"SELECT * FROM Customers";
return SqlString ;
}
void CCustomersRecSet::DoFieldExchange(CFieldExchange* pFX)
{
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Int(pFX, _T("[CustomerID]"), m_iCustomerID);
//(System.Int32)
RFX_Text(pFX, _T("[CompanyName]"), m_csCompanyName);
//(System.String)
RFX_Text(pFX, _T("[ContactFirstName]"), m_csContactFirstName);
//(System.String)
RFX_Text(pFX, _T("[ContactLastName]"), m_csContactLastName);
//(System.String)
RFX_Text(pFX, _T("[Address1]"), m_csAddress1);
//(System.String)
RFX_Text(pFX, _T("[Address2]"), m_csAddress2);
//(System.String)
RFX_Text(pFX, _T("[Address3]"), m_csAddress3);
//(System.String)
RFX_Text(pFX, _T("[PostalCode]"), m_csPostalCode);
//(System.String)
RFX_Text(pFX, _T("[Title]"), m_csTitle);
//(System.String)
RFX_Text(pFX, _T("[PhoneNumber]"), m_csPhoneNumber);
//(System.String)
RFX_Text(pFX, _T("[FaxNumber]"), m_csFaxNumber);
//(System.String)
RFX_Text(pFX, _T("[EmailAddress]"), m_csEmailAddress);
//(System.String)
RFX_Text(pFX, _T("[Notes]"), m_csNotes);
//(System.String)
RFX_Bool(pFX, _T("[AcceptsSpam]"), m_bAcceptsSpam);
//(System.Boolean)
RFX_Text(pFX, _T("[WEBAddress]"), m_csWEBAddress);
//(System.String)
RFX_Byte(pFX, _T("[SexId]"), m_iSexId);
//(System.Byte)
RFX_Int(pFX, _T("[CountryId]"), m_iCountryId);
//(System.Int32)
RFX_Text(pFX, _T("[ContactMiddleName]"), m_csContactMiddleName);
//(System.String)
RFX_Text(pFX, _T("[Address4]"), m_csAddress4);
//(System.String)
RFX_Int(pFX, _T("[CompanyID]"), m_iCompanyID);
//(System.Int32)
}
CString CCustomersRecSet::GetDefaultConnect()
{
// You'll have to change this for your own situation, this is a guide...
CString csDriver = L"MICROSOFT ACCESS DRIVER (*.mdb)";
CString csConnect;
csConnect.Format(L"ODBC;DRIVER={%s};DSN='';DBQ=%s", csDriver.GetString(), theApp.GetProgDbFullFileName().GetString());
return csConnect;
}
The program automatically declares fields of the correct type based on the type of the Access column, and calls the appropriate RFX_ macro, also based on the type of the Access column.
One of the main parts of the program is how the Access database is interrogated, I do that in a class called CTableDetails.
// CTableDetails: A class which reads the details of a particular table, the schema.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Diagnostics;
using System.Windows.Forms;
namespace RecSecMaker
{
// This class contains all I want to know about the format of a column (field) in a database table...
public struct AccessFieldData_t
{
public AccessFieldData_t (string sColName, Type TheType, int iColSize)
{
m_sColName = sColName ; // "SAPCODE"
m_ColType = TheType; // is this a string or a double or...? System.Int16 for example
m_iColSize = iColSize ; // How big is the field in bytes, length of the string if it is a string
}
public string m_sColName;
public Type m_ColType;
public int m_iColSize;
};
public class CTableDetails
{
public readonly string m_sTableName; // "Birthdays"
private List<AccessFieldData_t> m_ListOfFields = null;
// How many columns are in the table?
public int GetNumFields ()
{
if (m_ListOfFields == null)
{
return 0;
}
return m_ListOfFields.Count;
}
// Get details about a particular column or field
public AccessFieldData_t GetFieldDataByIndex (int i)
{
return m_ListOfFields[i];
}
// Construct the object from the database filename and the table inside that filename
public CTableDetails (string sMdbFullFileName, string sTableName)
{
m_sTableName = sTableName;
try
{
// Use using so we don't have to dispose of it...
using (OleDbConnection ADbConnection = DbHelpers.OpenDbConnection(sMdbFullFileName))
{
// These three lines are just to get hold of the table...
DbCommand DbCmd = ADbConnection.CreateCommand();
DbCmd.CommandText = "select * from " + sTableName + " where 1 = 0";
DbCmd.CommandType = CommandType.Text;
// The Reader will give us the table...
DbDataReader Reader = DbCmd.ExecuteReader();
// Now we can get how the columns are specified...
DataTable Schema = Reader.GetSchemaTable();
// Get ready to store information on each column...
m_ListOfFields = new List<AccessFieldData_t>();
foreach (DataRow row in Schema.Rows)
{
// Get hold of the three things I am interested in...
AccessFieldData_t TempData = new AccessFieldData_t(row.Field<string>("ColumnName"), row.Field<Type>("DataType"), row.Field<int>("ColumnSize"));
// Save the column details
m_ListOfFields.Add(TempData);
}
foreach (AccessFieldData_t Dat in m_ListOfFields)
{
Debug.WriteLine("{0}, {1}, {2}", Dat.m_sColName, Dat.m_ColType, Dat.m_iColSize);
}
}
}
catch (Exception e)
{
MessageBox.Show("Exception in database: " + e.Message);
}
}
}
}
I did not know how to do this before writing the program, so I learned something and built something useful.
I thought about writing about an article RecSecMaker for stackoverflow, but hell, the
loops you have to jump through just to impart information! And I thought
about GITHub, but life is too short.
No comments:
Post a Comment