如何在VS中调用数据库存储过程

一、存储过程

概述：存储过程是一种数据库对象，储存在数据库内，可用应用程序通过一个调用执行。允许用户声明变量，有条件执行。

调用存储过程：

首先在数据库写好存储过程

--根据loginId查询教员信息
if OBJECT_ID('SelectTeacherByLoginId') is not null
drop procedure SelectTeacherByLoginId
go
create procedure SelectTeacherByLoginId
@LoginId varchar(50) --参数
as
select TeacherID, TeacherName,Sex,LoginId,LoginPwd,Birthday,UserState,t.UserStateId
from Teacher t,UserState u where t.UserStateId=u.UserStateId and LoginId=@LoginId
go
--调用
exec SelectTeacherByLoginId 't001'

然后在应用程序管理类调用：

  #region 根据LoginID查询教员信息
        //根据LoginID查询教员信息
        public static Teacher SelectTeacherByLoginId(string loginId)
        {
            Teacher tea = null;
            string sql = "SelectTeacherByLoginId";
            SqlDataReader dr = DBHelper.ExecteReader(sql,
               CommandType.StoredProcedure //调用存储过程
               ,
               new SqlParameter[] {new SqlParameter("@LoginId",loginId)}
               );
            if (dr.Read())
            {
                tea = new Teacher();
                tea.TeacherID = Convert.ToInt32(dr["TeacherID"]);
                tea.LoginId = dr["LoginId"] + "";
                tea.LoginPwd = dr["LoginPwd"] + "";
                tea.TeacherName = dr["TeacherName"] + "";
                tea.Sex = dr["Sex"] + "";
                tea.UserState = dr["UserState"] + "";
                tea.UserStateId = Convert.ToInt32(dr["UserStateId"]);
                tea.Birthday = Convert.ToDateTime(dr["Birthday"]);
            }
            dr.Close();
            DBHelper.CloseCon();
            return tea;
        } 
        #endregion

最后在UI界面调用：