How can I convert database query results to an array?

Question

How would I convert the results returned in a MySQL query to an array in C# .Net/Mono

To my understanding you need to define arrays with the number of items the array will hold but I understand that the DataReader doesn't tell you have many row was returned. so how can I define a array.

So far I have:

string sqlWhere;

        if ((name != None) && (name != ""))
            sqlWhere = "WHERE name LIKE '%"+name+"%'";

        if ((company != None) && (company != ""))
            if (sqlWhere == "")
                sqlWhere = "WHERE company LIKE '%"+company+"%'";
            else
                sqlWhere = sqlWhere + " AND company LIKE '%"+company+"%'";

        if ((dateFrom != None) && (dateFrom != "") && (dateTo != None) && (dateTo != ""))
            if (sqlWhere == "")
                sqlWhere = "WHERE date(timestampIn) BETWEEN '"+dateFrom+"' AND '"+dateTo+"'";
            else
                sqlWhere = sqlWhere + " AND date(timestampIn) BETWEEN '"+dateFrom+"' AND '"+dateTo+"'";

        IDbCommand dbcmd = this.dbcon.CreateCommand();
        dbcmd.CommandText = "SELECT * FROM visitors " + sqlWhere;

                    MySqlDataReader Reader = dbcmd.ExecuteReader();

                    while (Reader.Read())
            {

            }

with the above code you are wide open to SQL injection - which is a serious security problem! — Yahia
– Yahia, Commented Jul 29, 2011 at 15:40
Why do you want an array? It's almost always a bad idea to create one. You can bind a datareader directly to a grid, if you need to. — Joel Coehoorn
– Joel Coehoorn, Commented Jul 29, 2011 at 15:45

bluwater2001 · Accepted Answer · 2011-07-29 16:08:24Z

public IList<Group> GetGroup()
        {
            Connection c = new Connection();
            String connectionString = c.ConnectionName;
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand mycmd = conn.CreateCommand();
            DataSet dspendingapps = new DataSet();
            dspendingapps.Clear();

            mycmd.CommandText = " select g.groupid,g.groupname from tbl_group g order by g.groupname ";
            conn.Open();

            OleDbDataAdapter appreader = new OleDbDataAdapter(mycmd);
            appreader.Fill(dspendingapps);
            conn.Close();

            IList<Group> g = new List<Group>();

            foreach (DataRow drapp in dspendingapps.Tables[0].Rows)
            {
                Group gg = new Group();
                gg.GroupId = Convert.ToInt16(drapp["groupid"]);
                gg.Name = drapp["groupname"].ToString();
                g.Add(gg);

            }
            return g;
        }

Justin Niessner · Accepted Answer · 2011-07-29 15:41:39Z

1

Instead of using a DataReader, use a DataAdapter to fill a DataTable. You can see the total number of rows in the resulting DataTable.

var results = new DataTable();

var adapter = new SqlDataAdapter();
adapter.SelectCommand = dbcmd;
dapater.Fill(results);

answered Jul 29, 2011 at 15:41

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Comments

Deepansh Gupta · Accepted Answer · 2011-07-29 15:42:00Z

1

You can use List<T> enter code here instead of array to store your data where T is the type of data you want to store e.g. string, int or your custom data type. For using Arrays, we need to specify the length at compile time but with List<T> we can store data without worrying about the length.

answered Jul 29, 2011 at 15:42

Deepansh Gupta

5934 silver badges9 bronze badges

Comments

geekyhybrid · Accepted Answer · 2011-07-29 16:06:01Z

1

Expanding on @Deepansh Gupta's response: You might consider creating a custom object or structure whose internal data fields correspond to the columns returned in your query result set (i.e., each object corresponds to a data row, each data variable in the object corresponds to columnar data). As your code goes through each row of the results, create a new custom object and append it to your List.

answered Jul 29, 2011 at 16:06

geekyhybrid

251 silver badge7 bronze badges

Collectives™ on Stack Overflow

How can I convert database query results to an array?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related