private string GetSelectQuery() { string query = ""; string queryWhere = ""; foreach (var a in ListColumn.Where(s => s.isPK == true)) { queryWhere += (!string.IsNullOrEmpty(queryWhere) ? " AND " : "") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } query = "SELECT * FROM " + tableName + (string.IsNullOrEmpty(queryWhere) ? "" : " WHERE " + queryWhere); return(query); }
private string GetDeleteQuery() { string query = ""; string queryWhere = ""; foreach (var a in ListColumn.Where(s => s.isPK == true)) { queryWhere += (!string.IsNullOrEmpty(queryWhere) ? " AND " : "") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } query = string.Format("DELETE FROM {0} WHERE {1} ", tableName, queryWhere); return(query); }
private string GetInsertQuery() { string query = ""; string queryCol = ""; string queryVal = ""; foreach (var a in ListColumn.Where(s => !string.IsNullOrEmpty(s.ColumnValue))) { queryCol += (string.IsNullOrEmpty(queryCol) ? "" : ",") + a.ColumnName; queryVal += (string.IsNullOrEmpty(queryVal) ? "" : ",") + string.Format("'{0}'", a.ColumnValue); } query = string.Format("INSERT INTO {0} ({1}) values ({2}) ", tableName, queryCol, queryVal); return(query); }
private string GetUpdateQuery() { string query = ""; string queryVal = ""; foreach (var a in ListColumn.Where(s => s.isPK == false)) { queryVal += (string.IsNullOrEmpty(queryVal) ? "" : ",") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } string queryWhere = ""; foreach (var a in ListColumn.Where(s => s.isPK == true)) { queryWhere = (string.IsNullOrEmpty(queryWhere) ? "WHERE" : "AND") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } query = string.Format("UPDATE {0} SET {1} {2} ", tableName, queryVal, queryWhere); return(query); }
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(); } } }
public void ExecuteInsertOrUpdatebyExist() { using (SqlConnection connection = new SqlConnection(connectionString)) { string strQuery = ""; string strQuerySelect = GetSelectQuery(); string strQueryUpdate = GetUpdateQuery(); string strQueryInsert = GetInsertQuery(); #region queryWhere string queryWhere = ""; foreach (var a in ListColumn.Where(s => s.isPK == true)) { queryWhere += (!string.IsNullOrEmpty(queryWhere) ? " AND " : "") + string.Format(" {0} = '{1}' ", a.ColumnName, a.ColumnValue); } queryWhere = (string.IsNullOrEmpty(queryWhere) ? "" : " WHERE " + queryWhere); #endregion strQuery += "declare @count int" + Environment.NewLine; strQuery += "select @count = count(*) from " + tableName + queryWhere + Environment.NewLine; strQuery += "if(@count > 0 )" + Environment.NewLine; strQuery += "BEGIN" + Environment.NewLine; strQuery += " " + strQueryUpdate + Environment.NewLine; strQuery += "END" + Environment.NewLine; strQuery += "ELSE" + Environment.NewLine; strQuery += "BEGIN" + Environment.NewLine; strQuery += " " + strQueryInsert + Environment.NewLine; strQuery += "END" + Environment.NewLine; Debug.WriteLine("Query is " + strQuery); SqlCommand cmd = new SqlCommand(strQuery); cmd.CommandType = CommandType.Text; cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } }