C#中的如下两条语句调用存储过程output参数始终为空(dataGridView1能正确调用到想要的select数据,但是存储过程的两个输出参数值不能传递给两个texBox.Text),不知为何?
textBox1.Text = parameters[2].Value.ToString();
textBox2.Text = nowpage + "/" + parameters[3].Value.ToString();
存储过程代码:
USE [tushu]
GO
/****** Object: StoredProcedure [dbo].[fenye] Script Date: 12/05/2015 11:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[fenye]
@pagenow int,
@pagesize int,
@countrow int output,
@countpage int output
AS
IF(@pagesize=10)
BEGIN
declare @startRow int, @endRow int
set @startRow = (@pagenow - 1) * @pagesize +1
set @endRow = @startRow + @pagesize -1
select checkbox,id,shuming,zuozhe,xueduan,nianji,beizhu from book
where id between @startRow and @endRow
order by id desc
select @countrow=count(*) from book
SET @countpage=(@countrow/@pagesize)+1
END
C#代码片段:
private void chaxun_Click(object sender, EventArgs e)
{
//查询条件默认
xueduan.SelectedIndex = 0;
nianji.SelectedIndex = 0;
hangshu.SelectedIndex = 0;//每页行数,默认10条每页
//tiaopage.Text = "";
//数据统计
//int counthang = 0;
//int countpage = 0;
int nowpage = 1;
int sizepage = 10;
int tiaopage = int.Parse(tiaozhuan.Text.Trim());
string xueDuan = xueduan.Text;
string nianJi = nianji.Text;
string shuMing = shuming.Text.Trim();//自动剔除收尾空格
string condatabase = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
string sql = "exec fenye @pagenow,@pagesize,NULL,NULL";//@countrow,@countpage
SqlConnection conn = new SqlConnection(condatabase);
dataGridView1.Rows.Clear();
try
{
SqlCommand cmmd = new SqlCommand(sql, conn);//执行SQL查询
// 创建参数
IDataParameter[] parameters =
{
new SqlParameter("@pagenow", SqlDbType.Int) ,
new SqlParameter("@pagesize", SqlDbType.Int) ,
new SqlParameter("@countrow", SqlDbType.Int) ,
new SqlParameter("@countpage", SqlDbType.Int)
};
// 设置参数类型
parameters[0].Value = nowpage;
parameters[1].Value = sizepage; // 设置为输出参数
parameters[2].Direction = ParameterDirection.Output; //设置为输出参数
parameters[3].Direction = ParameterDirection.Output;
// 添加参数
cmmd.Parameters.Add(parameters[0]);
cmmd.Parameters.Add(parameters[1]);
cmmd.Parameters.Add(parameters[2]);
cmmd.Parameters.Add(parameters[3]);
conn.Open();
System.Data.DataTable dt = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmmd);
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
this.dataGridView1.Rows.Add(dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt.Rows[i][4], dt.Rows[i][5], dt.Rows[i][6]);
}
if (dataGridView1.RowCount != 0)
{
dataGridView1.Rows[0].Cells[0].Selected = false;//设置列表首行默认不选中
}
textBox1.Text = parameters[2].Value.ToString();
textBox2.Text = nowpage + "/" + parameters[3].Value.ToString();
}
catch (Exception yichang)
{
MessageBox.Show(yichang.Message);
}
finally
{
conn.Close();//关闭数据库连接
}
}