public Attachments(int givenId, int givenRevNo, AttachmentsTableName attTable)
{
InitializeComponent();
glAttTable = attTable;
string[] fileNames = { };
RevNo = givenRevNo;
if (attTable == AttachmentsTableName.Audit_Attachments)
{
AuditId = givenId;
fileNames = getSavedAttachments(AuditId, RevNo, AttachmentsTableName.Audit_Attachments);
}
else if (attTable == AttachmentsTableName.FIDetail_Attachments)
{
DetailId = givenId;
fileNames = getSavedAttachments(DetailId, RevNo, AttachmentsTableName.FIDetail_Attachments);
}
foreach (string thisFileName in fileNames)
{
lvAttachedFiles.Items.Add(new ListViewItem(thisFileName));
}
AttCnt = fileNames.Length;
}
private bool UpdateAuditOnAttSave(int id, AttachmentsTableName attTable)
{
bool ret = false;
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string InsSt = "";
if (attTable == AttachmentsTableName.Audit_Attachments)
{
InsSt = "UPDATE [dbo].[Audit] SET [UpdUserID] = @UpdUserID, [UpdDt] = getDate(), [RevNo] = RevNo+1, [UseUpdTrigger] = 1 " +
"WHERE id=@id";
}
else if (attTable == AttachmentsTableName.FIDetail_Attachments)
{
InsSt = "UPDATE [dbo].[FIDetail] SET [UpdUserID] = @UpdUserID, [UpdDt] = getDate(), [RevNo] = RevNo+1, [UseUpdTrigger] = 1 " +
"WHERE id=@id";
}
try
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@UpdUserID", UserInfo.userDetails.Id);
cmd.CommandType = CommandType.Text;
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
ret = true;
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
sqlConn.Close();
return(ret);
}
public string[] getSavedAttachments(int tableId, int revNo, AttachmentsTableName attTable)
{
List <string> ret = new List <string>();
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string SelectSt = "";
if (attTable == AttachmentsTableName.Audit_Attachments)
{
SelectSt = "SELECT Name FROM [dbo].[Audit_Attachments] WHERE AuditId = " + tableId.ToString() + " AND RevNo = " + revNo.ToString();
}
else if (attTable == AttachmentsTableName.FIDetail_Attachments)
{
SelectSt = "SELECT Name FROM [dbo].[FIDetail_Attachments] WHERE FIDetailId = " + tableId.ToString() + " AND RevNo = " + revNo.ToString();
}
SqlCommand cmd = new SqlCommand(SelectSt, sqlConn);
try
{
sqlConn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ret.Add(reader["Name"].ToString().Trim());
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
return(ret.ToArray());
}
private bool InertIntoTable_AttachedFiles(int Id, int RevNo, string fileName, byte[] fileBytes, AttachmentsTableName attTable) //INSERT [dbo].[Audit_Attachments]
{
bool ret = false;
if (Id > 0 && RevNo > 0 && fileName.Trim().Length > 0)
{
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string InsSt = "";
if (attTable == AttachmentsTableName.Audit_Attachments)
{
InsSt = "INSERT INTO [dbo].[Audit_Attachments] (Name, FileContents, AuditId, RevNo, UsersId, InsDate) VALUES " +
"(@Filename, @FileCont, @Id, @RevNo, @UsersId, getdate() ) ";
}
else if (attTable == AttachmentsTableName.FIDetail_Attachments)
{
InsSt = "INSERT INTO [dbo].[FIDetail_Attachments] (Name, FileContents, FIDetailId, RevNo, UsersId, InsDate) VALUES " +
"(@Filename, @FileCont, @Id, @RevNo, @UsersId, getdate() ) ";
}
try
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
cmd.Parameters.AddWithValue("@Id", Id);
cmd.Parameters.AddWithValue("@RevNo", RevNo);
cmd.Parameters.AddWithValue("@UsersId", UserInfo.userDetails.Id);
cmd.Parameters.AddWithValue("@Filename", fileName);
cmd.Parameters.Add("@FileCont", SqlDbType.VarBinary).Value = fileBytes;
cmd.CommandType = CommandType.Text;
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
ret = true;
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
}
return(ret);
}
//private bool UpdateAttachments_IsCurrent(int auditId, int revNo)
//{
// bool ret = false;
// SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
// string InsSt = "UPDATE [dbo].[Attachments] SET [IsCurrent] = 0 WHERE AuditId = @id AND RevNo = @RevNo";
// try
// {
// sqlConn.Open();
// SqlCommand cmd = new SqlCommand(InsSt, sqlConn);
// cmd.Parameters.AddWithValue("@id", auditId);
// cmd.Parameters.AddWithValue("@RevNo", revNo);
// cmd.CommandType = CommandType.Text;
// cmd.ExecuteNonQuery();
// ret = true;
// }
// catch (Exception ex)
// {
// MessageBox.Show("The following error occurred: " + ex.Message);
// }
// sqlConn.Close();
// return ret;
//}
LvFileInfo saveAttachmentLocally(int Id, int RevNo, string Filename, AttachmentsTableName attTable)
{
LvFileInfo ret = new LvFileInfo();
string tempPath = Path.GetTempPath(); //C:\Users\hkylidis\AppData\Local\Temp\
try
{
if (!Directory.Exists(tempPath))
{
MessageBox.Show("Error. Please check your privileges on " + tempPath);
}
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
return(ret);
}
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string SelectSt = "";
if (attTable == AttachmentsTableName.Audit_Attachments)
{
SelectSt = "SELECT [Name], [FileContents] FROM [dbo].[Audit_Attachments] WHERE AuditId = @Id and RevNo = @RevNo and Name = @Filename ";
}
else if (attTable == AttachmentsTableName.FIDetail_Attachments)
{
SelectSt = "SELECT [Name], [FileContents] FROM [dbo].[FIDetail_Attachments] WHERE FIDetailId = @Id and RevNo = @RevNo and Name = @Filename ";
}
SqlCommand cmd = new SqlCommand(SelectSt, sqlConn);
try
{
sqlConn.Open();
cmd.Parameters.AddWithValue("@Id", Id);
cmd.Parameters.AddWithValue("@RevNo", RevNo);
cmd.Parameters.AddWithValue("@Filename", Filename);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string realFileName = reader["Name"].ToString().Trim();
//string tempFile = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + "~" + realFileName);
//temp file -> attachment name with temp name and tilda 'tmp123~ΦΕΚ123.pdf'
string tempFile = Path.Combine(tempPath, realFileName);
try
{
File.WriteAllBytes(tempFile, (byte[])reader["FileContents"]);
}
catch (Exception ex)
{
MessageBox.Show("An error occured while saving temporarilly the attached file: '" + realFileName +
"'\r\n\r\n\r\nDetails:\r\n" + ex.Message);
try
{
tempFile = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + "~" + realFileName);
File.WriteAllBytes(tempFile, (byte[])reader["FileContents"]);
MessageBox.Show("Caution! File will be saved as: " + tempFile);
}
catch (Exception ex2)
{
MessageBox.Show("Caution! File " + realFileName + " will not be saved!\r\n" + ex2.Message);
}
}
ret = new LvFileInfo {
FileName = realFileName, FilePath = tempFile
};
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
return(ret);
}
return(ret);
}
