private void LoadData(NpgsqlDataReader reader)
{
Items = new List <OHResultItem>();
while (reader.Read())
{
OHResultItem item = new OHResultItem();
item.OperationID = int.Parse(reader["OperationID"].ToString());
item.PropertyName = reader["PropertyName"].ToString();
item.PropertyValue = reader["PropertyValue"].ToString();
item.PropertyType = reader["PropertyType"].ToString();
item.ObjectName = reader["ObjectName"].ToString();
item.ObjectType = reader["ObjectType"].ToString();
item.TimeStamp = DateTime.Parse(reader["LastModified"].ToString());
item.User = reader["User"].ToString();
// Guid.NewGuid().ToString();
item.Level = reader["Level"].ToString();
Items.Add(item);
}
}
public DataTable AsDataTable()
{
var table = new DataTable();
var list = AsList();
var columns = list.Select(p => p.PropertyName).Distinct();
var operations = list.Select(p => p.OperationID).Distinct();
foreach (var col in columns)
{
table.Columns.Add(col, typeof(string));
}
table.Columns.Add("User", typeof(string));
table.Columns.Add("TimeStamp", typeof(string));
if (Merge)
{
DataRow nrow = table.NewRow();
foreach (var property in columns)
{
nrow[property] = list.Find(p => p.PropertyName == property).PropertyValue;
}
OHResultItem tItem = list.Find(p => p.TimeStamp == list.Max(ml => ml.TimeStamp));
nrow ["User"] = tItem.User;
nrow["TimeStamp"] = tItem.TimeStamp;
table.Rows.Add(nrow);
}
else
{
foreach (var operation in operations)
{
DataRow nrow = table.NewRow();
var changes = list.FindAll(p => p.OperationID == operation).ToList();
foreach (var change in changes)
{
nrow[change.PropertyName] = change.PropertyValue;
}
OHResultItem tItem = list.Find(p => p.OperationID == operation);
nrow["User"] = tItem.User;
nrow["TimeStamp"] = tItem.TimeStamp;
table.Rows.Add(nrow);
}
}
return(table);
}
public override void SaveObject(OHInfo objInfo, OHTarget target)
{
OHSqlClient sqlClient = new OHSqlClient(target);
var keyPropertyValue = objInfo.LoggableProperties.Find(p => p.Name.Equals(objInfo.KeyPropertyName)).Value;
List <OHResultItem> lastVersion = sqlClient.GetLastVersion(objInfo.ObjectType, keyPropertyValue).AsList();
DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);
NpgsqlConnection conn = new NpgsqlConnection(target.ConnectionString);
NpgsqlCommand comm = new NpgsqlCommand();
comm = conn.CreateCommand();
comm.CommandType = System.Data.CommandType.Text;
string script = string.Empty;
script += "DO $$ \n";
script += @" DECLARE OperationID integer; ";
script += @"DECLARE ObjectLogID integer; ";
script += "BEGIN \n";
script += @"INSERT INTO ""Operations""(""User"", ""Guid"", ""PcName"", ""TimeStamp"", ""KeyValue"") VALUES('YEK', '" + Guid.NewGuid() + "', '" + Dns.GetHostName() + "', now(), '" + keyPropertyValue + "') RETURNING \"OperaionID\" INTO OperationID; \n";
script += @"ObjectLogID := (SELECT ""ObjectLogID"" FROM ""ObjectLogs"" WHERE ""Name"" LIKE '" + objInfo.ObjectName + "' AND \"ObjectType\" LIKE '" + objInfo.ObjectType.ToString() + "' LIMIT 1); \n";
script += @"IF ObjectLogID ISNULL THEN ";
script += @"INSERT INTO ""ObjectLogs""(""Name"", ""ObjectType"") VALUES('" + objInfo.ObjectName + "', '" + objInfo.SourceObject + "') RETURNING \"ObjectLogID\" INTO ObjectLogID; \n";
script += "END IF; \n";
script += "END $$; \n";
bool hasChanges = false;
foreach (var prop in objInfo.LoggableProperties)
{
OHResultItem LastVersionPropInfo = lastVersion.Find(p => p.PropertyName.Equals(prop.Name));
string oldValue = LastVersionPropInfo == null ? null : LastVersionPropInfo.PropertyValue;
string newValue = prop.Value;
if (objInfo.AppendType == AppendTypes.Incremental || newValue != oldValue)
{
hasChanges = true;
string dbValue = prop.Value == null ? "NULL" : string.Format("'{0}'", prop.Value);
script += @"INSERT INTO ""ObjectLogDetails"" (""ObjectLogID"" ,""OperationID"" ,""LevelID"" ,""PropertyName"" ,""PropertyValue"",""PropertyType"") VALUES ((Select ""ObjectLogID"" From ""ObjectLogs"" Where ""ObjectType""='" + objInfo.SourceObject + "'), (Select \"OperaionID\" From\"Operations\" Order By \"OperaionID\" Desc LIMIT 1), (Select \"LevelID\" From \"Levels\" Order By \"LevelID\" Desc LIMIT 1),'" + prop.Name + "', " + dbValue + ", '" + prop.Type + "');";
}
}
if (!hasChanges)
{
return;
}
comm.CommandText = string.Format(script, target.DBTablesPrefix);
comm.Parameters.Add(new NpgsqlParameter("@KeyValue", objInfo.LoggableProperties.Find(p => p.Name.Equals(objInfo.KeyPropertyName)).Value));
comm.Parameters.Add(new NpgsqlParameter("@User", string.IsNullOrEmpty(objInfo.User) ? string.Empty : objInfo.User));
comm.Parameters.Add(new NpgsqlParameter("@Guid", objInfo.Guid));
comm.Parameters.Add(new NpgsqlParameter("@PcName", string.IsNullOrEmpty(objInfo.PcName) ? string.Empty : objInfo.PcName));
comm.Parameters.Add(new NpgsqlParameter("@TimeStamp", objInfo.TimeStamp));
comm.Parameters.Add(new NpgsqlParameter("@ObjectName", objInfo.ObjectName));
comm.Parameters.Add(new NpgsqlParameter("@ObjectType", objInfo.ObjectType.ToString()));
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
