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));
}