private void renderSqlUpdate(DBColumn[] columns, string schemaName, string objectName, string procedureName) { DateTime generated = DateTime.Now; StringBuilder entrySb = new StringBuilder(); StringBuilder setSb = new StringBuilder(); StringBuilder whereSb = new StringBuilder(); StringBuilder ccSelectSb = new StringBuilder(); StringBuilder ccWhereSb = new StringBuilder(); StringBuilder returnSelectSb = new StringBuilder(); StringBuilder returnWhereSb = new StringBuilder(); int v = 0; // used in conjunction with returnSelectSb int w = 0; // used in conjunction with ccSelectSb, ccWhereSb int x = 0; // used in conjunction with entrySb int y = 0; // used in conjunction with setSb int z = 0; // used in conjunction with whereSb bool optimisticConcurrency = false; foreach (DBColumn column in columns) { if (!DataAccess.isModifiedDate(column) && !DataAccess.isCreatedDate(column) && !DataAccess.isCreationUserId(column)) { // entry values; Note does not include the recognized special columns if (x > 0) { entrySb.AppendFormat(",\r\n"); } entrySb.AppendFormat("@{0} {1}", column.Name, column.DataTypeNameComplete.ToUpper()); x++; } // set values; Note does not include computed columns, auto key columns, recognized special columns handled discreetly if (!column.IsComputed && !column.IsAutoKey && !DataAccess.isCreatedDate(column) && !DataAccess.isCreationUserId(column)) { if (y > 0) { setSb.AppendFormat(",\r\n"); } if (DataAccess.isModifiedDate(column)) { setSb.AppendFormat("\t[{0}] = getdate()", column.Name); } else { setSb.AppendFormat("\t[{0}] = @{0}", column.Name); } y++; } if (column.IsInPrimaryKey || column.IsComputed) { if (z > 0) { whereSb.AppendFormat(" AND\r\n"); } whereSb.AppendFormat("\t[{0}] = @{0}", column.Name); z++; } if (column.IsInPrimaryKey) { if (w > 0) { ccSelectSb.AppendFormat(", "); ccWhereSb.AppendFormat(" AND "); returnWhereSb.AppendFormat(" AND \r\n"); } ccSelectSb.AppendFormat("[{0}]", column.Name); ccWhereSb.AppendFormat("[{0}] = @{0}", column.Name); returnWhereSb.AppendFormat("\t\t[{0}] = @{0}", column.Name); w++; } if (column.IsComputed && column.DataTypeName == "timestamp") { optimisticConcurrency = true; } if (v > 0) { returnSelectSb.AppendFormat(",\r\n"); } returnSelectSb.AppendFormat("\t\t[{0}]", column.Name); v++; } string entryString = entrySb.ToString(); string setString = setSb.ToString(); string whereString = whereSb.ToString(); string ccString = string.Format("SELECT {0} FROM {1} WHERE {2}", ccSelectSb.ToString(), objectName, ccWhereSb.ToString()); string returnSelectString = returnSelectSb.ToString(); string returnWhereString = returnWhereSb.ToString(); entrySb = null; setSb = null; whereSb = null; ccSelectSb = null; ccWhereSb = null; returnSelectSb = null; returnWhereSb = null; #line default #line hidden #line 106 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N\'["); #line default #line hidden #line 107 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 107 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("].["); #line default #line hidden #line 107 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 107 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("]\') AND OBJECTPROPERTY(id, N\'IsProcedure\') = 1)\r\nDROP PROCEDURE ["); #line default #line hidden #line 108 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 108 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("].["); #line default #line hidden #line 108 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 108 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("]\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\n\r\n-- Update Procedu" + "re for "); #line default #line hidden #line 116 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 116 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("."); #line default #line hidden #line 116 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(objectName)); #line default #line hidden #line 116 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n-- Do not modify the contents of this procedure.\r\n-- Generated "); #line default #line hidden #line 118 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(generated.ToString())); #line default #line hidden #line 118 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\r\nCREATE PROCEDURE "); #line default #line hidden #line 120 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 120 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("."); #line default #line hidden #line 120 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 120 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\r\n"); #line default #line hidden #line 122 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(entryString)); #line default #line hidden #line 122 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\r\nAS \r\n\r\nSET NOCOUNT ON\r\n\r\nUPDATE ["); #line default #line hidden #line 128 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 128 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("].["); #line default #line hidden #line 128 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(objectName)); #line default #line hidden #line 128 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("]\r\nSET\r\n"); #line default #line hidden #line 130 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(setString)); #line default #line hidden #line 130 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\nWHERE\r\n"); #line default #line hidden #line 132 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(whereString)); #line default #line hidden #line 132 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n"); #line default #line hidden #line 133 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" if (optimisticConcurrency) { #line default #line hidden #line 135 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(@"------------------------------------------------------------------------------------------------------------------------------------------------------"", -- Note about OPTIMISTIC CONCURRENCY -- The SQL Server timestamp (alias rowversion) field is modified everytime a change is made to the row containing it. -- Verification that the timestamp contains the same original value is assurance that none of the fields have been changed prior to this update request. -- If the number of rows affected equals zero, then the update failed, else the update request was successful. ------------------------------------------------------------------------------------------------------------------------------------------------------ IF @@ROWCOUNT = 0 -- zero records affected BEGIN IF EXISTS(" ); #line default #line hidden #line 148 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(ccString)); #line default #line hidden #line 148 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(") BEGIN\r\n\t\t-- raiserror will throw a SqlException\r\n\t\tRAISERROR(\'A optimistic conc" + "urrency error occurred in procedure "); #line default #line hidden #line 150 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 150 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("."); #line default #line hidden #line 150 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 150 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(@". No information was updated as a result of this request because some of the information had been changed prior to this update request.', 16, 1) RETURN 8 -- concurrency failure; the record exists, but the concurrency (timestamp) field had changed prior to this request END ELSE BEGIN -- raiserror will throw a SqlException RAISERROR('An error occurred in procedure " ); #line default #line hidden #line 155 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 155 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("."); #line default #line hidden #line 155 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 155 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(". No information was updated as a result of this request because the record could" + " not be accessed based upon the key information provided.\', 16, 1)\r\n\t\tRETURN 9 -" + "- record does not exist based upon the primary key(s)\r\n\tEND\r\nEND\r\nELSE BEGIN\r\n\tS" + "ELECT\r\n"); #line default #line hidden #line 161 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(returnSelectString)); #line default #line hidden #line 161 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\tFROM ["); #line default #line hidden #line 162 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 162 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("].["); #line default #line hidden #line 162 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(objectName)); #line default #line hidden #line 162 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("]\r\n\tWHERE\r\n"); #line default #line hidden #line 164 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(returnWhereString)); #line default #line hidden #line 164 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\t\r\n\tRETURN -- update successful\r\nEND\r\n"); #line default #line hidden #line 168 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" } else { #line default #line hidden #line 171 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\t\t\r\nIF @@ERROR <> 0\r\nBEGIN\r\n\t-- raiserror will throw a SqlException\r\n\tRAISERROR(\'" + "An error occurred in procedure "); #line default #line hidden #line 175 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 175 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("."); #line default #line hidden #line 175 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(procedureName)); #line default #line hidden #line 175 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(". No information was updated as a result of this request.\', 16, 1)\r\n\tRETURN 9 -- " + "error occurred\r\nEND\r\nELSE BEGIN\r\n\tSELECT\r\n"); #line default #line hidden #line 180 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(returnSelectString)); #line default #line hidden #line 180 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\tFROM ["); #line default #line hidden #line 181 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(schemaName)); #line default #line hidden #line 181 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("].["); #line default #line hidden #line 181 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(objectName)); #line default #line hidden #line 181 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("]\r\n\tWHERE\r\n"); #line default #line hidden #line 183 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write(this.ToStringHelper.ToStringWithCulture(returnWhereString)); #line default #line hidden #line 183 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("\r\n\t\r\n\tRETURN -- update successful\r\nEND\r\n"); #line default #line hidden #line 187 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" } #line default #line hidden #line 188 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" this.Write("GO\r\n \r\nSET QUOTED_IDENTIFIER OFF\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\n"); #line default #line hidden #line 195 "C:\Users\mikel\Dropbox\MWLShare\DALCodeGen\DALCodeGen\sql\UpdateProc.tt" }
private bool IsColumnValidForUpdate(DBColumn column) { return(!DataAccess.isModifiedDate(column) && !DataAccess.isCreatedDate(column) && !DataAccess.isAuthorityId(column) && !DataAccess.isCreationUserId(column)); }