起因

昨天在項目維護中發(fā)現(xiàn)C#調(diào)用存儲過程時sql輸出參數(shù)數(shù)據(jù)類型為numeric(18,4)，C#對應Decimal類型，通過SQL Server Profiler 工具跟蹤發(fā)現(xiàn)ado.net默認生成的為numeric(29,0)，小數(shù)進度被省略咯，下面上代碼。

Paste_Image.png

sql存儲過程

create proc Test_output  
(  
@parameter1 numeric(18,4),  
@outputPara numeric(18,4) output  
)  
as  
  
set @outputPara=@parameter1  
select  @outputPara 'output'

C#代碼(為修改之前)

            decimal value = 123.4567M;
            decimal outputValue = 0M;
            List<object> arrList = new List<object>();
            using (SqlConnection Conn = new SqlConnection('數(shù)據(jù)庫連接字符串'))
            {
                Conn.Open();
                using (SqlCommand Cmd = Conn.CreateCommand())
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                    Cmd.CommandText = "Test_output";
                    SqlParameter para1 = new SqlParameter();
                    para1.SqlDbType = SqlDbType.Decimal;
                    para1.ParameterName = "@parameter1";
                    para1.Value = value;
                    para1.Direction = ParameterDirection.Input;

                    SqlParameter para2 = new SqlParameter();
                    para2.SqlDbType = SqlDbType.Decimal;
                    para2.ParameterName = "@outputPara";
                    para2.Value = outputValue;
                    para2.Direction = ParameterDirection.Output;

                    Cmd.Parameters.AddRange(new SqlParameter[] { para1, para2 });
                    Cmd.ExecuteNonQuery();
                    for (int i = 0; i < Cmd.Parameters.Count; i++)
                    {
                        if (Cmd.Parameters[i].Direction == ParameterDirection.Output)
                        {
                            arrList.Add(Cmd.Parameters[i].Value);
                        }
                    }
                }
            }

執(zhí)行結(jié)果：123M,小數(shù)被省略掉咯

Paste_Image.png

sql

declare @p2 numeric(29,0)
set @p2=123
exec Test_output @parameter1=123.4567,@outputPara=@p2 output
select @p2

Paste_Image.png

通過SQL Server Profiler 工具跟蹤到的sql（上面代碼），發(fā)現(xiàn)輸出參數(shù)的數(shù)據(jù)類型為numeric(29,0)，說明C#代碼中傳遞sqlparameter未指定參數(shù)小數(shù)位數(shù)。

修改后的代碼

            decimal value = 123.4567M;
            decimal outputValue = 0M;
            List<object> arrList = new List<object>();
            using (SqlConnection Conn = new SqlConnection(SPHelper.connectionString))
            {
                Conn.Open();
                using (SqlCommand Cmd = Conn.CreateCommand())
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                    Cmd.CommandText = "Test_output";
                    SqlParameter para1 = new SqlParameter();
                    para1.SqlDbType = SqlDbType.Decimal;
                    para1.ParameterName = "@parameter1";
                    para1.Value = value;
                    para1.Direction = ParameterDirection.Input;
                    para1.Precision = 18;//數(shù)據(jù)類型長度numeric(18,4)
                    para1.Scale = 4;
                    SqlParameter para2 = new SqlParameter();
                    para2.SqlDbType = SqlDbType.Decimal;
                    para2.ParameterName = "@outputPara";
                    para2.Value = outputValue;
                    para2.Direction = ParameterDirection.Output;
                    para2.Precision = 18;//數(shù)據(jù)類型長度numeric(18,4)
                    para2.Scale = 4;//小數(shù)位數(shù)
                    Cmd.Parameters.AddRange(new SqlParameter[] { para1, para2 });
                    Cmd.ExecuteNonQuery();
                    for (int i = 0; i < Cmd.Parameters.Count; i++)
                    {
                        if (Cmd.Parameters[i].Direction == ParameterDirection.Output)
                        {
                            arrList.Add(Cmd.Parameters[i].Value);
                        }
                    }
                }
            }

Paste_Image.png

執(zhí)行結(jié)果：123.4567M
參考：msdn