Thursday, 30 April 2009

ASP.NET Dynamic Data Entities Web Application: Hiding Primary Key / Identity Column

I have finally fallen in love; it took a while, but there you go.  ASP.net Dynamic Data is very cool.  I’m currently doing a new project for which there are two main GUIs.  The front-end, which is a standard ASP.NET MVC app which provides users with a beautiful interface, and the back-end; which just needs to provides Admins with a way to edit the data inside database tables.

ASP.NET dynamic data has been great because it simply supports the CRUD operations required.  If you’re using ADO.NET Entities (EDM) for your data model, then there’s one thing to bear in mind; the primary keys inside the database will be editable on the GUI.  This is not the case for LINQ-to-SQL data models.

However, I wanted to use the Entities Framework because it’s shown itself to me to be more robust and configurable.

You can stop any columns from being shown on the GUI by decorating them with the [ScaffoldColumn(false)] attribute.

It’s not as simple as you’d think, because you cannot just add that attribute and have done.  The code is within the Designer file and is generated everytime the EDM is refreshed, so what you need to do is create a partial class for the table containing the column you wish to hide. Create a new class which is solely for metadata.  Declare the same column (and data type) inside that metadata class, annotate that with [ScaffoldColumn(false)]. The associate your partial class with the metadata class.

This is more easily demonstrated:-

Here’s your generated code within the Designer module of the EDM

namespace MyDataLayer.Data
{
[global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute(NamespaceName = "MyDataLayer", Name = "Provider")]
[global::System.Runtime.Serialization.DataContractAttribute(IsReference = true)]
[global::System.Serializable()]
public partial class Provider : global::System.Data.Objects.DataClasses.
EntityObject
{
/// <summary>
///
This is the primary key column that we don’t want on the GUI
/// </summary>
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty = true, IsNullable = false)]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public int id
{
get
{
return this._id;
}
set
{
this.OnidChanging(value);
this.ReportPropertyChanging("id");
this._id = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
this.ReportPropertyChanged("id");
this.OnidChanged();
}
}
}
}



Here’s the partial class that must be created



[MetadataType(typeof(ProviderMetadata))]
public partial class
Provider {}



Here’s the metadata class which allows you to add more attributes to properties existing inside the code-gen’d Designer file.



public class ProviderMetadata
{

[ScaffoldColumn(false)]
public string id { get; set; }

}



So the Provider partial class inside the Designer code is extended by the new partial class.  The new partial class’s sole job is to add a new attribute, MetadataType, which points to the new metadata class, ProviderMetadata.



In LINQ-To-SQL, this is not necessary (apparently)! At least we have a way, with EDM, even if it’s a bit long-winded.

Thursday, 23 April 2009

Bulk schema change for stored procs in SQL Server

When you need to change the schema for SPs.

Remember this:

DECLARE
  @OldOwner sysname,
  @NewOwner sysname
 SET @OldOwner = 'oldOwner'
 SET @NewOwner = 'dbo'
DECLARE CURS CURSOR FOR
SELECT
  name
FROM
 sysobjects
WHERE
  type = 'p' 
AND
  uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) 
AND 
  NOT name LIKE 'dt%' FOR READ ONLY
DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@VERSION >= 'Microsoft SQL Server 2005'
    BEGIN
        EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
        exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
    END
    ELSE
        EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')
    FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS


Courtesy of Tim Page; http://www.geekzilla.co.uk/ViewC1D86C71-13E7-477B-94EC-FD01B524B6C3.htm 



k