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); }