public List <TO> MapOf3 <T1, T2, T3, TO>(string sql, object parameters, Func <T1, T2, T3, TO> map, string splitOn)
{
using IDbConnection cnn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString);
var output = cnn.Query <T1, T2, T3, TO>(sql, map, parameters, splitOn: splitOn);
return(output.ToList());
}
public int SaveData <T>(T savable, string sql)
{
using IDbConnection cnn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString);
var output = cnn.Query <int>(sql, savable).Single();
return(output);
}
public List <T> LoadDataWith <T>(string sql, object parameters, CommandType?type = null)
{
using IDbConnection cnn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString);
var output = cnn.Query <T>(sql, parameters, commandType: type);
return(output.ToList());
}
public List <T> LoadData <T>(string sql)
{
using IDbConnection cnn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString);
var output = cnn.Query <T>(sql, new DynamicParameters());
return(output.ToList());
}
/// <summary>
/// Execute query. Pass isscalar when running identity queries
/// </summary>
/// <param name="query"></param>
/// <param name="isScalar"></param>
/// <returns></returns>
public async Task <int> ExecuteQuery(string query, bool isScalar)
{
using (Microsoft.Data.SqlClient.SqlConnection cn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString))
{
await cn.OpenAsync();
using (Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand(query))
{
cmd.CommandTimeout = CommandTimeOut;
cmd.Connection = cn;
if (!isScalar)
{
var result = await cmd.ExecuteNonQueryAsync();
return(result);
}
else
{
var result = await cmd.ExecuteScalarAsync();
return(Convert.ToInt32(result));
}
}
}
}
public UnitTestHelpers()
{
_configPath = DirectoryLocator.GetTargetConfigurationPath();
var msSqlPath = Path.Combine(DirectoryLocator.GetTargetProjectPath(typeof(Program)), @"..\..\MsSqlDb");
// For the unit tests we
_tenantContext = new TenantContext
{
DbContext =
{
Catalog = "LeagueIntegration",
Schema = "dbo",
ConnectionKey = "dummy"
}
};
_tenantContext.DbContext.ConnectionString =
$"Server=(LocalDB)\\MSSQLLocalDB;AttachDbFilename={msSqlPath}\\LeagueIntegrationTest.mdf;Database={_tenantContext.DbContext.Catalog};Integrated Security=true";
// Make sure we can connect to the database
using (var connection = new Microsoft.Data.SqlClient.SqlConnection(_tenantContext.DbContext.ConnectionString))
try
{
connection.Open();
}
finally
{
connection.Close();
}
InitializeLlBlGenPro();
}
public IDbConnection GetConnection()
{
var connectionString = _configuration.GetSection("ConnectionStrings").GetSection("SQLConnection").Value;
var conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
return(conn);
}
public static async Task Execute(string search)
{
if (string.IsNullOrWhiteSpace(search))
{
throw new ArgumentException("Value cannot be null or whitespace.", nameof(search));
}
await using var connection = new Microsoft.Data.SqlClient.SqlConnection(Configuration.ConnectionString);
await connection.OpenAsync();
foreach (var table in connection.ExecuteQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME").ToList())
{
var tableName = table["TABLE_NAME"];
//Console.WriteLine(tableName);
foreach (var row in connection.ExecuteQuery($"SELECT * FROM [{tableName}]"))
{
foreach (var value in row.Values)
{
if (value == null)
{
continue;
}
var stringValue = value.ToString();
if (stringValue.Equals(search, StringComparison.OrdinalIgnoreCase))
{
Console.WriteLine($"{tableName} " + row.First().Key + " #" + row.First().Value);
}
}
}
}
}
public void BulkCopy_MicrosoftDataSqlClient()
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection())
{
Test <Microsoft.Data.SqlClient.SqlBulkCopy>(conn);
}
}
public static ObservableCollection <SyncTable> FetchServerTables(string dbName)
{
try
{
var sqlConn = new Microsoft.Data.SqlClient.SqlConnection(serverConnectionString);
var serverConn = new ServerConnection(sqlConn);
var server = new Server(serverConn);
var tables = new ObservableCollection <SyncTable>();
// get the remote database by name
Database database = server.Databases[dbName];
foreach (Table table in database.Tables)
{
// if the table is not a tracking table (we don't want to sync the tracking data)
if (!table.Name.Contains(trackingPrefix))
{
var syncTable = new SyncTable {
name = table.Name, pkColumn = table.Columns[0].Name
};
tables.Add(syncTable);
}
}
return(tables);
}
catch (Exception ex)
{
return(null);
}
}
public void ReadNorthwindSchema()
{
var connectionString = ConnectionStrings.Northwind;
DatabaseSchema schema;
//using the newer Microsoft Sql client
using (var connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString))
{
try
{
connection.ConnectionString = connectionString;
connection.Open();
}
catch (Exception exception)
{
Assert.Inconclusive("Cannot access database for provider Microsoft.Data.SqlClient message= " +
exception.Message);
}
var dbReader = new DatabaseReader(connection);
dbReader.Owner = "dbo";
dbReader.ReadAll();
schema = dbReader.DatabaseSchema;
}
//password is removed in SqlServer 2017
//Assert.AreEqual(ConnectionStrings.Northwind, schema.ConnectionString, "Connection string is in the schema");
Assert.IsNotNull(schema.ConnectionString, "Connection string is in the schema");
Assert.AreEqual("dbo", schema.Owner, "Schema/owner is in the schema");
}
public TResult ExecuteScalar <TResult>(string sql, string connectionStringName = "DefaultConnection")
{
using var connection = new Microsoft.Data.SqlClient.SqlConnection(_config.GetConnectionString(connectionStringName));
Server server = new Server(new ServerConnection(connection));
return((TResult)server.ConnectionContext.ExecuteScalar(sql));
}
public static void ExecuteSql(string connectionString, string sql)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString))
{
var server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(sql);
}
}
public async void SignIn(HttpContext httpContext, Customer customer, bool isPersistent = false)
{
using var con = new Microsoft.Data.SqlClient.SqlConnection(_connectionString);
ClaimsIdentity identity = new ClaimsIdentity(this.GetUserClaims(customer), CookieAuthenticationDefaults.AuthenticationScheme);
ClaimsPrincipal principal = new ClaimsPrincipal(identity);
await httpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, principal);
}
public TaxiCentralModel GetAllTaxiCentrals()
{
using (IDbConnection connection = new Microsoft.Data.SqlClient.SqlConnection(
GlobalConfig.GetConnectionString("TDMSDatabase")))
{
throw new NotImplementedException();
}
}
public void ExecuteNonQuery(string sql, string connectionStringName = "DefaultConnection")
{
using var connection = new Microsoft.Data.SqlClient.SqlConnection(_config.GetConnectionString(connectionStringName));
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(sql);
}
// Intentionally avoiding the use of DatabaseCommander to execute commands against the database
public DataTable ExecuteSql(string sql, string connectionStringName = "DefaultConnection")
{
using var connection = new Microsoft.Data.SqlClient.SqlConnection(_config.GetConnectionString(connectionStringName));
Server server = new Server(new ServerConnection(connection));
return(server.ConnectionContext.ExecuteWithResults(sql).Tables[0]);
}
static ConversionResult ToSql(MsConnection connection, IReadOnlyDictionary <string, object> context)
{
var schemaSettings = context.GetSchemaSettings();
SqlScriptBuilder builder = new(schemaSettings);
var sql = builder.BuildScript(connection);
return(new(null, "sql", sql));
}
/// <summary>
/// Asychronously executes the current collection of registered commands
/// </summary>
/// <remarks>
/// Opens the connection, begins a transaction, initializes all commands, then serially awaits the execution of each command (if async.) Finally commits transaction.
/// Upon failure, transaction is rolled-back. Null commands automatically return 1 for excution.
/// </remarks>
/// <returns>List of integers returned from each registered command</returns>
public async Task <IEnumerable <int> > ExecuteAsync(System.Threading.CancellationToken cancellationToken = default)
{
List <int> retVal = new List <int>();
using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString))
{
await conn.OpenAsync();
var trans = (Microsoft.Data.SqlClient.SqlTransaction) await conn.BeginTransactionAsync(cancellationToken);
try
{
int initialized = _Commands.Count;
for (int i = 0; i < initialized; i++)
{
if (_Commands[i] != null)
{
_Commands[i].Initialize(conn, trans);
}
}
for (int i = 0; i < _Commands.Count; i++)
{
if (_Commands[i] != null)
{
// This following line allows for chaining.
// in other words, a executing command can add more commands.
if (i >= initialized)
{
_Commands[i].Initialize(conn, trans);
}
if (_Commands[i] is ICommandAsync)
{
retVal.Add(await((ICommandAsync)_Commands[i]).ExecuteAsync(cancellationToken));
}
else
{
retVal.Add(_Commands[i].Execute());
}
}
else
{
retVal.Add(1);
}
}
await trans.CommitAsync();
}
catch (Exception)
{
try { await trans.RollbackAsync(); } catch (Exception) { }
throw;
}
finally
{
_Commands.Clear();
}
}
return((IEnumerable <int>)retVal);
}
public async Task <IActionResult> PromoteStudents(EnrStud input)
{
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText =
@"select
e.IdEnrollment, e.Semester, e.StartDate, s.IdStudy, s.Name
from Enrollment e
left join Studies s on s.IdStudy = e.IdStudy
where
s.Name = @studyName
and e.Semester = @semester";
com.Parameters.AddWithValue("studyName", input.Studies);
com.Parameters.AddWithValue("semester", input.Semester);
conn.Open();
SqlDataReader dataReader = await com.ExecuteReaderAsync();
await dataReader.ReadAsync();
Enrollment enr = new Enrollment
{
IdEnrollment = int.Parse(dataReader["IdEnrollment"].ToString()),
Semester = int.Parse(dataReader["Semester"].ToString()),
StartDate = DateTime.Parse(dataReader["StartDate"].ToString()),
Study = new Study
{
IdStudy = int.Parse(dataReader["IdStudy"].ToString()),
Name = dataReader["Name"].ToString(),
}
};
if (enr == null)
{
return(NotFound());
}
conn = new SqlConnection(_connectionString);
com = new SqlCommand();
com.Connection = conn;
com.CommandType = System.Data.CommandType.StoredProcedure;
com.CommandText = "PromoteStudents";
com.Parameters.AddWithValue("studies", input.Studies);
com.Parameters.AddWithValue("semester", input.Semester);
conn.Open();
await com.ExecuteNonQueryAsync();
return(StatusCode(200));
}
public int Save(PCAxis.Query.SavedQuery query, int?id)
{
query.Stats = null;
string pxsjson = JsonConvert.SerializeObject(query);
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand(
@"insert into
SavedQueryMeta
(
DataSourceType,
DatabaseId,
DataSourceId,
[Status],
StatusUse,
StatusChange,
OwnerId,
MyDescription,
CreatedDate,
SavedQueryFormat,
SavedQueryStorage,
QueryText,
Runs,
Fails
)
values
(
@databaseType,
@databaseId,
@mainTable,
'A',
'P',
'P',
'Anonymous',
@title,
@creationDate,
'PXSJSON',
'D',
@query,
0,
0
);
SELECT @@IDENTITY AS 'Identity';", conn);
cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type);
cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId);
cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0]));
cmd.Parameters.AddWithValue("title", "");
cmd.Parameters.AddWithValue("creationDate", DateTime.Now);
cmd.Parameters.AddWithValue("query", pxsjson);
int newid = Convert.ToInt32(cmd.ExecuteScalar());
return(newid);
}
return(-1);
}
protected override IDbConnection GetDbConnection(string connectionString)
{
var conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return(conn);
}
public override int CountActiveSessions(string applicationName)
{
Invariant.Require(applicationName.Length <= this.MaxApplicationNameLength);
using var connection = new Microsoft.Data.SqlClient.SqlConnection(DefaultConnectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = @applicationName";
command.Parameters.AddWithValue("applicationName", applicationName);
return((int)command.ExecuteScalar());
}
public bool MarkAsFailed(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("update SavedQueryMeta set UsedDate = @lastUsed, Runs = Runs + 1, Fails = Fails + 1 where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
cmd.Parameters.AddWithValue("lastUsed", DateTime.Now);
return(cmd.ExecuteNonQuery() == 1);
}
}
private static Microsoft.Data.SqlClient.SqlBulkCopy GetSqlBulkCopy(Microsoft.Data.SqlClient.SqlConnection sqlConnection, IDbContextTransaction transaction, BulkConfig config)
{
var sqlBulkCopyOptions = config.SqlBulkCopyOptions;
if (transaction == null)
{
return(new Microsoft.Data.SqlClient.SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, null));
}
else
{
var sqlTransaction = (Microsoft.Data.SqlClient.SqlTransaction)transaction.GetUnderlyingTransaction(config);
return(new Microsoft.Data.SqlClient.SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, sqlTransaction));
}
}
/// <summary>
/// Executes the current collection of registered commands
/// </summary>
/// <remarks>
/// Opens the connection, begins a transaction, initializes all command, then executes each command. Finally commits transaction.
/// Upon failure, transaction is rolled-back. Null commands automatically return 1 for execution.
/// </remarks>
/// <returns>List of integers returned from each registered command</returns>
public List <int> Execute()
{
List <int> retVal = new List <int>();
using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString))
{
conn.Open();
Microsoft.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
int initialized = _Commands.Count;
for (int i = 0; i < initialized; i++)
{
if (_Commands[i] != null)
{
_Commands[i].Initialize(conn, trans);
}
}
for (int i = 0; i < _Commands.Count; i++)
{
if (_Commands[i] != null)
{
// This following line allows for chaining.
// in other words, a executing command can add more commands.
if (i >= initialized)
{
_Commands[i].Initialize(conn, trans);
}
retVal.Add(_Commands[i].Execute());
}
else
{
retVal.Add(1);
}
}
trans.Commit();
}
catch (Exception)
{
try { trans.Rollback(); } catch (Exception) { }
throw;
}
finally
{
_Commands.Clear();
}
}
return(retVal);
}
public PCAxis.Query.SavedQuery Load(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
string query = cmd.ExecuteScalar() as string;
PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery;
return(sq);
}
return(null);
}
/// <summary>
/// Creates a DBA Instance Parameter from an established SQL Connection
/// </summary>
/// <param name="Connection">The connection to reuse</param>
public DbaInstanceParameter(Microsoft.Data.SqlClient.SqlConnection Connection)
{
InputObject = Connection;
DbaInstanceParameter tempParam = new DbaInstanceParameter(Connection.DataSource);
_ComputerName = tempParam.ComputerName;
if (tempParam.InstanceName != "MSSQLSERVER")
{
_InstanceName = tempParam.InstanceName;
}
if (tempParam.Port != 1433)
{
_Port = tempParam.Port;
}
_NetworkProtocol = tempParam.NetworkProtocol;
}
// Sql Server Connector
// TODO - Make the CreateTaxiCentral method actually save to the database
/// <summary>
/// Saves a new Taxi central to the database
/// </summary>
/// <param name="model">The Taxi central information</param>
/// <returns>The Taxi Central information, including the unique identifier.</returns>
public TaxiCentralModel CreateTaxiCentral(TaxiCentralModel model)
{
using (IDbConnection connection = new Microsoft.Data.SqlClient.SqlConnection(
GlobalConfig.GetConnectionString("TDMSDatabase")))
{
var p = new DynamicParameters();
p.Add("@Name", model.Name);
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spTaxiCentral_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@Id");
return(model);
}
}
public override bool RunTask()
{
try
{
ExecuteNonQuery(@"IF OBJECT_ID(N'dbo.ChangeLog', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ChangeLog](
[change_number] [int] NOT NULL,
[delta_set] [varchar](10) NOT NULL,
[start_dt] [datetime] NOT NULL,
[complete_dt] [datetime] NULL,
[applied_by] [varchar](100) NOT NULL,
[description] [varchar](500) NOT NULL,
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED
(
[change_number] ASC,
[delta_set] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END");
LoadExistingStoredProcedures();
LoadLocalFiles();
_task._logProcessor.Log(Source, $"Database has {_existingChangeScripts.Count} deployed changes and deploy package has {_changeScriptFiles.Count} changes to deploy. Starting sync process now!");
using (var connection = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString))
{
Server server = new Server(new ServerConnection(connection));
foreach (var storedProcedureFile in _changeScriptFiles)
{
if (!ProcessFile(storedProcedureFile, server))
{
return(false);
}
}
}
return(true);
}
catch (Exception e)
{
_task._logProcessor.Log(Source, "failed syncing stored procedures", attachment: e.ToString());
}
return(false);
}
