protected List <InvoiceDetailFileTypeItem> ConvertDataReaderToList(OracleDataReader dr)
{
List <InvoiceDetailFileTypeItem> items = new List <InvoiceDetailFileTypeItem>();
while (dr.Read())
{
InvoiceDetailFileTypeItem itm = new InvoiceDetailFileTypeItem();
itm.Description = dr["descr"].ToString();
itm.FileExtension = dr["extension"].ToString();
itm.Id = dr["file_type"].ToString();
itm.IsAvailable = (dr["file_type"].ToString() == "Y");
itm.Sql = dr["sql"].ToString();
items.Add(itm);
}
return(items);
}
public override string Save(InvoiceDetailFileTypeItem item)
{
using (OracleConnection cnn = new OracleConnection(base.ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("declare \r\n"
+ " cnt number; \r\n"
+ "begin \r\n"
+ " select count(*) \r\n"
+ " into cnt \r\n"
+ " from {0}.file_type \r\n"
+ " where file_type = :fileType; \r\n"
+ "\r\n"
+ " if( cnt = 0 ) then \r\n"
+ " insert into {0}.file_type (file_type, descr, is_available, extension, sql) \r\n"
+ " values (:fileType, :descr, :isAvailable, :fileExtension, :sql); \r\n"
+ " else \r\n"
+ " update {0}.file_type \r\n"
+ " set descr = :descr \r\n"
+ " ,is_available = :isAvailable \r\n"
+ " ,extension = :fileExtension \r\n"
+ " ,sql = :sql \r\n"
+ " where file_type = :fileType; \r\n"
+ " end if; \r\n"
+ "end; \r\n"
+ "commit;"
, base.SchemaName);
OracleHelper.AddCommandParameter(cmd, ":fileType", item.Id, OracleType.Char, 4);
OracleHelper.AddCommandParameter(cmd, ":descr", item.Description, OracleType.VarChar, 100);
char isAvailable = item.IsAvailable ? 'Y' : 'N';
OracleHelper.AddCommandParameter(cmd, ":isAvailable", isAvailable, OracleType.Char, 1);
OracleHelper.AddCommandParameter(cmd, ":fileExtension", item.FileExtension, OracleType.VarChar, 4);
OracleHelper.AddCommandParameter(cmd, ":sql", item.Sql, OracleType.VarChar, 4000);
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
return(item.Id);
}
}
