How to Programming with Ormlite-servicestack

sql-server,transactions,ormlite-servicestack

ServiceStack.OrmLite translates an object represented query into a SQL query. So, if you can call your view from a SQL string (which is the case) then you can do it with ServiceStack.OrmLite

ServiceStack OrmLite and transactions

Someone else put this answer in a comment and then deleted it... so: BeginTransaction needs to be OpenTransaction...

ServiceStack ORMLite saving nested [Reference]

c#,.net,servicestack,ormlite-servicestack

Saving references on multi-nested structures isn't supported, but you're likely headed towards friction trying to convert a large JSON hierarchical document to a relational structure which has the potential to explode into multiple tables. A solution with less-friction is to just let OrmLite save nested complex types as schema-less text...

How to use InsertOnly method in OrmLite?

servicestack,ormlite,ormlite-servicestack

You can find some examples in ApiSqlServerTests, e.g: db.InsertOnly(new Poco { FirstName = "Amy", Age = 27 }, q => q.Insert(p => new { p.FirstName, p.Age })); and async versions in ApiSqlServerTestsAsync, e.g: await db.InsertOnlyAsync(new Poco { FirstName = "Amy", Age = 27 }, q => q.Insert(p => new {...

Querying POCO's with References

c#,.net,servicestack,ormlite,ormlite-servicestack

This is called Self References in OrmLite and works with your above example: public class Order : IHasId<long> { [AutoIncrement] public long Id { get; set; } [References(typeof(Material))] public long MaterialId { get; set; } [Reference] public Material Material { get; set; } } public class Material : IHasId<long> {...

ServiceStack Ormlite transaction between services

sql-server,transactions,servicestack,ormlite-servicestack

You shouldn't have nested transactions, rather than calling across services to perform DB operations you should extract shared logic out either using a separate shared Repository or re-usable extension methods: public static class DbExtensions { public static void SaveCaseModel(this IDbConnection db, CaseModel case) { db.Insert<Case>(case); db.SaveAllReferences<CaseModel>(case); } } Then your...

Ormlite int based enums coming as varchar(max)

Add a [Flags] attribute to enums you want ServiceStack to treat as integers.

ServiceStack “Declaration referenced in a method implementation cannot be a final method”

Your error message sounds like you're mixing different versions of ServiceStack together. Try clearing your NuGet packages Cache. Alternatively you can try the most recent v4.0.34 packages on MyGet. As for non ASP.NET MVC Examples, most of the ServiceStack Live Demos are Single Page Apps that don't use ASP.NET MVC...

Load all hierarchical references with Servicestack ORMLite

json,servicestack,ormlite-servicestack

OrmLite's db.Load* API's is limited to loading 1-level depth of references. The Db.LoadReferences(instance) can be used to further fetch the disconnected POCO's references. You should also be mindful if loading references individually to avoid N+1 queries by loading them in a loop, i.e. when possible it's better to use a...

Anonymous object blob in database not serializing as JSON when queried

Found this link that solved my problem: https://github.com/ServiceStackV3/mythz_blog/blob/master/pages/314.md Essentially I added a "Type" field to the ItemStep class, and set that when I create a new row (create the next step in the timeline). Then, when I retrieve that record, I call a method like "GetBody" in the referenced link...

ServiceStack ORMLIte : Id is necessary

c#,orm,ormlite-servicestack

The are several API's in OrmLite which rely on a primary key which will be either: A property annotated with the [PrimaryKey] attribute A property named Id Otherwise the first property is considered to be the primary key Some of OrmLite API's that rely on a primary key is: db.Update(entity)...

ServiceStack ORMLite - Can't Infer Relationship (because of DTO name?)

sql-server,transactions,ormlite-servicestack

I've just tried creating an AutoQuery Service with all the types you've provided under a custom MyNamespace and it's working as expected (in the latest of ServiceStack), i.e: Service definition: namespace MyNamespace { [Route("/visits/{VisitId}/services", Verbs = "GET")] public class ServicesAtVisit : QueryBase<VisitService, ServiceAtVisit>, IJoin<VisitService, Service> { public int VisitId {...

ServiceStack Ormlite transactions broken?

To use Transactions in OrmLite you should use the OpenTransaction() API, e.g: using (var trans = db.OpenTransaction()) { //... } I've added a couple new API's to be able to use an OrmLite transaction with a raw ADO.NET IDbCommand in this commit. Use a managed OrmLite DB Command Use a...

ServiceStack taking a long time to execute stored procedure

c#,sql,servicestack,ormlite-servicestack,sql-server-2014

Adding an additional answer because really there were two issues. The real root seems to be with the stored procedure. I don't know why it wasn't consistently causing problems, but I rebuilt it using dynamic SQL to only include WHERE filters for parameters that are present. Originally, I had something...

How do I escape special characters when using ServiceStack OrmLite with SQLite?

c#,sqlite,servicestack,ormlite-servicestack

I've just added implicit support for escaping wildcards in this commit which will now escape wildcards in the typed expressions that make use of LIKE, namely StartsWith, EndsWith and Contains, e.g: using (var db = OpenDbConnection()) { db.DropAndCreateTable<Poco>(); db.Insert(new Poco { Name = "a" }); db.Insert(new Poco { Name =...

One-to-Many relationship with ORMLite

sql,json,ormlite,ormlite-servicestack

An alternate more succinct example: public void Put(CreatePatient request) { var patient = new Patient { Name = request.Name, Insurances = request.Insurances.Map(x => new Insurance { Policy = i.Policy, Level = i.Level }) }; db.Save<Patient>(patient, references:true); } ...

Is there a built-in way for retrieving the parent object?

c#,.net,ormlite-servicestack

You should have a property like: [Reference] public ParentClass Parent { get; set; } on the ChildClass, and then you need to make sure that the property is properly filled up. You have the ParentId property on the child table in the database, so this shouldn't be any problem to...

ServiceStack OrmLite LeftJoin Issue

sql,servicestack,left-join,ormlite-servicestack

You should be able to use the new support for JOIN's in OrmLite's Typed SqlExpressions. It's best to use the latest v4.0.23 release on MyGet which includes improved support for selecting multiple columns across joined tables. With the new JOIN API's you can do what you need with something like:...

Potential illegal fragment detected when using OrmLite SqlExpression with Mysql?

mysql,servicestack,ormlite-servicestack

You should normally use the Custom Sql API's like db.SqlList<T> when executing Custom SQL. If you want to use Custom SQL in a typed SqlExpression you should use the Unsafe* API's to by-pass any Custom SQL detection. But for the Select API of an SqlExpression you should only be adding...

Can I generate SQL scripts with ServiceStack OrmLite?

c#,sqlite,servicestack,ormlite-servicestack

This is trivial now that OrmLite extension methods are now mockable by providing your own OrmLiteResultsFilter. E.g. this ResultsFilter below records every sql statement executed and inherts the behavior from OrmLiteResultsFilter to return empty results: public class CaptureSqlFilter : OrmLiteResultsFilter { public CaptureSqlFilter() { SqlFilter = CaptureSql; SqlStatements = new...

OrmLite: SQLiteExceptionSQL logic error or missing database near “)”: syntax error

This issue is because you Foo doesn't have any columns to INSERT since Id is a autoincrementing primary key and Bar is a [Reference] property so no columns are saved so the INSERT SQL ends up looking like: INSERT INTO "Foo" () VALUES (); This would work if you Foo...

Simple SELECT FOREIGN KEY with ServiceStack V3.9

c#,sql,servicestack,ormlite-servicestack

The easiest way to get the new Id would be to have the insert return it by using selectIdentity. You can do something like this: var newId = Db.Insert(DeviceInfo, selectIdentity: true); newId would then contain the AutoIncremented Id that was generated. Another thing that draws my eye is that the...

ServiceStack ormlite json does not deserialize from database

c#,serialization,servicestack,ormlite-servicestack,servicestack-text

The problem is that your class Inventory has a dictionary keyed by a complex class: public Dictionary<Item, int> Items { get; set; } However, according to the ServiceStack.Text documentation Any IDictionary is serialized into a standard JSON object, i.e: {"A":1,"B":2,"C":3,"D":4} Unfortunately your Item class cannot be represented as a simple...

How to globally change the command timeout using ServiceStack.OrmLite

c#,sql,servicestack,ormlite-servicestack

You can change the CommandTimeout globally with: OrmLiteConfig.CommandTimeout = NewTimeoutInSeconds; Scoped Timeout You can also specify a Timeout for a particular db connection with: using (var db = dbFactory.OpenDbConnection()) { db.SetCommandTimeout(NewTimeoutInSeconds); } ...

Servicestack Ormlite seems to be ignoring my Database Schemas c#

This issue is resolved from the v4.0.39+ release of ServiceStack that's now available on MyGet.

Generating Class Object from Postgresql Database ServiceStack.Ormlite

c#,asp.net,postgresql,servicestack,ormlite-servicestack

OrmLite's primarily a code-first ORM but does have T4 Templates to help with initially generating the code-first POCO's for existing database tables. Although after generating the POCO's, I'd be maintaining them (i.e. code-first) from that point forward....

How to retrieve Data Annotation Alias(“tablename”) and Alias(“field name”) from ServiceStack ORMLite?

c#,servicestack,data-annotations,ormlite-servicestack

You can query this from OrmLite's ModelDefinition that's created for every POCO Table, e.g: var modelDef = typeof(Account).GetModelMetadata(); var tableName = modelDef.ModelName; var idName = modelDef.PrimaryKey.FieldName; Which in both cases will return the [Alias] if it exists....

saving reference using ServiceStack ORMLite

orm,servicestack,ormlite-servicestack

You still need to provide the foreign key that OrmLite can use to store the relationship, e.g. either on the Child/ForeignKey table: public class Order { [AutoIncrement] public int Id { get; set; } [Reference] public Item Item { get; set; } public string ProUserId { get; set; } public...

How to extend ServiceStack IDbConnectionFactory and MiniProfiler

servicestack,ormlite-servicestack,miniprofiler

To enable SQL Profiling in MiniProfiler you need to register the OrmLiteConnectionFactory to use MiniProfilers ProfiledDbConnection, e.g: Container.Register<IDbConnectionFactory>(c => new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider) { ConnectionFilter = x => new ProfiledDbConnection(x, Profiler.Current) }); ...

Owin with custom ORM framework (not Entity Framework)

c#,asp.net,entity-framework,ormlite-servicestack

The OrmLiteConnectionFactory should be registered as a singleton. You can then use it to create ADO.NET IDbConnection with: using (var db = dbFactory.OpenDbConnection()) { // } Once it's registered as a Singleton you could use a lazy property pattern that's like in the RepositoryBase class to simplify data access, e.g:...

Access ServiceStack session from ConnectionFilter

The earlier multi tenant ServiceStack example shows how you can use the Request Context to store per-request items, e.g. you can populate the Request Context from a Global Request filter: GlobalRequestFilters.Add((req, res, dto) => { var session = req.GetSession(); if (session != null) RequestContext.Instance.Items.Add( "UserName", session.UserName); }); And access it...

ServiceStack Ormlite class with temporary field

Absolutely. You can simply add an [Ignore] attribute on the properties that are not part of your schema. From the documentation: Ignoring DTO Properties You may use the [Ignore] attribute to denote DTO properties that are not fields in the table. This will force the SQL generation to ignore that...

Joining the same table multiple times in ServiceStack AutoQuery

c#,sql-server,servicestack,ormlite-servicestack

OrmLite (and by extension AutoQuery) doesn't support custom Aliases for Table JOIN's so you wont be able to query individual tables via AutoQuery. Although the normal use-case of: [Route("/purchase/query")] public class QueryPurchase : QueryBase<Purchase> { public int Id { get; set; } } client.Get(new QueryPurchase { Id = 1 }).PrintDump();...

ServiceStack.OrmLite SqlServer new Merge method doesn't work for different names of references

This should now be resolved with this commit which is available from v4.0.41+ that's now available on MyGet.

Error using Merge in Servicestack.OrmLite Sql Server

orm,many-to-many,servicestack,ormlite-servicestack

This is working as intended, the error message indicates that it couldn't find a static relationship that could be merged which negates the purpose of the Merge command - to merge related result sets. When there is no statically defined relationship that exists, this is clearly an error the developer...

How does ServiceStack ORMLite handle Many to many relationships?

There is no explicit support for Many to Many relationships in OrmLite, i.e. you would handle it by creating a Poco that maps to each RDBMS table and just save each as you would normally, see this previous answer for an example.

Annotated Ignore field get's ignored in servicestack reponse

mysql,servicestack,ormlite-servicestack

After mythz explained to me the fact that if it doesn't map to your ormlite db it won't map to your resulting DTO later, i build a quick work around. Instead of returning my own response dto immediately in the following line: ARequest : QueryBase<Person, Result>, IJoin<Person, OtherThing> I just...

MySQL decimal column precision with Servicestack ORMLite

I've just added support for custom decimal precision for MySql in this commit. This change is available from v4.0.37+ that's now available on MyGet....

Service Stack ormlite generate http response for tables linked

c#,sql,servicestack,ormlite-servicestack

First make sure your table has an auto incremented primary key. Second, Be careful with multiple inserts. You should call GetLastInsertedId directly after each insert. Under the hood in SQL it translates into SELECT SCOPE_IDENTITY() Your code should look more like the following: Db.Insert(new DeviceInfo.DeviceInfo { DeviceType = NewDevice.Type, HumanReadDevId...

Can I have OrmLite use lowercase for PostgreSQL column names rather than the provided lowercase with underbar naming?

postgresql,ormlite-servicestack

You can use specify your own naming strategy with: public class LowercaseNamingStrategy : OrmLiteNamingStrategyBase { public override string GetTableName(string name) { return name.ToLower(); } public override string GetColumnName(string name) { return name.ToLower(); } } OrmLiteConfig.DialectProvider.NamingStrategy = new LowercaseNamingStrategy(); ...

Both calling Stored Procedure and SQL expression having problems when mapped to POCO which is not a domain object

c#,sql-server,orm,servicestack,ormlite-servicestack

From docs for using for calling Stored Procedures using SqlList, try instead calling your Stored Procedure with: var results = db.SqlList<DomainUserDto>( "EXEC TestJoinSupportForORMLite @UserId", new { UserId = id }); The issue is because you're returning ProUser's string Id but your DomainUserDto is trying to coerce it into an int...

ORMLIte[ServiceStack] . SaveReference method does not add items in the List

c#,orm,servicestack,ormlite-servicestack

You need to explicitly commit your ADO.NET transaction, i.e: using(var trans = Db.OpenTransaction(IsolationLevel.ReadCommitted)) { Db.Save(user); Db.SaveReferences(user,user.HomeAddress); Db.SaveReferences(user,user.Orders); trans.Commit(); return Find(user.Id); } ...

ServiceStack's Ormlite Delete not working

The way db.Delete() API works has been updated so that NULL fields are moved out of the parameterized queries and appended to the SQL filter so this should now work from v4.0.37+ that's now available on MyGet. You can also delete rows in OrmLite by PrimaryKey with: Db.DeleteById<TestTable>(entity.Id); For generic...

Using ServiceStack.OrmLite how can I add a bool column with a default value?

c#,ormlite-servicestack

On ServiceStack 4.0.36 with Azure SQL server I was able to use: [Default(typeof(bool), "0")] For the "false" default value on a boolean (bit) field....

Servicestack OrmLite deleting many to many

db.Save() only INSERT or UPDATE entities i.e. it doesn't DELETE them. To delete, retrieve the entities or entity Ids you want to delete and use OrmLite's db.Delete* API's explicitly, e.g. something like: var removeUsersAttendingIds = listingEvent.UsersAttending .Where(u => u.UserAccountId == user.Id) .Select(u => u.Id); db.DeleteByIds<UserAccountListingEvent>(removeUsersAttendingIds); ...

Whats the difference between ServiceStack.OrmLite packages and .Signed version?

ServiceStack NuGet packages with a .Signed Suffix are strong-named, where other ServiceStack packages are not Strong named by default except for ServiceStack.Interfaces which is both Strong-Named and a pure PCL library for maximum accessibility. The ServiceStack download page maintains the list of available Signed packages....

ServiceStack.OrmLite get multiple result sets from a stored procedure

linq,servicestack,ormlite-servicestack

Unfortunately, ServiceStack.OrmLite does not support multiple result sets unless combined with Dapper. ServiceStack MARS (Multiple Active Result Sets) using ORMLite and Output Parameters Alternatively, you can use the .Net SqlCommand. Return multiple recordsets from stored proc in C# ServiceStack.OrmLite V4 notes: https://github.com/ServiceStack/ServiceStack.OrmLite ServiceStack.OrmLite V3 notes: https://github.com/ServiceStack/ServiceStack.OrmLite/tree/v3...

LINQ query fails with nullable variable ormlite

This is nature of the Linq. In order to achieve what you need, you will need to use two where closes: dbConn.Where<Product>(p => p.IsActive.HasValue).Where(p=>p.Value==true); ...

Does ORMLite support dynamic Type of C#?

c#,orm,servicestack,ormlite-servicestack

By design OrmLite does not support marshalling to dynamic types, and expects resultsets to mapped to Typed POCO's. Although it does have specialized API's to map into loose-typed .NET collections: Dictionary<int, string> trackIdNamesMap = db.Dictionary<int, string>( "select Id, Name from Track") Dictionary<int, List<string>> albumTrackNames = db.Lookup<int, string>( "select AlbumId, Name...

ServiceStack.OrmLite support for IBM DB2

db2,servicestack,ormlite-servicestack

No there isn't a provider for DB2, the 8 OrmLite Dialect Providers that are available are listed on the OrmLite Home Page

Convert SQL to ServiceStack.ORMLite Sql Server

c#,sql-server,tsql,servicestack,ormlite-servicestack

For custom SQL like this, you'd use OrmLite's Custom SQL API's, with something like: var results = db.Select<Poco>(@"select convert(date, t.TransactionDate) [Date], tm.TeamName, a.AccountName, count(distinct(t.RequisitionNumber)) Total from task.tblTransactions t inner join task.tblRequisitions r on r.RequisitionNumber = t.RequisitionNumber inner join task.tblAccounts a on a.AccountNumber = r.AccountNumber inner join Team tm on tm.DivisionId...

How to debug ServiceStack Ormlite when things go wrong?

.net,authentication,servicestack,ormlite-servicestack

View Last SQL Executed The easiest approach is to just print out the last SQL Statement that was executed, i.e: db.GetLastSql().Print(); This works well for most OrmLite API's which only execute a single SQL statement. But it wont show all SQL executed with OrmLite's higher-level API's like db.Save() which can...

Post-registration action in ServiceStack

Customizing Authenticate Service Response I would recommend that you always return a concrete class in ServiceStack Services (i.e. instead of an anonymous object) this helps with being able to generate metadata services for the DTO types and lets you deserialize the response in typed APIs. You should also consider adhering...

How do I register IDbConnectionFactory using Castle Windsor

servicestack,castle-windsor,ormlite-servicestack

This will be the equivalent in Castle Windsor: container.Register(Component.For<IDbConnectionFactory>() .Instance( new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider))); ...

How to do a GroupBy statement with ServiceStack OrmLite