CODEWITHSUNDEEP
c#sql
Jamie Wright
Jamie Wright

Reputation: 45

Storing results from SQL in C#

I am trying to run a SQL query to gather data and write to a array. I want to be able to use this array to later run some if statements to see if the data that was retrieved meets certain requirements needed. I am very novice to C# and if there is a better way of doing this please point me in the right direction. Here is what I have so far:

public static class DBconnect
    { 

        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }

        public static void sqlConnect()

        {
            var qSql = "Select con.FIRSTNAME, Con.LASTNAME, Con.EMAIL, Act.USERFIELD1 'SerialNumber', ACT.ACCOUNT, TTC.SOFTWAREMAINTENANCE, TTC.HARDWAREMAINTENANCE " +
                    "from CONTACT CON Inner join ACCOUNT ACT on ACT.ACCOUNTID = CON.ACCOUNTID " +
                    "Inner join TTCONTRACTS TTC on TTC.ACCOUNTID = ACT.ACCOUNTID " +
                    "Where Con.EMAIL is not null and Act.USERFIELD1 is not null and " +
                    "ISNUMERIC(Act.USERFIELD1) = 1 and con.EMAIL like '%@%'";

            //Creates connection to sql database
            string connectionString = null;
            SqlConnection cnn;
            connectionString = @"Data Source=DESKTOP-MO813OQ\SalesLogic; Initial Catalog=SalesLogix_Pull; Integrated Security = True";
            cnn = new SqlConnection(connectionString);
            //Sql command to run the query's
            SqlCommand query = new SqlCommand(qSql, cnn);

            QueryResults[] allRecords = null;

            using (query)
            {
                cnn.Open();
                using (var reader = query.ExecuteReader())
                {
                    var list = new List<QueryResults>();
                    while (reader.Read())
                        list.Add(new QueryResults
                        {
                            FIRSTNAME = reader.GetString(0),
                            LASTNAME = reader.GetString(1),
                            EMAIL = reader.GetString(2),
                            SERIALNUMBER = reader.GetInt32(3),
                            ACCOUNT = reader.GetString(4),
                            USERFIELD1 = reader.GetInt32(5),
                            SOFTWAREMAINTENANCE = reader.GetBoolean(6),
                            HARDWAREMAINTENANCE = reader.GetBoolean(7)
                        });
                    allRecords = list.ToArray();
                }

                try
                {
                    cnn.Close();
                    MessageBox.Show("Connection Closed");
                }
                catch (Exception)
                {
                    MessageBox.Show("Error has occured.");
                }
            }


        }


    }

and 

class QueryResults
    {
        public string  FIRSTNAME { get; set; }

        public string LASTNAME { get; set; }

        public string EMAIL { get; set; }

        public int USERFIELD1 {get; set;}

        public int SERIALNUMBER { get; set; }

        public string ACCOUNT { get; set; }

        public bool SOFTWAREMAINTENANCE { get; set; }

        public bool HARDWAREMAINTENANCE { get; set; }
    }

When I run this I usually get these Errors:

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll on this part of the code:

list.Add(new QueryResults
                        {
                            FIRSTNAME = reader.GetString(0),
                            LASTNAME = reader.GetString(1),
                            EMAIL = reader.GetString(2),
                            SERIALNUMBER = reader.GetInt32(3),
                            ACCOUNT = reader.GetString(4),
                            USERFIELD1 = reader.GetInt32(5),
                            SOFTWAREMAINTENANCE = reader.GetBoolean(6),
                            HARDWAREMAINTENANCE = reader.GetBoolean(7)
                        });

Any help or pointers would be greatly appreciated!

Upvotes: 2

Views: 117

Answers (4)

Ramesh
Ramesh

Reputation: 9

The exception clearly says "Invalid Cast Exception". The format/datatype of the data stored in database and the casting in C# is miss matching. Please compare all the database column type with your C# Property type.

Upvotes: 1

Broom
Broom

Reputation: 596

I dislike hard coding indexes with the reader, in case I screw up a count, or the select changes

try this format for all of your collection initializer lines

SERIALNUMBER = reader.GetInt32(reader.GetOridinal("SerialNumber"))

instead of 

SERIALNUMBER = reader.GetInt32(3)

If that doesn't help, I would double check the actual column definitions in the database and make sure they are of the types you expect

Upvotes: 1

Scott Hannen
Scott Hannen

Reputation: 29222

For at least one of these the data type you're reading from the SqlDataReader doesn't match the method you're using to read it. In other words, maybe you're using .GetInt32 to read a varchar value.

The easiest thing is to look at the source tables and eyeball the columns to make sure that each column you're selecting from contains the expected type. If it doesn't, see if you can cast it in your query. USERFIELD1 looks suspicious because you're checking to see if it's numeric, which means it's not an int. In your query you could change 

Act.USERFIELD1 'SerialNumber'

to

convert(int, Act.USERFIELD1) 'SerialNumber'

Upvotes: 1

pkongkietvan
pkongkietvan

Reputation: 26

It's possible that some of your data column not return your expected output such as null value.

Try this

if(!reader.IsDBNull(colIndex))
   return reader.GetString(colIndex); 
else 
   return string.Empty;

If that not work, You can try debug which column has issue and provide me more information.

Hope this help.

Upvotes: 1

Related Questions