void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.CloneWithMasterDbName());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
public IEnumerable <Orders> GetAll() //查尋全部資料
{
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "SELECT * FROM Orders";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var orders = new List <Orders>();
var properties = typeof(Orders).GetProperties();
while (reader.Read())
{
var order = new Orders();
order = DbReaderModelBinder <Orders> .Bind(reader);
orders.Add(order);
}
reader.Close();
return(orders);
}
private void RegisterNewConnectionString(SqlConnectionString newValue)
{
if (newValue != null)
{
newValue.PropertyChanged += ConnectionStringPropertyChanged;
}
}
public IEnumerable <Orders> GetOrderDate(string OrderDate)
{
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "select * FROM Orders WHERE CONVERT(VARCHAR(25), OrderDate, 126) LIKE @OrderDate";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@OrderDate", OrderDate);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var properties = typeof(Orders).GetProperties();
var Orders = new List <Orders>();
while (reader.Read())
{
var Order = new Orders();
Order = DbReaderModelBinder <Orders> .Bind(reader);
Orders.Add(Order);
}
reader.Close();
return(Orders);
}
public void CanGetConnectionStringByName()
{
using (var store = GetDocumentStore())
{
var ravenConnectionStrings = new List <RavenConnectionString>();
var sqlConnectionStrings = new List <SqlConnectionString>();
var ravenConnectionStr = new RavenConnectionString()
{
Name = "RavenConnectionString",
TopologyDiscoveryUrls = new[] { "http://127.0.0.1:8080" },
Database = "Northwind",
};
var sqlConnectionStr = new SqlConnectionString
{
Name = "SqlConnectionString",
ConnectionString = MssqlConnectionString.Instance.VerifiedConnectionString.Value + $";Initial Catalog={store.Database}"
};
ravenConnectionStrings.Add(ravenConnectionStr);
sqlConnectionStrings.Add(sqlConnectionStr);
var result1 = store.Maintenance.Send(new PutConnectionStringOperation <RavenConnectionString>(ravenConnectionStr));
Assert.NotNull(result1.RaftCommandIndex);
var result2 = store.Maintenance.Send(new PutConnectionStringOperation <SqlConnectionString>(sqlConnectionStr));
Assert.NotNull(result2.RaftCommandIndex);
var result = store.Maintenance.Send(new GetConnectionStringsOperation(connectionStringName: sqlConnectionStr.Name, type: sqlConnectionStr.Type));
Assert.True(result.SqlConnectionStrings.Count > 0);
Assert.True(result.RavenConnectionStrings.Count == 0);
}
}
public IEnumerable <OrderDetails> GetAll() //查尋全部資料
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
return(connection.Query <OrderDetails>("SELECT * FROM OrderDetails "));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM OrderDetails";
//SqlCommand command = new SqlCommand(sql, connection);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var orderDetails = new List<OrderDetails>();
//var properties = typeof(OrderDetails).GetProperties();
//while (reader.Read())
//{
// var orderDetail = new OrderDetails();
// orderDetail = DbReaderModelBinder<OrderDetails>.Bind(reader);
// orderDetails.Add(orderDetail);
//}
//reader.Close();
//return orderDetails;
}
private string SIConnectionString()
{
var settings = Properties.Settings.Default;
var connStr = new SqlConnectionString(settings.ServerQNS, settings.DatabaseSI, settings.UserSI, settings.PasswordSI);
return(connStr.ConnString());
}
private string DbConnectionString()
{
var settings = Properties.Settings.Default;
var connStr = new SqlConnectionString(settings.AppDbServer, settings.Database, settings.User, settings.Password);
return(connStr.ConnString());
}
public Products FindById(int ProductID)
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
var result = connection.Query <Products>("SELECT * FROM Products WHERE ProductID = @ProductID", new { ProductID });
Products product = null;
foreach (var item in result)
{
product = item;
}
return(product);
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM Products WHERE ProductID = @ProductID";
//SqlCommand command = new SqlCommand(sql, connection);
//command.Parameters.AddWithValue("@ProductID", ProductID);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Products product = null;
//var properties = typeof(Products).GetProperties();
//while (reader.Read())
//{
// product = new Products();
// product = DbReaderModelBinder<Products>.Bind(reader);
//}
//reader.Close();
//return product;
}
public IEnumerable <ProductFormat> GetAll()
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
return(connection.Query <ProductFormat>("SELECT * FROM ProductFormat "));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM ProductFormat";
//SqlCommand command = new SqlCommand(sql, connection);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var properties = typeof(ProductFormat).GetProperties();
//var productFormats = new List<ProductFormat>();
//while (reader.Read())
//{
// var productFormat = new ProductFormat();
// productFormat = DbReaderModelBinder<ProductFormat>.Bind(reader);
// productFormats.Add(productFormat);
//}
//reader.Close();
//return productFormats;
}
public async Task <IActionResult> Post([FromBody] GameSession gameSession)
{
SqlConnectionString sqlConnectionString = new SqlConnectionString();
string connectionString = sqlConnectionString.GetSqlConnectionString();
Question questionHelper = new Question()
{
SqlConnectionString = connectionString
};
User userHelper = new User()
{
SqlConnectionString = connectionString
};
JWT jwtHelper = new JWT();
Buzz buzzHelper = new Buzz()
{
SqlConnectionString = connectionString
};
int user = jwtHelper.GetUserFromJWT(gameSession.JWT);
Question.QuestionStatus questionStatus =
await questionHelper.GetQuestionStatusFromUserAsync(jwtHelper.GetUserFromJWT(gameSession.JWT));
await buzzHelper.BuzzAsync(user, questionStatus.question);
await _hubContext.Clients.All.SendAsync(
$"User{await userHelper.GetAlexFromQuestionAsync(questionStatus.question)}",
await userHelper.GetUserNameFromUserAsync(user));
return(new OkObjectResult(JsonConvert.SerializeObject(new { Success = true })));
}
public async Task <IActionResult> Post([FromBody] CreateUserViewModel createUserViewModel)
{
string userName = createUserViewModel.Name;
string gameCode = createUserViewModel.GameCode.ToUpper();
SqlConnectionString sqlConnectionString = new SqlConnectionString();
string connectionString = sqlConnectionString.GetSqlConnectionString();
Game gameHelper = new Game()
{
SqlConnectionString = connectionString
};
User userHelper = new User()
{
SqlConnectionString = connectionString
};
JWT jwtHelper = new JWT();
int game = await gameHelper.GetGameFromCodeAsync(gameCode);
string jwt = await jwtHelper.GenerateJwtAsync(await userHelper.CreateUserAsync(userName, game, false),
game,
userName,
gameCode,
false,
Environment.GetEnvironmentVariable("JWT_KEY"));
var returnObject = new { JWT = jwt };
return((ActionResult) new OkObjectResult(JsonConvert.SerializeObject(returnObject)));
}
public void Create(ShoppingCart model)
{
//connection.Execute("INSERT INTO ShoppingCart VALUES ( @MemberID, @ProductFormatID, @Quantity, @UnitPrice )",
// new
// {
// model.MemberID,
// model.ProductFormatID,
// model.Quantity,
// model.UnitPrice
// });
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "INSERT INTO ShoppingCart VALUES ( @MemberID, @ProductID, @ProductFormatID, @Quantity)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@MemberID", model.MemberID);
command.Parameters.AddWithValue("@ProductID", model.ProductID);
command.Parameters.AddWithValue("@ProductFormatID", model.ProductFormatID);
command.Parameters.AddWithValue("@Quantity", model.Quantity);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public IEnumerable <Members> GetAll()
{
IDbConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
return(connection.Query <Members>("SELECT * FROM Members"));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM Members";
//SqlCommand command = new SqlCommand(sql, connection);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var properties = typeof(Members).GetProperties();
//var members = new List<Members>();
//while (reader.Read())
//{
// var member = new Members();
// member = DbReaderModelBinder<Members>.Bind(reader);
// members.Add(member);
//}
//reader.Close();
//return members;
}
public IEnumerable <Orders> GetStatus(string Status)
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
return(connection.Query <Orders>("select * FROM Orders WHERE Status = @Status", new { Status }));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM Orders WHERE Status = @Status";
//SqlCommand command = new SqlCommand(sql, connection);
//command.Parameters.AddWithValue("@Status", status);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var orders = new List<Orders>();
//var properties = typeof(Orders).GetProperties();
//while (reader.Read())
//{
// var order = new Orders();
// order = DbReaderModelBinder<Orders>.Bind(reader);
// orders.Add(order);
//}
//reader.Close();
//return orders;
}
private void ConfigureRfidServices(IServiceCollection services)
{
// database
var connectionString = SqlConnectionString.Create(this.Configuration.GetConnectionString("Rfid"));
services.AddSingleton(connectionString);
services.AddSingleton <SqlConnectionFactory>();
services.AddSingleton <Database.Database>();
// administration
var adminPasswordHasher = new PasswordHasher <AdministrationUser>();
services.AddSingleton <IPasswordHasher <AdministrationUser> >(adminPasswordHasher);
services.AddSingleton <Areas.Administration.Commands.CommandFactory>();
// auth
services.AddSingleton <Areas.Auth.Services.Auth>();
var authPasswordHasher = new PasswordHasher <AuthUser>();
services.AddSingleton <IPasswordHasher <AuthUser> >(authPasswordHasher);
services.AddSingleton <Areas.Auth.Commands.CommandFactory>();
// log
services.AddSingleton <Areas.Log.Commands.CommandFactory>();
// access control
services.AddSingleton <Areas.AccessControl.Commands.CommandFactory>();
}
public static void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.GetMasterConnection());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
public void CanGetConnectionStringByName()
{
using (var store = GetDocumentStore())
{
var ravenConnectionStrings = new List <RavenConnectionString>();
var sqlConnectionStrings = new List <SqlConnectionString>();
var ravenConnectionStr = new RavenConnectionString()
{
Name = "RavenConnectionString",
TopologyDiscoveryUrls = new[] { "http://127.0.0.1:8080" },
Database = "Northwind",
};
var sqlConnectionStr = new SqlConnectionString
{
Name = "SqlConnectionString",
ConnectionString = SqlEtlTests.GetConnectionString(store)
};
ravenConnectionStrings.Add(ravenConnectionStr);
sqlConnectionStrings.Add(sqlConnectionStr);
store.Maintenance.Server.Send(new PutConnectionStringOperation <RavenConnectionString>(ravenConnectionStr, store.Database));
store.Maintenance.Server.Send(new PutConnectionStringOperation <SqlConnectionString>(sqlConnectionStr, store.Database));
var result = store.Maintenance.Server.Send(new GetConnectionStringsOperation(store.Database, connectionStringName: sqlConnectionStr.Name, type: sqlConnectionStr.Type));
Assert.True(result.SqlConnectionStrings.Count > 0);
Assert.True(result.RavenConnectionStrings.Count == 0);
}
}
public IEnumerable <ShoppingCart> FindByMemberID(string MemberID)
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
var result = connection.Query <ShoppingCart>("SELECT * FROM ShoppingCart WHERE MemberID = @MemberID", new { MemberID });
return(result);
}
public static Result <TextSetup> FromDataGroups(DataGroups dataGroups, string adapterName, string fileName)
{
var connectionsGroup = dataGroups.ConnectionsGroup;
var commandsGroup = dataGroups.CommandsGroup;
var adaptersGroup = dataGroups.AdaptersGroup;
return
(from adapterGraph in adaptersGroup.RequireGroup(adapterName)
from connectionName in adapterGraph.RequireValue("connection")
from connectionGroup in connectionsGroup.RequireGroup(connectionName)
let commandName = adaptersGroup.GetValue("command").DefaultTo(() => adapterName)
from commandGraph in commandsGroup.RequireGroup(commandName)
let connection = new Connection(connectionGroup)
{
["file"] = fileName
}
from connectionString in SqlConnectionString.FromConnection(connection)
from command in Command.FromGroup(commandGraph)
from parameters in Data.Parameters.Parameters.FromGroup(adapterGraph.GetGroup("parameters"))
from fields in Data.Fields.Fields.FromGroup(adapterGraph.GetGroup("fields"))
select new TextSetup
{
CommandText = command.Text,
CommandTimeout = command.CommandTimeout,
ConnectionString = connectionString,
Parameters = parameters,
Fields = fields
});
}
public IEnumerable <Employees> GetAll()
{
IDbConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
return(connection.Query <Employees>("SELECT * FROM employees"));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM employees";
//SqlCommand command = new SqlCommand(sql, connection);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var employees = new List<Employees>();
//while (reader.Read())
//{
// var employee = new Employees();
// employee = DbReaderModelBinder<Employees>.Bind(reader);
// employees.Add(employee);
//}
//reader.Close();
//return employees;
}
public IEnumerable <Employees> FindByHireYear(int startYear, int endYear)
{
IDbConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
return(connection.Query <Employees>("SELECT * FROM Employees WHERE YEAR(HireDate) BETWEEN @startYear AND @endYear ORDER BY HireDate DESC", new { startYear, endYear }));
//SqlConnection connection = new SqlConnection(
// SqlConnectionString.ConnectionString());
//var sql = "SELECT * FROM Employees WHERE YEAR(HireDate) BETWEEN @startYear AND @endYear ORDER BY HireDate DESC";
//SqlCommand command = new SqlCommand(sql, connection);
//command.Parameters.AddWithValue("@startYear", startYear);
//command.Parameters.AddWithValue("@endYear", endYear);
//connection.Open();
//var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//var employees = new List<Employees>();
//while (reader.Read())
//{
// Employees employee = new Employees();
// employee = DbReaderModelBinder<Employees>.Bind(reader);
// employees.Add(employee);
//}
//reader.Close();
//return employees;
}
public string GetConnectionString()
{
ISqlConnection sqlprovider = new SqlConnectionString(config);
SqlConnectionStringBuilder sqlBuilder = sqlprovider.GetConnectionStringBuilder();
return BuildEntityFrameworkConnection(sqlBuilder, config.Database.EntityFrameworkEntitiesName).ToString();
}
public LoginProcessor(TdsPackage package, SqlConnectionString dbConnectionOptions)
{
_package = package;
_dbConnectionOptions = dbConnectionOptions;
Connect(package.Writer.InstanceName, dbConnectionOptions.MARS);
Login();
}
public override string ToString()
{
var cosmosString = CosmosAvailable
? $"CosmosConn = {CosmosConnectionString.Substring(0, 20)}..., DbName = {CosmosDatabaseName}"
: "(no cosmos)";
return($"{Title}: Menu = {MenuSet}, sqlCon = {SqlConnectionString.Substring(0, 20)}..., {cosmosString}");
}
public Products FindNextProductID()
{
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "SELECT TOP 1 * FROM Products ORDER BY ProductID DESC";
return(connection.Query <Products>(sql).FirstOrDefault());
}
public IEnumerable <Products> Get()
{
var db = new SqlConnection(SqlConnectionString.ConnectionString());
var products = db.Query <Products>("SELECT * FROM Products");
return(products);
}
public void CanGetAllConnectionStrings()
{
using (var store = GetDocumentStore())
{
var ravenConnectionStrings = new List <RavenConnectionString>();
var sqlConnectionStrings = new List <SqlConnectionString>();
var elasticSearchConnectionStrings = new List <ElasticSearchConnectionString>();
for (var i = 0; i < 5; i++)
{
var ravenConnectionStr = new RavenConnectionString()
{
Name = $"RavenConnectionString{i}",
TopologyDiscoveryUrls = new[] { $"http://127.0.0.1:808{i}" },
Database = "Northwind",
};
var sqlConnectionStr = new SqlConnectionString
{
Name = $"SqlConnectionString{i}",
ConnectionString = MssqlConnectionString.Instance.VerifiedConnectionString.Value + $";Initial Catalog={store.Database}"
};
var elasticConnectionStr = new ElasticSearchConnectionString
{
Name = $"ElasticConnectionString{i}",
Nodes = new[] { $"http://127.0.0.1:808{i}" },
};
ravenConnectionStrings.Add(ravenConnectionStr);
sqlConnectionStrings.Add(sqlConnectionStr);
elasticSearchConnectionStrings.Add(elasticConnectionStr);
var result1 = store.Maintenance.Send(new PutConnectionStringOperation <RavenConnectionString>(ravenConnectionStr));
Assert.NotNull(result1.RaftCommandIndex);
var result2 = store.Maintenance.Send(new PutConnectionStringOperation <SqlConnectionString>(sqlConnectionStr));
Assert.NotNull(result2.RaftCommandIndex);
var result3 = store.Maintenance.Send(new PutConnectionStringOperation <ElasticSearchConnectionString>(elasticConnectionStr));
Assert.NotNull(result3.RaftCommandIndex);
}
var result = store.Maintenance.Send(new GetConnectionStringsOperation());
Assert.NotNull(result.SqlConnectionStrings);
Assert.NotNull(result.RavenConnectionStrings);
Assert.NotNull(result.ElasticSearchConnectionStrings);
for (var i = 0; i < 5; i++)
{
result.SqlConnectionStrings.TryGetValue($"SqlConnectionString{i}", out var sql);
Assert.Equal(sql?.ConnectionString, sqlConnectionStrings[i].ConnectionString);
result.ElasticSearchConnectionStrings.TryGetValue($"ElasticConnectionString{i}", out var elastic);
Assert.Equal(elastic?.Nodes, elasticSearchConnectionStrings[i].Nodes);
result.RavenConnectionStrings.TryGetValue($"RavenConnectionString{i}", out var raven);
Assert.Equal(raven?.TopologyDiscoveryUrls, ravenConnectionStrings[i].TopologyDiscoveryUrls);
Assert.Equal(raven?.Database, ravenConnectionStrings[i].Database);
}
}
}
public TdsConnection(ITdsStream tdsStream, SqlConnectionString dbConnectionOptions)
{
_tdsStream = tdsStream;
TdsPackage = new TdsPackage(_tdsStream);
var loginProcessor = new LoginProcessor(TdsPackage, dbConnectionOptions);
StreamParser = new TdsStreamParser(TdsPackage, loginProcessor);
StreamParser.ParseInput();
_messageCountAfterLogin = SqlMessages.Count;
}
public void Delete(int ShoppingCartID)
{
IDbConnection connection = new SqlConnection(SqlConnectionString.ConnectionString());
connection.Execute("DELETE FROM ShoppingCart WHERE ShoppingCartID = @ShoppingCartID",
new
{
ShoppingCartID
});
}
public static bool TestConnection(this SqlConnectionString connectionString,
bool showSuccessDialog, bool showFailureDialog, ref string failureMessage)
{
if (string.IsNullOrEmpty(connectionString.Server))
{
if (showFailureDialog)
{
MessageBox.Show("Server name not specified", "Connection failed", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
}
return(false);
}
if (string.IsNullOrEmpty(connectionString.Database))
{
if (showFailureDialog)
{
MessageBox.Show("Initial Catalog not specified", "Connection failed", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
}
return(false);
}
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
}
catch (SqlException ex)
{
failureMessage = ex.Message;
if (showFailureDialog)
{
MessageBox.Show("The following error occured:\r\n" + ex.Message, "Connection failed",
MessageBoxButton.OK,
MessageBoxImage.Asterisk);
}
return(false);
}
if (showSuccessDialog)
{
MessageBox.Show("Successfully connected to the databse", "Connection successful",
MessageBoxButton.OK,
MessageBoxImage.Asterisk);
}
return(true);
}
}
public List<string> GetDatabases(SqlConnectionString connectionString)
{
var databases = new List<string>();
using (var conn = new SqlConnection(connectionString.WithDatabase("master")))
{
conn.Open();
var serverConnection = new ServerConnection(conn);
var server = new Server(serverConnection);
databases.AddRange(from Database database in server.Databases select database.Name);
}
return databases;
}
public List<DatabaseTable> GetTables(SqlConnectionString connectionString)
{
using (var conn = new SqlConnection(connectionString.WithDatabase("master")))
{
conn.Open();
var serverConnection = new ServerConnection(conn);
var server = new Server(serverConnection);
return
server
.Databases[connectionString.Database]
.Tables
.Cast<Table>()
.Select(t => new DatabaseTable
{
Name = t.Name,
RowCount = t.RowCount
})
.ToList();
}
}
public List<string> GetDatabases(SqlConnectionString connectionString)
{
var databases = new List<string>();
try
{
using (var conn = new SqlConnection(connectionString.WithDatabase("master")))
{
conn.Open();
var serverConnection = new ServerConnection(conn);
var server = new Server(serverConnection);
databases.AddRange(server.Databases.Cast<Database>().Select(database => database.Name));
}
}
catch (Exception)
{
return null;
}
return databases;
}
public void AddToSqlConnectionStrings(SqlConnectionString sqlConnectionString)
{
base.AddObject("SqlConnectionStrings", sqlConnectionString);
}
public void standard_sql_connection_string_with_userid_password()
{
// Arrange
InstallerConfig config = new InstallerConfig();
config.Database.DataSource = @"kingwilder-pc\sqlserver";
config.Database.InitialCatalog = "CommerceApp";
config.Database.UseTrustedConnection = false;
config.Database.UserName = "******";
config.Database.Password = "******";
IConnectionProvider provider = new SqlConnectionString(config);
// Act
var actual = provider.GetConnectionString();
var expected = @"Data Source=kingwilder-pc\sqlserver;Initial Catalog=CommerceApp;User ID=dbadminuser;Password=dbadminpassword";
// Assert
Assert.AreEqual(expected, actual);
}
public void standard_sql_connection_string_with_integrated_security()
{
// Arrange
InstallerConfig config = new InstallerConfig();
config.Database.DataSource = @"kingwilder-pc\sqlserver";
config.Database.InitialCatalog = "BloggerApp";
config.Database.UseTrustedConnection = true;
ISqlConnection provider = new SqlConnectionString(config);
// Act
var actual = provider.GetConnectionString();
var expected = @"Data Source=kingwilder-pc\sqlserver;Initial Catalog=BloggerApp;Integrated Security=True";
// Assert
Assert.AreEqual(expected, actual);
}
public void standard_sql_connection_string_with_all_parameters_returns_integrated_security()
{
// Arrange
InstallerConfig config = new InstallerConfig();
config.Database.DataSource = @"kingwilder-pc\sqlserver";
config.Database.InitialCatalog = "CommerceApp";
config.Database.UseTrustedConnection = true;
config.Database.UserName = "******";
config.Database.Password = "******";
IConnectionProvider provider = new SqlConnectionString(config);
// Act
var actual = provider.GetConnectionString();
var expected = @"Data Source=kingwilder-pc\sqlserver;Initial Catalog=CommerceApp;Integrated Security=True";
// Assert
Assert.AreEqual(expected, actual);
}
public static SqlConnectionString CreateSqlConnectionString(int id, string connectionString)
{
SqlConnectionString sqlConnectionString = new SqlConnectionString();
sqlConnectionString.Id = id;
sqlConnectionString.ConnectionString = connectionString;
return sqlConnectionString;
}
// -----------
// Constructor
// -----------
internal SqlConnectionContainer(SqlConnectionContainerHashHelper hashHelper, string appDomainKey, bool useDefaults) {
IntPtr hscp;
Bid.NotificationsScopeEnter(out hscp, "<sc.SqlConnectionContainer|DEP> %d#, queue: '%ls'", ObjectID, hashHelper.Queue);
bool setupCompleted = false;
try {
_hashHelper = hashHelper;
string guid = null;
// If default, queue name is not present on hashHelper at this point - so we need to
// generate one and complete initialization.
if (useDefaults) {
guid = Guid.NewGuid().ToString();
_queue = SQL.SqlNotificationServiceDefault+"-"+guid;
_hashHelper.ConnectionStringBuilder.ApplicationName = _queue; // Used by cleanup sproc.
}
else {
_queue = _hashHelper.Queue;
}
#if DEBUG
SqlConnectionString connectionStringOptions = new SqlConnectionString(_hashHelper.ConnectionStringBuilder.ConnectionString);
Bid.NotificationsTrace("<sc.SqlConnectionContainer|DEP> Modified connection string: '%ls'\n", connectionStringOptions.UsersConnectionStringForTrace());
#endif
// Always use ConnectionStringBuilder since in default case it is different from the
// connection string used in the hashHelper.
_con = new SqlConnection(_hashHelper.ConnectionStringBuilder.ConnectionString); // Create connection and open.
// Assert permission for this particular connection string since it differs from the user passed string
// which we have already demanded upon.
SqlConnectionString connStringObj = (SqlConnectionString) _con.ConnectionOptions;
connStringObj.CreatePermissionSet().Assert();
if (connStringObj.LocalDBInstance != null ) {
// If it is LocalDB, we demanded LocalDB permissions too
LocalDBAPI.AssertLocalDBPermissions();
}
_con.Open();
_cachedServer = _con.DataSource; // SQL BU DT 390531.
if (!_con.IsYukonOrNewer) { // After open, verify Yukon or later.
throw SQL.NotificationsRequireYukon();
}
if (hashHelper.Identity != null) {
// For now, DbConnectionPoolIdentity does not cache WindowsIdentity.
// That means for every container creation, we create a WindowsIdentity twice.
// We may want to improve this.
_windowsIdentity = DbConnectionPoolIdentity.GetCurrentWindowsIdentity();
}
_escapedQueueName = SqlConnection.FixupDatabaseTransactionName(_queue); // Properly escape to prevent SQL Injection.
_appDomainKeyHash = new Dictionary<string, int>(); // Dictionary stores the Start/Stop refcount per AppDomain for this container.
_com = new SqlCommand();
_com.Connection = _con;
// SQL BU DT 391534 - determine if broker is enabled on current database.
_com.CommandText = "select is_broker_enabled from sys.databases where database_id=db_id()";
if (!(bool) _com.ExecuteScalar()) {
throw SQL.SqlDependencyDatabaseBrokerDisabled();
}
_conversationGuidParam = new SqlParameter("@p1", SqlDbType.UniqueIdentifier);
_timeoutParam = new SqlParameter("@p2", SqlDbType.Int);
_timeoutParam.Value = 0; // Timeout set to 0 for initial [....] query.
_com.Parameters.Add(_timeoutParam);
setupCompleted = true;
// connection with the server has been setup - from this point use TearDownAndDispose() in case of error
// Create standard query.
_receiveQuery = "WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from " + _escapedQueueName + "), TIMEOUT @p2;";
// Create queue, service, [....] query, and async query on user thread to ensure proper
// init prior to return.
if (useDefaults) { // Only create if user did not specify service & database.
_sprocName = SqlConnection.FixupDatabaseTransactionName(SQL.SqlNotificationStoredProcedureDefault+"-"+guid);
CreateQueueAndService(false); // Fail if we cannot create service, queue, etc.
}
else {
// Continue query setup.
_com.CommandText = _receiveQuery;
_endConversationQuery = "END CONVERSATION @p1; ";
_concatQuery = _endConversationQuery + _receiveQuery;
}
bool ignored = false;
IncrementStartCount(appDomainKey, out ignored);
// Query synchronously once to ensure everything is working correctly.
// We want the exception to occur on start to immediately inform caller.
SynchronouslyQueryServiceBrokerQueue();
_timeoutParam.Value = _defaultWaitforTimeout; // [....] successful, extend timeout to 60 seconds.
AsynchronouslyQueryServiceBrokerQueue();
}
catch (Exception e) {
if (!ADP.IsCatchableExceptionType(e)) {
throw;
}
ADP.TraceExceptionWithoutRethrow(e); // Discard failure, but trace for now.
if (setupCompleted) {
// Be sure to drop service & queue. This may fail if create service & queue failed.
// This method will not drop unless we created or service & queue ref-count is 0.
TearDownAndDispose();
}
else {
// connection has not been fully setup yet - cannot use TearDownAndDispose();
// we have to dispose the command and the connection to avoid connection leaks (until GC collects them).
if (_com != null) {
_com.Dispose();
_com = null;
}
if (_con != null) {
_con.Dispose();
_con = null;
}
}
throw;
}
finally {
Bid.ScopeLeave(ref hscp);
}
}
// ------------
// Stop methods
// ------------
internal bool Stop( string connectionString,
out string server,
out DbConnectionPoolIdentity identity,
out string user,
out string database,
ref string queueService,
string appDomainKey,
out bool appDomainStop) {
IntPtr hscp;
Bid.NotificationsScopeEnter(out hscp, "<sc.SqlDependencyProcessDispatcher.Stop|DEP> %d#, queue: '%ls'", ObjectID, queueService);
try {
Debug.Assert(this == _staticInstance, "Instance method called on non _staticInstance instance!");
server = null; // Reset out param.
identity = null;
user = null;
database = null;
appDomainStop = false;
SqlConnectionStringBuilder connectionStringBuilder = null;
SqlConnectionContainerHashHelper hashHelper = GetHashHelper( connectionString,
out connectionStringBuilder,
out identity,
out user,
queueService);
#if DEBUG
SqlConnectionString connectionStringOptions = new SqlConnectionString(connectionStringBuilder.ConnectionString);
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Stop|DEP> Modified connection string: '%ls'\n", connectionStringOptions.UsersConnectionStringForTrace());
#endif
bool stopped = false;
lock (_connectionContainers) {
if (_connectionContainers.ContainsKey(hashHelper)) {
SqlConnectionContainer container = _connectionContainers[hashHelper];
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Stop|DEP> %d#, hashtable hit, container: %d\n", ObjectID, container.ObjectID);
server = container.Server; // Return server, database, and queue info for use by calling SqlDependency.
database = container.Database;
queueService = container.Queue;
if (container.Stop(appDomainKey, out appDomainStop)) { // Stop can be blocking if refCount == 0 on container.
stopped = true;
_connectionContainers.Remove(hashHelper); // Remove from collection.
}
}
else {
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Stop|DEP> %d#, hashtable miss.\n", ObjectID);
}
}
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Stop|DEP> %d#, stopped: %d\n", ObjectID, stopped);
return stopped;
}
finally {
Bid.ScopeLeave(ref hscp);
}
}
private bool Start( string connectionString,
out string server,
out DbConnectionPoolIdentity identity,
out string user,
out string database,
ref string queueService,
string appDomainKey,
SqlDependencyPerAppDomainDispatcher dispatcher,
out bool errorOccurred,
out bool appDomainStart,
bool useDefaults) {
IntPtr hscp;
Bid.NotificationsScopeEnter(out hscp, "<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, queue: '%ls', appDomainKey: '%ls', perAppDomainDispatcher ID: '%d'", ObjectID, queueService, appDomainKey, dispatcher.ObjectID);
try {
Debug.Assert(this == _staticInstance, "Instance method called on non _staticInstance instance!");
server = null; // Reset out params.
identity = null;
user = null;
database = null;
errorOccurred = false;
appDomainStart = false;
lock (_sqlDependencyPerAppDomainDispatchers) {
if (!_sqlDependencyPerAppDomainDispatchers.ContainsKey(appDomainKey)) {
_sqlDependencyPerAppDomainDispatchers[appDomainKey] = dispatcher;
}
}
SqlConnectionStringBuilder connectionStringBuilder = null;
SqlConnectionContainerHashHelper hashHelper = GetHashHelper( connectionString,
out connectionStringBuilder,
out identity,
out user,
queueService);
#if DEBUG
SqlConnectionString connectionStringOptions = new SqlConnectionString(connectionStringBuilder.ConnectionString);
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> Modified connection string: '%ls'\n", connectionStringOptions.UsersConnectionStringForTrace());
#endif
bool started = false;
SqlConnectionContainer container = null;
lock (_connectionContainers) {
if (!_connectionContainers.ContainsKey(hashHelper)) {
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, hashtable miss, creating new container.\n", ObjectID);
container = new SqlConnectionContainer(hashHelper, appDomainKey, useDefaults);
_connectionContainers.Add(hashHelper, container);
started = true;
appDomainStart = true;
}
else {
container = _connectionContainers[hashHelper];
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, hashtable hit, container: %d\n", ObjectID, container.ObjectID);
if (container.InErrorState) {
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, container: %d is in error state!\n", ObjectID, container.ObjectID);
errorOccurred = true; // Set outparam errorOccurred true so we invalidate on Start().
}
else {
container.IncrementStartCount(appDomainKey, out appDomainStart);
}
}
}
if (useDefaults && !errorOccurred) { // Return server, database, and queue for use by SqlDependency.
server = container.Server;
database = container.Database;
queueService = container.Queue;
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, default service: '%ls', server: '%ls', database: '%ls'\n", ObjectID, queueService, server, database);
}
Bid.NotificationsTrace("<sc.SqlDependencyProcessDispatcher.Start|DEP> %d#, started: %d\n", ObjectID, started);
return started;
}
finally {
Bid.ScopeLeave(ref hscp);
}
}
