c# - MySQL to Array to MSSQL Query Statement

Question

I'm working on a program to link two databases (MySQL and MSSQL) and show them in a datagrid table.

I'm getting a count to get the number of arrays value, then assigning the array, then using the array value to return into a datagrid table.

The problem I have is skuArray[RowCount++] = Convert.ToInt32(myReader[0]);is returning error: cannot convert int to string. I changed it to skuArray[RowCount++] = Convert.String(myReader[0]); and it complied correctly but gives the message Object reference not set to an instance of an object.

All SQL Queries have been tested and successfully execute.

Code:

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;
using System.Xml.Serialization;
using System.Data.SqlClient;

namespace SQL_Database_Connector
{
    public partial class Sync_Databases : Form
    {
        string serverInfo; // MySQL Database Information
        string portInfo;
        string databaseInfo;
        string usernameInfo;
        string passwordInfo;

        string MSserverInfo; // MSSQL Database Information
        string MSdatabaseInfo;
        string MSusernameInfo;
        string MSpasswordInfo;

        public string[] skuArray;
        public string queryString;
        public int RowCount;

        public Sync_Databases()
        {
            InitializeComponent();
        }

        private void Sync_Databases_Load(object sender, EventArgs e)
        {
            if (File.Exists("data.xml")) // MySQL Database
            {
                XmlSerializer xs = new XmlSerializer(typeof(Information));
                FileStream read = new FileStream("data.xml", FileMode.Open, FileAccess.Read, FileShare.Read);
                Information info = (Information)xs.Deserialize(read);
                serverInfo = info.server;
                portInfo = info.port;
                databaseInfo = info.database;
                usernameInfo = info.username;
                passwordInfo = info.password;
                read.Close();
            }
            try
            {
                string MyConnection = String.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};", serverInfo, portInfo, databaseInfo, usernameInfo, passwordInfo);
                string Query = "SELECT COUNT(*) " +
                               "FROM catalog_product_entity " +
                               "INNER JOIN catalog_product_entity_int " +
                               "ON catalog_product_entity_int.entity_id = catalog_product_entity.entity_id " +
                               "WHERE catalog_product_entity.sku IS NOT NULL " +
                               "AND catalog_product_entity.sku <> 0 " +
                               "AND(catalog_product_entity_int.attribute_id = '84' AND catalog_product_entity_int.value = '1');";
                MySqlConnection MyConn = new MySqlConnection(MyConnection);
                MySqlCommand MyCommand = new MySqlCommand(Query, MyConn);
                MyConn.Open();
                MySqlDataReader myReader;
                myReader = MyCommand.ExecuteReader();
                try
                {
                    while (myReader.Read())
                    {
                       RowCount = myReader.GetInt32(0); // Get Row Count
                       //MessageBox.Show(RowCount.ToString()); // Test Row Count
                    }
                }
                finally
                {
                    myReader.Close();
                    MyConn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem with Row Count: "+ ex.Message);
            }
            try
            {
                string MyConnection = String.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};", serverInfo, portInfo, databaseInfo, usernameInfo, passwordInfo);
                string Query = "SELECT catalog_product_entity.sku AS 'SKU' " +
                               "FROM catalog_product_entity " +
                               "INNER JOIN catalog_product_entity_int " +
                               "ON catalog_product_entity_int.entity_id = catalog_product_entity.entity_id " +
                               "WHERE catalog_product_entity.sku IS NOT NULL " +
                               "AND catalog_product_entity.sku <> 0 " +
                               "AND(catalog_product_entity_int.attribute_id = '84' AND catalog_product_entity_int.value = '1');";
                MySqlConnection MyConn = new MySqlConnection(MyConnection);
                MySqlCommand MyCommand = new MySqlCommand(Query, MyConn);
                MyConn.Open();
                MySqlDataReader myReader;
                myReader = MyCommand.ExecuteReader();
                try
                {
                    while (myReader.Read())
                    {
                        skuArray[RowCount++] = Convert.ToString(myReader[0]); // Assigning Array Values
                        //MessageBox.Show(skuArray.ToString()); //T est
                    }
                }
                finally
                {
                    myReader.Close();
                    MyConn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem with MySQL query to capture Array: "+ ex.Message);
            }
            if (File.Exists("data2.xml")) // MSSQL Database
            {
                XmlSerializer xs = new XmlSerializer(typeof(Information));
                FileStream read = new FileStream("data2.xml", FileMode.Open, FileAccess.Read, FileShare.Read);
                Information info = (Information)xs.Deserialize(read);
                MSserverInfo = info.server;
                MSdatabaseInfo = info.database;
                MSusernameInfo = info.username;
                MSpasswordInfo = info.password;
                read.Close();
            }
            try
            {
                string connectionString = string.Format("Data Source={0}; Initial Catalog={1}; User ID={2}; Password={3};", MSserverInfo, MSdatabaseInfo, MSusernameInfo, MSpasswordInfo);
                string sql = string.Format("SELECT ItemLookupCode,Description, Quantity, Price, LastReceived " +
                             "FROM Item " +
                             "WHERE ItemLookupCode IS IN {0} " +
                             "ORDER BY LastReceived ASC;", skuArray);
                SqlConnection connection = new SqlConnection(connectionString);
                SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                connection.Open();
                dataadapter.Fill(ds, "sql_table");
                connection.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "sql_table";
                SqlConnection conn = new SqlConnection();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem with SQL query or connection: "+ ex.Message);
            }
        }
    }
}

I want to address this at a higher level (hence comment, rather than answer). Your best option here is likely to set up a linked database in Sql Server that connects to the MySql DB. You can set this up so you can reference tables from both Sql Server and MySql in the same SQL query. Your program makes one simple request to Sql Server, and Sql Server talks to MySql as needed. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 28, 2016 at 17:53
@Yaman skuArray[RowCount++] = myReader[0].ToString(); gives the same object error. — ahinkle
– ahinkle, Commented Nov 28, 2016 at 17:55
You never assign an array to skuArray. Why not use a list instead var skuList = new List<string>(); and then you can just do skuList.Add(myReader[0].ToString()); — juharr
– juharr, Commented Nov 28, 2016 at 18:06

andruso · Accepted Answer · 2019-11-28 12:50:33Z

2

Where have you initialised the array? I dont see any line which would say:

skuArray = new string[RowCount];

RowCount is just a placeholder i am using.

edited Nov 28, 2019 at 12:50

andruso

2,0051 gold badge19 silver badges26 bronze badges

answered Nov 28, 2016 at 17:56

Swetha

4674 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ahinkle Over a year ago

I initialized it in the public load.. public string[] skuArray; I attempted to setup as you have defined and I get out of bounds error.

Swetha Over a year ago

You have just declared it. You need to initialise the array the way I have specified. After you know what the array length is going to be. You need to initialise it . That is the reason you are getting the error.You are getting out of bounds because you are doing RowCount++ . You need to start with the first index 0 all the way upto RowCount-1.

Swetha Over a year ago

int index = 0; while (myReader.Read() && index < RowCount) { skuArray[index++] = Convert.ToString(myReader[0]); // Assigning Array Values //MessageBox.Show(skuArray.ToString()); //T est } //Try this

Collectives™ on Stack Overflow

c# - MySQL to Array to MSSQL Query Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related