public static List <Dictionary <string, object> > Execute(this MySqlConnection connection, string sqlCmd, int start, int limit)
{
Stack <Dictionary <string, object> > output = new Stack <Dictionary <string, object> >();
MySqlDataReader reader = null;
try
{
MySqlCommand cmd = new MySqlCommand(sqlCmd, connection);
reader = cmd.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> entry = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
entry[reader.GetDataTypeName(i)] = reader.GetValue(i);
}
output.Push(entry);
}
}
catch (MySqlException ex)
{
StaticLogger.Log(LogLevel.Error, "FAILED MySQL READ : " + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(output.ToList());
}
//--------------------------------------------------------------------------
//llena tabla
public static List <ClsClientes> MostrarDatos()
{
List <ClsClientes> lista = new List <ClsClientes>();
MySqlConnection conexion = ClsConexion.ObtenerConexion();
MySqlCommand _comando = new MySqlCommand(String.Format("SELECT * from tblcliente"), conexion);
MySqlDataReader _reader = _comando.ExecuteReader();
//conexion.Open();
while (_reader.Read())
{
ClsClientes Cliente = new ClsClientes();
Cliente.IdCliente = _reader.GetInt32(0);
Cliente.fechaRegistro = _reader.GetString(1);
Cliente.Nombre = _reader.GetString(2);
Cliente.Apellidos = _reader.GetString(3);
Cliente.Direccion = _reader.GetString(4);
Cliente.Correo = _reader.GetString(5);
Cliente.Telefono = _reader.GetString(6);
Cliente.FechaN = _reader.GetDataTypeName(7);
Cliente.IMC = _reader.GetDouble(8);
Cliente.Estado = _reader.GetInt32(9);
lista.Add(Cliente);
}
conexion.Close();
return(lista);
}
private List <string[]> ExecuteLineReads(string cmd)
{
List <string[]> lResult = new List <string[]>();
try
{
MySqlCommand sqlcmd = new MySqlCommand(cmd, sqlcon_);
sqlcon_.Open();
MySqlDataReader reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
string[] sLine = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("VARCHAR"))
{
sLine[i] = reader[i].ToString();
}
}
lResult.Add(sLine);
}
}
catch (SystemException ex)
{
Trace.WriteLine(string.Format("{0}(): {1}", System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message), "ERROR");
}
sqlcon_.Close();
return(lResult);
}
public override string GetDataTypeName(int ordinal)
{
if (types != null)
{
return(types[ordinal].Name);
}
return(reader.GetDataTypeName(ordinal));
}
public void SetFields(MySqlDataReader dataReader)
{
for (var col = 0; col < dataReader.FieldCount; col++)
{
AddField(
dataReader.GetName(col).ToString(),
dataReader.GetFieldType(col),
dataReader.GetDataTypeName(col)
);
}
}
/// <summary>
/// Check existing data in SQL table.
/// </summary>
/// <param name="data">The string value to check for.</param>
/// <returns>Returns TRUE if data already exists.</returns>
private bool CheckExistingData(string data)
{
ConfigHandling cfg_ = new ConfigHandling();
string[] sTypes = cfg_.GetAllTypes();
string sCmdSub = string.Empty;
bool result = false;
if (data.Contains(" "))
{
Trace.WriteLine(string.Format("Whitespace are not allowed ({0})", data), "ERROR");
return(true);
}
if (sTypes.Length > 0)
{
for (int i = 0; i < sTypes.Length; i++)
{
sCmdSub += string.Format(" OR {0}='{1}'", sTypes[i], data);
}
}
string sCmd = string.Format("SELECT * FROM {0} WHERE Serialnumber='{1}'{2}", tablename_, data, sCmdSub);
try
{
MySqlCommand sqlcmd = new MySqlCommand(sCmd, sqlcon_);
sqlcon_.Open();
MySqlDataReader reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
string tmp = null;
if (reader.FieldCount > 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("VARCHAR"))
{
tmp += reader[i].ToString() + " - ";
}
}
}
Trace.WriteLine(String.Format("Found in table: {0}", tmp));
result = true;
}
}
catch (SystemException ex)
{
Trace.WriteLine(string.Format("{0}(): {1}", System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message), "ERROR");
}
sqlcon_.Close();
return(result);
}
private void queryReadInteral(ref Result result, ref MySqlCommand command)
{
// Create reader
MySqlDataReader reader = command.ExecuteReader();
// Read row-by-row
byte[] buffer;
MemoryStream bufferMS;
int bufferOffset;
int bytesAvailable;
ResultRow row;
int t;
while (reader.Read())
{
row = new ResultRow();
for (t = 0; t < reader.FieldCount; t++)
{
row.attributes.Add(reader.GetName(t), reader.IsDBNull(t) ? null : reader.GetValue(t));
// Check if the column of the row is a byte-array, if so -> add to separate byte dictionary
string dt = reader.GetDataTypeName(t);
if (dt == "BLOB" || dt == "TINYBLOB" || dt == "MEDIUMBLOB" || dt == "LONGBLOB")
{
bufferMS = new MemoryStream();
try
{
bufferOffset = 0;
bytesAvailable = (int)reader.GetBytes(t, 0, null, 0, 0);
while (bufferOffset < bytesAvailable)
{
reader.GetBytes(t, bufferOffset, buffer = new byte[BYTE_BUFFER_SIZE], 0, BYTE_BUFFER_SIZE);
bufferMS.Write(buffer, 0, BYTE_BUFFER_SIZE);
bufferOffset += BYTE_BUFFER_SIZE;
}
bufferMS.Flush();
if (row.attributesByteArray == null)
{
row.attributesByteArray = new Dictionary <string, byte[]>();
}
row.attributesByteArray.Add(reader.GetName(t), bufferMS.ToArray());
}
catch { }
finally
{
bufferMS.Dispose();
}
}
}
result.tuples.Add(row);
}
reader.Close();
}
public void NumericAsBinary()
{
MySqlCommand cmd = new MySqlCommand("SELECT IFNULL(NULL,0) AS MyServerID", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual("BIGINT", reader.GetDataTypeName(0));
Assert.AreEqual(typeof(Int64), reader.GetFieldType(0));
Assert.AreEqual("System.Int64", reader.GetValue(0).GetType().FullName);
Assert.AreEqual(0, reader.GetValue(0));
}
}
public List <Object[]> ExecuteQuery(string sql)
{
List <Object[]> lResult = new List <object[]>();
MySqlCommand mySqlCommand = getSqlCommand(sql);
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
Object[] row = new Object[reader.FieldCount];
for (int i = 0; i < row.Length; ++i)
{
string fieldType = reader.GetDataTypeName(i).ToUpper();
switch (fieldType)
{
case "VARCHAR":
row[i] = reader.GetString(i);
break;
case "INT":
row[i] = reader.GetInt32(i);
break;
default:
break;
}
}
lResult.Add(row);
}
}
}
catch (Exception)
{
// LogOut.WriteLine("查询失败了！");
}
finally
{
reader.Close();
}
return(lResult);
}
public static DateTime GetDbDateTime(MySqlDataReader myReader, int myColumnNo)
{
DateTime myData = new DateTime(1900, 1, 1);
string data = "";
string datatype = "";
try
{
if (!myReader.IsDBNull(myColumnNo))
{
datatype = myReader.GetDataTypeName(myColumnNo);
if (datatype.ToLower().Equals("datetime") || datatype.ToLower().Equals("date"))
{
myData = myReader.GetDateTime(myColumnNo);
}
else if (datatype.ToLower().Equals("varchar"))
{
data = myReader.GetString(myColumnNo);
//Debug.Print("varchar data [" + data + "]");
if (data.Length <= 0)
{
myData = new DateTime(1900, 1, 1);
}
else if (data.IndexOf("x") >= 0)
{
Debug.Print("varchar mask data [" + data + "]");
}
else
{
myData = Convert.ToDateTime(data);
}
}
}
else
{
myData = new DateTime(1900, 1, 1);
}
}
catch (Exception)
{
myData = new DateTime(1900, 1, 1);
}
return(myData);
}
public static object TryParse(this MySqlDataReader read, int index)
{
try
{
var str = read.GetDataTypeName(index);
switch (str)
{
case "TINYINT":
return(read.GetByte(index));
case "SMALLINT":
return(read.GetInt16(index));
case "INT":
case "MEDIUMINT":
return(read.GetInt32(index));
case "BIGINT":
return(read.GetInt64(index));
case "FLOAT":
return(read.GetFloat(index));
case "DOUBLE":
return(read.GetDouble(index));
case "DECIMAL":
return(read.GetDecimal(index));
case "TIMESTAMP":
return(read.GetTimeSpan(index));
case "DATETIME":
return(read.GetMySqlDateTime(index));
default:
return(read.GetValue(index) as string);
}
}
catch
{
return(0);
}
}
/// <summary>
/// Lấy dữ liệu theo tên store truyền vào
/// </summary>
/// <param name="storeName">Tên store</param>
/// <returns>IEnumerable</returns>
/// Create By: NVMANH (20/10/2019)
public IEnumerable <Customer> GetEntities(string storeName)
{
using (DatabaseContext databaseContext = new DatabaseContext())
{
MySqlDataReader sqlDataReader = databaseContext.ExecuteReader(storeName);
while (sqlDataReader.Read())
{
var entity = Activator.CreateInstance <Customer>();
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
var fieldType = sqlDataReader.GetDataTypeName(i);
var fieldName = sqlDataReader.GetName(i);
var fieldValue = sqlDataReader.GetValue(i);
var property = entity.GetType().GetProperty(fieldName);
if (fieldValue != System.DBNull.Value && property != null)
{
if (fieldType == "BIT")
{
if ((UInt64)fieldValue == 0)
{
property.SetValue(entity, false);
}
else if ((UInt64)fieldValue == 1)
{
property.SetValue(entity, true);
}
continue;
}
//if (property.PropertyType == typeof(Guid) && fieldValue.GetType() == typeof(String))
if ((property.PropertyType == typeof(Guid) || property.PropertyType == typeof(Guid?)) && fieldValue.GetType() == typeof(String))
{
property.SetValue(entity, Guid.Parse((string)fieldValue));
}
else
{
property.SetValue(entity, fieldValue);
}
}
}
yield return(entity);
}
}
}
// !!In this method you can config how your DB-data should be written to your list!!
private string readHandling(MySqlDataReader pDataReader, int pIndex)
{
//Usually if you convert a type DATE into string, the time gets added to. Here we don't want that.
if (pDataReader.GetDataTypeName(pIndex) == "DATE")
{
try
{
DateTime dateOutput = new DateTime();
dateOutput = pDataReader.GetDateTime(pIndex);
//********************* An example what you can do: *********************
//For an unknown date I like to save it as 01.01.1000: Here I convert the value back to "unknown"
//I'm from Europe so we use the calender in this format: :D
if (dateOutput.ToString("dd/MM/yyyy") == "01.01.1000")
{
return("unknown");
}
else
{
return(dateOutput.ToString("dd/MM/yyyy"));
}
//********************* delete upper section if you don't wanna have it *********************
//than uncomment this:
//return dateOutput.ToString("dd/MM/yyyy");
}
catch
{
return("FAILURE: readerHandling! DATE");
}
}
else if (false)
{
//If you wanna have another special read handling
}
else
{
return(pDataReader.GetString(pIndex));
}
}
private List <Dictionary <string, string> > obtenerValoresItems(Dictionary <string, CamposImpresion> camposImpresion, int Id)
{
List <Dictionary <string, string> > retorno = new List <Dictionary <string, string> >();
MySqlConnection cone = new MySqlConnection(ConfigurationManager.ConnectionStrings["ce"].ConnectionString);
MySqlCommand cmd = new MySqlCommand("select * from vw_comprobanteitem " +
"where cen_id = @cen_id", cone);
try
{
cmd.Parameters.AddWithValue("@cen_id", Id);
//lstDatos = new List<datos>();
cone.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Dictionary <string, string> item = new Dictionary <string, string>();
for (int i = 0; i < dr.FieldCount; i++)
{
datos d = new SGECA.ColaDeImpresion.datos();
d.Ordinal = dr.GetSchemaTable().Rows[i][0].ToString().ToUpper();
d.Valor = dr[i];
d.Tipo = dr.GetDataTypeName(i).ToUpper();
CamposImpresion c;
if (camposImpresion.TryGetValue(d.Ordinal, out c))
{
c.Datos = d;
string key = c.Codigo;
string valor = formatearDatos(c);
item.Add(key, valor);
}
}
retorno.Add(item);
}
cone.Close();
}
catch (Exception Exception)
{
LogManager.Mensaje m = new LogManager.Mensaje("SGECA.ColaDeImpresion.builders.Documento",
"obtenerValoresItems",
0,
"Error al intentar obtener los valores de items.",
Exception.Message,
"",
true,
LogManager.EMensaje.Critico,
Exception.StackTrace);
Notify(m);
retorno = null;
}
finally
{
if (cone.State != ConnectionState.Closed)
{
cone.Close();
}
}
return(retorno);
}
public override string GetDataTypeName(int ordinal)
{
return(_reader.GetDataTypeName(ordinal));
}
public string GetDataTypeName(int i)
{
return(reader.GetDataTypeName(i));
}
public string GetDataTypeName(int i)
{
return(_mySqlDataReader.GetDataTypeName(i));
}
/// <summary>
/// 显示命令效果
/// 简单的数据显示
/// </summary>
/// <param name="reader"></param>
public static void ShowData(MySqlDataReader reader)
{
if (reader == null || reader.IsClosed == true)
{
return;
}
bool result = false;
while (reader.Read())
{
result = true;
_strShowBuilder.Clear();
_strShowBuilder.Append("命令执行成功: ");
for (int i = 0; i < reader.FieldCount - 1; i++)
{
_strShowBuilder.Append("第" + i + "列:" + "(列名:" + reader.GetName(i) + " 类型:" + reader.GetDataTypeName(i) + ") " + reader.GetValue(i) + " | ");
}
int temp = reader.FieldCount - 1;
_strShowBuilder.Append("第 " + temp + " 列:" + "(列名:" + reader.GetName(temp) + " 类型:" + reader.GetDataTypeName(temp) + ") " + reader.GetValue(temp));
Console.WriteLine(_strShowBuilder.ToString());
}
if (result == false)
{
Console.WriteLine("该命令，没有获取任何数据");
}
ResetReader(reader);
}
public static void Main(string[] args)
{
// Build the connection string
MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.Server = db_host;
connBuilder.Port = db_port;
connBuilder.UserID = db_username;
connBuilder.Password = db_password;
connBuilder.Database = db_name;
try
{
using (MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString))
{
// Connect the database
conn.Open();
Console.WriteLine("Data source: {0}", conn.DataSource);
Console.WriteLine("Server version: {0}", conn.ServerVersion);
Console.WriteLine("Server thread: {0}", conn.ServerThread);
Console.WriteLine("Database: {0}", conn.Database);
Console.WriteLine("Connection timeout: {0}", conn.ConnectionTimeout);
Console.WriteLine();
// UPDATE statement
string new_comment = "C# " + GetNow();
string sql0 = String.Format("update {0} set {1}={2} where {3}!={4}", db_table, db_update_column, db_update_column_variable, db_column, db_column_variable);
Console.WriteLine(sql0);
using (var cmd = new MySqlCommand(sql0, conn))
{
cmd.Parameters.AddWithValue(db_update_column_variable, new_comment);
cmd.Parameters.AddWithValue(db_column_variable, db_column_value);
int updatedRows = cmd.ExecuteNonQuery();
Console.WriteLine("Total updated rows: {0}", updatedRows);
}
Console.WriteLine();
// Full SELECT statement
string sql1 = String.Format("select * from {0}", db_table);
Console.WriteLine(sql1);
using (var cmd = new MySqlCommand(sql1, conn))
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int columns = reader.FieldCount;
Console.WriteLine("Total columns: {0}", columns);
for (int ii = 0; ii < columns; ii++)
{
Console.WriteLine(" - {0} {1}", reader.GetName(ii), reader.GetDataTypeName(ii));
}
int number = 0;
while (reader.Read())
{
number++;
Console.Write(number);
for (int ii = 0; ii < columns; ii++)
{
string type = reader.GetDataTypeName(ii);
string value = "?";
if (!reader.IsDBNull(ii))
{
if (type.EndsWith("CHAR"))
{
value = reader.GetString(ii);
}
else if (type.Equals("DATETIME"))
{
value = reader.GetDateTime(ii).ToString();
}
else if (type.EndsWith("INT"))
{
value = reader.GetInt32(ii).ToString();
}
}
else
{
value = "(null)";
}
Console.Write(" '{0}'", value);
}
Console.WriteLine();
}
}
Console.WriteLine();
// SELECT WHERE statement
string sql2 = String.Format("select count(*) from {0} where {1}!={2}", db_table, db_column, db_column_variable);
Console.WriteLine(sql2);
using (var cmd = new MySqlCommand(sql2, conn))
{
cmd.Parameters.AddWithValue(db_column_variable, db_column_value);
Object result = cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
Console.WriteLine();
// SELECT function statement
string sql3 = String.Format("select factorial({0})", db_factorial_variable);
Console.WriteLine(sql3);
using (var cmd = new MySqlCommand(sql3, conn))
{
cmd.Parameters.AddWithValue(db_factorial_variable, db_factorial_value);
Object result = cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
Console.WriteLine();
// CALL procedure statement
string sql4 = String.Format("add_and_subtract");
Console.WriteLine(sql4);
using (var cmd = new MySqlCommand(sql4, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(db_add_and_subtract_a_variable, db_add_and_subtract_a_value);
cmd.Parameters.AddWithValue(db_add_and_subtract_b_variable, db_add_and_subtract_b_value);
MySqlParameter par3 = new MySqlParameter(db_add_and_subtract_x_variable, MySqlDbType.Int32);
par3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par3);
MySqlParameter par4 = new MySqlParameter(db_add_and_subtract_y_variable, MySqlDbType.Int32);
par4.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par4);
cmd.ExecuteNonQuery();
Console.WriteLine("Result x: {0}", cmd.Parameters[db_add_and_subtract_x_variable].Value);
Console.WriteLine("Result y: {0}", cmd.Parameters[db_add_and_subtract_y_variable].Value);
}
}
}
catch (MySqlException mex)
{
Console.WriteLine("MySQL error {0}: {1}", mex.Number, mex.Message);
}
}
/// <summary>
/// 获取内容页模板
/// </summary>
/// <returns></returns>
public static TemplateInfo getContentTemplate(string url, bool isMobile)
{
url = url.Replace("." + BaseConfig.extension, "");
Dictionary <string, object> variable = new Dictionary <string, object>();
TemplateInfo info = null;
double dataTypeId = 0, rootId = 0, moduleId = 0, skinId = 0, id = 0, classId = 0;
MySqlDataReader rs = Sql.ExecuteReader("select dataTypeId,rootId,moduleId,skinId,id,title,createDate,updateDate,classId,clickCount,url,pic,userId from maintable where url=@url and orderId>-1", new MySqlParameter[] {
new MySqlParameter("url", url)
});
if (rs.Read())
{
dataTypeId = rs.GetDouble(0);
rootId = rs.GetDouble(1);
moduleId = rs.GetDouble(2);
skinId = rs.GetDouble(3);
id = rs.GetDouble(4);
classId = rs.GetDouble(8);
for (int i = 0; i < rs.FieldCount; i++)
{
if (i == 10)
{
variable[rs.GetName(i)] = Config.webPath + rs[i] + "." + BaseConfig.extension;
}
else
{
variable[rs.GetName(i)] = rs[i];
}
}
}
rs.Close();
if (rootId == 0)
{
return(null);
}
if (skinId == 0)
{
rs = Sql.ExecuteReader("select " + (isMobile ? "_contentSkinID" : "contentSkinID") + " from class where id=@id", new MySqlParameter[] { new MySqlParameter("id", classId) });
if (rs.Read())
{
skinId = rs.IsDBNull(0) ? 0 : rs.GetDouble(0);
}
rs.Close();
}
string tableName = (string)Sql.ExecuteScalar("select tableName from datatype where id=" + dataTypeId.ToString());
rs = Sql.ExecuteReader("select * from " + tableName + " where id=@id", new MySqlParameter[] {
new MySqlParameter("id", id)
});
if (rs.Read())
{
for (int i = 0; i < rs.FieldCount; i++)
{
string fieldName = rs.GetName(i);
if (rs.IsDBNull(i))
{
variable[rs.GetName(i)] = "";
}
else
{
if (rs.GetDataTypeName(rs.GetOrdinal(fieldName)) == "ntext")
{
SystemLink v1 = new SystemLink();
string FieldValue = rs[i] + "";
FieldValue = v1.Replace(FieldValue);
variable[fieldName] = FieldValue;
}
else
{
variable[fieldName] = rs[i];
}
}
}
}
rs.Close();
if (skinId > 0)
{
info = get(skinId);
}
if (info == null)
{
MySqlDataReader rs2 = Sql.ExecuteReader("select B.title,B.u_content,B.u_editboxStatus,B.u_parameterValue,B.id,B.classId from template B where B.ClassID in (0,@moduleId,@rootId) and B.u_datatypeid=@datatypeId and B.u_type=@typeId and B.u_defaultFlag=1 and B.u_webFAid=@webFAid order by u_layer desc limit 0,1",
new MySqlParameter[] {
new MySqlParameter("moduleId", moduleId),
new MySqlParameter("rootId", rootId),
new MySqlParameter("datatypeid", dataTypeId),
new MySqlParameter("typeId", 2),
new MySqlParameter("webFAid", isMobile?1:0)
});
if (rs2.Read())
{
info = new TemplateInfo();
info.id = rs2.GetDouble(4);
info.title = rs2[0].ToString();
info.u_content = rs2[1].ToString();
info.u_editboxStatus = rs2.GetBoolean(2) ? 1 : 0;
info.u_parameterValue = rs2[3].ToString();
info.classId = rs2.GetDouble(5);
info.u_type = 2;
}
rs2.Close();
}
if (info != null)
{
info.variable = variable;
}
return(info);
}
public static List <List <string> > ToTableList(this MySqlDataReader reader)
{
//所有行
var rows = new List <List <string> >();
if (reader.HasRows)
{
while (reader.Read())
{
//逐行添加内容
var cols = new List <string>();
for (var i = 0; i < reader.FieldCount; i++)
{
var value = "";
#region 根据格式取值
var type = reader.GetDataTypeName(i).ToLower();
if (reader.IsDBNull(i))
{
value = "";
}
else if (type == ("smallint"))
{
value = reader.GetInt16(i) + "";
}
else if (type.Contains("int"))
{
value = reader.GetInt32(i) + "";
//
//var v = reader.GetInt32(i);
//if (v > 1000000000)
//{
// value = reader.GetInt32(i).ToDateTime().ToStr();
//}
//else
//{
// value = reader.GetInt32(i) + "";
//}
}
else if (type.Contains("float"))
{
value = reader.GetDouble(i) + "";
}
else if (type.Contains("date"))
{
value = reader.GetDateTime(i).ToString("yyyy-MM-dd") + "";
}
else if (type.Contains("datetime"))
{
value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm") + "";
}
else if (type.Contains("char") || type.Contains("text"))
{
value = reader.GetString(i) + "";
}
else
{
value = reader.GetString(i) + "";
}
#endregion
cols.Add(value);
}
rows.Add(cols);
}
}
reader.Close();
reader.Dispose();
return(rows);
}
public override List <List <DataCollection> > Index(string table, OrderBy order)
{
// returns a dynamic list of RECORDS/ROWS, each of there are List<object>
List <List <DataCollection> > res = new List <List <DataCollection> >();
try
{
//Connect
this.Connect();
//Create and select query
string query = $"SELECT * FROM {table} WHERE 1 ORDER BY {order.Name} {order.OrderCriteria}";
//instantiate the MySql command
com = new MySqlCommand(query, con);
//execute (READER) the query
MySqlDataReader dr = com.ExecuteReader();
//parse the dataReader
//Is there any Records/Rows from SELECT
if (dr.HasRows)
{
while (dr.Read())
{
//Create a List<DataCollection> for each Record
//List<object> row = new List<object>();
List <DataCollection> data = new List <DataCollection>();
Console.WriteLine(dr.FieldCount);
//Read every column of each of the rows
for (int i = 0; i < dr.FieldCount; i++)
{
DataCollection item = new DataCollection(dr.GetName(i), Types.VARCHAR, dr.GetValue(i));
item.ThisFieldType(dr.GetDataTypeName(i));
data.Add(item);
//row.Add(dr.GetValue(i));
}
//ADD this list to the res collection
res.Add(data);
}
}
else
{
BD.ERROR = "EMPTY TABLE, THERE IS NO ROWS IN THE RESULT";
}
}
catch (MySqlException mysqlex)
{
BD.ERROR = $"MySql ERROR WHILE READING TABLE: -{table}- index. {mysqlex.Message}";
}
catch (IOException ioex)
{
BD.ERROR = $"ERROR OF COMIUNICATION WHILE READING TABLE: -{table}- index. {ioex.Message}";
}
catch (Exception ex)
{
BD.ERROR = $"GENERAL ERROR OF READING TABLE: -{table}- index. {ex.Message}";
}
finally
{
//Closes Connection
this.Disconnect();
}
//return the Records
return(res);
}
public Table GetTable(string tablename)
{
Table table = null;
List <ColumnStructure> information = new List <ColumnStructure>();
using (MySqlTransaction transaction = Connection.Instance.BeginTransaction()) {
//Structure
string query = "DESC " + tablename;
MySqlCommand command = Connection.Instance.GetCommand(query, transaction);
using (MySqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
ColumnStructure cs = new ColumnStructure();
cs.ColumnName = DbUtils.GetString(reader["Field"]);
cs.DataType = DbUtils.GetString(reader["Type"]);
cs.KeyType = DbUtils.GetString(reader["Key"]);
cs.IsNullable = DbUtils.GetString(reader["Null"]);
cs.Extra = DbUtils.GetString(reader["Extra"]);
information.Add(cs);
}
}
//Data
table = new Table(tablename, information);
query = "SELECT * from " + tablename;
MySqlCommand commandData = Connection.Instance.GetCommand(query, transaction);
using (MySqlDataReader reader = commandData.ExecuteReader()) {
while (reader.Read())
{
DataRow row = new DataRow();
row.ColumnStructure = information;
for (int i = 0; i < reader.FieldCount; i++)
{
row.Attributes.Add(new Flag(reader.GetName(i), reader[i].ToString(), reader.GetDataTypeName(i)));
}
table.DataRows.Add(row);
}
}
transaction.Commit();
}
return(table);
}
public override List <List <DataCollection> > Read(List <string> fields, string table1, string table2, List <string> onFields, List <SearchCollection> search)
{
// returns a dynamic list of RECORDS/ROWS, each of there are List<object>
List <List <DataCollection> > res = new List <List <DataCollection> >();
try
{
//Connect
this.Connect();
//Parse the fields collections
string parseFields = "";
foreach (string col in fields)
{
parseFields += $"{col},";
}
//remove last comma
parseFields = parseFields.Remove(parseFields.Length - 1);
//parse search collection
string parseWhere = "";
foreach (SearchCollection criteria in search)
{
parseWhere += $"{criteria.Name} {criteria.ParseOperator(criteria.Operator)} {criteria.Value} {criteria.ParseLogicOperator(criteria.LogicOp)}";
}
//Parse the onQuery
//the Onstring
string onInnerString = "";
foreach (string col in onFields)
{
onInnerString += $" {col}";
}
//Create and select query
string query = $"SELECT {parseFields} FROM {table1} INNER JOIN {table2} ON {onInnerString} WHERE {parseWhere} ";
//instantiate the MySql command
com = new MySqlCommand(query, con);
//execute (READER) the query
MySqlDataReader dr = com.ExecuteReader();
//parse the dataReader
//Is there any Records/Rows from SELECT
if (dr.HasRows)
{
while (dr.Read())
{
//Create a List<object> for each Record
List <DataCollection> row = new List <DataCollection>();
DataCollection item;
//Read every column of each of the rows
for (int i = 0; i < dr.FieldCount; i++)
{
item = new DataCollection(dr.GetName(i), Types.VARCHAR, dr.GetValue(i));
item.ThisFieldType(dr.GetDataTypeName(i));
row.Add(item);
}
//ADD this list to the res collection
res.Add(row);
}
}
else
{
BD.ERROR = "EMPTY TABLE, THERE IS NO ROWS IN THE RESULT ON INNER JOIN QUERY";
}
}
catch (MySqlException mysqlex)
{
BD.ERROR = $"MySql ERROR WHILE READING TABLES: {table1} , {table2}. {mysqlex.Message}";
}
catch (IOException ioex)
{
BD.ERROR = $"ERROR OF COMIUNICATION WHILE READING TABLES: {table1} , {table2}. {ioex.Message}";
}
catch (Exception ex)
{
BD.ERROR = $"GENERAL ERROR OF READING TABLES: {table1} , {table2}. {ex.Message}";
}
finally
{
//Closes Connection
this.Disconnect();
}
//return the Records
return(res);
}
public string GetDataTypeName(int ordinal) => _dataReader.GetDataTypeName(ordinal);