Subsonic: How to exclude a table column so that its not included in the generated SQL query

Question

I need to insert a record to a table.

Subsonic builds the query something like this (as far as i know):

INSERT INTO Table1
(Title, Description, RowVersion)
VALUES 
(@Title, @Description, @RowVersion)

But i want to remove the RowVersion column from the SQL query bacause its autogenerated by the sql server. How can i do that?

Answer 1

You don't need to worry about this. SubSonic is intelligent enough to handle this!

Just create new object assign values to properties and save it.

var o = new DataObject();
o.Name="Foo";
o.Age = 20;
//o.RowVersion = ....; DON'T ASSIGN THIS
o.Save();

EDIT:- Here is what I've tried:

Table Definition:

CREATE TABLE [dbo].[TestTimeStamp](
[RowID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Description] [nvarchar](50) NOT NULL,
[RowVersion] [timestamp] NOT NULL
)

Code:

private static void Test()
{
    var o = new TestTimeStamp();
    o.Description = "Hello World";
    o.Save();
}

FIXED:- Yippe, I spinned my head over the cause, as this has never happened in SubSonic 2. I branched SubSonic 3 code, but there was not anything to find. Then after much fooling around I once again examined T4 templates. Some how the IsReadOnly property is not being set but it is checked when cretaing insert, update queries in SubSonic.Extension.Object.cs class. So the solution is to add a line to Structs.tt file's for loop which adds columns to table classes :) . To fix find the following loop (it starts at line 30)

<# foreach(var col in tbl.Columns){#>
       Columns.Add(new DatabaseColumn("<#=col.Name#>", this)
       {

and change initialization of new DatabaseColumn to as follows:

   Columns.Add(new DatabaseColumn("<#=col.Name#>", this)
   {
       IsPrimaryKey = <#=col.IsPK.ToString().ToLower()#>,
       DataType = DbType.<#=col.DbType.ToString()#>,
       IsNullable = <#=col.IsNullable.ToString().ToLower()#>,
       AutoIncrement = <#=col.AutoIncrement.ToString().ToLower()#>,
       IsForeignKey = <#=col.IsForeignKey.ToString().ToLower()#>,

       //THIS LINE DO THE TRICK.
       IsReadOnly = <#=col.DataType.ToLower().Equals("timestamp")
                          .ToString().ToLower() #>
   });

PS:- Please get subsonic srouce from here. In the previous version only null and AutoIncrement is checked on inclusion into Add and Update column list, but this code checks for ReadOnly property also.

Answer 2

I was having grief with timestamp fields so just excluded timestamp columns in Structs.tt altogether. Same place as above Line 30. This is using current SubSonic 3.0 master checkout

if(<#=col.DataType.ToLower().Equals("timestamp").ToString().ToLower() #>)
{}else{
 Columns.Add(new DatabaseColumn("<#=col.Name#>", this)
 {
      IsPrimaryKey = <#=col.IsPK.ToString().ToLower()#>,
      DataType = DbType.<#=col.DbType.ToString()#>,
      IsNullable = <#=col.IsNullable.ToString().ToLower()#>,
      AutoIncrement = <#=col.AutoIncrement.ToString().ToLower()#>,
      IsForeignKey = <#=col.IsForeignKey.ToString().ToLower()#>,
      MaxLength = <#=col.MaxLength#>
 });
}