SubSonic 3 Alpha Updated
Many thanks to the folks who downloaded and ran the alpha. As I figured, I missed a few things. The good news is that I've fixed them, and 99% of them were with the templates - which is good news because it means that you could have fixed them if you had to :).
But first - I'd like to say a big HELLO! to all the folks at CodeBetter, because as of today, I will be cross-posting my blog there. Indeed it's quite an honor and many thanks to Jeremy Miller for inviting me!
Here's the updated list of bits:
- Fixed an SP bug that would result in the command type not getting set properly
- Restructured and re-organized the namespacing
- Renamed the DLL to SubSonic.Core, rather than SubSonic (forgot to do that earlier)
- Fixed up the _Settings.tt template to more accurately convey just WTF it is you're setting
- Fixed a foreign-key naming bug which allowed spaces through
- Fixed a bug with self-referencing tables, wherein a child property could wiggle through and end up having the same name as the parent
- Fixed a commenting issue in the Repositories, which VS didn't like
You can download the source, if you like, or grab the download here (DLL plus templates). If you have a question - you can leave a note here or (preferably) ask it here, on our mailing list.
Setup Walkthrough
I was asked on the comments of my last post for a walkthrough - so here it is. Setting up SubSonic 3:
1) Download the bits.
2) Open Zip file, and put on your hard drive somewhere
3) Create a project and add an app/web.config file
4) Add a VALID connection string to your favorite DB
5) Edit the _Settings.tt file, letting it know the name of your connection string and whatever Namespace you want for your generated objects:
6) Drag the _Generated file into your project - put it where you like
7) You're done. Go grab a soda, then start coding.
If you want more information about how to use what's generated for you, read more here and also read up here.
Many thanks to the folks testing!
I am working about to start working on a project and i'm wondering how you will set up the project stucture using Domain-Driven Design.
Thanks
Error 2 Running transformation: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 78.
at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Microsoft.VisualStudio.TextTemplating3FC41802D2E2344020AEBC538CDDAA8D.GeneratedTextTransformation.GetConnection() in d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\_Utility.tt:line 18
at Microsoft.VisualStudio.TextTemplating3FC41802D2E2344020AEBC538CDDAA8D.GeneratedTextTransformation.GetSchema(String meta, String[] restrictions) in d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\_Utility.tt:line 70
at Microsoft.VisualStudio.TextTemplating3FC41802D2E2344020AEBC538CDDAA8D.GeneratedTextTransformation.GetSchema(String meta) in d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\_Utility.tt:line 66
at Microsoft.VisualStudio.TextTemplating3FC41802D2E2344020AEBC538CDDAA8D.GeneratedTextTransformation.GetTables() in d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\_Utility.tt:line 63
at Microsoft.VisualStudio.TextTemplating3FC41802D2E2344020AEBC538CDDAA8D.GeneratedTextTransformation.TransformText() in d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\Structs.tt:line 3 d:\dev\projects\sandbox\trunk\SubSonicMvcApplication1\SubSonicMvcApplication1\Models\_Generated\Structs.tt 1 1
I am trying to use it for my project, but some problems appear, a new release would be helpfull, especially for newcomers like me :)
1. Rev 18 from SVN still contains problematic code regarding Foreign Keys - it creates a class with a fields mapped to related objects, but not checking for null when adding new objects to the table (mentioned earlier by aherrick); just need null checking
2. Cannot insert any new object into db tables. The message is: Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.
Each of my tables has PK of bigint with identity and autonumber set. I don't know what is going on.?? When debugging, SS3 tries to put value of '0' there, but it shouldn't - these fields are autonumbers.
3. Some of my tables has datetime fields with null-allowed property set.That means, when I have no date to put in, I put null (in MS SQL it is DBNull); In this case SS3 tries to insert date of 0001-01-01 which is generally not acceptable by db. How to correct this? I just want to put null (DBNull) there if I have no specific date. From .NET 2.0 we have nullable types which is perfect, why loose that?
Thank you for your reply and help.
TK
7) You're done. Go grab a BEER, then start coding
know the great DNN secret :)
best regards,
christian
Thanks for the new releash.
I noticed a change in this version comparison to preview 2.
In preview 2, I can delete the whole schema region in my DB class. My application code
-------------------------------------------------------------------------
DB db = new DB();
Console.WriteLine(db.Products.Count());
--------------------------------------------------------------------------
still works. Now if I remove the schema region, and run the similar code
--------------------------------------------------------------------------
DB db = DB.CreateDB();
Console.WriteLine(db.Products.Count());
---------------------------------------------------------------------------
I got an exception: Object reference not set to an instance of an object, at Subsonic.DataProviders.DbDataProvider.QualifyTableName(ITable tbl)
I notice in your new version, the entity classes become more complex
than the ones in the previous version. They are now similar to the classes
in LINQ to SQL, so I assume your schema section is similar to LINQ to
SQL's attributes on the entity classes. I wish you also support the attribute
approach.
needed to qualify the table name) it will indeed break.
No, the schema bits I'm using aren't like Linq To Sql's - they're sort of
similar to what we did before. It's just meta data for your DB which helps
us to write a better query.
In terms of attributes - I usually don't like that approach as it ties the
core classes to the implementation. Also I don't see the benefit of it
immediately.
Just out of curiousity how do you see SubSonic in relation to Linq2SQL? I see two main improvements in SubSonic:
1. Multiple provider support (Oracle, MySQL etc.)
2. Easier customization using T4 templates
Anything else?
Great work~ I love your tools :-). When do you expect a release of the final product?
mDLish~
Usually I am reading your blog as a purely technical reference to something which might be useful, but the moments like this puts me back to reality that you are the same human like most of us...fuck that :)
which makes me feel really good, that I am dealing with a breathing person as well as the project (that's why you call it baby) and not some sturdy, non-responsive, too technical and retarder Bender copy which needs to get out ASAP to satisfy some remaining fans of Futurama...(an analogy to Entity Framework).
Thanks for the Alpha bits, going to play with it (wish I haven't read Oren's post about Fluent NHibernate's mapping lately, I would have more time =)
Running transformation: System.ArgumentException: The URL cannot be empty.
Parameter name: url
at System.Xml.XmlTextReaderImpl..ctor(String url, XmlNameTable nt)
at System.Xml.XmlTextReader..ctor(String url, XmlNameTable nt)
at System.Xml.XmlDocument.Load(String filename)
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.GetConfigSetting(String key) in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Settings.tt:line 64
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.get_ConnectionString() in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Settings.tt:line 36
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.GetConnection() in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Utility.tt:line 17
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.GetSchema(String meta, String[] restrictions) in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Utility.tt:line 70
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.GetSchema(String meta) in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Utility.tt:line 66
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.GetTables() in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\_Utility.tt:line 63
at Microsoft.VisualStudio.TextTemplating87C37A978FF42465FE7868BAC7C9BE84.GeneratedTextTransformation.TransformText() in c:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\Structs.tt:line 3 C:\dev\projects\SubSonic3Preview\SubSonic3Preview\Models\_Generated\Structs.tt 1 1
I have one question... For example,
NorthwindRepository
NorthwindRepository
Customers cust = customers.Find(c => c.CustomerID == "ALFKI").SingleOrDefault();
Orders ord = orders.Find(o => o.CustomerID == cust.CustomerID).First();
In this scenario "cust" doesn't have a Orders collection (or IQueryable
And one more question... May be it should be better to use single notation for tables: "Customer" instead of "Customers"? Or you suggest to use my POCO objects but not autogenereted ones?
added subsonic reference. added connection string, no go.
I am guessing but I think you need to fill out the provider field on the connectionstring so that SubSonic can decide which data provider to instantiate.
Hope this helps, I haven't tried it myself yet (I have the same problem) :)
The GetConfigSetting method is calling "GetConfigPath()", which in turn is only looking at "App.config" to get its info.
public string GetConfigPath(){
EnvDTE.Project project = GetCurrentProject();
foreach(EnvDTE.ProjectItem item in project.ProjectItems)
{
// if it is the configuration, then open it up
if(string.Compare(item.Name, "App.config", true) == 0)
{
System.IO.FileInfo info =
new System.IO.FileInfo(project.FullName);
return info.Directory.FullName + "\\" + item.Name;
}
}
return "";
}
I changed on line 77 ("_Settings.tt" file) the Compare method to "Web.config" and everything generated as expected. I would suggest modifying the template to have logic to check either App.config or Web.config depending on what project you are building.
Here is what I have currently using the Chinook DB.
SubSonicTestRepository
Album album = new Album();
album.Title = "Test01";
album.ArtistId = 1;
repo.Add(album);
wrong idea?
The GetPK method in the _Utility.tt file expects primary keys to have their name prefixed with "PK_". Oracle likes to postfix "_PK" so if you get PK violations on inserts then maybe you need to change the template or the names of you PKs.
other providers as I get the time/help.
IColumn col = tbl.GetColumn(key);
col gets set to null (so null reference is thrown on line 93)
any thoughts? -- Thanks
here is a screen cap of the error
http://ajondeck.net/images/subsonic_null.jpg
"break" and then hover your mouse over the variable "col". This should tell
you the name that SubSonic is trying to find. If you don't have that column
in your DB, can you tell me what the name is?
My problem was not a column, but rather the new foreign key properties of the class template. In line 89 of andrew's screenshot the properties of the table object are converted to a dictionary. The new foreign key properties are appearing in the dictionary and GetColumn is returning a null for the foreign key property.
As a temporary workaround I changed my class template to make the foreign keys to be methods instead of properties to avoid having the relational properties appearing the dictionary.
http://ajondeck.net/images/subsonic_null2.jpg
Also - interesting stuff on hanselminutes the other day regarding DDD
However take a look at what is happening below. When subsonic looping through the columns in the dictionary, it is not recognizing the primary key.
http://ajondeck.net/images/subsonic_primarykeyerror.jpg
Hope this is helping --
param's not null. You can add this yourself if you want; I'll add it in now.
Will there be any extensibility points for mapping providers other than your ImplicitMapping? I wrote a very basic XmlMapping, inheriting from QueryMapping, but it's not clear how I would be able to plug it into the query surface/DB class to get it operational. QueryPolicy takes a QueryMapping in its constructor, but QueryPolicy is implicitly declared in the constructor of DbQueryProvider without any provisioning for a specific implementation of QueryPolicy and/or QueryMapping. To make matters worse, there is no 'set' on the Policy property on DbQueryProvider. In fact in the methods of DbQueryProvider, the local private field 'policy' is used instead of the property so even if it had a setter and was virtual, it would be of no value. From what I remember from your storefront episode on dependency injection, this is kind of a bad way to do things, eh?
Not trying to dump, just throwing my observations out there. I don't think a different mapping scheme is that much of an edge case.
I had a problem with a database that is not Northwind or Chinook. My SQL Server database has tables that are not owned by dbo and writing operations were failing as they were being scoped to "dbo". I have created a template fix that I thought I would share. The following changes got me going:
In the QuerySurface.TT I added/ the lines prefaced with the ->
#region Schemas
<#foreach(DataRow drSchema in tbl.Rows){
string tableName=drSchema[2].ToString().Trim();
string fixedName=tableName.Replace(" ","_");
if(!ExcludeTables.Contains(tableName) && drSchema[3].ToString()=="BASE TABLE") {
+-> string schemaOwner = drSchema["TABLE_SCHEMA"].ToString().Trim();
#>
//********************<#=tableName#>********************
--> ITable <#=fixedName#>Schema=new DatabaseTable("<#=schemaOwner#>", "<#=tableName#>",DataProvider);
This fixed everything except the update because it is using the DatabaseTable constructor that injects a "dbo" schema. I changed to the Subsonic.Core SubSonic.Query.Update
public Update(IDataProvider provider)
{
_query = new SqlQuery(provider);
_provider = provider;
_query.QueryCommandType = QueryType.Update;
+-> ITable tbl = provider.FindTable(typeof(T).Name);
--> _query.FromTables.Add(new DatabaseTable(tbl.SchemaName, typeof(T).Name, _provider));
}
select * from Customers join Orders on Orders.CustomerID = Customers.CustomerID where Customers.CustomerID = 'ALFKI'
it's on my mind :)
Northwind.DB _db=new Northwind.DB();
var result=from p in db.Products
join c in _db.Categories on p.CategoryID equals c.CategoryID
select new {c.CategoryName, p.ProductName};
Take a look at some of the Unit tests (which I had to scrap and rebuild -
that's why there's so few).
new NorthwindRepository
Northwind.CustomerCollection customersByCategory = new Select()
.From
.InnerJoin
.InnerJoin(Northwind.OrderDetail.OrderIDColumn,
Northwind.Order.OrderIDColumn)
.InnerJoin(Northwind.Product.ProductIDColumn,
Northwind.OrderDetail.ProductIDColumn)
.Where("CategoryID").IsEqualTo(5)
.ExecuteAsCollection
INNER JOIN [dbo].[aspnet_Membership] AS t1 ON (t0.UserId = CONVERT(VARCHAR, t1.UserId))
WHERE (t1.Email = 'changedtoprotecttheinnocent@gmail.com')
** Insufficient result space to convert uniqueidentifier value to char. **
I think the CONVERT is causing a stink here
SELECT [t0].[CustomerId] AS [Id], [t0].[UserId], [t1].[Email], [t0].[FirstName], [t0].[LastName]
FROM [dbo].[Customer] AS [t0]
INNER JOIN [dbo].[aspnet_Membership] AS [t1] ON [t0].[UserId] = (CONVERT(NVarChar(MAX),[t1].[UserId])
Unique Identifier. I wonder what's happening with the CONVERT ... I'll look
into that. Can you open an issue for me?
Issue opened.
systems? Either way I'll take a look at CONVERT - but I think this is a
design issue.
System O handles orders and needs to associate an order with a user from system A, B or C.
System A uses an int for the customer ID, system B uses a Guid and system C uses a nchar(16).
What type would you use?
provider uses) - that should work pretty well.
How is update on an object graph going to work. I send an Object graph to a client via WCF modify some properties, add/delete some children and then send it all back again.
I know that I got to have some kind of dirty tracking but what else is missing? I can think of concurrency issues. and how to update the graph in the right order.
Can you help?
Thanks
Rob, isn't it great that other people improve your code?
I've ran in to some problem when the Providername didn't match the database name. The template couldn't find/generate the primary keys.
With the "open strategy" of templates it was nice to discover how simple it is to "pimp" the generation of code.
Im not sure this is usefull information, I post for one reason. To thank You for the inspiration of new ways to handle things.
My small tribute to this is the following:
__Utility.tt
string GetPK_01(string tableName) {
string PKSql = @"SELECT CU.COLUMN_NAME AS [PkColumn]
FROM INFORMATION_SCHEMA.TABLES AS T
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON TC.TABLE_CATALOG = T.TABLE_CATALOG
AND TC.TABLE_SCHEMA = T.TABLE_SCHEMA
AND TC.TABLE_NAME = T.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_NAME = @tableName
ORDER BY T.TABLE_NAME, CU.ORDINAL_POSITION";
string pk = "";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand(PKSql, conn);
cmd.Parameters.AddWithValue("@tableName", tableName);
conn.Open();
IDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
pk = rdr.GetString(0);
}
}
return pk;
}
After a hundred requests or so I get "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
On the activity monitor for the SQL server (Express 2008) the connections just pile up and pile up every time a new query is made. DB.CloseDB() doesn't seem to help.
reader you have to close it. Can you share some code?
Just passing by to say hi and congrats!!! Subsonic is the best! I live in Brazil and it's hard to find open source projects in .Net communities here, I think it's hard in the whole world, specially wonderful projects as subsonic. I was afraid of subsonic being discontinued, I discovered it some months ago in asp.net forums and I felt like the site was stopped, no updates since some time, that made me sad because it it's really really nice and I had to say it for you, subsonic rocks!!! Thank you and the team for that. I have no enough knowledge to contribute but oh God how I'd like to. Maybe when I got enough skills in the near future. I downloading subsonic 3 and I'll use it in my next project.
Hugs!
public Product GetProductById(int id)
{
var db = DB.CreateDB();
return (from p in db.Product where p.Id == id select p).SingleOrDefault
}
Each time I use it the SQL server gets another connection that never closes. When I use DB.Select.From
Maybe I have misunderstood how to use the LINQ functionality. Haven't used it against a DB before.
I'm so glad I never took the time to learn codedom, t4 seems easier to grasp.
Why you said that you can just change connection string to make it work with other provider?
I saw in your template. You didn't even use IDbConnection or IDbCommand or DbCommand or whatsoever BUT I see you use SqlConnection, SqlCommand. So I just wonder that how can you make it works with MySql.
By the way, I am a fan of Subsonic. Cool
All you need to do is to update the Utility.tt class to work with MySQL (or
Oracle or whatever) which I hope to do in the near future.
I'll get there...
Thanks a lot for this great tool.
I have been using subsonic for a while now with two major projects and extremely happy with it.
Quick question, I am playing with subsonic 3 from the SVN, and tried the Update method in the Repository, but the data never made it to the table!
am i missing something?
sarmaad
this is the test code:
WA.Core.DB _db = WA.Core.DB.CreateDB();
WA.Core.TestRepository
Customer cu = _db.Customer.Single(r => r.FirstName == txtselected.Text);
cu.Address = txtAddress.Text;
cu.City = txtCity.Text;
cu.State = txtState.Text;
cu.Country = txtCountry.Text;
cu.ZipCode = Convert.ToInt32(txtZipCode.Text);
cRes.Update(cu);
DB.CloseDB();
thanks
I am working about to start working on a project and i'm wondering how you will set up the project stucture using Domain-Driven Design.
Thanks
then it might help a bit. So in this regard - work your model using DDD as
you normall would, then build up a repository which uses SubSonic. You can
set the generated stuff to be internal and load your objects with SubSonic's
stuff.