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