<# // Copyright (c) 2008, David DeWinter and Ryan Hauert // All rights reserved. // // THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED // WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, // SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF // USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, // OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY // OF SUCH DAMAGE. #> <#@ template language="C#" debug="True" #> <#@ output extension=".sql" #> <#@ assembly name="System.Core" #> <#@ assembly name="System.Data" #> <#@ assembly name="System.Data.Entity" #> <#@ assembly name="System.Xml" #> <#@ assembly name="System.Xml.Linq" #> <#@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"#> <#@ assembly name="Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #> <#@ import namespace="System.Collections" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data.EntityClient" #> <#@ import namespace="System.Data.SqlClient" #> <#@ import namespace="System.Diagnostics" #> <#@ import namespace="System.IO" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Text.RegularExpressions" #> <#@ import namespace="System.Xml.Linq" #> <#@ import namespace="Microsoft.SqlServer.Management.Smo" #> <# // Configuration Parameters: // The full path of the directory where the .tt file is located. string directoryName = ""; // By default this template will generate stored procedures for every EDMX file // in the current directory. Use this file filter regular expression to constrain // what EDMX files are parsed. For example, if you want to parse only a file // with the name "Entities.edmx", then set edmxFileFilter = new Regex(@"Entities\.edmx"); Regex edmxFileFilter = null; // Use this to create a prefix for your stored procedures. string sprocPrefix = ""; #> <# // Conform the configuration parameters if (String.IsNullOrEmpty(directoryName)) { // The only way we can get the current directory is by looking at the stack trace. Note // that the "debug" attribute of the template directive must be set to true for this to work. string stackTraceFileName = new StackTrace(true).GetFrame(0).GetFileName(); if (String.IsNullOrEmpty(stackTraceFileName)) { throw new ArgumentException("No value was specified for the 'directoryName' configuration parameter" + ", and we could not figure out the file name from the stack trace (most likely because of running " + "the template with debug='False' specified in the <\u0023@ template \u0023> directive."); } else { directoryName = Path.GetDirectoryName(stackTraceFileName); } } else if (!Directory.Exists(directoryName)) { throw new ArgumentException("The specified directory does not exist: " + directoryName); } #> -------------------------------------------------------------------------------- -- -- This code was generated by a tool. -- -- Changes to this file may cause incorrect behavior and will be lost if -- the code is regenerated. -- -------------------------------------------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO <# string[] entityFrameworkFiles = Directory.GetFiles(directoryName, "*.edmx"); for (int i = 0; i < entityFrameworkFiles.Length; ++i) { string entityFrameworkFile = entityFrameworkFiles[i]; if (edmxFileFilter == null || edmxFileFilter.IsMatch(entityFrameworkFile)) { #> -- Creating stored procedures for <#= entityFrameworkFile #>. <# IEnumerable tables = ExtractTables(entityFrameworkFile); Dictionary tableLookup = new Dictionary(); bool tablesExist = false; // Get the first table in the list so we can hook it up with a database. foreach (Table table in tables) { if (!tablesExist) { tablesExist = true; #> USE "<#= table.Parent.Name #>" GO <# } tableLookup.Add(String.Format("{0}.{1}", table.Schema, table.Name), table); } if (!tablesExist) { continue; } StringBuilder insertParameterList = new StringBuilder(); StringBuilder insertColumnList = new StringBuilder(); StringBuilder insertValuesClause = new StringBuilder(); StringBuilder serverGeneratedColumns = new StringBuilder(); StringBuilder serverGeneratedColumnsWithDataTypes = new StringBuilder(); StringBuilder primaryKeyColumnRetrievalList = new StringBuilder(); StringBuilder updateParameterList = new StringBuilder(); StringBuilder updateColumnPairList = new StringBuilder(); StringBuilder updateColumnComparisonClause = new StringBuilder(); StringBuilder deleteParameterList = new StringBuilder(); StringBuilder deleteColumnComparisonClause = new StringBuilder(); // Loop over all the tables in the database of interest. foreach (Table table in tables) { // Set up a lookup of the foreign keys in the table, to figure out which // foreign key columns we care about for delete sprocs. (The Entity Framework // requires there to be a mapping for all association columns, but the tables // in the database might not reflect which tables are in the model.) Dictionary foreignKeyLookup = new Dictionary(); ForeignKeyCollection foreignKeys = table.ForeignKeys; int foreignKeyCount = foreignKeys.Count; for (int j = 0; j < foreignKeyCount; ++j) { ForeignKey foreignKey = foreignKeys[j]; ForeignKeyColumnCollection foreignKeyColumns = foreignKey.Columns; int foreignKeyColumnCount = foreignKeyColumns.Count; if (tableLookup.ContainsKey(String.Format("{0}.{1}", foreignKey.ReferencedTableSchema, foreignKey.ReferencedTable))) { for (int k = 0; k < foreignKeyColumnCount; ++k) { foreignKeyLookup[foreignKeyColumns[k].Name] = null; } } } string partiallyEscapedQualifiedTableName = String.Format("{0}\".\"{1}{2}", table.Schema, sprocPrefix, table.Name); string escapedQualifiedTableName = String.Format("\"{0}\".\"{1}\"", table.Schema, table.Name); // Various tracking booleans to enable us to make optimizations // in selecting server-generated columns when we write the INSERT sproc bool hasIdentityPrimaryKey = false; string identityColumn = null; string identityPrimaryKeyColumn = null; ColumnCollection columns = table.Columns; int columnCount = columns.Count; bool hasPrimaryKey = false; bool hasUniqueIdentifierPrimaryKeyWithFunctionalDefault = false; bool? hasOneServerGeneratedColumnThatIsIdentity = null; // Iterate over all the columns before we start outputting the sproc contents. // This way we iterate over the ColumnCollection only once. for (int j = 0; j < columnCount; ++j) { Column column = columns[j]; string columnName = column.Name; string dataType = ToSqlString(column.DataType); // If the column is neither a computed column (regardless of whether // it's a persisted computed column), an identity column, nor a primary // key column, then it's a column we can update. if (!column.Computed && !column.Identity && !column.InPrimaryKey) { AppendFormatWithSmallIndentAndNewLine(updateParameterList, "@{0} {1},", columnName, dataType); AppendFormatWithIndentAndNewLine(updateColumnPairList, "\"{0}\" = @{0},", columnName); } bool uniqueIdentifierColumnHasFunctionalDefault = false; if (dataType == "uniqueidentifier" && column.DefaultConstraint != null && Regex.IsMatch(column.DefaultConstraint.Text, @"^\(NEW(SEQUENTIAL)?ID\(\)\)$", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture)) { uniqueIdentifierColumnHasFunctionalDefault = true; if (column.InPrimaryKey) { hasUniqueIdentifierPrimaryKeyWithFunctionalDefault = true; } } if (column.Identity && !hasOneServerGeneratedColumnThatIsIdentity.HasValue) { hasOneServerGeneratedColumnThatIsIdentity = true; identityColumn = "\"" + columnName + "\""; } if (column.InPrimaryKey) { hasPrimaryKey = true; AppendFormatWithIndentAndNewLine(primaryKeyColumnRetrievalList, "\"{0}\" = @{0} AND", columnName); if (column.Identity) { identityColumn = identityPrimaryKeyColumn = "\"" + columnName + "\""; hasIdentityPrimaryKey = true; } if (!column.Computed) { // We'll use non-computed primary key columns to make // comparisons in the WHERE clause in the UPDATE statement. AppendFormatWithSmallIndentAndNewLine(updateParameterList, "@{0} {1},", columnName, dataType); if (!column.Identity) { // We'll also put another parameter in the input list for the "original" ID. // This original ID applies only when the primary key column is not an identity column. string pascalCasedColumnName = UpperCaseFirst(columnName); AppendFormatWithSmallIndentAndNewLine(updateParameterList, "@original{0} {1},", pascalCasedColumnName, dataType); AppendFormatWithIndentAndNewLine(updateColumnPairList, "\"{0}\" = @{0},", columnName); AppendFormatWithIndentAndNewLine(updateColumnComparisonClause, "\"{0}\" = @original{1} AND", columnName, pascalCasedColumnName); } else { // Otherwise, just compare on the identity column. AppendFormatWithIndentAndNewLine(updateColumnComparisonClause, "\"{0}\" = @{0} AND", columnName); } } // Both the delete column comparison clause and the parameter list ignore whether the PK is an identity or computed. AppendFormatWithIndentAndNewLine(deleteColumnComparisonClause, "\"{0}\" = @{0} AND", columnName); AppendFormatWithSmallIndentAndNewLine(deleteParameterList, "@{0} {1},", columnName, dataType); } else if (column.IsForeignKey && foreignKeyLookup.ContainsKey(columnName)) { // If the column is a foreign key column, then the EF dictates that // for the delete stored procedure to take in these parameters. AppendFormatWithSmallIndentAndNewLine(deleteParameterList, "@{0} {1},", columnName, dataType); } // If the column is generated on the server if (column.Computed || column.Identity || uniqueIdentifierColumnHasFunctionalDefault) { serverGeneratedColumns.AppendFormat(" INSERTED.\"{0}\",", column.Name); AppendFormatWithIndentAndNewLine(serverGeneratedColumnsWithDataTypes, "{0} {1},", columnName, dataType); if (!column.Identity) { hasOneServerGeneratedColumnThatIsIdentity = false; } } else { if (!uniqueIdentifierColumnHasFunctionalDefault) { AppendFormatWithSmallIndentAndNewLine(insertParameterList, "@{0} {1},", columnName, dataType); AppendFormatWithIndentAndNewLine(insertValuesClause, "@{0},", columnName); AppendFormatWithIndentAndNewLine(insertColumnList, "\"{0}\",", columnName); } } } // Remove the trailing new line and any extra characters like commas or a superfluous "AND" RemoveTrailingNewLine(updateColumnComparisonClause, 4); RemoveTrailingNewLine(updateParameterList, 1); RemoveTrailingNewLine(updateColumnPairList, 1); RemoveTrailingNewLine(deleteColumnComparisonClause, 4); RemoveTrailingNewLine(deleteParameterList, 1); RemoveTrailingCharacters(serverGeneratedColumns, 1); RemoveTrailingNewLine(serverGeneratedColumnsWithDataTypes, 1); RemoveTrailingNewLine(insertParameterList, 1); RemoveTrailingNewLine(insertValuesClause, 1); RemoveTrailingNewLine(insertColumnList, 1); RemoveTrailingNewLine(primaryKeyColumnRetrievalList, 4); #> ------------------------------------------------------------------------------------------ -- -- <#= escapedQualifiedTableName #> Stored Procedures -- ------------------------------------------------------------------------------------------ <# if (hasPrimaryKey) { #> IF OBJECT_ID('"<#= partiallyEscapedQualifiedTableName #>_Insert"') IS NOT NULL DROP PROC "<#= partiallyEscapedQualifiedTableName #>_Insert" GO CREATE PROCEDURE "<#= partiallyEscapedQualifiedTableName #>_Insert" <# if (insertParameterList.Length > 0) { #> ( <#= insertParameterList #> ) <# } #> AS BEGIN <# if (hasUniqueIdentifierPrimaryKeyWithFunctionalDefault) { #> DECLARE @results TABLE ( <#= serverGeneratedColumnsWithDataTypes #> ) <# } #> INSERT INTO <#= escapedQualifiedTableName #> <# if (insertColumnList.Length > 0) { #> ( <#= insertColumnList #> ) <# } if (hasUniqueIdentifierPrimaryKeyWithFunctionalDefault) { #> OUTPUT <#= serverGeneratedColumns #> INTO @results <# } if (insertValuesClause.Length > 0) { #> VALUES ( <#= insertValuesClause #> ) <# } else { #> DEFAULT VALUES <# } if (hasUniqueIdentifierPrimaryKeyWithFunctionalDefault) { #> SELECT * FROM @results <# } else if (serverGeneratedColumns.Length > 0) { if (hasOneServerGeneratedColumnThatIsIdentity.HasValue && hasOneServerGeneratedColumnThatIsIdentity.Value) { #> SELECT SCOPE_IDENTITY() AS <#= identityColumn #> <# } else if (hasIdentityPrimaryKey) { #> SELECT <#= serverGeneratedColumns #> FROM <#= escapedQualifiedTableName #> AS INSERTED WHERE <#= identityPrimaryKeyColumn #> = SCOPE_IDENTITY() <# } else { #> SELECT <#= serverGeneratedColumns #> FROM <#= escapedQualifiedTableName #> AS INSERTED WHERE <#= primaryKeyColumnRetrievalList #> <# } } #> END GO IF OBJECT_ID('"<#= partiallyEscapedQualifiedTableName #>_Update"') IS NOT NULL DROP PROC "<#= partiallyEscapedQualifiedTableName #>_Update" GO CREATE PROCEDURE "<#= partiallyEscapedQualifiedTableName #>_Update" ( <#= updateParameterList #> ) AS BEGIN UPDATE <#= escapedQualifiedTableName #> SET <#= updateColumnPairList #> WHERE <#= updateColumnComparisonClause #> END GO IF OBJECT_ID('"<#= partiallyEscapedQualifiedTableName #>_Delete"') IS NOT NULL DROP PROC "<#= partiallyEscapedQualifiedTableName #>_Delete" GO CREATE PROCEDURE "<#= partiallyEscapedQualifiedTableName #>_Delete" ( <#= deleteParameterList #> ) AS BEGIN DELETE FROM <#= escapedQualifiedTableName #> WHERE <#= deleteColumnComparisonClause #> END GO <# } else { #> -- Errors Found During Generation: -- The table <#= escapedQualifiedTableName #> does not have a primary key defined. No insert, update, or delete sproc can be created. <# } // Clear the StringBuilders so we can use them for the next Table. insertParameterList.Length = insertColumnList.Length = insertValuesClause.Length = serverGeneratedColumns.Length = updateParameterList.Length = updateColumnComparisonClause.Length = primaryKeyColumnRetrievalList.Length = serverGeneratedColumnsWithDataTypes.Length = updateColumnPairList.Length = deleteParameterList.Length = deleteColumnComparisonClause.Length = 0; } } } #> <#+ XNamespace edmxns = "http://schemas.microsoft.com/ado/2007/06/edmx"; XNamespace edmns = "http://schemas.microsoft.com/ado/2006/04/edm"; XNamespace ssdlns = "http://schemas.microsoft.com/ado/2006/04/edm/ssdl"; private IEnumerable ExtractTables(string edmxFilePath) { // Get the EntityContainer name from the edmx to find the // correct connection string in the in the app.config. XDocument edmxDoc = XDocument.Load(edmxFilePath); XElement edmxElement = edmxDoc.Element(edmxns + "Edmx"); // element XElement runtimeElement = edmxElement.Element(edmxns + "Runtime"); // element XElement storageModelsElement = runtimeElement.Element(edmxns + "StorageModels"); // element XElement ssdlSchemaElement = storageModelsElement.Element(ssdlns + "Schema"); string entityContainerName = runtimeElement .Element(edmxns + "ConceptualModels") // element .Element(edmns + "Schema") // element .Element(edmns + "EntityContainer") // element .Attribute("Name").Value; string ssdlNamespace = ssdlSchemaElement.Attribute("Namespace").Value; // Get the connection string out of the EF connection string from the app.config. XDocument configFile = XDocument.Load(Path.Combine(Path.GetDirectoryName(edmxFilePath), "app.config")); IEnumerable connectionStrings = configFile.Element("configuration") // element .Element("connectionStrings") // element .Elements("add"); XElement entityConnectionStringElement = Enumerable.FirstOrDefault(connectionStrings, delegate(XElement xe) { return xe.Attribute("name").Value == entityContainerName; }); if (entityConnectionStringElement == null) { this.Write(String.Format("-- The {0} file does not have an associated connection string.{1}", edmxFilePath, Environment.NewLine)); yield break; } string entityConnectionString = entityConnectionStringElement.Attribute("connectionString").Value; string connectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString; SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString); Server server = new Server(connectionStringBuilder.DataSource); Database database = server.Databases[connectionStringBuilder.InitialCatalog]; // Get a list of tables from the SSDL. XElement entityContainerElement = ssdlSchemaElement.Element(ssdlns + "EntityContainer"); IEnumerable entitySets = entityContainerElement.Elements(ssdlns + "EntitySet"); string defaultSchema = entityContainerElement.Attribute("Name").Value; foreach (XElement entitySet in entitySets) { string tableName = entitySet.Attribute("Name").Value; string schema = defaultSchema; XAttribute schemaAttribute = entitySet.Attribute("Schema"); if (schemaAttribute != null && !String.IsNullOrEmpty(schemaAttribute.Value)) { schema = schemaAttribute.Value; } Table table = database.Tables[tableName, schema]; if (table == null) { throw new InvalidOperationException(String.Format("Table \"{0}.{1}\" could not be found in target database.", schema, tableName)); } yield return table; } } private string ToSqlString(DataType dataType) { switch (dataType.Name) { case "char": case "varchar": case "nchar": case "nvarchar": case "binary": case "varbinary": return String.Format("{0}({1})", dataType.Name, dataType.MaximumLength); case "decimal": case "numeric": return String.Format("{0}({1},{2})", dataType.Name, dataType.NumericPrecision, dataType.NumericScale); default: return dataType.Name; } } private void RemoveTrailingCharacters(StringBuilder sb) { RemoveTrailingCharacters(sb, 1); } private void RemoveTrailingCharacters(StringBuilder sb, int characters) { if (sb.Length != 0) { sb.Remove(sb.Length - characters, characters); } } private void RemoveTrailingNewLine(StringBuilder sb, int extraCharactersToRemove) { RemoveTrailingCharacters(sb, Environment.NewLine.Length + extraCharactersToRemove); } private string UpperCaseFirst(string value) { return Char.ToUpper(value.Substring(0, 1)[0]) + value.Substring(1, value.Length - 1); } private void AppendFormatWithNewLine(StringBuilder sb, string format, params object[] args) { sb.AppendFormat(format + Environment.NewLine, args); } private void AppendFormatWithIndent(StringBuilder sb, string format, params object[] args) { sb.AppendFormat("\t\t\t" + format, args); } private void AppendFormatWithIndentAndNewLine(StringBuilder sb, string format, params object[] args) { AppendFormatWithNewLine(sb, "\t\t\t" + format, args); } private void AppendFormatWithSmallIndentAndNewLine(StringBuilder sb, string format, params object[] args) { AppendFormatWithNewLine(sb, "\t" + format, args); } #>