public static string CreateUpdateProcedure(string aplicacion, string tablename, FieldArray fields, string entidad)
{
/*
CREATE PROCEDURE [dbo].[RPOSXXX_entidad_I]
@alias1 AS dbotype,
@alias2 AS dbotype
AS
BEGIN
UPDATE tablename
SET field2 = @alias2,
field3 = @alias3
WHERE field1 = @alias1
END
*/
const string PARAM_FORMAT = "\n\t@{0} AS {1},";
const string SET_FORMAT = "\n\t\t\t\t{0} = @{1},";
const string WHERE_FORMAT = "{0} = @{1}\n\t\t\t AND ";
string spName = GetSPName(aplicacion,entidad,SPType.Update);
string SP_FORMAT =
"CREATE PROCEDURE [dbo].[" + spName + "] \n" +
"\t{0}\n" +
"AS\n" +
"BEGIN\n" +
"\tUPDATE " + tablename + " \n" +
"\t\t\tSET\n" +
"\t\t\t{1}\n" +
"\t\t\tWHERE {2} \n" +
"END;";
string paramPart = "";
string wherePart = "";
string setPart = "";
FieldArray nfields = new FieldArray();
foreach(Field fld in fields)
{
if(fld.Alias == null || fld.Alias == "") fld.Alias = fld.Name;
nfields.Add(fld);
}
string[] pks = GetPrimaryKeys(tablename);
foreach(string pk in pks)
{
Field field = null;
foreach(Field fld in fields)
{
if(fld.Name == pk)
{
nfields.Remove(fld);
field = fld;
break;
}
}
if(field != null)
{
string nParam = string.Format(PARAM_FORMAT, field.Alias, field.DBDefinition);
paramPart += nParam;
string nWhere = string.Format(WHERE_FORMAT, field.Name, field.Alias);
wherePart += nWhere;
}
}
foreach(Field fld in nfields)
{
string nParam = string.Format(PARAM_FORMAT, fld.Alias, fld.DBDefinition);
paramPart += nParam;
setPart += string.Format(SET_FORMAT, fld.Name, fld.Alias);
}
paramPart = CleanSPPart(paramPart);
setPart = CleanSPPart(setPart);
wherePart = CleanSPPart(wherePart);
if(wherePart.Length > 0)wherePart = wherePart.Remove(wherePart.Length - 4, 4);
return string.Format(SP_FORMAT, paramPart, setPart, wherePart);
}
