public void AddColumnValues(string ColumnName, object ColumnValue, bool isPK) { if (ColumnValue != null) { DCColumn temp = new DCColumn(); temp.ColumnName = ColumnName; temp.ColumnValueObj = ColumnValue; temp.isPK = isPK; ListColumn.Add(temp); } }
public void ExecuteQueryInsertUpdate() { DCColumn colModified = ListColumn.Where(s => s.ColumnName == "modifiedOn").FirstOrDefault(); if (colModified != null) { string queryWhere = ""; string queryInsert = GetInsertQuery(); string queryUpdate = GetUpdateQuery(); foreach (var a in ListColumn.Where(s => s.isPK == true)) { queryWhere = (string.IsNullOrEmpty(queryWhere) ? "WHERE" : "AND") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } string query = "declare @oldmoddate datetime = null " + Environment.NewLine; query += string.Format("select top 1 @oldmoddate = modifiedOn from {0} {1}", this.tableName, queryWhere); query += "IF(@oldmoddate IS NULL)" + Environment.NewLine; query += " BEGIN" + Environment.NewLine; query += " " + queryInsert + Environment.NewLine; query += " END" + Environment.NewLine; query += "ELSE IF(@oldmoddate < '" + colModified.ColumnValue + "' )" + Environment.NewLine; query += " BEGIN" + Environment.NewLine; query += " " + queryUpdate + Environment.NewLine; query += " END" + Environment.NewLine; Debug.WriteLine("Query is " + query); using (SqlConnection connection = new SqlConnection(connectionString)) { string strQuery = GetInsertQuery(); SqlCommand cmd = new SqlCommand(query); cmd.CommandType = CommandType.Text; cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } }