SubSonic 3.0 Preview 2
Over the last week I've made massive improvements and upgrades to SubSonic 3.0 and the templates that it comes with. I haven't been this obsessive and inspired since I created SubSonic in the first place, and I have to tell you I am more than stoked to see this pull together.
The Foundation
I'll be really concise with this post - there's so much here ... it will be hard but I really want to keep to the point. SubSonic 3.0 is a Layer Cake which is focused on openness and transparency. Here are the layers:
- Core: Data Access and SQL Translation. This is the part that executes commands against the database, and translates queries (SubSonic's or Linq's) into SQL.
- Surface: Working with your database. This layer "surfaces" your database and implements "IQuerySurface" (I couldn't think of a better name). In short, it exposes every table as IQueryable<T>, every Stored Procedure as an executable method, and also provides you with Aggregation queries (Count, Sum, Min, Max, etc).
- Implementation: Your Favorite Data Pattern. This can be Repository, ActiveRecord... whatever. I've created (with this release) an IRepository pattern for you that I think covers 99% of the needs out there (you'll see in a moment) and also allows for some nice testability.
Of these, the only thing that is locked down is part 1 - The Core (as part of our DLL). The rest is built using T4 templates that you add to your project. So if you hear me constantly say "you can change this as you need to in the templates" - well it's because you can. These aren't hard to learn, and are created using plain old C# code so changing/altering as you need should be easy. 
In fact I was explaining to Steve Harman yesterday that if you wanted to create a full-blown Unit Of Work ORM, you could. You could also create an ActiveRecord pattern too. Really - it's that flexible.
The "Surface"
There's probably a better name for this - but it seems to capture what I'm after: a simple, easy to understand way to work with your database. This template (called "QuerySurface" - which used to be "Provider") exposes the tables in your database as IQueryable<T> so you can work against them using Linq:
Northwind.DB db = new DB(); var result = from p in db.Products where p.CategoryID==5 select p; foreach (var item in result) { Console.WriteLine(item.ProductName); }
I can go off here and show you all the ways to query with Linq. But you've seen em before and hopefully you won't notice the difference with SubSonic. If you've used Linq To Sql, this will look extremely familiar to you. However know that my approach differs from Linq To Sql in that there is no change tracking and need to have a "context". The DB, in this sense, is simply a wrapper.
One thing you might not have seen with Linq To Sql is the ability to run Updates and Inserts, which I've always missed and have now implemented with SubSonic 3.0:
db.Update<Products>().Set(
x => x.Discontinued == false,
x => x.ReorderLevel == 100)
.Where(x=>x.Category==5)
.Execute(); db.Insert.Into<Region>(x => x.RegionID, x => x.RegionDescription)
.Values(6, "Hawaii")
.Execute();
I know a lot of people don't like the Lambda thing - you can still use our old query tool (with structs or strings in place of these lambdas) if you like.
This one's a personal favorite - deleting has become a one-line operation:
db.Delete<Region>(x => x.RegionID == 6).Execute();
This next thing is what I consider to be a "Killer Feature" - a batch query (or as NHibernate calls it, a "Future" query). One thing that always gets me about ORMs is having to manage the connections that it makes to a database. Wouldn't it be nice to lob a bunch of IQueryable queries into a batch querier and have it make only one call to the DB, in one execution step? If you think so, I have good news for you:
db.Queue(from p in db.Products where p.CategoryID == 5 select p); db.Queue(from p in db.Products where p.CategoryID == 7 select p); db.Queue(from p in db.Products where p.CategoryID == 9 select p); IDataReader rdr=db.ExecuteBatch();
With this release I've included a new "BatchQuery" class that takes either one of SubSonic's queries or an IQueryable query, parses the SQL and pulls out the parameters into one, big batched SQL statement. It then will execute as a pass-through, or return a multi-result set. Yes, I will do my best to support transactions this way in the future :).
If you don't like working with DataReaders, never fear, one of the new members of SubSonic's Extension classes (DatabaseExtensions) has a new method for IDataReader called "ToList<T>()" - which will take a reader and shove the results into a "List<T>".
So to complete this example (making sure to reference SubSonic.Extension.DatabaseExtensions):
db.Queue(from p in db.Products where p.CategoryID == 5 select p); db.Queue(from p in db.Products where p.CategoryID == 7 select p); db.Queue(from p in db.Products where p.CategoryID == 9 select p); List<Products> result1 = null; List<Products> result2 = null; List<Products> result3 = null; using (IDataReader rdr = qry.ExecuteReader()) { result1 = rdr.ToList<Products>(); if (rdr.NextResult()) result2 = rdr.ToList<Products>(); if (rdr.NextResult()) result3 = rdr.ToList<Products>(); }
One SQL statement, one execution step, 3 typed lists. I like it :).
Believe it or not you probably shouldn't work "this close" to your database. This is where the 3rd tier comes in.
The Implementation
Hopefully you can see what I'm trying to do here - give YOU the power. By way of example I've created an IRepository<T> interface that's part of SubSonic:
namespace SubSonic { public interface IRepository<T> { IQueryable<T> GetAll(); PagedList<T> GetPaged(int pageIndex, int pageSize); IQueryable<T> Find(Expression<Func<T, bool>> expression); void Add(T item); int Update(T item); int Delete(T item); int Delete(object key); int Delete(Expression<Func<T, bool>> expression); } }
One of the templates you'll see in the download below is "Repository.TT", which is an implementation of this interface that you can use right now if you like. One day I might make an ActiveRecord one as well - but for now you can use this code like this:
NorthwindRepository<Region> repo = new NorthwindRepository<Region>(); Region r = repo.Find(x => x.RegionDescription == "Hawaii").SingleOrDefault(); r.RegionDescription = "Hawaii FIVE O"; repo.Update(r);
Again, one of my very favorites is "Delete":
NorthwindRepository<Region> repo = new NorthwindRepository<Region>);
repo.Delete(x=>x.RegionID==6);
You can also use the item, or a primary key:
repo.Delete(6);
//OR
Region r=repo.Find(x=>x.RegionID==6).SingleOrDefault();
rept.Delete(r);
Let's Watch a Video!
A lot of people have asked for a "Rob special" video, and not to disappoint I've created one to show you how to get started (props to whomever knows the tune). It's 4 minutes and covers how to use SubSonic 3.0 (and its templates) with a Console App. You can also use this with any other app EXCEPT for a Web Site - for some reason T4 doesn't like those:
(Double-click for full screen, or you can download the WMV here).
The Download
You can download Preview 2 here. I've fixed/tweaked/changed a lot of stuff, including:
- Fixed an issue with Columns not generating. I think this was because I wasn't closing connections properly, and some databases were case-sensitive. I've tested this on 2008 and 2005 and it works well.
- Made every class Partial
- Changed tables from Fields to Properties
- Fixed a paging bug with the Linq translator
- Renamed some TT files. Provider has become "QuerySurface" and Connection.TT is now called "_Utility.tt".
- Renamed the no-code, required TT files to have underscore
- Implemented MANY of the changes suggested in this post by jguerts (thanks!)
As always- if you have any questions please let me know. If they're long ones, please email me at microsoft (robcon) or my full name at Gmail.
Cheers!
Once thing, ive noticed, not sure if you already aware of...
SPs (and I guess other DB elements) with - in their name, cause invalid method names to be generated..
This looks fantastic!
Are you any closer to releasing the source? I'm *really* keen to use this in our new project but reluctant to commit until it's either officially supported by someone or Open-Sourced
Thanks for your work on this!
It was up yesterday and gone today!!!!!!!!
Edit: HA! You got it :). Yep - well done. Great song, awesome album. I listened to it the whole time I was coding this stuff...
What about IQueryNinja, if u don't like the word Surface? It does do secret stuff, keeping things out of site while winning.
Keep the wort at a full boil Rob!
This looks really great. I tried out one of your early, early releases and liked it back then.
We're currently porting a cash-cow VB6 Windows application to Winforms. This is a product that makes our company a good chunk o' change. To date the work we've done has been using Linq to SQL (which we really liked). However, we've gotten a good punch in the gut with the recent developments around Linq to SQL and Entity Framework. It's pretty clear Microsoft will be "investing" lots in Entity Framework and not so much in Linq to SQL.
So, on to the question. Our initial impression of the Entity Framework is that it's a bit too "heavy" for our needs. We like Linq to SQL (as said). SubSonic seems more in line with what we like...and it supports multiple RDBMS systems. Obviously, you can't make the decision for us, but can you lay out some of the use-cases you see for SubSonic? Would a "product" (that's being sold) like ours benefit from it as it's ORM underpinning? Any insight you have would be great.
Is SubSonic going to be open-source (maybe it is already). Anyway, very cool stuff. Thanks.
3.0 soon.
In terms of insight- I can tell you what my aim is - you can make up your
mind RE other stuff :).
No ORM is a silver bullet. I don't care much for ORMs for this reason :).
What I like is simple data access that I can modify to my needs. What I've
learned from SubSonic 2.x and below is that convention is nice and saves
time, but if you have a locked-in system people always seem to find the
edges; it's just the way it is.
I know I'm being vague - but I think you can appreciate why :).
Now I have to go and exercise some code, can't wait
Your projects are great and real help to us mear mortals. I have looked at both Subsonic and Subsonic MVC.
Will you be updating the MVC plugin with new your new code or would you say that are in sync in terms of functionality now.
So no the generated works well with databinding I guess :-)
The next step is implementing child collections I gees that I can figure out how to do that in the templates. But how do I fill them?
The I have another question - Can I make projections so I can create my own "Views" by joining tables?
And when some of the dust have settled, then it would be nice to have you explaining how the magic works.
SubSonic could do that if a UnitOfWork set of templates was made...
Meh. Never mind. *slaps forehead* Typo in the ProviderName setting.
- concurrency, maybe the database independent way with an int being incremented for each write to the database
- Transactions
Wish list
- alternate table, column names
- inheritance
as they always have with SubSonic (I ported that over).
Alternate table/column names can be implemented by you in the templates.
Inheritance? Can you tell me more?
public IQueryable
{
var posts = from p in db.GetQuery
select p;
return posts;
}
but got a sql error that i does not know object post how can i make it work so know the table name is posts
the generated code is in namspace Subnus.MVC.Data.SubnusMVC if that helps
Can you give me a hint
>> Transactions work as they always have with SubSonic
I have never used SubSonic, can you give me code snippet with an update using transaction
>> Inheritance? Can you tell me more?
Yes of curse, I am working with an application that's mangeds antenna equipment and it would be nice to have a equipment base class that every equipment class implements and specific classes for each equipment type. Single table inheritance would be ok :-)
2. Add
Also, I'd be up for helping you modify the MVC addin to work with subsonic v.3. Personally, I see more value in going that approach to running the T4 templates than simply having them generate code for everything in your DB when you save one of the files. So following a Rails approach ... you define the model you want to create in your gui, hit run and it builds a migration file, your partial class, your iqueryable goodness and optional a controller and view.
Anyways, I'm rambling now so I'll end it here
public IQueryable
{
var posts = from p in db.Posts
select new Post
{
PostId = p.PostId,
Body = p.body,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
Guid = p.Guid,
IsPublic = p.IsPublic,
IsDeleted = p.IsDeleted
};
return posts;
}
I thing I need to reed the book on my shelf "Linq in action" :)
the sql I am trying to create is
select cat.CategoryId ,cat.Name
from SubnusMVC.Categories as cat
join SubnusMVC.Post_Category_Map as pcm on cat.CategoryId = pcm.CategoryId
where pcm.Postid =1
var categories = from cat in db.Categories
select new Subnus.MVC.Data.SubnusMVC.Categories
{
Name = cat.Name,
CategoryId = cat.CategoryId
};
var postCategoryMap = from pcm in db.Post_Category_Map
select new Subnus.MVC.Data.SubnusMVC.Post_Category_Map
{
CategoryId = pcm.CategoryId,
PostId = pcm.PostId
};
var result = from category in categories
join postcat in postCategoryMap on category.CategoryId equals postcat.CategoryId
where postcat.PostId == postId
orderby category.Name
select new
{
CategoryId = category.CategoryId,
Name = category.Name
};
foreach (var item in result)
{
System.Diagnostics.Debug.Write(item.Name);
}
but i get a error the forach loop
do you have links to where i can learn linq
My only issue (and a small one at that) is the templates generate C#. Are you planning on implementing VB versions of the templates?
In the meantime I have just converted the templates to generate VB.NET.
I had no major issues, though for some reason the extension methods for ToDictionary() and ToConstraintList() didn't want to show saying "T" didn't doesn't implement the methods. I've had issues with extensions methods before where they didn't seem to want to make themselves available to the objects they supposedly extend but I can't remember how I fixed it.
As a work around I just called the corresponding extension method via its static class. It seems to work or at least build for now. Next step is to build the rest of the application around it.
Multiple template directives were found in the template. All but the first one will be ignored. Multiple parameters to the template directive should be specified within one template directive.
Any help would be appreciated.
static void Main(string[] args)
{
var db = new DB();
var query = from i in db.INCIDENTS
where i.RECEIVED_DT > DateTime.Now.AddYears(-1)
select i;
foreach (var item in query) { //bombs here on query object
Console.WriteLine(item.INCIDENT_TYPE);
}
}
This query fails with...
Unhandled Exception: System.NullReferenceException: Object reference not set to
an instance of an object.
at SubSonic.Linq.QueryBinder.VisitMemberAccess(MemberExpression m)
at SubSonic.Linq.ExpressionVisitor.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.Visit(Expression exp)
at SubSonic.Linq.ExpressionVisitor.VisitMethodCall(MethodCallExpression m)
at SubSonic.Linq.QueryBinder.VisitMethodCall(MethodCallExpression m)
at SubSonic.Linq.ExpressionVisitor.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.Visit(Expression exp)
at SubSonic.Linq.ExpressionVisitor.VisitBinary(BinaryExpression b)
at SubSonic.Linq.ExpressionVisitor.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.BindWhere(Type resultType, Expression source, La
mbdaExpression predicate)
at SubSonic.Linq.QueryBinder.VisitMethodCall(MethodCallExpression m)
at SubSonic.Linq.ExpressionVisitor.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.Visit(Expression exp)
at SubSonic.Linq.QueryBinder.Bind()
at SubSonic.Linq.DbQueryProvider.Translate(Expression expression)
at SubSonic.Linq.DbQueryProvider.Execute(Expression expression)
at SubSonic.Linq.Query`1.GetEnumerator()
at SubSonicTest.Program.Main(String[] args) in C:\Temp\SubSonicTest\SubSonicT
est\Program.cs:line 19
If I change the query's where to a non-date related comparison, things work fine. This is my own database here. Just wondering if I'm doing something wrong or missing something.
int albumid = 1;
var db = new Chinook.DB();
var query = from a in db.Album
where a.AlbumId == albumid
select a;
foreach (var item in query)
{
Console.WriteLine(item.Title);
}
Console.ReadLine();
does not work but
var db = new Chinook.DB();
var query = from a in db.Album
where a.AlbumId == 1
select a;
foreach (var item in query)
{
Console.WriteLine(item.Title);
}
Console.ReadLine();
[Test]
[Description("Selects products where categoryId==5")]
public void TestSimpleQueryQuery()
{
var db = new DB();
var result = from p in db.Products where (p.CategoryID == 5) select p;
foreach (var products in result)
Console.WriteLine(products.ProductName);
Assert.Greater(result.Count(), 1);
}
[Test]
[Description("Selects products where categoryId==5")]
public void TestSimpleQueryQueryWithIntParameter()
{
const int catId = 5;
var db = new DB();
var result = from p in db.Products where (p.CategoryID==catId) select p;
foreach (var products in result)
Console.WriteLine(products.ProductName);
Assert.Greater(result.Count(), 1);
}
[Test]
public void TestSimpleRepositoryQuery()
{
var repo = new NorthwindRepository
var result = repo.Find(x => x.CategoryID == 5);
foreach (var products in result)
Console.WriteLine(products.ProductName);
Assert.Greater(result.Count(),1);
}
[Test]
public void TestSimpleRepositoryQueryWithIntParameter()
{
const int catId = 5;
var repo = new NorthwindRepository
var result = repo.Find(x => x.CategoryID == catId);
foreach (var products in result)
Console.WriteLine(products.ProductName);
Assert.Greater(result.Count(), 1);
}
Great work, many thanks for the preview. Cant wait for the Beta.
Red
class Program
{
static void Main(string[] args)
{
var result = GetAlbumById(1);
foreach (var item in result)
{
Console.WriteLine(item.Title);
}
}
public static IQueryable
{
var db = new DB();
var query = from a in db.Album
where a.AlbumId == id
select a;
return query;
}
} does not work i get at error in the foreach loop because it is at method i cant make the id a const
this is with the new tast database Chinook http://blog.wekeroad.com/blog/subsonic-3-0-repository-template-update/
Are there plans to update the query engine to use parameterized queries so the server makes use of cached execution plans?
T4 templates also do not work in a Smart Device project because the Compact Framework does not include the System.CodeDom namespace. But I found a way to make them work if you or your readers are interested: ( http://www.agilification.com/post/T4-Templates-For-The-NET-Compact-Framework.aspx ).
--Jeff
wouldn't show u that. What do you mean?
Sent from my phone. Please excuse brief replies.
Linq2Sql:
exec sp_executesql N'SELECT [t0].[ProjectId], [t0].[Name]
FROM [dbo].[Project] AS [t0]
WHERE [t0].[ProjectId] = @p0',N'@p0 int',@p0=1
Subsonic:
SELECT t0.Name, t0.ProjectId
FROM Project AS t0
WHERE (t0.ProjectId = 1)
just watched the video (thanks! can't play with subsonic right now). I have 2 questions:
1. When you were showing System.Data.Common in action, e.g. changing the datastore from MS SQL to MySql database, you haven't changed values in settings.tt (you changed them only in app.config file).
ConnectionString and ProviderName values thus stayed unchanged, and it worked?
2. Why is the ProviderName value in settings.tt set to = "Northwind"? Shouldn't it be set to System.Data.SqlClient?
Now I am thinking if settings.tt file isn't redundant, or if it can't possibly read the settings from web/app.config file (is pure xml afterall).
thanks
duh :). I'm wondering if this is the way DbCommands are created versus
SqlCommands. Since SqlCommands are platform-specific they can construct
parameterized queries - DbCommands, however, are generic so perhaps the
execution is different. This is interesting...
This morning I tested with Preview 2. The repo.Find call and the Linq syntax I was using with Preview 1 now show up in the trace as a parameterized query...it must have just been Preview 1. I did notice though that the repo.GetAll call is showing up in the trace with the two events, BatchStarting and BatchComplete. The parameterized queries are showing up as RPC:Completed events.
I'm not sure if the Batch mode has something to do with it not being parameterized in Preview 1 but that's the only difference I see in the trace between Preview 1 and Preview 2.
repo.GetAll:
SELECT t0.Address, t0.City, t0.Company, t0.Country, t0.CustomerId, t0.Email, t0.Fax, t0.FirstName,
t0.LastName, t0.Phone, t0.PostalCode, t0.State
FROM Customer AS t0
repo.Find:
exec sp_executesql N'SELECT t0.Address, t0.City, t0.Company, t0.Country, t0.CustomerId, t0.Email, t0.Fax,
t0.FirstName, t0.LastName, t0.Phone, t0.PostalCode, t0.State
FROM Customer AS t0
WHERE (t0.CustomerId > @p0)',N'@p0 int',@p0=1
For example, I have added List
[SqlException (0x80131904): Invalid column name 'Permissions'.
Invalid column name 'Topics'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry) +228
SubSonic.Linq.DbQueryProvider.Execute(String commandText, String[] paramNames, Object[] paramValues, Func`2 fnRead) +233
SubSonic.Linq.DbQueryProvider.Execute(Expression expression) +1850
SubSonic.Linq.Query`1.GetEnumerator() +69
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +7663302
System.Linq.Enumerable.ToList(IEnumerable`1 source) +61
test.DataServices.SubSonicDataService.ListCategories()
Hope that makes sense,
Jesse
Using your preview, I'm doing a simple query and I'm getting an exception, something about being unable to map the parameter in the where clause (for which the compiler generates a special type MediaService+<>c__DisplayClass0).
public MediaAsset GetMediaAsset(int id)
{
return_mediaRepository.GetMediaAsset()
.Where(x => x.IDMediaAsset == id)
.SingleOrDefault();
}
Saludos!!!
I'm getting this.
ArgumentException: No hay ninguna asignación de tipo de objeto Sherezade.Services.MediaService+<>c__DisplayClass0 a un tipo nativo de un proveedor administrado conocido.]
System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen) +1917459
System.Data.SqlClient.SqlParameter.GetMetaTypeOnly() +4870773
System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +17
System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +203
System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +237
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry) +228
SubSonic.Linq.DbQueryProvider.Execute(String commandText, String[] paramNames, Object[] paramValues, Func`2 fnRead) +233
SubSonic.Linq.DbQueryProvider.Execute(Expression expression) +1850
SubSonic.Linq.QueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +72
System.Linq.Queryable.SingleOrDefault(IQueryable`1 source) +269
Sherezade.Services.MediaService.GetMediaAsset(Int32 id) in D:\Proyectos\Web\Sherezade\Sherezade.Services\Media\MediaService.cs:42
Sherezade.Web.Controllers.MediaController.Get(Int32 id, Nullable`1 width, Nullable`1 height) in D:\Proyectos\Web\Sherezade\Sherezade.Web\App\Controllers\MediaController.cs:96
lambda_method(ExecutionScope , ControllerBase , Object[] ) +224
System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +51
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(MethodInfo methodInfo, IDictionary`2 parameters) +568
System.Web.Mvc.<>c__DisplayClassc.
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +305
System.Web.Mvc.<>c__DisplayClasse.
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +305
System.Web.Mvc.<>c__DisplayClasse.
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(MethodInfo methodInfo, IDictionary`2 parameters, IList`1 filters) +455
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +736
System.Web.Mvc.Controller.ExecuteCore() +180
System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +96
System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +36
System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +377
System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +71
System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +36
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75
drop
public partial class file_type {
public int file_type_id { get; set; }
public string file_type { get; set; } // error here: "Member names cannot be the same as their enclosing type
}
Otherwise, very cool stuff. Hat's off!
And do you have some kind of road map for this very cool spike?
string CleanUp(string tableName){
string result=tableName;
//strip blanks etc...
result=result.Replace(" ","_")
.Replace("$","")
.Replace("-","_")
.Replace("/","")
.Replace(@"\","")
.Replace("'","");
if(NumberExtensions.IsNumber(tableName.Substring(0, 1)))
{
result="_"+result;
}
return result;
}
string PrefixName(string objectName)
{
return "_" + objectName;
}
string KeywordCleanUp(string objectName)
{
switch (objectName)
{
case "abstract":
return PrefixName(objectName);
break;
case "event":
return PrefixName(objectName);
break;
case "new":
return PrefixName(objectName);
break;
case "struct":
return PrefixName(objectName);
break;
case "as":
return PrefixName(objectName);
break;
case "explicit":
return PrefixName(objectName);
break;
case "null":
return PrefixName(objectName);
break;
case "switch":
return PrefixName(objectName);
break;
case "base":
return PrefixName(objectName);
break;
case "extern":
return PrefixName(objectName);
break;
case "object":
return PrefixName(objectName);
break;
case "this":
return PrefixName(objectName);
break;
case "bool":
return PrefixName(objectName);
break;
case "false":
return PrefixName(objectName);
break;
case "operator":
return PrefixName(objectName);
break;
case "throw":
return PrefixName(objectName);
break;
case "break":
return PrefixName(objectName);
break;
case "finally":
return PrefixName(objectName);
break;
case "out":
return PrefixName(objectName);
break;
case "true":
return PrefixName(objectName);
break;
case "byte":
return PrefixName(objectName);
break;
case "fixed":
return PrefixName(objectName);
break;
case "override":
return PrefixName(objectName);
break;
case "try":
return PrefixName(objectName);
break;
case "case":
return PrefixName(objectName);
break;
case "float":
return PrefixName(objectName);
break;
case "params":
return PrefixName(objectName);
break;
case "typeof":
return PrefixName(objectName);
break;
case "catch":
return PrefixName(objectName);
break;
case "for":
return PrefixName(objectName);
break;
case "private":
return PrefixName(objectName);
break;
case "uint":
return PrefixName(objectName);
break;
case "char":
return PrefixName(objectName);
break;
case "foreach":
return PrefixName(objectName);
break;
case "protected":
return PrefixName(objectName);
break;
case "ulong":
return PrefixName(objectName);
break;
case "checked":
return PrefixName(objectName);
break;
case "goto":
return PrefixName(objectName);
break;
case "public":
return PrefixName(objectName);
break;
case "unchecked":
return PrefixName(objectName);
break;
case "class":
return PrefixName(objectName);
break;
case "if":
return PrefixName(objectName);
break;
case "readonly":
return PrefixName(objectName);
break;
case "unsafe":
return PrefixName(objectName);
break;
case "const":
return PrefixName(objectName);
break;
case "implicit":
return PrefixName(objectName);
break;
case "ref":
return PrefixName(objectName);
break;
case "case ":
return PrefixName(objectName);
break;
case "ushort":
return PrefixName(objectName);
break;
case "continue":
return PrefixName(objectName);
break;
case "in":
return PrefixName(objectName);
break;
case "return":
return PrefixName(objectName);
break;
case "using":
return PrefixName(objectName);
break;
case "decimal":
return PrefixName(objectName);
break;
case "int":
return PrefixName(objectName);
break;
case "sbyte":
return PrefixName(objectName);
break;
case "virtual":
return PrefixName(objectName);
break;
case "default":
return PrefixName(objectName);
break;
case "interface":
return PrefixName(objectName);
break;
case "sealed":
return PrefixName(objectName);
break;
case "volatile":
return PrefixName(objectName);
break;
case "delegate":
return PrefixName(objectName);
break;
case "internal":
return PrefixName(objectName);
break;
case "short":
return PrefixName(objectName);
break;
case "void":
return PrefixName(objectName);
break;
case "do":
return PrefixName(objectName);
break;
case "is":
return PrefixName(objectName);
break;
case "sizeof":
return PrefixName(objectName);
break;
case "while":
return PrefixName(objectName);
break;
case "double":
return PrefixName(objectName);
break;
case "lock":
return PrefixName(objectName);
break;
case "stackalloc":
return PrefixName(objectName);
break;
case "else":
return PrefixName(objectName);
break;
case "long":
return PrefixName(objectName);
break;
case "static":
return PrefixName(objectName);
break;
case "enum":
return PrefixName(objectName);
break;
case "namespace":
return PrefixName(objectName);
break;
case "string":
return PrefixName(objectName);
break;
case "from":
return PrefixName(objectName);
break;
case "get":
return PrefixName(objectName);
break;
case "group":
return PrefixName(objectName);
break;
case "into":
return PrefixName(objectName);
break;
case "join":
return PrefixName(objectName);
break;
case "let":
return PrefixName(objectName);
break;
case "orderby":
return PrefixName(objectName);
break;
case "partial":
return PrefixName(objectName);
break;
case "select":
return PrefixName(objectName);
break;
case "set":
return PrefixName(objectName);
break;
case "value":
return PrefixName(objectName);
break;
case "var":
return PrefixName(objectName);
break;
case "where":
return PrefixName(objectName);
break;
case "yield":
return PrefixName(objectName);
break;
default:
return objectName;
}
}
Wouldn't you mind to update the comparison or O/R-M tools here:
http://dnagir.blogspot.com/2008/11/orm-requirements-analysis.html
Maybe some info on Subsonic.
Thanks.
Loving this preview, I already started playing with it. Got a couple of questions though. Forgive me if my questions seem stupid.
1. Will the linq stuff support linq queries such as 'from p in db.Products where p.Category == "blue" '?
2. I want my generated to have inheritance and relationships, so i'll be customizing the templates to accomplish that, I think I got an idea on how to do this, but I wanted to know if the Linq provider can handle this. For example, let's say if I have a class Customer that has a collection of Products as one of its properties which represent the relationship. Will the Linq provider be able to generate the query with the right joins? Secondly, on insert will it be able to generate the inserts for the two different tables (customers and products) with the proper keys?
3. What's the title of the song you're playing on that video? I've watched the video a couple i think that song is growing on me :).
Thanks
Quick question, is this supposed to work w/ Oracle (or any other db) yet? I see in the video you just added a reference to MySQL client library, changed your connection string and got it running right away. I tried swapping out the conn string to my Oracle DB and got no such love. Looking into _Utility.tt, it looks like you've got SqlConnection, SqlCommand, etc hard coded in there instead of DbConnection, DbCommand, etc. So my guess is that the download only works against SqlServer and the video is previewing the ability to swap dbs on the fly???
Great work as always. I really like where this is going.
1) I changed the connection string, but I also changed the provider. I'm
pretty sure Oracle supports the Data Factory - but no I haven't tested it
yet.
2) In your code you can use whichever provider you want since all it needs
is the schema - I'll try and change this (it's in the queue) going forward.
1) I did change the provider as well (by "connection string" I meant connection string entry in the configuration file - always say what you mean, right?). Anyway, here's what I have:
In App.config:
In _Settings.tt:
const string ConnectionString=@"Data Source=bar;Persist Security Info=True;User ID=foo;Password=foobar;";
const string Namespace = "Foo";
const string ProviderName="Foo";
string[] ExcludeTables= new string[]{"sysdiagrams","SubSonicSchemaInfo"};
When I build I get a transformation error on classes.tt:
Running transformation: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.VisualStudio.TextTemplating116F6562DC44BE6ABBCF1D25C0C5BF9E.GeneratedTextTransformation.GetConnection() in c:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\_Utility.tt:line 19
at Microsoft.VisualStudio.TextTemplating116F6562DC44BE6ABBCF1D25C0C5BF9E.GeneratedTextTransformation.GetSchema(String meta, String[] restrictions) in c:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\_Utility.tt:line 47
at Microsoft.VisualStudio.TextTemplating116F6562DC44BE6ABBCF1D25C0C5BF9E.GeneratedTextTransformation.GetSchema(String meta) in c:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\_Utility.tt:line 43
at Microsoft.VisualStudio.TextTemplating116F6562DC44BE6ABBCF1D25C0C5BF9E.GeneratedTextTransformation.GetTables() in c:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\_Utility.tt:line 40
at Microsoft.VisualStudio.TextTemplating116F6562DC44BE6ABBCF1D25C0C5BF9E.GeneratedTextTransformation.TransformText() in c:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\Classes.tt:line 8 C:\Documents and Settings\twayso\My Documents\Visual Studio 2008\Projects\subsonic_3_p2_test\_Generated\Classes.tt 1 1
2) I did start trying modifying the the _Utility.tt code to use the OracleClient to get the schema info, but I was getting error after error so I gave up. After seeing your reply though, I think I'll try changing it to use the Data.Common objects and get it to work. I'll let you know what I come up with.
Happy T-day!
Also it seems that the StoredProcedure.ExecuteScalar
const string DataProvider = "System.Data.OracleClient";
Then I modified the _Utility.tt to use the DataFactory class based on the DataProvider as shown below. This works but gives trouble with the schema retrieval. I will have to look at it again. Also the remaining code, mapping datatypes to from C# to SQL and retrieval of foreign keys, is hardcoded to MS SQL so that will need to be tweaked. Here is my modified _Utility.tt :
<#@ include file="_Settings.tt" #>
<#+
string CleanUp(string tableName){
string result=tableName;
//strip blanks
result=result.Replace(" ","_");
//put your logic here...
return result;
}
DbProviderFactory GetFactory(){
return DbProviderFactories.GetFactory(DataProvider);
}
DbConnection GetConnection(){
string connectionString=ConnectionString;
DbConnection conn= GetFactory().CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();
return conn;
}
string GetPK(string table){
string[] restrictions = new string[4] { ProviderName, null, table, null };
DataTable tbl=GetSchema("IndexColumns",restrictions);;
string pk="";
foreach(DataRow dr in tbl.Rows){
string index=dr["constraint_name"].ToString();
if(index.StartsWith("PK_")){
pk=dr["COLUMN_NAME"].ToString();
break;
}
}
return pk;
}
DataTable GetColumns(string table){
string[] restrictions = new string[4] { ProviderName, null, table, null };
return GetSchema("COLUMNS",restrictions);
}
DataTable GetTables(){
return GetSchema("tables");
}
DataTable GetSchema(string meta){
return GetSchema(meta, new string[4]);
}
DataTable GetSchema(string meta, string[] restrictions){
DataTable tbl=null;
using(DbConnection conn=GetConnection()){
tbl= conn.GetSchema(meta, restrictions);
}
return tbl;
}
DataTable GetSPs(){
return GetSchema("Procedures");
}
DataTable GetFKs(string tableName){
DataTable tbl=new DataTable();
using(DbConnection conn = GetConnection()){
DbCommand cmd= GetFactory().CreateCommand();
cmd.CommandText = FKSql;
cmd.Connection = conn;
DbParameter parm = GetFactory().CreateParameter();
parm.ParameterName = "@tableName";
parm.Value = tableName;
cmd.Parameters.Add(parm);
DbDataAdapter da = GetFactory().CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(tbl);
}
return tbl;
}
DataTable GetSPParams(string spName){
string[] restrictions = new string[4] { ProviderName, null, spName, null };
return GetSchema("ProcedureParameters", restrictions);
}
------- SNIP the remaining code ----
SELECT t0.field1, t0.field2 FROM tablename AS t0
In Oracle this is not valid because in that sql the "AS" is considered the table alias.
Is there some way we can override the sql statement generation or will we have to wait for the next preview?
Anyway, great work Rob.
Sent from my phone. Please excuse brief replies.
It just seems that the majority of all bugs that I run into with Subsonic are bugs that only happen in Databases other than SQL Server.
"No mapping exists from object type ... to a known managed provider native type."
e.g. this works fine:
var query = repo.Find(x => x.ProductID == 1).SingleOrDefault();
this throws the execption:
int id = 1;
var query = repo.Find(x => x.ProductID == id).SingleOrDefault();
As said, it also throws the exception using the DB class e.g.
string firstName = "Nancy";
var db = new DB();
var result = (from e in db.Employees where e.EmployeeID == criteria.Value select e).SingleOrDefault();
I'm using VS 2008 Professional, SQL Server 2005 Express with the Northwind database from CodePlex. It is a C# console app with nothing other than the SubSonic assembly referece. Any ideas?
Dan.
string firstName = "Nancy";
var db = new DB();
var result = (from e in db.Employees where e.FirstName == firstName select e).SingleOrDefault();
and i doesent work, infact its plain wierd:
i get this error:
the type of namespace DB could not be found (repository,cs) LIne 26 column 23
but what the strange thing is is that
namespace Northwind {
///
/// A Repository class which wraps the Northwind Database
///
public class NorthwindRepository
IQuerySurface _db;
public NorthwindRepository(IQuerySurface db) {
_db = db;
}
public NorthwindRepository() {
_db = new DB();
}
is the filem, but im not using northwind, this is all rubbish if it will only ever work with a namespace called northwind.
i have setup my app.config and setting.tt to both say mysqltest as the namespace........... so anyone please...... i was perfectly happy with subsonic 2 except it never did stored procedures, now i am worse off cos not only do i not have stored procedures still, but i dont even have the DB code anymore.
help much appreciated
Cheers
Our state's agencies prefer to use Oracle. Would you let me know who is working on the TT templates for Subsonic with the Oracle provider?
Great work on version 3. I hope you're getting paid somehow to maintain it! :)
So many thankss....