转换要求:
1、数据库表必须有主键
2、表之间有外键关系会报错
3、VS手动添加对Microsoft.VisualBasic的引用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using System.Data.OleDb;
namespace ConsoleApp1
{
class Program
{
string constr = "Data Source=.;Initial Catalog=KyjhrMis91_backup;Integrated Security=True"
static void Main(string[] args)
{
//var str=selectJson(sql);
//updateTable(ConvTable(str),sql);
//var str=JsonConvert.SerializeObject(selectTable(sql));
selectTable("TrnRec");
Console.Read();
}
/// <summary>
/// 查询表并且转换结构并进行繁体转简体
/// </summary>
/// <param name="sql"></param>
/// <param name="constr"></param>
/// <returns></returns>
static DataTable selectTable(string TableName)
{
string sql = "SELECT * FROM " + TableName;
SqlDataAdapter sda = new SqlDataAdapter(sql, constr);
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
DataTable dt = new DataTable();
sda.Fill(dt);
try
{
//Console.WriteLine(ConvTable(dt).Rows.Count);
//DataTable dt2 = ConvTable(dt);
DataTable dt2 = dt.Copy();
setColType(dt, TableName);//转换字段结构
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 1; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].GetType() == "".GetType())
{
string str = StrConv(dt2.Rows[i][j].ToString());
dt.Rows[i][j] = str;
}
}
Console.WriteLine("已更新第{0}行数据", i + 1);
}
int num = sda.Update(dt);
Console.WriteLine("更新了{0}行数据", num);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return dt;
}
/// <summary>
/// 转换表字段排序规则为简体
/// </summary>
/// <param name="dt"></param>
static void setColType(DataTable dt, string TableName)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].DataType == "".GetType())
{
string name = dt.Columns[i].ColumnName;
string sqlstr = "declare @len int,@name nvarchar(20),@sqlstr nvarchar(1000);set @name='" + name + "';SELECT @len=length FROM SYSCOLUMNS where name=@name and ID=OBJECT_ID('" + TableName + "');set @sqlstr='ALTER TABLE " + TableName + " ALTER COLUMN '+@name +' varchar('+CONVERT(NVARCHAR(10), @len)+') COLLATE Chinese_PRC_CI_AS';exec(@sqlstr);";
exec(sqlstr);
}
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql"></param>
/// <param name="constr"></param>
static void exec(string sql)
{
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
}
}
#region IString 成员
/// <summary>
/// 简繁转换
/// </summary>
/// <param name="x"></param>
/// <param name="type"></param>
/// <returns></returns>
public static string StrConv(string x, string type = "简体")
{
String value = String.Empty;
switch (type)
{
case "繁体"://转繁体
value = Microsoft.VisualBasic.Strings.StrConv(x, Microsoft.VisualBasic.VbStrConv.TraditionalChinese, 0);
break;
case "简体":
value = Microsoft.VisualBasic.Strings.StrConv(x, Microsoft.VisualBasic.VbStrConv.SimplifiedChinese, 0);
break;
default:
break;
}
return value;
}
#endregion
}
}
可能用到的SQL语句
1、用于设置表主键
declare @tabNAme nvarchar(20),@sqlstr1 nvarchar(1000),@id nvarchar(20)
set @tabName='EmpChg'--你的表名字
set @id='rid'--你要设置的主键名字
set @sqlstr1 ='ALTER TABLE '+@tabName+' ADD CONSTRAINT PK_EmployeesID PRIMARY KEY ('++');'+'SELECT * FROM '+@tabName
exec(@sqlstr1)
2、查询重复数据以及删除重复数据
select [id] from [TableName] group by [id] having count(*)>1
delete [TableName] where [id] in (select [id] from [TableName] group by [id] having count(*)>1) and [其他判断条件]