private bool DoesPossibleDuplicateExists()
{
string query = @"Select TOP 1 1 FROM tbl_DocumentInfo WITH (NOLOCK) WHERE DocumentType =@DocumentType AND Author = @Author AND CreatedDate = @CreatedDate";
try
{
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@CreatedDate", SqlDbType.NVarChar).Value = dateTimePickerCreatedDate.Value;
command.Parameters.Add("@DocumentType", SqlDbType.BigInt).Value = comboBoxDocumentType.SelectedValue;
command.Parameters.Add("@Author", SqlDbType.BigInt).Value = comboBoxAuthor.SelectedValue;
object value = DBSupport.ExecuteScalar(command);
if (value == null || value == DBNull.Value)
{
return(false);
}
else
{
return(true);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(textBoxDocumentType.Text))
{
ShowMessageBox("Please insert document type.");
return;
}
string documentType = textBoxDocumentType.Text.Trim();
string query = @" IF NOT EXISTS(SELECT * FROM tbl_DocumentType WHERE Name = @Name)
INSERT INTO tbl_DocumentType (Name , InsertedDate) values (@Name,GetDate()) ";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = documentType;
long documentTypeId = DBSupport.ExecuteNonQueryWithIdentity(cmd);
if (documentTypeId == -1)
{
ShowMessageBox("Document type already exists. Please enter another type.");
}
else if (documentTypeId > 0)
{
this.DocumentId = documentTypeId;
this.Close();
}
}
}
private DataTable GetAuthors(long publisher, long documentType)
{
string query = "SELECT Id, Name FROM tbl_AuthorInfo WITH (NOLOCK)";
if (publisher > 0 && documentType > 0)
{
query = @"SELECT DISTINCT AI.Id, AI.Name
FROM tbl_AuthorInfo AI WITH (NOLOCK)
, tbl_DocumentInfo DI WITH (NOLOCK)
WHERE AI.ID = DI.Author AND DI.Publisher = @Publisher AND DI.DocumentType = @DocumentType
ORDER BY AI.Name";
}
else if (documentType > 0)
{
query = @"SELECT DISTINCT AI.Id, AI.Name
FROM tbl_AuthorInfo AI WITH (NOLOCK)
, tbl_DocumentInfo DI WITH (NOLOCK)
WHERE AI.ID = DI.Author AND DI.DocumentType = @DocumentType
ORDER BY AI.Name";
}
else if (publisher > 0)
{
query = @"SELECT DISTINCT AI.Id, AI.Name
FROM tbl_AuthorInfo AI WITH (NOLOCK)
, tbl_DocumentInfo DI WITH (NOLOCK)
WHERE AI.ID = DI.Author AND DI.Publisher = @Publisher
ORDER BY AI.Name";
}
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@DocumentType", SqlDbType.BigInt).Value = documentType;
cmd.Parameters.Add("@Publisher", SqlDbType.BigInt).Value = publisher;
return(DBSupport.ExecuteQueryAndGetDataTable(cmd));
}
}
private void buttonAddAuthor_Click(object sender, EventArgs e)
{
string author = comboBoxAuthor.Text.Trim();
if (string.IsNullOrWhiteSpace(author) || author.Equals("Select author", StringComparison.InvariantCultureIgnoreCase))
{
ShowMessageBox("Please add author name.");
return;
}
string query = @" IF NOT EXISTS(SELECT * FROM tbl_AuthorInfo WHERE Name = @Name)
INSERT INTO tbl_AuthorInfo (Name , InsertedDate) values (@Name,GetDate()) ";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = author;
long authorId = DBSupport.ExecuteNonQueryWithIdentity(cmd);
if (authorId == -1)
{
ShowMessageBox("Author already exists. Please enter another type.");
}
else if (authorId > 0)
{
ShowMessageBox("Author added successfully");
LoadAuthors();
comboBoxAuthor.SelectedValue = authorId;
}
}
}
static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
if (args != null && args.Length > 0)
{
DBSupport.StartUp(args[0]);
}
else
{
DBSupport.StartUp(null);
}
}
catch
{
MessageBox.Show("Could not connect to database. Program will not function properly.");
}
try
{
Application.Run(new FormMain());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Application.Exit();
}
}
public static void DropTable(string tableName)
{
string query = @"IF EXISTS (SELECT TABLE_NAME FROM TEMPDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '" + tableName + "') "
+ "DROP TABLE " + tableName;
DBSupport.ExecuteNonQuery(query);
}
private void SetTotalCount()
{
using (SqlCommand command = new SqlCommand("SELECT COUNT(1) FROM tbl_DocumentInfo WITH (NOLOCK)"))
{
object count = DBSupport.ExecuteScalar(command);
if (count != null && count != DBNull.Value)
{
labelTotalFileCount.Text = count.ToString();
}
}
}
private bool InsertImages(long documentId)
{
List <long> imageIds = new List <long>();
foreach (ListViewItem item in listViewImages.Items)
{
string sourcePath = item.SubItems[1].Text.Trim();
string query = "SELECT II.ID FROM tbl_ImageInfo(NoLock) II, tbl_DocumentImageAssociation(NoLock) DIA WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @DocumentID AND II.ImagePath = @imagePath";
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@imagePath", SqlDbType.NVarChar).Value = sourcePath;
command.Parameters.Add("@DocumentID", SqlDbType.BigInt).Value = documentId;
object value = DBSupport.ExecuteScalar(command);
string oldDirectory = Path.GetDirectoryName(sourcePath);
string documentType = comboBoxDocumentType.Text;
string fileName = textBoxNepaliDate.Text + "_" + comboBoxAuthor.Text + "_" + Guid.NewGuid().ToString().Replace("-", "") + Path.GetExtension(sourcePath);
string destinationPath = Path.Combine(ConfigValues.FileSaveRootLocation, documentType, fileName);
string newDirectory = Path.GetDirectoryName(destinationPath);
if (!oldDirectory.Equals(newDirectory, StringComparison.InvariantCultureIgnoreCase))
{
try
{
CopyFileToDestination(sourcePath, destinationPath);
command.CommandText = @"DECLARE @ImageID BigInt
INSERT INTO tbl_ImageInfo (Name, ImagePath, InsertedDate) VALUES (@fileName, @imagePath, GetDate());
SET @ImageID = SCOPE_IDENTITY()
INSERT INTO tbl_DocumentImageAssociation (DocumentId, ImageId , InsertedDate) VALUES(@DocumentID, @ImageID, GetDate())
SELECT @ImageID";
command.Parameters.Clear();
command.Parameters.Add("@imagePath", SqlDbType.NVarChar).Value = destinationPath;
command.Parameters.Add("@DocumentID", SqlDbType.BigInt).Value = documentId;
command.Parameters.Add("@fileName", SqlDbType.NVarChar).Value = fileName;
object imageId = DBSupport.ExecuteScalar(command);
imageIds.Add(long.Parse(imageId.ToString()));
}
catch (IOException ex)
{
throw new IOException("Could not save file to disk. Error :" + ex.Message, ex);
}
}
else
{
imageIds.Add(long.Parse(value.ToString()));
}
}
}
DeleteImages(documentId, imageIds);
return(true);
}
private void LoadDocumentForUpdate()
{
try
{
string query = "SELECT * FROM tbl_DocumentInfo WITH (NOLOCK) WHERE Id = @documentId";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@documentId", SqlDbType.BigInt).Value = _documentId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
textBoxName.Text = dt.Rows[0]["Name"].ToString();
textBoxTitle.Text = dt.Rows[0]["Title"].ToString();
textBoxSummary.Text = dt.Rows[0]["Summary"].ToString();
textBoxBody.Text = dt.Rows[0]["Body"].ToString();
comboBoxAuthor.SelectedValue = long.Parse(dt.Rows[0]["Author"].ToString());
comboBoxDocumentType.SelectedValue = long.Parse(dt.Rows[0]["DocumentType"].ToString());
comboBoxPublisher.SelectedValue = long.Parse(dt.Rows[0]["Publisher"].ToString());
dateTimePickerCreatedDate.Value = DateTime.Parse(dt.Rows[0]["CreatedDate"].ToString());
checkBoxDateUncertain.Checked = bool.Parse(dt.Rows[0]["DateUncertain"].ToString());
}
else
{
MessageBox.Show("Requested document could not be retrieved from database.");
this.ParentForm.Close();
}
cmd.CommandText = @"SELECT ImageId, ImagePath FROM tbl_DocumentImageAssociation(NOLOCK) DIA, tbl_ImageInfo(NoLock) II
WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @documentId";
dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
AddFileToListViewImages(dr["ImagePath"].ToString());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Could load document. Error : " + ex.Message);
this.ParentForm.Close();
}
}
private DataTable GetDocumentType(long publisher)
{
string query = "SELECT Id, Name FROM tbl_DocumentType WITH (NOLOCK) ORDER BY Name";
if (publisher > 0)
{
query = @"SELECT DISTINCT DT.Id, DT.Name
FROM tbl_DocumentType DT WITH (NOLOCK)
, tbl_DocumentInfo DI WITH (NOLOCK)
WHERE DT.ID = DI.DocumentType AND DI.Publisher = @Publisher
ORDER BY DT.Name";
}
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@Publisher", SqlDbType.BigInt).Value = publisher;
return(DBSupport.ExecuteQueryAndGetDataTable(cmd));
}
}
private void DeleteImages(long documentId, List <long> imageIds)
{
imageIds.Add(0);
string deleteQuery = @"
SELECT ImagePath FROM tbl_ImageInfo II, tbl_DocumentImageAssociation DIA WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @DocumentID AND II.ID NOT IN(" + string.Join(",", imageIds) + @")
DELETE FROM II FROM tbl_ImageInfo II, tbl_DocumentImageAssociation DIA WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @DocumentID AND II.ID NOT IN(" + string.Join(",", imageIds) + @")
DELETE FROM tbl_DocumentImageAssociation WHERE DocumentId = @documentId AND ImageId NOT IN(" + string.Join(",", imageIds) + @")";
using (SqlCommand cmd = new SqlCommand(deleteQuery))
{
cmd.Parameters.Add("@documentId", SqlDbType.BigInt).Value = documentId;
DataTable filesToDelete = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (filesToDelete != null && filesToDelete.Rows.Count > 0)
{
foreach (DataRow dr in filesToDelete.Rows)
{
DeleteFile(dr["ImagePath"].ToString());
}
}
}
}
private void DeleteDocument()
{
try
{
string query = @"DELETE FROM tbl_DocumentInfo WHERE Id = @documentId;
DELETE FROM II FROM tbl_ImageInfo II, tbl_DocumentImageAssociation DIA WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @DocumentID
DELETE FROM tbl_DocumentImageAssociation WHERE DocumentId = @documentId";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@documentId", SqlDbType.BigInt).Value = _documentId;
DBSupport.ExecuteNonQuery(cmd);
}
MessageBox.Show("File deleted successfully.");
OnDocumentUpdate?.Invoke();
this.ParentForm.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could load delete document document. Error : " + ex.Message);
}
}
private void LoadDocumentForView()
{
try
{
string query = @"SELECT Title, AI.Name as Author, Body, DT.Name as DocumentType, DI.CreatedDate, DI.DateUncertain
FROM tbl_DocumentInfo DI WITH(NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK),
tbl_DocumentType DT WITH (NOLOCK)
WHERE DI.Author = AI.Id
AND DI.DocumentType = DT.Id
AND DI.Id = @documentId";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@documentId", SqlDbType.BigInt).Value = _documentId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
_title = dt.Rows[0]["Title"].ToString();
_author = dt.Rows[0]["Author"].ToString();
_documentType = dt.Rows[0]["DocumentType"].ToString();
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(DateTime.Parse(dt.Rows[0]["CreatedDate"].ToString()));
bool isUncertain = bool.Parse(dt.Rows[0]["DateUncertain"].ToString());
_createdDate = nepaliDate.Year + "-" + nepaliDate.Month.ToString("00") + "-" + nepaliDate.Day.ToString("00") + (isUncertain ? " *" : "");
_body = dt.Rows[0]["Body"].ToString();
AppendText(richTextBoxDocumentContent, _title, new Font("Arial", 14, FontStyle.Bold), HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine, alignment: HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _author, new Font("Arial", 12), HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine, alignment: HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _body, new Font("Arial", 12), HorizontalAlignment.Left);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _createdDate, new Font("Arial", 12), HorizontalAlignment.Left, isUncertain ? Color.Red : Color.Black);
}
else
{
MessageBox.Show("Requested document could not be retrieved from database.");
this.ParentForm.Close();
}
cmd.CommandText = @"SELECT ImageId, ImagePath FROM tbl_DocumentImageAssociation(NOLOCK) DIA, tbl_ImageInfo(NoLock) II
WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @documentId";
dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
AddFileToListViewImages(dr["ImagePath"].ToString());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Could load document. Error : " + ex.Message);
this.ParentForm.Close();
}
}
private DataTable GetDocumentType()
{
string query = "SELECT Id, Name FROM tbl_DocumentType WITH (NOLOCK) ORDER BY Name";
return(DBSupport.GetDataTable(query));
}
private void exportSelectedFilesAsSeparateDocxToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection selectedRows = dataGridViewDocumentSearchResult.SelectedRows;
if (selectedRows.Count > 0)
{
int progress = 0;
int totalCount = selectedRows.Count;
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
try
{
if (DialogResult.OK == folderBrowserDialog.ShowDialog())
{
progressBarExport.Show();
string today = NepaliDateTime.Now.ToDateTimeString();
string saveFileName = string.Empty;
for (int i = selectedRows.Count - 1; i >= 0; i--)
{
try
{
object fileId = selectedRows[i].Cells["ColumnID"].Value;
string query = @"SELECT DI.Title, DI.Body, DI.CreatedDate AS EnglishDate, AI.Name as Author
FROM tbl_DocumentInfo DI WITH (NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK)
WHERE DI.Author = AI.Id
AND DI.Id = @Id" ;
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Id", SqlDbType.BigInt).Value = fileId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(command);
if (dt != null)
{
string folderPath = Path.Combine(folderBrowserDialog.SelectedPath, today);
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
DateTime engDate = DateTime.Parse(dt.Rows[0]["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
string filename = nepaliDate.ToString() + "_" + dt.Rows[0]["Title"].ToString() + "_" + Guid.NewGuid().ToString().Replace("-", "");
filename = string.Join("_", filename.Split(Path.GetInvalidFileNameChars()));
saveFileName = Path.Combine(folderPath, filename + ".docx");
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
XWPFDocument newDocument = new XWPFDocument();
AppendInfoToDocument(newDocument, dt.Rows[0]["Title"].ToString(), dt.Rows[0]["Author"].ToString()
, dt.Rows[0]["Body"].ToString(), nepaliDate.ToString());
newDocument.Write(fs);
}
}
}
progressBarExport.Value = ++progress * 100 / totalCount;
progressBarExport.Update();
}
catch (Exception ex)
{
ShowMessageBox("Could not save file: " + saveFileName + Environment.NewLine + " Error : " + ex.Message
, MessageBoxIcon.Error);
}
}
ShowMessageBox("Documents created successfully !");
}
}
catch (Exception ex)
{
ShowMessageBox("Could not save files." + Environment.NewLine + " Error : " + ex.Message
, MessageBoxIcon.Error);
}
finally
{
progressBarExport.Value = 0;
progressBarExport.Hide();
}
}
}
private void exportAsDocxToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection selectedRows = dataGridViewDocumentSearchResult.SelectedRows;
if (selectedRows.Count > 0)
{
try
{
SaveFileDialog saveFile = new SaveFileDialog()
{
FileName = "FileFromSearch.docx",
Filter = "Document | *.docx",
DefaultExt = "docx"
};
if (DialogResult.OK == saveFile.ShowDialog())
{
string saveFileName = saveFile.FileName;
if (!Path.GetExtension(saveFile.FileName).Equals("." + saveFile.DefaultExt, StringComparison.InvariantCultureIgnoreCase))
{
saveFileName = saveFile.FileName + "." + saveFile.DefaultExt;
}
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
XWPFDocument newDocument = new XWPFDocument();
for (int i = selectedRows.Count - 1; i >= 0; i--)
{
object fileId = selectedRows[i].Cells["ColumnID"].Value;
string query = @"SELECT DI.Title, DI.Body, DI.CreatedDate AS EnglishDate, AI.Name as Author
FROM tbl_DocumentInfo DI WITH (NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK)
WHERE DI.Author = AI.Id
AND DI.Id = @Id" ;
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Id", SqlDbType.BigInt).Value = fileId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(command);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
DateTime engDate = DateTime.Parse(dr["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
AppendInfoToDocument(newDocument, dr["Title"].ToString(), dr["Author"].ToString(), dr["Body"].ToString()
, nepaliDate.ToString());
}
}
}
}
newDocument.Write(fs);
}
ShowMessageBox("Document created successfully !");
}
}
catch (Exception ex)
{
ShowMessageBox("Could not save file. Error : " + ex.Message, MessageBoxIcon.Error);
}
}
else
{
}
}
private DataTable GetPublishers()
{
string query = "SELECT Id, Name FROM tbl_PublisherInfo WITH (NOLOCK) ORDER BY Name";
return(DBSupport.GetDataTable(query));
}
private void Search()
{
long publisher = (long)comboBoxPublisherScope.SelectedValue;
long documentType = (long)comboBoxDocumentTypeScope.SelectedValue;
long author = (long)comboBoxSearchAuthor.SelectedValue;
string whereClause = string.Empty;
if (publisher != 0)
{
whereClause = " Publisher = " + publisher;
}
if (documentType != 0)
{
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause += " AND DocumentType =" + documentType;
}
else
{
whereClause = " DocumentType = " + documentType;
}
}
if (author != 0)
{
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause += " AND Author =" + author;
}
else
{
whereClause = " Author = " + author;
}
}
highlightTerms = new List <string>();
if (checkBoxSearchTitle.Checked || checkBoxSearchSummary.Checked || checkBoxSearchBody.Checked)
{
string searchText = GetSearchText(ref highlightTerms);
string stringClause = string.Empty;
if (checkBoxSearchTitle.Checked)
{
stringClause = " CONTAINS(Title, N'" + searchText + "') ";
}
if (checkBoxSearchSummary.Checked)
{
if (string.IsNullOrWhiteSpace(stringClause))
{
stringClause = " CONTAINS(Summary, N'" + searchText + "') ";
}
else
{
stringClause += " OR CONTAINS(Summary, N'" + searchText + "') ";
}
}
if (checkBoxSearchBody.Checked)
{
if (string.IsNullOrWhiteSpace(stringClause))
{
stringClause = " CONTAINS(Body, N'" + searchText + "') ";
}
else
{
stringClause += " OR CONTAINS(Body, N'" + searchText + "') ";
}
}
if (string.IsNullOrWhiteSpace(whereClause))
{
whereClause = stringClause;
}
else if (!string.IsNullOrWhiteSpace(stringClause))
{
whereClause += " AND (" + stringClause + ")";
}
}
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause = " WHERE " + whereClause;
}
string query = @"SELECT ROW_NUMBER() OVER (ORDER BY DI.CreatedDate DESC, DI.Id DESC) AS SN, DI.Id, AI.Name AS Author, Title, DT.Name as DocumentType, '' AS NepaliDate, CreatedDate AS EnglishDate, DateUncertain
FROM tbl_DocumentInfo DI WITH (NOLOCK)
LEFT JOIN tbl_PublisherInfo PI WITH (NOLOCK) ON DI.Publisher = PI.Id
LEFT JOIN tbl_DocumentType DT WITH (NOLOCK) ON DI.DocumentType = DT.Id
LEFT JOIN tbl_AuthorInfo AI WITH (NOLOCK) ON AI.Id = DI.Author
" + whereClause;
DataTable dt = DBSupport.GetDataTable(query);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
DateTime engDate = DateTime.Parse(dr["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
bool isUncertain = bool.Parse(dr["DateUncertain"].ToString());
dr["NepaliDate"] = nepaliDate.ToString() + (isUncertain ? " *" : "");
}
labelResultCount.Text = dt.Rows.Count.ToString();
}
else
{
labelResultCount.Text = "0";
}
dataGridViewDocumentSearchResult.DataSource = dt;
dataGridViewDocumentSearchResult.AutoResizeColumns();
}
private void ButtonInsert_Click(object sender, EventArgs e)
{
if (ValidateInsertDocumentInputs())
{
if (_openMode == Mode.ADD)
{
if (DoesPossibleDuplicateExists())
{
string message = "A file with same Author , Document Type and Created date already exists. This may be a duplicate." + Environment.NewLine + " Are you sure to continue?";
if (DialogResult.Yes != ShowMessageBox(message, MessageBoxIcon.Question, MessageBoxButtons.YesNo))
{
return;
}
}
string query = @"INSERT INTO tbl_DocumentInfo
(Name, Title, Summary, Body, CreatedDate, DateUncertain, DocumentType, Publisher, Author, InsertedDate)
VALUES (@Name, @Title, @Summary, @Body, @CreatedDate, @DateUncertain, @DocumentType, @Publisher, @Author, GetDate())
SELECT SCOPE_IDENTITY();";
try
{
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = textBoxName.Text;
command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = textBoxTitle.Text;
command.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = textBoxSummary.Text.Trim().Equals("Summary") ? "" : textBoxSummary.Text;
command.Parameters.Add("@Body", SqlDbType.NVarChar).Value = textBoxBody.Text;
command.Parameters.Add("@CreatedDate", SqlDbType.NVarChar).Value = dateTimePickerCreatedDate.Value;
command.Parameters.Add("@DateUncertain", SqlDbType.Bit).Value = checkBoxDateUncertain.Checked;
command.Parameters.Add("@Publisher", SqlDbType.BigInt).Value = comboBoxPublisher.SelectedValue;
command.Parameters.Add("@DocumentType", SqlDbType.BigInt).Value = comboBoxDocumentType.SelectedValue;
command.Parameters.Add("@Author", SqlDbType.BigInt).Value = comboBoxAuthor.SelectedValue;
object docId = DBSupport.ExecuteScalar(command);
InsertImages(long.Parse(docId.ToString()));
MessageBox.Show("Document successfully added.");
OnDocumentUpdate?.Invoke();
ClearDocumentAddUI();
SetTotalCount();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (_openMode == Mode.UPDATE)
{
string message = "Once the document is updated it couldn't be reverted to previous. Are you sure you want to update document?";
if (DialogResult.Yes == ShowMessageBox(message, MessageBoxIcon.Question, MessageBoxButtons.YesNo))
{
string query = @"UPDATE tbl_DocumentInfo
SET Name = @Name,
Title = @Title,
Summary = @Summary,
Body = @Body,
CreatedDate = @CreatedDate,
DateUncertain = @DateUncertain,
DocumentType = @DocumentType,
Publisher = @Publisher,
Author = @Author,
UpdatedDate = GetDate()
WHERE Id = @documentId";
try
{
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = textBoxName.Text;
command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = textBoxTitle.Text;
command.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = textBoxSummary.Text.Trim().Equals("Summary") ? "" : textBoxSummary.Text;
command.Parameters.Add("@Body", SqlDbType.NVarChar).Value = textBoxBody.Text;
command.Parameters.Add("@CreatedDate", SqlDbType.NVarChar).Value = dateTimePickerCreatedDate.Value;
command.Parameters.Add("@DateUncertain", SqlDbType.Bit).Value = checkBoxDateUncertain.Checked;
command.Parameters.Add("@DocumentType", SqlDbType.BigInt).Value = comboBoxDocumentType.SelectedValue;
command.Parameters.Add("@Publisher", SqlDbType.BigInt).Value = comboBoxPublisher.SelectedValue;
command.Parameters.Add("@Author", SqlDbType.BigInt).Value = comboBoxAuthor.SelectedValue;
command.Parameters.Add("@documentId", SqlDbType.BigInt).Value = _documentId;
DBSupport.ExecuteNonQuery(command);
InsertImages(DocumentId);
MessageBox.Show("Document successfully updated.");
OnDocumentUpdate?.Invoke();
this.ParentForm.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}