最近做一個工廠的自動化項目使用到sqlserver,查詢其中某個值的時候傳回的是空值,研究了一下發現該值的類型是nchar,定長unicode編碼,網上查了下
nchar
和
nvarchar
的差別,nvarchar類型存儲位元組數為字長實際長度兩倍(unicode編碼),進一步通過程式驗證發現nchar(l)存儲時按開辟空間的長度進行存儲的(也是unicode編碼,位元組數為2*l),餘位會補空,讀取到C#中該值長度是
l
而不是實際長度。以下是測試代碼:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
string connectionString = "server=;database=;user=;password=";
using (SqlConnection con = new SqlConnection(connectionString))
{
try
{
con.Open();
}
catch (SqlException e)
{
throw e;
}
if (con.State == ConnectionState.Open)
{
string insertCmd = $"insert into InvoiceCardState(ChrBoxUp,ChrLot) values('KS','KS')";
int rows=ExecuteSql(insertCmd, con);
DataTable dt = new DataTable();
if(rows>0)
{
string queryCmd = $"select * from Test where ChrBoxUp='KS'";
QuerySql(queryCmd, con, dt);
if(dt.Rows.Count>0)
{
string lot = dt.Rows[0]["ChrLot"].ToString();
string pb = dt.Rows[0]["ChrBoxUp"].ToString();
int lotl = lot.Length;
int pbl = pb.Length;
Console.WriteLine("lot length:" + lotl.ToString());
Console.WriteLine("pb length:" + pbl.ToString());
}
}
}
}
}
static int ExecuteSql(string sqlcmd, SqlConnection con)
{
int rows = 0;
SqlCommand cmd = new SqlCommand(sqlcmd, con);
try
{
rows = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
throw e;
}
return rows;
}
static int QuerySql(string sqlcmd, SqlConnection con, DataTable dt)
{
int errorCode = 0;
SqlCommand cmd = new SqlCommand(sqlcmd, con);
using (SqlDataAdapter adpter = new SqlDataAdapter(cmd))
{
try
{
adpter.Fill(dt);
}
catch (SqlException e)
{
errorCode = e.ErrorCode;
}
}
return errorCode;
}
}
}
其中Test表中
ChrBoxUp
為
nvarchar(12)
,
ChrLot
類型為nchar(12),均存入字段
KS
,然後讀取,結果ChrLot長度度為12,ChrBoxUp長度為2,加斷點監視
string lot
的值
KS
右端存在一部分空格,并且如果将查詢條件改為
where ChrLot='KS'
,查詢結果為空,如果需要查詢可以用
where rtrim(ChrLot)='KS'
進行查詢。