private void ExecuteInsert(IChangeSet owner, IChangeSetRow row, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
Table tbl = owner.Table;
if (tbl == null)
{
throw new ApplicationException("更新対象の表が設定されていないため、更新できません");
}
StringBuilder bufF = new StringBuilder();
StringBuilder bufP = new StringBuilder();
List <NpgsqlParameter> listP = new List <NpgsqlParameter>();
bool needComma = false;
foreach (ColumnInfo f in owner.Fields)
{
if (needComma)
{
bufF.Append(", ");
bufP.Append(", ");
}
bufF.Append(GetEscapedIdentifier(f.Name, true));
if (row[f.Index] == null || row[f.Index] is DBNull)
{
if (f.IsDefaultDefined)
{
bufP.Append("DEFAULT");
}
else
{
bufP.Append("NULL");
}
}
else
{
bufP.Append(':');
bufP.Append(f.Name);
NpgsqlParameter p = CreateParameterByFieldInfo(f, row[f.Index], false) as NpgsqlParameter;
p.SourceColumn = f.Name;
p.SourceVersion = DataRowVersion.Current;
listP.Add(p);
}
needComma = true;
}
StringBuilder buf = new StringBuilder();
buf.Append("insert into ");
buf.Append(tbl.EscapedIdentifier(CurrentSchema));
buf.AppendLine(" (");
buf.Append(" ");
buf.Append(bufF);
buf.AppendLine(") values (");
buf.Append(" ");
buf.Append(bufP);
buf.AppendLine();
buf.Append(") returning *");
ExecuteSql(buf.ToString(), listP.ToArray(), owner, row, connection, transaction);
}
private void ExecuteDelete(IChangeSet owner, IChangeSetRow row, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
Table tbl = owner?.Table;
if (tbl == null)
{
throw new ApplicationException("更新対象の表が設定されていないため、削除できません");
}
if (owner.KeyFields == null || owner.KeyFields.Length == 0)
{
throw new ApplicationException("主キーが設定されていないため、削除できません");
}
List <NpgsqlParameter> listP = new List <NpgsqlParameter>();
StringBuilder bufC = new StringBuilder();
bool needAnd = false;
foreach (ColumnInfo f in owner.KeyFields)
{
if (needAnd)
{
bufC.Append(" and ");
}
else
{
bufC.Append("where ");
}
if (f.IsNullable)
{
bufC.AppendFormat("(({0} = :old_{1}) or ({0} is null and :old_{1} is null))", GetEscapedIdentifier(f.Name, true), f.Name);
}
else
{
bufC.AppendFormat("({0} = :old_{1})", GetEscapedIdentifier(f.Name, true), f.Name);
}
bufC.AppendLine();
NpgsqlParameter p = CreateParameterByFieldInfo(f, row.Old(f.Index), true) as NpgsqlParameter;
listP.Add(p);
needAnd = true;
}
StringBuilder buf = new StringBuilder();
buf.Append("delete from ");
buf.AppendLine(tbl.EscapedIdentifier(CurrentSchema));
buf.Append(bufC);
ExecuteSql(buf.ToString(), listP.ToArray(), owner, row, connection, transaction);
}
public override void ApplyChange(IChangeSet owner, IChangeSetRow row, IDbConnection connection, IDbTransaction transaction, Dictionary <IChangeSetRow, bool> applied)
{
if (row == null)
{
return;
}
if (row.ChangeKind == ChangeKind.None)
{
return;
}
if (applied.ContainsKey(row))
{
return;
}
if (owner == null)
{
throw new ArgumentNullException("owner");
}
IChangeSetRow dep = owner.Rows.FingRowByOldKey(row.GetKeys());
if (dep != null && dep != row)
{
ApplyChange(owner, dep, connection, transaction, applied);
}
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (!(connection is NpgsqlConnection))
{
throw new ArgumentException("connection");
}
NpgsqlConnection conn = (NpgsqlConnection)connection;
NpgsqlTransaction txn = (NpgsqlTransaction)transaction;
{
try
{
switch (row.ChangeKind)
{
case ChangeKind.None:
return;
case ChangeKind.New:
ExecuteInsert(owner, row, conn, txn);
break;
case ChangeKind.Modify:
ExecuteUpdate(owner, row, conn, txn);
break;
case ChangeKind.Delete:
ExecuteDelete(owner, row, conn, txn);
break;
}
applied.Add(row, true);
}
catch (Exception t)
{
row.SetError(t);
throw;
}
}
}
private void ExecuteUpdate(IChangeSet owner, IChangeSetRow row, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
Table tbl = owner?.Table;
if (tbl == null)
{
throw new ApplicationException("更新対象の表が設定されていないため、更新できません");
}
if (owner.KeyFields == null || owner.KeyFields.Length == 0)
{
throw new ApplicationException("主キーが設定されていないため、更新できません");
}
StringBuilder bufF = new StringBuilder();
List <NpgsqlParameter> listP = new List <NpgsqlParameter>();
bool needComma = false;
foreach (ColumnInfo f in owner.Fields)
{
if (!row.IsModified(f.Index))
{
continue;
}
if (needComma)
{
bufF.AppendLine(", ");
}
bufF.Append(" ");
bufF.Append(GetEscapedIdentifier(f.Name, true));
bufF.Append(" = :");
bufF.Append(f.Name);
NpgsqlParameter p = CreateParameterByFieldInfo(f, row[f.Index], false) as NpgsqlParameter;
p.SourceColumn = f.Name;
p.SourceVersion = DataRowVersion.Current;
listP.Add(p);
needComma = true;
}
bufF.AppendLine();
if (!needComma)
{
// 変更がなかった
return;
}
StringBuilder bufC = new StringBuilder();
bool needAnd = false;
foreach (ColumnInfo f in owner.KeyFields)
{
if (needAnd)
{
bufC.Append(" and ");
}
else
{
bufC.Append("where ");
}
if (f.IsNullable)
{
bufC.AppendFormat("(({0} = :old_{1}) or ({0} is null and :old_{1} is null))", GetEscapedIdentifier(f.Name, true), f.Name);
}
else
{
bufC.AppendFormat("({0} = :old_{1})", GetEscapedIdentifier(f.Name, true), f.Name);
}
bufC.AppendLine();
NpgsqlParameter p = CreateParameterByFieldInfo(f, row.Old(f.Index), true) as NpgsqlParameter;
listP.Add(p);
needAnd = true;
}
StringBuilder buf = new StringBuilder();
buf.Append("update ");
buf.Append(tbl.EscapedIdentifier(CurrentSchema));
buf.AppendLine(" set");
buf.Append(bufF);
buf.Append(bufC);
buf.Append("returning *");
ExecuteSql(buf.ToString(), listP.ToArray(), owner, row, connection, transaction);
}
//[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:SQL クエリのセキュリティ脆弱性を確認")]
private void ExecuteSql(string sql, NpgsqlParameter[] paramList, IChangeSet owner, IChangeSetRow row, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
LastSql = sql;
LastParameter = paramList;
NpgsqlCommand cmd = new NpgsqlCommand(sql, connection, transaction);
cmd.Parameters.AddRange(paramList);
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.FieldCount == 0)
{
return;
}
int[] idxList = new int[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
ColumnInfo fi = owner.GetFieldByName(reader.GetName(i));
idxList[i] = (fi != null) ? fi.Index : -1;
}
row.Read(reader, idxList);
}
}
