//
// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements. See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License. You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// .Net StockTrader Sample WCF Application for Benchmarking, Performance Analysis and Design Considerations for Service-Oriented Applications
//===============================================================================================
// Customer is part of the SQLServer DAL for StockTrader. This is called from the
// BSL to execute commands against the database. It is constructed to use one SqlConnection per
// instance. Hence, BSLs that use this DAL should always be instanced properly.
// The DAL will work with both ADO.NET and System.Transactions or ServiceComponents/Enterprise
// Services attributed transactions [autocomplete]. When using ADO.NET transactions,
// The BSL will control the transaction boundaries with calls to dal.BeginTransaction();
// dal.CommitTransaction(); dal.RollbackTransaction().
//===============================================================================================
//======================================================================================================
// Code originally contributed by Microsoft Corporation.
// This contribution to the Stonehenge project is limited strictly
// to the source code that is submitted in this submission.
// Any technology, including underlying platform technology,
// that is referenced or required by the submitted source code
// is not a part of the contribution.
// For example and not by way of limitation,
// any systems/Windows libraries (WPF, WCF, ASP.NET etc.)
// required to run the submitted source code is not a part of the contribution
//======================================================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using Trade.IDAL;
using Trade.Utility;
using Trade.ConfigServiceDataContract;
namespace Trade.DALMySQL
{
public class Config : IConfig
{
public Config()
{
}
//Constructor for internal DAL-DAL calls to use an existing DB connection.
public Config(MySqlConnection conn)
{
_internalConnection = conn;
}
//_internalConnection: Used by a DAL instance such that a DAL instance,
//associated with a BSL instance, will work off a single connection between BSL calls.
private MySqlConnection _internalConnection;
//_internalADOTransaction: Used only when doing ADO.NET transactions.
//This will be completely ignored when null, and not attached to a cmd object
//In MySQLHelper unless it has been initialized explicitly in the BSL with a
//dal.BeginADOTransaction(). See app config setting in web.config and
//Trade.BusinessServiceHost.exe.config "Use System.Transactions Globally" which determines
//whether user wants to run with ADO transactions or System.Transactions. The DAL itself
//is built to be completely agnostic and will work with either.
private MySqlTransaction _internalADOTransaction;
//Used only when doing ADO.NET transactions.
public void BeginADOTransaction()
{
if (_internalConnection.State != ConnectionState.Open)
_internalConnection.Open();
_internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
}
//Used only when explicitly using ADO.NET transactions from the BSL.
public void RollBackTransaction()
{
_internalADOTransaction.Rollback();
_internalADOTransaction = null;
}
//Used only when explicitly using ADO.NET transactions from the BSL.
public void CommitADOTransaction()
{
_internalADOTransaction.Commit();
_internalADOTransaction = null;
}
public void Open(string connString)
{
if (_internalConnection == null)
_internalConnection = new MySqlConnection(connString);
if (_internalConnection.State != ConnectionState.Open)
_internalConnection.Open();
}
public void Close()
{
if (_internalConnection != null && _internalConnection.State != ConnectionState.Closed)
_internalConnection.Close();
}
private const string PARM_CLIENT = "?Client";
private const string PARM_BS = "?Bs";
private const string PARM_OPS = "?Ops";
private const string PARM_URL = "?Url";
private const string PARM_SERVICENAME = "?ServiceName";
private const string PARM_SEC = "?Sec";
//SQL Queries to lookup configuration info for given services
private const string MYSQL_GET_CLIENT_CONFIG = @"SELECT c.BS, s.URL, s.SEC FROM clienttobs c INNER JOIN service s ON c.BS = s.SERVICENAME WHERE c.CLIENT = ?Client";
private const string MYSQL_GET_BS_CONFIG = @"SELECT b.OPS, b.URL, b.SEC, d.DBNAME, d.HOSTNAME, d.PORT FROM (SELECT 1 as Row, b1.OPS, s.URL, s.SEC FROM BSTOOPS as b1 INNER JOIN SERVICE AS s ON b1.OPS = s.SERVICENAME WHERE b1.BS = ?Bs) as b INNER JOIN (SELECT 1 as Row, DBNAME, HOSTNAME, PORT FROM DBCONFIG WHERE ACTIVE='Y') as d ON b.Row = d.Row";
private const string MYSQL_GET_OPS_CONFIG = @"SELECT DBNAME, HOSTNAME, PORT FROM DBCONFIG WHERE ACTIVE='Y'";
private const string MYSQL_FIND_CLIENT_TO_BS = @"SELECT b.CLIENT, b.BS FROM CLIENTTOBS as b WHERE b.CLIENT = ?Client";
private const string MYSQL_SET_CLIENT_TO_BS = @"INSERT INTO CLIENTTOBS (CLIENT, BS) VALUES (?Client, ?Bs)";
private const string MYSQL_UPDATE_CLIENT_TO_BS = @"UPDATE CLIENTTOBS SET BS=?Bs WHERE CLIENT=?Client";
private const string MYSQL_FIND_BS_TO_OPS = @"SELECT b.BS, b.OPS FROM BSTOOPS as b WHERE b.BS = ?Bs";
private const string MYSQL_FIND_SERVICE = @"SELECT SERVICENAME, URL, SEC FROM SERVICE WHERE SERVICENAME = ?ServiceName";
private const string MYSQL_SET_BS_TO_OPS = @"INSERT INTO BSTOOPS (BS, OPS) VALUES (?Bs, ?Ops)";
private const string MYSQL_UPDATE_BS_TO_OPS = @"UPDATE BSTOOPS SET OPS=?Ops WHERE BS=?Bs";
private const string MYSQL_COUNT_BS = @"SELECT COUNT(*) as count FROM SERVICE WHERE SERVICENAME LIKE '%_BS' OR SERVICENAME LIKE '%_BSSEC'";
private const string MYSQL_COUNT_OPS = @"SELECT COUNT(*) as count FROM SERVICE WHERE SERVICENAME LIKE '%_OPS' OR SERVICENAME LIKE '%_OPSSEC'";
private const string MYSQL_GET_BS = @"SELECT SERVICENAME, URL, SEC FROM SERVICE WHERE SERVICENAME LIKE '%_BS' OR SERVICENAME LIKE '%_BSSEC'";
private const string MYSQL_GET_OPS = @"SELECT SERVICENAME, URL, SEC FROM SERVICE WHERE SERVICENAME LIKE '%_OPS' OR SERVICENAME LIKE '%_OPSSEC'";
private const string MYSQL_UPDATE_SERVICE_LOCATION = @"UPDATE SERVICE SET URL = ?Url, SEC = ?Sec WHERE SERVICENAME = ?ServiceName";
private const string MYSQL_INSERT_SERVICE_LOCATION = @"INSERT INTO SERVICE (SERVICENAME, URL, SEC) VALUES (?ServiceName, ?Url, ?Sec)";
///
/// Looks for a service location with the given ServiceName in the database. If found,
/// the URL and SEC values are updated. If not found, a new service location is inserted.
///
/// Service Location consisting on a Service Name, URL and SEC value
public void SetServiceLocation(ServiceLocation location)
{
MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_SERVICENAME, MySqlDbType.VarChar, 50) };
parm[0].Value = location.ServiceName;
MySqlParameter[] parm2 = new MySqlParameter[] { new MySqlParameter(PARM_SERVICENAME, MySqlDbType.VarChar, 50), new MySqlParameter(PARM_URL, MySqlDbType.VarChar, 500), new MySqlParameter(PARM_SEC, MySqlDbType.Bit) };
parm2[0].Value = location.ServiceName;
parm2[1].Value = location.ServiceURL;
parm2[2].Value = location.Sec;
MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_FIND_SERVICE, parm);
if (!rdr.Read())
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_INSERT_SERVICE_LOCATION, parm2);
}
else
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_UPDATE_SERVICE_LOCATION, parm2);
}
}
///
/// Looks up the given Client Service Name in the CLIENTTOBS table in the database
/// for information regarding configuration - of the Business Service and the DB
///
/// Name of the Order Processing Service
/// ClientConfigResponse object with config information on the BSuri and DB connection info. or null if the given ClientName is not in the database
public ClientConfigResponse GetClientConfig(string client)
{
MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_CLIENT, MySqlDbType.VarChar, 50) };
parm[0].Value = client;
MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_GET_CLIENT_CONFIG, parm);
// Read and Guard
if (!rdr.Read())
return null;
var response = new ClientConfigResponse();
response.BS = rdr.GetString(rdr.GetOrdinal("URL"));
response.BSName = rdr.GetString(rdr.GetOrdinal("BS"));
response.Sec = ConvertStringToBool(rdr["SEC"] as string);
rdr.Close();
return response;
}
///
/// Looks up the given Business Service Name in the BSTOOPS table in the database
/// for information regarding configuration - of the Order Processing Service and the DB
///
/// Name of the Buiness Service
/// BSConfigResponse object with config information on the OPSuri and DB connection info, or null if the given BSName is not in the database
public BSConfigResponse GetBSConfig(string bs)
{
MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_BS, MySqlDbType.VarChar, 50) };
parm[0].Value = bs;
MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_GET_BS_CONFIG, parm);
// Read and Gaurd
if (!rdr.Read())
return null;
var response = new BSConfigResponse();
response.OPS = rdr.GetString(rdr.GetOrdinal("URL"));
response.OPSName = rdr.GetString(rdr.GetOrdinal("OPS"));
response.Sec = ConvertStringToBool(rdr["SEC"] as string);
response.DBName = rdr.GetString(rdr.GetOrdinal("DBNAME"));
response.DBHostName = rdr.GetString(rdr.GetOrdinal("HOSTNAME"));
response.DBPort = rdr.GetInt32(rdr.GetOrdinal("PORT"));
rdr.Close();
return response;
}
///
/// Finds the database configuration information from DBCONFIG for the Order Processing Service
///
/// Name of the Order Processing Service
/// OPSConfigResponse object with config information on the DB connection info
public OPSConfigResponse GetOPSConfig(string ops)
{
MySqlDataReader rdr = MySQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_GET_OPS_CONFIG);
// Read and Gaurd
if (!rdr.Read())
return null;
var response = new OPSConfigResponse();
response.DBName = rdr.GetString(rdr.GetOrdinal("DBNAME"));
response.DBHostName = rdr.GetString(rdr.GetOrdinal("HOSTNAME"));
response.DBPort = rdr.GetInt32(rdr.GetOrdinal("PORT"));
rdr.Close();
return response;
}
///
/// Sets a connection between the specified client and business service
/// If no ClientToBS configuration entry exists for this client, it is created
/// or else the configuration for the client is replaced by this one
///
public void SetClientToBS(ClientToBS clientConfig)
{
MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_CLIENT, MySqlDbType.VarChar, 50) };
parm[0].Value = clientConfig.Client;
MySqlParameter[] parm2 = new MySqlParameter[] { new MySqlParameter(PARM_CLIENT, MySqlDbType.VarChar, 50), new MySqlParameter(PARM_BS, MySqlDbType.VarChar, 50) };
parm2[0].Value = clientConfig.Client;
parm2[1].Value = clientConfig.Bs;
MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_FIND_CLIENT_TO_BS, parm);
if (!rdr.Read())
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_SET_CLIENT_TO_BS, parm2);
}
else
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_UPDATE_CLIENT_TO_BS, parm2);
}
}
///
/// Sets a connection between the specified client and business service
/// If no ClientToBS configuration entry exists for this client, it is created
/// or else the configuration for the client is replaced by this one
///
public void SetBSToOPS(BSToOPS bsConfig)
{
MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_BS, MySqlDbType.VarChar, 50) };
parm[0].Value = bsConfig.Bs;
MySqlParameter[] parm2 = new MySqlParameter[] { new MySqlParameter(PARM_BS, MySqlDbType.VarChar, 50), new MySqlParameter(PARM_OPS, MySqlDbType.VarChar, 50) };
parm2[0].Value = bsConfig.Bs;
parm2[1].Value = bsConfig.Ops;
MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
MYSQL_FIND_BS_TO_OPS, parm);
if (!rdr.Read())
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_SET_BS_TO_OPS, parm2);
}
else
{
rdr.Close();
MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_UPDATE_BS_TO_OPS, parm2);
}
}
public ServiceLocation[] GetBSLocations()
{
int count = (int)MySQLHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_COUNT_BS);
ServiceLocation[] bsLocations = new ServiceLocation[count];
MySqlDataReader rdr = MySQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_GET_BS);
int i = 0;
while (rdr.Read())
{
bsLocations[i] = new ServiceLocation();
bsLocations[i].ServiceName = (string)rdr["SERVICENAME"];
bsLocations[i].ServiceURL = (string)rdr["URL"];
bsLocations[i].Sec = ConvertStringToBool(rdr["SEC"] as string);
i++;
}
return bsLocations;
}
public ServiceLocation[] GetOPSLocations()
{
int count = (int)MySQLHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_COUNT_OPS);
ServiceLocation[] opsLocations = new ServiceLocation[count];
MySqlDataReader rdr = MySQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, MYSQL_GET_OPS);
int i = 0;
while (rdr.Read())
{
opsLocations[i] = new ServiceLocation();
opsLocations[i].ServiceName = (string)rdr["SERVICENAME"];
opsLocations[i].ServiceURL = (string)rdr["URL"];
opsLocations[i].Sec = ConvertStringToBool(rdr["SEC"] as string);
i++;
}
return opsLocations;
}
private bool ConvertStringToBool(string value)
{
if (value == null)
return false;
return value.Equals("Y", StringComparison.InvariantCultureIgnoreCase);
}
}
}