/// <summary>
/// Method to get the column names
/// </summary>
/// <param name="mapItemCollection"></param>
/// <param name="propertyToFetch"></param>
/// <returns></returns>
public string GetColumnNames(ColumnMapItemCollection mapItemCollection, ColumnProperty propertyToFetch)
{
if (mapItemCollection == null)
{
throw new ArgumentNullException("mapItemCollection");
}
if (mapItemCollection.Count <= 0)
{
throw new ArgumentOutOfRangeException("mapItemCollection");
}
StringBuilder builder = new StringBuilder();
switch (propertyToFetch)
{
case ColumnProperty.Source:
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
builder.AppendFormat("{0},", columnMapItem.SourceColumn);
}
break;
case ColumnProperty.Destination:
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
builder.AppendFormat("{0},", columnMapItem.DestinationColumn);
}
break;
default:
builder.Append(",");
break;
}
return(builder.ToString().Substring(0, builder.Length - 1));
}
/// <summary>
/// Method to get the column names
/// </summary>
/// <param name="mapItemCollection"></param>
/// <param name="propertyToFetch"></param>
/// <returns></returns>
public string GetColumnNames(ColumnMapItemCollection mapItemCollection, ColumnProperty propertyToFetch)
{
if (mapItemCollection == null) { throw new ArgumentNullException("mapItemCollection"); }
if (mapItemCollection.Count <= 0) { throw new ArgumentOutOfRangeException("mapItemCollection"); }
StringBuilder builder = new StringBuilder();
switch (propertyToFetch)
{
case ColumnProperty.Source:
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
builder.AppendFormat("{0},", columnMapItem.SourceColumn);
}
break;
case ColumnProperty.Destination:
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
builder.AppendFormat("{0},", columnMapItem.DestinationColumn);
}
break;
default:
builder.Append(",");
break;
}
return builder.ToString().Substring(0, builder.Length - 1);
}
public string ConstructSql(string tableName, DataRow row, ColumnMapItemCollection mapItemCollection)
{
// Get the column names that need to be used
string columnNames = GetColumnNames(mapItemCollection, ColumnProperty.Destination);
// construct the base Skeleton of the sql.
string baseSql = string.Format("insert into {0}({1}) values({2})", tableName, columnNames, "{0}");
// loop through the collection and construct the values string
StringBuilder builder = new StringBuilder();
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
string constructedValue = ConstructIndividualValue(columnMapItem.DataType,
row[columnMapItem.SourceColumn].ToString());
builder.Append(constructedValue);
}
return string.Format(baseSql, builder.ToString().Substring(0, builder.ToString().Length - 1));
}
public string ConstructSql(string tableName, DataRow row, ColumnMapItemCollection mapItemCollection)
{
// Get the column names that need to be used
string columnNames = GetColumnNames(mapItemCollection, ColumnProperty.Destination);
// construct the base Skeleton of the sql.
string baseSql = string.Format("insert into {0}({1}) values({2})", tableName, columnNames, "{0}");
// loop through the collection and construct the values string
StringBuilder builder = new StringBuilder();
foreach (ColumnMapItem columnMapItem in mapItemCollection)
{
string constructedValue = ConstructIndividualValue(columnMapItem.DataType,
row[columnMapItem.SourceColumn].ToString());
builder.Append(constructedValue);
}
return(string.Format(baseSql, builder.ToString().Substring(0, builder.ToString().Length - 1)));
}
private void Form1_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=destination;Uid=root;Pwd=12345;");
MySql.Data.MySqlClient.MySqlConnection sourceConnection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=users;Uid=root;Pwd=12345;");
try
{
connection.Open();
sourceConnection.Open();
DataTable table = new DataTable();
MySqlBulkCopy upload = new MySqlBulkCopy();
upload.DestinationTableName = "session";
ColumnMapItemCollection collection = new ColumnMapItemCollection();
ColumnMapItem sessionId = new ColumnMapItem();
ColumnMapItem userId = new ColumnMapItem();
ColumnMapItem dateLogged = new ColumnMapItem();
ColumnMapItem loggedFrom = new ColumnMapItem();
ColumnMapItem active = new ColumnMapItem();
sessionId.DataType = "text";
sessionId.DestinationColumn = "IdSession";
sessionId.SourceColumn = "IdSession";
userId.DataType = "int";
userId.DestinationColumn = "userid";
userId.SourceColumn = "userid";
dateLogged.DataType = "datetime";
dateLogged.DestinationColumn = "dateLogged";
dateLogged.SourceColumn = "dateLogged";
loggedFrom.DataType = "text";
loggedFrom.DestinationColumn = "loggedFrom";
loggedFrom.SourceColumn = "loggedFrom";
active.DataType = "int";
active.DestinationColumn = "active";
active.SourceColumn = "active";
collection.Add(sessionId);
collection.Add(userId);
collection.Add(dateLogged);
collection.Add(loggedFrom);
collection.Add(active);
upload.ColumnMapItems = collection;
upload.DestinationDbConnection = connection;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("select idsession,userid,datelogged,loggedfrom,active from session", sourceConnection);
MySql.Data.MySqlClient.MySqlDataReader reader = command.ExecuteReader();
upload.Upload(reader);
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
connection.Dispose();
sourceConnection.Close();
sourceConnection.Dispose();
}