public ImagesDto GetByDocId(Int32 docsId, ImagesDtoType imagesDtoType)
{
ImagesDto imagesDto = null;
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
var selectTSqlCmd = GetSelectByDocIdSql(imagesDtoType);
using (var cmd = new SqlCommand(selectTSqlCmd, conn))
{
cmd.Parameters.AddWithValue("@DocsId", docsId);
using (var dataReader = cmd.ExecuteReader())
{
if (dataReader.Read())
{
var serverTxn = dataReader.GetSqlBinary(4).Value;
imagesDto = new ImagesDto(
DBUtils.HandleInt32IfNull(dataReader["DOCS_ID"].ToString()),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["MARKUP_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["MARKUP_IMAGE_FILE_EXT"]),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["ORIG_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["ORIG_IMAGE_FILE_EXT"]),
imagesDtoType,
Convert.ToInt32(dataReader["ID"])
);
}
}
}
}
ts.Complete();
}
return(imagesDto);
}
public ImagesDto GetByDocId(Int32 docsId, ImagesDtoType imagesDtoType)
{
ImagesDto imagesDto = null;
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
var selectTSqlCmd = GetSelectByDocIdSql(imagesDtoType);
using (var cmd = new SqlCommand(selectTSqlCmd, conn))
{
cmd.Parameters.AddWithValue("@DocsId", docsId);
using (var dataReader = cmd.ExecuteReader())
{
if (dataReader.Read())
{
var serverTxn = dataReader.GetSqlBinary(4).Value;
imagesDto = new ImagesDto(
DBUtils.HandleInt32IfNull(dataReader["DOCS_ID"].ToString()),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["MARKUP_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["MARKUP_IMAGE_FILE_EXT"]),
ReadBytesFromSqlFileStream(Convert.ToString(dataReader["ORIG_IMAGE_PATH_NAME"]), serverTxn),
Convert.ToString(dataReader["ORIG_IMAGE_FILE_EXT"]),
imagesDtoType,
Convert.ToInt32(dataReader["ID"])
);
}
}
}
}
ts.Complete();
}
return imagesDto;
}
public ImagesDto(Int32 docsId, byte[] markupImage, string markupImageFileExt, byte[] originalImage, string originalImageFileExt, ImagesDtoType type, Int32 imageId = 0)
{
DocsId = docsId;
MarkupImage = markupImage;
MarkupImageFileExt = markupImageFileExt;
OriginalImage = originalImage;
OriginalImageFileExt = originalImageFileExt;
Type = type;
ImageId = imageId;
}
public ImagesDto(Int32 docsId, byte[] markupImage, string markupImageFileExt, byte[] originalImage, string originalImageFileExt, ImagesDtoType type, Int32 imageId = 0)
{
DocsId = docsId;
MarkupImage = markupImage;
MarkupImageFileExt = markupImageFileExt;
OriginalImage = originalImage;
OriginalImageFileExt = originalImageFileExt;
Type = type;
ImageId = imageId;
}
public ImagesDto SwitchImagesDtoType(ImagesDto pData, Int32 newDocId)
{
using (var ts = new TransactionScope())
{
ImagesDtoType newType = pData.Type == ImagesDtoType.Inbound
? ImagesDtoType.Associated
: ImagesDtoType.Inbound;
ImagesDto newDto = new ImagesDto(newDocId, pData.MarkupImage, pData.MarkupImageFileExt, pData.OriginalImage, pData.OriginalImageFileExt, newType);
Insert(newDto);
Delete(pData);
ts.Complete();
return(newDto);
}
}
private string GetSelectByDocIdSql(ImagesDtoType imagesDtoType)
{
var docsIdFieldName = (imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID";
return(string.Format(@"SELECT
ID,
{0} as DOCS_ID,
ORIG_IMAGE_FILE_EXT,
ORIG_IMAGE_BLOB.PathName() as ORIG_IMAGE_PATH_NAME,
GET_FILESTREAM_TRANSACTION_CONTEXT(),
MARKUP_IMAGE_FILE_EXT,
MARKUP_IMAGE_BLOB.PathName() as MARKUP_IMAGE_PATH_NAME
FROM {1}{2} WHERE {3} = @DocsId ",
docsIdFieldName,
DBUtils.SCHEMA_NAME,
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
docsIdFieldName));
}
private string GetSelectByDocIdSql(ImagesDtoType imagesDtoType)
{
var docsIdFieldName = (imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID";
return string.Format(@"SELECT
ID,
{0} as DOCS_ID,
ORIG_IMAGE_FILE_EXT,
ORIG_IMAGE_BLOB.PathName() as ORIG_IMAGE_PATH_NAME,
GET_FILESTREAM_TRANSACTION_CONTEXT(),
MARKUP_IMAGE_FILE_EXT,
MARKUP_IMAGE_BLOB.PathName() as MARKUP_IMAGE_PATH_NAME
FROM {1}{2} WHERE {3} = @DocsId ",
docsIdFieldName,
DBUtils.SCHEMA_NAME,
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
docsIdFieldName);
}
