C#/MySql 6: Reading from table
Written by: maffelu , 2009-04-17 07:30:01
commandLine = "SELECT * FROM mycontacts;";
cmd.CommandText = commandLine;
MySqlDataReader msdr;
msdr = cmd.ExecuteReader();
while (msdr.Read())
{
Console.WriteLine(msdr.GetString(0));
}
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();
}
}
}
There are no comments on this article.
If you have any question or just want to leave a message, just fill out the form below!
Your e-mail will not be visible in your post, it is for validation reasons only
Maffelu
Creator and admin of MorkaLork.com.
Started programming in HTML back when frames and tables was the way to design a page, moved on to Pascal/Delphi, PHP, javascript/jQuery, VB.NET/C#, Java and C++.
Currently studies .NET (in general) focusing on ASP.NET.