public int Deletar(LinhaModel obj)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(_connectionString))
{
string query = $"DELETE FROM [dbo].[Linha]" +
$" WHERE [Id] = {obj.Id}";
connection.Open();
SqlCommand command = new SqlCommand(null, connection)
{
CommandText = query,
CommandType = CommandType.Text
};
command.ExecuteScalar();
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection?.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public void AtualizarLinha(LinhaModel linhaModel)
{
var linha = new Linha {
Id = linhaModel.Id, Codigo = linhaModel.Codigo, Nome = linhaModel.Nome, Cor = linhaModel.Cor, Categoria = linhaModel.Categoria, Favorita = linhaModel.Favorita
};
BuzzOnConnection.Instance.Update <Linha>(linha);
}
public int Inserir(LinhaModel obj)
{
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(_connectionString))
{
string query = $"INSERT INTO [dbo].[Linha]" +
$" ([Simcard]" +
$" ,[Numero]" +
$" ,[Operadora]" +
$" ,[Antcolab]" +
$" ,[Atualcolab]" +
$" ,[Status]" +
$" ,[Obs])" +
$" VALUES" +
$" (@p1" +
$" ,@p2" +
$" ,@p3" +
$" ,@p4" +
$" ,@p5" +
$" ,@p6" +
$" ,@p7) ";
connection.Open();
SqlCommand command = new SqlCommand(null, connection)
{
CommandText = query,
CommandType = CommandType.Text
};
FillParameter(command, "p1", obj.Simcard);
FillParameter(command, "p2", obj.Numero);
FillParameter(command, "p3", obj.Operadora);
FillParameter(command, "p4", obj.Antcolab);
FillParameter(command, "p5", obj.Atualcolab);
FillParameter(command, "p6", obj.Status);
FillParameter(command, "p7", obj.Obs);
command.ExecuteScalar();
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection?.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public List <LinhaModel> Listar(LinhaModel obj)
{
List <LinhaModel> lista = new List <LinhaModel>();
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = $"SELECT * FROM Linha WHERE 1 = 1";
if (obj != null)
{
if (!string.IsNullOrWhiteSpace(obj.Numero))
{
query += $" AND Numero LIKE '{obj.Numero}%'";
}
}
SqlCommand command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
lista.Add(new LinhaModel
{
Id = reader.GetInt32(0),
Simcard = SafeGetString(reader, 1),
Numero = reader.GetString(2),
Operadora = SafeGetString(reader, 3),
Antcolab = SafeGetString(reader, 4),
Atualcolab = SafeGetString(reader, 5),
Status = SafeGetString(reader, 6),
Obs = SafeGetString(reader, 7)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection?.State == ConnectionState.Open)
{
connection.Close();
}
}
return(lista);
}
public ActionResult Delete(int id, LinhaModel model)
{
try
{
new LinhaDao().Deletar(model);
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ModelState.AddModelError("", ex.Message);
return(View());
}
}
public LinhaModel Obter(int id)
{
LinhaModel entity = new LinhaModel();
SqlConnection connection = null;
try
{
using (connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = $"SELECT * FROM Linha WHERE Id = {id}";
SqlCommand command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
entity = new LinhaModel
{
Id = reader.GetInt32(0),
Simcard = SafeGetString(reader, 1),
Numero = reader.GetString(2),
Operadora = SafeGetString(reader, 3),
Antcolab = SafeGetString(reader, 4),
Atualcolab = SafeGetString(reader, 5),
Status = SafeGetString(reader, 6),
Obs = SafeGetString(reader, 7)
};
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection?.State == ConnectionState.Open)
{
connection.Close();
}
}
return(entity);
}
public ActionResult Create(LinhaModel model)
{
try
{
if (!ModelState.IsValid)
{
return(View());
}
new LinhaDao().Inserir(model);
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ModelState.AddModelError("", ex.Message);
return(View());
}
}
public ActionResult Edit(int id, LinhaModel model)
{
try
{
if (!ModelState.IsValid)
{
return(View());
}
// TODO: Add update logic here
new LinhaDao().Editar(model);
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ModelState.AddModelError("", ex.Message);
return(View());
}
}
