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); }