private void TestADO()
{
var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select * from customers";
var rdr = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(rdr);
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Response.Write(row[col.ColumnName] + ", ");
}
Response.Write("<br />");
}
rdr.Close();
rdr.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
public IEnumerable <BeansPersona> GetListPersonaRepository()
{
IEnumerable <BeansPersona> oData = null;
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
try
{
//oData = connection.Query<BeansPersona>("ppanana.mysql.database.azure.com.examen.SP_GetPersona", commandType: System.Data.CommandType.StoredProcedure);
oData = connection.Query <BeansPersona>("SELECT nId ID , vNombres Nombres , vApellidos Apellidos , nEdad Edad , dteFechaNacimiento FechaNacimiento FROM personas", System.Data.CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Dispose();
connection.Close();
}
}
return(oData);
}
/// <summary>
/// Rolls back the transaction if it hasn't been completed
/// </summary>
public void Dispose()
{
if (_disposed)
{
return;
}
try
{
try
{
if (_currentTransaction == null)
{
return;
}
using (_currentTransaction)
{
try
{
_currentTransaction.Rollback();
}
catch { }
_currentTransaction = null;
}
}
finally
{
_currentConnection.Dispose();
}
}
finally
{
_disposed = true;
}
}
private void add_read_send(object sender, EventArgs e)
{
try
{
//Connect to Database
my.MySqlClient.MySqlConnection conn = connectToDatabase();
//Add Data to table using LocalSetupInsert - needs altering
LocalSetupInsert(conn);
//Read Data from table using LocalSetupGet
LocalSetupGet(conn);
//Save data into csv file
//save_into_csv(results);
//close connection to table
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
//call send_email function
send_email("*****@*****.**");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
public void TestConnection()
{
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnStr);
Conn.Open();
Conn.Close();
Conn.Dispose();
}
public void CreateMySqlCommand(string myExecuteQuery, MySql.Data.MySqlClient.MySqlConnection myConnection)
{
try
{
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
connection.Close();
connection.Dispose();
}
}
/// <summary>
/// Responsible for cleaning up the class
/// </summary>
public void Dispose()
{
if (null != m_Connection)
{
m_Connection.Close();
m_Connection.Dispose();
m_Connection = null;
}
}
Boolean vWait = true; //Aguarda backup do banco antes do backup de arquivos.
public frmBackup()
{
InitializeComponent();
try
{ // Abrir o arquivo para autenticar o servidor.
using (StreamReader sr = new StreamReader(@"C:\Program Files (x86)\GIPP\autentication"))
{
// Ler o conteudo do arquivo para uma string.
String line = sr.ReadToEnd();
connection = new MySql.Data.MySqlClient.MySqlConnection(line);
}
}
catch (Exception ex)
{
MessageBox.Show("Arquivo não pode ser lido: " + ex.Message, "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
this.notifyIcon.ContextMenuStrip = new System.Windows.Forms.ContextMenuStrip(); //Cria e configura o icone de notificação do Backup.
this.notifyIcon.ContextMenuStrip.Items.Add("Mostrar");
this.notifyIcon.ContextMenuStrip.Items.Add("Ocultar");
this.notifyIcon.ContextMenuStrip.Items.Add("Fechar");
this.notifyIcon.ContextMenuStrip.ItemClicked += new System.Windows.Forms.ToolStripItemClickedEventHandler(this.notifyIcon_MenuClick);
cmd.Connection = connection;
try
{
connection.Open();
cmd.CommandText = "SELECT data_last_backup FROM system_checks WHERE id = '1'";
lblBakData.Text = (string)cmd.ExecuteScalar();
cmd.CommandText = "SELECT system_last_backup FROM system_checks WHERE id = '1'";
lblBakFiles.Text = (string)cmd.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Erro! " + ex.Message);
}
finally
{
connection.Close();
connection.Dispose();
}
lblFiles.Text = "Verificando backup...";
vError = false;
u = new Thread(uThreadProcess);
cmbBkpTime.Text = "24"; //A Thread 'u' será iniciada dentro de cmbBkpTime_SelectedIndexChanged.
vTime = "24";
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.IsOpen)
{
dbc.Close();
}
if (dbc != null)
{
dbc.Dispose();
dbc = null;
}
//throw new NotImplementedException();
}
public static bool Check_Connection(string connection_string)
{
try
{
MySql.Data.MySqlClient.MySqlConnection Connection = new MySql.Data.MySqlClient.MySqlConnection(connection_string);
Connection.Open();
Connection.Close();
Connection.Dispose();
return(true);
}
catch
{
return(false);
}
}
private void ReiniciarConexion()
{
try
{
MySQLConn.Close();
}
catch (System.Exception)
{
MySQLConn.Dispose();
}
finally
{
MySQLConn.Open();
}
}
public BeansResponse UpdPersonaResponse(BeansPersonaR oPersona, int nId)
{
string[] dteFecha = oPersona.FechaNacimiento.ToShortDateString().Split('/');
BeansResponse oResponse = new BeansResponse();
try
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
var parameter = new DynamicParameters();
connection.Open();
try
{
//parameter.Add("@Nombres", oPersona.Nombres);
//parameter.Add("@Apellido", oPersona.Apellidos);
//parameter.Add("@Edad", oPersona.Edad);
//parameter.Add("@FechaNacimiento", oPersona.FechaNacimiento);
string sql = "update Personas set nEdad =@Edad , dteFechaNacimiento =@FechaNacimiento where nId=@nId";
sql = sql.Replace("@nId", nId.ToString()).Replace("@Apellido", '"' + oPersona.Apellidos + '"').Replace("@Edad", oPersona.Edad.ToString()).Replace("@FechaNacimiento", '"' + dteFecha[2] + "-" + dteFecha[1] + "-" + dteFecha[0] + '"');
//var data = connection.Query<BeansPersona>("examen.InsPersona", param: parameter, commandType: System.Data.CommandType.StoredProcedure);
var data = connection.Query <BeansPersona>(sql, commandType: System.Data.CommandType.Text);
oResponse.bStatus = true;
oResponse.vResponse = "Se actualizo correctamente";
}
catch (Exception ex)
{
oResponse.bStatus = false;
oResponse.vResponse = "No se pudo actualizar";
throw ex;
}
finally
{
connection.Dispose();
connection.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(oResponse);
}
private void 显示_Click(object sender, EventArgs e)
{
string myConnectionString;
myConnectionString = "server=localhost;uid=root;password=****;port=3306;database=test01";
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
conn.Open();
string cmd = "SELECT * FROM revitDatas";
MySql.Data.MySqlClient.MySqlCommand mscmd = new MySql.Data.MySqlClient.MySqlCommand(cmd, conn);
MySql.Data.MySqlClient.MySqlDataAdapter adpter = new MySql.Data.MySqlClient.MySqlDataAdapter(mscmd);
DataTable table = new DataTable();
adpter.Fill(table);
dataGridView1.DataSource = table;
conn.Close();
conn.Dispose();
}
public int ExecuteBulk(string SqlStatment)
{
MySql.Data.MySqlClient.MySqlConnection Cn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
int result = -1;
if (Cn.State != ConnectionState.Open)
{
Cn.Open();
}
MySql.Data.MySqlClient.MySqlCommand command = Cn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction;
// Start a local transaction
transaction = Cn.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
foreach (string sql in SqlStatment.Split(';'))
{
if (!String.IsNullOrEmpty(sql))
{
command.CommandText = sql;
result = command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback(); throw e;
}
finally
{
Cn.Close();
Cn.Dispose();
MySql.Data.MySqlClient.MySqlConnection.ClearPool(Cn);
}
return(result);
}
public string LoadCvsFileIntoMySqlTable(string csvFilePath, string tableName, string connectionString)
{
DataTable dtTarget = new DataTable("target"); // target is the data to be loaded into the MySql table.
string errorMessage = "";
System.Console.WriteLine($"Connecting to mySql...");
try
{
using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
errorMessage = GetTargetTableStructure(tableName, connection, ref dtTarget);
if (errorMessage == "")
{
System.Console.WriteLine($"Table '{tableName}' has {dtTarget.Columns.Count} columns");
}
if (errorMessage == "")
{
errorMessage = ReadCsvFile(csvFilePath, ref dtTarget, tableName, connection);
System.Console.WriteLine($"Data file '{csvFilePath}' loaded with: {dtTarget.Rows.Count} rows {dtTarget.Columns.Count} columns.");
}
if (errorMessage == "")
{
errorMessage = LoadDataToTargetTable(tableName, connection, ref dtTarget);
}
connection.Close();
connection.Dispose();
}
else
{
errorMessage = $"ERROR: Connection state is '{connection.State.ToString()}'";
}
}
}
catch (System.Exception e)
{
errorMessage = e.Message;
}
return errorMessage;
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (_MyConnection != null)
{
if (_MyConnection.State == System.Data.ConnectionState.Open)
{
_MyConnection.Close();
}
_MyConnection.Dispose();
}
}
CloseHandle(handle);
handle = System.IntPtr.Zero;
}
disposed = true;
}
private bool TestMysqlConnection()
{
string connStr = "server=" + tbIP.Text + ";port=3306;user="******";password="******"; database=" + tbDBName.Text + ";";
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
try
{
conn.Open();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
conn.Close();
conn.Dispose();
}
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
MySql.Data.MySqlClient.MySqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (MySql.Data.MySqlClient.MySqlCommand) CommandsCollection[storedProcedure];
}
else
{
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new MySql.Data.MySqlClient.MySqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (MySql.Data.MySqlClient.MySqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
public static List <string> Get(string sql, params object[] parameters)
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection();
connection.ConnectionString = "server=localhost;userid=root;pwd=wm360;port=3308;database=gb;sslmode=none;";
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
List <string> list = new List <string> ();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
list.Add(name);
}
connection.Close();
connection.Dispose();
return(list);
}
public override int save(string tableName, Data.Object row)
{
int status = -1;
MySql.Data.MySqlClient.MySqlConnection dbConn = this.ConnectAsync() as MySql.Data.MySqlClient.MySqlConnection;
if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
{
String sql = "";
try
{
sql = "INSERT INTO `" + tableName + "` (\n";
String cols = "";
String values = "";
String col_val = "";
List <String> props = new List <String>();
props.AddRange(row.getPropertyNames());
props.Sort();
Entity ent = null;
foreach (Entity e in this.Entities)
{
if (e.Name == tableName)
{
ent = e;
break;
}
}
foreach (String prop in props)
{
if (row.get(prop) != null)
{
cols += "`" + prop + "`, ";
}
if (row.get(prop) != null)
{
values += "@" + prop + ", ";
}
if (ent != null && !ent.UniqueColumns.Contains(prop))
{
col_val += "`" + prop + "`= @" + prop + ", ";
}
}
cols = cols.Substring(0, cols.Length - 2);
sql += cols + ") ";
sql += "\nValues(";
values = values.Substring(0, values.Length - 2);
sql += values + ") ";
sql += "\nON DUPLICATE KEY ";
sql += "\nUPDATE \n";
col_val = col_val.Substring(0, col_val.Length - 2);
sql += col_val + ";";
var cmd = dbConn.CreateCommand();
cmd.CommandText = sql;
popSQL(cmd, row);
status = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (m_DebugInfo)
{
Console.WriteLine("Error [{0}]: {1}", tableName, ex.Message);
}
status = -2;
}
finally
{
if (dbConn != null)
{
try
{
dbConn.Close();
}
finally
{
dbConn.Dispose();
}
}
}
decrementOpenConnections();
}
return(status);
}
public override System.Data.DataTable getFiltered(String tableName,
filtering.BaseFilter filter = null,
filtering.Sort sort = null,
filtering.Paging page = null)
{
int status = -1;
System.Data.DataTable results = new System.Data.DataTable();
MySql.Data.MySqlClient.MySqlConnection dbConn = this.ConnectAsync() as MySql.Data.MySqlClient.MySqlConnection;
if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
{
String sql = "";
try
{
sql = "Select * from `" + tableName + "` ";
// build filter
if (filter != null)
{
sql += " Where " + buildFilter(filter);
}
// build sort
if (sort != null)
{
sql += " ORDER BY " + buildSort(sort);
}
// build paging
if (page != null)
{
sql += " LIMIT " + buildPaging(page);
}
var cmd = dbConn.CreateCommand();
cmd.CommandText = sql;
results = parseResults(cmd.ExecuteReader());
status = 0;
}
catch (Exception ex)
{
if (m_DebugInfo)
{
Console.WriteLine("Error [{0}]: {1}", tableName, ex.Message);
}
status = -2;
}
finally
{
if (dbConn != null)
{
try
{
dbConn.Close();
}
finally
{
dbConn.Dispose();
}
}
}
decrementOpenConnections();
}
return(results);
}
static void Main(string[] args)
{
if (args == null)
{
return;
}
var db = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["UmecaDb"].ConnectionString);
db.Open();
var command = db.CreateCommand();
command.CommandText = "select * from cat_device where is_obsolete = 0";
command.CommandType = CommandType.Text;
var reader = command.ExecuteReader();
var result = new List <Device>();
while (reader.Read())
{
result.Add(new Device
{
Id = reader.GetInt64("id_device"),
Name = reader.GetString("name"),
Ip = reader.GetString("Ip"),
Port = reader.GetInt32("Port")
});
}
db.Close();
db.Dispose();
var character = ' ';
var service = new Program();
do
{
switch (character)
{
case '1': //Read all users
service.ReadUsers();
Console.Clear();
Console.WriteLine("Usuarios encontrados: {0}", service._usersList.Count);
Console.BackgroundColor = ConsoleColor.Yellow;
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("{0,5} {1,-30} {2,-15} {3,-5} {4,-8} {5}", "ID", "Name", "Pass", "Role", "Enabled", "FP");
Console.BackgroundColor = ConsoleColor.Black;
Console.ForegroundColor = ConsoleColor.Gray;
foreach (var user in service._usersList)
{
Console.WriteLine("{0,5} {1,-30} {2,15} {3,5} {4,8} {5}", user.EnrollNumber, user.Name, user.Password, user.Privilege, user.Enabled, user.FingerPrints.Count);
}
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '2':
Console.Clear();
Console.WriteLine("Introduzca los siguientes datos:");
int enrollNumber, privilege, fingerIndex;
string line, name, password;
bool enabled;
do
{
Console.Write("ID: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out enrollNumber));
Console.Write("Nombre: ");
name = Console.ReadLine();
Console.Write("Contraseña: ");
password = Console.ReadLine();
do
{
Console.Write("Rol: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out privilege));
do
{
Console.Write("Habilitado: ");
line = Console.ReadLine();
} while (!bool.TryParse(line, out enabled));
do
{
Console.Write("Dedo: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);
service.Write(enrollNumber, name, password, privilege, enabled, fingerIndex);
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '3':
Console.Clear();
Console.WriteLine("Introduzca los siguientes datos:");
do
{
Console.Write("ID: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out enrollNumber));
do
{
Console.Write("Dedo: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);
service.Enroll(enrollNumber, fingerIndex);
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '4':
service.ReadLogs();
Console.Clear();
Console.WriteLine("Logs encontrados: {0}", service._attendanceLogs.Count);
Console.BackgroundColor = ConsoleColor.Yellow;
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("{0,-10} {1,10} {2,15}", "ID", "Event", "Date");
Console.BackgroundColor = ConsoleColor.Black;
Console.ForegroundColor = ConsoleColor.Gray;
foreach (var user in service._attendanceLogs)
{
Console.WriteLine("{0,-10} {1,10} {2,15}", user.EnrollNumber, user.InOutMode == 4 ? "In" : (user.InOutMode == 5 ? "Out" : user.InOutMode.ToString()), user.Date);
}
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case 'T':
service.SetDateTime();
break;
case 'C':
service.Clear();
break;
case 'E':
service.Events();
break;
}
Menu();
} while ((character = Console.ReadKey().KeyChar) != 'x');
}
private void Form1_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=destination;Uid=root;Pwd=12345;");
MySql.Data.MySqlClient.MySqlConnection sourceConnection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=users;Uid=root;Pwd=12345;");
try
{
connection.Open();
sourceConnection.Open();
DataTable table = new DataTable();
MySqlBulkCopy upload = new MySqlBulkCopy();
upload.DestinationTableName = "session";
ColumnMapItemCollection collection = new ColumnMapItemCollection();
ColumnMapItem sessionId = new ColumnMapItem();
ColumnMapItem userId = new ColumnMapItem();
ColumnMapItem dateLogged = new ColumnMapItem();
ColumnMapItem loggedFrom = new ColumnMapItem();
ColumnMapItem active = new ColumnMapItem();
sessionId.DataType = "text";
sessionId.DestinationColumn = "IdSession";
sessionId.SourceColumn = "IdSession";
userId.DataType = "int";
userId.DestinationColumn = "userid";
userId.SourceColumn = "userid";
dateLogged.DataType = "datetime";
dateLogged.DestinationColumn = "dateLogged";
dateLogged.SourceColumn = "dateLogged";
loggedFrom.DataType = "text";
loggedFrom.DestinationColumn = "loggedFrom";
loggedFrom.SourceColumn = "loggedFrom";
active.DataType = "int";
active.DestinationColumn = "active";
active.SourceColumn = "active";
collection.Add(sessionId);
collection.Add(userId);
collection.Add(dateLogged);
collection.Add(loggedFrom);
collection.Add(active);
upload.ColumnMapItems = collection;
upload.DestinationDbConnection = connection;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("select idsession,userid,datelogged,loggedfrom,active from session", sourceConnection);
MySql.Data.MySqlClient.MySqlDataReader reader = command.ExecuteReader();
upload.Upload(reader);
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
connection.Dispose();
sourceConnection.Close();
sourceConnection.Dispose();
}
public static void run()
{
try
{
// 手动连接
conn.ConnectionString = connStr;
conn.Open();
string tab = "tusers";
string sql3 = $"INSERT INTO {tab} VALUES(default, @str)";
var cmd3 = new MySql.Data.MySqlClient.MySqlCommand(sql3, conn);
cmd3.Parameters.AddWithValue("@str", "👏");
using (cmd3)
{
int rows = cmd3.ExecuteNonQuery();
System.Console.WriteLine(rows);
}
string sql1 = $"select * from {tab}";
var cmd1 = new MySql.Data.MySqlClient.MySqlCommand(sql1, conn);
MySql.Data.MySqlClient.MySqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
// 此时 reader 为当前行
for (int col = 0; col < reader.FieldCount; col++)
{
var fieldName = reader.GetName(col);
var val = reader[col];
System.Console.Write("{0}: {1}\n", fieldName, val);
}
}
reader.Close();
cmd1.Dispose();
/*
* // 创建数据库执行对象
* string sql1 = "SELECT * FROM [TUser]";
* var cmd1 = new System.Data.SqlClient.SqlCommand(sql1, conn);
*
* // 执行查询命令
* // 写操作: ExecuteNonQuery
* // 读操作: ExecuteReader, ExecuteScalar
* System.Data.SqlClient.SqlDataReader reader = cmd1.ExecuteReader();
* System.Console.WriteLine("HasRows: {0}", reader.HasRows);
* System.Console.WriteLine("FieldCount: {0}", reader.FieldCount);
* System.Console.WriteLine("Depth: {0}", reader.Depth);
* System.Console.WriteLine("RecordsAffected: {0}", reader.RecordsAffected); // -1 for select
*
* // 手动去调用 Read() 方法之后, DataReader 对象才会移动到结果集的第一行
* // 同时此方法也返回一个 Bool 值, 表明下一行是否可用, True 则可用, False 则到达结果集末尾
* while (reader.Read())
* {
* // GetOrdinal 获取对应列的序号
* int index = reader.GetOrdinal("login");
* System.Console.WriteLine("\n[login] index: {0}", index);
*
* // 此时 reader 为当前行
* for (int col = 0; col < reader.FieldCount; col++)
* {
* var fieldName = reader.GetName(col);
* var val = reader[col];
* var dtype = reader.GetDataTypeName(col);
* if (dtype == "datetime")
* {
* // https://docs.microsoft.com/zh-cn/dotnet/standard/base-types/standard-date-and-time-format-strings?view=netcore-2.2
* var utc = System.DateTime.SpecifyKind(reader.GetDateTime(col), System.DateTimeKind.Utc);
* val = string.Format("\"{0:O}\"", utc);
* }
* System.Console.Write("{0}: {1} ", fieldName, val);
* System.Console.WriteLine(dtype);
* }
* }
* reader.Close();
* cmd1.Dispose();
*
* // ExecuteScalar: 获取一行一列值
* string sql2 = "SELECT COUNT([Id]) FROM [TUser]";
* var cmd2 = new System.Data.SqlClient.SqlCommand(sql2, conn);
* int count = (int) cmd2.ExecuteScalar();
* System.Console.WriteLine("users count: {0}", count);
* cmd2.Dispose();
*
* // 新增数据
* string sql3 = string.Format(@"INSERT INTO [TUser]
* VALUES('*****@*****.**', 'David', 'david123', '{0}', '{0}')", System.DateTime.UtcNow);
* var cmd3 = new System.Data.SqlClient.SqlCommand(sql3, conn);
* using(cmd3)
* {
* int rows = cmd3.ExecuteNonQuery();
* System.Console.WriteLine(rows);
* }
*
* // 修改
* var sql4 = @"UPDATE [TUser]
* SET [Password] = @Password, [UpdatedAt] = @UpdatedAt
* WHERE [Id] = @Id";
* using(var cmd4 = conn.CreateCommand())
* {
* cmd4.CommandText = sql4;
* cmd4.CommandType = System.Data.CommandType.Text;
* cmd4.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[]
* {
* new System.Data.SqlClient.SqlParameter("@Password", "hello_world"),
* new System.Data.SqlClient.SqlParameter("@UpdatedAt", System.DateTime.UtcNow),
* new System.Data.SqlClient.SqlParameter("@Id", 5),
* });
*
* // SqlCommand.Prepare method requires all parameters to have an explicitly set type.
* // cmd4.Prepare();
*
* // var r = cmd1.ExecuteNonQuery();
* var r = cmd4.ExecuteReader();
* System.Console.WriteLine(r.RecordsAffected);
* }
*/
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Console.WriteLine("MySqlException: {0}", ex.Message);
}
catch (System.Exception ex)
{
System.Console.WriteLine("Exception: {0}", ex.Message);
}
finally
{
System.Console.WriteLine("正在关闭连接...Done!");
conn.Close();
conn.Dispose();
}
}
private void Form1_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=destination;Uid=root;Pwd=12345;");
MySql.Data.MySqlClient.MySqlConnection sourceConnection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=users;Uid=root;Pwd=12345;");
try
{
connection.Open();
sourceConnection.Open();
DataTable table = new DataTable();
MySqlBulkCopy upload = new MySqlBulkCopy();
upload.DestinationTableName = "session";
ColumnMapItemCollection collection = new ColumnMapItemCollection();
ColumnMapItem sessionId = new ColumnMapItem();
ColumnMapItem userId = new ColumnMapItem();
ColumnMapItem dateLogged = new ColumnMapItem();
ColumnMapItem loggedFrom = new ColumnMapItem();
ColumnMapItem active = new ColumnMapItem();
sessionId.DataType = "text";
sessionId.DestinationColumn = "IdSession";
sessionId.SourceColumn = "IdSession";
userId.DataType = "int";
userId.DestinationColumn = "userid";
userId.SourceColumn = "userid";
dateLogged.DataType = "datetime";
dateLogged.DestinationColumn = "dateLogged";
dateLogged.SourceColumn = "dateLogged";
loggedFrom.DataType = "text";
loggedFrom.DestinationColumn = "loggedFrom";
loggedFrom.SourceColumn = "loggedFrom";
active.DataType = "int";
active.DestinationColumn = "active";
active.SourceColumn = "active";
collection.Add(sessionId);
collection.Add(userId);
collection.Add(dateLogged);
collection.Add(loggedFrom);
collection.Add(active);
upload.ColumnMapItems = collection;
upload.DestinationDbConnection = connection;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("select idsession,userid,datelogged,loggedfrom,active from session", sourceConnection);
MySql.Data.MySqlClient.MySqlDataReader reader = command.ExecuteReader();
upload.Upload(reader);
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
connection.Dispose();
sourceConnection.Close();
sourceConnection.Dispose();
}
public ActionResult TokenLogin(string _url, string _token)
{
//string Token = HttpContext.Request.Params["URL"].ToString().Substring(HttpContext.Request.Params["URL"].ToString().LastIndexOf('/') + 1);
if (!String.IsNullOrWhiteSpace(_token) && _token.IndexOf('|') > -1)
{
if (Session["CURENT_USER_ID"] != null && Session["ID_SOCIETATE"] != null) // s-a mai accesat odata linkul (de exemplu pt. email si pt. printare documente
{
HttpContext.Session["TOKEN"] = _token;
return(Redirect(String.Format("{0}{1}", HttpUtility.UrlDecode(_url), _token)));
}
else // aici trebuie sa trimitem cod pe email
{
if (TempData["TOKEN"] == null || String.IsNullOrWhiteSpace(TempData["TOKEN"].ToString()))
{
string conStr = HttpContext.Server.MapPath("~").ToLower().IndexOf("test") > 0 ? CommonFunctions.StringCipher.Decrypt(ConfigurationManager.ConnectionStrings["MySQLConnectionString_test"].ConnectionString, CommonFunctions.StringCipher.RetrieveKey()) : CommonFunctions.StringCipher.Decrypt(ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString, CommonFunctions.StringCipher.RetrieveKey()); // separam socisa de socisa_test
Utilizator u = null;
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(conStr);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "UTILIZATORIsp_GetByUserName";
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("_USER_NAME", "email"));
con.Open();
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (r.Read())
{
int authenticatedUserId = Convert.ToInt32(r["ID"]);
u = new Utilizator(Convert.ToInt32(authenticatedUserId), conStr, r);
break;
}
r.Close(); r.Dispose();
if (con != null && con.State == System.Data.ConnectionState.Open)
{
con.Close();
con.Dispose();
}
if (u == null)
{
TempData.Clear();
return(Redirect("~"));
}
else
{
TempData["TOKEN"] = _token;
TempData["URL"] = _url;
string[] separator = { "|" };
string[] token = _token.Split(separator, StringSplitOptions.RemoveEmptyEntries);
string md5p = token[0];
int id = Convert.ToInt32(token[1]);
Dosar d = new Dosar(Convert.ToInt32(u.ID), conStr, id);
MD5 md5h = MD5.Create();
if (!CommonFunctions.VerifyMd5Hash(md5h, d.NR_DOSAR_CASCO, md5p))
{
TempData.Clear();
return(Redirect("~"));
}
else
{
if (Convert.ToBoolean(ConfigurationManager.AppSettings["UseDoubleAutentificationForEmail"]))
{
SocietateAsigurare sa = (SocietateAsigurare)d.GetSocietateRca().Result;
TempData["tempLogin"] = u;
//string rnd = "1234"; // pt. test
Random generator = new Random();
string rnd = generator.Next(0, 1000000).ToString("D6");
SendVerificationCode(EmailProfiles.AwsCereriSES, sa.EMAIL_NOTIFICARI, rnd);
TempData["verificationCode"] = rnd;
TempData["verificationCodeIssueTime"] = DateTime.Now;
return(View("TokenLogin", new LoginJson()));
//return RedirectToAction("TokenLogin", "Utilizatori");
}
else
{
return(InternalTokenLogin(_token, _url, u));
}
}
}
}
else
{
TempData.Clear();
return(Redirect("~"));
}
}
}
else
{
return(Redirect("~"));
}
}
public void CreateTables()
{
try
{
if (ItIsMSSQL)
{
SqlConnection objConn = new SqlConnection(ConnectionString);
objConn.Open();
var command = new SqlCommand(@"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Events'))
BEGIN
CREATE TABLE[dbo].[Events]([InfobaseCode] int Not NULL, [DateTime][datetime] Not NULL,
[TransactionStatus][varchar](1) NULL,
[TransactionStartTime][datetime] NULL,
[TransactionMark] bigint NULL,
[Transaction][varchar](100) NULL,
[UserName] int NULL,
[ComputerName] int NULL,
[AppName] Int NULL,
[EventID] int NULL,
[EventType][varchar](1) NULL,
[Comment][nvarchar](max) NULL,
[MetadataID] int NULL,
[DataStructure][nvarchar](max) NULL,
[DataString][nvarchar](max) NULL,
[ServerID] int NULL,
[MainPortID] int NULL,
[SecondPortID] int NULL,
[Seance] int NULL);
CREATE CLUSTERED INDEX[CIX_Events] ON[dbo].[Events]([InfobaseCode], [DateTime])
END", objConn);
command.ExecuteNonQuery();
command.CommandText = "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Infobases'))" + Environment.NewLine +
" CREATE TABLE [dbo].[Infobases] ([Guid] [char](40) NOT NULL, [Code] int NOT NULL, [Name] [char](100))" + Environment.NewLine +
" IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Infobases') AND Name = 'ClusteredIndex')" + Environment.NewLine +
" CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Infobases] ([Guid] ASC);";
command.ExecuteNonQuery();
command.CommandText =
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Users'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Users]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Users') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Users] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Metadata'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Metadata]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Metadata') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Metadata] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Computers'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Computers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Computers') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Computers] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Applications'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Applications]([InfobaseCode] int NOT NULL, [Code] int NOT NULL,[Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Applications') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Applications] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'EventsType'))" + Environment.NewLine +
"CREATE TABLE [dbo].[EventsType]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](max));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'EventsType') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[EventsType] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Servers'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Servers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Servers') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Servers] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'MainPorts'))" + Environment.NewLine +
"CREATE TABLE [dbo].[MainPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'MainPorts') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[MainPorts] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'SecondPorts'))" + Environment.NewLine +
"CREATE TABLE [dbo].[SecondPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'SecondPorts') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[SecondPorts] ([InfobaseCode] ASC, [Code] ASC);";
command.ExecuteNonQuery();
command.CommandText = "SELECT TOP 1 * FROM Events";
command.ExecuteReader();
command.Dispose();
objConn.Close();
objConn.Dispose();
}
else if (ItIsMySQL)
{
MySql.Data.MySqlClient.MySqlConnection objConn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
objConn.Open();
string DBName = objConn.Database;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand
{
Connection = objConn,
CommandText = "CREATE TABLE IF NOT EXISTS `Events` (`InfobaseCode` int(11) NOT NULL, `DateTime` int(11) NOT NULL," +
"`TransactionStatus` varchar(1) NULL, `TransactionStartTime` datetime NULL, "+
"`TransactionMark` bigint NULL, `Transaction` varchar(100) NULL, `UserName` int(11) NULL, `ComputerName` int(11) NULL, "+
"`AppName` int(11) NULL, `EventID` int(11) NULL, `EventType` varchar(1) NULL, " +
"`Comment` text NULL, `MetadataID` int(11) NULL, `DataStructure` text NULL, `DataString` text NULL, "+
"`ServerID` int(11) NULL, `MainPortID` int(11) NULL, `SecondPortID` int(11) NULL, `Seance` int(11) NULL"+
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
};
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `Infobases` (`Guid` varchar(40) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100)," +
"PRIMARY KEY `Guid` (`Guid`));";
command.ExecuteNonQuery();
command.CommandText =
"CREATE TABLE IF NOT EXISTS `Users`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Metadata`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Computers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Applications`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `EventsType`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` text, PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Servers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `MainPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `SecondPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));";
command.ExecuteNonQuery();
command.Dispose();
objConn.Close();
objConn.Dispose();
}
Log.Info("Target database tables have been verified!");
}
catch (Exception ex)
{
Log.Error(ex, "Error occurred while during target database tables verification");
}
}
void FastTest()
{
List <string> m_sql = new List <string>();
DateTime now = DateTime.Now;
byte[] data = new byte[1024000];
byte[] dataNull = new byte[0];
string strData = "";
Stopwatch sw = new Stopwatch();
for (int i = 0; i < data.Length; i++)
{
data[i] = 2;
//strData = strData + data[i].ToString();
}
DateTime Sample = now;
int FrameIndex = 0;
int ParamIndex = 0;
byte AtnDataType = 1;
int id = 0;
for (int i = 0; i < 1; i++)
{
String historyCommandString = "insert into hfradar.radarsample";
//// String historyCommandString = "insert into vesseldatabase.carrecordstable";
historyCommandString += " (Sample,FrameIndex,ParamIndex,AtnDataType,Atn1Data,Atn2Data,Atn3Data,Atn4Data,Atn5Data,Atn6Data,Atn7Data,Atn8Data)";
historyCommandString += "values (@Sample,@FrameIndex,@ParamIndex,@AtnDataType,@Atn1Data,@Atn2Data,@Atn3Data,@Atn4Data,@Atn5Data,@Atn6Data,@Atn7Data,@Atn8Data)";
//historyCommandString += i + ",'";
//historyCommandString += now.ToString() + "','";
//historyCommandString += strData + "');";
m_sql.Add(historyCommandString);
}
try
{
// 获取对象
TCPUDPServer controlObject = (TCPUDPServer)this;
// 尝试打开数据库连接
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(TCPUDPServer.DatabaseConnectionString);
connection.Open();
if (connection.State == ConnectionState.Open)
{
try
{
//// 复制数据缓冲区的数据
//List<string> dynamicSqlCommandCollection = null;
//CopyBufferCommands(ref dynamicSqlCommandCollection);
// 判断是否有数据需要操作
if ((m_sql != null))
{
// 保存所有的数据项
sw.Start();
int j = 0;
SaveData(connection, m_sql, controlObject, now, FrameIndex, ParamIndex, AtnDataType, data, dataNull, dataNull, dataNull, dataNull, dataNull, dataNull, dataNull);
sw.Stop();
//获取运行时间间隔
// TimeSpan ts = sw.Elapsed;
//获取运行时间[毫秒]
long times = sw.ElapsedMilliseconds;
//获取运行的总时间
long times2 = sw.ElapsedTicks;
}
else
{
// 空数据...
//controlObject.AddStatusString("数据集合为空, 入库操作无效。");
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
// 关闭数据库连接
connection.Close();
}
}
// 释放资源
connection.Dispose();
}
catch (System.Exception ex)
{
}
}
/*
* En el procedimiento Comando, se buscará primero si ya existe el comando en dicha Hashtable para retornarla
* (convertida en el tipo correcto). Caso contrario, se procederá a la creación del mismo,
* y su agregado en el repositorio. Dado que cabe la posibilidad de que ya estemos dentro de una transacción,
* es necesario abrir una segunda conexión a la base de datos, para obtener la definición de los parámetros
* del procedimiento Almacenado (caso contrario da error, por intentar leer sin tener asignado el
* objeto Transaction correspondiente). Además, el comando, obtenido por cualquiera de los mecanismos
* debe recibir la conexión y la transacción correspondientes (si no hay Transacción, la variable es null,
* y ese es el valor que se le pasa al objeto Command)
*/
protected override System.Data.IDbCommand Comando(string procedimientoAlmacenado)
{
MySql.Data.MySqlClient.MySqlCommand com;
if (ColComandos.Contains(procedimientoAlmacenado))
com = (MySql.Data.MySqlClient.MySqlCommand)ColComandos[procedimientoAlmacenado];
else
{
var con2 = new MySql.Data.MySqlClient.MySqlConnection(CadenaConexion);
con2.Open();
com = new MySql.Data.MySqlClient.MySqlCommand(procedimientoAlmacenado, con2) { CommandType = System.Data.CommandType.StoredProcedure };
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(com);
con2.Close();
con2.Dispose();
ColComandos.Add(procedimientoAlmacenado, com);
}//end else
com.Connection = (MySql.Data.MySqlClient.MySqlConnection)Conexion;
com.Transaction = (MySql.Data.MySqlClient.MySqlTransaction)MTransaccion;
return com;
}
public String LocalSetupGet(my.MySqlClient.MySqlConnection conn)
{
string strQuery = String.Empty;
strQuery = string.Format("SELECT username, domain, ipaddress, computer_name, process_name, process_path, process_starttime, processid, process_description, process_exittime, version, productname, companyname, semester, process_path_commandline, sid, fullname, department, role, idactivity FROM app_performance.activity WHERE process_exittime BETWEEN '2016-10-06 11:00:30' AND '2016-11-09 20:21:30'");
my.MySqlClient.MySqlCommand cmd = new my.MySqlClient.MySqlCommand(strQuery, conn);
my.MySqlClient.MySqlDataReader myReader = null;
try
{
myReader = cmd.ExecuteReader();
int n = 0;
try {
System.IO.Directory.CreateDirectory(@"C:\bithubReports");
} catch (Exception ex) {
return(ex.Message.ToString());
}
//var filepath = @"C:\bithubReports\report.txt";
var csv = new System.Text.StringBuilder();
while (myReader.Read())
{
HttpContext.Current.Response.Write(myReader.GetString(0) + "\n");
string result = "";
string name, domain, ip, cpu, proc, procPath, procStart, procID, procDesc, procExit, v, pName, cName, sem, ppcl, sid, fName, dep, role, id;
if (myReader.IsDBNull(0))
{
name = " ";
}
else
{
name = String.Format("{0}", myReader.GetString(0));
}
if (myReader.IsDBNull(1))
{
domain = " ";
}
else
{
domain = String.Format("{0}", myReader.GetString(1));
}
if (myReader.IsDBNull(2))
{
ip = " ";
}
else
{
ip = String.Format("{0}", myReader.GetString(2));
}
if (myReader.IsDBNull(3))
{
cpu = " ";
}
else
{
cpu = String.Format("{0}", myReader.GetString(3));
}
if (myReader.IsDBNull(4))
{
proc = " ";
}
else
{
proc = String.Format("{0}", myReader.GetString(4));
}
if (myReader.IsDBNull(5))
{
procPath = " ";
}
else
{
procPath = String.Format("{0}", myReader.GetString(5));
}
if (myReader.IsDBNull(6))
{
procStart = " ";
}
else
{
procStart = String.Format("{0}", myReader.GetDateTime(6));
}
if (myReader.IsDBNull(7))
{
procID = " ";
}
else
{
procID = String.Format("{0}", myReader.GetInt64(7));
}
if (myReader.IsDBNull(8))
{
procDesc = " ";
}
else
{
procDesc = String.Format("{0}", myReader.GetString(8));
}
if (myReader.IsDBNull(9))
{
procExit = " ";
}
else
{
procExit = String.Format("{0}", myReader.GetDateTime(9));
}
if (myReader.IsDBNull(10))
{
v = " ";
}
else
{
v = String.Format("{0}", myReader.GetString(10));
}
if (myReader.IsDBNull(11))
{
pName = " ";
}
else
{
pName = String.Format("{0}", myReader.GetString(11));
}
if (myReader.IsDBNull(12))
{
cName = " ";
}
else
{
cName = String.Format("{0}", myReader.GetString(12));
}
if (myReader.IsDBNull(13))
{
sem = " ";
}
else
{
sem = String.Format("{0}", myReader.GetString(13));
}
if (myReader.IsDBNull(14))
{
ppcl = " ";
}
else
{
ppcl = String.Format("{0}", myReader.GetString(14));
}
if (myReader.IsDBNull(15))
{
sid = " ";
}
else
{
sid = String.Format("{0}", myReader.GetString(15));
}
if (myReader.IsDBNull(16))
{
fName = " ";
}
else
{
fName = String.Format("{0}", myReader.GetString(16));
}
if (myReader.IsDBNull(17))
{
dep = " ";
}
else
{
dep = String.Format("{0}", myReader.GetString(17));
}
if (myReader.IsDBNull(18))
{
role = " ";
}
else
{
role = String.Format("{0}", myReader.GetString(18));
}
if (myReader.IsDBNull(19))
{
id = " ";
}
else
{
id = String.Format("{0}", myReader.GetString(19));
}
result = name + "," + domain + "," + ip + "," + cpu + "," + proc + "," + procPath + "," + procStart + "," + procID + "," + procDesc + "," + procExit + "," + v + "," + pName + "," + cName + "," + sem + "," + ppcl + "," + sid + "," + fName + "," + dep + "," + role + "," + id;
string line = result;
try{
csv.AppendLine(line);
} catch (Exception ex) {
return(ex.Message.ToString());
}
n = 0;
}
try{
System.IO.File.WriteAllText(filepath, csv.ToString());
} catch (Exception ex) {
return(ex.Message.ToString());
}
}
catch (Exception ex)
{
return(ex.Message.ToString());
}
try
{
String email = "*****@*****.**";
send_email(email);
}
catch (Exception ex)
{
return(ex.Message.ToString());
}
if (myReader != null)
{
if (myReader.IsClosed == false)
{
myReader.Close();
myReader.Dispose();
}
}
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
HttpContext.Current.Response.Write("Success");
return("Success");
}
public void Dispose()
{
Command.Dispose();
Connection.Dispose();
}
static void Main(string[] args)
{
if (args == null) return;
var db = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["UmecaDb"].ConnectionString);
db.Open();
var command = db.CreateCommand();
command.CommandText = "select * from cat_device where is_obsolete = 0";
command.CommandType = CommandType.Text;
var reader = command.ExecuteReader();
var result = new List<Device>();
while (reader.Read())
{
result.Add(new Device
{
Id = reader.GetInt64("id_device"),
Name = reader.GetString("name"),
Ip = reader.GetString("Ip"),
Port = reader.GetInt32("Port")
});
}
db.Close();
db.Dispose();
var character = ' ';
var service = new Program();
do
{
switch (character)
{
case '1': //Read all users
service.ReadUsers();
Console.Clear();
Console.WriteLine("Usuarios encontrados: {0}", service._usersList.Count);
Console.BackgroundColor = ConsoleColor.Yellow;
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("{0,5} {1,-30} {2,-15} {3,-5} {4,-8} {5}", "ID", "Name", "Pass", "Role", "Enabled", "FP");
Console.BackgroundColor = ConsoleColor.Black;
Console.ForegroundColor = ConsoleColor.Gray;
foreach (var user in service._usersList)
{
Console.WriteLine("{0,5} {1,-30} {2,15} {3,5} {4,8} {5}", user.EnrollNumber, user.Name, user.Password, user.Privilege, user.Enabled, user.FingerPrints.Count);
}
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '2':
Console.Clear();
Console.WriteLine("Introduzca los siguientes datos:");
int enrollNumber, privilege, fingerIndex;
string line, name, password;
bool enabled;
do
{
Console.Write("ID: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out enrollNumber));
Console.Write("Nombre: ");
name = Console.ReadLine();
Console.Write("Contraseña: ");
password = Console.ReadLine();
do
{
Console.Write("Rol: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out privilege));
do
{
Console.Write("Habilitado: ");
line = Console.ReadLine();
} while (!bool.TryParse(line, out enabled));
do
{
Console.Write("Dedo: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);
service.Write(enrollNumber, name, password, privilege, enabled, fingerIndex);
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '3':
Console.Clear();
Console.WriteLine("Introduzca los siguientes datos:");
do
{
Console.Write("ID: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out enrollNumber));
do
{
Console.Write("Dedo: ");
line = Console.ReadLine();
} while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);
service.Enroll(enrollNumber, fingerIndex);
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case '4':
service.ReadLogs();
Console.Clear();
Console.WriteLine("Logs encontrados: {0}", service._attendanceLogs.Count);
Console.BackgroundColor = ConsoleColor.Yellow;
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("{0,-10} {1,10} {2,15}", "ID", "Event", "Date");
Console.BackgroundColor = ConsoleColor.Black;
Console.ForegroundColor = ConsoleColor.Gray;
foreach (var user in service._attendanceLogs)
{
Console.WriteLine("{0,-10} {1,10} {2,15}", user.EnrollNumber, user.InOutMode == 4 ? "In" : (user.InOutMode == 5 ? "Out" : user.InOutMode.ToString()), user.Date);
}
Console.Write("Presione cualquier tecla para continuar...");
Console.ReadKey();
break;
case 'T':
service.SetDateTime();
break;
case 'C':
service.Clear();
break;
case 'E':
service.Events();
break;
}
Menu();
} while ((character = Console.ReadKey().KeyChar) != 'x');
}
/// <summary>
///
/// Note:
/// max_connect_errors needs to be set.
/// there is some kind of network socket stream error happening
/// and mysql chocks it up as bad client and eventually
/// blocks the client.
/// </summary>
/// <param name="name"></param>
/// <param name="parms"></param>
/// <returns></returns>
public override System.Data.DataTable CallQuery(String name, List <object> parms)
{
int status = -1;
System.Data.DataTable results = new System.Data.DataTable();
MySql.Data.MySqlClient.MySqlConnection dbConn = this.ConnectAsync() as MySql.Data.MySqlClient.MySqlConnection;
if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
{
String sql = "";
try
{
sql = "call " + name + "(";
for (int i = 0; parms != null && i < parms.Count; i++)
{
if (Utilities.Util.isNumber(parms[i]))
{
sql += parms[i] + ",";
}
else if (parms[i] is String) // should be the last type
{
sql += "\'" + MySql.Data.MySqlClient.MySqlHelper.EscapeString((String)parms[i]) + "\'" + ",";
}
}
if (sql.EndsWith(","))
{
sql = sql.Substring(0, sql.Length - 1);
}
sql += ");";
var cmd = dbConn.CreateCommand();
cmd.CommandText = sql;
results = parseResults(cmd.ExecuteReader());
status = 0;
}
catch (Exception ex)
{
if (m_DebugInfo)
{
Console.WriteLine("Error [{0}]: {1}", name, ex.Message);
}
status = -2;
}
finally
{
if (dbConn != null)
{
try
{
dbConn.Close();
}
finally
{
dbConn.Dispose();
}
}
}
decrementOpenConnections();
}
return(results);
}