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)); }