public void Where_Simple() { SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>().Where("productID").IsGreaterThan(5); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string w = gen.GenerateWhere(); Assert.AreEqual(" WHERE [dbo].[Products].[ProductID] > @ProductID0\r\n", w); }
public void Drop_Table() { ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildDropTableStatement(Product.Schema); Assert.AreEqual("DROP TABLE [dbo].[Products]", sql); }
public void Where_Simple() { SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>().Where("productID").IsGreaterThan(5); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string w = gen.GenerateWhere(); Assert.AreEqual(" WHERE [dbo].[Products].[ProductID] > @ProductID0\r\n", w); }
public static void IntersectsWithCast_CountTest() { var expected = @"WHERE (geometry::Parse('POINT (' + CONVERT(varchar, [CreatedBy]) + ' ' + CONVERT(varchar, [CreatedOn]) + ')')).STIntersects(geometry::Parse(geometry::Parse(@Parameter00).MakeValid().STUnion(geometry::Parse(@Parameter00).MakeValid().STStartPoint()).ToString()).ToString()) = 1"; var select = Select.AllColumnsFrom<Product>() .Where(Geospatial.SqlHelper.CastAsPoint(GeospatialType.Geometry, Product.Columns.CreatedBy, Product.Columns.CreatedOn)).IntersectsWith(TestPolygon.ToString(), GeospatialType.Geometry); var gen = new ANSISqlGenerator(select); var sql = gen.GetCountSelect().Trim(); Console.WriteLine(sql); Assert.IsTrue(sql.Contains(expected)); }
public static void Intersects_Test() { var expected = @"WHERE ([dbo].[Products].[CreatedBy]).STIntersects(geography::Parse(geometry::Parse(@CreatedBy0).MakeValid().STUnion(geometry::Parse(@CreatedBy0).MakeValid().STStartPoint()).ToString()).ToString()) = 1"; var select = Select.AllColumnsFrom<Product>() .Where(Product.Columns.CreatedBy).IntersectsWith(TestPolygon.ToString()); var gen = new ANSISqlGenerator(select); var sql = gen.GenerateWhere().Trim(); Console.WriteLine(sql); Assert.AreEqual(expected, sql); }
public void Select_Generate_FromList() { Select qry = Select.AllColumnsFrom<Product>(); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string from = gen.GenerateFromList(); Assert.AreEqual(" FROM [dbo].[Products]\r\n", from); }
public void Select_ColumnList_Specified() { SubSonic.SqlQuery qry = new Select("productid", "productname").From(Product.Schema); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string selectList = gen.GenerateCommandLine(); Assert.AreEqual("SELECT [dbo].[Products].[ProductID], [dbo].[Products].[ProductName]\r\n", selectList); }
public void Select_Generate_FromList() { Select qry = Select.AllColumnsFrom <Product>(); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string from = gen.GenerateFromList(); Assert.AreEqual(" FROM [dbo].[Products]\r\n", from); }
public void Create_Table() { ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildCreateTableStatement(Product.Schema); Assert.AreEqual( "CREATE TABLE [dbo].[Products] (\r\n [ProductID] int NOT NULL PRIMARY KEY IDENTITY(1,1),\r\n [ProductName] nvarchar(40) NOT NULL,\r\n [SupplierID] int NULL,\r\n [CategoryID] int NULL,\r\n [QuantityPerUnit] nvarchar(20) NULL,\r\n [UnitPrice] money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (((0))),\r\n [UnitsInStock] int NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (((0))),\r\n [UnitsOnOrder] int NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (((0))),\r\n [ReorderLevel] int NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (((0))),\r\n [Discontinued] bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (((0))),\r\n [AttributeXML] varchar NULL,\r\n [DateCreated] datetime NULL CONSTRAINT DF_Products_DateCreated DEFAULT ((getdate())),\r\n [ProductGUID] uniqueidentifier NULL CONSTRAINT DF_Products_ProductGUID DEFAULT ((newid())),\r\n [CreatedOn] datetime NOT NULL CONSTRAINT DF_Products_CreatedOn DEFAULT ((getdate())),\r\n [CreatedBy] nvarchar(50) NULL,\r\n [ModifiedOn] datetime NOT NULL CONSTRAINT DF_Products_ModifiedOn DEFAULT ((getdate())),\r\n [ModifiedBy] nvarchar(50) NULL,\r\n [Deleted] bit NOT NULL CONSTRAINT DF_Products_Deleted DEFAULT (((0))) \r\n)", sql); }
public void Remove_Column() { TableSchema.Table productSchema = Product.Schema; TableSchema.TableColumn column = productSchema.GetColumn("ProductName"); ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildDropColumnStatement(productSchema, column); Assert.AreEqual("ALTER TABLE [dbo].[Products] DROP COLUMN [ProductName]", sql); }
public void Alter_Column() { TableSchema.Table productSchema = Product.Schema; TableSchema.TableColumn column = productSchema.GetColumn("ProductName"); column.MaxLength = 150; ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildAlterColumnStatement(column); Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql); // Set it back to 40 or Create_Table fails. column.MaxLength = 40; }
public void Add_Column() { TableSchema.Table productSchema = Product.Schema; TableSchema.TableColumn column = new TableSchema.TableColumn(productSchema); column.ColumnName = "Address4"; column.DataType = DbType.String; column.MaxLength = 50; column.IsNullable = true; ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildAddColumnStatement(Product.Schema, column); Assert.AreEqual("ALTER TABLE [dbo].[Products] ADD [Address4] nvarchar(50) NULL", sql); }
public void Select_Generate_JoinList() { SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>() .InnerJoin(Category.Schema) .InnerJoin(Supplier.Schema); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string joins = gen.GenerateJoins(); Assert.AreEqual( " INNER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]\r\n", joins); }
public void Select_Generate_JoinList() { SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>() .InnerJoin(Category.Schema) .InnerJoin(Supplier.Schema); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string joins = gen.GenerateJoins(); Assert.AreEqual( " INNER JOIN `main`.`Categories` ON `main`.`Products`.`CategoryID` = `main`.`Categories`.`CategoryID`\r\n INNER JOIN `main`.`Suppliers` ON `main`.`Products`.`SupplierID` = `main`.`Suppliers`.`SupplierID`\r\n", joins); }
public void Select_Generate_JoinList() { SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>() .InnerJoin(Category.Schema) .InnerJoin(Supplier.Schema); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string joins = gen.GenerateJoins(); Assert.AreEqual( " INNER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]\r\n", joins); }
public void Where_And() { SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>().Where("productID").IsGreaterThan(5).And("categoryID").IsEqualTo(5); ANSISqlGenerator gen = new ANSISqlGenerator(qry); string w = gen.GenerateWhere(); Assert.AreEqual( " WHERE `main`.`Products`.`ProductID` > @ProductID0\r\n AND `main`.`Products`.`CategoryID` = @CategoryID1\r\n", w); }
public void Alter_Column() { TableSchema.Table productSchema = Product.Schema; TableSchema.TableColumn column = productSchema.GetColumn("ProductName"); column.MaxLength = 150; ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildAlterColumnStatement(column); Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql); }
public void Create_Table() { ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildCreateTableStatement(Product.Schema); //Assert.Fail("sql = " + sql); string expected = @"CREATE TABLE `main`.`Products` ( `ProductID` int NOT NULL PRIMARY KEY IDENTITY(1,1), `ProductName` nvarchar(150) NOT NULL, `SupplierID` int NULL, `CategoryID` int NULL, `QuantityPerUnit` nvarchar(MAX) NULL, `UnitPrice` real NULL, `UnitsInStock` int NULL, `UnitsOnOrder` int NULL, `ReorderLevel` int NULL, `Discontinued` bit NOT NULL, `AttributeXML` nvarchar(MAX) NULL, `DateCreated` datetime NULL, `ProductGUID` uniqueidentifier NULL, `CreatedOn` datetime NOT NULL, `CreatedBy` nvarchar(MAX) NULL, `ModifiedOn` datetime NOT NULL, `ModifiedBy` nvarchar(MAX) NULL, `Deleted` bit NOT NULL )"; Assert.AreEqual(expected, sql); }
public void Create_Table() { ANSISqlGenerator gen = new ANSISqlGenerator(null); string sql = gen.BuildCreateTableStatement(Product.Schema); Assert.AreEqual( "CREATE TABLE [dbo].[Products] (\r\n [ProductID] int NOT NULL PRIMARY KEY IDENTITY(1,1),\r\n [ProductName] nvarchar(40) NOT NULL,\r\n [SupplierID] int NULL,\r\n [CategoryID] int NULL,\r\n [QuantityPerUnit] nvarchar(20) NULL,\r\n [UnitPrice] money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (((0))),\r\n [UnitsInStock] int NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (((0))),\r\n [UnitsOnOrder] int NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (((0))),\r\n [ReorderLevel] int NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (((0))),\r\n [Discontinued] bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (((0))),\r\n [AttributeXML] nvarchar NULL,\r\n [DateCreated] datetime NULL CONSTRAINT DF_Products_DateCreated DEFAULT ((getdate())),\r\n [ProductGUID] uniqueidentifier NULL CONSTRAINT DF_Products_ProductGUID DEFAULT ((newid())),\r\n [CreatedOn] datetime NOT NULL CONSTRAINT DF_Products_CreatedOn DEFAULT ((getdate())),\r\n [CreatedBy] nvarchar(50) NULL,\r\n [ModifiedOn] datetime NOT NULL CONSTRAINT DF_Products_ModifiedOn DEFAULT ((getdate())),\r\n [ModifiedBy] nvarchar(50) NULL,\r\n [Deleted] bit NOT NULL CONSTRAINT DF_Products_Deleted DEFAULT (((0))) \r\n)", sql); }