sql server 数据库简繁转换

转换要求：

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 [其他判断条件]