private static int RunSql(NameValueCollection parameters)
{
var connectionStrings = parameters["connection-strings"];
var connectionStringsFile = parameters["connection-strings-file"];
var queryFile = parameters["query-file"];
var resultConnectionString = parameters["result-connection-string"];
var dumpSql = parameters["dump-sql"];
var historyMode = parameters["history-mode"];
var parametersAreValid =
(!string.IsNullOrEmpty(connectionStrings) || !string.IsNullOrEmpty(connectionStringsFile)) &&
!string.IsNullOrEmpty(queryFile) &&
!string.IsNullOrEmpty(resultConnectionString);
if (!parametersAreValid)
{
Console.Out.WriteLine("Invalid arguments");
Console.Out.WriteLine(
"Usage: Generator.exe -cmd run-sql [-connection-strings <1c db connection strings comma delimited> | -connection-strings-file <connection strings with areas>] -query-file <path to file with 1c query> -result-connection-string <where to put results> [-dump-sql true] [-history-mode true]");
return(-1);
}
var querySources = string.IsNullOrEmpty(connectionStrings)
? StringHelpers.ParseLinesWithTabs(File.ReadAllText(connectionStringsFile),
(s, items) => new QuerySource
{
ConnectionString = s,
Areas = items.Select(int.Parse).ToArray()
}).ToArray()
: connectionStrings.Split(',')
.Select(x => new QuerySource
{
ConnectionString = x,
Areas = new int[0]
}).ToArray();
var target = new MsSqlDatabase(resultConnectionString);
var queryText = File.ReadAllText(queryFile);
var targetTableName = Path.GetFileNameWithoutExtension(queryFile);
var stopwatch = Stopwatch.StartNew();
try
{
var writer = new MsSqlBulkCopyWriter(target, targetTableName, historyMode == "true", 1024);
var parallelOptions = new ParallelOptions
{
CancellationToken = CancellationToken.None,
MaxDegreeOfParallelism = querySources.Length
};
Sql.Execute(querySources, queryText, writer, parallelOptions, dumpSql == "true",
new Dictionary <string, object>());
stopwatch.Stop();
Console.Out.WriteLine("\r\ndone, [{0}] millis", stopwatch.ElapsedMilliseconds);
return(0);
}
catch (Exception e)
{
Console.Out.WriteLine("\r\ndone, [{0}] millis", stopwatch.ElapsedMilliseconds);
Console.Out.WriteLine("error [{0}]", e);
return(-1);
}
}
public BatchWriter(MsSqlDatabase target, string tableName, int batchSize, bool historyMode)
{
this.target = target;
this.tableName = tableName;
this.batchSize = batchSize;
this.historyMode = historyMode;
}
public MsSqlQueryBuilderSelect(MsSqlDatabase database)
{
Database = database ?? throw new System.ArgumentNullException(nameof(database));
_builderWhere = database.QueryFactory.CreateWhere();
_builderWhere.And(_builderWhere);
_limitBuilder = database.QueryFactory.CreateLimit();
}
public void Parameters_DuplicateParameters_AnExceptionIsThrownWhenDuplicateParametersAreFound()
{
var database = new MsSqlDatabase();
Fixture fixture = new Fixture();
var duplicate = fixture.CreateAnonymous<DuplicateParameters>();
database.ConvertToParameters(duplicate);
}
private static void CreateTable(MsSqlDatabase sqlDb, XElement table)
{
var tableName = table.Attribute("name").Value;
sqlDb.ExecuteQuery(new MsSqlQuery($"IF OBJECT_ID(\'{tableName}\', 'U') IS NOT NULL DROP TABLE {tableName}"));
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE ");
sb.Append(tableName);
sb.Append(" (");
bool first = true;
foreach (var tablePart in table.Elements())
{
if (!first)
{
sb.Append(", ");
}
else
{
first = false;
}
if (tablePart.Name == "column")
{
sb.Append(tablePart.Attribute("name").Value);
sb.Append(' ');
sb.Append(tablePart.Attribute("type").Value);
sb.Append(' ');
if (tablePart.Attribute("nullable") != null && tablePart.Attribute("nullable").Value == "true")
{
sb.Append("NULL");
}
else
{
sb.Append("NOT NULL");
}
}
else
{
throw new Exception(String.Format("Unknown table part {1} when creating table {0}", tableName, tablePart.Name));
}
}
sb.Append(")");
string primaryKeyName = "pk_" + tableName;
RemovePrimaryKey(sqlDb, tableName, primaryKeyName);
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
AddPrimaryKey(sqlDb, tableName, primaryKeyName, table);
}
private MsSqlDatabase GetMsSqlDatabase()
{
MsSqlCredentials credentials = IntegratedSecurity == CheckState.Checked ? null : new MsSqlCredentials {
UserName = Form.TextBoxUserName.Text, Password = Form.TextBoxPassword.Text
};
MsSqlDatabase result = new MsSqlDatabase(this.ServerName, this.DatabaseName, credentials);
result.ConnectionString = GetConnectionStringBuilder().ConnectionString;
return(result);
}
public QueryExecuter(QuerySource[] sources, MsSqlDatabase target, string queryText,
string targetTableName, bool dumpSql, bool historyMode)
{
this.sources = sources;
this.target = target;
this.queryText = queryText;
this.targetTableName = targetTableName;
this.dumpSql = dumpSql;
this.historyMode = historyMode;
}
protected override void Initialize()
{
MsSqlDatabase database = new MsSqlDatabase();
MsSqlCredentials creds = new MsSqlCredentials {
UserName = MsSqlUserId, Password = MsSqlPassword
};
MsSqlConnectionStringResolver conn = new MsSqlConnectionStringResolver(MsSqlServerName, MsSqlDatabaseName, creds);
database.ConnectionStringResolver = conn;
Database = database;
}
public void DaoAssemblyGenerator()
{
MsSqlCredentials creds = new MsSqlCredentials {
UserName = "******", Password = "******"
};
MsSqlDatabase db = new MsSqlDatabase(new MsSqlConnectionStringResolver("chumsql2", "Db_Sillydatabase", creds));
MsSqlSmoSchemaExtractor extractor = new MsSqlSmoSchemaExtractor(db);
DaoAssemblyGenerator generator = new DaoAssemblyGenerator(extractor, "C:\\Bam\\Data\\Test");
Out(generator.PropertiesToString(), ConsoleColor.Cyan);
}
public void Start_And_Stop_SQL_server_2019_latest()
{
var db = new MsSqlDatabase("BardDB_SQL_2019",
"Password1",
"1066");
var result = db.StartDatabase();
_output.WriteLine(result);
db.StopDatabase();
}
public void Parameters_SingleLevelClass_PropertiesAreConvertedToDbParameters()
{
Fixture fixture = new Fixture();
var user = fixture.CreateAnonymous<SingleLevelClass>();
var propertyNames = user.GetType().GetProperties().Select(p => "@" + p.Name).ToArray();
var database = new MsSqlDatabase();
var parameters = database.ConvertToParameters(user);
var parameterNames = parameters.Select(d => d.ParameterName).ToArray();
Assert.That(parameterNames, Is.EquivalentTo(propertyNames));
Console.WriteLine(string.Join(", ", parameterNames));
}
public void Parameters_ThirdLevelClass_PropertiesAreConvertedToDbParameters()
{
var database = new MsSqlDatabase();
Fixture fixture = new Fixture();
var user = fixture.CreateAnonymous<ThirdLevelClass>();
var propertyNames = new[] { "@Id", "@FirstName", "@LastName", "@Email", "@Password", "@CreateTime", "@UpdateDatetime" };
var parameters = database.ConvertToParameters(user);
var parameterNames = parameters.Select(d => d.ParameterName).ToArray();
Assert.That(parameterNames, Is.EquivalentTo(propertyNames));
Console.WriteLine(string.Join(", ", parameterNames));
}
public void DiagnosticInfoShouldHaveMsSqlDatabase()
{
string name = 8.RandomLetters();
MsSqlDatabase msDatabase = new MsSqlDatabase("chumsql2", "DaoRef", name);
DiagnosticService svc = new DiagnosticService(null);
DiagnosticInfo info = svc.GetDiagnosticInfo();
DatabaseInfo dbInfo = info.Databases.FirstOrDefault(dbi => dbi.ConnectionName.Equals(name));
Expect.IsNotNull(dbInfo);
Expect.AreEqual(name, dbInfo.ConnectionName);
Expect.AreEqual(typeof(MsSqlDatabase).FullName, dbInfo.DatabaseType);
OutLineFormat("{0}", ConsoleColor.DarkBlue, info.ToYaml());
}
public void Start_And_Stop_SQL_server_2019_CU6_ubuntu_16_04()
{
var db = new MsSqlDatabase("BardDB_SQL_2019_CU6_ubuntu_16.04",
"Password1",
"1066",
"2019-CU6-ubuntu-16.04");
var result = db.StartDatabase();
_output.WriteLine(result);
db.StopDatabase();
}
public void Start_And_Stop_SQL_server_2017_latest()
{
var db = new MsSqlDatabase(
databaseName: "BardDB_SQL_2017",
saPassword: "******",
portNumber: "1066",
tagName: "2017-latest");
var result = db.StartDatabase();
_output.WriteLine(result);
db.StopDatabase();
}
public static HashSet <Database> Setup(Action <Database> initializer = null, string databaseName = "DaoRef")
{
if (initializer == null)
{
initializer = db => Db.TryEnsureSchema <TestTable>(db);
}
HashSet <Database> testDatabases = new HashSet <Database>();
MsSqlDatabase msDatabase = new MsSqlDatabase("chumsql2", databaseName, new MsSqlCredentials {
UserName = "******", Password = "******"
});
initializer(msDatabase);
testDatabases.Add(msDatabase);
SQLiteDatabase sqliteDatabase = new SQLiteDatabase(".\\Chumsql2", databaseName);
initializer(sqliteDatabase);
testDatabases.Add(sqliteDatabase);
OleDbDatabase oleDatabase = new OleDbDatabase("Microsoft.ACE.OLEDB.12.0", databaseName.RandomLetters(4));
initializer(oleDatabase);
testDatabases.Add(oleDatabase);
OracleDatabase oracleDatabase = new OracleDatabase("chumsql2", databaseName, new OracleCredentials {
UserId = "C##ORACLEUSER", Password = "******"
});
initializer(oracleDatabase);
testDatabases.Add(oracleDatabase);
MySqlDatabase mySqlDatabase = new MySqlDatabase("chumsql2", databaseName, new MySqlCredentials {
UserId = "mysql", Password = "******"
});
initializer(mySqlDatabase);
testDatabases.Add(mySqlDatabase);
NpgsqlDatabase npgsqlDatabase = new NpgsqlDatabase("chumsql2", databaseName, new NpgsqlCredentials {
UserId = "postgres", Password = "******"
});
initializer(npgsqlDatabase);
testDatabases.Add(npgsqlDatabase);
return(testDatabases);
}
private static void RemovePrimaryKey(MsSqlDatabase sqlDb, string tableName, string primaryKeyName)
{
StringBuilder sb = new StringBuilder();
sb.Append("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'");
sb.Append(primaryKeyName);
sb.Append("') AND type in (N'U'))");
sb.AppendLine();
sb.Append("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" DROP CONSTRAINT ");
sb.Append(primaryKeyName);
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
}
public Login CreateLogin()
{
Login login;
MsSqlDatabase msdb = new MsSqlDatabase("enova", @"DESKTOP-LDPQC0R\SQL2017", "enova", "sa", "sa", false);
msdb.Active = true;
try
{
login = msdb.Login(false, "Administrator", "123456");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
return(login);
}
private static int RunSql(NameValueCollection parameters)
{
var connectionStrings = parameters["connection-strings"];
var connectionStringsFile = parameters["connection-strings-file"];
var queryFile = parameters["query-file"];
var resultConnectionString = parameters["result-connection-string"];
var dumpSql = parameters["dump-sql"];
var historyMode = parameters["history-mode"];
var parametersAreValid =
(!string.IsNullOrEmpty(connectionStrings) || !string.IsNullOrEmpty(connectionStringsFile)) &&
!string.IsNullOrEmpty(queryFile) &&
!string.IsNullOrEmpty(resultConnectionString);
if (!parametersAreValid)
{
Console.Out.WriteLine("Invalid arguments");
Console.Out.WriteLine(
"Usage: Generator.exe -cmd run-sql [-connection-strings <1c db connection strings comma delimited> | -connection-strings-file <connection strings with areas>] -query-file <path to file with 1c query> -result-connection-string <where to put results> [-dump-sql true] [-history-mode true]");
return(-1);
}
var querySources = string.IsNullOrEmpty(connectionStrings)
? StringHelpers.ParseLinesWithTabs(File.ReadAllText(connectionStringsFile),
(s, items) => new QuerySource
{
db = new PostgreeSqlDatabase(s),
areas = items.Select(int.Parse).ToArray()
})
: connectionStrings.Split(',')
.Select(x => new QuerySource
{
db = new PostgreeSqlDatabase(x),
areas = new int[0]
});
var target = new MsSqlDatabase(resultConnectionString);
var queryText = File.ReadAllText(queryFile);
var targetTableName = Path.GetFileNameWithoutExtension(queryFile);
var sqlExecuter = new QueryExecuter(querySources.ToArray(), target, queryText,
targetTableName, dumpSql == "true", historyMode == "true");
var succeeded = sqlExecuter.Execute();
return(succeeded ? 0 : -1);
}
private static void AddPrimaryKey(MsSqlDatabase sqlDb, string tableName, string primaryKeyName, XElement table)
{
StringBuilder sb = new StringBuilder();
sb.Append("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" ADD CONSTRAINT ");
sb.Append(primaryKeyName);
sb.Append(" PRIMARY KEY (");
bool first = true;
foreach (var tablePart in table.Elements())
{
if (tablePart.Name == "column" &&
tablePart.Attribute("primary-key") != null &&
tablePart.Attribute("primary-key").Value == "true")
{
if (!first)
{
sb.Append(", ");
}
else
{
first = false;
}
sb.Append(tablePart.Attribute("name").Value);
}
}
if (!first)
{
sb.Append(")");
sqlDb.ExecuteQuery(new MsSqlQuery(sb.ToString()));
}
}
private void QueryTestByProc()
{
Console.WriteLine("\r\n开始测试使用存储过程查询数据...\r\n");
string time = Stopwatch(() =>
{
MsSqlDatabase database = new MsSqlDatabase("MsSqlBaseDbConnectionString");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@FEILDS", "*", DbType.String),
DbParameters.CreateDbParameter("@TABLE_NAME", "Base_User", DbType.String),
DbParameters.CreateDbParameter("@PAGE_INDEX", 1, DbType.Int32),
DbParameters.CreateDbParameter("@PAGE_SIZE", 100000, DbType.Int32),
DbParameters.CreateDbParameter("@ORDERTYPE", 1, DbType.Int32),
DbParameters.CreateDbParameter("@ANDWHERE", "DeleteMark=0", DbType.String),
DbParameters.CreateDbParameter("@ORDERFEILD", "CreateDate", DbType.String),
};
List <UserEntity> res = database.ExecuteByProc <UserEntity>("[dbo].[PROC_PAGINATIONBY_SINGLE_TABLE]", parameter).ToList();
Console.WriteLine("QueryTestByProc=>记录数：" + res.Count);
});
Console.WriteLine("执行结束，耗时：" + time);
}
public static SchemaExtractor GetMsSqlSchemaExtractor(MsSqlDatabase database)
{
return(new MsSqlSchemaExtractor(database));
}
public ConnectionApp()
{
Database = new MsSqlDatabase("Data Source=localhost;Initial Catalog=ExampleDatabase;Integrated Security=SSPI");
}
/// <summary>
/// Connect to MS SQL Server
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void ConnectToMsSql_OnExecuted(object sender, ExecutedRoutedEventArgs e)
{
await DiagramFacade.CloseDiagramsOnDisconnect(this);
SessionProvider.Instance.Disconnect();
DatabaseConnectionSidebar.HideDatabaseStackPanels();
ProgressDialogController progressDialogController = null;
Func <ProgressDialogController, Task> closeProgress = async t =>
{
if (t != null)
{
if (t.IsOpen)
{
await t.CloseAsync();
}
}
};
try
{
progressDialogController = await this.ShowProgressAsync("Please wait", "Connecting to server...", false, new MetroDialogSettings()
{
AnimateShow = false,
AnimateHide = false
});
progressDialogController.SetIndeterminate();
MsSqlDatabase db = new MsSqlDatabase();
string name = null, pass = null;
bool integratedSecurity = true;
var server = MsSqlServerNameTextBox.Text;
if (WinAuthSwitch.IsChecked != null)
{
integratedSecurity = !WinAuthSwitch.IsChecked.Value;
}
if (!integratedSecurity)
{
name = MsSqlUsernameTextBox.Text;
pass = MsSqlPasswordBox.Password;
}
await db.BuildSession(server, integratedSecurity, name, pass);
await closeProgress(progressDialogController);
await this.ShowMessageAsync("Connected", $"Successfuly connected to {SessionProvider.Instance.ServerName}");
var flyout = Flyouts.Items[0] as Flyout;
if (flyout != null)
{
flyout.IsOpen = !flyout.IsOpen;
}
await DatabaseConnectionSidebar.LoadMsSqlData();
}
catch (SqlException exception)
{
await closeProgress(progressDialogController);
await this.ShowMessageAsync("Connection error", exception.Message);
SessionProvider.Instance.ConnectionType = ConnectionType.None;
}
}
private async void ConnectToMsSqlServerOwnConnString_OnExecuted(object sender, ExecutedRoutedEventArgs e)
{
var example =
@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Init;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True";
var res = await this.ShowInputAsync("Connection string", $"Use customized connection string \n (Example: {example})");
if (res != null)
{
await DiagramFacade.CloseDiagramsOnDisconnect(this);
SessionProvider.Instance.Disconnect();
DatabaseConnectionSidebar.HideDatabaseStackPanels();
ProgressDialogController progressDialogController = null;
Func <ProgressDialogController, Task> closeProgress = async t =>
{
if (t != null)
{
if (t.IsOpen)
{
await t.CloseAsync();
}
}
};
try
{
progressDialogController = await this.ShowProgressAsync("Please wait", "Connecting to server...", false,
new MetroDialogSettings()
{
AnimateShow = false,
AnimateHide = false
});
progressDialogController.SetIndeterminate();
var db = new MsSqlDatabase();
await db.TryToConnectToServer(res);
SessionProvider.Instance.OwnConnectionString = res;
SessionProvider.Instance.UseOwnConnectionString = true;
SessionProvider.Instance.ConnectionType = ConnectionType.SqlServer;
await closeProgress(progressDialogController);
await this.ShowMessageAsync("Connected", $"Successfuly connected to server");
var flyout = Flyouts.Items[0] as Flyout;
if (flyout != null)
{
flyout.IsOpen = !flyout.IsOpen;
}
await DatabaseConnectionSidebar.LoadMsSqlData();
}
catch (SqlException exception)
{
await closeProgress(progressDialogController);
await this.ShowMessageAsync("Connection error", exception.Message);
SessionProvider.Instance.ConnectionType = ConnectionType.None;
}
catch (ArgumentException)
{
await closeProgress(progressDialogController);
await this.ShowMessageAsync("Connection error", "Connection string is not valid");
SessionProvider.Instance.ConnectionType = ConnectionType.None;
}
}
}
public MsSqlEviStorage(IR2RML mapping, MsSqlDatabase database)
: base(mapping, database)
{
}
private static void ExecuteQuery(MsSqlDatabase sqlDb, XElement query)
{
sqlDb.ExecuteQuery(new MsSqlQuery(query.Value));
}
