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