数据|数据库|转换
/*
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;
}
}
}