/// <summary>
/// Returns a random existing Primary Key value
/// </summary>
private int GetRandomPK(Random rnd, TableMetadata table)
{
using (DataStressConnection conn = CreateConnection())
{
conn.Open();
// This technique to get a random row comes from http://www.4guysfromrolla.com/webtech/042606-1.shtml
// When you set rowcount and then select into a scalar value, then the query is optimised so that
// just the last value is selected. So if n = ROWCOUNT then the query returns the n'th row.
int rowNumber = rnd.Next(Depth);
DbCommand com = conn.CreateCommand();
string cmdText = string.Format(
@"SET ROWCOUNT {0};
DECLARE @PK INT;
SELECT @PK = PrimaryKey FROM {1} WITH(NOLOCK)
SELECT @PK", rowNumber, table.TableName);
com.CommandText = cmdText;
object result = com.ExecuteScalarSyncOrAsync(CancellationToken.None, rnd).Result;
if (result == DBNull.Value)
{
throw DataStressErrors.TestError(string.Format("Table {0} returned DBNull for primary key", table.TableName));
}
else
{
int primaryKey = (int)result;
return(primaryKey);
}
}
}
// method used to delete stress tables in the database
protected void CleanupUserTables(TableMetadata tableMetadata)
{
string DropTable = "drop TABLE " + tableMetadata.TableName + ";";
using (DataStressConnection conn = CreateConnection(null))
{
conn.Open();
using (DbCommand com = conn.CreateCommand())
{
try
{
com.CommandText = DropTable;
com.ExecuteNonQuery();
}
catch (Exception) { }
}
}
}
protected bool OpenConnection(DataStressConnection conn)
{
try
{
conn.Open();
return(true);
}
catch (Exception e)
{
if (IsServerNotAccessibleException(e, conn.DbConnection.ConnectionString, conn.DbConnection.DataSource))
{
// Ignore this exception.
// This exception will fire when using named pipes with MultiSubnetFailover option set to true.
// MultiSubnetFailover=true only works with TCP/IP protocol and will result in exception when using with named pipes.
return(false);
}
else
{
throw e;
}
}
}
// method used to create stress tables in the database
protected void BuildUserTables(List <TableMetadata> TableMetadataList)
{
string CreateTable1 =
"CREATE TABLE stress_test_table_1 (PrimaryKey int identity(1,1) primary key, int_FLD int, smallint_FLD smallint, real_FLD real, float_FLD float, decimal_FLD decimal(28,4), " +
"smallmoney_FLD smallmoney, bit_FLD bit, tinyint_FLD tinyint, uniqueidentifier_FLD uniqueidentifier, varbinary_FLD varbinary(756), binary_FLD binary(756), " +
"image_FLD image, varbinarymax_FLD varbinary(max), timestamp_FLD timestamp, char_FLD char(756), text_FLD text, varcharmax_FLD varchar(max), " +
"varchar_FLD varchar(756), nchar_FLD nchar(756), ntext_FLD ntext, nvarcharmax_FLD nvarchar(max), nvarchar_FLD nvarchar(756), datetime_FLD datetime, " +
"smalldatetime_FLD smalldatetime);" +
"CREATE UNIQUE INDEX stress_test_table_1 on stress_test_table_1 ( PrimaryKey );" +
"insert into stress_test_table_1(int_FLD, smallint_FLD, real_FLD, float_FLD, decimal_FLD, " +
"smallmoney_FLD, bit_FLD, tinyint_FLD, uniqueidentifier_FLD, varbinary_FLD, binary_FLD, " +
"image_FLD, varbinarymax_FLD, char_FLD, text_FLD, varcharmax_FLD, " +
"varchar_FLD, nchar_FLD, ntext_FLD, nvarcharmax_FLD, nvarchar_FLD, datetime_FLD, " +
"smalldatetime_FLD) values ( 0, 0, 0, 0, 0, $0, 0, 0, '00000000-0000-0000-0000-000000000000', " +
"0x00, 0x00, 0x00, 0x00, '0', '0', '0', '0', N'0', N'0', N'0', N'0', '01/11/2000 12:54:01', '01/11/2000 12:54:00' );"
;
string CreateTable2 =
"CREATE TABLE stress_test_table_2 (PrimaryKey int identity(1,1) primary key, bigint_FLD bigint, money_FLD money, numeric_FLD numeric, " +
"time_FLD time, date_FLD date, datetimeoffset_FLD datetimeoffset, sql_variant_FLD sql_variant, " +
"datetime2_FLD datetime2, xml_FLD xml);" +
"CREATE UNIQUE INDEX stress_test_table_2 on stress_test_table_2 ( PrimaryKey );" +
"insert into stress_test_table_2(bigint_FLD, money_FLD, numeric_FLD, " +
"time_FLD, date_FLD, datetimeoffset_FLD, sql_variant_FLD, " +
"datetime2_FLD, xml_FLD) values ( 0, $0, 0, '01/11/2015 12:54:01', '01/11/2015 12:54:01', '01/11/2000 12:54:01 -08:00', 0, '01/11/2000 12:54:01', '0' );"
;
if (TableMetadataList == null)
{
TableMetadataList = new List <TableMetadata>();
}
List <TableColumn> tableColumns1 = new List <TableColumn>();
tableColumns1.Add(new TableColumn("PrimaryKey", -1));
tableColumns1.Add(new TableColumn("int_FLD", -1));
tableColumns1.Add(new TableColumn("smallint_FLD", -1));
tableColumns1.Add(new TableColumn("real_FLD", -1));
tableColumns1.Add(new TableColumn("float_FLD", -1));
tableColumns1.Add(new TableColumn("decimal_FLD", -1));
tableColumns1.Add(new TableColumn("smallmoney_FLD", -1));
tableColumns1.Add(new TableColumn("bit_FLD", -1));
tableColumns1.Add(new TableColumn("tinyint_FLD", -1));
tableColumns1.Add(new TableColumn("uniqueidentifier_FLD", -1));
tableColumns1.Add(new TableColumn("varbinary_FLD", 756));
tableColumns1.Add(new TableColumn("binary_FLD", 756));
tableColumns1.Add(new TableColumn("image_FLD", -1));
tableColumns1.Add(new TableColumn("varbinarymax_FLD", -1));
tableColumns1.Add(new TableColumn("timestamp_FLD", -1));
tableColumns1.Add(new TableColumn("char_FLD", -1));
tableColumns1.Add(new TableColumn("text_FLD", -1));
tableColumns1.Add(new TableColumn("varcharmax_FLD", -1));
tableColumns1.Add(new TableColumn("varchar_FLD", 756));
tableColumns1.Add(new TableColumn("nchar_FLD", 756));
tableColumns1.Add(new TableColumn("ntext_FLD", -1));
tableColumns1.Add(new TableColumn("nvarcharmax_FLD", -1));
tableColumns1.Add(new TableColumn("nvarchar_FLD", 756));
tableColumns1.Add(new TableColumn("datetime_FLD", -1));
tableColumns1.Add(new TableColumn("smalldatetime_FLD", -1));
TableMetadata tableMeta1 = new TableMetadata("stress_test_table_1", tableColumns1);
TableMetadataList.Add(tableMeta1);
List <TableColumn> tableColumns2 = new List <TableColumn>();
tableColumns2.Add(new TableColumn("PrimaryKey", -1));
tableColumns2.Add(new TableColumn("bigint_FLD", -1));
tableColumns2.Add(new TableColumn("money_FLD", -1));
tableColumns2.Add(new TableColumn("numeric_FLD", -1));
tableColumns2.Add(new TableColumn("time_FLD", -1));
tableColumns2.Add(new TableColumn("date_FLD", -1));
tableColumns2.Add(new TableColumn("datetimeoffset_FLD", -1));
tableColumns2.Add(new TableColumn("sql_variant_FLD", -1));
tableColumns2.Add(new TableColumn("datetime2_FLD", -1));
tableColumns2.Add(new TableColumn("xml_FLD", -1));
TableMetadata tableMeta2 = new TableMetadata("stress_test_table_2", tableColumns2);
TableMetadataList.Add(tableMeta2);
using (DataStressConnection conn = CreateConnection(null))
{
conn.Open();
using (DbCommand com = conn.CreateCommand())
{
try
{
com.CommandText = CreateTable1;
com.ExecuteNonQuery();
}
catch (DbException de)
{
// This can be improved by doing a Drop Table if exists.
if (de.Message.Contains("There is already an object named \'" + tableMeta1.TableName + "\' in the database."))
{
CleanupUserTables(tableMeta1);
com.ExecuteNonQuery();
}
else
{
throw de;
}
}
try
{
com.CommandText = CreateTable2;
com.ExecuteNonQuery();
}
catch (DbException de)
{
// This can be improved by doing a Drop Table if exists in the query itself.
if (de.Message.Contains("There is already an object named \'" + tableMeta2.TableName + "\' in the database."))
{
CleanupUserTables(tableMeta2);
com.ExecuteNonQuery();
}
else
{
throw de;
}
}
for (int i = 0; i < Depth; i++)
{
TrackedRandom randomInstance = new TrackedRandom();
randomInstance.Mark();
DbCommand comInsert1 = GetInsertCommand(randomInstance, tableMeta1, conn);
comInsert1.ExecuteNonQuery();
DbCommand comInsert2 = GetInsertCommand(randomInstance, tableMeta2, conn);
comInsert2.ExecuteNonQuery();
}
}
}
}
protected bool OpenConnection(DataStressConnection conn)
{
try
{
conn.Open();
return true;
}
catch (Exception e)
{
if (IsServerNotAccessibleException(e, conn.DbConnection.ConnectionString, conn.DbConnection.DataSource))
{
// Ignore this exception.
// This exception will fire when using named pipes with MultiSubnetFailover option set to true.
// MultiSubnetFailover=true only works with TCP/IP protocol and will result in exception when using with named pipes.
return false;
}
else
{
throw e;
}
}
}