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

//转自网络,先留个底

1.获取Return返回值

//存储过程//Create PROCEDURE MYSQL//@a int,//@b int//AS//return @a + @b//GOSqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());conn.Open();SqlCommand MyCommand = new SqlCommand("MYSQL", conn);mandType = 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());

2.获取Output输出参数值

//存储过程//Create PROCEDURE MYSQL//@a int,//@b int,//@c int output//AS//Set @c = @a + @b//GOSqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());conn.Open();SqlCommand MyCommand = new SqlCommand("MYSQL", conn);mandType = 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());

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

public static int User_Add(User us){int iRet;SqlConnection conn = new SqlConnection(Conn_Str);SqlCommand cmd = new SqlCommand("User_Add", conn);mandType = 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;}

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

public static decimal Cart_UserAmount(int UID){decimal iRet;SqlConnection conn = new SqlConnection(Conn_Str);SqlCommand cmd = new SqlCommand("Cart_UserAmount", conn);mandType = 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;}

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

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");elseSystem.out.println(aNumber +"is even");

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

//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);mandType = mandType.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();}}}

其他写法:

myco = new MySqlConnection(strconn);myco.Open();mycomm = new MySqlCommand("GetRecordCount", myco);mandType = 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();}

/*其他:

参数有@开头的,也有?开头的.

MySqlDataAdapter.fill 返回集