C#——调用存储过程方法

 

下面是我自己总结的一个比较简单的c#调用SQL Server 2005存储过程的小例子，主要是介绍整个过程，有问题的话请大虾们指导，谢谢~

 

作者：shinehoo

 

1）存储过程这样写的

ALTER PROCEDURE dbo.procShowLog

    (

    @StartTime datetime,

    @EndTime datetime,

    @LogSite varchar(20),

    @UserName varchar(30)

    )

AS

    /* SET NOCOUNT ON */

BEGIN

 

IF(@UserName = ‘’)

   

    BEGIN

 

        select * from tbLog where LogInTime > @StartTime and LogInTime < @EndTime and LogSite = @LogSite;

   

    END;

 

ELSE

 

    BEGIN

 

    select * from tbLog where LogInTime > @StartTime and LogInTime < @EndTime and LogSite = @LogSite and UserName = @UserName;

        

    END;

    

                

    RETURN

   

END;

 

2）C#里面有各类专门调用存储过程

 

/// <summary>

        /// 执行存储过程返回一个表。

        /// </summary>

        /// <param name="proName">存储过程名</param>

        /// <param name="paraValues">参数值列表</param>

        /// <returns>DataTable对象</returns>

        public static DataTable getDataTable(string proName, SqlParameter[] paraValues)

        {

            SqlConnection sqlcon = getConnection();

            DataTable dt;

            SqlCommand comm = new SqlCommand(proName, sqlcon);

            comm.CommandType = CommandType.StoredProcedure;

 

            // 添加所有参数

            if (paraValues != null)

            {

                foreach (SqlParameter para in paraValues)

                {

                    comm.Parameters.Add(para);

                }

            }

 

            try

            {

                SqlDataAdapter sqlda = new SqlDataAdapter(comm);

                dt = new DataTable();

                sqlda.Fill(dt);

            }

            catch (Exception e)

            {

                throw e;

            }

            finally

            {

                sqlcon.Close();

            }

 

            return dt;

        }

 

3）在程序中调用的代码是

 

/// <summary>

        /// 查找事件响应

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnSearch_Click(object sender, EventArgs e)

        {

            SqlParameter[] paraValues = { new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@LogSite", SqlDbType.VarChar, 20), new SqlParameter("@UserName", SqlDbType.VarChar, 30)};

 

            paraValues[0].Value = dtpStart.Value;

            paraValues[1].Value = dtpEnd.Value;

            paraValues[2].Value = cbxLogSite.SelectedItem.ToString();

            paraValues[3].Value = tbxUserName.Text;

 

 

            if (dtpStart.Value <= dtpEnd.Value)

            {

 

                DataTable dt = DBOperate.getDataTable("dbo.procShowLog", paraValues);

 

                dgvLog.DataSource = dt;

 

            }

            else

            {

                MessageBox.Show("起始日期不能超过截止日期！");

            }

 

        }

 转载请注明：http://blog.csdn.net/shineHoo