65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL SERVER 2000,2005 和 MSDE 数据生成器

ganesan balachandar
starIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIconemptyStarIcon

1.43/5 (4投票s)

2006年2月28日

12分钟阅读

viewsIcon

90291

downloadIcon

970

该工具可以为任何表生成指定数量的行。可用于应用程序的压力测试

引言

生成大量数据本质上是为各种表生成所需的插入语句数量，同时考虑到某些列是外键，并且将插入到这些列中的值是从父表中获取的。牢记这些，因此开发了此工具“RDGen”。以下步骤描述了用于填充数据的逻辑。

  1. 对于选定的表，查找表中的所有列。
  2. 对于每一列，查找其数据类型并检查该列是否为外键。
  3. 如果它是外键，则从父表中获取一个值。
  4. 如果不是外键，则根据数据类型生成一个随机值。
  5. 为选定的表生成带有各种已生成值的插入语句。
  6. 重复步骤 3、4、5 所需的次数。

安装和编译示例代码

请在此文章随附找到源代码。更改文件 DatabaseCommunicationMethods.cs 中静态变量 ConnectionString 的值。

定义问题

我们需要从数据库中获取表列表并显示给用户。用户应该能够选择他喜欢的表并为该表生成数据。对于非外键的列，我们需要随机生成数据并插入到表中。我们将从哪里获取数据？即使它是外键，我们也将如何识别父表并从中随机选择一个值？让我们继续看。

定义解决方案

获取所需的元数据

1. 可以从视图 INFORMATION_SCHEMA.TABLES 获取表列表。它列出了数据库中的所有表和视图。因此，您需要添加一个过滤器条件 TABLE_TYPE =’BASE TABLE’。

2. 要获取列列表及其数据类型以及该列是否为外键，可以使用以下查询。

SELECT 
COLUMN_NAME,
DATA_TYPE,
isnull(CHARACTER_MAXIMUM_LENGTH,0) AS 'LENGTH',
IS_NULLABLE,
ISNULL((SELECT 'Y' FROM SYSFOREIGNKEYS WHERE 
        FKEYID =ID AND FKEY=COLID),'N') as 'IsForeignKey',
Ordinal_Position,
SYSCOLUMNS.IsComputed,
IsIdentity,
IsRowGuidCol

FROM 
SYSCOLUMNS,
(SELECT
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
Ordinal_Position,
COLUMNPROPERTY(OBJECT_ID('#TABLE_NAME#'), 
               INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 
               'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(OBJECT_ID('#TABLE_NAME#'), 
               INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 
               'IsRowGuidCol') AS IsRowGuidCol

FROM 
INFORMATION_SCHEMA.COLUMNS

WHERE
TABLE_NAME ='#TABLE_NAME#') AS A

WHERE 
ID 
IN
(SELECT ID FROM SYSOBJECTS WHERE TYPE='U' AND NAME ='#TABLE_NAME#') 
AND
A.COLUMN_NAME =NAME

Order By
Ordinal_Position

将 TABLE_NAME 替换为选定表的名称。SYSBOJECTS 包含有关所有对象的信息。SYSCOLUMNS 包含有关所有列的信息。SYSFOREIGNKEYS 包含有关外键的信息。查询从SYSOBJECTS 中检索表 id，并使用它从SYSCOLUMNS 中获取信息。此外，我们还获取该列是否为标识列、GUID 或计算列。

我们还需要知道对于外键列，父表是什么，引用列是什么。这就是系统存储过程“SP_FKEYS”变得非常有用的地方。我们可以传递

将选定表的名称作为参数（@FKTABLE_NAME）并获取其中所有外键的列表。我们只需要存储过程返回结果中的以下列。它们是

1. PKTABLE_NAME（父表）

2. PKCOLUMN_NAME（引用列）

3. FKCOLUMN_NAME（选定表中的外键列）。

填充外键列
使用 PKTABLE_NAME 和 PKCOLUMN_NAME，我们可以构造以下 SQL 语句

SELECT PKCOLUMN_NAME FROM PKTABLE_NAME

以获取选定表中外键列 FKCOLUMN_NAME 的值。但是我们需要只获取一个值。所以上面的查询需要修改如下。

SELECT TOP 1 PKCOLUMN_NAME FROM PKTABLE_NAME

只选择一个值。最好从父表中获取一个随机值。要从任何表中随机获取一条记录，我使用 NewID() T-SQL 函数。因此，上面的查询可以修改为

SELECT TOP 1 PKCOLUMN_NAME FROM PKTABLE_NAME ORDER BY NEWID().

填充其他列

现在我们知道如何获取元数据以及如何填充外键列的值。但是，我们将如何填充表中的其他列？我们将从哪里获取值？这就是 .NET Framework 中的 Random() 类派上用场的地方。例如

如果我们有一个 datetime 类型的列，我们可以这样生成该列的值。

有一个包含 1-28 天的列表。有一个包含 12 个月的列表。有一个包含您所需的年份列表。随机选择一天，选择一个月，选择一年。然后以 dd-MMM-YYY 的格式组合。（或者您可以将它们生成为 DD-MM-YYYY 格式。这是一个选择问题。）同样，我有一个包含大小写字母的列表。我根据列长度从列表中随机选择各种字符并将其附加以生成随机值。

我发现这种技术适用于所有数据类型，并且在程序中也更容易实现。该工具目前支持许多数据类型，但仍遗漏了一些。既然逻辑已详细描述，让我们继续找出该工具是如何实现的。

代码

上图显示了 RDGen 工具中的各种类。column() 类补充了表中的一个列。Table() 类补充了数据库中的一个表。它有一个列列表。ForeignKey 类补充了数据库中的外键。InsertStatement() 类补充了 INSERT 语句。RandomGenerator() 是可以生成随机字符、整数、日期、月份、年份或 tinyint 的类。 DataBuilder() 使用 RandomGenerator() 根据数据类型为每个列构建值。 DCM() 类处理与数据库服务器的通信。（DCM 代表数据库通信方法。）

让我们详细讨论每个类中的各种方法以及它们如何协同工作以生成大量数据。准备好进行代码演练，打开代码继续。

column() 类具有以下属性

Name（名称），

Data Type（数据类型），

Length（长度），

Nullable（是否允许空值），

ForeignKey（是否为外键）

IsIdentity（是否为标识列），

IsGuid（是否为 GUID），

ParentKeyColumn（仅对于外键，此属性会返回一个值），

ParentKeyTable（仅对于外键），

DataValue（用于保存生成的值）。

ForeignKey() 类包含以下属性

FKColumnName（外键列），

PKParentTable（父表）

PKColumnName（父表中被引用的列）。

insertstaatement() 类只是补充了 SQL 中的插入语句。

SQL 中的插入语句格式如下。

INSERT INTO TABLE (COL1, COL2, COL3) VALUES (VALUE1, VALUE2, VALUE3)。

它具有以下成员。

BeginStatement 包含值“Insert Into”。

_TableName 用于选定表的名称。

OpenPara 和 Close Para 分别用于“(”和“)”。

MiddleStatement 包含“Values”。

ColumnNames 用于包含用逗号分隔的列名。

ColumnValues 用于包含用逗号分隔的值。

已重写 ToString() 以返回格式化的 INSERT 语句。

RDGenTable() 补充了数据库中的表。它有一个“Column”类的列表来补充它拥有的列。它有一个 InsertStatement() 类作为成员来生成所需的 Insert Statement。它有一个名为 PopulateColumnNames() 的方法。该方法将生成一个包含逗号分隔的列名的字符串。它还有一个名为 PopulateData() 的方法，该方法为所有列填充数据。 PopulateData() 又使用其他方法 GetForeignKeyValue()（用于从父表中获取值）和 GetRandomValue()（用于返回根据列数据类型随机生成的值）。标识列不应包含在插入语句中，计算列也是如此。该工具目前不支持 GUID。IncludeColumn() 返回一个布尔值，该值决定表中的列是否将被包含在工具生成的插入语句中。

DCM() 类（DatabaseCommunication.cs）负责与数据库的通信。它有一个 GetTableList() 方法，该方法返回数据库中的所有表。 GetColumnList() 方法返回元有关选定表的列的元数据。 GetForeignKeyValue() 方法为特定外键从父表中返回一个值。

RandomGenerator() 和 DataBuilder() 类负责填充非外键列的值。RandomGenerator() 一次生成一个字符以适应各种数据类型。DataBuilder() 使用 RandomGenerator() 生成所需数量的字符。

RandomGenerator() 类有两个 HashTable

MonthSet - 一个包含所有月份的哈希表

characterset – 一个包含所有从 A-Z 和 a-z 的字符的哈希表。

RandomGenerator() 具有以下方法

1. GetDay()- 返回介于 1 和 28 之间的值。

2. GetMonth()- 从 MonthSet 返回一个随机值

3. GetYear()- 返回当前年份。

4. GetChar- 从 characterset 返回一个随机值

5. GetInt()- 返回一个介于 1 和 9 之间的数字。

6. GetTinyInt()- 返回一个介于 1 和 255 之间的数字。

DataBuilder() 类使用 RandomGenerator() 为表中的各种列生成值。

它具有以下方法。

1. GetBinary()

它不返回二进制值。而是生成一个字符值。它使用插入语句中的 cast 函数生成二进制值。

2. GetBit()

随机返回 0 或 1

3. GetBigInt()

返回 BigInt 类型列的值

4. GetChar()
根据列数据类型和长度返回一个字符串。

5. GetDateTime()

返回一个字符串以填充 datetime 数据类型的列。

6. GetInt()

返回一个字符串以填充 int 数据类型的列。

7. GetSmallInt()

返回一个字符串以填充 smallint 数据类型的列。

8. GetText()

返回一个长度为 500 个字符的字符串，用于填充 text 和 ntext 数据类型的列。

如果您看到所有函数都返回一个字符串。稍后我们将看到，所有这些都将连接起来以正确格式化插入语句。

资源文件 TableList.txt 和 ColumnList.txt 包含用于获取表列表和获取选定表列元数据的查询。由于 SQL 语句较长，因此最好将其放入资源文件中并在应用程序中读取，而不是将其硬编码在应用程序中。

现在我们已经看到了工具中各个类的描述，是时候继续找出所有这些类如何交互以生成选定表的大量记录了。

Stress Data Generator 工具有一个简单的 UI。cbTRableList - 用于显示表列表的组合框，txtNoOfRows - 用于输入所需记录数的文本框，txtOutput - 用于显示执行结果的文本框，ColumnsGrid - 用于显示元数据的网格，cmdGenerate - 用于触发数据生成过程的按钮，并显示网格中的元数据。

当窗体加载时，组合框中会填充应用程序连接的数据库中的表名。这是通过 DCM() 类的 GetTableList() 方法实现的。该方法返回一个 datatable，并将该 datatable 设置为 cbTableList 组合框的数据源。

请按照 cmdGenerate 按钮单击事件中的代码。

RDGenTable RDGenSelectedTable = new RDGenTable(cbTableList.Text);
ColumnsGrid.DataSource = RDGenSelectedTable.TableColumns;
StreamWriter sr = new StreamWriter(cbTableList.Text + ".sql");

for (int i = 1; i <= iRequiredRows; i++)
{
    sr.WriteLine( RDGenSelectedTable.GetInsertStatement());
    sr.WriteLine("");
}

txtOutput.Text = " Execute the contents of " + cbTableList.Text + 
                 ".sql" + " in Query Analyzer";
sr.Close();

通过将选定表的名称作为参数传递给构造函数，创建 RDGenTable() 的新实例。现在在 RDGenTable() 的构造函数中，我们有以下代码。

public RDGenTable(string TableName)
{
    this.Name = TableName;
    this.TableColumns = DCM.GetColumnList(this.Name);
    _InsertStatement.TableName = this.Name;
    PopulateColumnNames();
}

DCM.GetColumnList() 的静态方法通过将选定表作为参数名称来调用。现在 TableColumns（Column 类的列表）将被填充为表中所有列的元数据。

现在让我们深入研究 GetColumnList 方法。

List<ForeignKey> RDGenForeignKeys = new List<ForeignKey>();
SqlConnection RDGenConn = new SqlConnection(ConnectionString);
SqlCommand RDGenCommand = new SqlCommand("sp_fkeys", RDGenConn);
RDGenCommand.Parameters.AddWithValue("@fktable_name", TableName);
RDGenConn.Open();
RDGenCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader RDGenForeignKeyReader = RDGenCommand.ExecuteReader();

foreach(System.Data.Common.DbDataRecord ForeignKeyRecord 
                                        in RDGenForeignKeyReader)
{
    ForeignKey RDGenForeignKey = new ForeignKey();
    RDGenForeignKey.PKColumnName = (string)ForeignKeyRecord["PKCOLUMN_NAME"];
    RDGenForeignKey.PKTableName = (string)ForeignKeyRecord["PKTABLE_NAME"];
    RDGenForeignKey.FKColumnName = (string)ForeignKeyRecord["FKCOLUMN_NAME"];
    RDGenForeignKeys.Add(RDGenForeignKey);
}

在这里，我们首先通过执行存储过程 sp_fkeys 并带有参数 @fktable_name 来获取所选表所需的外部键信息。将选定的表名作为参数传递。RDGenForeignKeys 列表将填充父表名、引用列和选定表中每个外键的外键列名。

然后通过执行与应用程序关联的 ColumnList.txt 资源文件中的 SQL 来获取所有列的元数据。这里将字符串 #TABLE_NAME# 替换为选定的表名。

foreach(System.Data.Common.DbDataRecord ColumnRow in RDGenReader)
{
    Column TableColumn = new Column(); 
    TableColumn.Name = (string)ColumnRow[0];
    TableColumn.DataType = (string)ColumnRow[1];
    TableColumn.Length = (int)ColumnRow[2];
    TableColumn.Nullable = ((string)ColumnRow[3] == "Y" ? true : false);
    TableColumn.ForeignKey = ((string)ColumnRow[4] == "Y" ? true : false);

    /* if the column is a foreign key find the parent key and table */
    if (TableColumn.ForeignKey)
    {
        foreach (ForeignKey RDGenTableForeignKey in RDGenForeignKeys)
        {
            if (RDGenTableForeignKey.FKColumnName == TableColumn.Name)
            {
                TableColumn.ParentKeyColumn = 
                            RDGenTableForeignKey.PKColumnName;
                TableColumn.ParentKeyTable = RDGenTableForeignKey.PKTableName;
            }
        }
     }

     TableColumn.OrdinalPosition = (int)ColumnRow[5];
     TableColumn.IsComputed = ((int)ColumnRow[6] == 1? true : false);
     TableColumn.IsIdentity = ((int)ColumnRow[7] == 1 ? true : false);
     TableColumn.IsGuid = ((int)ColumnRow[8] == 1 ? true : false);
     RDGenColumns.Add(TableColumn);
}

我们从数据库读取选定表的元数据，并填充 RDGenColumns（这是 Column 类的列表）。对于每个外键列，我们还通过使用 RDGenForeignKeys 列表来填充 ParentKeyColumn、ParentKeyTable。

现在让我们回到从中调用该方法的 RDGenTable 构造函数。在调用 GetColumnList() 之后，将执行以下行。

_InsertStatement.TableName = this.Name;
PopulateColumnNames();

实例 _InsertStatement 的 TableName 被设置为选定的表。

之后，调用 PopulateColumnNames() 方法。在此方法中，_InsertStatement 的 ColumnsNames 将用选定表列名的逗号分隔值填充。（注意：如果列是标识列、计算列或 GUID 列，则不会包含在此处。同样，如果列具有不支持的类型，则不会包含。请参阅文章末尾的附加说明。）此时，我们已填充选定表插入语句的一半。（即）

INSERT INTO TABLE (COL1,COL2,COL3) VALUES

现在我们必须为选定表中的所有列生成值。请记住，此处必须处理外键列。现在让我们回到实例化 RDGenTable() 类的位置。为方便读者，我已在此处再次提供代码。

RDGenTable RDGenSelectedTable = new RDGenTable(cbTableList.Text);
ColumnsGrid.DataSource = RDGenSelectedTable.TableColumns;
StreamWriter sr = new StreamWriter(cbTableList.Text + ".sql");

for (int i = 1; i <= iRequiredRows; i++)
{
    sr.WriteLine( RDGenSelectedTable.GetInsertStatement());
    sr.WriteLine("");
}

txtOutput.Text = " Execute the contents of " + cbTableList.Text + 
                 ".sql" + " in Query Analyzer";

sr.Close();

如果看到，GetInsertStatement() 会按照指定次数调用。

现在让我们研究 GetInsertStatement() 方法代码。

public string GetInsertStatement()
{
    PopulateData();
    string InsertStatement = _InsertStatement.ToString();
    _InsertStatement.ColumnValues = "";
    return InsertStatement;
}

这只是调用 PopulateData 方法并返回 InsertStatement。而 PopulateData() 方法具有以下方法。

在 PopulateData() 方法中，我们根据列是否为外键来调用 GetForeignKeyValue()、GetRandomValue() 方法。DCM 类的 GetForeignKey() 方法调用 GetForeignKeyValue() 方法，从引用表中选择引用列的随机值。类似地，GetRandomValue() 使用 DataBuilder() 为选定的列生成值。

这是一个用于理解和扩展的小工具。它有效地完成了市场上各种商业工具的工作。为了方便在 VS.NET 中进行代码导航，我使用了免费工具 DPack，网址为 http://www.usysware.com/DPack/Default.aspx。

附加信息。

该工具不支持以下数据类型。

Image、uniqueidentifier、sql_variant、sysname。

结论

因此，我们可以非常快速地为各种表生成大量数据，同时考虑到它们之间的关系。祝您愉快！读取!

这是一个用于理解和扩展的小工具。它有效地完成了市场上各种商业工具的工作。为了方便在 VS.NET 中进行代码导航，我使用了免费工具 DPack，网址为 http://www.usysware.com/DPack/Default.aspx。

附加信息。

该工具不支持以下数据类型。

Image、uniqueidentifier、sql_variant、sysname。

© . All rights reserved.