/*
a、注意数据库编码要能兼容gb2312和big5,比如MySql中使用utf8
b、该代码采用遍历的方式,并用MySqlCommandBuilder进行批量更新,所以能转换的表必须包含主键,不包括主键的表则不能转换
c、引用了Microsoft.VisualBasic.dll进行简繁转换
*/
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.VisualBasic;
namespace Gb2312ToBig5
{
class Program
{
static void Main( string [] args)
{
// 入口
Console.WriteLine( " 请输入数据库所在IP: " );
string ip = Console.ReadLine().Trim();
Console.WriteLine( " 请输入数据库名称: " );
string db = Console.ReadLine().Trim();
Console.WriteLine( " 请输入登录数据库用户名: " );
string user = Console.ReadLine().Trim();
Console.WriteLine( " 请输入登录数据库密码: " );
string psw = Console.ReadLine();
string connectionString = " Data Source= " + ip + " ;User ID= " + user + " ;Password= " + psw + " ;DataBase= " + db + " ;Allow Zero Datetime=true;Charset=utf8; " ;
Console.WriteLine( " 生成的数据库连接字符串为:{0},继续吗?(Y/N) " , connectionString);
if (Console.ReadLine().ToString().ToUpper() == " Y " )
{
// 包含所有表名称的DataTable
DataTable dtAll = tableList(connectionString);
if (dtAll != null )
{
if (dtAll.Rows.Count > 0 )
{
Console.Write( " 转换中,请稍候: " );
for ( int i = 0 ; i < dtAll.Rows.Count; i ++ )
{
dtConvert(dtAll.Rows[i][ 0 ].ToString(), connectionString);
}
}
}
}
}
// 将DataTable中每行每列转为繁体
private static void dtConvert( string dtName, string connectionString)
{
string sql = "" ;
MySqlCommand cmd = null ;
MySqlDataAdapter da = null ;
DataTable dt = null ;
MySqlCommandBuilder builder = null ;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
sql = " select * from " + dtName;
cmd = new MySqlCommand(sql, conn);
conn.Open();
da = new MySqlDataAdapter(cmd);
// 添加主键映射
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dt = new DataTable();
da.Fill(dt);
// 遍历dt做替换
if (dt.Rows.Count > 0 )
{
// 如果表包含主键
if (dt.PrimaryKey.Length > 0 )
{
#region 遍历
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
for ( int j = 0 ; j < dt.Columns.Count; j ++ )
{
if (dt.Columns[j].DataType.ToString() == " System.String " )
{
if (dt.Rows[i][j] != null )
{
if (dt.Rows[i][j].ToString() != string .Empty)
{
dt.Rows[i][j] = getBig5(dt.Rows[i][j].ToString());
Console.Write( " . " );
}
}
}
}
}
#endregion
builder = new MySqlCommandBuilder(da);
da.Update(dt);
}
}
// 释放资源
builder.Dispose();
cmd.Dispose();
da.Dispose();
dt.Clear();
dt.Dispose();
}
catch (Exception error)
{
Console.WriteLine(error.ToString());
}
finally
{
conn.Close();
}
}
}
// 遍历每个表
private static DataTable tableList( string connectionString)
{
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
// SHOW TABLES为MySQL列出所有表,如SQLServer请使用相关命令
MySqlCommand cmd = new MySqlCommand( " SHOW TABLES " ,conn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, " temp_tables " );
dt = ds.Tables[ " temp_tables " ];
}
catch (Exception error)
{
Console.WriteLine(error.ToString());
}
finally
{
conn.Close();
}
}
return dt;
}
// 简体转繁体
private static string getBig5( string gb2312)
{
string big5 = "" ;
if ((gb2312 != null ) && (gb2312 != String.Empty))
{
gb2312 = gb2312.Trim();
big5 = Strings.StrConv(gb2312,VbStrConv.TraditionalChinese, 0 );
}
return big5;
}
}
}
a、注意数据库编码要能兼容gb2312和big5,比如MySql中使用utf8
b、该代码采用遍历的方式,并用MySqlCommandBuilder进行批量更新,所以能转换的表必须包含主键,不包括主键的表则不能转换
c、引用了Microsoft.VisualBasic.dll进行简繁转换
*/
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.VisualBasic;
namespace Gb2312ToBig5
{
class Program
{
static void Main( string [] args)
{
// 入口
Console.WriteLine( " 请输入数据库所在IP: " );
string ip = Console.ReadLine().Trim();
Console.WriteLine( " 请输入数据库名称: " );
string db = Console.ReadLine().Trim();
Console.WriteLine( " 请输入登录数据库用户名: " );
string user = Console.ReadLine().Trim();
Console.WriteLine( " 请输入登录数据库密码: " );
string psw = Console.ReadLine();
string connectionString = " Data Source= " + ip + " ;User ID= " + user + " ;Password= " + psw + " ;DataBase= " + db + " ;Allow Zero Datetime=true;Charset=utf8; " ;
Console.WriteLine( " 生成的数据库连接字符串为:{0},继续吗?(Y/N) " , connectionString);
if (Console.ReadLine().ToString().ToUpper() == " Y " )
{
// 包含所有表名称的DataTable
DataTable dtAll = tableList(connectionString);
if (dtAll != null )
{
if (dtAll.Rows.Count > 0 )
{
Console.Write( " 转换中,请稍候: " );
for ( int i = 0 ; i < dtAll.Rows.Count; i ++ )
{
dtConvert(dtAll.Rows[i][ 0 ].ToString(), connectionString);
}
}
}
}
}
// 将DataTable中每行每列转为繁体
private static void dtConvert( string dtName, string connectionString)
{
string sql = "" ;
MySqlCommand cmd = null ;
MySqlDataAdapter da = null ;
DataTable dt = null ;
MySqlCommandBuilder builder = null ;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
sql = " select * from " + dtName;
cmd = new MySqlCommand(sql, conn);
conn.Open();
da = new MySqlDataAdapter(cmd);
// 添加主键映射
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dt = new DataTable();
da.Fill(dt);
// 遍历dt做替换
if (dt.Rows.Count > 0 )
{
// 如果表包含主键
if (dt.PrimaryKey.Length > 0 )
{
#region 遍历
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
for ( int j = 0 ; j < dt.Columns.Count; j ++ )
{
if (dt.Columns[j].DataType.ToString() == " System.String " )
{
if (dt.Rows[i][j] != null )
{
if (dt.Rows[i][j].ToString() != string .Empty)
{
dt.Rows[i][j] = getBig5(dt.Rows[i][j].ToString());
Console.Write( " . " );
}
}
}
}
}
#endregion
builder = new MySqlCommandBuilder(da);
da.Update(dt);
}
}
// 释放资源
builder.Dispose();
cmd.Dispose();
da.Dispose();
dt.Clear();
dt.Dispose();
}
catch (Exception error)
{
Console.WriteLine(error.ToString());
}
finally
{
conn.Close();
}
}
}
// 遍历每个表
private static DataTable tableList( string connectionString)
{
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
// SHOW TABLES为MySQL列出所有表,如SQLServer请使用相关命令
MySqlCommand cmd = new MySqlCommand( " SHOW TABLES " ,conn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds, " temp_tables " );
dt = ds.Tables[ " temp_tables " ];
}
catch (Exception error)
{
Console.WriteLine(error.ToString());
}
finally
{
conn.Close();
}
}
return dt;
}
// 简体转繁体
private static string getBig5( string gb2312)
{
string big5 = "" ;
if ((gb2312 != null ) && (gb2312 != String.Empty))
{
gb2312 = gb2312.Trim();
big5 = Strings.StrConv(gb2312,VbStrConv.TraditionalChinese, 0 );
}
return big5;
}
}
}