Use SqlDataReader and string array

Question

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

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);
            }
        }

And my stored procedure is:

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)

But I get an error.

How should I edit my code to do this job?

Answer 1

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

string[] textbox = new string[4];

or even this:

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. 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.

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.

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.

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")));

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

textbox.ToArray();

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.

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.