public static void TestMain()
{
string connectionString = DataTestUtility.TcpConnStr;
string tempTable = DataTestUtility.GetUniqueName("T", "[", "]");
string tempKey = DataTestUtility.GetUniqueName("K", "[", "]");
DbProviderFactory provider = SqlClientFactory.Instance;
try
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
DbTransaction tx;
#region <<Create temp table>>
cmd.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract into " + tempTable + " from authors where au_id='UNKNOWN-ID'";
cmd.ExecuteNonQuery();
cmd.CommandText = "alter table " + tempTable + " add constraint " + tempKey + " primary key (au_id)";
cmd.ExecuteNonQuery();
#endregion
tx = con.BeginTransaction();
cmd.Transaction = tx;
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3210', 'Doe', 'Jane' , '882-8080', 'One Microsoft Way', 'Redmond', 'WA', '98052', 0)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3211', 'Doe', 'John' , '882-8181', NULL, NULL, NULL, NULL, 0)";
cmd.ExecuteNonQuery();
tx.Commit();
cmd.Transaction = null;
string parameterName = "@p1";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = parameterName;
p1.Value = "876-54-3210";
cmd.Parameters.Add(p1);
cmd.CommandText = "select * from " + tempTable + " where au_id >= " + parameterName;
// Test GetValue + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
actualResult.Append(rdr.GetValue(i));
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValue<T> + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValue <bool>(i));
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValue <decimal>(i));
}
else
{
actualResult.Append(rdr.GetFieldValue <string>(i));
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValueAsync<T> + IsDBNullAsync
using (DbDataReader rdr = cmd.ExecuteReaderAsync().Result)
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.ReadAsync().Result)
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNullAsync(i).Result)
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValueAsync <bool>(i).Result);
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValueAsync <decimal>(i).Result);
}
else
{
actualResult.Append(rdr.GetFieldValueAsync <string>(i).Result);
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
}
// GetStream
byte[] correctBytes = { 0x12, 0x34, 0x56, 0x78 };
string queryString;
string correctBytesAsString = "0x12345678";
queryString = string.Format("SELECT CAST({0} AS BINARY(20)), CAST({0} AS IMAGE), CAST({0} AS VARBINARY(20))", correctBytesAsString);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
byte[] buffer = new byte[256];
Stream stream = reader.GetStream(i);
int bytesRead = stream.Read(buffer, 0, buffer.Length);
for (int j = 0; j < correctBytes.Length; j++)
{
Assert.True(correctBytes[j] == buffer[j], "ERROR: Bytes do not match");
}
}
}
}
// GetTextReader
string[] correctStrings = { "Hello World", "\uFF8A\uFF9B\uFF70\uFF9C\uFF70\uFF99\uFF84\uFF9E" };
string[] collations = { "Latin1_General_CI_AS", "Japanese_CI_AS" };
for (int j = 0; j < collations.Length; j++)
{
string substring = string.Format("(N'{0}' COLLATE {1})", correctStrings[j], collations[j]);
queryString = string.Format("SELECT CAST({0} AS CHAR(20)), CAST({0} AS NCHAR(20)), CAST({0} AS NTEXT), CAST({0} AS NVARCHAR(20)), CAST({0} AS TEXT), CAST({0} AS VARCHAR(20))", substring);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
char[] buffer = new char[256];
TextReader textReader = reader.GetTextReader(i);
int charsRead = textReader.Read(buffer, 0, buffer.Length);
string stringRead = new string(buffer, 0, charsRead);
Assert.True(stringRead == (string)reader.GetValue(i), "ERROR: Strings to not match");
}
}
}
}
}
}
finally
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
}
