• C#执行存储过程


    返回单个值   

       /// </summary>
            /// <param name="user">用户信息</param>
             /// <returns></returns>
             public static int VerifyUser(clsUser user)
              {
                 int iRet;
                 string sql = String.Format("EXECUTE VerifyUser @myVerifyReader OUTPUT,'{0}','{1}'",user.username,user.password); //执行的T-SQL串
                  SqlCommand scmd = new SqlCommand(sql, conn); 
    
                scmd.CommandType = CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@myVerifyReader",SqlDbType.Int)); 
                scmd.Parameters["@myVerifyReader"].Direction = ParameterDirection.Output; 
    
                //  DBOpen(conn); //在返回值上有问题  
                scmd.ExecuteNonQuery();
    iRet = (int)scmd.Parameters["@myVerifyReader"].Value; DBClose(conn);

    return iRet;
    }

    sql

    /*
    * VerifyUser 存储过程
    * 用途:验证用户登陆
    */
    CREATE PROCEDURE VerifyUser
    (
        @myVerifyReader int OUTPUT, --返回结果
        @username varchar(50), --用户名
        @userpassword varchar(50) --用户密码
    )
    AS
    IF EXISTS(SELECT [id] FROM [Users] WHERE username = @username AND userpassword = @userpassword)
        SET @myVerifyReader = 0 --通过验证
    
    ELSE IF EXISTS(SELECT [id] FROM [Users] WHERE username = @username)
        SET @myVerifyReader = 1 --用户存在,密码不正确
    ELSE
        SET @myVerifyReader = 2 --用户不存在
    
    RETURN  isnull(@myVerifyReader,3)
    -------------------------------------------------
    GO

    返回一个表

    //连接数据库
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=KARL;Initial Catalog=ER;Persist Security Info=True;User ID=sa;Password=sa";
            SqlCommand command = new SqlCommand("proc_getMovieID", conn);
            conn.Open();
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@movieName", SqlDbType.NVarChar, 50);
            command.Parameters["@movieName"].Value = movieName;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            DataSet ds = new DataSet();
            da.Fill(ds, "tables");
    
            //计算movieID
            Int32 movieID;
            if (ds.Tables[0].Rows.Count == 0)
                movieID = -1;
            else
                movieID = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            //关闭数据库
            conn.Close();
            return movieID;
  • 相关阅读:
    [原创]java WEB学习笔记26:MVC案例完整实践(part 7)---修改的设计和实现
    [原创]java WEB学习笔记25:MVC案例完整实践(part 6)---新增操作的设计与实现
    [原创]java WEB学习笔记24:MVC案例完整实践(part 5)---删除操作的设计与实现
    [原创]java WEB学习笔记23:MVC案例完整实践(part 4)---模糊查询的设计与实现
    Tapestry IoC Configuration
    Tapestry IoC Decorator
    Tapestry IoC Service
    Tapestry-Again
    mysql error nr.1045 解决方法
    Bootstrap学习 导航
  • 原文地址:https://www.cnblogs.com/panmy/p/5625078.html
Copyright © 2020-2023  润新知