c# 调用 postgresql 存储过程

最近使用C# 开发管理系统，其间使用存储过程来更新数据，发现其特有的语法特点：SQL直接用存储过程名 ；

如：sql = "clientmatch";
clientmatch就是存储过程名

因为在Navicat中的语法是：select clientmatch('yjlx','10736714',''),想当然的以为在C#中也是这样，谁知老是报语法错误。

经过试错，发现语句中的SQL直接写存储过程名（函数名）即可，其它的一律不用带。（一定不能带括号和参数，参数要另外声明）

 

//使用存储过程，更新下游客户号
            sql = "clientmatch";
            NpgsqlParameter[] sqlPara = new NpgsqlParameter[3];
            //pgsql 的参数与SQL SERVER 的格式不一样
            sqlPara[0] = new NpgsqlParameter("@tmpfilename", NpgsqlTypes.NpgsqlDbType.Text, 30);
            sqlPara[0].Value = tmpTableName;
            sqlPara[1] = new NpgsqlParameter("@supplierid", NpgsqlTypes.NpgsqlDbType.Text, 10);
            sqlPara[1].Value = supplierNumber;
            sqlPara[2] = new NpgsqlParameter("@clientname", NpgsqlTypes.NpgsqlDbType.Text, 100);
            sqlPara[2].Value = "";
　　　　　　　DBHelperPg.ExecuteStoreProcedure(sql, sqlPara);

 

附1：DBHelperPg.ExecuteStoreProcedure

public static int ExecuteStoreProcedure(string sql, params NpgsqlParameter[] parameters)
        {
            int num2 = -1;
            using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
            {
                using (NpgsqlCommand command = connection.CreateCommand())
                {

                    command.CommandText = sql;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(parameters);
                    try
                    {
                        connection.Open();
                        num2 = command.ExecuteNonQuery();
                    }
                    catch (NpgsqlException exception)
                    {
                        //throw new Exception(exception.Message);
                        MessageBox.Show(exception.Message + "\n 错误SQL：" + sql);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return num2;
        }

 

 

调用的存储过程

 

CREATE OR REPLACE FUNCTION "public"."clientmatch"("tmpfilename" text, "supplierid" text, "clientname" text)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare 
    mysql  text ;
        mycolumn text ;
        filename text;
        supplier text;
        clientinfo text ;
        strwhere text; 

begin 
    --需要新建的字段名
    filename = tmpfilename;
    supplier = supplierid
    clientinfo = clientname;
  mycolumn = '';
    ----------------------------------------------

    if supplier !='' then
        strwhere = ' and yjlx_gysbh ='||supplier|| ;
    end if;

    if clientinfo !='' then
        strwhere = strwhere || ' and yjlx_khmc ='''||clientinfo|| '''';
    end if;
    

    mysql = ' update "+ filename+" set yjlx_khbh =b.clientid from client_alias as b ';
    mysql = mysql || ' where yjlx_gysbh=b.supplierid and     yjlx_khmc=b.clientalias ';
    mysql = mysql ||strwhere;
            RAISE NOTICE 'SQL语句1为： %', mysql;
    
      execute mysql  ;

    mysql = ' update ' ||filename || ' set yjlx_khbh =b.id from client as b  where yjlx_khbh is null and yjlx_khmc=b.name ';
        mysql = mysql ||strwhere;
            RAISE NOTICE 'SQL语句2为： %', mysql;
      execute mysql  ;

  
    return 0;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100