easy way to magically generate the update and insert stored procedures when you override the delete

Question

Acck I have not had to create a special stored procedure for my entity class in a while, before the current version. And then it was on smaller tables. I created my delete, great good, but I forgot that EF makes you create the insert and update as well. Anyone know of a quick way to do this? I have about 7-8 tables with about 30-50 columns in each I need to do this for, not looking forward to the typing.

Answer 1

CRUD stored procedure creation is awesome (and one of many features) in SSMS Tools. http://www.ssmstoolspack.com/

Answer 2

And then it was on smaller tables. I created my delete, great good, but I forgot that EF makes you create the insert and update as well

You tagged EntityFramework 5.0 with this question, so i dont quite understand why do you need to generate the stored procedures for delete.

All CRUD (Create, Read, Delete, Update) are done at the DbContext, with no need to write procedures for that. If you have a well organized database (with PKs, FKs and good indices) the is no need for you to have the stored procedures, because you can even compile the Views of EF so that you can have an even better result.

Se a sample code:

    DbContext context = new YourContext();

    public bool Delete<TEntity>(TEntity entity) where TEntity : class
    {
        var set = context.Set<TEntity>();
        set.Attach(entity);
        return true;
    }

    public bool Add<TEntity>(TEntity entity) where TEntity : class
    {
        var set = context.Set<TEntity>();
        set.Add(entity);
        return true;
    }

You dont even need to use this approach, you can use DbContext.Set directly, like in the following example:

    void Run()
    {

        using (DbContext context = new MyContext())
        {
            //Create a new person to insert
            var newItem = new Person() { Name = "Mike"} ;

            var set = context.Set<Person>();
            set.Add(newItem);

            // Returns a record from database with PK = "John"
            var itemToDelete = set.Find("John");
            set.Remove(itemToDelete);

            // This will add the new record, and delete "John" from the Database
            context.SaveChanges();
        }
    }

So you see, no need for stored procedures for CRUD!

Use stored procedures for other stuff, related to the database, no need for them with EF :)

Anyone know of a quick way to do this? I have about 7-8 tables with about 30-50 columns in each I need to do this for, not looking forward to the typing.

The whole point here was to show that you don't need to write stored procedures to handle CRUD operation when using EF.

You asked for a quick way of doing this (and even mentioned you dont want to type them) my answer is:

The quicker way is not do any procedure! This is fast and avoid typing! :)

The code demonstrated already handles your requirement without the need of any stored procedure. :)

Added information

If the reason you think you need stored procedures to delete is because you want to delete related entities and you think that EF does not handle this sittuation, then this is a different issue and i may tell you some possible causes:

1) Maybe you end with a error because of FK reference, in this case, please take a look here

2) Maybe the error is caused because when you are removing one entity from a relationship with other entity, EF will not understand you want do fisically delete the record, you need to excplicity alter the .ChangeState to Deleted.

Take this examle:

public static void StudentLeaves(string name)
{
    using (var context = new SchoolContext())
    {
        context.Students.Remove(context.Students.Single(s => s.Name == name));
        context.SaveChanges();
    }
}

The above example will delete this student right? But noow look at the second example

public static void StudantLeaveParticularSchool(string schoolName, string name)
{
    using (var context = new SchoolContext())
    {
        var school = context.Schools.Single(a => a.Nome == schoolName);
        school .Students.Remove(context.Students.Single(a => a.Nome == name));
        context.SaveChanges();
    }
}

The following code will not delete the Student from the Database, it will only remove the relationship!

If you are using CodeFirst, you can explicitly say that you want to use DeleteCascade, something like this:

modelBuilder
    .Entity()
    .HasRequired(s => s.Schools)
    .WithMany(r => r.Students)
.WillCascadeOnDelete();

UPDATED:

For those of you that are interested in havin a "magic" way of creating the stored procedures, i will tell you a way to do this, but i tell you again: If you are using EF with best practices, there is no need to do this, when i first started development using EF i also tought i needed this, so we wrote a few T4 files that generated hundreds of stored procedures in the blink of an eye. But after some time we discovered that this is not the right approach when using EF, so we dropped the procedures and took the T4 out of the project, thing got much easier.

For you to create the procedures you will need to write T4 files. If you want to know more about T4, find it here (http://msdn.microsoft.com/en-us/library/vstudio/bb126247.aspx)

I will show you the DELETE creation T4 template we wrote (you will need some other base .tt files we have to make this run, so keep in mind this is a sample):

<#@ template language="C#" debug="true" #>
<#@ output extension=".sql" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.XML" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>
<#@ import namespace="System.Runtime.Remoting.Messaging" #>
-- Winsys Solutions
-- Stored Procedure de delete para a tabela <#= this.SchemaName #>.<#= this.TableName #>
CREATE PROCEDURE <#= this.SchemaName #>.<#= this.TableName.Replace("TBWS4_", "PRWS4_") #>_Delete
    <# WriteParameterDeclarations(this.Table); #> 
AS
BEGIN
    DELETE FROM 
        <#= this.SchemaName #>.<#= this.TableName #> 
    WHERE
        <# WriteWhereClause(this.Table); #> 
END
GO
<#@ include file="CommonSqlHelper.tt" #>

<#+
    /// <summary>
    /// Writes stored procedure parameter declarations for all columns in the 
    /// primary key and all TIMESTAMP columns of the specified table. 
    /// </summary>
    void WriteParameterDeclarations(Table table)
    {
        PushIndent("    ");
        int parameterIndex = 0;
        foreach(Column column in table.Columns)
        {
            if (column.InPrimaryKey || column.DataType.SqlDataType == SqlDataType.Timestamp)
            {
                if (parameterIndex > 0)
                    WriteLine(",");
                Write("@{0} {1}", column.Name, GetDataTypeDeclaration(column.DataType));
                parameterIndex++;
            }
        }
        PopIndent();
    }
#>

<#+
    string TableName
    {
        get { return (string) CallContext.GetData("DeleteStoredProcedure.TableName"); }
    }

    string SchemaName
    {
        get { return (string) CallContext.GetData("DeleteStoredProcedure.SchemaName"); }
    }

    Table Table
    {
        get { return (Table) CallContext.GetData("DeleteStoredProcedure.Table"); }
    }
#>

The helper methods are:

/// <summary>
/// Returns a string that contains T-SQL declaration for the specified data 
/// type. For string data types this includes maximum length, for numeric 
/// data types this includes scale and precision.
/// </summary>
string GetDataTypeDeclaration(DataType dataType)
{
    string result = dataType.Name;
    switch(dataType.SqlDataType)
    {
        case SqlDataType.Binary:
        case SqlDataType.Char:
        case SqlDataType.NChar:
        case SqlDataType.NVarChar:
        case SqlDataType.VarBinary:
        case SqlDataType.VarChar:
            result += string.Format("({0})", dataType.MaximumLength);
            break;

        case SqlDataType.NVarCharMax:
        case SqlDataType.VarBinaryMax:
        case SqlDataType.VarCharMax:
            result += "(max)";
            break;

        case SqlDataType.Decimal:
        case SqlDataType.Numeric:
            result += string.Format("({0}, {1})", dataType.NumericPrecision, dataType.NumericScale);
            break;
    }
    return result;
}

/// <summary>
/// Generates where clause for UPDATE and DELETE statements for the specified
/// table.
/// </summary>
void WriteWhereClause(Table table, bool includeAllColumns = false)
{
    PushIndent("        ");
    int whereIndex = 0;
    foreach(Column column in table.Columns)
    {
        if (column.InPrimaryKey || column.DataType.SqlDataType == SqlDataType.Timestamp || includeAllColumns)
        {
            if (whereIndex > 0)
                WriteLine(" AND");

            if (includeAllColumns)
                Write("({0} = @{0} OR @{0} IS NULL)", column.Name);
            else
                Write("{0} = @{0}", column.Name);

            whereIndex++;           
        }
    }
    PopIndent();
}