C#与SQL Server存储过程之二（调用）：使用C#调用SQL Server的存储过程

来源：互联网发布：我国电力系统分析软件编辑：程序博客网时间：2024/05/16 15:22

Form上有一个button按钮，name：buttonProcedureClient。

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Procedure_Client

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void buttonProcedureClient_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection("Data Source=liuxueqin;Initial Catalog=北风贸易;Integrated Security=True");

con.Open();

//调用procedure 1----------

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "ChangeCityName";

SqlParameter param1 = new SqlParameter("@City", SqlDbType.NVarChar, 15);

SqlParameter param2 = new SqlParameter("@ID", SqlDbType.Int);

cmd.Parameters.Add(param1);

cmd.Parameters.Add(param2);

cmd.Parameters[0].Value = "武汉市洪山区";

cmd.Parameters[1].Value = 10248;

cmd.ExecuteNonQuery();

//调用procedure 2.1----------

cmd.CommandText = "GetAllInfo";

cmd.Parameters.Clear();

SqlDataReader reader = cmd.ExecuteReader();

int i = 0;

while (reader.Read() && i < 5)

{

object obj1 = reader[0];

object obj2 = reader[1];

MessageBox.Show(obj1.ToString() + " " + obj2.ToString(), "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

i++;

}

reader.Close();

//调用procedure 2.2----------

cmd.CommandText = "GetAllInfoByID";

//SqlParameter param3 = new SqlParameter("@ID", SqlDbType.Int); //a1

SqlParameter param3 = new SqlParameter("@ID", 10249); //b

cmd.Parameters.Clear();

cmd.Parameters.Add(param3);

//cmd.Parameters[0].Value = 10249; //a2

//以上a，b两种方式都可行

//两种调用方式1.SqlCommand和SqlDataReader 2.SqlDataAdapter和DataSet

//reader = cmd.ExecuteReader();

//while (reader.Read())

//{

// int col = reader.FieldCount;

// object obj1 = reader[0];

// object obj2 = reader[1];

// MessageBox.Show(obj1.ToString() + " " + obj2.ToString(), "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

//}

//reader.Close();

SqlDataAdapter dataadapter = new SqlDataAdapter(cmd);

DataSet dataset = new DataSet();

dataadapter.Fill(dataset);

foreach (DataRow datarow in dataset.Tables[0].Rows)

{

for (int col = 0; col < dataset.Tables[0].Columns.Count; col++ )

{

object obj = datarow[col];

MessageBox.Show(obj.ToString() , "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

//调用procedure 3----------

cmd.CommandText = "GetCityName";

cmd.Parameters.Clear();

SqlParameter param4 = new SqlParameter("@ID",SqlDbType.Int);

SqlParameter param5 = new SqlParameter("@City", SqlDbType.NVarChar, 15);//传入参数可以不指定长度,直接给值,但传出的必须指定长度

cmd.Parameters.Add(param4);

param5.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param5);

cmd.Parameters["@ID"].Value = 10249;

cmd.ExecuteScalar();

string city = (cmd.Parameters["@City"].Value).ToString();

//调用procedure 4.1----------

cmd.CommandText = "GetRow";

cmd.Parameters.Clear();

SqlDataReader reader1 = cmd.ExecuteReader();

while (reader1.Read() )

{

object obj1 = reader1[0];

object obj2 = reader1[1];

MessageBox.Show(obj1.ToString() + " " + obj2.ToString(), "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

reader1.Close();

//调用procedure 4.2----------

cmd.CommandText = "GetMultipleRow";

cmd.Parameters.Clear();

SqlDataReader reader1 = cmd.ExecuteReader();

while (reader1.Read())

{

object obj1 = reader1[0];

object obj2 = reader1[1];

MessageBox.Show(obj1.ToString() + " " + obj2.ToString(), "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

reader1.Close();

}