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

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();

        }

    }

}