/// <summary>
/// I don't like this setup but can't think of a better way of doing
/// right now.
/// </summary>
/// <param name="connection"></param>
public void SetServerVariables(MySqlConnection connection)
{
if (serverVariablesSet) return;
// retrieve the encoding that should be used for character data
MySqlCommand cmd = new MySqlCommand("show variables like 'max_allowed_packet'", connection);
try
{
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
driver.MaxPacketSize = reader.GetInt64( 1 );
reader.Close();
}
catch (Exception)
{
driver.MaxPacketSize = 1047552;
}
cmd.CommandText = "show variables like 'character_set'";
driver.Encoding = System.Text.Encoding.Default;
try
{
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
driver.Encoding = CharSetMap.GetEncoding( reader.GetString(1) );
reader.Close();
}
catch
{
throw new MySqlException("Failure to initialize connection");
}
serverVariablesSet = true;
}
/// <summary>l um valor</summary>
internal static int executeNonQuery( string query )
{
string connectionString = OrionGlobals.getConnectionString("connectiostring-mysql");
MySqlConnection conn = new MySqlConnection(connectionString);
try {
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
object obj = cmd.ExecuteScalar();
if( obj == null ) {
return 0;
}
return int.Parse(obj.ToString());
} catch( Exception e ) {
Chronos.Utils.Log.log("Connection String: {0}", connectionString);
Chronos.Utils.Log.log("Error: " + e.Message);
throw;
} finally {
conn.Close();
}
}
public void MySqlStore()
{
MySqlConnection connection = new MySqlConnection(
"Server=localhost;" +
"User ID=root;" +
"Password=;");
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
string sql = "DROP DATABASE openid_test;";
cmd.CommandText = sql;
try {
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{ }
cmd.Dispose();
cmd = connection.CreateCommand();
cmd.CommandText = "CREATE DATABASE openid_test;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = connection.CreateCommand();
cmd.CommandText = "USE openid_test;";
cmd.ExecuteNonQuery();
cmd.Dispose();
MySqlStore store = new MySqlStore(connection);
store.CreateTables();
StoreTester tester = new StoreTester(store);
tester.Test();
}
// returns a Open connection
public override void GetConnection ()
{
string connectionString = null;
try {
connectionString = ConfigClass.GetElement (configDoc, "database", "connectionString");
} catch (XPathException e) {
Console.WriteLine ("Error reading the config file !!");
Console.WriteLine (e.Message);
con = null;
return;
}
con = new MySqlConnection (connectionString);
try {
con.Open ();
} catch (MySqlException e) {
Console.WriteLine ("Cannot establish connection with the database");
Console.WriteLine ("Probably the Database is down ");
con = null;
} catch (InvalidOperationException e) {
Console.WriteLine ("Cannot open connection ");
Console.WriteLine ("Probably the connection is already open");
con = null;
} catch (Exception e) {
Console.WriteLine ("Cannot open connection ");
con = null;
}
}
public CatList(string listName, bool create, MySqlConnection conn)
{
Setup(conn, listName);
if (create){
base.MarkNew();
}else{
this.Load(listName);
}
}
/// <summary>
/// Executes a single command against a MySQL database. A new <see cref="MySqlConnection"/> is created
/// using the <see cref="MySqlConnection.ConnectionString"/> given.
/// </summary>
/// <param name="connectionString"><see cref="MySqlConnection.ConnectionString"/> to use</param>
/// <param name="commandText">SQL command to be executed</param>
/// <param name="parms">Array of <see cref="MySqlParameter"/> objects to use with the command.</param>
/// <returns></returns>
public static int ExecuteNonQuery( string connectionString, string commandText, params MySqlParameter[] parms )
{
//create & open a SqlConnection, and dispose of it after we are done.
using (MySqlConnection cn = new MySqlConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandText, parms );
}
}
/// <summary>
/// Executes a single command against a MySQL database. The <see cref="MySqlConnection"/> is assumed to be
/// open when the method is called and remains open after the method completes.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">SQL command to be executed</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command.</param>
/// <returns></returns>
public static int ExecuteNonQuery( MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters )
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add( p );
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
/// The state of the <see cref="MySqlConnection"/> object remains unchanged after execution
/// of this method.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command to execute</param>
/// <returns><see cref="DataSet"/> containing the resultset</returns>
public static DataSet ExecuteDataset(MySqlConnection connection, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connection, commandText, (MySqlParameter[])null);
}
/// <summary>
/// Execute a single command against a MySQL database.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command text to use for the command</param>
/// <param name="commandParameters">Parameters to use for the command</param>
/// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
public static object ExecuteScalar(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters)
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add( p );
//execute the command & return the results
object retval = cmd.ExecuteScalar();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Execute a single command against a MySQL database.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command text to use for the command</param>
/// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
public static object ExecuteScalar(MySqlConnection connection, string commandText)
{
//pass through the call providing null for the set of MySqlParameters
return ExecuteScalar(connection, commandText, (MySqlParameter[])null);
}
/// <summary>
/// Executes a single command against a MySQL database.
/// </summary>
/// <param name="connectionString">Settings to use for this command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
public static MySqlDataReader ExecuteReader(string connectionString, string commandText, params MySqlParameter[] commandParameters)
{
//create & open a SqlConnection
MySqlConnection cn = new MySqlConnection(connectionString);
cn.Open();
try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn, null, commandText, commandParameters, false );
}
catch
{
//if we fail to return the SqlDatReader, we need to close the connection ourselves
cn.Close();
throw;
}
}
public CatList(string listName, MySqlConnection conn)
{
Setup(conn, listName);
this.Load(listName);
}
/// <summary>
/// Overloaded. Initializes a new instance of the MySqlCommand class.
/// </summary>
public MySqlCommand(string cmdText, MySqlConnection connection)
{
this.cmdText = cmdText;
this.connection = connection;
}
/*
* Because the user should not be able to directly create a
* DataReader object, the constructors are
* marked as internal.
*/
internal MySqlDataReader( MySqlCommand cmd, CommandBehavior behavior)
{
this.command = cmd;
connection = (MySqlConnection)command.Connection;
commandBehavior = behavior;
}
public CatLists(MySqlConnection conn)
{
_conn = conn;
conn.Open();
ArrayList listNames = new ArrayList();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText ="SELECT DISTINCT ListName FROM " + _table;
MySqlDataReader dr = cmd.ExecuteReader();
while(dr.Read()) {
listNames.Add(Convert.ToString(dr["ListName"]));
}
dr.Close();
conn.Close();
foreach(string listName in listNames){
List.Add(new CatList(listName, false, conn));
}
}
/// <summary>
/// Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a MySqlConnection object.
/// </summary>
/// <param name="selectCommandText"></param>
/// <param name="conn"></param>
public MySqlDataAdapter( string selectCommandText, MySqlConnection conn)
{
SelectCommand = new MySqlCommand( selectCommandText, conn );
}
public void Connect(string server, string user, string password, string database)
{
string myConn = "Data Source=" + server + ";User ID=" + user + ";Database=" + database + ";Password=" + password;
mysql = new MySqlConnection(myConn);
mysql.Open();
}
internal void SerializeToBytes( System.IO.MemoryStream s, MySqlConnection conn )
{
string parm_string = null;
byte[] bytes = null;
//TODO: should value == null throw an exception?
if (Value == DBNull.Value || Value == null)
parm_string = "Null";
else if (paramValue is bool)
parm_string = Convert.ToByte(paramValue).ToString();
else if (paramValue is Enum)
parm_string = ((int)paramValue).ToString();
else
{
switch (dbType)
{
case MySqlDbType.Null:
parm_string = "Null";
break;
case MySqlDbType.VarChar:
case MySqlDbType.String:
parm_string = "'" + EscapeString(Value.ToString()) + "'";
break;
case MySqlDbType.Double:
parm_string = Convert.ToDouble(Value).ToString( conn.NumberFormat );
break;
case MySqlDbType.Float:
parm_string = Convert.ToSingle(Value).ToString( conn.NumberFormat );
break;
case MySqlDbType.Decimal:
parm_string = Convert.ToDecimal(Value).ToString( conn.NumberFormat );
break;
case MySqlDbType.Time:
if (Value is DateTime)
parm_string = String.Format("'{0:HH:mm:ss}'", ((DateTime)Value));
else
parm_string = String.Format("'{0}'", Value.ToString());
break;
case MySqlDbType.Date:
if (Value is DateTime)
parm_string = String.Format("'{0:yyyy-MM-dd}'", ((DateTime)Value));
else
parm_string = "'" + Value.ToString() + "'";
break;
case MySqlDbType.Datetime:
if (Value is DateTime)
parm_string = String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", ((DateTime)Value));
else
parm_string = "'" + Value + "'";
break;
case MySqlDbType.Blob:
case MySqlDbType.MediumBlob:
case MySqlDbType.LongBlob:
case MySqlDbType.TinyBlob:
Type t = paramValue.GetType();
if (t == typeof(System.Byte[]))
{
s.WriteByte((byte)'\'');
EscapeByteArray( (byte[])paramValue, s );
s.WriteByte((byte)'\'');
return;
}
else if(t == typeof(string))
parm_string = "'" + EscapeString((string)paramValue) + "'";
else if (t == typeof(System.Guid))
{
parm_string = "'" + paramValue.ToString() + "'";
}
break;
case MySqlDbType.Int:
case MySqlDbType.Int24:
parm_string = Convert.ToInt32( Value.ToString() ).ToString();
break;
case MySqlDbType.BigInt:
parm_string = Convert.ToInt64( Value.ToString() ).ToString();
break;
case MySqlDbType.Short:
parm_string = Convert.ToInt16( Value.ToString() ).ToString();
break;
default:
parm_string = Value.ToString();
break;
}
}
bytes = conn.Encoding.GetBytes(parm_string);
s.Write(bytes, 0, bytes.Length);
}
/// <summary>Construtor</summary>
public MySqlExceptionLogUtility()
{
conn = new MySqlConnection( OrionGlobals.getConnectionString("connectiostring-mysql") );
}
//***********************************
//Method to close database connection
//***********************************
public void CloseDBConnection()
{
dbcon.Close();
dbcon = null;
}
ArrayList TypeArray = new ArrayList(); //C# data types
#endregion Fields
#region Constructors
//*********************************
//constructor: open mysql database
//*********************************
public DataBaseReader(string connect_string)
{
dbcon = new MySqlConnection(connect_string);
dbcon.Open();
}
/// <summary>
/// Overloaded. Initializes a new instance of the MySqlCommand class.
/// </summary>
public MySqlCommand(string cmdText, MySqlConnection connection, MySqlTransaction txn)
{
this.cmdText = cmdText;
this.connection = connection;
curTransaction = txn;
}
private void Setup(MySqlConnection conn, string listName)
{
_conn = conn; _listName = listName;
_ents = new Entries(this);
if (_dbLayer == null) {
_dbLayer = new MySqlDBLayer(this);
}
}
/// <summary>
/// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
/// The state of the <see cref="MySqlConnection"/> object remains unchanged after execution
/// of this method.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
/// <param name="commandText">Command to execute</param>
/// <param name="commandParameters">Parameters to use for the command</param>
/// <returns><see cref="DataSet"/> containing the resultset</returns>
public static DataSet ExecuteDataset(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters)
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add( p );
//create the DataAdapter & DataSet
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
// detach the MySqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
//return the dataset
return ds;
}
public SQLData()
{
if (Connection != null)
Console.WriteLine ("Connected to mysql");
if (failed == false) {
// Create database if needded
MySqlCommand command = new MySqlCommand ("show databases;", Connection);
IDataReader reader = command.ExecuteReader();
bool found = false;
while (reader.Read())
if (reader.GetString(0) == "gtk_binding")
found = true;
reader.Close();
if (found == false) {
command = new MySqlCommand ("create database gtk_binding", Connection);
command.ExecuteNonQuery();
}
Database = "gtk_binding";
Connection.Close();
connection = null;
if (Connection != null)
Console.WriteLine ("Using database " + Connection.Database);
// Create table if needded
command = new MySqlCommand ("show tables;", Connection);
reader = command.ExecuteReader();
found = false;
while (reader.Read()) {
if (reader.GetString(0) == "binding_test")
found = true;
Console.WriteLine ("Table: " + reader.GetString(0));
}
reader.Close();
if (found == false) {
Console.WriteLine ("Creating Table binding_test");
command = new MySqlCommand ("CREATE TABLE `gtk_binding`.`binding_test` (" +
"`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
"`string_data` TEXT CHARACTER SET utf8 NOT NULL COMMENT 'String Data'," +
"`integer_data` INT NOT NULL DEFAULT 12 COMMENT 'Integer Value'," +
"`float_data` DOUBLE NOT NULL DEFAULT 13" +
")" +
"ENGINE = InnoDB " +
"CHARACTER SET utf8 " +
"COMMENT = 'Table with simple test data';" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue1', 5, 4);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue2', 6, 3);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue3', 7, 44);" +
"insert into binding_test (string_data, integer_data, float_data) values ('othervalue4', 8, 45);" +
"insert into binding_test (string_data, integer_data, float_data) values ('somevalue', 3, 7);", Connection);
command.ExecuteNonQuery();
}
Console.WriteLine ("Playground for this demo is set up ;)");
// Connection.
Console.WriteLine ("Opening table");
///get the dataadapter values
MySqlDataAdapter dataAdapter = new MySqlDataAdapter("select * from binding_test", Connection);
///Initialize, insert, update and delete commands for the database
// InitializeCommands();
///fillup the dataset now
DataSet dataSet = new DataSet();
// demoTable = new DataTable();
///fillup the data adapter now
dataAdapter.Fill(dataSet,"binding_test");
demoTable = dataSet.Tables["binding_test"];
Console.WriteLine ("Read {0} rows successfully", demoTable.Rows.Count);
}
else
Console.WriteLine ("Not connected & exiting!");
}
public static MySqlDataReader LoadWhereColumnIs(MySqlConnection conn, string table, string key, string value)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table + " WHERE " + key + " = @Id " +
"ORDER BY 1", conn);
cmd.Parameters.Add("@Id", value);
MySqlDataReader dr = cmd.ExecuteReader();
return dr;
}
/// <summary>
/// Executes a single command against a MySQL database, possibly inside an existing transaction.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn )
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add( p );
//create a reader
MySqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (ExternalConn)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}
public static MySqlDataReader Load(MySqlConnection conn, string table, string pk, int value)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table + " WHERE " + pk + " = @PK", conn);
cmd.Parameters.Add("@PK", value);
return cmd.ExecuteReader();
}
/// <summary>
/// Updates the given table with data from the given <see cref="DataSet"/>
/// </summary>
/// <param name="connectionString">Settings to use for the update</param>
/// <param name="commandText">Command text to use for the update</param>
/// <param name="ds"><see cref="DataSet"/> containing the new data to use in the update</param>
/// <param name="tablename">Tablename in the dataset to update</param>
public static void UpdateDataSet( string connectionString, string commandText, DataSet ds, string tablename )
{
MySqlConnection cn = new MySqlConnection( connectionString );
cn.Open();
MySqlDataAdapter da = new MySqlDataAdapter( commandText, cn );
MySqlCommandBuilder cb = new MySqlCommandBuilder( da );
da.Update( ds, tablename );
cn.Close();
}
/*public MySqlDataReader Load() {
MySqlConnection conn = (MySqlConnection) _bb.Conn;
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + _bb.Table + " WHERE ID = @ID", conn);
cmd.Parameters.Add("@Id", _bb.Id);
return cmd.ExecuteReader();
}*/
public static MySqlDataReader LoadAll(MySqlConnection conn, string table)
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + table, conn);
MySqlDataReader dr = cmd.ExecuteReader();
return dr;
}
