/// <summary>
/// Escapes characters to make a MySQL readable query.
/// </summary>
/// <param name="str">The string to translate</param>
/// <param name="conn">A valid, open connection</param>
/// <returns>The quoted escaped string</returns>
/// Modified by Chris Palowitch ([email protected])
/// utilizing StringBuilder for acceptable performance with large data
internal static string Escape(string str, MySQLConnection conn)
{
byte[] bytes = conn.CharacterEncoding.GetBytes(str);
StringBuilder to = new StringBuilder(bytes.Length * 2 + 1);
StringBuilder result = new StringBuilder(bytes.Length * 2 + 1);
result.Append('\'');
conn.NativeConnection.mysql_real_escape_string(to, str, (uint)bytes.Length);
result.Append(to);
result.Append('\'');
return(result.ToString());
}
public BindVarQueryStatement(MySQLConnection connection, bool closeConnection, string query, MySQLParameterCollection parameterCollection, uint?fetchSize, CursorType cursorType) : base(connection, query, parameterCollection, fetchSize, cursorType)
{
_connection = connection;
var fields = new List <MYSQL_FIELD>();
using (var resultMetadata = new NativeResultMetadata(Stmt))
{
for (var i = 0; i < resultMetadata.mysql_num_fields(); i++)
{
IntPtr fieldPtr = resultMetadata.mysql_fetch_field_direct((uint)i);
var field = Marshal.PtrToStructure <MYSQL_FIELD>(fieldPtr);
fields.Add(field);
}
}
if (Stmt.mysql_stmt_execute() != 0)
{
throw new MySqlException(Stmt);
}
_fields = fields.ToArray();
_closeConnection = closeConnection;
IsClosed = false;
_rowColumns = new MYSQL_BIND[_fields.Length];
for (var index = 0; index < _fields.Length; index++)
{
var fieldMetadata = _fields[index];
_rowColumns[index] = new MYSQL_BIND();
//else if (fieldMetadata.Type == enum_field_types.MYSQL_TYPE_NULL && parameters != null && parameters.Count > index)//Caso select distinct donde mysql_stmt_bind_param3 mapea erroneamente a NULL
//{
// // TODO: case needs deep review
// fieldMetadata.Type = PreparedStatement.DbtoMysqlType(parameters[index].DbType);
//}
_rowColumns[index].InitForBind(fieldMetadata, Stmt._nativeConnection);
}
sbyte code = Stmt.mysql_stmt_bind_result(_rowColumns);
if (code != 0)
{
throw new MySqlException(Stmt);
}
}
/// <summary>
/// A shortcut to make a simple select command with where and sorting part. (where and sorting would be null)
/// </summary>
/// <param name="conn">Database connection</param>
/// <param name="Tables">An array of table names as strings. Examples:
/// <code>
/// new string[]{"trash","users"}
///
/// new string[]{"trash",users}
///
/// new string[]{"trash"}
///
/// null
/// </code>
/// </param>
/// <param name="Fields">An array of field names (or expressions) as strings. Examples:
/// <code>
/// new string[]{"id","name"}
///
/// new string[]{"id",name}
///
/// new string[]{"id"}
///
/// null
/// </code>
/// </param>
/// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example:
/// <code>
/// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}}
///
/// new object[,]{{"myfield","=",100}}
///
/// new object[,]{{"myfield","=",myvalue}}
///
/// null
/// </code>
/// </param>
/// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example:
/// <code>
/// new object[,]{{"myfield","=","myfield2"}}
///
/// null
/// </code>
/// </param>
/// <param name="Sorting">Pairs of {field,sorttype} as an string array. For example:
/// <code>
/// new string[] {{"SettingID","ASC"},{"SettingValue","DESC"}}
///
/// new string[] {{"SettingID","ASC"}}
///
/// null
/// </code></param>
/// <example>Example looking for SettingValue by SettingID<code>
/// using MySQLDriverCS;
/// ...
/// MySQLConnection DBConn;
/// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString );
/// DBConn.Open();
/// ...
/// DataTable dt = new MySQLSelectCommand(DBConn,
/// new string[] {"SettingID","SettingValue"},
/// new string[] {"Settings"},
/// new object[,] {{"SettingID","=",SettingID}},
/// null,
/// null
/// ).Table;
/// string Value = null;
/// if(dt.Rows.Count!=0)
/// {
/// Value = dt.Rows[0]["SettingValue] as string;
/// }
/// else
/// {
/// // Value not found
/// }
/// ...
/// DBConn.Close();
/// </code></example>
public MySQLSelectCommand(
MySQLConnection conn, // Database connection
string[] Fields, // Fields to show (also admit expressions)
string[] Tables, // Tables
object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing)
string[,] WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing)
string[,] Sorting // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority)
)
{
DoSelect(
conn, // Database connection
Fields, // Fields to show (also admit expressions)
Tables, // Tables
WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing)
WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing)
Sorting, // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority)
false, 0, 0, false
);
}
internal MySQLTransaction(MySQLConnection connection, IsolationLevel isolationLevel)
{
Connection = connection;
var sql = "";
switch (isolationLevel)
{
case IsolationLevel.ReadCommitted:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
break;
case IsolationLevel.ReadUncommitted:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
break;
case IsolationLevel.RepeatableRead:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ";
break;
case IsolationLevel.Serializable:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE";
break;
case IsolationLevel.Chaos:
throw new MySqlException("MySQLDriverCS Error: Chaos isolation level is not implemented in MySQL.");
}
IsolationLevel = isolationLevel;
using (var cmd = new MySQLCommand(sql, connection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand("BEGIN", connection))
{
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Extended SELECT with all settings (Except INTO OUTFILE/DUMPFILE for security reasons)
/// </summary>
/// <param name="conn">Database connection</param>
/// <param name="PrevFlags">Flags used in SELECT: [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]</param>
/// <param name="FieldsAndAlias">fields like {{"f1"},{"f2"}} or {{"f1","aliasf1"},...}</param>
/// <param name="FieldsTitles">If it's null they're retrieved from FieldsAndAlias. Useful for re-aliasing and required if you use AS or explicit index using.</param>
/// <param name="Tables">Table list</param>
/// <param name="WhereValues">Like {{field,op,value}...}</param>
/// <param name="WhereExpressions">Like {{expr1,op,expr2}}</param>
/// <param name="GroupBy">Like {{field}...} or {{field,ASC|DESC}...}</param>
/// <param name="GroupByHavingValues">like {{field,op,value}...}</param>
/// <param name="GroupByHavingExpressions">Like {expr1,expr2}</param>
/// <param name="OrderBy">Like {{field}...} or {{field,ASC|DESC}...}</param>
/// <param name="Limit">Like null, new int[]{}, new int{a number} (rows), or pair of numbers new int[]{offset,rows}</param>
/// <param name="LastFlags">this includes PROCEDURE, FOR UPDATE and LOCK IN SHARE MODE</param>
public MySQLSelectCommand(
MySQLConnection conn, // Database connection
string PrevFlags, // select flags like SQL_CACHE or SQL_BUFFER_RESULT or DISTINCT
string[,] FieldsAndAlias, // fields like {{"f1"},{"f2"}} or {{"f1","aliasf1"},...}
string[] FieldsTitles,
string[] Tables, // Table list
object[,] WhereValues, // like {{field,op,value}...}
string[,] WhereExpressions, // Like {{expr1,op,expr2}}
string[] GroupBy, // Like {"field "+"ASC"|"DESC"}...
object[,] GroupByHavingValues, // like {{field,op,value}...}
string[,] GroupByHavingExpressions, // Like {{expr1,op,expr2}}
string[] OrderBy, // Like {"field "+"ASC"|"DESC"}...
int[] Limit, // Like null, empty, a number(rows), or pair of numbers(offset,rows)
string LastFlags // this includes PROCEDURE, FOR UPDATE and LOCK IN SHARE MODE
)
{
string query = PrevFlags; int n;
string[] Fields;
if (FieldsTitles != null)
{
Fields = FieldsTitles;
}
else
{
Fields = new string[FieldsAndAlias.GetLength(0)];
for (n = 0; n < Fields.Length; n++)
{
Fields[n] = FieldsAndAlias[n, 0];
}
}
// FieldsAndAlias
if (true)
{
for (int i = 0; i < FieldsAndAlias.GetLength(0); i++)
{
query += " " + FieldsAndAlias[i, 0];
if (FieldsAndAlias[i, 1] != null)
{
query += " AS " + FieldsAndAlias[i, 1];
}
}
}
// Tables
if (true)
{
query += " FROM";
for (n = 0; n < Tables.Length; n++)
{
query += " " + conn.Database + "." + Tables[n].ToString();
if (n != (Tables.Length - 1))
{
query += ",";
}
}
}
// Where
query += WhereQueryPart(WhereValues, WhereExpressions, "WHERE", conn);
// Group by
if (true)
{
if (GroupBy != null)
{
for (n = 0; n < GroupBy.Length; n++)
{
query += " " + GroupBy[n].ToString();
if (n != (GroupBy.Length - 1))
{
query += ",";
}
}
// Having
query += WhereQueryPart(GroupByHavingValues, GroupByHavingExpressions, "HAVING", conn);
}
}
// Order by
if (OrderBy != null)
{
for (n = 0; n < OrderBy.Length; n++)
{
query += " " + OrderBy[n].ToString();
if (n != (OrderBy.Length - 1))
{
query += ",";
}
}
}
// Limit
if (Limit != null)
{
if (Limit.GetLength(0) == 1)
{
query += " LIMIT " + Limit[0];
}
else
{
query += " LIMIT " + Limit[0] + "," + Limit[1];
}
}
query += " " + LastFlags;
Exec(query, conn, Fields);
}
/// Modified by Claudia Murialdo (07/24/04) in order to support
/// culture-independent format of numeric values in a stmt.
internal static string WhereQueryPart(object[,] WhereParamFields, string[,] WhereNoparamFields, string prefix, MySQLConnection conn)
{
string query = ""; int n;
bool Anything = false;
if ((WhereParamFields != null) && (WhereParamFields.Length != 0))
{
Anything = true;
n = WhereParamFields.GetLength(0);
for (int m = 0; m < WhereParamFields.GetLength(0); m++)
{
n--;
string Field = WhereParamFields[m, 0] as string;
string Operand = WhereParamFields[m, 1] as string;
object Value = WhereParamFields[m, 2];
if (Value == null)
{
query += " " + Field + " " + Operand + " NULL ";
}
else
if ((Value.GetType() == typeof(int)) ||
(Value.GetType() == typeof(long)) ||
(Value.GetType() == typeof(short)) ||
(Value.GetType() == typeof(decimal)) ||
(Value.GetType() == typeof(float)) ||
(Value.GetType() == typeof(double)))
{
query += " " + Field + " " + Operand + " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " ";
}
else
{
query += " " + Field + " " + Operand + " " + MySQLUtils.Escape(Value.ToString(), conn) + " ";
}
if (n != 0)
{
query += " AND ";
}
}
}
if ((WhereNoparamFields != null) && (WhereNoparamFields.Length != 0))
{
if (Anything)
{
query += " AND ";
}
Anything = true;
n = WhereNoparamFields.GetLength(0);
for (int m = 0; m < WhereNoparamFields.GetLength(0); m++)
{
n--;
string Field = WhereNoparamFields[m, 0] as string;
string Operand = WhereNoparamFields[m, 1] as string;
object Value = WhereNoparamFields[m, 2];
if (Value == null)
{
query += " " + Field + " " + Operand + " NULL ";
}
else
{
query += " " + Field + " " + Operand + " " + Value.ToString() + " ";
}
if (n != 0)
{
query += " AND ";
}
}
}
if (Anything)
{
query = " " + prefix + " " + query;
}
return(query);
}
private void Exec(string query, MySQLConnection conn, string[] Fields)
{
MySQLCommand command = new MySQLCommand(query, conn);
//GDBUtils.WhereCommandPart(ref command,WhereParamFields);
command.Prepare();
DataTable table;
BindVarQueryStatement reader = null;
// Execute query ->
try
{
reader = (BindVarQueryStatement)command.ExecuteReader(/*CommandBehavior.SequentialAccess*/);
// Get results ->
table = new DataTable(null);
for (int n = 0; n < reader.FieldCount; n++)
{
System.Data.DataColumn dc = new DataColumn(reader.GetName(n));
String s = dc.ToString();
System.Data.DataColumn dcNew = new System.Data.DataColumn(s);
table.Columns.Add(dcNew);
}
while (reader.Read())
{
if (reader.IsClosed)
{
throw new MySqlException("Reader is closed.");
}
DataRow row = table.NewRow();
// Bugfixed by Yann Sénécheau 2002-10-28 ->
// for(n=0;n<Fields.Length;n++)
for (int n = 0; n < reader.FieldCount; n++)
// <- Bugfixed by Yann Sénécheau 2002-10-28
{
if (n < reader.FieldCount)
{
if (reader.GetValue(n) == null)
{
row[n] = null;
}
else
if (reader.GetFieldType(n) == typeof(string))
{
row[n] = reader.GetString(n).Clone();
}
else
{
row[n] = reader.GetValue(n);
}
}
else
{
break;
}
}
table.Rows.Add(row);
}
// <- Get results
}
catch (Exception e)
{
throw new MySqlException(e.Message + " in query '" + query + "'");
}
finally
{
if (reader != null)
{
reader.Close();
}
command.Dispose();
}
// <- Execute query
Table = table;
}
internal void DoSelect(
MySQLConnection conn, // Database connection
string[] Fields, // Fields to show (also admit expressions)
string[] Tables, // Tables
object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing)
string[,] WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing)
string[,] Sorting, // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority)
bool useLimits, int limitStartRow, int limitRows, bool Distinct
)
{
string query = "SELECT ";
int n;
// Text Building ->
if (Distinct)
{
query += "DISTINCT ";
}
for (n = 0; n < Fields.Length; n++)
{
query += " " + Fields[n].ToString();
if (n != (Fields.Length - 1))
{
query += ",";
}
}
if (Tables != null)
{
query += " FROM ";
}
else
{
Tables = new string[0];
}
for (n = 0; n < Tables.Length; n++)
{
query += " " + conn.Database + "." + Tables[n].ToString();
if (n != (Tables.Length - 1))
{
query += ",";
}
}
query += WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn);
if ((Sorting != null) && (Sorting.Length != 0))
{
query += " ORDER BY ";
n = Sorting.Length / 2;
for (int m = 0; m < (Sorting.Length / 2); m++)
{
n--;
query += " " + Sorting[m, 0].ToString() + " " + Sorting[m, 1].ToString();
if (n != 0)
{
query += ",";
}
}
}
if (useLimits)
{
query += " LIMIT " + limitStartRow.ToString() + "," + limitRows.ToString();
}
// <- Text Building
Exec(query, conn, Fields);
}
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query and a MySQLConnection.</summary>
/// <param name="cmdText"></param>
/// <param name="connection"></param>
public MySQLCommand(string cmdText, MySQLConnection connection)
{
CommandText = cmdText;
this._connection = connection;
}
public InternalNativeTracer(MySQLConnection mySqlConnection)
{
_mySqlConnection = mySqlConnection;
}
// Update by Omar del Valle Rodríguez ([email protected]) // In order support CommandBehavior.CloseConnection internal LiteralQueryStatement(IntPtr resultPtr, MySQLConnection connection, bool closeConnection) { _nativeResult = new NativeResult(resultPtr); // Add by Omar del Valle Rodríguez ([email protected]) // Save if close connection after close MySQLDataReader m_CloseConnection = closeConnection; this._connection = connection; dt = new DataTable(); uint i; ulong j; uint num_fields = _nativeResult.mysql_num_fields(); ulong num_rows = _nativeResult.mysql_num_rows(); List <QueryFieldDescription> fields = new List <QueryFieldDescription>(); for (i = 0; i < num_fields; i++) { var field = new MYSQL_FIELD(); var ptr = _nativeResult.mysql_fetch_field_direct(i); Marshal.PtrToStructure(ptr, field); dt.Columns.Add(field.Name); //Type ftype = MySqlField.MysqltoNetType(field); var mySqlField = new QueryFieldDescription(field.Name, (enum_field_types)field.Type, field.MaxLength, field.Length, field.Flags); if (mySqlField.FieldType != null) { dt.Columns[field.Name].DataType = mySqlField.FieldType; if (dt.Columns[field.Name].DataType == System.Type.GetType("System.Byte[]")) { dt.Columns[field.Name].ExtendedProperties.Add("max_length", (int)field.MaxLength); } } fields.Add(mySqlField); } for (j = 0; j < num_rows; j++) { IntPtr myrow = _nativeResult.mysql_fetch_row(); if (myrow == IntPtr.Zero) { throw new MySqlException(connection.NativeConnection); } DataRow dr = dt.NewRow(); /* for BLOB support * "Christophe Ravier" <*****@*****.**> 2003-11-27*/ var lengths = _nativeResult.mysql_fetch_lengths((int)num_fields); for (i = 0; i < num_fields; i++) { QueryFieldDescription queryFieldDescription = fields[(int)i]; IntPtr ptr = Marshal.ReadIntPtr(myrow, (int)i * Marshal.SizeOf <IntPtr>()); if (ptr == IntPtr.Zero) { dr[(int)i] = Convert.DBNull; } else if (queryFieldDescription.FieldType == typeof(bool)) { dr[(int)i] = GetValidString(ptr) != "0"; } else if (queryFieldDescription.FieldType == typeof(byte[])) { int length = (int)lengths[i]; var val = new byte[length]; Marshal.Copy(ptr, val, 0, length); dr[(int)i] = val; } else if (queryFieldDescription.FieldType == typeof(short)) { dr[(int)i] = Convert.ToInt16(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(ushort)) { dr[(int)i] = Convert.ToUInt16(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(byte)) { dr[(int)i] = Convert.ToByte(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(sbyte)) { dr[(int)i] = Convert.ToSByte(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(decimal)) { dr[(int)i] = Convert.ToDecimal(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(double)) { dr[(int)i] = Convert.ToDouble(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(float)) { dr[(int)i] = Convert.ToSingle(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(int)) { dr[(int)i] = Convert.ToInt32(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(uint)) { dr[(int)i] = Convert.ToUInt32(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(long)) { dr[(int)i] = Convert.ToInt64(GetValidString(ptr)); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_GEOMETRY) { // TODO: returns empty string string val = GetValidString(ptr); dr[(int)i] = val; } else if (queryFieldDescription.FieldType == typeof(ulong)) { if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_BIT) { { int byteCount; if (queryFieldDescription.Length <= 8) { byteCount = 1; } else if (queryFieldDescription.Length <= 16) { byteCount = 2; } else if (queryFieldDescription.Length <= 24) { byteCount = 3; } else if (queryFieldDescription.Length <= 32) { byteCount = 4; } else if (queryFieldDescription.Length <= 32 + 8) { byteCount = 5; } else if (queryFieldDescription.Length <= 32 + 16) { byteCount = 6; } else if (queryFieldDescription.Length <= 32 + 24) { byteCount = 7; } else { byteCount = 8; } var buffer = new byte[sizeof(long)]; for (int l = 0; l < byteCount; l++) { buffer[l] = Marshal.ReadByte(ptr, l); } dr[(int)i] = BitConverter.ToInt64(buffer, 0); } } else { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { dr[(int)i] = Convert.ToUInt64(val); } } } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATE || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATE) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATETIME || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATETIME2 || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIMESTAMP || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIMESTAMP2) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIME || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIME2) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact("0001-01-01 " + val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_YEAR) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.FieldType == typeof(DateTime)) { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { DateTimeFormatInfo format = new DateTimeFormatInfo(); if (val.Length > 20) { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture.DateTimeFormat); } else { if (val.Length > 10) { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd", CultureInfo.InvariantCulture.DateTimeFormat); } } } } else if (queryFieldDescription.FieldType == typeof(string)) { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { dr[(int)i] = val; } } else { throw new Exception(); } } dt.Rows.Add(dr); } }
public BindVarStatement(MySQLConnection connection, string query, MySQLParameterCollection parameterCollection, uint?fetchSize, CursorType cursorType)
{
Stmt = connection.NativeConnection.CreateStatement();
if (fetchSize != null)
{
var ptr = Marshal.AllocHGlobal(Marshal.SizeOf(fetchSize.Value));
Marshal.StructureToPtr(fetchSize.Value, ptr, false);
var code1 = Stmt.mysql_stmt_attr_set(StmtAttrTypes.STMT_ATTR_PREFETCH_ROWS, ptr);
Marshal.FreeHGlobal(ptr);
if (code1 != 0)
{
throw new MySqlException(Stmt);
}
}
if (cursorType != CursorType.NoCursor)
{
var value = (enum_cursor_type)cursorType;
var ptr = Marshal.AllocHGlobal(Marshal.SizeOf(value));
Marshal.StructureToPtr(value, ptr, false);
var code1 = Stmt.mysql_stmt_attr_set(StmtAttrTypes.STMT_ATTR_CURSOR_TYPE, ptr);
Marshal.FreeHGlobal(ptr);
if (code1 != 0)
{
throw new MySqlException(Stmt);
}
}
// execute prepare to call mysql_stmt_param_count
if (Stmt.mysql_stmt_prepare(query, query.Length) != 0)
{
throw new MySqlException(Stmt);
}
// This function will not deliver a valid result until mysql_stmt_prepare() was called.
var paramCount = (int)Stmt.mysql_stmt_param_count();
if (paramCount != parameterCollection.Count)
{
throw new MySqlException($"Invalid parameters, stmt parameters:{paramCount} parameters count:{parameterCollection.Count}");
}
// alloc & set binds
_bindParams = new MYSQL_BIND[parameterCollection.Count];
for (var i = 0; i < parameterCollection.Count; i++)
{
_bindParams[i] = new MYSQL_BIND();
var param = parameterCollection[i];
var bindInput = param.GetBindInput();
if (bindInput != null)
{
_bindParams[i].buffer_type = bindInput.BufferType;
if (bindInput.Buffer == null)
{
_bindParams[i].ResetBuffer();
}
else
{
_bindParams[i].SetBuffer(bindInput.Buffer);
}
if (bindInput.IsNull.HasValue)
{
_bindParams[i].SetIsNull(bindInput.IsNull.Value);
}
else
{
_bindParams[i].ResetIsNull();
}
if (bindInput.Length.HasValue)
{
_bindParams[i].SetLength((uint)bindInput.Length.Value);
}
else
{
_bindParams[i].ResetLength();
}
_bindParams[i].is_unsigned = (byte)(bindInput.IsUnsigned ? 1 : 0);
}
else
{
throw new NotSupportedException();
}
}
// link binds to stmt
if (Stmt.mysql_stmt_bind_param(_bindParams) != 0)
{
throw new MySqlException(Stmt);
}
}
/// <summary>
/// Construct and Run a simple insert command.
/// </summary>
/// <param name="conn">Database connection</param>
/// <param name="FieldsAndValues">Pairs of Fields and values as an object array. Examples:
/// <code>
/// new object[,]{{"SettingID",SettingID},{"SettingValue",Value}}
///
/// new object[,]{{"SettingID","times"},{"SettingValue",100}}
///
/// null
/// </code>
/// </param>
/// <param name="Table">Table name</param>
/// <example>Example:
/// <code>
/// using MySQLDriverCS;
/// ...
/// MySQLConnection DBConn;
/// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString );
/// DBConn.Open();
/// ...
/// new MySQLInsertCommand(
/// DBConn,
/// new object[,] {
/// {"SettingID",SettingID},
/// {"SettingValue",Value}
/// },
/// "Settings"
/// );
/// ...
/// DBConn.Close();
/// </code></example>
/// Modified by Claudia Murialdo (07/24/04) in order to support time
/// component of the datetime values and culture-independent
/// format of numeric values in a stmt.
public MySQLInsertCommand(
MySQLConnection conn,
object[,] FieldsAndValues,
string Table
)
{
if (FieldsAndValues == null)
{
throw new MySqlException("FieldsAndValues is null.");
}
if (FieldsAndValues.GetLength(0) == 0)
{
throw new MySqlException("FieldsAndValues is empty.");
}
string query = "insert into " + Table + " ( ";
int m;
for (m = 0; m < FieldsAndValues.GetLength(0); m++)
{
string Field = FieldsAndValues[m, 0] as string;
query += " " + Field.ToString() + " ";
if (m != (FieldsAndValues.GetLength(0) - 1))
{
query += " , ";
}
}
query += " ) VALUES ( ";
for (m = 0; m < FieldsAndValues.GetLength(0); m++)
{
string Field = FieldsAndValues[m, 0] as string;
object Value = FieldsAndValues[m, 1];
if (Value == null)
{
query += " NULL ";
}
else
if ((Value.GetType() == typeof(int)) ||
(Value.GetType() == typeof(long)) ||
(Value.GetType() == typeof(short)) ||
(Value.GetType() == typeof(decimal)) ||
(Value.GetType() == typeof(float)) ||
(Value.GetType() == typeof(double)))
{
query += " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " ";
}
else
if (Value.GetType() == typeof(bool))
{
bool bValue = (bool)Value;
string str = (bValue)? "1" : "0";
query += " " + str + " ";
}
else
if (Value.GetType() == typeof(DateTime))
{
DateTime dt = (DateTime)Value;
query += " \"" + dt.Year.ToString("D4") + "-" + dt.Month.ToString("D2") + "-" + dt.Day.ToString("D2") +
" " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + ((dt.Millisecond > 0)? "." + dt.Millisecond.ToString("D3") : "") + "\" ";
}
else
{
query += " " + MySQLUtils.Escape(Value.ToString(), conn) + " ";
}
if (m != (FieldsAndValues.GetLength(0) - 1))
{
query += " , ";
}
}
query += ") ;";
MySQLCommand command = new MySQLCommand(query, conn);
command.Prepare();
// Execute query ->
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new MySqlException(e.Message + " in query '" + query + "'");
}
// <- Execute query
command.Dispose();
Query = query;
bSuccess = true;
}
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query, a MySQLConnection, and the IDbTransaction.
/// </summary>
/// <param name="cmdText"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
public MySQLCommand(string cmdText, MySQLConnection connection, MySQLTransaction transaction) : this(cmdText, connection)
{
_transaction = transaction;
}
/// <summary>
/// Constructor from MySQLCommand and MySQLConnection
/// </summary>
/// <param name="strCommand"></param>
/// <param name="conn"></param>
public MySQLDataAdapter(String strCommand, MySQLConnection conn)
{
m_selectCommand = new MySQLCommand(strCommand, conn);
}
/// <summary>
/// A shortcut to make a simple update command with where part. (where would be null)
/// </summary>
/// <param name="conn">Database connection</param>
/// <param name="FieldsAndValues">Pairs of Fields and values as an object array. Examples:
/// <code>
/// new object[,]{{"SettingID",SettingID},{"SettingValue",Value}}
///
/// new object[,]{{"SettingID","times"},{"SettingValue",100}}
///
/// null
/// </code>
/// </param>
/// <param name="Table">Table name</param>
/// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example:
/// <code>
/// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}}
///
/// new object[,]{{"myfield","=",100}}
///
/// new object[,]{{"myfield","=",myvalue}}
///
/// null
/// </code>
/// </param>
/// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example:
/// <code>
/// new object[,]{{"myfield","=","myfield2"}}
///
/// null
/// </code>
/// </param>
/// <example>Example updating SettingValue by SettingID in Settings table<code>
/// using MySQLDriverCS;
/// ...
/// MySQLConnection DBConn;
/// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString );
/// DBConn.Open();
/// ...
/// new MySQLUpdateCommand(DBConn,
/// new object[,] {{"SettingValue",Value}},
/// "Settings",
/// new object[,] {{"SettingID","=",SettingID}},
/// null
/// );
/// ...
/// DBConn.Close();
/// </code></example>
/// Modified by Claudia Murialdo (07/24/04) in order to support time
/// component of the datetime values.
public MySQLUpdateCommand(
MySQLConnection conn, // Database connection
object[,] FieldsAndValues, // Pairs of Fields and values
string Table, // Table
object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing)
string[,] WhereNoparamFields // Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing)
)
{
if (FieldsAndValues == null)
{
throw new MySqlException("FieldsAndValues is null.");
}
if (FieldsAndValues.GetLength(0) == 0)
{
throw new MySqlException("FieldsAndValues is empty.");
}
string query = "update " + conn.Database + "." + Table + " set ";
ArrayList NewValues = new ArrayList();
for (int m = 0; m < FieldsAndValues.GetLength(0); m++)
{
string Field = FieldsAndValues[m, 0] as string;
object Value = FieldsAndValues[m, 1];
if (Value == null)
{
query += " " + Field + "=NULL ";
}
else
if (Value.GetType() == typeof(DateTime))
{
DateTime dt = (DateTime)Value;
query += " " + Field + "=\"" + dt.Year.ToString("D4") + "-" + dt.Month.ToString("D2") + "-" + dt.Day.ToString("D2") +
" " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + ((dt.Millisecond > 0) ? "." + dt.Millisecond.ToString("D3") : "") + "\" ";
}
else
if (Value.GetType() == typeof(bool))
{
bool bValue = (bool)Value;
string str = (bValue)? "1" : "0";
query += " " + Field + "=" + str + " ";
}
else
if (Value.GetType() == typeof(string))
{
string str = Value as string;
query += " " + Field + "=" + MySQLUtils.Escape(str, conn) + " ";
}
else
if (Value.GetType() == typeof(int))
{
query += " " + Field + "=" + ((int)Value).ToString() + " ";
}
if (m != (FieldsAndValues.GetLength(0) - 1))
{
query += ", ";
}
}
query += MySQLSelectCommand.WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn) + " ";
MySQLCommand command = new MySQLCommand(query, conn);
command.Prepare();
// Execute query ->
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new MySqlException(e.Message + " in query '" + query + "'");
}
// <- Execute query
command.Dispose();
Query = query;
bSuccess = true;
}
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query, a MySQLConnection, and the IDbTransaction.
/// </summary>
/// <param name="cmdText"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="usePreparedStatement"></param>
public MySQLCommand(string cmdText, MySQLConnection connection, MySQLTransaction transaction, bool usePreparedStatement) : this(cmdText, connection, transaction)
{
this.UsePreparedStatement = usePreparedStatement;
}
