c#查Oracle视图出现中文乱码问题

c#连接Oracle数据库查视图出现中文乱码问题（已解决）

前言：大家都知道乱码问题，一般都是字符的编码格式不一致导致的。然而我发现Oracle连接字符串中又没有设置编码格式的属性。

通过各种翻阅文档，发现Oracle中有处理编码格式导致中文乱码的函数。代码如下：

     try
     {
            string sql = $"select hisid,brid,sqh,staccnum,utl_raw.cast_to_raw(name) as name,utl_raw.cast_to_raw(sex) as sex,utl_raw.cast_to_raw(age) as age,utl_raw.cast_to_raw(locname) as locname,printed,reporturl from sa.blzzdy";
   			DataTable dt = Shared.CallProcedure(null, sql);         
			List<TestObject> objs = new List<TestObject>();
            foreach (DataRow dr in dt.Rows)
            {
                objs.Add(new TestObject
                {
                    HISID = dr["hisid"].ToString(),
                    BRID = dr["brid"].ToString(),
                    SQH = dr["sqh"].ToString(),
                    STACCNUM = dr["staccnum"].ToString(),
                    PRINTED = dr["printed"].ToString(),
                    REPORTURL = dr["reporturl"].ToString(),
                    NAME = dr["name"] is DBNull ? "" : Encoding.Default.GetString((byte[])dr["name"]),
                    SEX = dr["sex"] is DBNull ? "" : Encoding.Default.GetString((byte[])dr["sex"]),
                    AGE = dr["age"] is DBNull ? "" : Encoding.Default.GetString((byte[])dr["age"]),
                    LOCNAME = dr["locname"] is DBNull ? "" : Encoding.Default.GetString((byte[])dr["locname"])
                });
            }

            dt.Clear();


            Console.WriteLine("获取打印信息出参：" + JsonConvert.SerializeObject(objs));

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

 public class TestObject
    {
        public string HISID { get; set; }
        public string BRID { get; set; }
        public string SQH { get; set; }
        public string STACCNUM { get; set; }
        public string NAME { get; set; }
        public string SEX { get; set; }
        public string AGE { get; set; }
        public string LOCNAME { get; set; }
        public string PRINTED { get; set; }
        public string REPORTURL { get; set; }
    }

 /// 封装了调用存储过程、视图的帮助类
public class Shared
{ 
	public static readonly string connectionString =ConfigurationManager.AppSettings.Get("conString");
    /// <summary>
    /// 查询视图、调用存储过程
    /// </summary>
    /// <param name="proName">存储过程名</param>
    /// <param name="sql">Sql</param>
    /// <param name="paraValues">输入输出参数</param>
    public static DataTable CallProcedure(string proName, string sql, OracleParameter[] paraValues = null)
    {
        DataTable dt = new DataTable();
        using (var conn = CreateOracleConnection())
        {
            OracleCommand cmd = conn.CreateCommand();
            if (string.IsNullOrEmpty(sql))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = proName;
                if (paraValues != null)
                {
                    //添加参数
                    cmd.Parameters.AddRange(paraValues);
                }
            }
            else
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
            }

            using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
            {
                adapter.Fill(dt);
            }
            return dt;
        }
    }

    /// <summary>
    /// 创建Oracle连接对象
    /// </summary>
    public static OracleConnection CreateOracleConnection()
    {           
         var connection = new OracleConnection(connectionString);
        connection.Open();
        return connection;
    }
}

//下面的代码是如何调用存储过程

    /// <summary>
    /// 当日挂号
    /// </summary>
    /// <param name="request"></param>
    /// <returns></returns>
    public string AddReg(REQ_AddReg request)
    {
       //定义存储过程的参数数组
        OracleParameter[] paraValues ={
                                        new OracleParameter("HID", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("ID_NO", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("PATIENT_NAME", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("PATIENT_ID", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("DEPT_CODE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("DEPT_NAME", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("DOCTOR_CODE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("DOCTOR_NAME", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("REG_DATE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("HB_TIME", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("CLINIC_DURATION", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("TRADE_ORDER_NO", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("DIAG_FEE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("PHARMACY_FEE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("FREE_TREAT_FLAG", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("PAY_TYPE", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("HIS_OPERATOR", OracleDbType.Varchar2, 50,null,ParameterDirection.Input),
                                        new OracleParameter("RESULT_CODE", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("ERROR_MSG", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("HIS_ORDER_NO", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("VERIFY_CODE", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("CLINIC_NO", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("VISIT_NO", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                        new OracleParameter("SER_NO", OracleDbType.Varchar2, 50, null, ParameterDirection.Output),
                                     };
		paraValues[0].Value = request.reg_id;//号源
        paraValues[1].Value = request.card_id;//身份证号
        paraValues[2].Value = request.name;  //姓名
        paraValues[3].Value = request.patient_id; //病人id
        paraValues[4].Value = request.dept_no; //科室编号
        paraValues[5].Value = request.dept_name;  //科室名称
        paraValues[6].Value = request.doc_no; //医生编号
        paraValues[7].Value = request.doc_name; //医生名称
        paraValues[8].Value = request.date;//预约日期
        paraValues[9].Value = request.time_quantum;//号源时段
        paraValues[10].Value = request.work_dtime;  //号源午别
        paraValues[11].Value = request.serial_no; //建融智医订单号         
		paraValues[12].Value = request.reg_fee; //诊疗费
        paraValues[13].Value = request.expert_fee;  //药事费
        paraValues[14].Value = request.is_expert; //挂号费别
        paraValues[15].Value = request.pay_info; //支付方式
        paraValues[16].Value = request.user_no; //HIS操作员
        LogHelper.Instance.Debug("AddReg获取挂号信息入参：" + Newtonsoft.Json.JsonConvert.SerializeObject(request));
        DataTable dt = Shared.CallProcedure("PROC_APP_REGIST", null, paraValues);

        RESP_AddReg dbUser = new RESP_AddReg();


        dbUser.visit_no= request.tel_no; //号源id
        dbUser.dept_no = request.dept_no;
        dbUser.dept_name = request.dept_name;
        dbUser.doc_no = request.doc_no;
        dbUser.date = request.date;
        dbUser.HB_TIME = request.time_quantum;
        dbUser.CLINIC_DURATION = request.work_dtime;
        dbUser.TRADE_ORDER_NO = request.serial_no;
        dbUser.reg_fee = request.reg_fee;
        dbUser.PHARMACY_FEE = request.expert_fee;  //药事费
        dbUser.FREE_TREAT_FLAG= request.is_expert; //挂号费别
        dbUser.PAY_TYPE = request.pay_info; //支付方式
        dbUser.RESULT_CODE = paraValues[17].Value.ToString();//交易结果代码 
        dbUser.ERROR_MSG = paraValues[18].Value.ToString();  //交易结果信息
        dbUser.reg_no = paraValues[19].Value.ToString();     //HIS交易流水号 
        dbUser.VERIFY_CO = paraValues[20].Value.ToString();  //取号验证码
        dbUser.CLINIC_NO = paraValues[21].Value.ToString();  //门诊号            
        dbUser.visit_no = paraValues[22].Value.ToString();   //就诊序号
        dbUser.SER_NO = paraValues[23].Value.ToString();     //发票号
        LogHelper.Instance.Debug("AddReg获取挂号信息出参：" + Newtonsoft.Json.JsonConvert.SerializeObject(dbUser));
        dt.Clear();

        string str = Shared.GetDefalutJSON("0", dbUser.ERROR_MSG, dbUser);

        if ("0000".Equals(dbUser.RESULT_CODE))
            return Shared.GetDefalutJSON("0", dbUser.ERROR_MSG, dbUser);
        else
            return Shared.GetDefalutJSON("-1", "挂号失败："+dbUser.ERROR_MSG);
    }