Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version

Generic Solution Framework to Saving History of Records Using MSSQL Server 2005 Xml Column

, 14 Mar 2007 CPOL
An article about How to Save Old Versions of Records Using MSSQL Server 2005 Xml Column
conhist_src.zip
ConHist
ConHist
Properties
Settings.settings
bin
Debug
ConHist.vshost.exe
ConHist.pdb
obj
Debug
TempPE
ConHist.MDIParent1.resources
ConHist.Properties.Resources.resources
ConHist.exe
ConHist.frmCustomers.resources
ConHist.frmEditCustomer.resources
Refactor
ConHist.frmLogin.resources
ConHist.frmHistory.resources
ConHist.pdb
ConHist.frmEditEmployee.resources
ConHist.frmEmployees.resources
ConHist.csproj.GenerateResource.Cache
ConHist.csproj.user
using System;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.ComponentModel;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ConHist
{
    public partial class frmCustomers : Form
    {
        private DataSet dsForm = null;

        public frmCustomers()
        {
            InitializeComponent();
        }

        private void frmCustomers_Load(object sender, EventArgs e)
        {

            bool ret = bindData();
            if (!ret)
            {
                this.Close();
            }
        }
        private bool bindData()
        {
            try
            {
                GlobalSettings gs = GlobalSettings.Instance;
                SqlConnection cnn = new SqlConnection(gs.ConnectionString);
                string stpName = "STP_GET_CUSTOMERS";

                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = stpName;

                SqlParameter pm1 = cmd.CreateParameter();
                pm1.ParameterName = "@P_ERROR_CODE";
                pm1.Direction = ParameterDirection.InputOutput;
                pm1.DbType = DbType.Int32;
                pm1.Value = 0;
                cmd.Parameters.Add(pm1);


                SqlParameter pm2 = cmd.CreateParameter();
                pm2.ParameterName = "@P_ERROR_MESSAGE";
                pm2.Direction = ParameterDirection.InputOutput;
                pm2.DbType = DbType.String;
                pm2.Size = 512;
                pm2.Value = "";
                cmd.Parameters.Add(pm2);

                cnn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                int returnCode = -1;
                string returnMessage = "";
                bool ret = Utils.ProcessReturnParameters(cmd, ref returnCode, ref returnMessage);
                if (!ret)
                {
                    Utils.ProcessErrorMessage(returnCode, returnMessage);
                    return false;
                }

                DataSet ds = new DataSet("DS_CUSTOMERS");
                DataTable dt = new DataTable("DT_CUSTOMERS");
                dt.Load(dr);
                ds.Tables.Add(dt);
                ds.AcceptChanges();
                dsForm = new DataSet();
                dsForm = ds;
                this.dataGridView1.DataSource = dsForm.Tables[0].DefaultView;
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.CurrentRow == null)
            {
                return;
            }
            if (this.dataGridView1.CurrentRow.Cells["ID"] == null)
            {
                return;
            }
            string recordToEdit = this.dataGridView1.CurrentRow.Cells["ID"].Value.ToString();
            int version = int.Parse(this.dataGridView1.CurrentRow.Cells["VERSION"].Value.ToString());
            frmEditCustomer customerEditForm = new frmEditCustomer();
            customerEditForm.FormMode = FormMode.EditRecord;
            customerEditForm.ID = recordToEdit;
            customerEditForm.Version = version;
            customerEditForm.Text = "Edit Record";
            customerEditForm.ShowDialog();
            bindData();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            this.bindData();
        }


        private void btnAdd_Click(object sender, EventArgs e)
        {
            frmEditCustomer newCustomerForm = new frmEditCustomer();
            newCustomerForm.FormMode = FormMode.NewRecord;
            newCustomerForm.ID = System.Guid.NewGuid().ToString().ToUpper();
            newCustomerForm.Version = 0;
            newCustomerForm.Text = "Add Record";
            newCustomerForm.ShowDialog();
            bindData();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.CurrentRow == null)
            {
                return;
            }
            if (this.dataGridView1.CurrentRow.Cells["ID"] == null)
            {
                return;
            }
            string recordToDelete = this.dataGridView1.CurrentRow.Cells["ID"].Value.ToString();
            int version = int.Parse(this.dataGridView1.CurrentRow.Cells["VERSION"].Value.ToString());
            deleteRecord(recordToDelete, version);
            bindData();
        }
        private bool deleteRecord(string recordToDelete, int version)
        {
            try
            {
                GlobalSettings gs = GlobalSettings.Instance;
                SqlConnection cnn = new SqlConnection(gs.ConnectionString);
                string stpName = "STP_DEL_CUSTOMER";

                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = stpName;

                SqlParameter pm1 = cmd.CreateParameter();
                pm1.ParameterName = "@P_ERROR_CODE";
                pm1.Direction = ParameterDirection.InputOutput;
                pm1.DbType = DbType.Int32;
                pm1.Value = 0;
                cmd.Parameters.Add(pm1);


                SqlParameter pm2 = cmd.CreateParameter();
                pm2.ParameterName = "@P_ERROR_MESSAGE";
                pm2.Direction = ParameterDirection.InputOutput;
                pm2.DbType = DbType.String;
                pm2.Size = 512;
                pm2.Value = "";
                cmd.Parameters.Add(pm2);


                SqlParameter pm3 = cmd.CreateParameter();
                pm3.ParameterName = "@P_ID";
                pm3.Direction = ParameterDirection.Input;
                pm3.DbType = DbType.String;
                pm3.Size = 64;
                pm3.Value = recordToDelete;
                cmd.Parameters.Add(pm3);

                SqlParameter pm4 = cmd.CreateParameter();
                pm4.ParameterName = "@P_VERSION";
                pm4.Direction = ParameterDirection.Input;
                pm4.DbType = DbType.Int16;
                pm4.Value = version;
                cmd.Parameters.Add(pm4);


                SqlParameter pm5 = cmd.CreateParameter();
                pm5.ParameterName = "@P_TRANSACTION_USER_ID";
                pm5.Direction = ParameterDirection.Input;
                pm5.DbType = DbType.String;
                pm5.Size = 64;
                pm5.Value = gs.UserId;
                cmd.Parameters.Add(pm5);

                cnn.Open();
                cmd.ExecuteNonQuery();
                int returnCode = -1;
                string returnMessage = "";
                bool ret = Utils.ProcessReturnParameters(cmd, ref returnCode, ref returnMessage);
                if (!ret)
                {
                    Utils.ProcessErrorMessage(returnCode, returnMessage);
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        private void btnShowCurrentHistory_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.CurrentRow == null)
            {
                return;
            }
            if (this.dataGridView1.CurrentRow.Cells["ID"] == null)
            {
                return;
            }
            string specificRecord = this.dataGridView1.CurrentRow.Cells["ID"].Value.ToString();

            frmHistory history = new frmHistory();
            history.RelatedTable = "CUSTOMERS";
            history.SpecificRecordId = specificRecord;
            history.ShowDialog();
        }

        private void btnShowHistory_Click(object sender, EventArgs e)
        {
            frmHistory history = new frmHistory();
            history.RelatedTable = "CUSTOMERS";
            history.SpecificRecordId = "";
            history.ShowDialog();
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

yincekara
Software Developer (Senior) MikroKom Yazilim A.S.
Turkey Turkey
c# , vb6 , mssql , oracle , mysql , asp , asp.net developer.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411028.1 | Last Updated 14 Mar 2007
Article Copyright 2007 by yincekara
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid