/// <summary> /// Run the provided query against the source database to get the data to be moved. /// </summary> /// <param name="server">ip or name of the source database</param> /// <param name="keyspace">name of the keyspace to use for the source query</param> /// <param name="query">query to get source data</param> /// <param name="gotSourceData">Data retrieved</param> /// <param name="sourceData">indicator of if an error was thrown</param> private static void GetSourceData(string server, string keyspace, string query, TransferResultsInfo info, out bool gotSourceData, out List <dynamic> sourceData) { sourceData = new List <dynamic>(); gotSourceData = true; try { CqlConnection srcConn = new CqlConnection("Contact Points=" + server, server); CqlCommand srcCmd = new CqlCommand(); srcConn.Open(); srcConn.ChangeDatabase(keyspace); srcCmd.Connection = srcConn; srcCmd.CommandType = CommandType.Text; srcCmd.CommandText = query; sourceData = srcCmd.ExecuteDynamics(); info.SourceCount = sourceData.Count; //cleanup srcCmd.Connection.Close(); srcCmd.Connection.Dispose(); srcCmd.Dispose(); } catch (Exception ex) { info.Message = "Error getting source data: \r\n" + ex.Message; gotSourceData = false; } }
/// <summary> /// Insert retrived data into destination table /// </summary> /// <param name="server">ip or name of the destination database</param> /// <param name="keyspace">name of the keyspace to use for the insert</param> /// <param name="columnFamily">name of the column family for the insert</param> /// <param name="sourceData">data retrieved from the source</param> private static void InsertIntoDest(string server, string keyspace, string columnFamily, List <dynamic> sourceData, TransferResultsInfo info) { try { CqlConnection destConn = new CqlConnection("Contact Points=" + server, server); CqlCommand destCmd = new CqlCommand(); destConn.Open(); destConn.ChangeDatabase(keyspace); destCmd.Connection = destConn; destCmd.CommandText = "SELECT COUNT(*) FROM " + columnFamily; info.OriginalDestCount = Convert.ToInt32(destCmd.ExecuteScalar()); destCmd.InsertDynamicList(sourceData, columnFamily); destCmd.CommandText = "SELECT COUNT(*) FROM " + columnFamily; info.AfterDestCount = Convert.ToInt32(destCmd.ExecuteScalar()); //cleanup destCmd.Connection.Close(); destCmd.Connection.Dispose(); destCmd.Dispose(); } catch (Exception ex) { info.Message = "Error writting to destination: \r\n" + ex.Message; } }
public void ExecuteNonQueryInsertAndSelectTest() { _connection.Open(); var cmd = _connection.CreateCommand(); string keyspaceName = "keyspace_ado_1"; cmd.CommandText = string.Format(TestUtils.CREATE_KEYSPACE_SIMPLE_FORMAT, keyspaceName, 3); cmd.ExecuteNonQuery(); _connection.ChangeDatabase(keyspaceName); string tableName = "table_ado_1"; cmd.CommandText = string.Format(@" CREATE TABLE {0}( tweet_id uuid, author text, body text, isok boolean, PRIMARY KEY(tweet_id))", tableName); cmd.ExecuteNonQuery(); var longQ = new StringBuilder(); longQ.AppendLine("BEGIN BATCH "); int RowsNo = 300; for (int i = 0; i < RowsNo; i++) { longQ.AppendFormat(@" INSERT INTO {0} (tweet_id, author, isok, body) VALUES ({1},'test{2}',{3},'body{2}');", tableName, Guid.NewGuid(), i, i % 2 == 0 ? "false" : "true"); } longQ.AppendLine("APPLY BATCH;"); cmd.CommandText = longQ.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = string.Format(@"SELECT * from {0} LIMIT 10000;", tableName); var reader = cmd.ExecuteReader(); var counter = 0; while (reader.Read()) { Assert.AreEqual(4, reader.FieldCount); counter++; } Assert.AreEqual(RowsNo, counter); cmd.CommandText = string.Format(@"DROP TABLE {0};", tableName); cmd.ExecuteNonQuery(); cmd.CommandText = string.Format(@"DROP KEYSPACE {0};", keyspaceName); cmd.ExecuteNonQuery(); }
public void complexTest() { connection.Open(); var cmd = connection.CreateCommand(); string keyspaceName = "keyspace" + Guid.NewGuid().ToString("N").ToLower(); cmd.CommandText = string.Format(@"CREATE KEYSPACE {0} WITH replication = {{ 'class' : 'SimpleStrategy', 'replication_factor' : 1 }};" , keyspaceName); cmd.ExecuteNonQuery(); connection.ChangeDatabase(keyspaceName); string tableName = "table" + Guid.NewGuid().ToString("N").ToLower(); cmd.CommandText = string.Format(@"CREATE TABLE {0}( tweet_id uuid, author text, body text, isok boolean, PRIMARY KEY(tweet_id))", tableName); cmd.ExecuteNonQuery(); StringBuilder longQ = new StringBuilder(); longQ.AppendLine("BEGIN BATCH "); int RowsNo = 2000; for (int i = 0; i < RowsNo; i++) { longQ.AppendFormat(@"INSERT INTO {0} ( tweet_id, author, isok, body) VALUES ({1},'test{2}',{3},'body{2}');", tableName, Guid.NewGuid().ToString(), i, i % 2 == 0 ? "false" : "true"); } longQ.AppendLine("APPLY BATCH;"); cmd.CommandText = longQ.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = string.Format(@"SELECT * from {0} LIMIT 10000;", tableName); var reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader.GetValue(i).ToString() + "|"); } Console.WriteLine(); } cmd.CommandText = string.Format(@"DROP TABLE {0};", tableName); cmd.ExecuteNonQuery(); cmd.CommandText = string.Format(@"DROP KEYSPACE {0};", keyspaceName); cmd.ExecuteNonQuery(); }
public void ExecuteNonQueryInsertAndSelectTest() { _connection.Open(); var cmd = _connection.CreateCommand(); string keyspaceName = "keyspace_ado_1"; cmd.CommandText = string.Format(TestUtils.CreateKeyspaceSimpleFormat, keyspaceName, 3); cmd.ExecuteNonQuery(); VerifyStatement( QueryType.Query, $"CREATE KEYSPACE \"{keyspaceName}\" WITH replication = {{ 'class' : 'SimpleStrategy', 'replication_factor' : {3} }}", 1); _connection.ChangeDatabase(keyspaceName); VerifyStatement(QueryType.Query, $"USE \"{keyspaceName}\"", 1); string tableName = "table_ado_1"; cmd.CommandText = string.Format("CREATE TABLE {0} (tweet_id uuid,author text,body text,isok boolean,PRIMARY KEY(tweet_id))", tableName); cmd.ExecuteNonQuery(); VerifyStatement( QueryType.Query, $"CREATE TABLE {tableName} (tweet_id uuid,author text,body text,isok boolean,PRIMARY KEY(tweet_id))", 1); var longQ = new StringBuilder(); longQ.AppendLine("BEGIN BATCH "); var guids = Enumerable.Range(0, 300).Select(i => Guid.NewGuid()).ToArray(); int RowsNo = 300; for (int i = 0; i < RowsNo; i++) { longQ.AppendFormat("INSERT INTO {0} (tweet_id, author, isok, body) VALUES ({1},'test{2}',{3},'body{2}');", tableName, guids[i], i, i % 2 == 0 ? "false" : "true"); } longQ.AppendLine("APPLY BATCH;"); cmd.CommandText = longQ.ToString(); cmd.ExecuteNonQuery(); VerifyStatement( QueryType.Query, longQ.ToString(), 1); TestCluster.PrimeFluent( b => b.WhenQuery($"SELECT * from {tableName} LIMIT 10000;") .ThenRowsSuccess( new[] { "tweet_id", "author", "body", "isok" }, r => r.WithRows(guids.Select((guid, idx) => new object[] { guid, $"test{idx}", $"body{idx}", idx % 2 != 0 }).ToArray()))); cmd.CommandText = string.Format(@"SELECT * from {0} LIMIT 10000;", tableName); var reader = cmd.ExecuteReader(); var counter = 0; while (reader.Read()) { Assert.AreEqual(4, reader.FieldCount); counter++; } Assert.AreEqual(RowsNo, counter); }
public async Task ChangeDatabaseThenInsertSelect() { //Assume const string insertCql = @"insert into BasicFlow (id,value) values (901,'Hallo 901');"; const string retrieveCql = @"select * from BasicFlow;"; //Act using(var connection = new CqlConnection(ConnectionString)) { Assert.AreEqual("", connection.Database); await connection.OpenAsync(); //change database connection.ChangeDatabase("test"); Assert.AreEqual("test", connection.Database); //insert data var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One); await cmd.ExecuteNonQueryAsync(); //select data var selectCmd = new CqlCommand(connection, retrieveCql, CqlConsistency.One); CqlDataReader reader = await selectCmd.ExecuteReaderAsync(); Assert.AreEqual(1, reader.Count); if(await reader.ReadAsync()) { Assert.AreEqual(901, reader["id"]); Assert.AreEqual("Hallo 901", reader["value"]); Assert.AreEqual(DBNull.Value, reader["ignored"]); } else Assert.Fail("Read should have succeeded"); } }