c# Dynamic Linq Query

Question

How can i get compoiste primpary key value at run time for a audit purpose.

in my application when user modies record i save that tables old and modied values for audit purpose . using two tables ChangeRecord and crdetail table.

for simple single key primary key tables the DataChangeTracker procedure is working fine.

for tables having composite primary key , how can i use dynamic linq query to get the composite keys and store it to CRTDetail audit table.

i tried using dynamic linq query in new method DataChangeTracker_CPk, which i am unable to do. its failing expression is invalid

can some body understand it.

using KIToolkit;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
using System.Linq.Dynamic.Core;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Kings.ERP
    {
        internal class AuditDump
        {
            public static void CreateCmdsAndUpdate(DataSet Fromds, string updatetables = "")
            {
                updatetables = updatetables.ToUpper();
                using(SqlConnection connection = new SqlConnection(Appvaribales.ConnectionString))
                {
                    connection.Open();
                    for(int d = 0; d <= Fromds.Tables.Count - 1; d++)
                    {
                        string sqltable = Fromds.Tables[d].TableName.ToUpper();
                        if(!string.IsNullOrWhiteSpace(updatetables) && !updatetables.Contains(sqltable)) continue;
                        string queryString = "";
                        DataView dvDel = new DataView(Fromds.Tables[d], null, null, DataViewRowState.Deleted);
                        DataView dvMod = new DataView(Fromds.Tables[d], null, null, DataViewRowState.ModifiedOriginal);
                        DataTable modifiedTable = dvMod.ToTable();
                        if(!modifiedTable.Columns.Contains("host_name"))
                        {
                            modifiedTable.Columns.Add("host_name", typeof(string));
                            queryString += "host_name,";
                        }
                        if(!modifiedTable.Columns.Contains("nt_username"))
                        {
                            modifiedTable.Columns.Add("nt_username", typeof(string));
                            queryString += "nt_username,";
                        }
                        if(!modifiedTable.Columns.Contains("log_date"))
                        {
                            modifiedTable.Columns.Add("log_date", typeof(DateTime));
                            queryString += "log_date,";
                        }
                        if(!modifiedTable.Columns.Contains("log_updtcd"))
                        {
                            modifiedTable.Columns.Add("log_updtcd", typeof(string));
                            queryString += "log_updtcd,";
                        }
                        if(!modifiedTable.Columns.Contains("user_id"))
                        {
                            modifiedTable.Columns.Add("user_id", typeof(int));
                            queryString += "user_id,";
                        }
                        if(queryString.EndsWith(",")) queryString = queryString.Remove(queryString.Length - 1);
                        modifiedTable.TableName = "audit." + sqltable;
                        for(int m = 0; m < modifiedTable.Rows.Count; m++)
                        {
                            modifiedTable.Rows[m]
                                ["host_name"] = Environment.MachineName;
                            modifiedTable.Rows[m]
                                ["nt_username"] = Login.sysUserName;
                            modifiedTable.Rows[m]
                                ["log_date"] = DateTime.UtcNow;
                            modifiedTable.Rows[m]
                                ["user_id"] = Login.SysUserId;
                            modifiedTable.Rows[m]
                                ["log_updtcd"] = "E";
                        }
                        DataTable DeletedTable = dvDel.ToTable();
                        if(!DeletedTable.Columns.Contains("host_name")) DeletedTable.Columns.Add("host_name", typeof(string));
                        if(!DeletedTable.Columns.Contains("nt_username")) DeletedTable.Columns.Add("nt_username", typeof(string));
                        if(!DeletedTable.Columns.Contains("log_date")) DeletedTable.Columns.Add("log_date", typeof(DateTime));
                        if(!DeletedTable.Columns.Contains("log_updtcd")) DeletedTable.Columns.Add("log_updtcd", typeof(string));
                        if(!DeletedTable.Columns.Contains("user_id")) DeletedTable.Columns.Add("user_id", typeof(string));
                        DeletedTable.TableName = "audit.deleted" + sqltable;
                        for(int i = 0; i < DeletedTable.Rows.Count; i++)
                        {
                            DeletedTable.Rows[i]
                                ["host_name"] = Environment.MachineName;
                            DeletedTable.Rows[i]
                                ["nt_username"] = Login.sysUserName;
                            DeletedTable.Rows[i]
                                ["log_date"] = DateTime.UtcNow;
                            DeletedTable.Rows[i]
                                ["log_updtcd"] = "D";
                            DeletedTable.Rows[i]
                                ["user_id"] = Login.SysUserId;
                        }
                        SqlCommand cmd = new SqlCommand(@"select a.* from sys.columns a inner join sys.tables b on a.object_id =b.object_id                                         inner join sys.schemas c on c.schema_id=b.schema_id                                         where c.name = 'audit' and b.name='" + sqltable + "'", connection);
                        SqlDataReader dr = cmd.ExecuteReader();
                        if(dr.HasRows)
                        {
                            while(dr.Read())
                            {
                                if(Fromds.Tables[d].Columns.Contains(dr["name"].ToString()))
                                {
                                    queryString += "," + (string) dr["Name"];
                                }
                            }
                            queryString = "Select " + queryString + " From audit." + sqltable;
                            dr.Close();
                            try
                            {
                                SqlDataAdapter dbadapter = new SqlDataAdapter();
                                dbadapter.SelectCommand = new SqlCommand(queryString, connection);
                                SqlCommandBuilder builder = new SqlCommandBuilder(dbadapter);
                                DataSet dsaudit = new DataSet();
                                dsaudit.Tables.Add(modifiedTable);
                                dsaudit.Tables.Add(DeletedTable);
                                dbadapter.Update(modifiedTable);
                                dbadapter.Update(DeletedTable);
                            }
                            catch(SqlException ex)
                            {
                                MessageBox.Show("Failed To Update:" + modifiedTable.TableName + Environment.NewLine + "Becuase" + Environment.NewLine + ex.ToString());
                            }
                        }
                        else
                        {
                            dr.Close();
                        }
                    }
                    connection.Close();
                }
            }
            public static void DataChangeTracker(DataSet ds, string updatetables = "")
                {
                    using(SqlConnection conn = new SqlConnection(Appvaribales.ConnectionString))
                        {
                            conn.Open();
                            foreach(DataTable tbl in ds.Tables)
                                {
                                    if(!string.IsNullOrWhiteSpace(updatetables) && !updatetables.Contains(tbl.TableName)) continue;
                                    DataView dvMod = new DataView(tbl, null, null, DataViewRowState.ModifiedOriginal);
                                    DataView dvDel = new DataView(tbl, null, null, DataViewRowState.Deleted);
                                    if(dvMod.Count.Equals(0) && dvDel.Count.Equals(0)) continue;
                                    SqlCommand cmd = new SqlCommand(@"select sys.columns.* from sys.columns                         inner join sys.tables on sys.columns.object_id =sys.tables.object_id WHERE UPPER(sys.tables.NAME)='" + tbl.TableName + "'", conn);
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    List < string > properties = new List < string > ();
                                    if(dr.HasRows)
                                    {
                                        while(dr.Read())
                                        {
                                            if(tbl.Columns.Contains(dr["name"].ToString()))
                                            {
                                                properties.Add(dr["name"].ToString());
                                            }
                                        }
                                        dr.Close(); //11.8.2023 Instead use sp_TablePk to handle composite Pk, dci audit fail                         
                                        string pmkeyString = string.Format(@"select C.COLUMN_NAME FROM                                       INFORMATION_SCHEMA.TABLE_CONSTRAINTS T                                       JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C                                       ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME                                       WHERE C.TABLE_NAME='{0}'                                       and T.CONSTRAINT_TYPE='PRIMARY KEY'", tbl.TableName);
                                        cmd.CommandText = pmkeyString;
                                        string pmKey = "";
                                        var cmdresult = cmd.ExecuteScalar();
                                        if(cmdresult == null) /*                              * Tables without primary key audit not done so returned back                              */ return;
                                        else pmKey = cmdresult.ToString();
                                        object o1 = new object();;
                                        DataSet dsaudit = new DataSet();
                                        CursorAdapter ca1 = new CursorAdapter();
                                        ca1.SelectCmd = @"Select id,ChangeRecordTracker_id,FieldName,OldValue,NewValue                                 From [audit].[CRTDetail]";
                                        ca1.SqlTable = "[audit].[CRTDetail]";
                                        ca1.CursorName = "CRTDetail";
                                        ca1.keyfields = "id"; // ca1.AutoIncColumns = "id";                         ca1.SendUpdate = true;                         ca1.updatableFieldList = @"ChangeRecordTracker_id,FieldName,OldValue,NewValue";                        
                                        try
                                        {
                                            ca1.BuildAdapter(ref dsaudit, ref o1, SqlDataBase.FillType.Schema);
                                        }
                                        catch(SqlException ex)
                                        {
                                            MessageBox.Show(ex.ToString());
                                        }
                                        string[] pmkeys = pmKey.Split(',');
                                        /

Answer 1

i want to store changes to tables.

i have above attached procedure which works well

for example in my bill entry form, on save i call it as below

AuditDump.DataChangeTracker(ds, "Bill,billitem,BillGeneral,billpackings,oc_link");

only problem is this procedure works if tables have primary key on single field.

it checks modified field in the each of DatachnageTracker procedure parameters passed tables names and store them in two tables ChangeRecordTracker, CRTDetail which are in Audit Schmema of the same database.

their structure of these tables is as under

USE [dci]
GO

/****** Object:  Table [audit].[CRTDetail]    Script Date: 25-08-2023 17:30:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [audit].[CRTDetail](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ChangeRecordTracker_id] [bigint] NULL,
	[FieldName] [varchar](50) NULL,
	[OldValue] [varchar](max) NULL,
	[NewValue] [varchar](max) NULL,
 CONSTRAINT [PK_CRTDetail] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [audit].[CRTDetail]  WITH CHECK ADD  CONSTRAINT [FK_CRTDetail_ChangeRecordTracker] FOREIGN KEY([ChangeRecordTracker_id])
REFERENCES [audit].[ChangeRecordTracker] ([id])
GO

ALTER TABLE [audit].[CRTDetail] CHECK CONSTRAINT [FK_CRTDetail_ChangeRecordTracker]
GO

and

USE [dci]
GO

/****** Object:  Table [audit].[ChangeRecordTracker]    Script Date: 25-08-2023 17:31:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [audit].[ChangeRecordTracker](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[TableName] [varchar](50) NULL,
	[pmKey] [varchar](25) NULL,
	[pmVal] [varchar](50) NULL,
	[log_date] [datetime] NULL,
	[host_name] [varchar](50) NULL,
	[nt_username] [varchar](50) NULL,
	[User_id] [tinyint] NULL,
	[Action] [varchar](1) NULL,
 CONSTRAINT [PK_ChangeRecordTracker] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [audit].[ChangeRecordTracker] ADD  CONSTRAINT [DF_ChangeRecordTracker_log_date]  DEFAULT (getdate()) FOR [log_date]
GO

i want it to work for composite primpary key i.e. primary having multiple fileds

I came across Dynamic Linq Query

DataChangeTracker_CPk this commented procedure in earlier post was a failed attempt.

i want some help how can i do that using dynamic linq query.