Capturing values whenever value has to be modified in DB

Question

Hi,

I am uploading a excel file which contains following information:

ProjectName Mig Conv Upl
Test_123 NO YES NO

Here, ProjectName , Mig , Conv, Upl are different columns in excel.
The above content will update SQL DB table with YES/NO for project "Test_123". My requirement is, whenever the value is updated from YES to NO or NO to YES, I want to capture only for that column and add it in one collection. So that, after updating the DB, I have to write only modified row in log. Please note I am using C#.NET for this.
Please let me know what is the best way to achieve this feature.

Thanks,
Santosh

Answer 1

Hello Santosh < @Santosh Umarani >

If all you need is to INERT new rows or UPDATE existing rows then you can use MERGE as other mentioned, but if you also need to log the changes as you asked for or if you need to actually trigger a push action when the excel changed o the table in the SQL Server changed, then check the below explanation

whenever value has to be modified

There are two approach to trigger actions: (1) Scheduled Pull: you connect to the source every X time and check if there was a changed. (2) Push: The source trigger an action whenever there is a changed. In order to use push approach (option 2), the source must support this type of trigger. I will come back to this according to the requirement of your needs...

Regarding your needs, some clarification is needed

add it in one collection.

Not sure what you mean by "collection". Collection of what and where? SQL Server does not manage collections but tables.

after updating the DB, I have to write only modified row in log.

If the by "log" you mean a table then this is a simple action which can be done using push approach, since SQL Server supports executing of query as a result of UPDATE values in table. This is done using object named TRIGGER.

For example if the table which include the data named ProjectsTbl and your log table named LogTbl, then you can CREATE TRIGGER on ProjectsTbl after UPDATE and in the TRIGGER you INSERT the values that were changed to the LogTbl.

Check this full demo:

use tempdb  
GO  
  
DROP TABLE IF EXISTS dbo.ProjectsTbl, dbo.LogTbl  
GO  
  
CREATE TABLE dbo.ProjectsTbl(  
 id int identity(1,1),  
 rojectName NVARCHAR(100),   
 Mig BIT,   
 Conv BIT,   
 Upl BIT  
)  
INSERT dbo.ProjectsTbl (rojectName, Mig, Conv, Upl)  
 VALUES ('Ronen',1,0,1),('Ariely',0,1,1)  
GO  
  
CREATE TABLE dbo.LogTbl(  
 id int identity(1,1),  
 rojectName NVARCHAR(100),   
 Mig BIT,   
 Conv BIT,   
 Upl BIT,  
 ChangedAt DATETIME2  
)  
GO  
  
-- Option 1: log only if the update include one of the columns Mig, Conv, Upl  
--           note that if you UPADTE value to the old value then this considered as updated  
CREATE OR ALTER TRIGGER LogUpdatesInProjectsTbl ON dbo.ProjectsTbl AFTER UPDATE AS   
 -- Note! You can use UPDATE() function or COLUMNS_UPDATED function in the body of the trigger, if you want to execute the trigger only if specific column(s) where changed  
 -- COLUMNS_UPDATED: https://learn.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699  
 -- UPDATE() : https://learn.microsoft.com/sql/t-sql/functions/update-trigger-functions-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699  
  
 IF ( UPDATE (Mig) OR UPDATE(Conv) or UPDATE(Upl) ) BEGIN  
 INSERT dbo.LogTbl (rojectName, Mig, Conv, Upl, ChangedAt)  
 SELECT rojectName, Mig, Conv, Upl, GETDATE() FROM deleted -- We log the values before the UPDATE  
 END  
GO  
  
-- Option 2: log only if the actual bvalue was changed on one of the columns Mig, Conv, Upl  
CREATE OR ALTER TRIGGER LogUpdatesInProjectsTbl ON dbo.ProjectsTbl AFTER UPDATE AS   
 -- Note! This trigger check if the actual values where changed and not jsut if the value was UPDATE  
 --select * from deleted  
 --select * from inserted  
 INSERT dbo.LogTbl (rojectName, Mig, Conv, Upl, ChangedAt)  
 SELECT d.rojectName, d.Mig, d.Conv, d.Upl, GETDATE()   
 FROM deleted d -- We log the values before the UPDATE  
 LEFT JOIN inserted i on d.id = i.id  
 WHERE d.Mig != i.Mig or d.rojectName !=i.rojectName or d.Upl != i.Upl  
GO  
  
SELECT * FROM dbo.ProjectsTbl  
SELECT * FROM dbo.LogTbl  
GO -- Log empty  
  
-- Test UPDATE ingle row  
UPDATE dbo.ProjectsTbl  
 SET Mig = 1  
 WHERE id = 2  
GO  
-- Check result:  
SELECT * FROM dbo.ProjectsTbl  
SELECT * FROM dbo.LogTbl  
GO -- Notice a new log row  
  
-- Inser more rows for tests  
INSERT dbo.ProjectsTbl (rojectName, Mig, Conv, Upl)  
 VALUES ('Ronen2',0,0,0),('Ariely2',0,0,0)  
GO  
SELECT * FROM dbo.ProjectsTbl  
SELECT * FROM dbo.LogTbl  
GO  
  
-- Test UPDATE of multiple rows  
UPDATE dbo.ProjectsTbl  
 SET Mig = 1  
 WHERE id > 1 -- three rows  
GO  
-- Check result:  
SELECT * FROM dbo.ProjectsTbl  
SELECT * FROM dbo.LogTbl  
GO -- Notice that we updated three rows but only two new rows in the log, since one of the updated rows was nopt actually changed  

I am uploading a excel file

Not clear to me if you want to make the check and log when your excel changed or when you upload data from the excel.

Above solution is in the SQL Server level which mean it is not related to the excel specifically and will happen on any UPDATE in the SQL Server level. If you need to check changes in the Excel then the solution is not related to SQL Server.

In this case, Step one of your request is to trigger action "whenever value has to be modified" in the excel, and Step two will be to to log these changed rows.

Excel also include option for push actions when value is changed. This can be done using Macro and the event Worksheet_Change.

Check this document for a demo:

https://learn.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change?WT.mc_id=DP-MVP-5001699

Answer 2

@Santosh Umarani , Welcome to Micrsoft Q&A, please refer to the following steps to update the database from excel and capture the modified columns.

First, we could get the information from the excel by using Microsoft.Office.Interop.Excel.

Second, Please get data from sql database when ProjectName is equal to Test_123.

Third, we could check if the three values are all the same, if not, we could use excel's data to update database.

Fourth, after updating the database, we could write the modified columns to the log file.

Here is a code example you could refer to.

   private void button1_Click(object sender, EventArgs e)  
        {  
            string path = @"C:\Users\username\Desktop\test1.xlsx";  
            var dt= READExcel(path);  
            dataGridView1.DataSource = dt;  
            var value=dt.AsEnumerable().Where(i=>i.Field<string>("ProjectName").Equals("Test_123")).FirstOrDefault();  
            Console.WriteLine(value["Mig"].ToString());  
            string connstr = "connstr";  
            SqlConnection connection = new SqlConnection(connstr);  
            connection.Open();  
            string commandtext = "select * from logtable where ProjectName='Test_123'";  
            SqlCommand cmd = new SqlCommand(commandtext, connection);  
            SqlDataReader reader = cmd.ExecuteReader();  
            bool mig = false;  
            bool Conv=false;  
            bool Upl=false;  
            while (reader.Read())  
            {  
                mig = Convert.ToBoolean(reader["Mig"]);  
                Conv = Convert.ToBoolean(reader["Conv"]);  
                Upl= Convert.ToBoolean(reader["Upl"]);  
            }  
            connection.Close();  
            if (mig == Result(value["Mig"].ToString()) && Conv == Result(value["Conv"].ToString()) && Upl == Result(value["Upl"].ToString()))  
            {  
                MessageBox.Show("the same data");  
            }  
            else  
            {  
                connection.Open();  
                string updatecmd = string.Format("update logtable set Mig='{0}',Conv='{1}',Upl='{2}' where ProjectName='Test_123'", Result(value["Mig"].ToString()), Result(value["Conv"].ToString()), Result(value["Upl"].ToString()));  
                SqlCommand updatedcommand = new SqlCommand(updatecmd, connection);  
                updatedcommand.ExecuteNonQuery();  
                connection.Close();  
            }  
            List<string> list = new List<string>();  
            if(mig!= Result(value["Mig"].ToString()))  
            {  
                list.Add(string.Format("The column mig has been changed from {0} to {1}",mig, Result(value["Mig"].ToString())));  
            }  
            if (Conv != Result(value["Conv"].ToString()))  
            {  
                list.Add(string.Format("The column mig has been changed from {0} to {1}", Conv, Result(value["Mig"].ToString())));  
            }  
            if (Upl != Result(value["Upl"].ToString()))  
            {  
                list.Add(string.Format("The column mig has been changed from {0} to {1}", Upl, Result(value["Upl"].ToString())));  
            }  
            File.WriteAllLines("test.log",list);  
            MessageBox.Show("success");  
  
        }  
  
        public bool Result(string text)  
        {  
            if(text=="YES")  
            {  
                return true;  
            }  
            else if(text=="NO")  
            {  
                return false;  
            }  
            return false;  
        }  
        public DataTable READExcel(string path)  
        {  
            Microsoft.Office.Interop.Excel.Application objXL = null;  
            Microsoft.Office.Interop.Excel.Workbook objWB = null;  
            objXL = new Microsoft.Office.Interop.Excel.Application();  
            objWB = objXL.Workbooks.Open(path);  
            Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];  
  
            int rows = objSHT.UsedRange.Rows.Count;  
            int cols = objSHT.UsedRange.Columns.Count;  
            DataTable dt = new DataTable();  
            int noofrow = 1;  
  
            for (int c = 1; c <= cols; c++)  
            {  
                string colname = objSHT.Cells[1, c].Text;  
                dt.Columns.Add(colname);  
                noofrow = 2;  
            }  
  
            for (int r = noofrow; r <= rows; r++)  
            {  
                DataRow dr = dt.NewRow();  
                for (int c = 1; c <= cols; c++)  
                {  
                    dr[c - 1] = objSHT.Cells[r, c].Text;  
                }  
  
                dt.Rows.Add(dr);  
            }  
  
            objWB.Close();  
            objXL.Quit();  
            return dt;  
        }  

Database design:

Hope the above code could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi pituach ,

Thank you very much for detailed information.

The above code has worked. However, I had one query: Instead of storing the log data in dbo.LogTbl table, can I store in temporary table ? I tried to store in temporary table, but could not succeed.
Kindly waiting for your response.

Thanks,
Santosh