C#获取存储过程返回值和输出参数值的方法

小咪咪 2021-09-29 11:14 442阅读 0赞

//转自网络,先留个底

1.获取Return返回值

![复制代码][copycode.gif]

//存储过程
    //Create PROCEDURE MYSQL
    //     @a int,
    //     @b int
    //AS
    //     return @a + @b
    //GO
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
    conn.Open();
    SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
    MyCommand.CommandType = CommandType.StoredProcedure;
    MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
    MyCommand.Parameters["@a"].Value = 10;
    MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
    MyCommand.Parameters["@b"].Value = 20;
    MyCommand.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
    MyCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
    MyCommand.ExecuteNonQuery();
    Response.Write(MyCommand.Parameters["@return"].Value.ToString());

![复制代码][copycode.gif]

2.获取Output输出参数值

![复制代码][copycode.gif]

//存储过程
    //Create PROCEDURE MYSQL
    //     @a int,
    //     @b int,
    //     @c int output
    //AS
    //     Set @c = @a + @b
    //GO
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
    conn.Open();
    SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
    MyCommand.CommandType = CommandType.StoredProcedure;
    MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
    MyCommand.Parameters["@a"].Value = 20;
    MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
    MyCommand.Parameters["@b"].Value = 20;
    MyCommand.Parameters.Add(new SqlParameter("@c", SqlDbType.Int));
    MyCommand.Parameters["@c"].Direction = ParameterDirection.Output;
    MyCommand.ExecuteNonQuery();
    Response.Write(MyCommand.Parameters["@c"].Value.ToString());

![复制代码][copycode.gif]

C\#接收存储过程返回值:

![复制代码][copycode.gif]

public static int User_Add(User us)
         {
             int iRet;
             SqlConnection conn = new SqlConnection(Conn_Str);
             SqlCommand cmd = new SqlCommand("User_Add", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.AddWithValue("@UName", us.UName);
             cmd.Parameters.AddWithValue("@UPass", us.UPass);
             cmd.Parameters.AddWithValue("@PassQuestion", us.PassQuestion);
             cmd.Parameters.AddWithValue("@PassKey", us.PassKey);
             cmd.Parameters.AddWithValue("@Email", us.Email);
             cmd.Parameters.AddWithValue("@RName", us.RName);
             cmd.Parameters.AddWithValue("@Area", us.Area);
             cmd.Parameters.AddWithValue("@Address", us.Address);
             cmd.Parameters.AddWithValue("@ZipCodes", us.ZipCodes);
             cmd.Parameters.AddWithValue("@Phone", us.Phone);
             cmd.Parameters.AddWithValue("@QQ", us.QQ);
             cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;       
             try
             {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
             }
             catch (SqlException ex)
             {
                 throw ex;
             }
             finally
             {
                 conn.Close();
             }
             return iRet;
         }

![复制代码][copycode.gif]

C\#接收存储过程输出参数:

![复制代码][copycode.gif]

public static decimal Cart_UserAmount(int UID)
        {
            decimal iRet;
            SqlConnection conn = new SqlConnection(Conn_Str);
            SqlCommand cmd = new SqlCommand("Cart_UserAmount", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UID", UID);
            cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Direction=ParameterDirection.Output;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                iRet = (decimal)cmd.Parameters["@Amount"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return iRet;
        }

![复制代码][copycode.gif]

C\# 中调用 MySQL 存储过程并传递参数和获取返回值

![复制代码][copycode.gif]

CallableStatement PreparedFunc = MyConnect.prepareCall("{ ? = call isodd( ? ) }");
    PreparedFunc.registerOutParameter(1, Types.INTEGER);
    
    PreparedFunc.setInt(1, aNumber);
    PreparedFunc.execute();
    
    if (PreparedFunc.getInt(1) == 1)
        System.out.println(aNumber +"is odd");
    else
        System.out.println(aNumber +"is even");

![复制代码][copycode.gif]

C\#操作Mysql数据库的存储过程

![复制代码][copycode.gif]

//MySql过程
    //CREATE PROCEDURE JugePasswordCorrect(in username VARCHAR(64), in pwd VARCHAR(32))
    //BEGIN
    //SELECT COUNT(*) FROM acctable WHERE account=username AND password=pwd;
    //END;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using MySql.Data.MySqlClient;
    
    namespace TestMysql2
    {
        class Program
        {
            static void Main(string[] args)
            {
                MySqlConnection conn = new MySqlConnection("Database=test;Data Source=localhost;User Id='test';Password='test';pooling=true;Minimum Pool Size=5;Maximum Pool Size=10;CharSet=utf8;port=3306;Connect Timeout=3600");
                conn.Open();
    
                MySqlCommand cmd = new MySqlCommand("JugePasswordCorrect", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add("?username", MySqlDbType.VarChar, 64);
                cmd.Parameters["?username"].Value = "test";
                cmd.Parameters.Add("?pwd", MySqlDbType.VarChar, 32);
                cmd.Parameters["?pwd"].Value = "test";
                int rows = Convert.ToInt32(cmd.ExecuteScalar());
                if (rows > 0)
                {
                    // 密码正确
                }
                conn.Close();
            }
        }
    }

![复制代码][copycode.gif]

其他写法:

![复制代码][copycode.gif]

myco = new MySqlConnection(strconn);
        myco.Open();
        mycomm = new MySqlCommand("GetRecordCount", myco);
        mycomm.CommandType = CommandType.StoredProcedure;
    
        MySqlParameter[] para = new MySqlParameter[]{
            new MySqlParameter("@tbName",MySqlDbType.VarChar,20),
            new MySqlParameter("@strWhere",MySqlDbType.VarChar,500)};
    
        para[0].Value = "uinfo";
        para[1].Value = strWhere;
        mycomm.Parameters.AddRange(para);
        MySqlDataReader mydr=mycomm.ExecuteReader();
        while(mydr.Read()){
            pageCount = System.Convert.ToInt32(mydr["countStr"].ToString()) ;
            pageCountMod = pageCount % pageSize;
            pageCount= pageCount/ pageSize;
            if (pageCount == 0)
            {
                //pageCount++;
            }
            else
            {
                if (pageCountMod != 0)
                {
                    pageCount++;
                }
            }
            pageCountLab.Text = pageCount.ToString();
        }

转自：[https://www.cnblogs.com/xiii/p/5162038.html][https_www.cnblogs.com_xiii_p_5162038.html]

[copycode.gif]: /images/20210720/36d0d2108a544ebe97e1005b6b19550a.png
[https_www.cnblogs.com_xiii_p_5162038.html]: https://www.cnblogs.com/xiii/p/5162038.html