internal static string insertString(IDynamic obj, string[] props, out List <System.Data.SqlClient.SqlParameter> parameters, DynamicTable table)
{
//need to make sure that one have the identity for the item
//or two the pks are there for the update
var identity = table.Identity();
parameters = new List <SqlParameter>();
List <string> updateFields = new List <string>();
string[] tableFields = table.Fields();
string updateStatement = "INSERT INTO " + table.Name + " (";
if (!string.IsNullOrEmpty(identity) && props.Length > 1)
{
foreach (var item in props)
{
if (item != identity && tableFields.Contains(item))
{
updateFields.Add(item);
parameters.Add(Data.getParameter(item, obj[item]));
}
}
}
else
{
foreach (var item in props)
{
if (tableFields.Contains(item))
{
updateFields.Add(item);
parameters.Add(Data.getParameter(item, obj[item]));
}
}
}
updateStatement += string.Join(", ", updateFields) + ") VALUES (" +
string.Join(", ", updateFields.Select(s => "@" + s).ToArray()) + ")";
if (!string.IsNullOrEmpty(identity))
{
parameters.Add(new SqlParameter
{
ParameterName = "@" + identity,
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
updateStatement += ";\r\nSET @" + identity + " = SCOPE_IDENTITY();";
}
return(updateStatement);
}
internal static string updateString(IDynamic obj, string[] props, out List <System.Data.SqlClient.SqlParameter> parameters, DynamicTable table)
{
//need to make sure that one have the identity for the item
//or two the pks are there for the update
var identity = table.Identity();
parameters = new List <SqlParameter>();
List <string> updateFields = new List <string>();
string[] tableFields = table.Fields();
string updateStatement = "UPDATE " + table.Name + " SET ";
if (!string.IsNullOrEmpty(identity) && obj[identity] != null && props.Length > 1)
{
foreach (var item in props)
{
if (item != identity && tableFields.Contains(item))
{
updateFields.Add(item);
parameters.Add(Data.getParameter(item, obj[item]));
}
}
updateStatement += string.Join(", ", updateFields.Select(o => o + "= @" + o).ToArray());
updateStatement += " WHERE " + identity + " = @" + identity;
parameters.Add(new SqlParameter
{
ParameterName = "@" + identity,
Value = obj[identity]
});
}
else
{
var pks = table.PrimaryKeys();
if (pks != null && pks.Length > 0)
{
var hasPk = true;
foreach (var pk in pks)
{
if (obj[pk] == null)
{
hasPk = false;
break;
}
}
if (hasPk)
{
foreach (var item in props)
{
if (pks.FirstOrDefault(i => i == item) == null && tableFields.Contains(item))
{
updateFields.Add(item);
parameters.Add(Data.getParameter(item, obj[item]));
}
}
updateStatement += string.Join(", ", updateFields.Select(o => o + "= @" + o).ToArray());
updateStatement += " WHERE ";
updateFields.Clear();
foreach (var item in pks)
{
updateFields.Add(item);
parameters.Add(Data.getParameter(item, obj[item]));
}
updateStatement += string.Join(" AND ", updateFields.Select(o => o + " = @" + o).ToArray());
}
}
}
return(updateStatement);
}
