public List <AVPair> GetAVPairs()
{
List <AVPair> avPairs = new List <AVPair>();
string commandText = "select [Id],[Key],[Value],[CreatedBy],[CreatedTS],[ModifiedBy],[ModifiedTS],[Source] from [AVPair]";
DataTable table = ExecuteCommand(commandText);
if (table == null)
{
return(avPairs);
}
foreach (DataRow row in table.Rows)
{
AVPair avPair = new AVPair();
avPair.Id = (int)row["Id"].ToString().ParseInt();
avPair.Key = row["Key"] != DBNull.Value ? row["Key"].ToString() : null;
avPair.Value = row["Value"] != DBNull.Value ? row["Value"].ToString() : null;
avPair.CreatedBy = row["CreatedBy"] != DBNull.Value ? row["CreatedBy"].ToString() : null;
avPair.CreatedTS = (DateTime)(row["CreatedTS"] != DBNull.Value ? row["CreatedTS"].ToString().ParseDateTime() : DateTime.MinValue);
avPair.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? row["ModifiedBy"].ToString() : null;
avPair.ModifiedTS = row["ModifiedTS"] != DBNull.Value ? row["ModifiedTS"].ToString().ParseDateTime() : null;
avPairs.Add(avPair);
}
table.Dispose();
return(avPairs);
}
public void Save(AVPair avPair)
{
IDal myDal = new DataAccessLayer(EyediaCoreConfigurationSection.CurrentConfig.Database.DatabaseType).Instance;
IDbConnection conn = myDal.CreateConnection(_ConnectionString);
conn.Open();
IDbTransaction transaction = myDal.CreateTransaction(conn);
IDbCommand command = myDal.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
try
{
if (avPair.Id == 0)
{
command.CommandText = "INSERT INTO [AVPair] ([Key], [Value], [CreatedBy], [CreatedTS], [Source]) ";
command.CommandText += " VALUES (@Key, @Value, @CreatedBy, @CreatedTS, @Source)";
command.AddParameterWithValue("Key", avPair.Key);
command.AddParameterWithValue("Value", avPair.Value);
command.AddParameterWithValue("CreatedBy", avPair.CreatedBy);
command.AddParameterWithValue("CreatedTS", DateTime.Now);
command.AddParameterWithValue("Source", GetSource(avPair.Source));
command.ExecuteNonQuery();
//Deb: I know dirty coding, need to be changed. OUTPUT INSERTED.Id not working @SQL CE
command.Parameters.Clear();
command.CommandText = "SELECT max(Id) from [AVPair]";
int newCodeSetId = (Int32)command.ExecuteScalar();
}
else
{
command.CommandText = "UPDATE [AVPair] SET [Key] = @Key, [Value] = @Value, ModifiedBy = @ModifiedBy, ModifiedTS = @ModifiedTS, Source = @Source";
command.CommandText += "WHERE [Id] = @Id";
command.AddParameterWithValue("Key", avPair.Key);
command.AddParameterWithValue("Value", avPair.Value);
command.AddParameterWithValue("ModifiedBy", avPair.ModifiedBy);
command.AddParameterWithValue("ModifiedTS", DateTime.Now);
command.AddParameterWithValue("Source", GetSource(avPair.Source));
command.AddParameterWithValue("Id", avPair.Id);
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message, ex);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
transaction.Dispose();
}
}
public string GetAVPairValue(string key)
{
AVPair avPair = this.AVPairs.Where(av => av.Key.Equals(key, StringComparison.OrdinalIgnoreCase)).SingleOrDefault();
return(avPair == null ? string.Empty : avPair.Value);
}
