DB中的乱码==>修正

最新推荐文章于 2023-08-14 11:32:43 发布

吉普赛的歌

最新推荐文章于 2023-08-14 11:32:43 发布

阅读量2k

点赞数

分类专栏： C# 文章标签： string database insert cmd sql server

本文链接：https://blog.csdn.net/yenange/article/details/7209685

版权

C# 专栏收录该内容

138 篇文章 2 订阅

订阅专栏

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data;  

namespace ConsoleApplication2
{
    class Program
    {
        static string connectionString = "server=192.168.148.80;uid=ecuser;pwd=1234;database=ec15_20120117_liu;pooling=true; Min Pool Size=5; Max Pool Size=100";
        static void Main(string[] args)
        {
            string sql = "SELECT PatientID,ForeignName,ForeignName_New FROM Patient_YNG ORDER BY PatientID";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter adapter =new SqlDataAdapter(cmd);
            DataTable dt=new DataTable();
            adapter.Fill(dt);
            adapter.UpdateCommand=new SqlCommandBuilder(adapter).GetUpdateCommand();
            
            foreach (DataRow dr in dt.Rows)
            {
                dr["ForeignName_New"]=ISO8859_GB2312(dr["ForeignName"].ToString());
            }
            adapter.Update(dt);
            con.Close();
            Console.ReadLine();
        }
        //读出时进行转换  
        static string ISO8859_GB2312(string read)
        {
            //声明字符集  
            System.Text.Encoding iso8859, gb2312;
            string[] enArr = { "iso8859-1", "gb2312", "utf-8", "big5", };
            //iso8859  
            iso8859 = System.Text.Encoding.GetEncoding(enArr[0]);
            //国标2312  
            //gb2312 = System.Text.Encoding.GetEncoding(enArr[2]);  
            byte[] iso;
            iso = iso8859.GetBytes(read);
            //返回转换后的字符  
            string s = new String(System.Text.Encoding.Default.GetChars(iso));
            //s = System.Text.Encoding.UTF8.GetString(read);  
            return s;
            //return gb2312.GetString(iso);  
        }
    }
}

INSERT INTO Patient_YNG (PatientID,ForeignName)
SELECT PatientID,
       ForeignName
FROM   Patient
WHERE  clinicid = 1483
       AND ForeignName IS NOT NULL
       AND LEN(ForeignName) != 0
ORDER BY
       PatientID
       
UPDATE patient_yng 
SET PatientFirstName=
(SELECT PatientFirstName FROM patient WHERE patient.PatientID=patient_yng.PatientID )
,PatientlastName=
(SELECT PatientlastName FROM patient WHERE patient.PatientID=patient_yng.PatientID )

SELECT * FROM patient_yng

--修正后的还有的乱码判断
SELECT * FROM Patient_YNG
WHERE ForeignName_New LIKE '%?%' 
or ForeignName_New LIKE '%#%'
OR ForeignName_New LIKE '%''%'
OR ForeignName_New LIKE '%"%'
OR ForeignName_New LIKE '%&%'
OR ForeignName_New LIKE '%a%'
OR ForeignName_New LIKE '%b%'
OR ForeignName_New LIKE '%c%'
OR ForeignName_New LIKE '%d%'
OR ForeignName_New LIKE '%e%'
OR ForeignName_New LIKE '%f%'
OR ForeignName_New LIKE '%g%'
OR ForeignName_New LIKE '%h%'
OR ForeignName_New LIKE '%i%'
OR ForeignName_New LIKE '%j%'
OR ForeignName_New LIKE '%k%'
OR ForeignName_New LIKE '%l%'
OR ForeignName_New LIKE '%m%'
OR ForeignName_New LIKE '%n%'
OR ForeignName_New LIKE '%o%'
OR ForeignName_New LIKE '%p%'
OR ForeignName_New LIKE '%q%'
OR ForeignName_New LIKE '%r%'
OR ForeignName_New LIKE '%s%'
OR ForeignName_New LIKE '%t%'
OR ForeignName_New LIKE '%u%'
OR ForeignName_New LIKE '%v%'
OR ForeignName_New LIKE '%w%'
OR ForeignName_New LIKE '%x%'
OR ForeignName_New LIKE '%y%'
OR ForeignName_New LIKE '%z%'