Get Value of Tuple From MySQL into String Array

Question

I want to select all rows and columns from database (There's no conditions) and store each tuple (for example: value of index[0,0] in the table) in an String array..

Here's the code i am using:

using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("S;Port=P;Database=DB;Uid=U;Pwd=P"))                      {
       connection.Open();
       MySql.Data.MySqlClient.MySqlCommand cmd=connection.CreateCommand();
       cmd.CommandText = "SELECT * FROM table_name";
       MySql.Data.MySqlClient.MySqlDataReader datr = cmd.ExecuteReader();            
       coun = Convert.ToInt32(cmd.ExecuteScalar());
       while (datr.Read()){
            for (int i = 0; i < coun; i++)  {                          
                 First_String[i] = datr[0].ToString();
                 Second_String[i] = datr[1].ToString();
                Third_String[i] = datr[2].ToString();
                        /* and so on...*/   
             }
       }
}

I am getting an error which is: Object not "Object reference not set to an instance of an object."

What the wrong thing am doing?

Thanks

The inner loop is meaningless. You're repeating the same data coun times. While(data.Read()) does what you're trying to do with the inner loop. — Zein Makki, Apr 12 '14 at 18:57
Make sure to initialize your first, second and third arrays, also check your database if any null values exists — CuriousPen, Apr 12 '14 at 18:57
What row is throwing the error ? since not all of your code is visible. — Zein Makki, Apr 12 '14 at 18:59
@user3185569 but how the data will be filled in the string arrays? they need an index which is "i" in the for loop — Izzo32, Apr 12 '14 at 19:01
@CuriousPen, how to do the initialization of arrays?, and there's no null values in the database. — Izzo32, Apr 12 '14 at 19:03
@Izzo32 In the second While iteration , Data will be overwritten because i will be 0 again. — Zein Makki, Apr 12 '14 at 19:04
@Izzo32 like First_String=new string[column_count_of db_table] — CuriousPen, Apr 12 '14 at 19:04

Reza · Answer 1 · 2014-04-12T19:14:42.023

0

Add this method

public static class DataReaderExtensions
{

    public static string SafeGetString(this SqlDataReader reader, int colIndex)
    {
       if(!reader.IsDBNull(colIndex))
           return reader.GetString(colIndex);
       else 
           return string.Empty;
    }
}

Change your code to this

for (int i = 0; i < coun; i++)
{
 First_String[i] = datr.SafeGetString(0);
 Second_String[i] = datr.SafeGetString(1);
 Third_String[i] = datr.SafeGetString(2);
}

edited Apr 12 '14 at 19:14

answered Apr 12 '14 at 18:52

Reza

18,865
13
88
163

Can you set a break point and tell me what line is caused the exception? – Reza Apr 12 '14 at 18:57
are you sure that your table has at least 3 columns? – Reza Apr 12 '14 at 19:01
@Izzo32 p.s. for looping through columns you dont need to run `coun = Convert.ToInt32(cmd.ExecuteScalar());` just use `datr.FieldCount` – Reza Apr 12 '14 at 19:11

Zohaib Aslam · Accepted Answer · 2014-04-12T19:18:40.513

try this code

using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("S;Port=P;Database=DB;Uid=U;Pwd=P"))                      {
       connection.Open();
       MySql.Data.MySqlClient.MySqlCommand cmd=connection.CreateCommand();
       cmd.CommandText = "SELECT * FROM table_name";
       MySql.Data.MySqlClient.MySqlDataReader datr = cmd.ExecuteReader();            
       coun = Convert.ToInt32(cmd.ExecuteScalar());
       int counter = 0;
       string[] First_String = new string[datr.FieldCount];
       string[] Second_String = new string[datr.FieldCount];
       string[] Third_String = new string[datr.FieldCount];
       while (datr.Read()){                          
                 First_String[counter] = datr[0].ToString();
                 Second_String[counter] = datr[1].ToString();
                Third_String[counter] = datr[2].ToString();
                        /* and so on...*/   
                counter++;
       }
}

Yes i tried your code, i got the same error but at the end of code at >> counter++ — Izzo32, Apr 12 '14 at 19:25

score 0 · Answer 3 · edited May 23 '17 at 11:49

Also if you are working with datareader a lot you can use this helper class from this post

SqlDataReader Best way to check for null values -sqlDataReader.IsDBNull vs DBNull.Value

/// <summary>
/// Helper class for SqlDataReader, which allows for the calling code to retrieve a value in a generic fashion.
/// </summary>
public static class SqlReaderHelper
{
    private static bool IsNullableType(Type theValueType)
    {
        return (theValueType.IsGenericType && theValueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    /// <summary>
    /// Returns the value, of type T, from the SqlDataReader, accounting for both generic and non-generic types.
    /// </summary>
    /// <typeparam name="T">T, type applied</typeparam>
    /// <param name="theReader">The SqlDataReader object that queried the database</param>
    /// <param name="theColumnName">The column of data to retrieve a value from</param>
    /// <returns>T, type applied; default value of type if database value is null</returns>
    public static T GetValue<T>(this SqlDataReader theReader, string theColumnName)
    {
        // Read the value out of the reader by string (column name); returns object
        object theValue = theReader[theColumnName];

        // Cast to the generic type applied to this method (i.e. int?)
        Type theValueType = typeof(T);

        // Check for null value from the database
        if (DBNull.Value != theValue)
        {
            // We have a null, do we have a nullable type for T?
            if (!IsNullableType(theValueType))
            {
                // No, this is not a nullable type so just change the value's type from object to T
                return (T)Convert.ChangeType(theValue, theValueType);
            }
            else
            {
                // Yes, this is a nullable type so change the value's type from object to the underlying type of T
                NullableConverter theNullableConverter = new NullableConverter(theValueType);

                return (T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType);
            }
        }

        // The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
        return default(T);
    }
}

usage:

yourSqlReaderObject.GetValue<int?>("SOME_ID_COLUMN");
yourSqlReaderObject.GetValue<string>("SOME_VALUE_COLUMN");

Get Value of Tuple From MySQL into String Array

3 Answers3