C# MySql DataTable fill in Array

Question

i got a problem.

I want to write a Programm to Insert some stuff into a Mysql Database. In the first time i make for every column in the Database a own String and used this to make the SQL Query. But in the most tables i got more then 100 columns. And i dont want to make 100 Variables. So i decided to make it with a datatable and Array. But now i dont know how to fill the names of the columns into the array. Here is the part of the code that do that.

        private void button_npccreate_click(object sender, EventArgs e)
    {
        //Umwandlung von C# Variable in Mysql Variable
        MySqlCommand command = new MySqlCommand();
        command.Parameters.AddWithValue("@server", server);
        command.Parameters.AddWithValue("@port", port);
        command.Parameters.AddWithValue("@user", user);
        command.Parameters.AddWithValue("@password", mysqlpassword);
        command.Parameters.AddWithValue("@world", db_world);


        //Connection zu Mysql Server
        string myConnectionString = "Server=" + server + ";Port=" + port + ";Database=" + db_world + ";Uid=" + user + ";Pwd=" + mysqlpassword + ";";
        MySqlConnection connection = new MySqlConnection(myConnectionString);
        MySqlCommand cmd;
        cmd = connection.CreateCommand();
        string readquery = "SELECT * FROM quest_template;";
        MySqlDataAdapter read_adapter = new MySqlDataAdapter(readquery, connection);
        DataTable tableRead = new DataTable();
        connection.Open();
        read_adapter.Fill(tableRead);
        connection.Close();
        int columns = 0;


        if(tableRead.Rows.Count > 0)
        {
        foreach(DataColumn dc in tableRead.Columns)
        {
            columns++;
        }
        }
        else
        {
            MessageBox.Show("The table is empty. Please check your Database.");
        }

        string [] columns_array = new string[columns];
        foreach (DataColumn dc in tableRead.Columns)
        {
// Here i must fill the Array i think. But i dont know how to do                      this.
        }

    }

If you are using the Fill method to populate a DataTable, then work with that DataTable instead of working with an array. DataTables are much more powerful. If you still want to use arrays, then you need to create an array with the size of the result set for rows and as many dimensions as there is in the datatable. Also in your code you need a nested for loop. One for rows and one for columns. — NoChance, Jul 10 '15 at 21:47

Graffito · Answer 1 · 2015-07-11T22:37:24.583

You should take into account Emmad Kareem remarks.

The problem with your objective is that columns may contain values of different types. Therefore, you cannot collect all cells in a bidimensional array (one dimension for columns, one for row). For example, in the code below, if you want to merge col0Array and col1array in a single 2 dim array, you need to declare this array as "object[,]", what gives no advantage vs a DataTable.

int   [] Col0Array = new int   [tableRead.Rows.Count] ; // first Column type is int
string[] Col1Array = new string[tableRead.Rows.Count] ; // second Column type is string
for (int i=0;tableRead.Rows.Count;i++)
{
   Col0Array[i]=(int   )tableRead.Rows[i][0] ;
   Col1Array[i]=(string)tableRead.Rows[i][1] ;
}

C# MySql DataTable fill in Array

1 Answers1