static Task <int> Main()
{
return(Pulumi.Deployment.RunAsync(() =>
{
//create volume
var volumeClaim = new MsSqlPersistentVolumeClaim().Deploy();
//create new secret for SA account
var secret = new SqlSAPwdSecret().Deploy();
//Deploy sql server
var sqlName = "mssqldb";
var appLabels = new Pulumi.InputMap <string> {
{ "app", sqlName },
};
var deployment = new SqlServerDeployment().Deploy(sqlName, appLabels);
//Deploy service
var sqlService = new SqlServerService().Deploy("mssql-service", deployment, appLabels);
return new Dictionary <string, object?>
{
{ "secret", secret.GetResourceName() },
{ "deployment", deployment.GetResourceName() },
{ "service", sqlService.GetResourceName() },
};
}));
}
private IActionResult Get(object id)
{
ApiConfiguration apiConfiguration = RouteData.DataTokens["apiConfiguration"] as ApiConfiguration;
RouteConfiguration route = RouteData.DataTokens["routeConfiguration"] as RouteConfiguration;
ActionConfiguration action = RouteData.DataTokens["actionConfiguration"] as ActionConfiguration;
if (route.Enabled && action.Enabled)
{
string sqlQuery = ControllerService.GetStringReplacedQuery(Request, route, action, id);
SqlServerService sqlServerService = new SqlServerService(apiConfiguration.ConnectionString, route);
DataTable resultSetDataTable = sqlServerService.ExecuteResultQuery(sqlQuery);
if (resultSetDataTable.Rows.Count > 0)
{
return(new OkObjectResult(resultSetDataTable.Rows[0]));
}
else
{
return(new NotFoundObjectResult(null));
}
}
else
{
Response.StatusCode = 501;
return(new ObjectResult(null));
}
}
protected void Application_Start()
{
System.Data.Entity.Database.SetInitializer <TrainingSQLContext>(null);
GlobalConfiguration.Configure(WebApiConfig.Register);
var sqlServer = SqlServerService.GetInstance();
var countryDatasource = CountriesDatasource.GetInstance();
}
private void Delete(object id)
{
ApiConfiguration apiConfiguration = RouteData.DataTokens["apiConfiguration"] as ApiConfiguration;
RouteConfiguration route = RouteData.DataTokens["routeConfiguration"] as RouteConfiguration;
ActionConfiguration action = RouteData.DataTokens["actionConfiguration"] as ActionConfiguration;
if (route.Enabled && action.Enabled)
{
string sqlQuery = ControllerService.GetStringReplacedQuery(Request, route, action, id);
SqlServerService sqlServerService = new SqlServerService(apiConfiguration.ConnectionString, route);
sqlServerService.ExecuteNonQuery(sqlQuery);
}
}
/// <summary>
/// Prepares the logger for adding data to a SQL database
/// </summary>
/// <param name="loggingConfiguration">The settings to use with the logger</param>
/// <exception cref="ArgumentNullException"></exception>
public SqlLogger(ILoggingConfiguration loggingConfiguration)
{
Settings = loggingConfiguration;
// Validate inputs
if (string.IsNullOrWhiteSpace(Settings.LogSqlTable))
{
throw new ArgumentNullException(nameof(Settings.LogSqlTable), "Must provide SQL table name to add logs to");
}
if (string.IsNullOrWhiteSpace(Settings.SqlConnectionString))
{
throw new ArgumentNullException(nameof(Settings.SqlConnectionString), "Must provide SQL connection string");
}
// Prepare class
sql = new SqlServerService(Settings.SqlConnectionString);
parameters = new List <SqlParameter>();
}
public void Post()
{
ApiConfiguration apiConfiguration = RouteData.DataTokens["apiConfiguration"] as ApiConfiguration;
RouteConfiguration route = RouteData.DataTokens["routeConfiguration"] as RouteConfiguration;
ActionConfiguration action = RouteData.DataTokens["actionConfiguration"] as ActionConfiguration;
if (route.Enabled && action.Enabled)
{
string json;
using (StreamReader streamReader = new StreamReader(Request.Body))
{
json = streamReader.ReadToEnd();
}
string sqlQuery = ControllerService.GetStringReplacedQuery(Request, route, action, json);
SqlServerService sqlServerService = new SqlServerService(apiConfiguration.ConnectionString, route);
sqlServerService.ExecuteNonQuery(sqlQuery);
}
}
public void Init(ImportFileOptions importTask, List <string> headers)
{
ImportTask = importTask;
Headers = headers;
Log.Debug($"SqlServerWriter: init for '{ImportTask.file}'");
sqlCmdBuilder = new SqlCmdBuilder(importTask);
HeaderFields = sqlCmdBuilder.GetHeaderFields(Headers);
sqlServerService = new SqlServerService(Log, getSqlConnectionString());
// Drop table
sqlServerService.simpleExecQuery(GetDropTableStatement());
// Create table
sqlServerService.simpleExecQuery(GetCreateTableStatement());
// Truncate Table
sqlServerService.simpleExecQuery(GetTruncateTableStatement());
}
public void GeneralTestMethod()
{
// Some things should be done before running this test method:
//
// - Configure the EF and the ConnectionString in the App.config
// - Create a new database table
// - Create a new data model and a new mapping configuration that represent the database table
// - Add this configuration to the DbContext configurations
// Notes:
// this example builds a list of 1 000 000 objects, executes a bulk insert and after that a batch update
// Bulk Insert time spent:
// - About 1 minute retrieving the primary key values
// - About 5 seconds without retrieving the primary key values
// Batch Update time spent:
// - About 50 seconds updating 1 000 000 of rows
// The measurement was taken while running some tests in Debug mode, so in Release mode it should be faster
// To sum up, although it was taken in Debug mode, it is still faster than EntityFramework/EntityFrameworkCore (much faster)
// Creates a list of Users
IList <User> entities = new List <User>();
for (var i = 0; i < 100; i++)
{
var entity = new User
{
Name = $"John Doe {i}",
Email = $"johndoe{i}@gmail.com",
Password = "******"
};
entities.Add(entity);
}
// Creates the data table using the extensions method
// You can also construct your data table by another way, but this method can simplify it
var dataTable = entities.AsStronglyTypedDataTable();
// Creates the services
// ps.: The MetadataService is needed only to get the primary key names, if you do not want to get them automatically, do not need to create this instance
using (IMetadataService metadataService = new SqlServerMetadataService())
using (ISqlService sqlService = new SqlServerService())
{
// Overrides the default timeout setting 2 minutes to ensure that the data will be inserted successfully
// ps.: Default timeout is 1 minute
sqlService.Timeout = TimeSpan.FromMinutes(2);
// Setting the primary key names and passing them as parameter, their values will be retrieved from the database after the bulk insert execution
// It is optional, does not need to be set
// Not setting them can save a lot of time
// Gets the primary key names from the entity mapping
var databaseKeyNames = metadataService.GetDbKeyNames(entities.GetTypeFromEnumerable());
// You can specify the primary key names directly, get from another source or pass null
// var databaseKeyNames = new List<string> { "Id" };
// Or
// IList<string> databaseKeyNames = null;
// Creates a Stopwatch, just to know the time which was spent during the execution
var stopwatch = Stopwatch.StartNew();
// Invokes the BulkInsert method
// You can also pass the BatchSize and the BulkCopyOptions parameters to this method
// BatchSize will be used to flush the values against the database table
// BulkCopyOptions can be mixed up to get a lot of advantages, by default some options will be set
// var bulkInsertTask = sqlService.BulkInsertAsync(dataTable: dataTable, primaryKeyNames: databaseKeyNames);
// You can do something here while waiting for the task completion
// Waits for the task completion
// dataTable = bulkInsertTask.GetAwaiter().GetResult();
dataTable = sqlService.BulkInsert(dataTable: dataTable, primaryKeyNames: databaseKeyNames);
// Stops the Stopwatch
stopwatch.Stop();
// Gets the total of time spent
Debug.WriteLine($"Bulk Insert Elapsed Time: {stopwatch.Elapsed}");
if (databaseKeyNames != null && databaseKeyNames.Any())
{
// Restarts the Stopwatch
stopwatch.Restart();
// Invokes the BatchUpdate method
// You can also pass the BatchSize parameter to this method
// BatchSize will be used to flush the values against the database table
// var batchUpdateTask = sqlService.BatchUpdateAsync(dataTable, "Update [User] SET [Name] = 'Batch Update Usage Example' WHERE [Id] = @Id");
// You can do something here while waiting for the task completion
// Waits for the task completion
// batchUpdateTask.Wait();
sqlService.BatchUpdate(dataTable, "Update [User] SET [Name] = 'Batch Update Usage Example' WHERE [Id] = @Id");
// Stops the Stopwatch
stopwatch.Stop();
// Gets the total of time spent
Debug.WriteLine($"Batch Update Elapsed Time: {stopwatch.Elapsed}");
}
// Transforms back the data table into a list of objects
entities = dataTable.ToList <User>();
}
}
CountriesDatasource()
{
this.SqlServer = SqlServerService.GetInstance();
}
public RouteSqlRepository()
{
_sqlServerService = SqlServiceFactory.CreateService();
}
public D_Inventario_Jac(OracleService _oracleService, SqlServerService _sqlServerService)
{
oracleService = _oracleService;
sqlServerService = _sqlServerService;
}
private void LoadSchema()
{
SqlServerService sqlServerService = new SqlServerService(ConnectionString, null);
string primaryKeyQuery;
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.SELECT("c.TABLE_CATALOG,c.TABLE_SCHEMA,c.TABLE_NAME,c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,u.COLUMN_NAME");
sqlBuilder.FROM("INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c");
sqlBuilder.INNER_JOIN("INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME");
sqlBuilder.WHERE("c.CONSTRAINT_TYPE = 'PRIMARY KEY'");
primaryKeyQuery = sqlBuilder.ToString();
DataTable primaryKeyColumnSchema = sqlServerService.GenericExecuteResultQuery(primaryKeyQuery);
foreach (DataRow primaryKeyColumnSchemaRow in primaryKeyColumnSchema.Rows)
{
SqlServerPrimaryKeyColumn primaryKeyColumn = new SqlServerPrimaryKeyColumn(primaryKeyColumnSchemaRow);
PrimaryKeyColumns.Add(primaryKeyColumn);
}
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
DataTable columnSchema = sqlConnection.GetSchema("Columns");
foreach (DataRow columnSchemaRow in columnSchema.Rows)
{
SqlServerColumn column = new SqlServerColumn(columnSchemaRow);
SqlServerPrimaryKeyColumn primaryKeyColumn = PrimaryKeyColumns.Find(pkc => pkc.Name == column.Name && pkc.TableName == column.TableName && pkc.Schema == column.Schema && pkc.Catalog == column.Catalog);
if (primaryKeyColumn != null)
{
column.ConstraintName = primaryKeyColumn.ConstraintName;
column.IsPrimaryKey = true;
}
Columns.Add(column);
}
DataTable tableSchema = sqlConnection.GetSchema("Tables");
foreach (DataRow tableSchemaRow in tableSchema.Rows)
{
SqlServerTable table = new SqlServerTable(tableSchemaRow);
Tables.Add(table);
List <SqlServerColumn> columns = Columns.FindAll(c => c.Schema == table.Schema && c.TableName == table.Name);
foreach (SqlServerColumn column in columns)
{
column.Table = table;
table.Columns.Add(column);
}
}
sqlConnection.Close();
}
}
