CodeProject: SQL Class shell generator. Free source code and programming help

Prize winner in Competition "Code Generation 2008 Competition" (Second Prize level)

Report Article

Discuss

Send to a friend

42 votes for this Article.

Popularity: 6.82 Rating: 4.20 out of 5

Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

Download SQL2Class.zip - 813.85 KB (NOTE: now VS 2008)

2nd place in the Code Generation 2008 Competition

Introduction

Having worked with the SQLDMO i found some limitations, so, i decided to make my own SQLDMO called SQLReader.
Its an readonly 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

Im working alot with Database rich applications, and i needed an app that could make Class Shells from an Database table.

Using the code

To load databases from an SQL Server, is straight forward. below i will show quick, how to use the the SQLReader, and afterwards i will show how to use the information, to make classes from tables.

Loading 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();

or loading only one Database.

SqlSrv.LoadDatabases("AdventureWorks");

Looping through the objects of the SQLServer

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...
        }

    }
}

An full documentation of the SQLReader DLL is in the ZIP file. please look into it. or look here: http://paw.jershauge.dk/sqlr/index.htm

SQL2ClassDemo

Type the name or the ip of the server you want to connect to.
Choose if you want to connect with intergrated security or not.
If you do not use the intergrated security, please type the username and password.
If you only want to load one database, then type the name of that one, and check the box.
Then click Connect

SQL Server properties

This section show you in an PropertyGrid way the SQLReader properties, if you want to see them.

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 Setting

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 setting that the Code generator will preform 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 settings can be set. like what kind of modifiers there shall be on the fields and properties.

Add Comments, will add information to the field about the SQL object. like what kind the datatype is in the sql server 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 correcsponding class types of the related Table in the foreign key.

User Databasename as Namespace, is as it says, it uses the database name as namespace name.

Add Schema to the namespace, Can be helpfull if you user multiple schames within one database, and have the same table names under each schema.

Class namespace imports, here you can select what kind of namepspaces 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 dobble click on the file to open it.
Or navigate to the output folder, witch 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 the codedom actually creates an for loop which looks very nasty.)
Using statement
ForEach statement
Static Classes instead it makes an sealed abstract class
Variable plus-plus increment like (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. im working on an CodeDom Clearner project which will correct this and make some more nice looking code.

Example to clearn up.
CodeDom version:

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

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

A programmers version:

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

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

Some basic tricks will be provided on a future CodeDom Article (called: Basics of CodeDom). keep an eye out for it.

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
 /// <remarks>This field represents the full SELECT string for the table Employee,
 /// with the WHERE clause.</remarks>
 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 ";
 
 /// <remarks>This field represents the full INSERT INTO string for the table
 /// Employee.</remarks>
 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]) ";
 
 /// <remarks>This field represents the full UPDATE string for the table Employee,
 /// with the WHERE clause.</remarks>
 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 ";
 
 /// <remarks>This field represents the DELETE string for the table Employee,
 /// with the WHERE clause.</remarks>
 internal static string _SQL_Delete =
    "DELETE FROM [HumanResources].[Employee] WHERE [EmployeeID]=@EmployeeID ";
 #endregion
 
 #region Tables Memebers
 /// <remarks>SQL Type:int - Primary key for Employee records.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:nvarchar - Unique national identification number such
 /// as a social security number.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:int - Identifies the employee in the Contact table.
 /// Foreign key to Contact.ContactID.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:nvarchar - Network login.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:int - Manager to whom the employee is assigned.
 /// Foreign Key to Employee.M</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:nvarchar - Work title such as Buyer or Sales
 /// Representative.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:datetime - Date of birth.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:nchar - M = Married, S = Single</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:nchar - M = Male, F = Female</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:datetime - Employee hired on this date.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:Flag - Job classification. 0 = Hourly, not exempt from
 /// collective bargaining. 1 = Salaried, exempt from collective bargaining.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:smallint - Number of available vacation hours.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:smallint - Number of available sick leave hours.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:Flag - 0 = Inactive, 1 = Active</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:uniqueidentifier - ROWGUIDCOL number uniquely identifying
 /// the record. Used to support a merge replication sample.</remarks>
 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);
   }
  }
 }
 
 /// <remarks>SQL Type:datetime - Date and time the record was last updated.</remarks>
 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
 /// <remarks>Represents the foreign key object</remarks>
 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);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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
 /// <remarks>Represents the foreign key object</remarks>
 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);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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 EmployeeDepartmentHistoryCollection", err);
   }
  }
  set
  {
   try
   {
    _EmployeeDepartmentHistoryCollection = value;
   }
   catch (System.Exception err)
   {
    throw new Exception("Error setting EmployeeDepartmentHistoryCollection", err);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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);
   }
  }
 }
 
 /// <remarks>Represents the foreign key object</remarks>
 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:
http://paw.jershauge.dk/sqlr/index.htm

History

16 july 2008

Version 1.0.0.0 posted

17 july 2008

Version 1.0.0.1 uploaded
Fixed Servername 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 finnished. (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 GetSQLCommandStrings method, which will return an array of strings.

19 August 2008

Added an demo on how to make an ClassCollection (inherited from CollectionBase)

16 September 2008

Added the ability to generate code from Views
Fixed the System.Collections.Generic missing in Import list.
The source files are now in Visual Studio 2008 and not Visual Studio 2005 (Sorry, i had to upgrade. i think one can correct this (go from VS2k8 to VS2k5) by deleting the solution files and make them by yourself.)
NOTE: the CP Article wizard is playing trix on me, for some reason all my Quote signs in all code examples are now maked with HTML quote tags ["], dont know why... sorry

Click here to download the Source Code: Download SQL2Class.zip - 813.85 KB

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Paw Jershauge

An Code Generator, for making SQL table into .Net Classses (2nd place in the Code Generation 2008 Competition)
A Class for getting the Rss feed list of a website
Seagate Date Code Calculator
DNSBL lookup Class
Base N converter (N = 10-62)
Lightweight Directory Access Protocol Uniform resource identifier (LDAPUri)
LoginHours from DirectoryEntry into boolean array

Occupation:	Software Developer
Location:	Denmark

SQL Class shell generator

TOC