SQL 类 Shell 生成器

Paw Jershauge

4.87/5 (60投票s)

2008 年 7 月 16 日

CPOL

6分钟阅读

240486

3318

从 SQL Server 数据库表中生成类骨架，（仅限 SQL 2005 & 2008）。支持的输出语言：C# 和 VB.NET。

下载源代码 - 2.01 MB (注意：现在使用 VS 2008)

引言

在处理 SQLDMO 时，我发现了一些限制，所以我决定自己制作一个 SQLDMO，称为 SQLReader。它是一个只读 DLL，它获取 SQL 元数据并将其放入类中，供您使用。SQLReader 无法在 SQL Server 中进行任何赋值。

此演示包含两个项目

SQL2ClassDemo
SQLReader

SQL2ClassDemo 是实际的代码生成器，SQLReader 是代码生成器的信息提供者。

背景

我经常处理丰富的数据库应用程序，我需要一个可以从数据库表中生成类骨架的应用程序。

使用代码

从 SQL Server 数据库加载数据库是一个简单的过程。在这里，我将展示如何使用 SQLReader，然后，我将展示如何使用这些信息从表中创建类。

以下是如何加载服务器数据库

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

以下是我们如何加载单个数据库

SqlSrv.LoadDatabases("AdventureWorks");

这是我们如何遍历 SQL Server 数据库中的对象

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

    }
}

SQLReader DLL 的完整文档可在 ZIP 文件中找到。请查看。或者在此处查看。

SQL2ClassDemo

键入要连接的服务器的名称或 IP。
选择是否要使用集成身份验证进行连接。
如果您不使用集成身份验证，请键入用户名和密码。
如果您只想加载一个数据库，请键入数据库名称并选中该框。
然后，单击“连接”。

SQL Server 属性

本节将向您展示如何列出 SQLReader 属性。

SQL Server 树视图

在本节中，您可以选择或取消选择要生成输出文件（源代码文件）的数据库/表。您还将看到所选树形项的 SQLReader 类的属性。

输出设置

本节允许您设置输出目录，源代码文件完成后将在此处生成。

源代码设置

在本节中，您可以设置代码生成器在创建源代码文件时将执行的一些基本设置。在语言组框中，设置您想要的源代码的语言类型。在这里，您还可以选择性地为字段设置前缀和后缀。

在“类设置”组中，还可以设置其他一些值。例如，字段和属性的修饰符类型。

添加注释，将为字段添加有关 SQL 对象的信息，例如数据库中的数据类型是什么，等等。
映射 MS_Description，将使用与 SQL Server Management Studio 中描述相同的文本为属性添加 Description 属性。
尝试更正属性名，将尝试更正 SQL 表中的列名。例如：orderId = OrderId 或 Order id = Orderid。
尝试更正属性显示名，将尝试更正 SQL 表中的列名。例如：UserId = User Id 或 OrderMadeByUser = Order Made By User。
创建引用对象属性，将获取与该表相关的任何外键，并创建分配相关表中相应类类型的属性。
使用数据库名作为命名空间，顾名思义，使用数据库名作为命名空间名。
将架构添加到命名空间，如果您在一个数据库中使用多个架构，并且每个架构下有相同的表名，这会很有帮助。
类命名空间导入，您可以在此处选择要导入到类中的命名空间类型。
类基类型，向类添加或删除一些基类型对象。

进度

在本节中，只需单击“创建”，即可开始。

您可以直接双击文件打开它。或者导航到输出文件夹，如果您选择了 AdventureWorks 数据库，它应该看起来像这样

使用 CodeDom

使用 CodeDom 创建源代码有其局限性。以下是一些可能遇到的限制的简短列表。CodeDom 无法创建

while 循环（尝试创建此循环时，CodeDom 实际上会创建一个 for 循环，看起来非常糟糕）。
using 语句
foreach 语句
静态类；它会创建一个密封的抽象类
变量递增（i++）；它会创建（i = (i + 1)）
在代码的同一行添加注释
子命名空间

但有一些方法可以解决这个问题。我正在开发一个 CodeDom 清理项目，它将纠正这个问题并生成更美观的代码。CodeDom 版本：

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

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

程序员的版本

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

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

关于

在本节中，我添加了一些关于这个小应用程序的基本信息

AdventureWorks 数据库中 HumanResources Employee 类的示例

类布局

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
}

参考文献

SQLReader 网站
MSDN
- 数据库
- 对象
- 架构
- 表格
- Column
- 外键
- 外键列
- 索引
- 索引列
- 扩展属性
- 类型
- 默认约束
- 触发器
- 注释
- 标识列

历史

2008 年 7 月 16 日

发布版本 1.0.0.0。

2008 年 7 月 17 日

上传版本 1.0.0.1。
修复了“服务器名称”框，现在可以连接到远程 SQL Server （感谢：jklucker）。
将单词“lenght”更正为“Length”（感谢：CincDev）。
修复了一些基本的异常处理，但尚未完成（感谢：jklucker）。
添加了目录选择框（感谢：CincDev）。
添加了保存设置和加载设置（感谢：CincDev）。

2008 年 7 月 31 日

向类添加了 Select、Insert、Update、Delete 方法。
添加了 GetSQLCommandStrings 方法，该方法将返回一个字符串数组。

2008 年 8 月 19 日

添加了一个关于如何创建 ClassCollection（继承自 CollectionBase）的演示。

2008 年 9 月 16 日

添加了从视图生成代码的功能。
修复了导入列表中缺失的 System.Collections.Generic。
源代码文件现在是 Visual Studio 2008 而不是 Visual Studio 2005（抱歉，我不得不升级；我认为我们可以通过删除解决方案文件并自己创建它们来纠正这一点（从 VS 2008 更改为 VS 2005））。
注意：CP 文章向导在捉弄我；出于某种原因，我所有代码示例中的所有引号都被标记为 HTML 引号标签 ["]，我不知道为什么……抱歉。

2009 年 1 月 9 日

更新了以下类，以便它们可以处理区分大小写的排序规则：IndexColumn、IndexColumns、IdentityColumn、IdentityColumns、View、Views。
注意：CP 文章向导在捉弄我；出于某种原因，我所有代码示例中的所有引号都被标记为 HTML 引号标签 ["]，我不知道为什么……抱歉。