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