C#/MySql 5: Insert into table
Written by: maffelu , 2009-04-17 07:29:18
commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
'Bob',
'49'
);";
cmd.CommandText = commandLine;
cmd.ExecuteNonQuery();
commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
@name,
@age
);";
cmd.CommandText = commandLine;
cmd.Parameters.AddWithValue("@name", inputName);
cmd.Parameters.AddWithValue("@age", inputAge);
cmd.ExecuteNonQuery();
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 (MySqlException ex)
{
Console.WriteLine("Error: \r\n" ex.ToString());
}
//Clear parameters
cmd.Parameters.Clear();
Console.Write("If you want to quit enter \"quit\": ");
inputControl = Console.ReadLine();
if (inputControl == "quit")
{
ender = true;
}
} while (!ender);
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.