C#调用MS SQL存储过程时输出参数小数被舍掉问题

起因

昨天在项目维护中发现C#调用存储过程时sql输出参数数据类型为numeric(18,4)，C#对应Decimal类型，通过SQL Server Profiler 工具跟踪发现ado.net默认生成的为numeric(29,0)，小数进度被省略咯，下面上代码。

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('数据库连接字符串'))
            {
                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);
                        }
                    }
                }
            }

执行结果：123M,小数被省略掉咯

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（上面代码），发现输出参数的数据类型为numeric(29,0)，说明C#代码中传递sqlparameter未指定参数小数位数。

修改后的代码

            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;//数据类型长度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;//数据类型长度numeric(18,4)
                    para2.Scale = 4;//小数位数
                    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

执行结果：123.4567M
参考：msdn