数据库简繁转换

川长思鸟来 2022-06-03 03:26 186阅读 0赞

转换要求：

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