データベースの簡単な変換

変換要件:
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();
        }
        /// 

        ///                  
        /// 
        /// 
        /// 
        /// 
        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;
        }
        /// 

        ///             
        /// 
        /// 
        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);
                }
            }
        }
        /// 

        ///   sql  
        /// 
        /// 
        /// 
        static void exec(string sql)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.ExecuteNonQuery();
            }
        }

        #region IString   
        /// 

        ///     
        /// 
        /// 
        /// 
        /// 
        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)

、重複データの問合せおよび重複データの削除

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 [      ]