private static List<Dialect> BuildDialectList(){ List<Dialect> l = new List<Dialect>(); Dialect d = new Dialect(){ Name="Sqlite", PathToAssembly="../../../ServiceStack.OrmLite.Sqlite/bin/Debug", AssemblyName="ServiceStack.OrmLite.Sqlite.dll", ClassName="ServiceStack.OrmLite.Sqlite.SqliteOrmLiteDialectProvider", InstanceFieldName="Instance", ConnectionString= "~/db.sqlite".MapAbsolutePath() }; l.Add(d); d = new Dialect(){ Name="SqlServer", PathToAssembly="../../../ServiceStack.OrmLite.SqlServer/bin/Debug", AssemblyName="ServiceStack.OrmLite.SqlServer.dll", ClassName="ServiceStack.OrmLite.SqlServer.SqlServerOrmLiteDialectProvider", InstanceFieldName="Instance", ConnectionString= "~/test.mdf".MapAbsolutePath() }; l.Add(d); d = new Dialect() {Name="MySql", PathToAssembly="../../../ServiceStack.OrmLite.MySql/bin/Debug", AssemblyName="ServiceStack.OrmLite.MySql.dll", ClassName="ServiceStack.OrmLite.MySql.MySqlDialectProvider", InstanceFieldName="Instance", ConnectionString= "Server = 127.0.0.1; Database = ormlite; Uid = root; Pwd = password" }; l.Add(d); d = new Dialect(){ Name="PostgreSQL", PathToAssembly="../../../ServiceStack.OrmLite.PostgreSQL/bin/Debug", AssemblyName="ServiceStack.OrmLite.PostgreSQL.dll", ClassName="ServiceStack.OrmLite.PostgreSQL.PostgreSQLDialectProvider", InstanceFieldName="Instance", ConnectionString="Server=localhost;Port=5432;User Id=postgres; Password=postgres; Database=ormlite" }; l.Add(d); d = new Dialect() {Name="FirebirdSql", PathToAssembly="../../../ServiceStack.OrmLite.Firebird/bin/Debug", AssemblyName="ServiceStack.OrmLite.Firebird.dll", ClassName="ServiceStack.OrmLite.Firebird.FirebirdOrmLiteDialectProvider", InstanceFieldName="Instance", ConnectionString="User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;" }; l.Add(d); return l; }
private static void TestDialect(Dialect dialect){ Console.Clear(); Console.WriteLine("Testing expressions for Dialect {0}", dialect.Name); OrmLiteConfig.ClearCache(); OrmLiteConfig.DialectProvider=dialect.DialectProvider; using (IDbConnection db = dialect.ConnectionString.OpenDbConnection()) using ( IDbCommand dbCmd = db.CreateCommand()) { dbCmd.DropTable<Person>(); dbCmd.DropTable<City>(); dbCmd.DropTable<Country>(); dbCmd.CreateTable<Country>(); dbCmd.CreateTable<City>(); dbCmd.CreateTable<Person>(); dbCmd.InsertAll<Country>(Factory.CountryList); dbCmd.InsertAll<City>(Factory.CityList); dbCmd.InsertAll<Person>(Factory.PersonList); try{ var vis = ReadExtensions.CreateExpression<TestPerson>(); vis.Where(r=>r.Continent=="Europe"); Console.WriteLine(vis.ToSelectStatement()); Console.WriteLine("-----------------------------------------"); vis.ExcludeJoin=true; vis.Where(); Console.WriteLine(vis.ToSelectStatement()); Console.WriteLine("-----------------------------------------"); var r0 = dbCmd.Select<TestPerson>(); Console.WriteLine("Records en person: '{0}'", r0.Count); vis.ExcludeJoin=false; vis.Select(r=> new {r.Continent, r.Name}).OrderBy(r=>r.BirthCountry); Console.WriteLine(vis.ToSelectStatement()); Console.WriteLine("-----------------------------------------"); vis.SelectDistinct(r=>r.Name); Console.WriteLine(vis.ToSelectStatement()); Console.WriteLine("-----------------------------------------"); vis.Select(); vis.Where(r=>r.Continent=="Europe").OrderBy(r=>r.BirthCountry); r0= dbCmd.Select(vis); Console.WriteLine("Records en person r.Continent=='Europe': '{0}'", r0.Count); r0= dbCmd.Select<TestPerson>(r=> r.BirthCity=="London"); Console.WriteLine("Records en person r.BirthCity=='London': '{0}'", r0.Count); TestPerson tp = r0[0]; tp.Id=0; dbCmd.Insert(tp); tp.Id= (int) dbCmd.GetLastInsertId(); Console.WriteLine("El id es :'{0}'", tp.Id); Console.WriteLine("Actualizados : '{0}'",dbCmd.UpdateOnly(tp, r=> r.Name, r=>r.Id==0)); try{ dbCmd.Update(tp); // all fields, except PK are updated where tp.Id==15 } catch(Exception e){ Console.WriteLine(e.Message); } Console.WriteLine("Borrados : '{0}'",dbCmd.Delete<TestPerson>( r=>r.Id==0)); int expected=6; var r1= dbCmd.Select<City>(qr=> qr.Population>=10); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r1.Count, expected==r1.Count?"OK":"********* FAILED *********"); expected=7; var r2= dbCmd.Select<Join1>(qr=>qr.Population<=5); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r2.Count, expected==r2.Count?"OK":"********* FAILED *********"); expected=3; var r3= dbCmd.Select<Join2>(qr=>qr.BirthCity=="London"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r3.Count, expected==r3.Count?"OK":"********* FAILED *********"); expected=5; var r4 =dbCmd.Select<Join3>(qr=>qr.Continent=="Europe"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r4.Count, expected==r4.Count?"OK":"********* FAILED *********"); expected=5; var city="Bogota"; var r5 = dbCmd.Select<PersonCity>(qr=> qr.JobCity==city); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r5.Count, expected==r5.Count?"OK":"********* FAILED *********"); expected=6; var r6 = dbCmd.Select<DerivatedFromPerson>(qr=>qr.BirthCityId!=qr.JobCityId); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r6.Count, expected==r6.Count?"OK":"********* FAILED *********"); expected=2; var r7= dbCmd.Select<DerivatedFromDerivatedFromPerson>(qr=>qr.Continent=="Asia"); Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r7.Count, expected==r7.Count?"OK":"********* FAILED *********"); var r8= dbCmd.Select<DerivatedFromDerivatedFromPerson>( exp=>exp. Where(qr=>qr.BirthCityId!=qr.JobCityId). OrderBy(qr=>qr.Continent)); Console.WriteLine("Expected:{0} Selected:{1} {2}", "America", r8.FirstOrDefault().Continent, "America"==r8.FirstOrDefault().Continent?"OK":"********* FAILED *********"); } catch(Exception e){ Console.WriteLine(e); Console.WriteLine(e.Message); } } Console.WriteLine("Press enter to return to main menu"); Console.ReadLine(); PaintMenu(); }