SQL Class Shell Generator

• Download source code - 2.01 MB (Note: now uses VS 2008)

TOC

• Introduction
• Background
• Using the code
• SQL2ClassDemo
• SQL Server properties
• SQL Server treeview
• Output settings
• Source code settings
• Progress
• Using CodeDom and its limits (17 Jul)
• About
• Sample of HumanResources Employee class from the AdventureWorks database (31 Jul)
• References
• History (9 Jan. 2009)

Introduction

Having worked with the SQLDMO, I found some limitations, so I decided to make my own SQLDMO called SQLReader. It is a read-only DLL that takes SQL meta data and puts it into classes that one can work with. The SQLReader can not assign anything in the SQL Server.

This demo consists of two projects:

• SQL2ClassDemo
• SQLReader

SQL2ClassDemo is the actual code generator, and the SQLReader is the code generator information supplier.

Background

I work a lot with database rich applications, and I needed an app that could make class shells from a database table.

Using the code

To load databases from a SQL Server database is a straightforward process. Here, I will show how to use the the SQLReader, and afterwards, I will show how to use the information to make classes from tables.

Here is how to load the server databases:

SQLServer SqlSrv = new SQLServer();
//Connecting to the local server
SqlSrv.ConnectionSetting.DataSource = Environment.MachineName;
SqlSrv.ConnectionSetting.IntegratedSecurity = false;
SqlSrv.ConnectionSetting.UserID = "sa";
SqlSrv.ConnectionSetting.Password = "P@ssw0rd";
SqlSrv.LoadDatabases();

Here is how we load just one database:

SqlSrv.LoadDatabases("AdventureWorks");

This is how we loop through the objects in the SQL Server database:

foreach (Database db in SqlSrv.Databases.Items)
{
    foreach (Table t in db.Tables.Items)
    {
        foreach (Column c in t.Columns.Items)
        {
            //Do something with the column...
        }

        foreach (Index i in t.Indexes.Items)
        {
            //Do something with the Index...
        }

        foreach (foreign_key fk in t.ForeignKeys.Items)
        {
            //Do something with the foreign_key...
        }

        foreach (KeyConstraint kc in t.Keys.Items)
        {
            //Do something with the KeyConstraint...
        }

    }
}

A full documentation of the SQLReader DLL is available in the ZIP file. Please look into it. Or look here.

SQL2ClassDemo

1. Type the name or the IP of the server you want to connect to.
2. Choose if you want to connect with Integrated Security or not.
3. If you do not use the Integrated Security, please type the username and password.
4. If you want to load only one database, then type the name of the database and check the box.
5. Then, click Connect.

SQL Server properties

This section shows you how to list the SQLReader properties.

SQL Server treeview

In this section, you can select or deselect the databases / tables you want to make an output file (source code file) from. You will also see the property of the SQLReader class of the selected item in the tree.

Output settings

This section allows you to set the output directory, where the source code files will appear when done creating them.

Source code settings

In this section, you can set some basic settings that the code generator will perform when creating the source code files. Set what kind of languages you want the source code to be in, in the language group box. Here, you can also set pre names and post names for the fields, if you desire that.

In the Class Setting group, some other values can be set. For example, what kind of modifiers there shall be on the fields and properties.

• Add Comments, will add information to the field about the SQL object, e.g., what kind of data types is in the database and so on.

• Map MS_Description, will add the Description attribute to the property with the same text as the description in the SQL Server Management Studio.
• Try to correct propertyname, will try to correct the name of the column from the SQL table. Example: orderId = OrderId or Order id = Orderid.
• Try to correct property displayname, will try to correct the column name from the SQL table. Example: UserId = User Id or OrderMadeByUser = Order Made By User.
• Create Reference Object Property, will take any foreign keys related to the table, and create properties that assign the corresponding class types of the related table in the foreign key.
• User Databasename as Namespace, as it says, uses the database name as the namespace name.
• Add Schema to the namespace, can be helpful if you use multiple schemas within one database, and have the same table names under each schema.
• Class namespace imports, here you can select what kind of namespaces you want to import into the class.
• Class BaseTypes, add or remove some base type objects to the class.

Progress

In this section, just click Create, and you are on your way.

You can just double click on the file to open it. Or navigate to the output folder, which should look like this if you have selected the AdventureWorks database:

Using CodeDom

Using CodeDom to create source code has its limitations. Here's a short list of some of the limits one may encounter. CodeDom can not make:

• while loops (when trying to make this, CodeDom actually creates a for loop which looks very nasty).
• using statements
• foreach statements
• Static classes; instead it makes a sealed abstract class
• Variable plus-plus increment (i++); instead it makes (i = (i + 1))
• Add comments to the same line as the code
• Child namespaces

But there are some ways to get around this. I am working on a CodeDom cleaner project which will correct this and make some more nice looking code. CodeDom version:

//If statements
if(CountVarUp)
{
    i = (i + 1);
}

//For loop
for(int i = 0; i < 100; (i = (i + 1)))
{
    //do something...
}

A programmer's version:

//If statements
if(CountVarUp)
    i++;

//For loop
for(int i = 0; i < 100; i++)
    //do something...

About

In this section, I added some basic information about this little application:

Sample of HumanResources Employee class from the AdventureWorks database

Class layout:

• AdventureWorks.HumanResources

• public void Select(string ConnectionString)
• public int Insert(string ConnectionString)
• public int Update(string ConnectionString)
• public int Delete(string ConnectionString)
• public string[] GetSqlCommandStrings()
• private void AddFromRecordSet(SqlDataReader rs)
• private SqlParameter[] GetSqlParameters()

• internal static string _SQL_Select
• internal static string _SQL_Insert
• internal static string _SQL_Update
• internal static string _SQL_Delete
• public int EmployeeID
• public string NationalIDNumber
• public int ContactID
• public string LoginID
• public int ManagerID
• public string Title
• public DateTime BirthDate
• public string MaritalStatus
• public string Gender
• public DateTime HireDate
• public bool SalariedFlag
• public short VacationHours
• public short SickLeaveHours
• public bool CurrentFlag
• public Guid rowguid
• public DateTime ModifiedDate
• public Contact ContactID_Contact
• public Employee ManagerID_Employee
• public EmployeeAddressCollection EmployeeAddressCollection
• public EmployeeDepartmentHistoryCollection EmployeeDepartmentHistoryCollection
• public EmployeePayHistoryCollection EmployeePayHistoryCollection
• public JobCandidateCollection JobCandidateCollection
• public PurchaseOrderHeaderCollection PurchaseOrderHeaderCollection
• public SalesPersonCollection SalesPersonCollection

[Description("Employee information such as salary, department, and title.")]
public class Employee : Object
{
 
#region Static SQL String Memebers
    /// This field represents the full SELECT string for the table Employee,
    /// with the WHERE clause.
    internal static string _SQL_Select = "@"SELECT [EmployeeID], [NationalIDNumber], " + 
             "[ContactID], [LoginID], [ManagerID], " + 
             "[Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], " + 
             "[SalariedFlag], [VacationHours], [SickLeaveHours], " + 
             "[CurrentFlag], [rowguid], [ModifiedDate] FROM " + 
             "[HumanResources].[Employee] WHERE [EmployeeID]=@EmployeeID ";
 
    /// This field represents the full INSERT INTO string for the table
    /// Employee.
    internal static string _SQL_Insert = "@"INSERT INTO " + 
       "[HumanResources].[Employee] ([EmployeeID], [NationalIDNumber]," + 
       " [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], " + 
       "[MaritalStatus], [Gender], [HireDate], [SalariedFlag], " + 
       "[VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], " + 
       "[ModifiedDate]) VALUES([EmployeeID], [NationalIDNumber], " + 
       "[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], " + 
       "[MaritalStatus], [Gender], [HireDate], [SalariedFlag], " + 
       "[VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) ";

    /// This field represents the full UPDATE string for the table Employee,
    /// with the WHERE clause.
    internal static string _SQL_Update = 
       "@"UPDATE [HumanResources].[Employee] SET [EmployeeID] = @EmployeeID, " + 
       "[NationalIDNumber] = @NationalIDNumber, [ContactID] = " + 
       "@ContactID, [LoginID] = @LoginID, [ManagerID] = @ManagerID, " + 
       "[Title] = @Title, [BirthDate] = @BirthDate, " + 
       "[MaritalStatus] = @MaritalStatus, [Gender] = @Gender, [HireDate] = @HireDate," + 
       " [SalariedFlag] = @SalariedFlag, [VacationHours] = @VacationHours, " + 
       "[SickLeaveHours] = @SickLeaveHours, [CurrentFlag] = " + 
       "@CurrentFlag, [rowguid] = @rowguid, [ModifiedDate] = " + 
       "@ModifiedDate WHERE [EmployeeID]=@EmployeeID ";
 
    /// This field represents the DELETE string for the table Employee,
    /// with the WHERE clause.
    internal static string _SQL_Delete =
       "DELETE FROM [HumanResources].[Employee] WHERE [EmployeeID]=@EmployeeID ";
#endregion
 
#region Tables Memebers
    /// SQL Type:int - Primary key for Employee records.
    private int _EmployeeID;
    
    [Description("Primary key for Employee records.")]
    [DisplayName("Employee ID")]
    [Category("Primary Key")]
    public int EmployeeID
    {
        get
        {
            try
            {
                return _EmployeeID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting EmployeeID", err);
            }
        }
        set
        {
            try
            {
                _EmployeeID = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting EmployeeID", err);
            }
        }
    }
 
    /// SQL Type:nvarchar - Unique national identification number such
    /// as a social security number.
    private string _NationalIDNumber;
 
    [Description("Unique national identification number" + 
              " such as a social security number.")]
    [DisplayName("National IDNumber")]
    [Category("Column")]
    public string NationalIDNumber
    {
        get
        {
            try
            {
                return _NationalIDNumber;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting NationalIDNumber", err);
            }
        }
        set
        {
            try
            {
                if ((value.Length <= 30))
                {
                    _NationalIDNumber = value;
                }
                else
                {
                    throw new OverflowException("Error setting" + 
                          " NationalIDNumber, " + 
                          "Length of value is to long. Maximum Length: 30");
                }
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting NationalIDNumber", err);
            }
        }
    }

    /// SQL Type:int - Identifies the employee in the Contact table.
    /// Foreign key to Contact.ContactID.
    private int _ContactID;
    
    [Description("Identifies the employee in the Contact table." + 
          " Foreign key to Contact.ContactID.")]
    [DisplayName("Contact ID")]
    [Category("Foreign Key")]
    public int ContactID
    {
        get
        {
            try
            {
                return _ContactID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting ContactID", err);
            }
        }
        set
        {
            try
            {
                _ContactID = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting ContactID", err);
            }
        }
    }
 
    /// SQL Type:nvarchar - Network login.
    private string _LoginID;
    
    [Description("Network login.")]
    [DisplayName("Login ID")]
    [Category("Column")]
    public string LoginID
    {
        get
        {
            try
            {
                return _LoginID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting LoginID", err);
            }
        }
        set
        {
            try
            {
                if ((value.Length <= 512))
                {
                    _LoginID = value;
                }
                else
                {
                    throw new OverflowException("Error " + 
                          "setting LoginID, " + 
                          "Length of value is to long. " + 
                          "Maximum Length: 512");
                }
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting LoginID", err);
            }
        }
    }
 
    /// SQL Type:int - Manager to whom the employee is assigned.
    /// Foreign Key to Employee.M
    private int _ManagerID;

    [Description("Manager to whom the employee is " + 
        "assigned. Foreign Key to Employee.M")]
    [DisplayName("Manager ID")]
    [Category("Foreign Key")]
    public int ManagerID
    {
        get
        {
            try
            {
                return _ManagerID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting ManagerID", err);
            }
        }
        set
        {
            try
            {
                _ManagerID = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting ManagerID", err);
            }
        }
    }
 
    /// SQL Type:nvarchar - Work title such as Buyer or Sales
    /// Representative.
    private string _Title;

    [Description("Work title such as Buyer or Sales Representative.")]
    [DisplayName("Title")]
    [Category("Column")]
    public string Title
    {
        get
        {
            try
            {
                return _Title;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting Title", err);
            }
        }
        set
        {
            try
            {
                if ((value.Length <= 100))
                {
                    _Title = value;
                }
                else
                {
                    throw new OverflowException("Error " + 
                          "setting Title, Length of value " + 
                          "is to long. Maximum Length: 100");
                }
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting Title", err);
            }
        }
    }
 
    /// SQL Type:datetime - Date of birth.
    private System.DateTime _BirthDate;

    [Description("Date of birth.")]
    [DisplayName("Birth Date")]
    [Category("Column")]
    public System.DateTime BirthDate
    {
        get
        {
            try
            {
                return _BirthDate;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting BirthDate", err);
            }
        }
        set
        {
            try
            {
                _BirthDate = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting BirthDate", err);
            }
        }
    }
 
    /// SQL Type:nchar - M = Married, S = Single
    private string _MaritalStatus;

    [Description("M = Married, S = Single")]
    [DisplayName("Marital Status")]
    [Category("Column")]
    public string MaritalStatus
    {
        get
        {
            try
            {
                return _MaritalStatus;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting MaritalStatus", err);
            }
        }
        set
        {
            try
            {
                if ((value.Length <= 2))
                {
                    _MaritalStatus = value;
                }
                else
                {
                    throw new OverflowException("Error " + 
                          "setting MaritalStatus, " + 
                          "Length of value is to long. Maximum Length: 2");
                }
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting MaritalStatus", err);
            }
        }
    }
 
    /// SQL Type:nchar - M = Male, F = Female
    private string _Gender;

    [Description("M = Male, F = Female")]
    [DisplayName("Gender")]
    [Category("Column")]
    public string Gender
    {
        get
        {
            try
            {
                return _Gender;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting Gender", err);
            }
        }
        set
        {
            try
            {
                if ((value.Length <= 2))
                {
                    _Gender = value;
                }
                else
                {
                    throw new OverflowException("Error setting Gender, " + 
                          "Length of value is to long. Maximum Length: 2");
                }
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting Gender", err);
            }
        }
    }
 
    /// SQL Type:datetime - Employee hired on this date.
    private System.DateTime _HireDate;

    [Description("Employee hired on this date.")]
    [DisplayName("Hire Date")]
    [Category("Column")]
    public System.DateTime HireDate
    {
        get
        {
            try
            {
                return _HireDate;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting HireDate", err);
            }
        }
        set
        {
            try
            {
                _HireDate = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting HireDate", err);
            }
        }
    }
 
    /// SQL Type:Flag - Job classification. 0 = Hourly, not exempt from
    /// collective bargaining. 1 = Salaried, exempt from collective bargaining.
    private bool _SalariedFlag;
 
    [Description("Job classification. 0 = Hourly, " + 
              "not exempt from collective bargaining." +
              "1 = Salaried, exempt from collective bargaining.")]
    [DisplayName("Salaried Flag")]
    [Category("Column")]
    public bool SalariedFlag
    {
        get
        {
            try
            {
                return _SalariedFlag;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting SalariedFlag", err);
            }
        }
        set
        {
            try
            {
                _SalariedFlag = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting SalariedFlag", err);
            }
        }
    }
 
    /// SQL Type:smallint - Number of available vacation hours.
    private short _VacationHours;

    [Description("Number of available vacation hours.")]
    [DisplayName("Vacation Hours")]
    [Category("Column")]
    public short VacationHours
    {
        get
        {
            try
            {
                return _VacationHours;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting VacationHours", err);
            }
        }
        set
        {
            try
            {
                _VacationHours = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting VacationHours", err);
            }
        }
    }
 
    /// SQL Type:smallint - Number of available sick leave hours.
    private short _SickLeaveHours;
    
    [Description("Number of available sick leave hours.")]
    [DisplayName("Sick Leave Hours")]
    [Category("Column")]
    public short SickLeaveHours
    {
        get
        {
            try
            {
                return _SickLeaveHours;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting SickLeaveHours", err);
            }
        }
        set
        {
            try
            {
                _SickLeaveHours = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting SickLeaveHours", err);
            }
        }
    }

    /// SQL Type:Flag - 0 = Inactive, 1 = Active
    private bool _CurrentFlag;

    [Description("0 = Inactive, 1 = Active")]
    [DisplayName("Current Flag")]
    [Category("Column")]
    public bool CurrentFlag
    {
        get
        {
            try
            {
                return _CurrentFlag;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting CurrentFlag", err);
            }
        }
        set
        {
            try
            {
                _CurrentFlag = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting CurrentFlag", err);
            }
        }
    }
 
    /// SQL Type:uniqueidentifier - ROWGUIDCOL number uniquely identifying
    /// the record. Used to support a merge replication sample.
    private System.Guid _rowguid;
 
    [Description(
     "ROWGUIDCOL number uniquely identifying " + 
     "the record. Used to support a merge" +
     "replication sample.")]
    [DisplayName("rowguid")]
    [Category("Column")]
    public System.Guid rowguid
    {
        get
        {
            try
            {
                return _rowguid;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting rowguid", err);
            }
        }
        set
        {
            try
            {
                _rowguid = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting rowguid", err);
            }
        }
    }
 
    /// SQL Type:datetime - Date and time the record was last updated.
    private System.DateTime _ModifiedDate;
    
    [Description("Date and time the record was last updated.")]
    [DisplayName("Modified Date")]
    [Category("Column")]
    public System.DateTime ModifiedDate
    {
        get
        {
            try
            {
                return _ModifiedDate;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting ModifiedDate", err);
            }
        }
        set
        {
            try
            {
                _ModifiedDate = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting ModifiedDate", err);
            }
        }
    }
#endregion

#region Related Objects
    /// Represents the foreign key object
    private Contact _ContactID_Contact;
    
    [Description("Represents the foreign key object of the type Contact")]
    public Contact ContactID_Contact
    {
        get
        {
            try
            {
                return _ContactID_Contact;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting ContactID_Contact", err);
            }
        }
        set
        {
            try
            {
                _ContactID_Contact = value;
                _ContactID = _ContactID_Contact.ContactID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting ContactID_Contact", err);
            }
        }
    }
 
    /// Represents the foreign key object
    private Employee _ManagerID_Employee;
    
    [Description("Represents the foreign key object of the type Employee")]
    public Employee ManagerID_Employee
    {
        get
        {
            try
            {
                return _ManagerID_Employee;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting ManagerID_Employee", err);
            }
        }
        set
        {
            try
            {
                _ManagerID_Employee = value;
                _ManagerID = _ManagerID_Employee.EmployeeID;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting ManagerID_Employee", err);
            }
        }
    }
#endregion
    
#region Related Object Collections
/// Represents the foreign key object
private EmployeeAddressCollection _EmployeeAddressCollection;
 
    [Description("Represents the foreign key relation." + 
              " This is an Collection of Employee.")]
    public EmployeeAddressCollection EmployeeAddressCollection
    {
        get
        {
            try
            {
                return _EmployeeAddressCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting EmployeeAddressCollection", err);
            }
        }
        set
        {
            try
            {
                _EmployeeAddressCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting EmployeeAddressCollection", err);
            }
        }
    }
    
    /// Represents the foreign key object
    private EmployeeDepartmentHistoryCollection _EmployeeDepartmentHistoryCollection;

    [Description("Represents the foreign key relation." + 
              " This is an Collection of Employee.")]
    public EmployeeDepartmentHistoryCollection EmployeeDepartmentHistoryCollection
    {
        get
        {
            try
            {
                return _EmployeeDepartmentHistoryCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting Employee" + 
                      "DepartmentHistoryCollection", err);
            }
        }
        set
        {
            try
            {
                _EmployeeDepartmentHistoryCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting Employee" + 
                            "DepartmentHistoryCollection", err);
            }
        }
    }
 
    /// Represents the foreign key object
    private EmployeePayHistoryCollection _EmployeePayHistoryCollection;

    [Description("Represents the foreign key relation." + 
              " This is an Collection of Employee.")]
    public EmployeePayHistoryCollection EmployeePayHistoryCollection
    {
        get
        {
            try
            {
                return _EmployeePayHistoryCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting EmployeePayHistoryCollection", err);
            }
        }
        set
        {
            try
            {
                _EmployeePayHistoryCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting EmployeePayHistoryCollection", err);
            }
        }
    }
 
    /// Represents the foreign key object
    private JobCandidateCollection _JobCandidateCollection;
 
    [Description("Represents the foreign key relation. This is an Collection of Employee.")]
    public JobCandidateCollection JobCandidateCollection
    {
        get
        {
            try
            {
                return _JobCandidateCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting JobCandidateCollection", err);
            }
        }
        set
        {
            try
            {
                _JobCandidateCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting JobCandidateCollection", err);
            }
        }
    }
 
    /// Represents the foreign key object
    private PurchaseOrderHeaderCollection _PurchaseOrderHeaderCollection;
 
    [Description("Represents the foreign key relation. This is an Collection of Employee.")]
    public PurchaseOrderHeaderCollection PurchaseOrderHeaderCollection
    {
        get
        {
            try
            {
                return _PurchaseOrderHeaderCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting PurchaseOrderHeaderCollection", err);
            }
        }
        set
        {
            try
            {
                _PurchaseOrderHeaderCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting PurchaseOrderHeaderCollection", err);
            }
        }
    }
 
    /// Represents the foreign key object
    private SalesPersonCollection _SalesPersonCollection;
 
    [Description("Represents the foreign key relation. This is an Collection of Employee.")]
    public SalesPersonCollection SalesPersonCollection
    {
        get
        {
            try
            {
                return _SalesPersonCollection;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error getting SalesPersonCollection", err);
            }
        }
        set
        {
            try
            {
                _SalesPersonCollection = value;
            }
            catch (System.Exception err)
            {
                throw new Exception("Error setting SalesPersonCollection", err);
            }
        }
    }
#endregion
 
#region Public Methods
    public void Select(string ConnectionString)
    {
        try
        {
            SqlConnection Conn = new SqlConnection(ConnectionString);
            SqlCommand Com = Conn.CreateCommand();
            Com.CommandText = Employee._SQL_Select;
            Com.Parameters.AddRange(GetSqlParameters());
            Conn.Open();
            SqlDataReader rs = Com.ExecuteReader();
            while(rs.Read())
            {
                AddFromRecordSet(rs);
            }
            rs.Close();
            Conn.Close();
            rs.Dispose();
            Com.Dispose();
            Conn.Dispose();
        }
        catch (System.Exception )
        {
            throw;
        }
    }
 
    public int Insert(string ConnectionString)
    {
        try
        {
            SqlConnection Conn = new SqlConnection(ConnectionString);
            SqlCommand Com = Conn.CreateCommand();
            Com.CommandText = Employee._SQL_Insert;
            Com.Parameters.AddRange(GetSqlParameters());
            Conn.Open();
            int rowseffected = Com.ExecuteNonQuery();
            Conn.Close();
            Com.Dispose();
            Conn.Dispose();
            return rowseffected;
        }
        catch (System.Exception )
        {
            throw;
        }
    }
 
    public int Update(string ConnectionString)
    {
        try
        {
            SqlConnection Conn = new SqlConnection(ConnectionString);
            SqlCommand Com = Conn.CreateCommand();
            Com.CommandText = Employee._SQL_Update;
            Com.Parameters.AddRange(GetSqlParameters());
            Conn.Open();
            int rowseffected = Com.ExecuteNonQuery();
            Conn.Close();
            Com.Dispose();
            Conn.Dispose();
            return rowseffected;
        }
        catch (System.Exception )
        {
            throw;
        }
    }
    
    public int Delete(string ConnectionString)
    {
        try
        {
            SqlConnection Conn = new SqlConnection(ConnectionString);
            SqlCommand Com = Conn.CreateCommand();
            Com.CommandText = Employee._SQL_Delete;
            Com.Parameters.AddRange(GetSqlParameters());
            Conn.Open();
            int rowseffected = Com.ExecuteNonQuery();
            Conn.Close();
            Com.Dispose();
            Conn.Dispose();
            return rowseffected;
        }
        catch (System.Exception )
        {
            throw;
        }
    }
 
    public string[] GetSqlCommandStrings()
    {
        try
        {
           string[] CommStr = new string[4];
           CommStr[0] = "SELECT [EmployeeID], [NationalIDNumber], [ContactID],
               [LoginID], [ManagerID], [Title], [BirthDate]," + 
                " [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours],
               [SickLeaveHours], [CurrentFlag], [rowguid]," + 
               " [ModifiedDate] FROM [HumanResources].[Employee] WHERE [EmployeeID] =
               " + _EmployeeID + ";
           CommStr[1] = "INSERT INTO [HumanResources].[Employee] ([EmployeeID],
              [NationalIDNumber], [ContactID], [LoginID]," +
              " [ManagerID], [Title], [BirthDate], " + 
              "[MaritalStatus], [Gender], [HireDate],
               [SalariedFlag], [VacationHours], " +
              "[SickLeaveHours], [CurrentFlag], [rowguid],
              [ModifiedDate]) VALUES(" + _EmployeeID + ", '" + 
              _NationalIDNumber.Replace("'","''") + "', " + _ContactID + ", '" +
              _LoginID.Replace("'","''") + "', " + 
              _ManagerID + ", '" + _Title.Replace("'","''") +
              "', '" + _BirthDate.Replace("'","''") + 
              "', '" + _MaritalStatus.Replace("'","''") +
              "', '" + _Gender.Replace("'","''") + 
              "', '" + _HireDate.Replace("'","''") + "',
              " + _SalariedFlag + ", " + _VacationHours + 
              ", " + _SickLeaveHours + ", " +
              _CurrentFlag + ", " + _rowguid + ", '" + 
              _ModifiedDate.Replace("'","''") + "')";
            CommStr[2] = "UPDATE [HumanResources].[Employee] SET [NationalIDNumber] = '" +
              _NationalIDNumber.Replace("'","''") + "' ,[ContactID] = " + _ContactID + " ,
              [LoginID] = '" + _LoginID.Replace("'","''") + 
              "' ,[ManagerID] = " + _ManagerID + 
              " ,[Title] = '" + _Title.Replace("'","''") + "' ,[BirthDate] = '" +
              _BirthDate.Replace("'","''") + "' ,[MaritalStatus] = '" + 
              _MaritalStatus.Replace("'","''") + 
              "' ,[Gender] = '" + _Gender.Replace("'","''") +
              "' ,[HireDate] = '" + _HireDate.Replace("'","''") + 
              "' ,[SalariedFlag] = " + _SalariedFlag + " ,[VacationHours] = " + 
              _VacationHours + " ,[SickLeaveHours] = " +
              _SickLeaveHours + " ,[CurrentFlag] = " + _CurrentFlag + " ,[rowguid] = " +
              _rowguid + " ,[ModifiedDate] = '" + _ModifiedDate.Replace("'","''") +
              "' WHERE [EmployeeID] = " + _EmployeeID + ";
            CommStr[3] = "DELETE FROM [HumanResources].[Employee] WHERE [EmployeeID] =
              " + _EmployeeID + ";
            return CommStr;
        }
        catch (System.Exception )
        {
            throw;
        }
    }
#endregion
 
#region Private Methods
    private void AddFromRecordSet(SqlDataReader rs)
    {
        try
        {
            // if value from the recordset, to the EmployeeID
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("EmployeeID")) == false))
            {
                EmployeeID = rs.GetInt32(rs.GetOrdinal("EmployeeID"));
            }
            // if value from the recordset, to the NationalIDNumber
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal(
                             "NationalIDNumber")) == false))
            {
                NationalIDNumber = rs.GetString(
                    rs.GetOrdinal("NationalIDNumber"));
            }
            // if value from the recordset, to the ContactID
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("ContactID")) == false))
            {
                ContactID = rs.GetInt32(rs.GetOrdinal("ContactID"));
            }
            // if value from the recordset, to the LoginID
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("LoginID")) == false))
            {
                LoginID = rs.GetString(rs.GetOrdinal("LoginID"));
            }
            // if value from the recordset, to the ManagerID
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("ManagerID")) == false))
            {
                ManagerID = rs.GetInt32(rs.GetOrdinal("ManagerID"));
            }
            // if value from the recordset, to the Title
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("Title")) == false))
            {
                Title = rs.GetString(rs.GetOrdinal("Title"));
            }
            // if value from the recordset, to the BirthDate
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("BirthDate")) == false))
            {
                BirthDate = rs.GetDateTime(rs.GetOrdinal("BirthDate"));
            }
            // if value from the recordset, to the MaritalStatus
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("MaritalStatus")) == false))
            {
                MaritalStatus = 
                  rs.GetString(rs.GetOrdinal("MaritalStatus"));
            }
            // if value from the recordset, to the Gender field
            // is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("Gender")) == false))
            {
                Gender = rs.GetString(rs.GetOrdinal("Gender"));
            }
            // if value from the recordset, to the HireDate
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("HireDate")) == false))
            {
                HireDate = rs.GetDateTime(rs.GetOrdinal("HireDate"));
            }
            // if value from the recordset, to the SalariedFlag
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("SalariedFlag")) == false))
            {
                SalariedFlag = 
                  rs.GetBoolean(rs.GetOrdinal("SalariedFlag"));
            }
            // if value from the recordset, to the VacationHours
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("VacationHours")) == false))
            {
                VacationHours = rs.GetInt16(rs.GetOrdinal("VacationHours"));
            }
            // if value from the recordset, to the SickLeaveHours
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("SickLeaveHours")) == false))
            {
                SickLeaveHours = rs.GetInt16(rs.GetOrdinal("SickLeaveHours"));
            }
            // if value from the recordset, to the CurrentFlag
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("CurrentFlag")) == false))
            {
                CurrentFlag = rs.GetBoolean(rs.GetOrdinal("CurrentFlag"));
            }
            // if value from the recordset, to the rowguid
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("rowguid")) == false))
            {
                rowguid = rs.GetGuid(rs.GetOrdinal("rowguid"));
            }
            // if value from the recordset, to the ModifiedDate
            // field is NOT null then set the value.
            if ((rs.IsDBNull(rs.GetOrdinal("ModifiedDate")) == false))
            {
                ModifiedDate = rs.GetDateTime(rs.GetOrdinal("ModifiedDate"));
            }
        }
        catch (SqlException sqlExc)
        {
            throw sqlExc;
        }
        catch (Exception Exc)
        {
            throw Exc;
        }
    }

    private SqlParameter[] GetSqlParameters()
    {
        List<SqlParameter> SqlParmColl = new List<SqlParameter>();
        try
        {
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@EmployeeID", EmployeeID, SqlDbType.Int));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@NationalIDNumber", NationalIDNumber, SqlDbType.NVarChar));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@ContactID", ContactID, SqlDbType.Int));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@LoginID", LoginID, SqlDbType.NVarChar));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@ManagerID", ManagerID, SqlDbType.Int));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@Title", Title, SqlDbType.NVarChar));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@BirthDate", BirthDate, SqlDbType.DateTime));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@MaritalStatus", MaritalStatus, SqlDbType.NChar));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@Gender", Gender, SqlDbType.NChar));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@HireDate", HireDate, SqlDbType.DateTime));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@SalariedFlag", SalariedFlag, ));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@VacationHours", VacationHours, SqlDbType.SmallInt));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@SickLeaveHours", SickLeaveHours, SqlDbType.SmallInt));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@CurrentFlag", CurrentFlag, ));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@rowguid", rowguid, SqlDbType.UniqueIdentifier));
            SqlParmColl.Add(AdventureWorks.AddSqlParm(
               "@ModifiedDate", ModifiedDate, SqlDbType.DateTime));
            return SqlParmColl.ToArray();
        }
        catch (Exception Exc)
        {
            throw Exc;
        }
    }
#endregion
}

References

• SQLReader website
• MSDN
  • Database
  • Objects
  • Schema
  • Table
  • Column
  • Foreign_key
  • Foreign_key_column
  • Indexes
  • Index_columns
  • Extended_properties
  • Type
  • Default_constraint
  • Trigger
  • Comment
  • Identity_column

History

16 July 2008

• Version 1.0.0.0 posted.

17 July 2008

• Version 1.0.0.1 uploaded.
• Fixed the Server name box, can now connect to remote SQL Server (thanks to: jklucker).
• Fixed the word lenght to Length (thanks to: CincDev).
• Fixed some basic exception handling, but not finished (thanks to: jklucker).
• Added Catalog select box (thanks to: CincDev).
• Added Save settings and Load settings (thanks to: CincDev).

31 July 2008

• Added Select, Insert, Update, Delete methods to the classes.
• Added the GetSQLCommandStrings method, which will return an array of strings.

19 August 2008

• Added a demo on how to make a ClassCollection (inherited from CollectionBase).

16 September 2008

• Added the ability to generate code from Views.
• Fixed the System.Collections.Generic missing in the Import list.
• The source files are now in Visual Studio 2008 and not Visual Studio 2005 (sorry, I had to upgrade; I think we can correct this (go from VS 2008 to VS 2005) by deleting the solution files and making them by ourselves).
• Note: The CP article wizard is playing tricks on me; for some reason, all my quote signs in all code examples are now marked with HTML quote tags ["], don't know why... sorry.

9 January 2009

• Updated the following classes so they can handle case sensitive collations: IndexColumn, IndexColumns, IdentityColumn, IdentityColumns, View, Views.
• Note: The CP article wizard is playing tricks on me; for some reason, all my quote signs in all code examples are now marked with HTML quote tags ["], don't know why... sorry.