如何从C＃传递可选参数到sql函数 [英] how to pass an optional parameter to sql function from C#

问题描述

嗨..

我有一个问题。请帮忙解决我的问题



我有一个sql函数

Hi..
I have an issue. please help to solve my problem

I have an sql function

function [dbo].[fnKudishikaAmt](@ParishName nvarchar(100),@Hno int,@dateto datetime=Null)
Returns Decimal(15,2)

sql函数使用execute命令显示正确的结果

the sql function shows proper result by using the execute command

Select dbo.fnKudishikaAmt('St.George Malankara Catholic Church',29,default)

我的要求是应该从c调用此函数

i我收到错误从字符串转换datetime时转换失败

My requirement is this function should be called from c#
i am getting the error Conversion failed when converting datetime from character string

public double kudishikatotal(string ParishName, Int32 HouseNo)
    {
        String SQLText = "select ChurchDB.dbo.fnKudishikaAmt(@ParishName,@Hno,@dateto) as fnresult";
        SqlCommand cmd = new SqlCommand(SQLText);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@ParishName", ParishName);
        cmd.Parameters.AddWithValue("@Hno", HouseNo);
        cmd.Parameters.AddWithValue("@dateto", "default");
        string rval = GetSingleValue(cmd);
        double kudiamt = 0;
        if (rval != null)
        {
            kudiamt = Convert.ToDouble(rval);
        }
        return kudiamt;
        
    }

private static string GetSingleValue(SqlCommand cmd)
    {
        string ConString = connectionstring();
        string returnvalue = "";
        using (SqlConnection con = new SqlConnection(ConString))
        {
            cmd.Connection = con;
            con.Open();
            returnvalue = cmd.ExecuteScalar().ToString();
            con.Close();

        }
        return returnvalue;
    }

推荐答案

用DBNull.Value替换default（没有qoutes !!）

Replace "default" with DBNull.Value (no qoutes!!)

这篇关于如何从C＃传递可选参数到sql函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！