public void _05_CreateAndDropTable()
{
DBDatabase db = ConnectDb();
//Create the table
CreateCustomTable(db);
//Insert a single row in the database
DBQuery ins = InsertARowIntoCustomTable(db, "First", "Second");
//Count the number of rows in the table
DBQuery count = DBQuery.SelectCount().From(TableName);
int result = Convert.ToInt32(db.ExecuteScalar(count));
Assert.AreEqual(1, result);
TestContext.WriteLine("The row was inserted and the the returned count was '{0}'", result);
//Select all the rows and read
DBQuery select = DBQuery.SelectAll().From(TableName);
int readcount = 0;
int colA;
string colB = null;
string colC = null;
db.ExecuteRead(select, reader =>
{
while (reader.Read())
{
colA = reader.GetInt32(0);
colB = reader.GetString(1);
colC = reader.GetString(2);
readcount++;
}
});
//Based on the last row read (which should be the only row)
Assert.AreEqual("First", colB);
Assert.AreEqual("Second", colC);
TestContext.WriteLine("Read {0} rows from the select statement and values were as predicted", readcount);
//Dropping the table
DropCustomTable(db);
//Try another insert to make sure it fails
try
{
InsertARowIntoCustomTable(db, "None", DBNull.Value);
throw new InvalidOperationException("The table was not dropped and the insert statement succeeded");
}
catch (System.Exception ex)
{
TestContext.WriteLine("Expected failure after inserting on a dropped table:{0}", ex.Message);
}
}
protected override DBDatabaseProperties GetPropertiesFromDb(DBDatabase forDatabase)
{
string statement = "SELECT SERVERPROPERTY('productversion') AS [version], SERVERPROPERTY ('productlevel') AS [level], SERVERPROPERTY ('edition') AS [edition]";
DBDatabaseProperties props;
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(forDatabase.ConnectionString);
string dbname = builder.InitialCatalog;
if (string.IsNullOrEmpty(dbname))
{
dbname = builder.DataSource;
}
TypedOperationCollection unsupported = new TypedOperationCollection();
this.FillNotSupported(unsupported);
DBSchemaInformation info = DBSchemaInformation.CreateDefault();
TopType[] tops = new TopType[] { TopType.Count, TopType.Percent, TopType.Range };
bool caseSensitive = false;
string level = "?";
string edition = "?";
Version version = new Version(1, 0);
forDatabase.ExecuteRead(statement, reader =>
{
if (reader.Read())
{
level = reader["level"].ToString();
edition = reader["edition"].ToString();
version = new Version(reader["version"].ToString());
}
});
props = new DBDatabaseProperties(dbname, "SQL Server",
level,
edition,
"@{0}",
version,
SupportedSchemaOptions.All,
caseSensitive,
DBParameterLayout.Named,
SUPPORTED_TYPES, tops,
info,
unsupported);
props.TemporaryTableConstruct = "";
props.TemporaryTablePrefix = "#";
return(props);
}
protected override DBDatabaseProperties GetPropertiesFromDb(DBDatabase forDatabase)
{
TypedOperationCollection unsupported = new TypedOperationCollection();
this.FillNotSupported(unsupported);
DBSchemaInformation info = DBSchemaInformation.CreateDefault();
TopType[] tops = new TopType[] { TopType.Count, TopType.Percent, TopType.Range };
bool caseSensitive = false;
string level = "?";
string edition = "?";
string database = "?";
Version version = new Version(1, 0);
string statement = "SELECT SERVERPROPERTY('productversion') AS [version], SERVERPROPERTY ('productlevel') AS [level], SERVERPROPERTY ('edition') AS [edition], DB_NAME() As [currentdatabase]";
forDatabase.ExecuteRead(statement, reader =>
{
if (reader.Read())
{
level = reader["level"].ToString();
edition = reader["edition"].ToString();
version = new Version(reader["version"].ToString());
database = reader["currentdatabase"].ToString();
}
});
DBDatabaseProperties props = new DBDatabaseProperties(database, "SQL Server",
level,
edition,
"?",
version,
SupportedSchemaOptions.All,
caseSensitive,
DBParameterLayout.Positional,
SUPPORTED_TYPES, tops,
info,
unsupported);
props.TemporaryTableConstruct = "";
props.TemporaryTablePrefix = "#";
return(props);
}
public void _12_CreateSProc()
{
DBDatabase db = ConnectDb();
this.CreateCustomTable(db);
try
{
DBParam name = DBParam.Param("name", DbType.String, 50);
DBParam desc = DBParam.Param("desc", DbType.String, 150);
DBQuery ins = DBQuery.InsertInto(TableName).Fields(TblCol2, TblCol3).Values(name, desc);
char one = 'A';
char two = 'a';
TestContext.WriteLine(ins.ToSQLString(db));
for (int i = 0; i < 26; i++)
{
int c = (int)one;
c += i;
string offset = ((char)c).ToString() + two.ToString();
name.Value = offset;
desc.Value = "Description of " + offset;
db.ExecuteNonQuery(ins);
}
int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
Assert.AreEqual(26, count);
DBQuery q = DBQuery.Create.StoredProcedure("finditemsincol2")
.WithParam("p1", DbType.String, 50, ParameterDirection.Input)
.As(
DBQuery.SelectAll().From(TableName)
.WhereField(TblCol2, Compare.Like, DBParam.Param("p1"))
);
TestContext.WriteLine("Execute Procedure: " + q.ToSQLString(db));
db.ExecuteNonQuery(q);
TestContext.WriteLine("Created the new stored procedure");
DBQuery exec = DBQuery.Exec("finditemsincol2").WithParamValue("p1", DbType.String, 50, "A%");
count = 0;
TestContext.WriteLine(exec.ToSQLString(db));
db.ExecuteRead(exec, reader =>
{
while (reader.Read())
{
count++;
Assert.IsTrue(reader[TblCol2].ToString().StartsWith("A"));
}
TestContext.WriteLine("Executed the stored procedure and read '" + count.ToString() + "' rows");
});
Assert.AreEqual(1, count);
}
finally
{
try
{
DBQuery drop = DBQuery.Drop.StoredProcedure("finditemsincol2");
db.ExecuteNonQuery(drop);
TestContext.WriteLine("Sucessfully dropped the stored procedure");
}
catch
{
TestContext.WriteLine("DROP PROCEDURE failed");
}
this.DropCustomTable(db);
}
}
public void _06_CreateAndDropView()
{
DBDatabase db = ConnectDb();
CreateCustomTable(db);
//Wrap a try round this so we can definitely drop the table
try
{
InsertARowIntoCustomTable(db, "Beta", "Alpha");
InsertARowIntoCustomTable(db, "Alpha", "Beta");
//Create the view
DBQuery create = DBQuery.Create.View(ViewName)
.As(DBQuery.SelectAll()
.From(TableName)
.WhereField("ColB", Compare.Like, DBConst.String("A%")));
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
TestContext.WriteLine("Created the view");
DBQuery selectView = DBQuery.SelectAll().From(ViewName);
int totalcount = 0;
db.ExecuteRead(selectView, reader =>
{
while (reader.Read())
{
string colb = reader["ColB"] as string;
totalcount += 1;
}
});
TestContext.WriteLine("Total Number of rows in view = {0}", totalcount);
DBDropQuery drop = DBQuery.Drop.View(ViewName);
db.ExecuteNonQuery(drop);
//Try another select to make sure it fails
try
{
db.ExecuteNonQuery(selectView);
throw new InvalidOperationException("The table was not dropped and the insert statement succeeded");
}
catch (InvalidOperationException)
{
throw;
}
catch (System.Exception ex)
{
TestContext.WriteLine("Expected failure after inserting on a dropped table:{0}", ex.Message);
}
}
catch (Exception)
{
throw;
}
finally
{
DropCustomTable(db);
}
}
