protected SQLCommandResult GetRecord(int id)
{
var result = new SQLCommandResult();
try
{
var selectCommando = $"SELECT * FROM {TableName} WHERE{ PrimaryKey} = id";
SqlCommand command = new SqlCommand();
command.CommandText = selectCommando;
result = Database.ExecuteCommand(command, EntityCommand.Read,
SqlParameters);
}
catch (Exception ex)
{
throw new Exception("GetRecord", ex);
}
return(result);
}
protected SQLCommandResult InsertRecord()
{
var result = new SQLCommandResult();
try
{
InsertHelper();
var insertCommando = $"INSERT INTO {TableName}({ InsertFields}) VALUES({ InsertParams}); SELECT scope_identity(); ";
SqlCommand command = new SqlCommand();
command.CommandText = insertCommando;
result = Database.ExecuteCommand(command,
EntityCommand.Insert, SqlParameters);
}
catch (Exception ex)
{
throw new Exception("InsertRecord", ex);
}
return(result);
}
protected SQLCommandResult DeleteRecord()
{
var result = new SQLCommandResult();
try
{
UpdateHelper();
var updateCommando = $"DELETE FROM {TableName} WHERE { PrimaryKey} = { SqlParameters[PrimaryKey]}";
SqlCommand command = new SqlCommand();
command.CommandText = updateCommando;
result = Database.ExecuteCommand(command,
EntityCommand.Delete, SqlParameters);
}
catch (Exception ex)
{
throw new Exception("DeleteRecord", ex);
}
return(result);
}
//advanced manier
public SQLCommandResult ExecuteCommand(SqlCommand sqlCommand, EntityCommand entityCommand, Dictionary <string, object> sqlParameters)
{
SQLCommandResult result = new SQLCommandResult();
try
{
result.EntityCommand = entityCommand;
connection.Open();
sqlCommand.Connection = connection;
foreach (KeyValuePair <string, object> sqlParam in sqlParameters)
{
sqlCommand.Parameters.AddWithValue($"@{sqlParam.Key}", sqlParam.Value);
}
if (entityCommand == EntityCommand.Read)
{
SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
sda.Fill(result.DataTable);
result.Count = result.DataTable.Rows.Count;
}
else
{
if (entityCommand == EntityCommand.Insert)
{
result.NewId = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
else
{
result.Count = sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
connection.Close();
}
return(result);
}