使用SqlDataReader和字符串数组 [英] Use SqlDataReader and string array

问题描述

我想使用数据读取器读取记录并将结果发送到数组字符串。

I want to use data reader to read a record and send the result to an array string.

我使用存储过程从2个表中选择数据。

I use a stored procedure to select data from 2 tables.

string ReqID = "";
string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlc;
string[] textbox;
string ReqType;

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["ReqID"] != null)
        ReqID = this.Request.QueryString["ReqID"];

    sqlc = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "LoadReq";

    string[] textbox = new string[4];
    if (!this.IsPostBack)
    {
        if (ReqID != "")
        {
            cmd.Parameters.Add("@ReqID", SqlDbType.NChar).Value = ReqID;
            cmd.Connection = sqlc;
            sqlc.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                textbox[2] = reader.GetString(reader.GetOrdinal("FReqID"));
                textbox[3] = reader.GetString(reader.GetOrdinal("OrderDate"));
                textbox[4] = reader.GetString(reader.GetOrdinal("OrderTime"));
                textbox[5] = reader.GetString(reader.GetOrdinal("ReqIP"));
                textbox[6] = reader.GetString(reader.GetOrdinal("Status"));
                textbox[7] = reader.GetString(reader.GetOrdinal("FileCode"));
                textbox[8] = reader.GetString(reader.GetOrdinal("Type"));
                textbox[9] = reader.GetString(reader.GetOrdinal("DOI"));
                textbox[10] = reader.GetString(reader.GetOrdinal("PubMedID"));
                textbox[11] = reader.GetString(reader.GetOrdinal("PaperCode"));


                LoadCorrectForm(textbox[8], textbox);
            }
        }

我的存储过程是：

CREATE PROCEDURE [dbo].LoadReq
@ReqID int = 0
AS
SELECT 
        Request.FReqID, Request.ReqIP, Request.OrderDate, Request.OrderTime,
        Request.[Status], Request.FileCode, Request.[Description],
        Details.[Type], Details.DOI, Details.PubMedID, Details.PaperCode, 
        Details.ISBN, Details.Title, Details.Journal, Details.BookTitle,
        Details.Conference, Details.[Year], Details.Publisher, Details.Edition, 
        Details.Vol, Details.Issue, Details.pp, Details.[Author(s)],
        Details.URL
     FROM 
        Request 
     INNER JOIN 
        Details ON Details.ID = Request.FreqID
     WHERE 
        (Request.ID = @ReqID)

但是我遇到错误。

我应该如何编辑代码来完成这项工作？

How should I edit my code to do this job?

推荐答案

正如哈比卜所说，您的数组太小。考虑此修改。代替此：

As stated by Habib, your array is too small. Consider this modification. Instead of this:

string[] textbox = new string[4];

甚至是这样：

string[] textbox = new string[11];

摆脱那条线，然后执行以下操作：

Get rid of that line and do this:

SqlDataReader reader = cmd.ExecuteReader();
string[] textbox = new string[reader.FieldCount];

建议

，您应该考虑一些建议。

Suggestions

In addition to the fix, there are some suggestions you should consider.

if (Request.QueryString["ReqID"] != null)
    ReqID = this.Request.QueryString["ReqID"];

using (SqlConnection sqlc = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("LoadReq", sqlc))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        if (!this.IsPostBack)
        {
            if (ReqID != "")
            {
                cmd.Parameters.Add("@ReqID", SqlDbType.NChar).Value = ReqID;

                sqlc.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    string[] textbox = new string[reader.FieldCount];

                    while (reader.Read())
                    {
                        textbox[2] = reader.GetString(reader.GetOrdinal("FReqID"));
                        textbox[3] = reader.GetString(reader.GetOrdinal("OrderDate"));
                        textbox[4] = reader.GetString(reader.GetOrdinal("OrderTime"));
                        textbox[5] = reader.GetString(reader.GetOrdinal("ReqIP"));
                        textbox[6] = reader.GetString(reader.GetOrdinal("Status"));
                        textbox[7] = reader.GetString(reader.GetOrdinal("FileCode"));
                        textbox[8] = reader.GetString(reader.GetOrdinal("Type"));
                        textbox[9] = reader.GetString(reader.GetOrdinal("DOI"));
                        textbox[10] = reader.GetString(reader.GetOrdinal("PubMedID"));
                        textbox[11] = reader.GetString(reader.GetOrdinal("PaperCode"));

                        LoadCorrectForm(textbox[8], textbox);
                    }
                }
            }

            ...
        }
    }
}

在修改后的代码中，有很多事情要注意：

In the modified code there are a number of things to take note of:

1. 我正在利用 using 语句。


2. 我正在利用其他的 .ctor 的 SqlCommand 使其更简洁。


3. 我正在利用 FieldCount 确定数组的大小。


4. 我不共享连接实例。


5. 这里可能使用更好的数据结构。


6. 您当前的代码流大概会建立连接和命令，即使它们没有

1. I'm leveraging the using statement.
2. I'm leveraging a different .ctor of the SqlCommand to make it more concise.
3. I'm leveraging the FieldCount to determine the size of the array.
4. I'm not sharing a connection instance.
5. There are probably better data structures to use here.
6. Your current code flow presumably builds the connection and command even if they don't need to be used.

第1点极为重要。 using 语句可确保正确处理创建和使用的对象。每当您使用实现 IDisposable 的东西时，都使用 using 语句来利用。

Point #1 is exceptionally important. The using statement ensures that the object created and used get disposed properly. Whenever you consume something that implements IDisposable, leverage the using statement.

第4点几乎与第1点一样重要。 不要共享连接实例。当您需要一个连接实例时，将其构建，打开，使用并处置。

Point #4 is almost as important as #1. Do not share connection instances. When you need one, build it, open it, use it, and dispose it.

让我详细说明＃5在这里。代替使用 string [] ， List< string> 怎么样。 List< string> 是可变的（即它可以自动更改大小）。该代码将容易得多，因为您可以执行以下操作：

Let me elaborate on #5 here. Instead of using a string[], how about a List<string>. The List<string> is mutable (i.e. it can change size automatically). The code would be much easier because you could do something like this:

textbox.Add(reader.GetString(reader.GetOrdinal("FReqID")));
textbox.Add(reader.GetString(reader.GetOrdinal("OrderDate")));
textbox.Add(reader.GetString(reader.GetOrdinal("OrderTime")));
textbox.Add(reader.GetString(reader.GetOrdinal("ReqIP")));
textbox.Add(reader.GetString(reader.GetOrdinal("Status")));
textbox.Add(reader.GetString(reader.GetOrdinal("FileCode")));
textbox.Add(reader.GetString(reader.GetOrdinal("Type")));
textbox.Add(reader.GetString(reader.GetOrdinal("DOI")));
textbox.Add(reader.GetString(reader.GetOrdinal("PubMedID")));
textbox.Add(reader.GetString(reader.GetOrdinal("PaperCode")));

然后，如果需要将其放入 string [] 您可以执行以下操作：

Then, if you needed to get it to a string[] you could just do this:

textbox.ToArray();

但是，这可能对您不利，因为您的偏移量很奇怪。直到索引 2 才开始插入数组。因此，我再一次不能确定地说出更好的数据结构。

But, that may not work well for you here because you have an odd offset. You don't start inserting into the array until index 2. So again, I can't really say with certainty there are better data structures.

第六点对我来说只是一个有趣的话题。如果它是 PostBack 或，则 ReqID 是一个空字符串，您实际上并没有做任何东西。这使我相信，您实际上应该在这些 if 语句内移动连接和命令对象的创建，但这只是我希望使用的逻辑流程，而不是当前的逻辑流程。

Point #6 is just an interesting one to me. If it's a PostBack or the ReqID is an empty string you don't actually do anything. That leads me to believe that you should actually moving the creation of the connection and command objects inside those if statements, but that's just a logical flow I'd prefer over the current one.

这篇关于使用SqlDataReader和字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！