mysql fabric c .net_MySQL Fabric for .net

Using Ranged Sharding

Sharding with Connector/Net requires you to specify the table, key, and scope for each executed query.

MySqlConnection con = new MySqlConnection(connectionString);

con.SetFabricProperties(table: "employees.employees", key: empId.ToString(),

mode: FabricServerModeEnum.Read_Write, scope: FabricScopeEnum.Local);

MySqlCommand cmd = new MySqlCommand(

string.Format("insert into employees(emp_no, first_name, last_name) values ({0}, '{1}', '{2}')",

empId, firstName, lastName), con);

cmd.ExecuteScalar();

You can use the following MySQL Fabric configuration to execute the code example:

Note

For related MySQL Fabric documentation, see Sharding Scenario.

using System;

using System.Collections.Generic;

using MySql.Data.MySqlClient;

using MySql.Fabric;

namespace FabricTest

{

class ShardTest

{

public const string connectionString = "server=fabric;uid=appuser;password=pass;";

public static void test_shard_range()

{

using (MySqlConnection con = new MySqlConnection(connectionString))

{

con.SetFabricProperties(groupId: "group_id-global", mode: FabricServerModeEnum.Read_Write,

scope: FabricScopeEnum.Global);

con.Open();

MySqlCommand cmd = new MySqlCommand("create database if not exists employees", con);

cmd.ExecuteScalar();

cmd.CommandText = "use employees";

cmd.ExecuteScalar();

cmd.CommandText = "drop table if exists employees";

cmd.ExecuteScalar();

cmd.CommandText =

@"create table employees (

emp_no int,

first_name char( 40 ),

last_name char( 40 )

)";

cmd.ExecuteScalar();

string gtid = prepare_synchronization(con);

string[] first_names = { "John", "Buffalo", "Michael", "Kate", "Deep", "Genesis" };

string[] last_names = { "Doe", "Bill", "Jackson", "Bush", "Purple" };

List list_emp_no = new List();

con.SetFabricProperties(scope: FabricScopeEnum.Local);

for (int i = 0; i < 10; i++)

{

int empId = pick_shard_key();

list_emp_no.Add(empId);

add_employee(con, empId, first_names[empId % first_names.Length], last_names[empId % last_names.Length], gtid);

}

for (int i = 0; i < list_emp_no.Count; i++)

{

int empId = list_emp_no[ i ];

find_employee(con, empId, gtid);

}

}

}

public static int pick_shard_key()

{

Random r = new Random();

int shard = r.Next(0, 2);

int shard_range = shard * 10000;

shard_range = (shard != 0) ? shard_range : shard_range + 1;

int shift_within_shard = r.Next(0, 99999);

return shard_range + shift_within_shard;

}

public static void add_employee(MySqlConnection con, int empId, string firstName, string lastName, string gtid)

{

con.SetFabricProperties( table: "employees.employees", key: empId.ToString(), mode: FabricServerModeEnum.Read_Write);

synchronize(con, gtid);

MySqlCommand cmd = new MySqlCommand(

string.Format("insert into employees( emp_no, first_name, last_name ) values ( {0}, '{1}', '{2}' )",

empId, firstName, lastName), con);

cmd.ExecuteScalar();

}

public static void find_employee(MySqlConnection con, int empId, string gtid)

{

con.SetFabricProperties(table: "employees.employees", key: empId.ToString(),

mode: FabricServerModeEnum.Read_only);

synchronize(con, gtid);

MySqlCommand cmd = new MySqlCommand(string.Format("

select first_name, last_name from employees where emp_no = {0}", empId), con);

using (MySqlDataReader r = cmd.ExecuteReader())

{

while (r.Read())

{

Console.WriteLine("( {0}, {1} )", r.GetString(0), r.GetString(1));

}

}

}

public static string prepare_synchronization(MySqlConnection con)

{

string gtid_executed = "";

MySqlCommand cmd = new MySqlCommand("select @@global.gtid_executed", con);

gtid_executed = ( string )cmd.ExecuteScalar();

return gtid_executed;

}

public static void synchronize(MySqlConnection con, string gtid_executed)

{

MySqlCommand cmd = new MySqlCommand( string.Format( "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('{0}', 0)",

gtid_executed ), con);

cmd.ExecuteScalar();

}

}

}