public AddCollectionResponse UpdateCollection(AddCollectionRequest request)
{
if (request == null || request.Collection == null)
{
return(new AddCollectionResponse
{
Message = "Could not add collection",
ResponseType = ResponseType.Succeeded
});
}
string oradb = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update collection set title = '" + request.Collection.Title + "', description = '" + request.Collection.Description + "', isprivate = '" + Convert.ToInt32(request.Collection.IsPrivate).ToString() + "' where id = " + request.Collection.ID;
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
GetCollectionsResponse response = new GetCollectionsResponse();
conn.Dispose();
conn.Close();
return(new AddCollectionResponse
{
ResponseType = ResponseType.Succeeded
});
}
public AddCollectionResponse SaveCollection(AddCollectionRequest request)
{
if (request == null || request.Collection == null)
{
return(new AddCollectionResponse
{
Message = "Could not add collection",
ResponseType = ResponseType.Succeeded
});
}
string oradb = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into collection (id, title, description, isprivate, createdby, datecreated, modifiedby, datemodified, isdeleted) values(collection_seq.NEXTVAL,'" + request.Collection.Title + "', '" + request.Collection.Description + "', '" + Convert.ToInt32(request.Collection.IsPrivate).ToString() + "', '1', sysdate, '1', sysdate, '0')";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
GetCollectionsResponse response = new GetCollectionsResponse();
conn.Dispose();
conn.Close();
return(new AddCollectionResponse
{
ResponseType = ResponseType.Succeeded
});
}
public GetCollectionsResponse GetCollections()
{
string oradb = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select distinct * from collection";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
GetCollectionsResponse response = new GetCollectionsResponse();
List <Collection> collections = new List <Collection>();
while (dr.HasRows && dr.Read())
{
int id = 0;
bool isPrivate;
Int32.TryParse(dr[0].ToString(), out id);
if (dr[3].ToString() == "0")
{
isPrivate = false;
}
else
{
isPrivate = true;
}
if (id == 0)
{
response.ResponseType = ResponseType.Failed;
response.Message = "Could not get Collections or the list is empty";
return(response);
}
Collection collection = new Collection
{
ID = id,
Title = dr[1].ToString(),
Description = dr[2].ToString(),
IsPrivate = isPrivate,
Items = new List <Item>() // Items
};
collections.Add(collection);
}
conn.Close();
OracleConnection conn2 = new OracleConnection(oradb);
conn2.Open();
cmd.Connection = conn2;
cmd.CommandText = "select * from metadata";
List <Metadata> metaList = new List <Metadata>();
OracleDataReader drMetadata = cmd.ExecuteReader();
while (drMetadata.HasRows && drMetadata.Read())
{
//(id, itemid, documenttypeid, author,abstract, publisher, language,url, rights,datepublished,extra)
int id, itemId, documentTypeId;
Int32.TryParse(drMetadata[0].ToString(), out id);
Int32.TryParse(drMetadata[1].ToString(), out itemId);
Int32.TryParse(drMetadata[2].ToString(), out documentTypeId);
Metadata metaData = new Metadata()
{
ID = id,
ItemId = itemId,
DocumentTypeId = documentTypeId,
Author = drMetadata[3].ToString(),
Abstract = drMetadata[4].ToString(),
Publisher = drMetadata[5].ToString(),
Language = drMetadata[6].ToString(),
Url = drMetadata[7].ToString(),
Rights = drMetadata[8].ToString(),
Date = Convert.ToDateTime(drMetadata[9]),
Extra = drMetadata[10].ToString()
};
metaList.Add(metaData);
}
cmd.CommandText = "select distinct * from item";
OracleDataReader drItems = cmd.ExecuteReader();
List <Item> items = new List <Item>();
while (drItems.HasRows && drItems.Read())
{
int id, collectionId, documentTypeId;
bool isPrivate;
Int32.TryParse(drItems[0].ToString(), out id);
Int32.TryParse(drItems[1].ToString(), out collectionId);
Int32.TryParse(drItems[2].ToString(), out documentTypeId);
bool.TryParse(drItems[3].ToString(), out isPrivate);
Item item = new Item
{
ID = id,
CollectionId = collectionId,
DocumentTypeId = documentTypeId,
IsPrivate = isPrivate,
Title = drItems[4].ToString(),
Metadata = metaList.FirstOrDefault(x => x.ItemId == id)
};
items.Add(item);
}
foreach (var collection in collections)
{
var itemsList = items.Where(x => x.CollectionId == collection.ID).ToList();
if (itemsList.Count > 0)
{
collection.Items.AddRange(itemsList);
}
}
response.collections = collections;
conn2.Dispose();
return(response);
}
