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 void Raise(ImagesSelectedEventArgs e)
{
CurrentSelected = e.Selected;
if (OnInboundDocSelected != null)
{
OnInboundDocSelected(e);
}
}
private int GetNextRowId(ImagesDto pData)
{
var seqName = (pData.Type == ImagesDtoType.Inbound)
? "SEQ_INBOUND_DOCS_BLOB"
: "SEQ_ASSOCIATED_DOCS_BLOB";
var newId = DBUtils.GetNextSequence(sqlConnStr, seqName);
return(newId);
}
private static string GenerateInsertSql(ImagesDto pData)
{
var imagesDtoType = pData.Type;
var insertTSql =
String.Format(
@"INSERT INTO {0}{1}(ID, {2}, ORIG_IMAGE_FILE_EXT, MARKUP_IMAGE_FILE_EXT)
VALUES(@ID, @DOCS_ID, @ORIG_IMAGE_FILE_EXT, @MARKUP_IMAGE_FILE_EXT);
SELECT ORIG_IMAGE_BLOB.PathName(),MARKUP_IMAGE_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}{1} INBOUND_DOCS_BLOB WHERE ID = @ID",
DBUtils.SCHEMA_NAME,
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID");
return(insertTSql);
}
public Int32 Insert(string originalFileName, string markupFileName, ImagesDto pData)
{
var insertTSql = GenerateInsertSql(pData);
var newId = GetNextRowId(pData);
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
var irr = PerformRowInsert(pData, conn, insertTSql, newId);
SaveDocImageFile(markupFileName, irr.MarkupServerPath, irr.ServerTransaction);
SaveDocImageFile(originalFileName, irr.OriginalServerPath, irr.ServerTransaction);
}
ts.Complete();
}
return newId;
}
private static ImagesServerInformation PerformRowInsert(ImagesDto pData, SqlConnection conn, string insertTSql,
int newId)
{
conn.Open();
using (var cmd = new SqlCommand(insertTSql, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@DOCS_ID", SqlDbType.VarChar).Value = pData.DocsId;
cmd.Parameters.Add("@ORIG_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", ""));
cmd.Parameters.Add("@MARKUP_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", ""));
return(ReadImagesServerInformation(cmd));
}
}
public void Delete(ImagesDto pData)
{
string deleteSql = String.Format(@"DELETE from {0}{1} where ID = @ImageId",
DBUtils.SCHEMA_NAME,
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB");
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
using (var cmd = new SqlCommand(deleteSql, conn))
{
cmd.Parameters.AddWithValue("@ImageId", pData.ImageId);
cmd.ExecuteNonQuery();
}
}
}
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);
}
}
public Int32 Insert(string originalFileName, string markupFileName, ImagesDto pData)
{
var insertTSql = GenerateInsertSql(pData);
var newId = GetNextRowId(pData);
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
var irr = PerformRowInsert(pData, conn, insertTSql, newId);
SaveDocImageFile(markupFileName, irr.MarkupServerPath, irr.ServerTransaction);
SaveDocImageFile(originalFileName, irr.OriginalServerPath, irr.ServerTransaction);
}
ts.Complete();
}
return(newId);
}
public void Update(ImagesDto pData)
{
var updateSql = String.Format(
@"UPDATE {0}{1}
set {2} = @DocsId,
ORIG_IMAGE_FILE_EXT = @OrigImageFileExt,
MARKUP_IMAGE_FILE_EXT = @MarkupImageFileExt
where ID = @Id;
SELECT
ORIG_IMAGE_BLOB.PathName(),
MARKUP_IMAGE_BLOB.PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}{1} WHERE ID = @Id" ,
DBUtils.SCHEMA_NAME,
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID");
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
ImagesServerInformation imagesServerInfo;
using (var cmd = new SqlCommand(updateSql, conn))
{
cmd.Parameters.AddWithValue("@Id", pData.ImageId);
cmd.Parameters.AddWithValue("@DocsId", pData.DocsId);
cmd.Parameters.AddWithValue("@OrigImageFileExt",
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", "")));
cmd.Parameters.AddWithValue("@MarkupImageFileExt",
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", "")));
imagesServerInfo = ReadImagesServerInformation(cmd);
}
SaveDocImage(pData.MarkupImage, imagesServerInfo.MarkupServerPath,
imagesServerInfo.ServerTransaction);
SaveDocImage(pData.OriginalImage, imagesServerInfo.OriginalServerPath,
imagesServerInfo.ServerTransaction);
}
ts.Complete();
}
}
private static string GenerateInsertSql(ImagesDto pData)
{
var imagesDtoType = pData.Type;
var insertTSql =
String.Format(
@"INSERT INTO {0}{1}(ID, {2}, ORIG_IMAGE_FILE_EXT, MARKUP_IMAGE_FILE_EXT)
VALUES(@ID, @DOCS_ID, @ORIG_IMAGE_FILE_EXT, @MARKUP_IMAGE_FILE_EXT);
SELECT ORIG_IMAGE_BLOB.PathName(),MARKUP_IMAGE_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}{1} INBOUND_DOCS_BLOB WHERE ID = @ID",
DBUtils.SCHEMA_NAME,
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
(imagesDtoType == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID");
return insertTSql;
}
private int GetNextRowId(ImagesDto pData)
{
var seqName = (pData.Type == ImagesDtoType.Inbound)
? "SEQ_INBOUND_DOCS_BLOB"
: "SEQ_ASSOCIATED_DOCS_BLOB";
var newId = DBUtils.GetNextSequence(sqlConnStr, seqName);
return newId;
}
private static ImagesServerInformation PerformRowInsert(ImagesDto pData, SqlConnection conn, string insertTSql,
int newId)
{
conn.Open();
using (var cmd = new SqlCommand(insertTSql, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@DOCS_ID", SqlDbType.VarChar).Value = pData.DocsId;
cmd.Parameters.Add("@ORIG_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", ""));
cmd.Parameters.Add("@MARKUP_IMAGE_FILE_EXT", SqlDbType.VarChar).Value =
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", ""));
return ReadImagesServerInformation(cmd);
}
}
public ImagesSelectedEventArgs(ImagesDto selected, bool canEditImage = true)
{
Selected = selected;
CanEditImage = canEditImage;
}
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;
}
}
public void Test_ImagesDal()
{
messageSeqNo = 0;
const string TEST_DOC_01 = @"C:\Users\ifrankel\AppDev\VS2013Projects\DocFlow\DBAccess.SqlServer.Test\TestData\Test_Image_01.tif";
const string TEST_DOC_02 = @"C:\Users\ifrankel\AppDev\VS2013Projects\DocFlow\DBAccess.SqlServer.Test\TestData\Test_Image_02.tif";
const string TEST_DOC_03 = @"C:\Users\ifrankel\AppDev\VS2013Projects\DocFlow\DBAccess.SqlServer.Test\TestData\Test_Image_03.tif";
ImagesDal inbImgsDal = new ImagesDal(sqlConnectionIntegratedSecurityString);
ImagesDto inbImgsData = new ImagesDto(9001, null, "TIF", null, "TIF", ImagesDtoType.Inbound );
Int32 imageId = inbImgsDal.Insert(TEST_DOC_01, TEST_DOC_01, inbImgsData);
Assert.IsTrue(imageId > 0, getMessage("Insert Test Image."));
}
public void Update(ImagesDto pData)
{
var updateSql = String.Format(
@"UPDATE {0}{1}
set {2} = @DocsId,
ORIG_IMAGE_FILE_EXT = @OrigImageFileExt,
MARKUP_IMAGE_FILE_EXT = @MarkupImageFileExt
where ID = @Id;
SELECT
ORIG_IMAGE_BLOB.PathName(),
MARKUP_IMAGE_BLOB.PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}{1} WHERE ID = @Id",
DBUtils.SCHEMA_NAME,
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB",
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID");
using (var ts = new TransactionScope())
{
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
ImagesServerInformation imagesServerInfo;
using (var cmd = new SqlCommand(updateSql, conn))
{
cmd.Parameters.AddWithValue("@Id", pData.ImageId);
cmd.Parameters.AddWithValue("@DocsId", pData.DocsId);
cmd.Parameters.AddWithValue("@OrigImageFileExt",
DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", "")));
cmd.Parameters.AddWithValue("@MarkupImageFileExt",
DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", "")));
imagesServerInfo = ReadImagesServerInformation(cmd);
}
SaveDocImage(pData.MarkupImage, imagesServerInfo.MarkupServerPath,
imagesServerInfo.ServerTransaction);
SaveDocImage(pData.OriginalImage, imagesServerInfo.OriginalServerPath,
imagesServerInfo.ServerTransaction);
}
ts.Complete();
}
}
public static TifImage GetMarkupTifImage(this ImagesDto thisDto)
{
return(new TifImage(thisDto.MarkupImage));
}
public void Delete(ImagesDto pData)
{
string deleteSql = String.Format(@"DELETE from {0}{1} where ID = @ImageId",
DBUtils.SCHEMA_NAME,
(pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB");
using (var conn = new SqlConnection(sqlConnStr))
{
conn.Open();
using (var cmd = new SqlCommand(deleteSql, conn))
{
cmd.Parameters.AddWithValue("@ImageId", pData.ImageId);
cmd.ExecuteNonQuery();
}
}
}
