public void SetUp()
{
_underlyingAppSettingStore = new NameValueCollection();
_connectionStrings = new ConnectionStringSettingsCollection();
_appSettings = new AppSettingsExtended(_underlyingAppSettingStore);
_interceptor = new ConfigurationSubstitutionInterceptor();
_connectionStringsExtended = new ConnectionStringsExtended(_connectionStrings, _appSettings, new[] {_interceptor});
}
internal ConnectionStringsConfiguration(ConnectionStringSettingsCollection connectionStrings)
{
_connectionStrings = connectionStrings;
}
private IEnumerable <ConfigurationConnectionStringModel> ProcessConnectionString(ConnectionStringSettingsCollection connectionStrings)
{
if (connectionStrings == null)
{
return(null);
}
var result = new List <ConfigurationConnectionStringModel>();
foreach (ConnectionStringSettings connectionString in connectionStrings)
{
var resultItem = new ConfigurationConnectionStringModel
{
Key = connectionString.Name,
Raw = connectionString.ConnectionString,
ProviderName = connectionString.ProviderName
};
try
{
var providerName = string.IsNullOrEmpty(connectionString.ProviderName) ? "System.Data.SqlClient" : connectionString.ProviderName;
var connectionFactory = DbProviderFactories.GetFactory(providerName);
var connectionStringBuilder = connectionFactory.CreateConnectionStringBuilder();
if (connectionStringBuilder != null)
{
connectionStringBuilder.ConnectionString = connectionString.ConnectionString;
var connectionDetails = new Dictionary <string, object>(StringComparer.InvariantCultureIgnoreCase);
var keys = connectionStringBuilder.Keys;
if (keys != null)
{
foreach (string key in keys)
{
connectionDetails.Add(key, connectionStringBuilder[key]);
}
resultItem.Details = connectionDetails;
AnnomalizeConnectionStringPassword(connectionDetails, resultItem);
}
}
}
catch (Exception e)
{
resultItem.Details = new Dictionary <string, object> {
{ "Error", e.Message }
};
}
result.Add(resultItem);
}
return(result.Count > 0 ? result : null);
}
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
/// <param name="types"></param>
private void SetKeysFromDb(DbConnection conn, string contextName, List <EntityMapType> types)
{
try {
DataTable fk = default(DataTable);
List <DbPkMapping> tblPrimKey = new List <DbPkMapping>();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var sql = @"select
b.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
where
CONSTRAINT_TYPE = 'PRIMARY KEY'
and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME";
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
var providerName = settings[contextName].ProviderName;
if (providerName != "System.Data.SqlClient")
{
fk = conn.GetSchema("IndexColumns");
var fi = conn.GetSchema("Indexes");
var PK_Column = default(String);
var INX_Name = default(String);
if (providerName == "MySql.Data.MySqlClient")
{
PK_Column = "PRIMARY";
INX_Name = "INDEX_NAME";
}
else
{
PK_Column = "PRIMARY_KEY";
INX_Name = "CONSTRAINT_NAME";
}
foreach (DataRow row in fi.Rows)
{
if (Convert.ToBoolean(row[PK_Column]))
{
tblPrimKey.Add(
fk.AsEnumerable()
.Where(w => w["TABLE_NAME"].ToString() == row["TABLE_NAME"].ToString())
.Select(s => new DbPkMapping
{
TableName = s["TABLE_NAME"].ToString()
, PkColumnName = s["COLUMN_NAME"].ToString()
, IndexName = s[INX_Name].ToString()
}).FirstOrDefault());
}
}
}
else
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
var result = cmd.ExecuteReader();
tblPrimKey = result
.Cast <DbDataRecord>()
.ToList()
.Select(s => new DbPkMapping
{
TableName = s["Table_Name"].ToString()
, PkColumnName = s["COLUMN_NAME"].ToString()
, IndexName = s["CONSTRAINT_NAME"].ToString()
}).ToList();
}
Func <Type, string> DeriveTableNameEntity = (Type entity) =>
{
var tblName = default(string);
try
{
tblName = entity
.BaseType
.GenericTypeArguments
.First()
.Name;
}
catch {
tblName = entity.Name;
}
return(tblName);
};
tblEntity =
types
.Where(s => !s.EntityType.GetCustomAttributes(typeof(DomainNoBindAttribute), true).Any())
.ToList()
.Select(s => new DbMappingTable
{
EntityName = s.EntityType.Name
, TableName = ps.Pluralize(DeriveTableNameEntity(s.EntityType))
, PkMapping = tblPrimKey.Where(w => w.TableName.ToLower() == ps.Pluralize(DeriveTableNameEntity(s.EntityType)).ToLower()).FirstOrDefault()
, EntityType = s.EntityType
, MappingTypeName = s.EntityTypeName
}
).ToList();
}
finally
{
conn.Close();
}
}
public ConfigurationService(NameValueCollection appSettings, ConnectionStringSettingsCollection connectionStrings)
{
this.appSettings = appSettings;
this.connectionStrings = connectionStrings;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
/*
* SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
* {
* DataSource = @"(localdb)\MSSQLLocalDB",
* InitialCatalog = "Lesson7",
* IntegratedSecurity = true,
* Pooling = true
* };
*/
//connection = new SqlConnection(connectionStringBuilder.ConnectionString);
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
string connectionString = settings[0]?.ConnectionString;
connection = new SqlConnection(connectionString);
adapter = new SqlDataAdapter();
#region select
//"Select EmployeeTable.Id, EmployeeTable.Name, Surname, Age, Salary, DepartmentTable.Name From EmployeeTable"
SqlCommand command =
new SqlCommand(string.Format("Select {0}, {1}, {2}, {3}, {4}, {5} as {6}, {7} from {8} inner join {9} on {10} = {11}",
"EmployeeTable.Id",
"EmployeeTable.Name",
"Surname",
"Age",
"Salary",
"DepartmentTable.Name",
"DepartmentName",
"Department",
"EmployeeTable",
"DepartmentTable",
"EmployeeTable.Department",
"DepartmentTable.Id"), connection);
adapter.SelectCommand = command;
#endregion
#region insert
command = new SqlCommand(@"Insert Into EmployeeTable (Name, Surname, Age, Salary, Department)
Values (@Name, @Surname, @Age, @Salary, @Department); Set @Id = @@IDENTITY;", connection);
command.Parameters.Add("@Name", SqlDbType.NVarChar, -1, "Name");
command.Parameters.Add("@Surname", SqlDbType.NVarChar, -1, "Surname");
command.Parameters.Add("@Age", SqlDbType.TinyInt, 1, "Age");
command.Parameters.Add("@Salary", SqlDbType.Decimal, 5, "Salary");
command.Parameters.Add("@Department", SqlDbType.Int, -1, "Department");
SqlParameter param = command.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
param.Direction = ParameterDirection.Output;
adapter.InsertCommand = command;
#endregion
#region update
command = new SqlCommand(@"Update EmployeeTable Set Name = @Name, Surname = @Surname,
Age = @Age, Salary = @Salary, Department = @Department
Where Id = @Id", connection);
command.Parameters.Add("@Name", SqlDbType.NVarChar, -1, "Name");
command.Parameters.Add("@Surname", SqlDbType.NVarChar, -1, "Surname");
command.Parameters.Add("@Age", SqlDbType.TinyInt, 1, "Age");
command.Parameters.Add("@Salary", SqlDbType.Decimal, 5, "Salary");
command.Parameters.Add("@Department", SqlDbType.Int, -1, "Department");
param = command.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
//param.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
#endregion
#region delete
command = new SqlCommand("Delete From EmployeeTable Where Id = @Id", connection);
param = command.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
adapter.DeleteCommand = command;
#endregion
dt = new DataTable();
adapter.Fill(dt);
DataGridEmployee.DataContext = dt.DefaultView;
//cbDepartments.ItemsSource = dt.DefaultView;
}
public object GetSection(string configKey)
{
// get the section from the default location (web.config or app.config)
object section = clientConfigSystem.GetSection(configKey);
switch (configKey)
{
case "appSettings":
if (this.appsettings != null)
{
return(this.appsettings);
}
if (section is NameValueCollection)
{
// create a new collection because the underlying collection is read-only
var cfg = new NameValueCollection();
NameValueCollection localSettings = (NameValueCollection)section;
foreach (string key in localSettings)
{
cfg.Add(key, localSettings[key]);
}
// merge the settings from core with the local appsettings
this.appsettings = cfg.Merge(ConfigurationManager.AppSettings);
section = this.appsettings;
}
break;
case "connectionStrings":
if (this.connectionStrings != null)
{
return(this.connectionStrings);
}
// create a new collection because the underlying collection is read-only
var cssc = new ConnectionStringSettingsCollection();
// copy the existing connection strings into the new collection
foreach (ConnectionStringSettings connectionStringSetting in ((ConnectionStringsSection)section).ConnectionStrings)
{
cssc.Add(connectionStringSetting);
}
// merge the settings from core with the local connectionStrings
cssc = cssc.Merge(ConfigurationManager.ConnectionStrings);
ConnectionStringsSection connectionStringsSection = new ConnectionStringsSection();
foreach (ConnectionStringSettings connectionStringSetting in cssc)
{
connectionStringsSection.ConnectionStrings.Add(connectionStringSetting);
}
this.connectionStrings = connectionStringsSection;
section = this.connectionStrings;
break;
}
return(section);
}
public static string ExcecuteProcedure(string Agent, string COMMANDE, int?codeimport, int?NBPARAMETRE, bool isprocedure)
{
string laliste = null;
ConnectionStringSettingsCollection lesChainesDeConnexion = ConfigurationManager.ConnectionStrings;
ConnectionStringSettings laChaineDeConnexion = lesChainesDeConnexion["galadbEntities"];
string[] caract = laChaineDeConnexion.ConnectionString.Split(new string[] { "data source" }, StringSplitOptions.RemoveEmptyEntries);
string[] chaine = caract[1].Split(new string[] { "MultipleActiveResultSetsnew" }, StringSplitOptions.RemoveEmptyEntries);
string[] connexion = chaine[0].Split(new Char[] { ';' });
string connectionString = "data source" + connexion[0] + ";" + connexion[1] + ";" + connexion[2] + ";" + connexion[3];
int result = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
List <IMPORTCOLONNE> lescolonnes = null;
using (galadbEntities context = new galadbEntities())
{
lescolonnes = context.IMPORTCOLONNE.Where(c => c.ID_PARAMETRAGE == codeimport).ToList();
};
if (lescolonnes != null && lescolonnes.Count > 0)
{
if (isprocedure == true)
{
SqlParameter[] parametres = new SqlParameter[int.Parse(NBPARAMETRE.ToString())];
string[] tabOper = Agent.Split(new char[] { ';' });
int a = 0;
foreach (IMPORTCOLONNE col in lescolonnes)
{
parametres[a] = new SqlParameter("@" + col.NOM, col.TYPE);
parametres[a].Value = tabOper[a];
a++;
}
foreach (SqlParameter param in parametres)
{
cmd.Parameters.Add(param);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connection;
cmd.CommandText = COMMANDE;
}
else
{
cmd.CommandText = COMMANDE;
int a = 0;
string[] tabOper = Agent.Split(new char[] { ';' });
foreach (IMPORTCOLONNE col in lescolonnes)
{
cmd.Parameters.AddWithValue("@" + col.NOM, tabOper[a]);
a++;
}
}
//result = cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
result = da.Fill(dt);
cmd.Dispose();
if (result > 0)
{
string resultat = string.Empty;
foreach (DataRow lign in dt.Rows)
{
resultat = Convert.ToString(lign[0]);
}
if (resultat == "0")
{
laliste = string.Empty;
}
else
{
string[] valeur = Agent.Split(new Char[] { ';' });
string elmt = null;
foreach (string lign in valeur)
{
if (elmt == null)
{
elmt += lign != null ? lign : string.Empty;
}
else
{
elmt += lign != null ? " " + lign : " " + string.Empty;
}
laliste = elmt;
}
}
}
}
}
return(laliste);
}
/// <summary>
/// Gets the connection name from environment.
/// </summary>
/// <param name="collection">The collection.</param>
/// <param name="unqualifiedKey">The unqualified key.</param>
/// <param name="environmentName">Name of the environment.</param>
/// <param name="throwConfigurationErrorsException">if set to <c>true</c> throw configuration errors exception.</param>
/// <returns></returns>
public static string GetConnectionNameFromEnvironment(this ConnectionStringSettingsCollection collection, string unqualifiedKey, string environmentName, bool throwConfigurationErrorsException)
{
return(collection.GetConnectionNameFromEnvironment(unqualifiedKey, environmentName, DeploymentEnvironment.ConfigurationKeyDelimiter, throwConfigurationErrorsException));
}
/// <summary>
/// Returns <see cref="ConnectionStringSettingsCollection" />
/// with the application settings
/// of the specified external configuration <see cref="XDocument" />.
/// </summary>
/// <param name="collection">The collection.</param>
/// <param name="externalConfigurationDoc">The external configuration document.</param>
/// <returns></returns>
public static ConnectionStringSettingsCollection WithConnectionStringSettingsCollection(this ConnectionStringSettingsCollection collection, XDocument externalConfigurationDoc)
{
var externalCollection = externalConfigurationDoc.ToConnectionStringSettingsCollection();
if (externalCollection == null)
{
return(null);
}
collection.OfType <ConnectionStringSettings>().ForEachInEnumerable(i => externalCollection.Add(i));
return(externalCollection);
}
/// <summary>
/// Gets the connection name from environment.
/// </summary>
/// <param name="collection">The collection.</param>
/// <param name="unqualifiedKey">The unqualified key.</param>
/// <param name="environmentName">Name of the environment.</param>
/// <param name="delimiter">The delimiter.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">unqualifiedKey - The expected App Settings key is not here.</exception>
/// <exception cref="ConfigurationErrorsException"></exception>
public static string GetConnectionNameFromEnvironment(this ConnectionStringSettingsCollection collection, string unqualifiedKey, string environmentName, string delimiter)
{
return(collection.GetConnectionNameFromEnvironment(unqualifiedKey, environmentName, delimiter, throwConfigurationErrorsException: true));
}
/// <summary>
/// Gets the connection string settings.
/// </summary>
/// <param name="collection">The collection.</param>
/// <param name="connectionName">Name of the connection.</param>
/// <returns></returns>
public static ConnectionStringSettings GetConnectionStringSettings(this ConnectionStringSettingsCollection collection, string connectionName)
{
return(collection.GetConnectionStringSettings(connectionName, throwConfigurationErrorsException: false));
}
private static void EncryptConfig(string pwd, Configuration config, AppSettingsSection appSettings, ConnectionStringSettingsCollection connectionStrings)
{
foreach (var key in appSettings.Settings.AllKeys)
{
appSettings.Settings[key].Value = StringCipher.Encrypt(appSettings.Settings[key].Value, pwd);
}
for (int i = 0; i < connectionStrings.Count; i++)
{
connectionStrings[i] = new ConnectionStringSettings(connectionStrings[i].Name, StringCipher.Encrypt(connectionStrings[i].ConnectionString, pwd), connectionStrings[i].ProviderName);
}
appSettings.Settings.Add("Encrypted", "True");
config.Save(ConfigurationSaveMode.Modified, true);
}
private object GetMergedConnectionStringSection(object originalSection)
{
//return if test config has no connectionstring defined
if (TestConfig.ConnectionStrings == null)
{
return originalSection;
}
var mergedConnectionStrings = new ConnectionStringSettingsCollection();
//Copy test connection string to collection
foreach (ConnectionStringSettings connectionStringSetting in TestConfig.ConnectionStrings.ConnectionStrings)
{
mergedConnectionStrings.Add(connectionStringSetting);
}
//merge connection strings from original config
if (originalSection != null)
{
foreach (ConnectionStringSettings item in ((ConnectionStringsSection)originalSection).ConnectionStrings)
{
if (!mergedConnectionStrings.Cast<ConnectionStringSettings>().Any(x => x.Name.Equals(item.Name, StringComparison.CurrentCultureIgnoreCase)))
{
mergedConnectionStrings.Add(item);
}
}
}
//create merged ConnectionStringsSection
ConnectionStringsSection connectionStringsSection = new ConnectionStringsSection();
foreach (ConnectionStringSettings connectionStringSetting in mergedConnectionStrings)
{
connectionStringsSection.ConnectionStrings.Add(connectionStringSetting);
}
return connectionStringsSection;
}
public DbContextInfo(Type contextType, ConnectionStringSettingsCollection connectionStringSettings)
: this(
Check.NotNull(contextType, "contextType"), null,
new AppConfig(Check.NotNull(connectionStringSettings, "connectionStringSettings")), null)
{
}
static void Main(string[] args)
{
Log.licenseHeader();
if (Array.IndexOf(args, @"/?") >= 0)
{
Console.WriteLine("See https://github.com/dc-sql/DBAid for more details");
return;
}
Arguments flag = new Arguments(args);
string server = flag.ContainsFlag("-server") ? flag.GetValue("-server") : String.Empty;
string database = flag.ContainsFlag("-db") ? flag.GetValue("-db") : "_dbaid";
int errorCount = 0;
int warningCount = 0;
string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
string logFile = Path.Combine(baseDirectory, logID + DateTime.Now.ToString("yyyyMMdd") + logExt);
string workingDirectory = String.Empty;
string processDirectory = String.Empty;
bool logVerbose = true;
byte fileRententionDays = 7;
bool emailEnable = true;
string emailSmtp = String.Empty;
string[] emailTo = { String.Empty };
string emailFrom = String.Empty;
string emailSubject = String.Empty;
long emailAttachmentByteLimit = 0;
int emailAttachmentCountLimit = 0;
bool emailEnableSsl = true;
bool emailIgnoreSslError = false;
bool emailAnonymous = true;
int defaultCmdTimout = 0;
ConnectionStringSettingsCollection cssc = new ConnectionStringSettingsCollection();
try
{
if (String.IsNullOrEmpty(server) || String.IsNullOrEmpty(database))
{
cssc = ConfigurationManager.ConnectionStrings;
}
else
{
string cs = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;";
cssc.Add(new ConnectionStringSettings(server.Replace("\\", "@"), cs));
}
workingDirectory = ConfigurationManager.AppSettings["WorkingDirectory"];
processDirectory = Path.Combine(workingDirectory, processedDir);
logVerbose = bool.Parse(ConfigurationManager.AppSettings["logVerbose"]);
fileRententionDays = byte.Parse(ConfigurationManager.AppSettings["ProcessedFileRetentionDays"]);
emailEnable = bool.Parse(ConfigurationManager.AppSettings["emailEnable"]);
emailSmtp = ConfigurationManager.AppSettings["EmailSmtp"];
emailTo = ConfigurationManager.AppSettings["EmailTo"].Split(';');
emailFrom = ConfigurationManager.AppSettings["EmailFrom"];
emailSubject = ConfigurationManager.AppSettings["EmailSubject"];
emailAttachmentByteLimit = long.Parse(ConfigurationManager.AppSettings["EmailAttachmentByteLimit"]);
emailAttachmentCountLimit = int.Parse(ConfigurationManager.AppSettings["EmailAttachmentCountLimit"]);
emailEnableSsl = bool.Parse(ConfigurationManager.AppSettings["EmailEnableSsl"]);
emailIgnoreSslError = bool.Parse(ConfigurationManager.AppSettings["EmailIgnoreSslError"]);
emailAnonymous = bool.Parse(ConfigurationManager.AppSettings["EmailAnonymous"]);
defaultCmdTimout = int.Parse(ConfigurationManager.AppSettings["default_cmd_timeout_sec"]);
if (!Directory.Exists(workingDirectory))
{
Directory.CreateDirectory(workingDirectory);
}
if (!Directory.Exists(processDirectory))
{
Directory.CreateDirectory(processDirectory);
}
Log.message(LogEntryType.INFO, "DBAidCollector", "Starting DBAid Collector", logFile);
}
catch (Exception ex)
{
Log.message(LogEntryType.ERROR, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
errorCount++;
if (emailEnable)
{
Smtp.send(emailSmtp, emailFrom, emailTo, Environment.MachineName, "Failed to initialise DBAid collector", null, emailAttachmentByteLimit, emailAttachmentCountLimit, emailEnableSsl, emailIgnoreSslError, emailAnonymous);
}
Console.Write("Settings in App.Config may be incorrect and/or missing, or permissions to write log file is missing.");
return;
}
try
{
//Clean up old files
FileIo.delete(baseDirectory, "*" + logExt, DateTime.Now.AddDays(-7));
FileIo.delete(processDirectory, "*" + processedExt, DateTime.Now.AddDays(fileRententionDays * -1));
}
catch (Exception ex)
{
Log.message(LogEntryType.WARNING, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
warningCount++;
}
foreach (ConnectionStringSettings css in cssc)
{
DateTime runtime = DateTime.UtcNow;
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(css.ConnectionString);
DataRowCollection procedures;
List <string> attachments = new List <string>();
string publicKey = String.Empty;
string instanceTag = String.Empty;
csb.ApplicationName = logID + Guid.NewGuid().ToString();
Log.message(LogEntryType.INFO, "DBAidCollector", "Starting Collection on [" + csb.DataSource + "]", logFile);
try
{
// query database for assigned application name.
csb.ApplicationName = Query.Select(csb.ConnectionString, mssqlAppSelect, defaultCmdTimout).Rows[0][0].ToString();
// query database for instance guid.
instanceTag = Query.Execute(csb.ConnectionString, mssqlInstanceTagProc, defaultCmdTimout).Rows[0][0].ToString();
if (String.IsNullOrEmpty(instanceTag))
{
instanceTag = css.Name.Replace("\\", "@").Replace("_", "~") + "_" + IPGlobalProperties.GetIPGlobalProperties().DomainName.Replace(".", "_");
}
// query database for public key.
publicKey = Query.Select(csb.ConnectionString, mssqlKeySelect, defaultCmdTimout).Rows[0][0].ToString();
// get procedure returned from control procedure.
procedures = Query.Select(csb.ConnectionString, mssqlControlProc, defaultCmdTimout).Rows;
}
catch (Exception ex)
{
Log.message(LogEntryType.WARNING, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
warningCount++;
continue;
}
foreach (DataRow dr in procedures)
{
string file = instanceTag + "_" + dr[0].ToString().Replace("].[", "_").Replace(".", "_") + "_" + runtime.ToString("yyyyMMddHHmmss") + encryptedExt;
string filepath = Path.Combine(workingDirectory, file);
// execute procedure, compress, and encrypt stream. Write contents out to file.
using (MemoryStream msRaw = new MemoryStream())
using (StreamWriter swRaw = new StreamWriter(msRaw, Encoding.Unicode))
{
try
{
DataTable dt = Query.Execute(csb.ConnectionString, dr[0].ToString(), defaultCmdTimout);
StringBuilder sb = new StringBuilder(dt.TableName.Length);
foreach (char c in dt.TableName) // remove special characters from table name as this causes issues with SSIS xml source.
{
if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '-' || c == '_')
{
sb.Append(c);
}
}
dt.TableName = sb.ToString();
dt.WriteXml(swRaw, XmlWriteMode.WriteSchema);
dt.Clear();
}
catch (Exception ex)
{
Log.message(LogEntryType.WARNING, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
warningCount++;
continue;
}
try
{
if (logVerbose)
{
Log.message(LogEntryType.INFO, "DBAidCollector", "Writing Encrypted File: \"" + filepath + "\"", logFile);
}
Crypto.encrypt(publicKey, msRaw, filepath);
}
catch (Exception ex)
{
Log.message(LogEntryType.WARNING, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
warningCount++;
continue;
}
}
}
Log.message(LogEntryType.INFO, "DBAidCollector", "Completed Collection on [" + csb.DataSource + "]", logFile);
try
{
foreach (string file in Directory.GetFiles(workingDirectory, "*" + encryptedExt))
{
attachments.Add(file);
}
string body = "DBAid collector process logged: \n\t"
+ errorCount.ToString()
+ " error(s) \n\t"
+ warningCount.ToString()
+ " warning(s) \n";
if (emailEnable)
{
Smtp.send(emailSmtp, emailFrom, emailTo, emailSubject, body, attachments.ToArray(), emailAttachmentByteLimit, emailAttachmentCountLimit, emailEnableSsl, emailIgnoreSslError, emailAnonymous);
foreach (string file in attachments)
{
FileIo.move(file, Path.Combine(processDirectory, Path.GetFileName(file) + processedExt));
}
Log.message(LogEntryType.INFO, "DBAidCollector", "Email Sent to \"" + string.Join("; ", emailTo) + "\"", logFile);
}
}
catch (Exception ex)
{
Log.message(LogEntryType.WARNING, "DBAidCollector", ex.Message + (logVerbose ? " - " + ex.StackTrace : ""), logFile);
}
}
Log.message(LogEntryType.INFO, "DBAidCollector", "Completed DBAid Collection", logFile);
//System.Threading.Thread.Sleep(10000);
}
public ConnectionStringsExtended(ConnectionStringSettingsCollection raw, IAppSettings appSettings, IEnumerable<IConnectionStringInterceptor> interceptors = null)
{
_appSettings = appSettings;
_interceptors = interceptors ?? new List<IConnectionStringInterceptor>();
Raw = raw;
}
static void Main(string[] args)
{
var appSettings = new Dictionary <string, uint>();
foreach (var key in ConfigurationManager.AppSettings.AllKeys)
{
appSettings.Add(key, uint.Parse(ConfigurationManager.AppSettings[key]));
}
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
foreach (ConnectionStringSettings cs in settings)
{
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(cs.ConnectionString)
{
ApplicationName = "dbaid-checkmk",
Encrypt = true,
TrustServerCertificate = true,
ConnectTimeout = 5
};
using (var conn = new SqlConnection(csb.ConnectionString))
{
bool isClustered = false;
string netBIOSname = Environment.MachineName;
string dbaidVersion = String.Empty;
try
{
conn.Open();
//check if clustered and primary
using (var cmd = new SqlCommand("SELECT CAST(SERVERPROPERTY('IsClustered') AS BIT)", conn)
{
CommandTimeout = 2, CommandType = CommandType.Text
})
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
isClustered = reader.GetBoolean(0);
}
}
using (var cmd = new SqlCommand("SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')", conn)
{
CommandTimeout = 5, CommandType = CommandType.Text
})
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
netBIOSname = reader.GetString(0);
}
}
if (Environment.MachineName != netBIOSname && isClustered)
{
continue;
}
using (var cmd = new SqlCommand(getDbaidVersionSql, conn)
{
CommandTimeout = 5, CommandType = CommandType.Text
})
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
dbaidVersion = reader.GetString(0);
}
}
// Output instance service check
Console.WriteLine("{0} mssql_{1}_{2} count={3} {4} - SQL Version={5}; DBAid Version={6}", StatusCode("OK"), cs.Name, "service", 1, "OK", conn.ServerVersion, dbaidVersion);
// Inventory the SQL Instance
using (var cmd = new SqlCommand(getProcListSql, conn)
{
CommandTimeout = 5, CommandType = CommandType.Text
})
{
cmd.Parameters.Add(new SqlParameter("@filter", "inventory%"));
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
foreach (DataRow dr in dt.Rows)
{
string proc = dr[0].ToString();
using (var inventory = new SqlCommand(proc, conn))
{
inventory.CommandType = CommandType.StoredProcedure;
inventory.ExecuteNonQuery();
}
}
}
// output check procedures
DoCheck(conn, cs.Name, ref appSettings);
// output chart procedures
DoChart(conn, cs.Name, ref appSettings);
}
catch (Exception e)
{
Console.WriteLine("{0} mssql_{1}_{2} count={3} {4} - {5}", StatusCode("CRITICAL"), cs.Name, "service", 1, "CRITICAL", e.Message);
}
}
}
#if DEBUG
Console.ReadKey();
#endif
}
public void TestInit()
{
appSettings = new NameValueCollection();
connectionStrings = new ConnectionStringSettingsCollection();
settings = new AppSettings(appSettings, connectionStrings);
}
public ConfigurationManager(NameValueCollection appSettings, ConnectionStringSettingsCollection connectionStringSettings)
{
AppSettings = new AppSettingsExtended(appSettings, ConfigurationInterceptors, ConfigureDefaultTypeConverters());
ConnectionStrings = new ConnectionStringsExtended(connectionStringSettings, AppSettings, ConnectionStringInterceptors);
}
public MockUserConfiguration()
{
_appSettingsCollection = new KeyValueConfigurationCollection();
_connectionStringSettingsCollection = new ConnectionStringSettingsCollection();
}
// <summary>
// Initializes a new instance of AppConfig based on supplied connection strings
// The default configuration for database initializers and default connection factory will be used
// </summary>
// <param name="connectionStrings"> Connection strings to be used </param>
public AppConfig(ConnectionStringSettingsCollection connectionStrings)
: this(connectionStrings, null, null)
{
DebugCheck.NotNull(connectionStrings);
}
private static void Main(string[] args)
{
// Define the connection string here so the entire scope of main can access it
string connectionString = string.Empty;
DateTime jobStartTime = DateTime.Now;
DateTime jobEndTime;
Stopwatch jobTimer = new Stopwatch();
double jobElapsedTime = 0;
bool jobSuccess = true;
jobTimer.Start();
if (args.Length > 0)
{
Arguments arguments = ConfigurationOverride.Parse(args);
}
else
{
// Get the connection string from app.config
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
if (settings != null && settings.Count == 1)
{
connectionString = settings[0].ConnectionString;
}
else
{
string message = string.Empty;
if (settings == null)
{
message = "Connection string not defined.";
}
if (settings.Count > 1)
{
message = "Multiple connection strings defined.";
}
EventLog eventLog = new EventLog("Application");
eventLog.Source = "UBSYNC";
eventLog.WriteEntry(message, EventLogEntryType.Error, 10001);
}
// Import the configuration from the app.config file
SyncConfiguration configuration = ConfigurationManager.GetSection("importFiles") as SyncConfiguration;
// Set the database reovery model to bulk_logged
// UBSYNC is refreshed daily and does not use full recovery mode
// This step is not needed
//ToggleSimpleRecovery(1, connectionString);
// Convert the importFiles collection to a generic list so Parallel.ForEach can be easily used
List <SyncConfigurationElement> elements = new List <SyncConfigurationElement>();
foreach (SyncConfigurationElement element in configuration.ImportFiles)
{
elements.Add(element);
}
// Import each file in a parallel thread
Parallel.ForEach(elements, (element) =>
{
Sync current = SyncFactory.Create(element, connectionString);
if (current.Synchronize() == false)
{
jobSuccess = false;
}
});
// Revert the database reovery model back to full
// UBSYNC is refreshed daily and does not use full recovery mode
// This step is not needed
//ToggleSimpleRecovery(0, connectionString);
// Kick off a full backup
// This is handled by a script and is not needed
// This is also not needed because full reovery isn't being used
//FullBackup();
}
jobEndTime = DateTime.Now;
jobTimer.Stop();
jobElapsedTime = Math.Round(jobTimer.Elapsed.TotalSeconds, 3);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("CSP_INSERT_JOB_METRICS", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 3600;
SqlParameter paramJobStartTime = command.Parameters.AddWithValue("@JOB_StartTime", jobStartTime);
SqlParameter paramJobEndTime = command.Parameters.AddWithValue("@JOB_EndTime", jobEndTime);
SqlParameter paramJobnElapsedTime = command.Parameters.AddWithValue("@JOB_ElapsedTime", jobElapsedTime);
SqlParameter paramJobSuccess = command.Parameters.AddWithValue("@JOB_Success", jobSuccess);
connection.Open();
command.ExecuteNonQuery();
}
}
Console.WriteLine("Complete");
Console.ReadLine();
}
public BaseRepository()
{
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
con = new SqlConnection(settings["Northwind"].ConnectionString);
}
internal static MigrationOptions ParseCommandLineArguments(CommandLineOptions options, CommandLineParser parser, ConnectionStringSettingsCollection connectionStrings,
out string connectionString, out string providerName, out string assemblyPath, out string[] additionalAssemblyPaths, out long timestamp, out SourceLevels traceLevels)
{
if (parser.Parameters.Length < 2 || // expect at least the target and one assemlby
parser.UnhandledSwitches.Length > 0)
{
throw new InvalidCommandLineArgumentException("Invalid command line arguments. Specify at least the target and one assembly." + Environment.NewLine + Environment.NewLine + GetUsageMessage(parser),
InvalidArgumentsExitCode);
}
// connection string
string target = parser.Parameters[0];
ConnectionStringSettings settings = connectionStrings[target];
if (settings == null)
{
throw new InvalidCommandLineArgumentException(string.Format(CultureInfo.CurrentCulture,
"Missing target: '{0}'. Could not find entry in the configuration file.", target),
InvalidTargetExitCode);
}
connectionString = settings.ConnectionString;
if (connectionString == null)
{
throw new InvalidCommandLineArgumentException(string.Format(CultureInfo.CurrentCulture,
"Empty target: '{0}'. The entry in the configuration file is empty.", target),
InvalidTargetExitCode);
}
// provider name
providerName = options.Provider;
// assembly paths
assemblyPath = parser.Parameters[1];
additionalAssemblyPaths = parser.Parameters.Skip(2).ToArray();
// timestamp
timestamp = long.MaxValue;
if (options.To != null)
{
try
{
timestamp = long.Parse(options.To, CultureInfo.CurrentCulture);
}
catch (FormatException x)
{
throw new InvalidCommandLineArgumentException(string.Format(CultureInfo.CurrentCulture,
"Could not parse timestamp: '{0}': {1}", options.To, x.Message),
InvalidArgumentsExitCode);
}
}
// trace level
traceLevels = SourceLevels.Warning;
if (!string.IsNullOrEmpty(options.TraceLevel))
{
try
{
traceLevels = (SourceLevels)Enum.Parse(typeof(SourceLevels), options.TraceLevel, true);
}
catch (ArgumentException x)
{
throw new InvalidCommandLineArgumentException(string.Format(CultureInfo.CurrentCulture,
"Could not parse traceLevel: '{0}': {1}", options.TraceLevel, x.Message),
InvalidArgumentsExitCode);
}
}
//
// other migration options
//
var migrationOptions = !string.IsNullOrEmpty(options.Module) ? new MigrationOptions(options.Module) : new MigrationOptions();
// supported providers
IEnumerable <string> supportedProviders = new[] { providerName };
if (!string.IsNullOrEmpty(options.Support))
{
supportedProviders = supportedProviders.Concat(options.Support.Split(new[] { SupportedProviderSeparator }, StringSplitOptions.RemoveEmptyEntries));
}
migrationOptions.SupportedProviders.Set(supportedProviders);
// scripting
if (!string.IsNullOrEmpty(options.ScriptTo))
{
if (options.ScriptOnly)
{
migrationOptions.OnlyScriptSqlTo(options.ScriptTo);
}
else
{
migrationOptions.ExecuteAndScriptSqlTo(options.ScriptTo);
}
}
else
{
if (options.ScriptOnly)
{
throw new InvalidCommandLineArgumentException("The -scriptOnly switch requires a -scriptTo argument.",
InvalidArgumentsExitCode);
}
}
// versioning table
if (!string.IsNullOrEmpty(options.VersioningTable))
{
migrationOptions.VersioningTableName = options.VersioningTable;
}
return(migrationOptions);
}
/// <summary>
/// Initializes a new instance of the <see cref="ConnectionStringConfigurationProvider"/> class.
/// </summary>
/// <param name="connectionStrings">The connection strings.</param>
public ConnectionStringConfigurationProvider(ConnectionStringSettingsCollection connectionStrings)
{
_connectionStrings = connectionStrings;
Check.IsNotNull(connectionStrings, "connectionStrings is required.");
}
public DictionaryWrappingConnectionStringSettingCollectionEnumerator(ConnectionStringSettingsCollection actual)
{
_actual = actual;
_keys = KeysOf(actual).ToArray();
Reset();
}
public InMemoryConfigurationManager()
{
AppSettings = new NameValueCollection();
ConnectionStrings = new ConnectionStringSettingsCollection();
}
/// <summary>
/// Merges two ConnectionStringSettingsCollections.
/// </summary>
/// <param name="first"></param>
/// <param name="second"></param>
/// <remarks>Used by <see cref="Williablog.Core.Configuration.ConfigSystem">ConfigSystem</c> to merge ConnectionStrings</remarks>
public static ConnectionStringSettingsCollection Merge(this ConnectionStringSettingsCollection first, ConnectionStringSettingsCollection second)
{
if (second == null)
{
return(first);
}
foreach (ConnectionStringSettings item in second)
{
ConnectionStringSettings itemInSecond = item;
ConnectionStringSettings existingItem = first.Cast <ConnectionStringSettings>().FirstOrDefault(x => x.Name == itemInSecond.Name);
if (existingItem != null)
{
first.Remove(item);
}
first.Add(item);
}
return(first);
}
/// <summary>
/// 取得连接字符串
/// </summary>
private Boolean GetConnStr()
{
ConnectionStringSettingsCollection cssc = System.Configuration.ConfigurationManager.ConnectionStrings;
// 链接配置段为空，退出，需要从参数中拿链接字符串
if (cssc == null || cssc.Count < 1)
{
return(false);
}
// 没有设置连接名，则查找默认连接名。
if (String.IsNullOrEmpty(_ConnName))
{
if (cssc["SiteSqlServer"] != null) // 如果有default的设置，则使用default
{
_ConnName = "SiteSqlServer";
}
else // 否则，使用第一个不是aspnet数据库的连接
{
for (Int32 i = 0; i < cssc.Count; i++)
{
if (!cssc[i].ConnectionString.ToLower().Contains("aspnetdb.mdf"))
{
_ConnName = cssc[i].Name;
break;
}
}
if (String.IsNullOrEmpty(_ConnName))
{
return(false);
}
}
}
else if (cssc[_ConnName] == null) //如果根据连接名无法取得值，则该连接名可能是连接字符串，退出
{
return(false);
}
_ConnStr = cssc[ConnName].ConnectionString;
// 分析类型
if (String.IsNullOrEmpty(cssc[ConnName].ProviderName)) // 没有指定驱动名，则需要从连接字符串中分析。
{
GetDbType();
}
else
{
String ass = cssc[ConnName].ProviderName;
if (ass.Contains("SqlClient"))
{
DALType = typeof(SqlServer);
}
else if (ass.ToLower().Contains("microsoft.jet.oledb"))
{
DALType = typeof(Access);
}
else if (ass.ToLower().Contains("mysql"))
{
DALType = typeof(MySql);
}
else if (ass.ToLower().Contains("sqlite"))
{
DALType = typeof(SQLite);
}
else
{
if (ass.Contains(",")) // 带有程序集名称，加载程序集
{
DALType = Assembly.Load(ass.Substring(0, ass.IndexOf(","))).GetType(ass.Substring(ass.IndexOf(",") + 1, ass.Length), true, false);
}
else // 没有程序集名称，则使用本程序集
{
DALType = this.GetType().Assembly.GetType(ass, true, false);
}
}
}
return(true);
}
private void PopulatePermissionTree()
{
ConnectionStringSettingsCollection connectionStrings =
ConfigurationManager.ConnectionStrings;
string connString = connectionStrings[
"ControlBasedSecurity.Properties.Settings.ControlSecurityConnectionString"].
ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
string queryString = "select controlID, Invisible, Disabled, RoleName " +
"from ControlsToRoles ctr " +
" join controls c on c.ControlID = ctr.FKControlID and c.Page = ctr.FKPage " +
" join roles r on r.RoleID = ctr.FKRole ";
if (ByControlRB.Checked)
{
queryString += " order by ControlID";
}
else
{
queryString += " order by RoleName";
}
DataSet ds = new DataSet();
SqlDataAdapter dataAdapter = null;
DataTable dt = null;
try
{
dataAdapter = new SqlDataAdapter(queryString, conn);
dataAdapter.Fill(ds, "controlsToRoles");
dt = ds.Tables[0];
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve permissions: " + e.Message,
"Error retrieving permissions",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
PermissionTree.BeginUpdate();
PermissionTree.Nodes.Clear();
TreeNode parentNode = null;
TreeNode subNode = null;
string currentName = string.Empty;
foreach (DataRow row in dt.Rows)
{
string subNodeText = ByControlRB.Checked ? row["RoleName"].ToString() : row["ControlID"].ToString();
subNodeText += ":";
subNodeText += Convert.ToInt32(row["Invisible"]) == 0 ? " visible " : " not visible ";
subNodeText += " and ";
subNodeText += Convert.ToInt32(row["Disabled"]) == 0 ? " enabled " : " disabled ";
subNode = new TreeNode(subNodeText);
string dataName = ByControlRB.Checked ? row["ControlID"].ToString() : row["RoleName"].ToString();
if (currentName != dataName)
{
parentNode = new TreeNode(dataName);
currentName = dataName;
PermissionTree.Nodes.Add(parentNode);
}
if (parentNode != null)
{
parentNode.Nodes.Add(subNode);
}
}
PermissionTree.EndUpdate();
}
/// <summary>
/// Retrieve config object based on key. If connectionstrings, we want to return our modified
/// config object. Otherwise, return baseconf's implementation.
/// </summary>
/// <param name="configKey"></param>
/// <returns></returns>
public object GetSection(string configKey)
{
if (configKey != "connectionStrings" && configKey != "appSettings")
{
return(_baseconf.GetSection(configKey));
}
// we'll keep a collection that includes both appSettings and SecureSettings so that they can
// both be accessed via appSettings
if (configKey == "appSettings")
{
if (_appSettings == null)
{
lock (lockme)
{
if (_appSettings == null)
{
NameValueCollection appSettingsSection = (NameValueCollection)_baseconf.GetSection(configKey);
// first add appSettings to the a new collection
NameValueCollection newAppSettings = new NameValueCollection(appSettingsSection);
// then add SecureSettings with decrypted values.
if (Settings.SecureSetting != null)
{
foreach (SecureSetting setting in Settings.SecureSetting)
{
if (appSettingsSection[setting.Key] != null)
{
throw new ApplicationException(String.Format("There's already an appSetting with key: {0}. Can't have SecureSetting with the same key.", setting.Key));
}
newAppSettings.Add(setting.Key, setting.Value);
}
}
_appSettings = newAppSettings;
}
}
}
return(_appSettings);
}
// the ConnectionStringsSection is being requested. If we don't yet have it cached, cache it.
if (_connectionStrings == null)
{
lock (lockme)
{
if (_connectionStrings == null)
{
ConnectionStringsSection csSection = _baseconf.GetSection(configKey) as ConnectionStringsSection;
if (csSection == null) // not sure how this could happen, but just in case
{
throw new ApplicationException(String.Format("ConfigKey: {0} does not cast to ConnectionStringsSection?!", configKey));
}
ConnectionStringSettingsCollection baseCsCollection = csSection.ConnectionStrings;
// create a new ConnectionStringsSection because we're unable to set base ConnectionStringSettingsCollection.
ConnectionStringsSection newCsSection = new ConnectionStringsSection();
// copy each ConnectionStringSettings from
foreach (ConnectionStringSettings baseCss in baseCsCollection)
{
var newCs = DecryptConnectionStringPassword(baseCss.ConnectionString);
ConnectionStringSettings newCss = new ConnectionStringSettings(baseCss.Name, newCs, baseCss.ProviderName);
newCsSection.ConnectionStrings.Add(newCss);
}
_connectionStrings = newCsSection;
}
}
}
return(_connectionStrings);
}
private void Update()
{
m_appSetting = ConfigurationManager.AppSettings;
m_connectionSetting = ConfigurationManager.ConnectionStrings;
}
public void SetUp()
{
_fakeConfig = new ConnectionStringSettingsCollection {new ConnectionStringSettings("key-here", "junk")};
_wrapper = new ConnectionStringsExtended(_fakeConfig, new AppSettingsExtended(new NameValueCollection()));
}
