/// <summary>
/// Carga las rutinas de la base de datos
/// </summary>
private void LoadRoutines(SQLServerProvider objConnection, Schema objSchema)
{
string strSQL;
// Crea la cadena SQL
strSQL = @"SELECT Routine_Catalog AS Table_Catalog, Routine_Schema AS Table_Schema,
Routine_Name AS Table_Name, Routine_Type, Routine_Definition
FROM Information_Schema.Routines
ORDER BY Routine_Name" ;
// Carga los datos
using (IDataReader rdoRoutines = objConnection.ExecuteReader(strSQL, null, CommandType.Text))
{ // Lee los registros
while (rdoRoutines.Read())
{
SchemaRoutine objRoutine = new SchemaRoutine(objSchema);
// Asigna los datos del recordset al objeto
objRoutine.Catalog = (string)rdoRoutines.IisNull("Table_Catalog");
objRoutine.Schema = (string)rdoRoutines.IisNull("Table_Schema");
objRoutine.Name = (string)rdoRoutines.IisNull("Table_Name");
objRoutine.Type = GetRoutineType((string)rdoRoutines.IisNull("Routine_Type"));
objRoutine.Definition = (string)rdoRoutines.IisNull("Routine_Definition");
// Añade el objeto a la colección
objSchema.Routines.Add(objRoutine);
}
// Cierra el recordset
rdoRoutines.Close();
}
}
public string GerarNotaFiscal(Domain.Pedido pedido)
{
try
{
NotaFiscal notaFiscal = new NotaFiscal();
var result = EmitirNotaFiscal(notaFiscal, pedido);
if (!string.IsNullOrWhiteSpace(result))
{
return(result);
}
var provider = new SQLServerProvider();
var notaFiscalRepository = new NotaFiscalRepository(provider);
if (!notaFiscalRepository.InserirNotaFiscal(notaFiscal))
{
return("Erro ao inserir Nota Fiscal");
}
GerarXml(provider, notaFiscal);
}
catch (Exception e)
{
throw e;
}
return(string.Empty);
}
public bool InserirNotaFiscalItem(NotaFiscal notaFiscal, NotaFiscalItem notaFiscalItem)
{
var provider = new SQLServerProvider();
var command = new SqlCommand();
//inserir nota fiscal item
provider.LimparParametros(command);
string storedProcedure = "P_NOTA_FISCAL_ITEM";
// Adiciona os parâmetros da instrução SQL
provider.AdicionarParametro(command, "@pId", SqlDbType.Int, 0, true);
provider.AdicionarParametro(command, "@pIdNotaFiscal", SqlDbType.Int, notaFiscal.Id);
provider.AdicionarParametro(command, "@pCfop", SqlDbType.VarChar, notaFiscalItem.Cfop);
provider.AdicionarParametro(command, "@pTipoIcms", SqlDbType.VarChar, notaFiscalItem.TipoIcms);
provider.AdicionarParametro(command, "@pBaseIcms", SqlDbType.Decimal, notaFiscalItem.BaseIcms);
provider.AdicionarParametro(command, "@pAliquotaIcms", SqlDbType.Decimal, notaFiscalItem.AliquotaIcms);
provider.AdicionarParametro(command, "@pValorIcms", SqlDbType.Decimal, notaFiscalItem.ValorIcms);
provider.AdicionarParametro(command, "@pNomeProduto", SqlDbType.VarChar, notaFiscalItem.NomeProduto);
provider.AdicionarParametro(command, "@pCodigoProduto", SqlDbType.VarChar, notaFiscalItem.CodigoProduto);
provider.AdicionarParametro(command, "@pBaseIPI", SqlDbType.Decimal, notaFiscalItem.BaseIPI);
provider.AdicionarParametro(command, "@pAliquotaIPI", SqlDbType.Decimal, notaFiscalItem.AliquotaIPI);
provider.AdicionarParametro(command, "@pValorIPI", SqlDbType.Decimal, notaFiscalItem.ValorIPI);
provider.AdicionarParametro(command, "@pDesconto", SqlDbType.Decimal, notaFiscalItem.Desconto);
// Retorna a quantidade de linhas afetadas
if (provider.ExecutaAtualizacao(command, storedProcedure) > 0)
{
notaFiscalItem.Id = Convert.ToInt32(command.Parameters["@pId"].Value.ToString().Trim());
return(true);
}
return(false);
}
public bool InserirNotaFiscal(NotaFiscal notaFiscal)
{
var provider = new SQLServerProvider();
var command = new SqlCommand();
//inserir nota fiscal
provider.LimparParametros(command);
string storedProcedure = "P_NOTA_FISCAL";
provider.AdicionarParametro(command, "@pId", SqlDbType.Int, 0, true);
provider.AdicionarParametro(command, "@pNumeroNotaFiscal", SqlDbType.Int, notaFiscal.NumeroNotaFiscal);
provider.AdicionarParametro(command, "@pSerie", SqlDbType.Int, notaFiscal.Serie);
provider.AdicionarParametro(command, "@pNomeCliente", SqlDbType.VarChar, notaFiscal.NomeCliente);
provider.AdicionarParametro(command, "@pEstadoDestino", SqlDbType.VarChar, notaFiscal.EstadoDestino);
provider.AdicionarParametro(command, "@pEstadoOrigem", SqlDbType.VarChar, notaFiscal.EstadoOrigem);
// Retorna a quantidade de linhas afetadas
if (provider.ExecutaAtualizacao(command, storedProcedure) > 0)
{
notaFiscal.Id = Convert.ToInt32(command.Parameters["@pId"].Value.ToString().Trim());
foreach (var notaFiscalItem in notaFiscal.ItensDaNotaFiscal)
{
if (!InserirNotaFiscalItem(notaFiscal, notaFiscalItem))
{
return(false);
}
}
}
return(true);
}
public void Truncate()
{
SQLServerProvider <Product> provider = new SQLServerProvider <Product>();
provider.TruncateTable();
provider.SubmitChanges();
}
public static IDbProvider AutoDetectProvider()
{
IDbProvider provider = null;
try
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(DWKitRuntime.ConnectionStringData)) { };
provider = new SQLServerProvider();
}
catch (ArgumentException) { }
if (provider == null)
{
try
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(DWKitRuntime.ConnectionStringData)) { };
provider = new PostgreSqlProvider();
}
catch (ArgumentException) { }
}
if (provider == null)
{
try
{
using (IDbConnection connection = new OracleConnection(DWKitRuntime.ConnectionStringData)) {}
provider = new OracleProvider();
}
catch (ArgumentException)
{
}
}
return(provider);
}
/// <summary>
/// Carga las columnas de la vista
/// </summary>
private void LoadColumns(SQLServerProvider objConnection, SchemaView objView)
{
ParametersDBCollection objColParameters = new ParametersDBCollection();
string strSQL;
// Asigna lo parámetros
objColParameters.Add("@View_Catalog", objView.Catalog);
objColParameters.Add("@View_Schema", objView.Schema);
objColParameters.Add("@View_Name", objView.Name);
// Crea la cadena SQL
strSQL = @"SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name
FROM Information_Schema.View_Column_Usage
WHERE View_Catalog = @View_Catalog
AND View_Schema = @View_Schema
AND View_Name = @View_Name" ;
// Carga las columnas
using (IDataReader rdoColumns = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text))
{ // Lee los registros
while (rdoColumns.Read())
{
SchemaColumn objColumn = new SchemaColumn(objView.Parent);
// Carga los datos de la columna
objColumn.Catalog = (string)rdoColumns.IisNull("Table_Catalog");
objColumn.Schema = (string)rdoColumns.IisNull("Table_Schema");
objColumn.Table = (string)rdoColumns.IisNull("Table_Name");
objColumn.Name = (string)rdoColumns.IisNull("Column_Name");
// Añade la columna a la colección
objView.Columns.Add(objColumn);
}
// Cierra el recordset
rdoColumns.Close();
}
}
public void Drop()
{
SQLServerProvider <ProductNew> provider = new SQLServerProvider <ProductNew>();
provider.DropTable();
provider.SubmitChanges();
}
/// <summary>
/// Carga la definición de vistas
/// </summary>
private void LoadViews(SQLServerProvider objConnection, Schema objSchema)
{
string strSQL;
// Crea la cadena SQL
strSQL = @"SELECT Table_Catalog, Table_Schema, Table_Name, View_Definition, Check_Option, Is_Updatable
FROM Information_Schema.Views
ORDER BY Table_Name" ;
// Carga las vistas
using (IDataReader rdoViews = objConnection.ExecuteReader(strSQL, null, CommandType.Text))
{ // Lee los registros
while (rdoViews.Read())
{
SchemaView objView = new SchemaView(objSchema);
// Asigna los datos al objeto
objView.Catalog = (string)rdoViews.IisNull("Table_Catalog");
objView.Schema = (string)rdoViews.IisNull("Table_Schema");
objView.Name = (string)rdoViews.IisNull("Table_Name");
objView.Definition = (string)rdoViews.IisNull("View_Definition");
objView.CheckOption = (string)rdoViews.IisNull("Check_Option");
objView.IsUpdatable = !(((string)rdoViews.IisNull("Is_Updatable")).Equals("NO", StringComparison.CurrentCultureIgnoreCase));
// Añade el objeto a la colección
objSchema.Views.Add(objView);
}
// Cierra el recordset
rdoViews.Close();
}
// Carga las columnas de la vista
foreach (SchemaView objView in objSchema.Views)
{
LoadColumns(objConnection, objView);
}
}
public void Insert()
{
Product a = new Product() { ProductName = "mac", ProductCode = "pro", CategoryId = 7 };
SQLServerProvider<Product> provider = new SQLServerProvider<Product>();
provider.Insert(a);
provider.SubmitChanges();
}
public void Delete()
{
Category cat = new Category() { Id = 11 };
cat.CategoryName = "dai";
SQLServerProvider<Category> provider = new SQLServerProvider<Category>();
provider.Delete(cat);
provider.SubmitChanges();
}
public void MultipleDelete()
{
List<Category> cats = new List<Category>();
cats.Add(new Category() { Id = 11 });
cats.Add(new Category() { Id = 12 });
SQLServerProvider<Category> provider = new SQLServerProvider<Category>();
provider.MultipleDelete(cats);
provider.SubmitChanges();
}
public void MultipleInsert()
{
List<Product> products = new List<Product>();
products.Add(new Product() { ProductName = "yosemite", ProductCode = "pro", CategoryId = 8 });
products.Add(new Product() { ProductName = "mavericks", ProductCode = "pro", CategoryId = 9 });
SQLServerProvider<Product> provider = new SQLServerProvider<Product>();
provider.MultipleInsert(products);
provider.SubmitChanges();
}
public void Insert()
{
Product a = new Product()
{
ProductName = "mac", ProductCode = "pro", CategoryId = 7
};
SQLServerProvider <Product> provider = new SQLServerProvider <Product>();
provider.Insert(a);
provider.SubmitChanges();
}
public void Delete()
{
Category cat = new Category()
{
Id = 11
};
cat.CategoryName = "dai";
SQLServerProvider <Category> provider = new SQLServerProvider <Category>();
provider.Delete(cat);
provider.SubmitChanges();
}
/// <summary>
/// Carga las rutinas del esquema de una base de datos
/// </summary>
public void LoadSchemaRoutines(Schema objSchema, SQLServerConnectionString objConnectionString)
{
using (SQLServerProvider objConnection = new SQLServerProvider(objConnectionString))
{ // Abre la conexión
objConnection.Open();
// Limpia las rutinas
objSchema.Routines.Clear();
// Obtiene los datos del esquema
LoadRoutines(objConnection, objSchema);
// Cierra la conexión
objConnection.Close();
}
}
public void SelectCustomersFromLondon_ShouldPass()
{
const string origFile = _path + "CustomersExpected.csv";
const string outFile = _path + "CustomersActual.csv";
const string query =
"select CustomerID, CompanyName, ContactName, ContactTitle, City, Region, PostalCode, Country " +
"from Customers where City != 'London' order by ContactTitle";
SQLServerProvider.ExecuteReaderQuery(query, _connectionString, outFile,
"CustomerID", "CompanyName", "ContactName", "ContactTitle", "City", "Region", "PostalCode", "Country");
Assert.True(Comparer.CompareFiles(origFile, outFile));
}
/// <summary>
/// Carga las restricciones de una tabla
/// </summary>
private void LoadConstraints(SQLServerProvider objConnection, SchemaTable objTable)
{
ParametersDBCollection objColParameters = new ParametersDBCollection();
string strSQL;
// Añade los parámetros
objColParameters.Add("@Table_Catalog", objTable.Catalog);
objColParameters.Add("@Table_Schema", objTable.Schema);
objColParameters.Add("@Table_Name", objTable.Name);
// Crea la cadena SQL
strSQL = @"SELECT TableConstraints.Table_Catalog, TableConstraints.Table_Schema, TableConstraints.Table_Name,
ColumnConstraint.Column_Name, ColumnConstraint.Constraint_Name,
TableConstraints.Constraint_Type, Key_Column.Ordinal_Position
FROM Information_Schema.Table_Constraints AS TableConstraints
INNER JOIN Information_Schema.Constraint_Column_Usage AS ColumnConstraint
ON TableConstraints.Constraint_Catalog = ColumnConstraint.Constraint_Catalog
AND TableConstraints.Constraint_Schema = ColumnConstraint.Constraint_Schema
AND TableConstraints.Constraint_Name = ColumnConstraint.Constraint_Name
INNER JOIN Information_Schema.Key_Column_Usage AS Key_Column
ON ColumnConstraint.Constraint_Catalog = Key_Column.Constraint_Catalog
AND ColumnConstraint.Constraint_Schema = Key_Column.Constraint_Schema
AND ColumnConstraint.Constraint_Name = Key_Column.Constraint_Name
AND ColumnConstraint.Column_Name = Key_Column.Column_Name
WHERE TableConstraints.Table_Catalog = @Table_Catalog
AND TableConstraints.Table_Schema = @Table_Schema
AND TableConstraints.Table_Name = @Table_Name
ORDER BY TableConstraints.Table_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position" ;
// Carga los datos
using (IDataReader rdoConstraint = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text))
{ // Lee los datos
while (rdoConstraint.Read())
{
SchemaConstraint objConstraint = new SchemaConstraint(objTable.Parent);
// Asigna los datos del registro
objConstraint.Catalog = (string)rdoConstraint.IisNull("Table_Catalog");
objConstraint.Schema = (string)rdoConstraint.IisNull("Table_Schema");
objConstraint.Table = (string)rdoConstraint.IisNull("Table_Name");
objConstraint.Column = (string)rdoConstraint.IisNull("Column_Name");
objConstraint.Name = (string)rdoConstraint.IisNull("Constraint_Name");
objConstraint.Type = GetConstraintType((string)rdoConstraint.IisNull("Constraint_Type"));
objConstraint.Position = (int)rdoConstraint.IisNull("Ordinal_Position");
// Añade la restricción a la colección
objTable.Constraints.Add(objConstraint);
}
// Cierra el recordset
rdoConstraint.Close();
}
}
public void SelectSuppliersWithIdLessThan25_ShouldFail()
{
const string origFile = _path + "SuppliersExpected.csv";
const string outFile = _path + "SuppliersActual.csv";
const string query =
"select SupplierID, ContactName, ContactTitle, City, Address, PostalCode, Country, Phone " +
"from Suppliers where SupplierID < 25 " +
"group by ContactTitle, SupplierID, CompanyName, ContactName, City, PostalCode, Country, Phone, Address";
SQLServerProvider.ExecuteReaderQuery(query, _connectionString, outFile,
"SupplierID", "ContactName", "ContactTitle", "City", "Address", "PostalCode", "Country", "Phone");
Assert.True(Comparer.CompareFiles(origFile, outFile));
}
public void InsertDataToSuppliers_ShouldPass()
{
const string origFile = _path + "SuppliersInsert.csv";
const string outFile = _path + "SuppliersActual.csv";
const string query =
"insert into Suppliers (CompanyName, ContactName, ContactTitle, City, Address, PostalCode, Country, Phone) " +
"values ('Heli Süßwaren GmbH & Co. KG', 'Sarah Rafferty', 'CTO', 'Berlin', 'Kaloadagatan 13', 'M14 SD', 'Germany', '(010) 9984510')";
const string selectStatement = "select SupplierID, ContactName, ContactTitle, City, PostalCode, Country, Phone from Suppliers";
SQLServerProvider.ExecuteManipulativeQuery(query, _connectionString);
SQLServerProvider.ExecuteReaderQuery(selectStatement, _connectionString, outFile, "SupplierID", "ContactName", "ContactTitle", "City", "PostalCode", "Country", "Phone");
Assert.False(Comparer.CompareFiles(origFile, outFile));
}
public void GerarXml(SQLServerProvider provider, NotaFiscal notaFiscal)
{
var configParametrosRepository = new ConfigParametrosRepository(provider);
var diretorioNotasFiscais = configParametrosRepository.ObterValorDoParametro("Path_notas_fiscais");
if (!System.IO.Directory.Exists(diretorioNotasFiscais))
{
System.IO.Directory.CreateDirectory(diretorioNotasFiscais);
}
var serializer = new XmlSerializer(typeof(NotaFiscal));
using (var writer = new StreamWriter(diretorioNotasFiscais + notaFiscal.Id))
{
serializer.Serialize(writer, notaFiscal);
}
}
public void MultipleDelete()
{
List <Category> cats = new List <Category>();
cats.Add(new Category()
{
Id = 11
});
cats.Add(new Category()
{
Id = 12
});
SQLServerProvider <Category> provider = new SQLServerProvider <Category>();
provider.MultipleDelete(cats);
provider.SubmitChanges();
}
public void MultipleInsert()
{
List <Product> products = new List <Product>();
products.Add(new Product()
{
ProductName = "yosemite", ProductCode = "pro", CategoryId = 8
});
products.Add(new Product()
{
ProductName = "mavericks", ProductCode = "pro", CategoryId = 9
});
SQLServerProvider <Product> provider = new SQLServerProvider <Product>();
provider.MultipleInsert(products);
provider.SubmitChanges();
}
/// <summary>
/// Carga las tablas de un esquema
/// </summary>
private void LoadTables(SQLServerProvider objConnection, Schema objSchema)
{
string strSQL;
// Crea la cadena SQL
strSQL = @"SELECT Tables.TABLE_CATALOG, Tables.TABLE_SCHEMA, Tables.TABLE_NAME,
Tables.TABLE_TYPE, Objects.Create_Date, Objects.Modify_Date, Properties.Value AS Description
FROM INFORMATION_SCHEMA.TABLES AS Tables INNER JOIN sys.all_objects AS Objects
ON Tables.Table_Name = Objects.name
LEFT JOIN sys.extended_properties AS Properties
ON Objects.object_id = Properties.major_id
AND Properties.minor_id = 0
AND Properties.name = 'MS_Description'
ORDER BY Tables.TABLE_NAME" ;
// Carga las tablas
using (IDataReader rdoTables = objConnection.ExecuteReader(strSQL, null, CommandType.Text))
{ // Recorre la colección de registros
while (rdoTables.Read())
{
SchemaTable objTable = objTable = new SchemaTable(objSchema);
// Asigna los datos del registro al objeto
objTable.Catalog = (string)rdoTables.IisNull("TABLE_CATALOG");
objTable.Schema = (string)rdoTables.IisNull("TABLE_SCHEMA");
objTable.Name = (string)rdoTables.IisNull("TABLE_NAME");
objTable.Type = GetTableType((string)rdoTables.IisNull("TABLE_TYPE"));
objTable.DateCreate = (DateTime)rdoTables.IisNull("Create_Date");
objTable.DateUpdate = (DateTime)rdoTables.IisNull("Modify_Date");
objTable.Description = (string)rdoTables.IisNull("Description");
// Añade el objeto a la colección (si es una tabla)
if (objTable.Type == SchemaTable.TableType.Table)
{
objSchema.Tables.Add(objTable);
}
}
// Cierra el recordset
rdoTables.Close();
}
// Carga los datos de las tablas
foreach (SchemaTable objTable in objSchema.Tables)
{
LoadColumns(objConnection, objTable);
LoadConstraints(objConnection, objTable);
}
}
/// <summary>
/// Carga el texto de una función, procedimiento, trigger ...
/// </summary>
private string LoadHelpText(SQLServerProvider objConnection, string strName)
{
string strText = "";
// Obtiene el texto resultante de llamar a la rutina sp_helptext
try
{ using (IDataReader rdoText = objConnection.ExecuteReader("EXEC sp_helptext '" + strName + "'", null, CommandType.Text))
{ // Obtiene el texto
while (rdoText.Read())
{
strText += (string)rdoText.IisNull("Text");
}
// Cierra el recordset
rdoText.Close();
} }
catch {}
// Devuelve el texto cargado
return(strText);
}
public string EmitirNotaFiscal(NotaFiscal notaFiscal, Pedido pedido)
{
var provider = new SQLServerProvider();
var notaFiscalRepository = new NotaFiscalRepository(provider);
notaFiscal.NumeroNotaFiscal = notaFiscalRepository.BuscarUltimaNotaFiscal();
notaFiscal.Serie = new Random().Next(Int32.MaxValue);
notaFiscal.NomeCliente = pedido.NomeCliente;
notaFiscal.EstadoDestino = pedido.EstadoDestino;
notaFiscal.EstadoOrigem = pedido.EstadoOrigem;
var notaFiscalItemList = new List <NotaFiscalItem>();
var result = ProcessarItemPedido(pedido, notaFiscal, notaFiscalItemList);
if (!string.IsNullOrWhiteSpace(result))
{
return(result);
}
notaFiscal.ItensDaNotaFiscal = notaFiscalItemList;
return(string.Empty);
}
/// <summary>
/// Carga las columnas de una tabla
/// </summary>
private void LoadColumns(SQLServerProvider objConnection, SchemaTable objTable)
{
ParametersDBCollection objColParameters = new ParametersDBCollection();
string strSQL;
// Añade los parámetros
objColParameters.Add("@Table_Catalog", objTable.Catalog);
objColParameters.Add("@Table_Schema", objTable.Schema);
objColParameters.Add("@Table_Name", objTable.Name);
// Crea la cadena SQL
strSQL = @"SELECT Columns.Column_Name, Columns.Ordinal_Position, Columns.Column_Default,
Columns.Is_Nullable, Columns.Data_Type, Columns.Character_Maximum_Length,
CONVERT(int, Columns.Numeric_Precision) AS Numeric_Precision,
CONVERT(int, Columns.Numeric_Precision_Radix) AS Numeric_Precision_Radix,
CONVERT(int, Columns.Numeric_Scale) AS Numeric_Scale,
CONVERT(int, Columns.DateTime_Precision) AS DateTime_Precision,
Columns.Character_Set_Name, Columns.Collation_Catalog, Columns.Collation_Schema, Columns.Collation_Name,
Objects.is_identity, Properties.value AS Description
FROM Information_Schema.Columns AS Columns INNER JOIN sys.all_objects AS Tables
ON Columns.Table_Name = Tables.name
INNER JOIN sys.columns AS Objects
ON Columns.Column_Name = Objects.name
AND Tables.object_id = Objects.object_id
LEFT JOIN sys.extended_properties AS Properties
ON Objects.object_id = Properties.major_id
AND Properties.minor_id = Objects.column_id
AND Properties.name = 'MS_Description'
WHERE Columns.Table_Catalog = @Table_Catalog
AND Columns.Table_Schema = @Table_Schema
AND Columns.Table_Name = @Table_Name
ORDER BY Ordinal_Position" ;
// Carga los datos
using (IDataReader rdoColumns = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text))
{ // Lee los datos
while (rdoColumns.Read())
{
SchemaColumn objColumn = new SchemaColumn(objTable.Parent);
// Asigna los datos del registro
objColumn.Name = (string)rdoColumns.IisNull("Column_Name") as string;
objColumn.OrdinalPosition = rdoColumns.IisNull("Ordinal_Position", 0);
objColumn.Default = (string)rdoColumns.IisNull("Column_Default");
objColumn.IsNullable = !(((string)rdoColumns.IisNull("Is_Nullable")).Equals("no", StringComparison.CurrentCultureIgnoreCase));
objColumn.DataType = (string)rdoColumns.IisNull("Data_Type");
objColumn.CharacterMaximumLength = rdoColumns.IisNull("Character_Maximum_Length", 0);
objColumn.NumericPrecision = rdoColumns.IisNull("Numeric_Precision", 0);
objColumn.NumericPrecisionRadix = rdoColumns.IisNull("Numeric_Precision_Radix", 0);
objColumn.NumericScale = rdoColumns.IisNull("Numeric_Scale", 0);
objColumn.DateTimePrecision = rdoColumns.IisNull("DateTime_Precision", 0);
objColumn.CharacterSetName = (string)rdoColumns.IisNull("Character_Set_Name");
objColumn.CollationCatalog = (string)rdoColumns.IisNull("Collation_Catalog");
objColumn.CollationSchema = (string)rdoColumns.IisNull("Collation_Schema");
objColumn.CollationName = (string)rdoColumns.IisNull("Collation_Name");
objColumn.IsIdentity = (bool)rdoColumns.IisNull("is_identity");
objColumn.Description = (string)rdoColumns.IisNull("Description") as string;
// Añade la columna a la colección
objTable.Columns.Add(objColumn);
}
// Cierra el recordset
rdoColumns.Close();
}
}
public void AlterTable()
{
SQLServerProvider<ProductNew> provider = new SQLServerProvider<ProductNew>();
provider.CreateAlterTable(false);
}
public void Truncate()
{
SQLServerProvider<Product> provider = new SQLServerProvider<Product>();
provider.TruncateTable();
provider.SubmitChanges();
}
public void Drop()
{
SQLServerProvider<ProductNew> provider = new SQLServerProvider<ProductNew>();
provider.DropTable();
provider.SubmitChanges();
}
public void AlterTable()
{
SQLServerProvider <ProductNew> provider = new SQLServerProvider <ProductNew>();
provider.CreateAlterTable(false);
}
public static IDbProvider AutoDetectProvider(IConfigurationRoot configuration, ILogger logger)
{
IDbProvider provider = null;
bool createDb = "true".Equals(configuration["DWKit:CreateDatabaseObjects"], StringComparison.InvariantCultureIgnoreCase);
try
{
using (new System.Data.SqlClient.SqlConnection(DWKitRuntime.ConnectionStringData))
{ }
provider = new SQLServerProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new MSSQL.DbCreator(DWKitRuntime.ConnectionStringData), "MSSQL", logger);
}
}
catch (ArgumentException)
{
}
if (provider == null)
{
try
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(DWKitRuntime.ConnectionStringData)) { }
provider = new PostgreSqlProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new PostgreSQL.DbCreator(DWKitRuntime.ConnectionStringData), "PostgreSql", logger);
}
}
catch (ArgumentException)
{
}
}
if (provider == null)
{
try
{
using (IDbConnection connection = new OracleConnection(DWKitRuntime.ConnectionStringData)) { }
provider = new OracleProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new Oracle.DbCreator(DWKitRuntime.ConnectionStringData), "Oracle", logger);
}
}
catch (ArgumentException)
{
}
}
if (provider == null)
{
throw new Exception($"Can't autodetect provider for connection string: {DWKitRuntime.ConnectionStringData}");
}
return(provider);
}
public ConfigParametrosRepository(SQLServerProvider provider)
{
this.repository = provider;
}
private GentleProviders()
{
MySQLProvider prov1 = new MySQLProvider("");
SQLServerProvider prov2 = new SQLServerProvider("");
}
public NotaFiscalRepository(SQLServerProvider provider)
{
this.repository = provider;
}
/// <summary>
/// Carga los triggers de un esquema
/// </summary>
private void LoadTriggers(SQLServerProvider objConnection, Schema objSchema)
{
string strSQL;
// Crea la cadena SQL
strSQL = @"SELECT tmpTables.name AS DS_Tabla, tmpTrigger.name AS DS_Trigger_Name,
USER_NAME(tmpTrigger.uid) AS DS_User_Name, tmpTrigger.category AS NU_Category,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsExecuted') = 1) THEN 1 ELSE 0 END)) AS IsExecuted,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsAnsiNullsOn') = 1) THEN 1 ELSE 0 END)) AS ExecIsAnsiNullsOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsQuotedIdentOn') = 1) THEN 1 ELSE 0 END)) AS ExecIsQuotedIdentOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsAnsiNullsOn') = 1) THEN 1 ELSE 0 END)) AS IsAnsiNullsOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'IsQuotedIdentOn') = 1) THEN 1 ELSE 0 END)) AS IsQuotedIdentOn,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsAfterTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsAfterTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsFirstUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsFirstUpdateTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsInsteadOfTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsInsteadOfTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastDeleteTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastDeleteTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastInsertTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastInsertTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsLastUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsLastUpdateTrigger,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsTriggerDisabled') = 1) THEN 1 ELSE 0 END)) AS ExecIsTriggerDisabled,
CONVERT(bit, (CASE WHEN (OBJECTPROPERTY(tmpTrigger.id, N'ExecIsUpdateTrigger') = 1) THEN 1 ELSE 0 END)) AS ExecIsUpdateTrigger,
tmpTrigger.crdate AS FE_Create, tmpTrigger.refdate AS FE_Reference
FROM sys.sysobjects AS tmpTrigger INNER JOIN sys.sysobjects AS tmpTables
ON tmpTrigger.parent_obj = tmpTables.id
WHERE OBJECTPROPERTY(tmpTrigger.id, N'IsTrigger') = 1
AND OBJECTPROPERTY(tmpTrigger.id, N'IsMSShipped') = 0
ORDER BY tmpTables.Name, tmpTrigger.Name" ;
// Carga los desencadenadores
using (IDataReader rdoTriggers = objConnection.ExecuteReader(strSQL, null, CommandType.Text))
{ // Recorre la colección de registros
while (rdoTriggers.Read())
{
SchemaTrigger objTrigger = new SchemaTrigger(objSchema);
// Asigna los datos del registro al objeto
objTrigger.Catalog = "TABLE_CATALOG"; // clsBaseDB.iisNull(rdoTables, "TABLE_CATALOG") as string;
objTrigger.Schema = "TABLE_SCHEMA"; // clsBaseDB.iisNull(rdoTables, "TABLE_SCHEMA") as string;
objTrigger.Table = (string)rdoTriggers.IisNull("DS_Tabla");
objTrigger.Name = (string)rdoTriggers.IisNull("DS_Trigger_Name");
objTrigger.UserName = (string)rdoTriggers.IisNull("DS_User_Name");
objTrigger.Category = (int)rdoTriggers.IisNull("NU_Category");
objTrigger.IsExecuted = (bool)rdoTriggers.IisNull("IsExecuted");
objTrigger.IsExecutionAnsiNullsOn = (bool)rdoTriggers.IisNull("ExecIsAnsiNullsOn");
objTrigger.IsExecutionQuotedIdentOn = (bool)rdoTriggers.IisNull("ExecIsQuotedIdentOn");
objTrigger.IsAnsiNullsOn = (bool)rdoTriggers.IisNull("IsAnsiNullsOn");
objTrigger.IsQuotedIdentOn = (bool)rdoTriggers.IisNull("IsQuotedIdentOn");
objTrigger.IsExecutionAfterTrigger = (bool)rdoTriggers.IisNull("ExecIsAfterTrigger");
objTrigger.IsExecutionDeleteTrigger = (bool)rdoTriggers.IisNull("ExecIsDeleteTrigger");
objTrigger.IsExecutionFirstDeleteTrigger = (bool)rdoTriggers.IisNull("ExecIsFirstDeleteTrigger");
objTrigger.IsExecutionFirstInsertTrigger = (bool)rdoTriggers.IisNull("ExecIsFirstInsertTrigger");
objTrigger.IsExecutionFirstUpdateTrigger = (bool)rdoTriggers.IisNull("ExecIsFirstUpdateTrigger");
objTrigger.IsExecutionInsertTrigger = (bool)rdoTriggers.IisNull("ExecIsInsertTrigger");
objTrigger.IsExecutionInsteadOfTrigger = (bool)rdoTriggers.IisNull("ExecIsInsteadOfTrigger");
objTrigger.IsExecutionLastDeleteTrigger = (bool)rdoTriggers.IisNull("ExecIsLastDeleteTrigger");
objTrigger.IsExecutionLastInsertTrigger = (bool)rdoTriggers.IisNull("ExecIsLastInsertTrigger");
objTrigger.IsExecutionLastUpdateTrigger = (bool)rdoTriggers.IisNull("ExecIsLastUpdateTrigger");
objTrigger.IsExecutionTriggerDisabled = (bool)rdoTriggers.IisNull("ExecIsTriggerDisabled");
objTrigger.IsExecutionUpdateTrigger = (bool)rdoTriggers.IisNull("ExecIsUpdateTrigger");
objTrigger.DateCreate = (DateTime?)rdoTriggers.IisNull("FE_Create");
objTrigger.DateReference = (DateTime?)rdoTriggers.IisNull("FE_Reference");
// Añade el objeto a la colección (si es una tabla)
objSchema.Triggers.Add(objTrigger);
}
// Cierra el recordset
rdoTriggers.Close();
}
// Carga el contenido de los triggers
foreach (SchemaTrigger objTrigger in objSchema.Triggers)
{
objTrigger.Content = LoadHelpText(objConnection, objTrigger.Name);
}
}
