What is C# equivalent of PHP's mysql_fetch_array function?

Question

I am learning C#/ASP.NET and I am wondering what the C# equivalent of the following PHP code is? I know the userid, and I want to fetch the rows from this table into the array of the variable "row", so I then can use it as "row['name']" and "row['email'].

$result = mysql_query("SELECT email, name FROM mytable WHERE id=7");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("Email: %s  Name: %s", $row["email"], $row["name"]);
}

Thanks.

Answer 1

I'm not sure if this is the same as mysql_fetch_array but i assume that. You can use IDBCommmand.ExecuteReader to create an IDataReader and use that to fill an Object[] with all fields of the row.

For example (using SQL-Server):

// use using statements to ensure that connections are disposed/closed (all implementing IDisposable)
using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
using (var cmd = new SqlCommand("SELECT email, name FROM mytable WHERE id=@id", con))
{
    cmd.Parameters.AddWithValue("@id", ID);  // use parameters to avoid sql-injection
    con.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        { 
            var fields = new object[reader.FieldCount];
            // following fills an object[] with all fields of the current line, 
            // is this similar to mysql_fetch_array?
            int count  = reader.GetValues(fields);
        }
    }
}

Edit:

I don't mean to make it as similar as possible, but how would I go about getting the same end result (a variable with the results) in C#

That's a matter of taste. You could use some kind of ORM like Enity-Framework, NHibernate, LINQ-To-SQL or Stackoverflow's Micro-ORM Dapper.NET(what i'm using currently) or plain ADO.NET (as shown above).

You can use a custom class that you fill manually with a DataReader or a DataTable which schema is loaded automatically.

For example (here using MySQL):

DataTable tblEmail = new DataTable();
using (var con = new MySqlConnection(Properties.Settings.Default.MySQL))
using (var da = new MySqlDataAdapter("SELECT Email, Name FROM Email WHERE id=@id", con))
{
    da.SelectCommand.Parameters.AddWithValue("@id", ID);
    da.Fill(tblEmail);
}

if (tblEmail.Rows.Count == 1)
{
    DataRow row  = tblEmail.Rows[0];
    String email = row.Field<String>("Email");
    String name  = row.Field<String>("Name");
}

As you can see, there are many ways in .NET. I have shown just two with ADO.NET.

Answer 2

There's no true equivalent. Having been a PHP developer in the past, I'd say the closest thing is to use a data adapter and fill a data table. Here's a reference to DbDataAdapter.Fill.

I'm not sure about the MySql driver but if you're using Sql Server here's some code to get you started:

using (var connection = new SqlConnection(connectionString))
{
    var table = new DataTable("tbl_objects");
    var adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand("SELECT * FROM tbl_name", connection);
    adapter.Fill(table);
}

Then, you can iterate over the rows in the table:

foreach(var row in table)
{
    Console.WriteLine("{0}", row["ColumnName"]);
}

Answer 3

You could loop through your result with a foreach loop as follows:

  foreach(var row in result)
  { 
      console.writeline("Email:" + row.Email, "Name:", row.Name);
  }

Is that the sort of thing you were looking for?

EDIT

In fact i have just seen you only have one result.

Then you can skip the foreach loop altogether

Answer 4

You need a connection to a database.

Assuming you are using mysql and an odbc connection.

var connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                     "SERVER=localhost;" +
                     "DATABASE=test;" +
                     "UID=venu;" +
                     "PASSWORD=venu;" +
                     "OPTION=3");


using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    OdbcCommand command = new OdbcCommand("SELECT email, name FROM mytable WHERE id=7", connection);

    connection.Open();

    // Execute the DataReader and access the data.
    OdbcDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        //do stuff with the data here row by row the reader is a cursor
    }

    // Call Close when done reading.
    reader.Close();

alternately you could use an odbcdataadapter and a datatable if you wanted all the results in a table you could use like an array.

Answer 5

The closest equivalent in .net would be something like this...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            var foo = MySqlHelper.ExecuteDataRow("Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;", "select * from foo");
            Console.WriteLine(foo["Column"]);
        }
    }
}

I assume you are using the MySql Data Connector http://dev.mysql.com/downloads/connector/net/

Do note there are better ways available in .net to connect to databases, but I think for a line by line, this is about as close to the PHP as you can get.