如何使用SQL数据加载器从数据库中读取数据 [英] How do I read data from database using SQL datareaders

问题描述

大家好。



我有一个存储过程，它登录用户并从多个表中检索一些数据，当我用测试数据测试过程时，它返回我需要的所有字段，但在我的应用程序中，当我使用datareader检索这些值时，我得到一个超出范围异常的索引。



我使用了C＃ > reader.VisibleFieldCount属性，用于计算返回的字段数，结果我只返回8个字段中的3个。





任何帮助将不胜感激



问候



我尝试过：



这是我的存储过程

  ALTER   PROCEDURE  [dbo]。[spCustomerLogin] 
 
  @ Email   nvarchar （Max），
  @ Password   nvarchar （最大）
 
  AS  
  SET   NOCOUNT   ON  
  SELECT  Customer_name，CUSTOMER_PROFILE_DATA.Customer_email，Customer_password，Customer_phone，Card_number  as  '  crd_num'，Card_expmnth，Card_expyear，Card_cvc 
  FROM  CUSTOMER_PROFILE_DATA 
  join  CARD_INFO 
  on  CARD_INFO.Customer_email = CUSTOMER_PROFILE_DATA.Customer_email 
  WHERE （CUSTOMER_PROFILE_DATA.Customer_email = @Email） AND （Customer_password =  @ Password ）
 返回 

我的读者de

  while （reader.Read（）&& reader.VisibleFieldCount >   5 ）
 {
 
 
 Response.Redirect（  ProfileC.aspx，假）; 
会话[  Cus_name] = Convert.ToString（reader [ 0 ]）; 
会话[  Cus_email] = Convert.ToString（reader [ 1 ]）; 
会话[  Cus_pass] = Convert.ToString（reader [ 2 ]）; 
会话[  Cus_phone] = Convert.ToString（reader [ 3 ]）的ToString（）; 
会话[  Card_num] = Convert.ToString（reader [ 4 ]）; 
会话[  Card_expmnth] = Convert.ToString（reader [ 5 ]）; 
会话[  Card_expyr] = Convert.ToString（reader [ 6 ]）; 
会话[  Card_cvv] = Convert.ToString（reader [ 7 ]）; 
 
} 
 

解决方案

尝试使用SqlDataAdapter [ ^ ]

 SqlCommand sqlCommand =  new  SqlCommand（）; 
 SqlDataAdapter da =  new  SqlDataAdapter（sqlCommand）; 
 DataTable dt =  new  DataTable（）; 
 da.Fill（dt）; 
  if （dt.Rows.Count ==  1 ）
 {
会话[  Cus_name] = Convert.ToString（dt.Rows [ 0 ] [ 0 ]）; 
会话[  Cus_email] = Convert.ToString（dt.Rows [ 0 ] [ 1 ]）; 
会话[  Cus_pass] = Convert.ToString（dt.Rows [ 0 ] [ 2 ]）; 
会话[  Cus_phone] = Convert.ToString（dt.Rows [ 0 ] [ 3 ]）; 
会话[  Card_num] = Convert.ToString（dt.Rows [ 0 ] [ 4 ]）; 
会话[  Card_expmnth] = Convert.ToString（dt.Rows [ 0 ] [ 5 ]）; 
会话[  Card_expyr] = Convert.ToString（dt.Rows [ 0 ] [ 6 ]）; 
会话[  Card_cvv] = Convert.ToString（dt.Rows [ 0 ] [ 7 ]）; 
 
 
} 

最后移动此行，以便所有必要的操作将导航到其他页面之前执行

 Response.Redirect（  ProfileC.aspx， false ）; 

1。不要使用VisibleFieldCount。它不是你想象的那样。

2.使用字段名称引用你的数据。这将使事情更容易调试。例如：

会话[  Cus_name] = reader [  Customer_name]。ToString（）; 

Hi, everyone

I have a stored procedure which logs in a user and retrieves some data from multiple table, When i test the procedure with test data it returns all the fields i need, But in my application when i use a datareader to retrieve these values i get an Index out of range exception.

I have used the "C#">reader.VisibleFieldCount property to count how many fields are being returned and it turns out i'm only getting back 3 of 8 fields.


Any assistance will be appreciated

Regards

What I have tried:

here is my stored procedure

ALTER PROCEDURE [dbo].[spCustomerLogin]

 @Email nvarchar(Max),
 @Password nvarchar(Max)

AS
SET NOCOUNT ON
SELECT Customer_name,CUSTOMER_PROFILE_DATA.Customer_email,Customer_password,Customer_phone,Card_number as 'crd_num',Card_expmnth,Card_expyear,Card_cvc
FROM CUSTOMER_PROFILE_DATA
join CARD_INFO
on CARD_INFO.Customer_email = CUSTOMER_PROFILE_DATA.Customer_email
WHERE (CUSTOMER_PROFILE_DATA.Customer_email =@Email) AND (Customer_password = @Password)
RETURN

My reader code

while(reader.Read() && reader.VisibleFieldCount > 5)
              {


                  Response.Redirect("ProfileC.aspx", false);
                  Session["Cus_name"] = Convert.ToString(reader[0]);
                  Session["Cus_email"] = Convert.ToString(reader[1]);
                  Session["Cus_pass"] = Convert.ToString(reader[2]);
                  Session["Cus_phone"] = Convert.ToString(reader[3]).ToString();
                  Session["Card_num"] = Convert.ToString(reader[4]);
                  Session["Card_expmnth"] = Convert.ToString(reader[5]);
                  Session["Card_expyr"] = Convert.ToString(reader[6]);
                  Session["Card_cvv"] = Convert.ToString(reader[7]);

              }

解决方案

try using SqlDataAdapter [^]

SqlCommand sqlCommand = new SqlCommand();
           SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
           DataTable dt = new DataTable();
           da.Fill(dt);
           if (dt.Rows.Count == 1)
           {
               Session["Cus_name"] = Convert.ToString(dt.Rows[0][0]);
               Session["Cus_email"] = Convert.ToString(dt.Rows[0][1]);
               Session["Cus_pass"] = Convert.ToString(dt.Rows[0][2]);
               Session["Cus_phone"] = Convert.ToString(dt.Rows[0][3]);
               Session["Card_num"] = Convert.ToString(dt.Rows[0][4]);
               Session["Card_expmnth"] = Convert.ToString(dt.Rows[0][5]);
               Session["Card_expyr"] = Convert.ToString(dt.Rows[0][6]);
               Session["Card_cvv"] = Convert.ToString(dt.Rows[0][7]);
             

           }

Move this line at the last, so that all the necessary actions will perform before navigating to other page

Response.Redirect("ProfileC.aspx", false);

1. Don't use VisibleFieldCount. It is not what you think it is.
2. Reference your data by using the field name. That will make things much easier to debug. For example:

Session["Cus_name"] = reader["Customer_name"].ToString();

这篇关于如何使用SQL数据加载器从数据库中读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！