private static void processArtigos()
{
List <Artigo> artigos = PriIntegration.ListaArtigos();
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
// Drop table
var dropQuery = "IF OBJECT_ID('dbo.Artigo', 'U') IS NOT NULL DROP TABLE dbo.Artigo;";
using (var command = new SqlCommand(dropQuery, connection))
{
command.ExecuteNonQuery();
}
// Create table
var createQuery =
"CREATE TABLE [dbo].[Artigo](" +
"[artigo] [nvarchar](48) NOT NULL ," +
"[descricao] [nvarchar](50) ," +
"[stk_reposicao] [float] ," +
"[stk_atual] [float] ," +
"[pvp] [float] NOT NULL ," +
"[needs_restock] [bit], " +
"CONSTRAINT [PK_Artigo] PRIMARY KEY CLUSTERED ( [artigo] ) " +
"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" +
") ON [PRIMARY]";
using (var command = new SqlCommand(createQuery, connection))
{
command.ExecuteNonQuery();
}
// Populate table
foreach (Artigo artigo in artigos)
{
var query = "INSERT INTO dbo.Artigo(artigo, descricao, stk_reposicao, stk_atual, pvp, needs_restock)" +
"VALUES (@artigo, @descricao, @stk_reposicao, @stk_atual, @pvp, @needs_restock)";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@artigo", artigo.CodArtigo);
command.Parameters.AddWithValue("@descricao", artigo.DescArtigo);
command.Parameters.AddWithValue("@stk_reposicao", artigo.STKReposicao);
command.Parameters.AddWithValue("@stk_atual", artigo.STKAtual);
command.Parameters.AddWithValue("@pvp", artigo.PVP);
command.Parameters.AddWithValue("@needs_restock", artigo.STKAtual <= artigo.STKReposicao ? 1 : 0);
command.ExecuteNonQuery();
}
}
}
}
private static void processFuncionarios()
{
List <Funcionario> listaFuncionarios = PriIntegration.listaFuncionarios();
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
// Drop table
var dropQuery = "IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL DROP TABLE dbo.Employee;";
using (var command = new SqlCommand(dropQuery, connection))
{
command.ExecuteNonQuery();
}
// Create table
var createQuery =
"CREATE TABLE [dbo].[Employee](" +
"[id] [varchar](10) NOT NULL ," +
"[abbrv_name] [varchar](80) ," +
"[phone] [nvarchar](15) ," +
"[email] [nvarchar](512) ," +
"CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [id] ) " +
"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" +
") ON [PRIMARY]";
using (var command = new SqlCommand(createQuery, connection))
{
command.ExecuteNonQuery();
}
// Populate table
foreach (Funcionario funcionario in listaFuncionarios)
{
var query = "INSERT INTO dbo.Employee(id, abbrv_name, phone, email)" +
"VALUES (@id, @abbrv_name, @phone, @email)";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@id", funcionario.CodFuncionario);
command.Parameters.AddWithValue("@abbrv_name", funcionario.NomeAbreviado);
command.Parameters.AddWithValue("@phone", funcionario.NumTelefone);
command.Parameters.AddWithValue("@email", funcionario.Email);
command.ExecuteNonQuery();
}
}
}
}