public void Delete(string table, IEnumerable <string> columns = null, IEnumerable <Condition> conditions = null)
{
delete = new StringBuilder("DELETE FROM ");
SqlCommand command = new SqlCommand();
delete.Append($"{table} ");
if (conditions != null)
{
delete.Append("WHERE ");
}
foreach (var item in conditions)
{
var paramName = new ParamNameGenerator().GenerateParamName(item.ColumnName);
delete.Append(CreateCondition(item) + paramName);
command.Parameters.AddWithValue(paramName, item.Value);
}
command.CommandText = delete.ToString();
command.Connection = con;
try
{
con.Open();
command.ExecuteNonQuery();
}
finally
{
con?.Close();
}
}
public void Update(string table, IEnumerable <IStatement> columns = null, IEnumerable <Condition> conditions = null)
{
update = new StringBuilder("UPDATE ");
var paramGen = new ParamNameGenerator();
SqlCommand command = new SqlCommand();
update.Append($"{table} ");
var updateColsList = new List <string>();
if (columns != null)
{
update.Append("SET ");
foreach (var item in columns)
{
var paramName = paramGen.GenerateParamName(item.ColumnName);
string cond = CreateStatement(item) + paramName;
updateColsList.Add(cond);
command.Parameters.AddWithValue(paramName, item.Value);
}
update.Append(string.Join(" , ", updateColsList));
}
if (conditions != null && conditions.Any())
{
update.Append(" WHERE ");
var paramsList = new List <string>();
foreach (var item in conditions)
{
var paramName = paramGen.GenerateParamName(item.ColumnName);
string cond = CreateCondition(item) + paramName;
paramsList.Add(cond);
command.Parameters.AddWithValue(paramName, item.Value);
}
update.Append(string.Join(" AND ", paramsList));
}
command.CommandText = update.ToString();
command.Connection = con;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.UpdateCommand = command;
con.Open();
dataAdapter.UpdateCommand.ExecuteNonQuery();
}
finally
{
command?.Dispose();
con?.Close();
}
}
public DataTable Select(string table, IEnumerable <string> columns = null, IEnumerable <Condition> conditions = null)
{
select = new StringBuilder("SELECT ");
SqlDataAdapter adapter;
if (columns != null)
{
select.Append(string.Join(", ", columns));
}
select.Append($"*FROM {table}");
SqlCommand command = new SqlCommand();
var conditionsList = new List <string>();
if (conditions != null)
{
select.Append(" WHERE ");
foreach (var item in conditions)
{
var paramName = new ParamNameGenerator().GenerateParamName(item.ColumnName);
string cond = CreateCondition(item) + paramName;
conditionsList.Add(cond);
command.Parameters.AddWithValue(paramName, item.Value);
}
select.Append(string.Join(" AND ", conditionsList));
}
command.CommandText = select.ToString();
command.Connection = con;
adapter = new SqlDataAdapter(command);
dt = new DataTable();
try
{
con.Open();
adapter.Fill(dt);
adapter.Dispose();
}
catch (SqlException)
{
throw new ArgumentNullException();
}
catch (InvalidOperationException)
{
throw new TooManyConnectionsOpenedException();
}
finally
{
command?.Dispose();
con?.Close();
}
return(dt);
}
public void Insert(string table, IEnumerable <string> columns = null, IEnumerable <IStatement> conditions = null)
{
add = new StringBuilder("INSERT INTO ");
add.Append($"{table} ");
if (columns != null)
{
add.Append(string.Join(", ", columns));
}
SqlCommand command = new SqlCommand();
if (conditions != null)
{
add.Append("(");
var conditionsList = new List <string>();
var paramList = new List <string>();
foreach (var item in conditions)
{
var paramName = new ParamNameGenerator().GenerateParamName(item.ColumnName);
conditionsList.Add(item.ColumnName);
command.Parameters.AddWithValue(paramName, item.Value);
paramList.Add(paramName);
}
add.Append(string.Join(",", conditionsList)).Append(" ) VALUES( ").Append(string.Join(",", paramList)).Append(")");
}
command.CommandText = add.ToString();
command.Connection = con;
try
{
con.Open();
command.ExecuteNonQuery();
}
finally
{
command?.Dispose();
con?.Close();
}
}
