public bool DeleteData(Dictionary <string, string> selectedRow, Table table)
{
StringBuilder whereFields = new StringBuilder();
var lst = table.lstColumnNames.ToList();
if (string.IsNullOrEmpty(table.primaryKey))
{
for (int i = 0; i < lst.Count; i++)
{
whereFields.Append(lst[i] + "=@param" + i + " ");
if (i != lst.Count - 1)
{
whereFields.Append(" AND ");
}
}
}
else
{
whereFields.Append(table.primaryKey + "=@param0");
}
string query = "DELETE FROM " + table.tableName + " WHERE " + whereFields;
if (connection.GetState() == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
var s = "";
try
{
SQLCommandInterface sqlCommand = connection.CreateCommand();
sqlCommand.AddQuery(query);
for (int i = 0; i < lst.Count; i++)
{
sqlCommand.AddParameter("@param" + i, selectedRow[lst[i]].ToString());
}
s = sqlCommand.GetCommandText();
var result = sqlCommand.ExecuteNonQuery();
connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
catch (Exception e)
{
connection.Close();
return(false);
}
return(true);
}
public bool UpdateData(Dictionary <string, object> values, Table table, Dictionary <string, object> oldValues)
{
StringBuilder setFields = new StringBuilder();
StringBuilder whereFields = new StringBuilder();
var lst = table.lstColumnNames.Where(x => x != table.AutoIncrementColumnNames).ToList();
var havePrimaryKey = string.IsNullOrEmpty(table.primaryKey);
for (int i = 0; i < lst.Count; i++)
{
setFields.Append(lst[i] + "=@parama" + i + " ");
if (havePrimaryKey)
{
whereFields.Append(lst[i] + "=@paramb" + i + " ");
}
if (i != lst.Count - 1)
{
setFields.Append(" , ");
if (havePrimaryKey)
{
whereFields.Append(" and ");
}
}
else
{
if (!havePrimaryKey)
{
whereFields.Append(table.primaryKey + "=@paramb");
}
}
}
string query = "update " + table.tableName + " set " + setFields + " where " + whereFields;
if (connection.GetState() == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
var s = "";
try
{
SQLCommandInterface sqlCommand = connection.CreateCommand();
sqlCommand.AddQuery(query);
var count = 0;
for (int i = 0; i < lst.Count; i++)
{
if (table.typeOfColumns[lst[i]] == "datetime" || table.typeOfColumns[lst[i]] == "date")
{
var date = values[lst[i]].ToString().Split('/');
var date1 = date[2].Split(' ');
var datetemp = date1[0] + "/" + date[0] + "/" + date[1];
values[lst[i]] = datetemp;
}
sqlCommand.AddParameter("@parama" + i, values[lst[i]].ToString());
//sqlCommand.Parameters.AddWithValue("@parama" + i, values[lst[i]].ToString());
if (!havePrimaryKey && count == 0)
{
count++;
sqlCommand.AddParameter("@paramb", oldValues[table.primaryKey].ToString());
//sqlCommand.Parameters.AddWithValue("@paramb", oldValues[table.primaryKey].ToString());
}
else
{
sqlCommand.AddParameter("@paramb" + i, oldValues[lst[i]].ToString());
//sqlCommand.Parameters.AddWithValue("@paramb" + i, oldValues[lst[i]].ToString());
}
}
s = sqlCommand.GetCommandText();
var result = sqlCommand.ExecuteNonQuery();
connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
catch (Exception e)
{
connection.Close();
return(false);
}
return(true);
}
public bool InsertData(Dictionary <string, string> values, Table table)
{
StringBuilder columnFields = new StringBuilder();
StringBuilder valuesFields = new StringBuilder();
columnFields.Append("(");
valuesFields.Append("(");
var lstColumn = table.lstColumnNames.Where(x => x != table.AutoIncrementColumnNames).ToList();
for (int i = 0; i < lstColumn.Count; i++)
{
columnFields.Append(lstColumn[i]);
valuesFields.Append("@param" + i);
if (i < lstColumn.Count - 1)
{
columnFields.Append(",");
valuesFields.Append(",");
}
}
columnFields.Append(")");
valuesFields.Append(")");
string query = "Insert into" + " " + table.tableName + " " + columnFields
+ " " + "values" + " " + valuesFields;
if (connection.GetState() == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
try
{
SQLCommandInterface sqlCommand = connection.CreateCommand();
sqlCommand.AddQuery(query);
for (int i = 0; i < lstColumn.Count; i++)
{
if (table.typeOfColumns[lstColumn[i]] == "datetime" || table.typeOfColumns[lstColumn[i]] == "date")
{
var date = values[lstColumn[i]].ToString().Split('/');
var date1 = date[2].Split(' ');
var datetemp = date1[0] + "/" + date[0] + "/" + date[1];
values[lstColumn[i]] = datetemp;
}
//sqlCommand.Parameters.AddWithValue("@param" + i, values[lstColumn[i]]);
sqlCommand.AddParameter("@param" + i, values[lstColumn[i]]);
}
var result = sqlCommand.ExecuteNonQuery();
connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
catch
{
connection.Close();
return(false);
}
return(true);
}