返回单个值
/// </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;