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();
}
}
