public void Delete(Model model)
{
string query = string.Format("DELETE FROM {0} WHERE Id = @Id", Table);
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@Id", model.Id));
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddRange(parameters.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
public void Update(Model model)
{
dal.Update(model);
}
public void Insert(Model model)
{
dal.Insert(model);
}
public void Delete(Model model)
{
dal.Delete(model);
}
private List<object> getListPropertyValues(Model model)
{
List<object> values = new List<object>();
foreach (string column in listColumns.Values)
{
if (model.GetType().GetProperty(column) != null)
{
values.Add(model.GetType().GetProperty(column).GetValue(model));
}
}
return values;
}
public void Update(Model model)
{
List<string> listColumnsParameters = getListColumnsParameters();
List<string> listColumns = new List<string>(this.listColumns.Keys);
List<object> listValues = getListPropertyValues(model);
listColumnsParameters.Remove("@Id");
listColumns.Remove("Id");
listValues.Remove(listValues[0]);
string fields = "";
int i = 0;
foreach (string column in listColumns)
{
fields += string.Format("{0} = {1},", column, listColumnsParameters[i]);
i++;
}
fields = fields.Trim(',');
string query = string.Format("UPDATE {0} SET {1} WHERE Id = @Id", Table, fields);
List<SqlParameter> parameters = new List<SqlParameter>();
i = 0;
foreach (object value in listValues)
{
parameters.Add(new SqlParameter(listColumnsParameters[i], value ?? DBNull.Value));
i++;
}
parameters.Add(new SqlParameter("@Id", model.Id));
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddRange(parameters.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}
public void Insert(Model model)
{
List<string> listColumnsParameters = getListColumnsParameters();
List<string> listColumns = new List<string>(this.listColumns.Keys);
List<object> listValues = getListPropertyValues(model);
listColumnsParameters.Remove("@Id");
listColumns.Remove("Id");
listValues.Remove(listValues[0]);
string strParameters = string.Join(",", listColumnsParameters);
string columns = string.Join(",", listColumns);
string query = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", Table, columns, strParameters);
List<SqlParameter> parameters = new List<SqlParameter>();
int i = 0;
foreach (object value in listValues)
{
parameters.Add(new SqlParameter(listColumnsParameters[i], value ?? DBNull.Value));
i++;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddRange(parameters.ToArray());
connection.Open();
command.ExecuteNonQuery();
}
}
}