C# Db Class Generator


Author: Dave
Date: 10.10.12 - 8:28am



Download: DbClassGen.zip

Needed this for a small project so thought i would share.

Sometimes you want to generate C# classes that mirror the data types used in a database table. You also want an easy way to load all of the class members from a data base record. This is a nice way to access members because you get intellisense when accessing them, and dont have to manually cast everything from the data reader object by hand everytime you want to use it.

For this task i created a small class generator that allows you to select the target database and tables you want to access.




After the classes are generated, you query the table through an autogenerated access function.





this does not handle joins yet. A more generic way to load data that contains any number of fields would be something like this:
public class CMyClass
{
        public Dictionary<string,string>Field = new Dictionary<string,string>();

        public void LoadRecord(MySqlDataReader r){

            DataTable schemaTable = r.GetSchemaTable();
 
            foreach (DataRow row in schemaTable.Rows) {
                string column = row["ColumnName"].ToString();
                string value = r[column].ToString();

                if (value == "System.Byte[]") value = ByteToString((byte[])r[column]);
                
                Field.Add(column, value);    
            }            

        }
}
When I get a little more time, I will combine these two techniques so that if unexpected fields are encountered as from a join, the overflow unrecgonized fields will be added to a Dictionary so they can at least be accessed as string values. Not perfect but workable and easy.




Update:
Ok I was able to use a combination of both techniques. After some benchmarking, I found that the code below seems to be the quickest way to access unknown members. In the initilization function, you call GetSchemaTable once. If you encounter ambiguous column names, you auto rename subsequent ones columnName_1.
public object GetRaw(string field){
     if(Schema.ContainsKey(field)) return RawValues[Schema[field]];
     return new object();
}
		
public Ctblmsgs(IDataReader r, Dictionary<string, int> schema)
{
     Schema = schema;
     RawValues = new object[r.FieldCount];
     r.GetValues(RawValues);





Comments: (2)

On 02.20.18 - 8:19am Jerhill wrote:
Hey, it didnt work out of the box. The exe was crashing due to a null pointer exception. When I opened the source code, the Visual Studio project was missing the reference to Mysql.Data so I had to download and link that one myself. After that it worked. Its nice the source code is included as I got it to work with a little effort. Also useful as I want to customize it. Thanks, great job D!

On 08.13.18 - 8:45pm Robert wrote:
Excellent app, Ill have to upload a new one -- its been modified to suit my needs. Updated to support .Net 4.5+ and a more Mysql.Data

 
Leave Comment:
Name:
Email: (not shown)
Message: (Required)
Math Question: 85 + 65 = ? followed by the letter: Q 



About Me
More Blogs
Main Site
Posts: (All)
2020 ( 2 )
2019 ( 5 )
2018 ( 6 )
2017 ( 6 )
2016 ( 22 )
2015 ( 16 )
2014 ( 25 )
2013 ( 4 )
2012 (10)
     VC 2008 Bit Fields
     Speed trap
     C# Db Class Generator
     VB6 vrs .NET (again)
     FireFox Whois Extension
     git and vb6
     Code Additions
     Compiled date to string
     C# ListView Sorter
     VB6 Wish List
2011 (7)
     C# Process Injection
     CAPTCHA Bots
     C# PE Offset Calculator
     VB6 Async Download
     Show Desktop
     coding philosophy
     Code release
2010 ( 11 )
2009 ( 3 )