MorkaLork Development

Interesting stuff I've picked up over the years...

C#/MySql 6: Reading from table

2009-04-17 07:30:01 | 256 views | csharp class method tutorial table mysql command database records return

When you're reading this article I will assume that you have the basic knowledge of creating a connection, creating a database, creating a table and inserting data into it.
You know how to use the MySqlCommand class in the sense that you can create a command and execute it.

When you want to read the records in a database the syntax is as follows:
SELECT field FROM table

for example:

SELECT name FROM mycontacts

The above statement would select all records and return the name column.

When you do this in .NET you use a datareader, in our case, the MySqlDataRead class. We create a query and we throw it into our command class(MySqlCommand). We then create a data reader based upon our command:


commandLine = "SELECT * FROM mycontacts;";
cmd.CommandText = commandLine;
MySqlDataReader msdr;
msdr = cmd.ExecuteReader();


As shown above, we do not use the ExecuteNonQuery() method, we use the ExecuteReader() method since we will actually be wanting something back.
To return a field we will have to loop through the data that was returned:


while (msdr.Read())
{
Console.WriteLine(msdr.GetString(0));
}


What we do here is that we request to obtain field 0 in string form. If we have the fields name and int in a table, we would access the name field with GetType(0) and age with GetType(1).

The Full Code




using System;
using MySql.Data.MySqlClient;

namespace MorkConsTest
{
class Program
{


static void Main(string[] args)
{
MySqlConnection connect;
MySqlCommand cmd;
string connectionLine;
string commandLine;

//========================CONNECTION PART==========================
//Create a connection string
connectionLine = "Data source=localhost;UserId=root;Password=dog;";

//Instantiate the MySqlConnection class, constructor takes one connectionstring
connect = new MySqlConnection(connectionLine);
//Instantiate the MySqlCommand class
cmd = new MySqlCommand();

try
{
//Create a connection
cmd.Connection = connect;
//Open the connection
cmd.Connection.Open();
Console.WriteLine("Connection opened.");
}
catch (NullReferenceException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}

//=========================CREATE DATABASE============================
//Create a query
commandLine = "CREATE DATABASE mydb;";
//Set the command query
cmd.CommandText = commandLine;
try
{
//Execute the command query
cmd.ExecuteNonQuery();
Console.WriteLine("Database created.");
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
//Close the connection
cmd.Connection.Close();
Console.WriteLine("Connection closed.");
}

//==============================CREATE TABLE===============================
//Alter the connection string to now select a database
connectionLine = "Data source=localhost;Database=mydb;UserId=root;Password=dog;";
connect = new MySqlConnection(connectionLine);

commandLine = @"CREATE TABLE myContacts
(
name VARCHAR(150),
age INT(3)
)";

Console.WriteLine("Attempting to create table...");
try
{
//Enter the query to the CommandText
cmd.CommandText = commandLine;
//Create and open the connection previously made
cmd.Connection = connect;
cmd.Connection.Open();
Console.WriteLine("Connection opened...");
//Execute the CommandText
cmd.ExecuteNonQuery();
Console.WriteLine("Table created!");
//Always remember to close the door after you leave :)
cmd.Connection.Close();
Console.WriteLine("Connection closed...");
}
catch (MySqlException ex)
{
//It fails!
Console.WriteLine("Operation failed, table was not created...");
Console.WriteLine(ex.ToString());
}


//================INPUT IN TABLE============================
string inputName;
string inputAge;
string inputControl;
bool ender = false;
commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
@name,
@age
);";

do
{
Console.Clear();
Console.Write("Enter a friends name: ");
inputName = Console.ReadLine();
Console.Write("Enter that friends age: ");
inputAge = Console.ReadLine();

//Set the command text
cmd.CommandText = commandLine;
//Set the parameters
cmd.Parameters.AddWithValue("@name", inputName);
cmd.Parameters.AddWithValue("@age", inputAge);

try
{
//Open a connection
cmd.Connection.Open();
//Execute the command
cmd.ExecuteNonQuery();
//Close the connection
cmd.Connection.Close();

}
catch (NullReferenceException ex)
{
Console.WriteLine("Error: " ex.ToString());
}
catch (MySqlException ex)
{
Console.WriteLine("Error: \r\n" ex.ToString());
}

//Clear parameters
cmd.Parameters.Clear();

Console.Write("If you want to quit enter \"quit\" otherwize press any key: ");
inputControl = Console.ReadLine();
if (inputControl == "quit")
{
ender = true;
}

} while (!ender);

//============READ FROM TABLE===================
//Let's clear it up
Console.Clear();

//Create a data reader
MySqlDataReader msdr;
//Start counting from 1, not 0
int counter = 1;

commandLine = "SELECT * FROM mycontacts;";
cmd.CommandText = commandLine;


try
{
//Open the connection
cmd.Connection.Open();
//Execute a dataread
msdr = cmd.ExecuteReader();
//We will loop through all the records, so while there is something to read...
while (msdr.Read())
{
//Output is done by getting the strings in arrayform
Console.WriteLine("Contact {0}: {1}, {2}.", counter, msdr.GetString(0), msdr.GetString(1));
counter ;
}
//Close the dataread connection
msdr.Close();
}
catch (MySqlException ex)
{
Console.WriteLine("Error: \r\n{0}", ex.ToString());
}
finally
{
//Close the database connection
cmd.Connection.Close();
}

Console.Read();
}
}
}






Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.