public MySql.Data.MySqlClient.MySqlDataReader RunCommandObj(string sql)
{
Command = Connection.CreateCommand();
Command.CommandType = System.Data.CommandType.Text;
Command.CommandText = sql;
return(Command.ExecuteReader());
}
/// <summary>
/// Create the dabase if it does not exist.
/// </summary>
/// <remarks>Mysql/mariadb specific</remarks>
private void CreateIfNotExists()
{
string connStr = LibLogic.Setup.DbFactoryWithoutDatabase.ConnectionString;
var cn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
var cmd = cn.CreateCommand();
cmd.CommandText = string.Format("CREATE DATABASE IF NOT EXISTS `{0}` CHARACTER SET utf8 COLLATE utf8_unicode_ci;", Setup.DatabaseName);
cmd.CommandType = System.Data.CommandType.Text;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
// Sessions database
// Alls sessions are store in mysql instead of in process.
// Provides better support for clustering, load balancing and restarting sites after updates
// without users being logged out or loosing data
var cn2 = new MySql.Data.MySqlClient.MySqlConnection(connStr);
var cmd2 = cn2.CreateCommand();
cmd2.CommandText = "CREATE DATABASE IF NOT EXISTS `VpnSessions` CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
cmd2.CommandType = System.Data.CommandType.Text;
cn2.Open();
cmd2.ExecuteNonQuery();
cn2.Close();
}
/// <summary>
/// ตรวจสอบว่าใน database server มี function นี้อยู่หรือไม่
/// </summary>
/// <param name="name">ชื่อ function</param>
/// <returns>bool</returns>
static bool FunctionIsExist(string name)
{
int result = 0;
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString))
using (var command = connection.CreateCommand())
{
var sb = new StringBuilder();
sb.AppendLine("SELECT COUNT(ROUTINE_NAME) as Result");
sb.AppendLine("FROM INFORMATION_SCHEMA.ROUTINES");
sb.AppendLine("WHERE ROUTINE_TYPE = @RoutineType");
sb.AppendLine("AND ROUTINE_SCHEMA = @RoutineSchema");
sb.AppendLine("AND ROUTINE_NAME = @RoutineName;");
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sb.ToString();
command.Parameters.AddWithValue("@RoutineType", "FUNCTION");
command.Parameters.AddWithValue("@RoutineSchema", "opminspection");
command.Parameters.AddWithValue("@RoutineName", name);
connection.Open();
result = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
}
return(result > 0);
}
/// <summary>
/// Creates a new command, enlisting it in the current transaction
/// </summary>
public MySqlData.MySqlClient.MySqlCommand CreateCommand()
{
var command = _currentConnection.CreateCommand();
command.Transaction = _currentTransaction;
return(command);
}
} // GetFromRow
public CmsPersistentVariable Fetch(string name)
{
if (name.Trim() == "")
{
return(new CmsPersistentVariable("", null));
}
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
{
OpenMySqlConnection(conn);
string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables ";
sql += " WHERE Name like @Name";
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@Name", name);
MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
sqlDA.Fill(ds);
if (this.hasSingleRow(ds))
{
DataRow dr = ds.Tables[0].Rows[0];
return(GetFromRow(dr));
}
return(new CmsPersistentVariable("", null));
}
} // Fetch
/// <summary>
/// 动态数据入库操作
/// </summary>
private static void SaveDynamicData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, AisControl controlObject)
{
// 开始事务处理
MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand command = connection.CreateCommand();
try
{
// 批量更新
foreach (string cmd in dynamicSqlCommandCollection)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
// 提交数据入库
transaction.Commit();
if (controlObject != null)
{
// 提交错误日志
controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
}
}
catch (Exception ex)
{
// 放弃当前的数据入库操作
transaction.Rollback();
if (controlObject != null)
{
// 提交错误日志
controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为：" + ex.ToString());
}
}
}
internal List <Mercado> RetrievebyConst(int id)
{
MySql.Data.MySqlClient.MySqlConnection con = Connect();
MySql.Data.MySqlClient.MySqlCommand command = con.CreateCommand();
command.CommandText = "select * from mercado where id = @A";
command.Parameters.AddWithValue("@A", id);
try
{
con.Open();
MySql.Data.MySqlClient.MySqlDataReader res = command.ExecuteReader();
Mercado d = null;
List <Mercado> mercados = new List <Mercado>();
while (res.Read())
{
Debug.WriteLine("Recuperado: " + res.GetInt32(0) + " " + res.GetString(1) + " " + res.GetInt32(2) + " " + res.GetString(3));
d = new Mercado(res.GetInt32(0), res.GetDouble(1), res.GetDouble(2), res.GetDouble(3), res.GetInt32(4), res.GetInt32(5));
mercados.Add(d);
}
con.Close();
return(mercados);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
Debug.WriteLine("Se ha producido un error de conexión");
return(null);
}
}
// Visibilidad para que sea visto por otras clases internal - como protected.
internal List <Mercado> Retrieve()
{
MySql.Data.MySqlClient.MySqlConnection conexion = Connect();
MySql.Data.MySqlClient.MySqlCommand cmd = conexion.CreateCommand();
cmd.CommandText = " select * from mercado";
try
{
conexion.Open();
MySql.Data.MySqlClient.MySqlDataReader res = cmd.ExecuteReader();
Mercado mercado = null;
List <Mercado> lMercados = new List <Mercado>();
while (res.Read())
{
Debug.WriteLine("Mercado: " + res.GetInt32(0) + " " + res.GetString(1) + " " +
res.GetString(2) + " " + res.GetString(3) + " ");
mercado = new Mercado(res.GetInt32(0), res.GetDouble(1), res.GetDouble(2), res.GetDouble(3), res.GetDouble(4), res.GetDouble(5));
lMercados.Add(mercado);
}
conexion.Close();
return(lMercados);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
Debug.WriteLine("Se ha producido un error de conexión.");
return(null);
}
}
private static void MySQL_OpenConnection()
{
string info = "server=localhost;user=root;database=tun;port=3306;password=Camara20";
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(info);
MySql.Data.MySqlClient.MySqlCommand command;
try
{
Console.WriteLine("MySQL: Connecting to 'tun' database...\n");
connection.Open();
// TODO: executing queries here
command = connection.CreateCommand();
command.CommandText = "DELETE FROM account";
command.ExecuteNonQuery();
Console.WriteLine("MySQL: all 'account' instances were successfully deleted!\n");
command.CommandText = "INSERT INTO account (username, password) VALUES ('ShaBer', 'qwerty');";
command.ExecuteNonQuery();
Console.WriteLine("MySQL: 'account' inserted successfully!\n");
}
catch (Exception ex)
{
Console.WriteLine("MySQL: An error occured while atempting to connect to 'tun'");
Console.WriteLine($"MySQL: - {ex.ToString()}\n");
}
connection.Close();
Console.WriteLine("MySQL: Closing connection");
}
// Devuelve la lista entera:
internal List <Receta> Retrieve()
{
// Conexión a sql y consulta:
MySql.Data.MySqlClient.MySqlConnection conexion = Connect();
MySql.Data.MySqlClient.MySqlCommand cmd = conexion.CreateCommand();
cmd.CommandText = " select * from recetas";
try
{
conexion.Open();
MySql.Data.MySqlClient.MySqlDataReader res = cmd.ExecuteReader();
// Creamos objeto mercado y lista:
Receta receta = null;
List <Receta> recetas = new List <Receta>();
while (res.Read())
{
// Introducimos los datos y añadimos a la lista todo:
receta = new Receta(res.GetInt32(0), res.GetString(1), res.GetString(2), res.GetInt32(3));
recetas.Add(receta);
}
// Cerramos la conexión a la bd:
conexion.Close();
return(recetas);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
Debug.WriteLine("Se ha producido un error de conexión.");
return(null);
}
}
static void Main(string[] args)
{
using (ConnectionMultiplexer redis = ConnectionMultiplexer.Connect("127.0.0.1:6379"))
{
ISubscriber sub = redis.GetSubscriber();
sub.Subscribe("createtenant", (channel, message) => {
Console.WriteLine($"收到消息:{message}\n 开始创建表……");
using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(message))
{
Console.WriteLine("打开数据库连接");
connection.Open();
Console.WriteLine("数据库连接打开成功");
Console.WriteLine("开始创建用户表");
var cmd = connection.CreateCommand();
cmd.CommandText = createUser;
cmd.ExecuteNonQuery();
Console.WriteLine("用户表创建成功\n 开始创建订单表");
cmd.CommandText = createOrder;
cmd.ExecuteNonQuery();
Console.WriteLine("订单表创建成功\n 开始创建商品表");
cmd.CommandText = createGoods;
cmd.ExecuteNonQuery();
Console.WriteLine("商品表创建成功");
cmd.Dispose();
}
});
Console.WriteLine("已订阅 messages");
Console.ReadKey();
}
}
public static int QueryIdentityTransaction(this MySql.Data.MySqlClient.MySqlConnection db, MySql.Data.MySqlClient.MySqlTransaction trans, string query, out long identity, params object[] args)
{
Stopwatch sw = new Stopwatch();
int affected = 0;
sw.Start();
using (var com = db.CreateCommand()) {
com.CommandText = query;
com.Transaction = trans;
com.CommandTimeout = 60;
for (int i = 0; i < args.Length; i++)
{
com.AddParameter("@" + i, args[i]);
}
try {
affected = com.ExecuteNonQuery();
} catch (Exception ex) {
TShock.Log.ConsoleError("[SEconomy MySQL] Query error: {0}", ex.Message);
affected = -1;
}
identity = com.LastInsertedId;
}
sw.Stop();
if (sw.Elapsed.TotalSeconds > 10)
{
TShock.Log.ConsoleError("[SEconomy MySQL] Your MySQL server took {0} seconds to respond!\r\nConsider squashing your journal.", sw.Elapsed.TotalSeconds);
}
return(affected);
}
public ActionResult Contact()
{
var users = new List <User>();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString.Get("EscademyDB")))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT FirstName, LastName, ProfilePicture, Id FROM esc_accounts WHERE Id = 1 OR Id = 2";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
users.Add(new User()
{
Id = reader.GetInt32("Id"),
FirstName = reader.GetString("FirstName"),
LastName = reader.GetString("LastName"),
Picture = reader.GetString("ProfilePicture")
});
}
}
conn.Close();
}
ViewBag.users = users;
return(View());
}
public override void LoadQueries()
{
MySql.Data.MySqlClient.MySqlConnection dbConn = this.ActiveConnection as MySql.Data.MySqlClient.MySqlConnection;
if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
{
// get procedures
String sql = "SHOW PROCEDURE STATUS;";
//String sql = "SHOW FUNCTION STATUS;";
var cmd = dbConn.CreateCommand();
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
Queries.Clear();
while (reader.HasRows && reader.Read())
{
Query q = new Query();
q.Name = reader[1].ToString();
Queries.Add(q);
}
reader.Close();
foreach (Query q in Queries)
{
q.Load(dbConn);
}
}
}
/// <summary>
/// สร้าง mysql function ลง database server
/// </summary>
/// <param name="name">ชื่อ function</param>
/// <returns>ข้อความแจ้งผลลัพท์</returns>
static string CreateFunction(string name)
{
if (name == "truncatetime")
{
if (!Program.FunctionIsExist(name))
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString))
using (var command = connection.CreateCommand())
{
var sb = new StringBuilder();
sb.AppendLine("CREATE DEFINER=`root`@`localhost` FUNCTION `TruncateTime`(dateValue DateTime) RETURNS date");
sb.AppendLine("BEGIN");
sb.AppendLine("RETURN Date(dateValue);");
sb.AppendLine("END");
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sb.ToString();
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return("Create \"TruncateTime\" successfully.");
}
else
{
return("\"TruncateTime\" already exist.");
}
}
return("Invalid function name.");
}
public IEnumerable <string> GetLocations()
{
string connectionString = Settings.Default.SwissCenterConnectionString;
IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select location_id, name from media_locations";
using (connection)
{
connection.Open();
IDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
using (reader)
{
while (reader.Read())
{
yield return(EnsureSlashes((string)reader["name"]));
}
}
}
}
}
} // Fetch
public CmsPersistentVariable[] FetchAllWithNamePrefix(string namePrefix)
{
if (namePrefix.Trim() == "")
{
return(new CmsPersistentVariable[0]);
}
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
{
OpenMySqlConnection(conn);
string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables ";
sql += " WHERE Name like @Name";
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@Name", namePrefix + "%");
MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
sqlDA.Fill(ds);
List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>();
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
arrayList.Add(GetFromRow(dr));
} // foreach row
}
return(arrayList.ToArray());
}
} // FetchAllWithNamePrefix
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();
}
} // Update
public bool Delete(int PersistentVariableId)
{
if (PersistentVariableId < 0)
{
return(false);
}
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
{
OpenMySqlConnection(conn);
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE from persistentvariables where PersistentVariableId = @PersistentVariableId;";
cmd.Parameters.AddWithValue("@PersistentVariableId", PersistentVariableId);
int numUpdated = cmd.ExecuteNonQuery();
if (numUpdated < 1)
{
return(false);
}
return(true);
}
}
public object GetBalance()
{
decimal balance = 0.0M;
bool auth = false;
if (EnsureUserRights(UserLevel.Coach))
{
auth = true;
var user = GetCurrentUser();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString.Get("EscademyMDB")))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT Status, Amount FROM esc_Transactions WHERE ReceiverId=@accId";
cmd.Parameters.AddWithValue("@accId", user.Id);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetString("Status") == "Pending")
{
balance += reader.GetDecimal("Amount");
}
}
}
conn.Close();
}
}
return(Json(new { Auth = auth ? "OK" : "NO_AUTH", Balance = balance }));
}
} // Fetch
public CmsPersistentVariable[] FetchAll()
{
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
{
OpenMySqlConnection(conn);
string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables; ";
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
sqlDA.Fill(ds);
List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>();
if (this.hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
arrayList.Add(GetFromRow(dr));
} // foreach row
} // if there is data
return(arrayList.ToArray());
}
} // FetchAll
public Tuple <bool, string> Run(string id)
{
var model = _sqlpublishorderRepository.Table.FirstOrDefault(s => s.Id == id);
model.IsRunning = true;
model.Result = "Running";
_sqlpublishorderRepository.Update(model);
try
{
if (model.YimiSqlProvider.SqlProviderType == SqlProviderType.MsSql)
{
using (SqlConnection conn = new SqlConnection(model.YimiSqlProvider.Connectstring))
{
conn.Open();
//string sql = $"Use [{model.YimiSqlProvider.DbName}]" + System.Environment.NewLine;
//sql += model.SqlText + System.Environment.NewLine;
//sql += "go";
SqlCommand sqlCommand = conn.CreateCommand();
sqlCommand.CommandType = System.Data.CommandType.Text;
sqlCommand.CommandTimeout = 1000;
sqlCommand.CommandText = model.SqlText;
sqlCommand.ExecuteNonQuery();
}
}
else
{
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(model.YimiSqlProvider.Connectstring))
{
conn.Open();
var sqlCommand = conn.CreateCommand();
sqlCommand.CommandType = System.Data.CommandType.Text;
sqlCommand.CommandTimeout = 1000;
sqlCommand.CommandText = model.SqlText;
sqlCommand.ExecuteNonQuery();
}
}
model.IsRunning = false;
model.IsPublished = true;
model.Result = "Run Successfully!";
_sqlpublishorderRepository.Update(model);
return(new Tuple <bool, string>(true, ""));
}
catch (Exception ex)
{
model.IsRunning = false;
model.Result = ex.Message;
_sqlpublishorderRepository.Update(model);
return(new Tuple <bool, string>(false, ex.Message));
}
}
}//LireHDR
private void MySqlInsertBD(string nomFichier, string codeClient, DateTime dateRéception, DateTime dateTraitement, string nomFichierOriginal, string numFaxExpediteur, bool traite) //Fonction qui fait les insertions dans la base de donnée concernant les fax
{
try
{
connection.Open();
LocalInsertBD();
MySql.Data.MySqlClient.MySqlCommand inserteFax = connection.CreateCommand();
MySql.Data.MySqlClient.MySqlCommand nombreFax = connection.CreateCommand();
nombreFax.CommandText = "SELECT LEFT ('datetraitement',10) AS DT, COUNT(*) FROM FAX GROUP BY DT";
inserteFax.CommandText = "INSERT INTO fax (nomFichier, codeClient, dateReception, dateTraitement,nomFichierOriginal, numFaxExpediteur, traite) " +
" VALUES(@nf, @cd, @dateRecept, @dateTraite, @nfOriginal,@numFaxExp, @traite)";
inserteFax.Parameters.AddWithValue("@nf", nomFichier);
inserteFax.Parameters.AddWithValue("@cd", codeClient);
inserteFax.Parameters.AddWithValue("@dateRecept", dateRéception);
inserteFax.Parameters.AddWithValue("@dateTraite", dateTraitement);
inserteFax.Parameters.AddWithValue("@nfOriginal", nomFichierOriginal);
inserteFax.Parameters.AddWithValue("@numFaxExp", numFaxExpediteur);
inserteFax.Parameters.AddWithValue("@traite", traite);
//Insert les éléments dans la base de donnée MySql
inserteFax.ExecuteNonQuery();
inserteFax.Connection = connection;
//Récupere le nombre de fax dans la base
MySql.Data.MySqlClient.MySqlDataReader dataReader = nombreFax.ExecuteReader();
if (dataReader.Read())
{
Int32.TryParse(dataReader.GetValue(1).ToString(), out CompteurFaxBD);
}
}
catch (DbException err) //Si une connection est impossible on enregistre les données dans un fichier texte
{
++erreurBD;
Console.Write(err);
CompteurFaxEnAttente++;
texte = texte + (nomFichier + ";" + codeClient + ";" + dateRéception + ";" + dateTraitement + ";" + nomFichierOriginal + ";" + numFaxExpediteur + ";" + traite) + Environment.NewLine;
}
connection.Close();
}//MySqlInsertBD
public List <NumEntry> GetTimeNumValue(string year, int fid)
{
string condition = " AND `key` > 1420041600 AND `key` < 1451577600";
if (year == "2016")
{
condition = " AND `key` > 1451577600 AND `key` < 1483200000";
}
if (year == "2017")
{
condition = " AND `key` > 1483200000 AND `key` < 1514736000";
}
if (year == "2018")
{
condition = " AND `key` > 1514736000 AND `key` < 1546272000";
}
if (year == "2019")
{
condition = " AND `key` > 1546272000";
}
List <NumEntry> lne = new List <NumEntry>();
string sql = "SELECT * FROM time_number_entry WHERE feed_id = " + fid + condition;// + " limit 1000";
string conStr = StaticData.conStr;
IDbConnection cn = new MySql.Data.MySqlClient.MySqlConnection(conStr);
try
{
cn.Open();
IDbCommand cmd = cn.CreateCommand();
cmd.CommandTimeout = 3000;
cmd.CommandText = sql;
IDataReader r = cmd.ExecuteReader();
while (r.Read())
{
NumEntry fm = new NumEntry();
fm.key = r["key"] is DBNull ? null : r["key"].ToString();
fm.value = r["value"] is DBNull ? 0 : Convert.ToDecimal(r["value"]);
fm.id = r["id"] is DBNull ? 0 : Convert.ToInt32(r["id"]);
fm.feed_id = r["feed_id"] is DBNull ? -1 : Convert.ToInt32(r["feed_id"]);
lne.Add(fm);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
finally
{
if (cn != null)
{
cn.Close();
}
}
return(lne);
}
public List <FeedModel> GetFeeds()
{
List <FeedModel> lfs = new List <FeedModel>();
string sql = @"SELECT p2.p0t,p2.p1t,p2.title p2t,p.* from feed p INNER JOIN(
SELECT p1.p0t,p1.title p1t, p.*from feed p INNER JOIN(
(SELECT p0.title p0t, p.* FROM feed p INNER JOIN
(SELECT * FROM feed WHERE(creator_id = 50 or owner_id = 50) AND parent_id = 0 AND `status` != -1) p0
ON p.parent_id = p0.id)) p1
ON p.parent_id = p1.id) p2
ON p.parent_id = p2.id"; //白城
//红光大道
sql = @" SELECT p2.p0t,p2.p1t,p2.title p2t, p.*from feed p INNER JOIN(
SELECT p1.p0t, p1.title p1t, p.* from feed p INNER JOIN(
(SELECT p0.title p0t, p.* FROM feed p INNER JOIN
(SELECT * FROM feed WHERE(id = 17687) AND parent_id = 0 AND `status` != -1) p0
ON p.parent_id = p0.id)) p1
ON p.parent_id = p1.id) p2
ON p.parent_id = p2.id";
string conStr = StaticData.conStr;
IDbConnection cn = new MySql.Data.MySqlClient.MySqlConnection(conStr);
try
{
cn.Open();
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = sql;
IDataReader r = cmd.ExecuteReader();
while (r.Read())
{
FeedModel fm = new FeedModel();
fm.P0T = r["p0t"] is DBNull ? null : (string)r["p0t"];
fm.P1T = r["p1t"] is DBNull ? null : (string)r["p1t"];
fm.P2T = r["p2t"] is DBNull ? null : (string)r["p2t"];
fm.ID = r["id"] is DBNull ? -1 : Convert.ToInt32(r["id"]);
fm.Title = r["title"] is DBNull ? null : (string)r["title"];
fm.Value_Type = r["value_type"] is DBNull ? -1 : (int)r["value_type"];
fm.Created = r["created"] is DBNull ? DateTime.Now : DateTime.Parse(r["created"].ToString());
lfs.Add(fm);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
finally
{
if (cn != null)
{
cn.Close();
}
}
return(lfs);
}
private void SaveData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, TCPUDPServer controlObject, DateTime Sample, int FrameIndex, int ParamIndex, byte AtnDataType, byte[] Atn1Data, byte[] Atn2Data, byte[] Atn3Data, byte[] Atn4Data, byte[] Atn5Data, byte[] Atn6Data, byte[] Atn7Data, byte[] Atn8Data)
{
// 开始事务处理
MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand command = connection.CreateCommand();
try
{
command.Parameters.AddWithValue("@Sample", Sample);
command.Parameters.AddWithValue("@FrameIndex", FrameIndex);
command.Parameters.AddWithValue("@ParamIndex", ParamIndex);
command.Parameters.AddWithValue("@AtnDataType", AtnDataType);
command.Parameters.AddWithValue("@Atn1Data", Atn1Data);
command.Parameters.AddWithValue("@Atn2Data", Atn2Data);
command.Parameters.AddWithValue("@Atn3Data", Atn3Data);
command.Parameters.AddWithValue("@Atn4Data", Atn4Data);
command.Parameters.AddWithValue("@Atn5Data", Atn5Data);
command.Parameters.AddWithValue("@Atn6Data", Atn6Data);
command.Parameters.AddWithValue("@Atn7Data", Atn7Data);
command.Parameters.AddWithValue("@Atn8Data", Atn8Data);
// 批量更新
foreach (string cmd in dynamicSqlCommandCollection)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
// 提交数据入库
transaction.Commit();
//if (controlObject != null)
//{
// // 提交错误日志
// // controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
// if (dynamicSqlCommandCollection.Count != 0)
// {
// this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
// }
// // this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
//}
}
catch (Exception ex)
{
// 放弃当前的数据入库操作
transaction.Rollback();
//if (controlObject != null)
//{
// // this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作失败。\r\n错误信息为：" + ex.ToString());
// // 提交错误日志
// //controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为：" + ex.ToString());
//}
Console.WriteLine(ex);
}
}
override public IDbCommand CreateCommand()
{
MySQLConnection sc = InternalConnection as MySQLConnection;
if (null == sc)
{
throw new InvalidOperationException("InvalidConnType00" + InternalConnection.GetType().FullName);
}
return(sc.CreateCommand());
}
internal void TruncateMinuteWiseMeasures()
{
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = @"TRUNCATE TABLE minute_wise;";
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
public void DeleteTable(MySql.Data.MySqlClient.MySqlConnection connect, MySql.Data.MySqlClient.MySqlCommand command, DataTable table)
{
var connectionString = String.Format("Server=localhost;Database=test;username=root;password=;Port=3306");
using (connect = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
connect.Open();
command = connect.CreateCommand();
command.CommandText = String.Format("DROP TABLE {0}", table.TableName);
command.ExecuteNonQuery();
}
}
public void TearDown()
{
string connStrDrop = LibLogic.Setup.DbFactoryWithoutDatabase.ConnectionString;
var cnDrop = new MySql.Data.MySqlClient.MySqlConnection(connStrDrop);
var cmdDrop = cnDrop.CreateCommand();
cmdDrop.CommandText = string.Format("DROP DATABASE IF EXISTS `{0}`;", testingdb);
cmdDrop.CommandType = System.Data.CommandType.Text;
cnDrop.Open();
cmdDrop.ExecuteNonQuery();
cnDrop.Close();
}
public int Mysql_File_Save(int PropertyObject, int FileSize, string FileName, string Content_Type,
int Height, int Width, byte[] Image, byte[] ImagePreview, bool IsDeleted)
{
int result = -1;
using (MySql.Data.MySqlClient.MySqlConnection oConn =
new MySql.Data.MySqlClient.MySqlConnection(this.connStr))
{
oConn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = oConn.CreateCommand();
cmd.Connection = oConn;
//Add new
//oCommand.CommandText = "insert into cust_file(customer_id, filename, filedata, contenttype, length) " +
// "values( ?in_customer_id, ?in_filename, ?in_filedata, ?in_contenttype, ?in_length)";
//INSERT INTO myrealty.images (id, img) VALUES (<INT(11)>, <LONGBLOB>);
cmd.CommandText = @"SET GLOBAL max_allowed_packet=16*1024*1024;
INSERT INTO ObjectImages (PropertyObject_Id, FileSize, FileName, Content_Type, Height, Width,
Image, ImagePreview, IsDeleted) VALUES (?PropertyObject, ?FileSize, ?FileName, ?Content_Type, ?Height, ?Width,
?Image, ?ImagePreview, ?IsDeleted); select last_insert_id();";
//oCommand.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?PropertyObject", PropertyObject);
cmd.Parameters.Add("?FileSize", FileSize);
cmd.Parameters.Add("?FileName", FileName);
cmd.Parameters.Add("?Content_Type", Content_Type);
cmd.Parameters.Add("?Height", Height);
cmd.Parameters.Add("?Width", Width);
cmd.Parameters.Add("?Image", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
cmd.Parameters["?Image"].Value = Image;
cmd.Parameters.Add("?ImagePreview", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
cmd.Parameters["?ImagePreview"].Value = ImagePreview;
cmd.Parameters.Add("?IsDeleted", IsDeleted);
result = Convert.ToInt32(cmd.ExecuteScalar());
oConn.Close();
}
return result;
}
public void InsertIntoFragt(Package package)
{
conn.ConnectionString = connectionstring;
conn.Open();
int price = 0;
if (package.Price != "")
{
price = Convert.ToInt32(package.Price);
}
MySql.Data.MySqlClient.MySqlCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO Fragt (`id`, `date`, `type`, `price`, `country`, `comment`) VALUES ("
+ package.Id + ", '"
+ package.Date.ToString("dd/MM/yyyy") + "', '"
+ package.Type + "', "
+ price + ", '"
+ package.Country + "', '"
+ package.Comment + "')";
command.ExecuteNonQuery();
conn.Close();
}
internal void TruncateAllTables()
{
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = @"
TRUNCATE TABLE plants;
TRUNCATE TABLE user_has_plant;
TRUNCATE TABLE kwh_by_day;
TRUNCATE TABLE user_has_plant;
TRUNCATE TABLE measure;
TRUNCATE TABLE temporary_measure;";
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
private void ExecuteSqlFromFile(string path)
{
try
{
if (File.Exists(path))
{
string sqlText = File.ReadAllText(path);
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = sqlText;
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private static void updateVersionInSQL(long svnVersion)
{
ConsoleLogStartAction(Resources.UILogging.UpdateSQL);
var mysqlHost = ConfigurationManager.AppSettings[AppSettingKeys.MySqlHost];
var mysqlUser = ConfigurationManager.AppSettings[AppSettingKeys.MySqlUser];
var mysqlDatabase = ConfigurationManager.AppSettings[AppSettingKeys.MySqlDatabase];
if (mysqlHost.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLHost);
mysqlHost = Console.ReadLine();
}
if (mysqlUser.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLUser);
mysqlUser = Console.ReadLine();
}
if (mysqlDatabase.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLDatabase);
mysqlDatabase = Console.ReadLine();
}
Console.WriteLine(Resources.Questions.MySQLPassword);
var connexionString = string.Empty;
using (SecureString mysqlPassword = GetPassword())
{
connexionString = string.Format(Program.MySqlConnectionString, mysqlHost, mysqlDatabase, mysqlUser, mysqlPassword.ConvertToUnsecureString());
using (var connexion = new MySql.Data.MySqlClient.MySqlConnection(connexionString))
{
try
{
connexion.Open();
var command = connexion.CreateCommand();
command.CommandText = Program.MySqlUpdate;
command.Parameters.AddWithValue(Program.MySqlVersion, svnVersion);
command.ExecuteNonQuery();
connexion.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(string.Format(Resources.Errors.MySqlUpdate, ex.Message));
}
}
}
ConsoleEndAction();
}
public int AddPropertyObject(PropertyObject po)
{
int result = -1;
using (MySql.Data.MySqlClient.MySqlConnection oConn =
new MySql.Data.MySqlClient.MySqlConnection(this.connStr))
{
oConn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = oConn.CreateCommand();
cmd.Connection = oConn;
cmd.CommandText = @"INSERT INTO PropertyObjects (City_Id, CityDistrict_Id, PropertyType_Id, PropertyAction_Id,
Title,PropertyDescription,RoomCount,TotalSpace,LivingSpace,KitchenSpace,BuildingTypeName_Id,Floor,CountFloors,IsNewBuilding,BalconAvailable,
BalconSpace,isBalconGlassed,ContactName,Price,Currency_Id,PriceForTypeName_Id,NoCommission,Phone1,Phone2,Phone3,Periods_Id,CreatedDate,SourceUrl,
UserOwner_Id,DistanceToCity,WCType_Id,CountPhotos,CommercialPropertyType_Id,ServiceType_Id,IsActive,IsDeleted,DeletedDate,LinkOfObjectGrab) values (?City, ?CityDistrict, ?PropertyType,
?PropertyAction,
?Title,?PropertyDescription,?RoomCount,?TotalSpace,?LivingSpace,?KitchenSpace,?BuildingTypeName,?Floor,?CountFloors,?IsNewBuilding,?BalconAvailable,
?BalconSpace,?isBalconGlassed,?ContactName,?Price,?Currency,?PriceForTypeName,?NoCommission,?Phone1,?Phone2,?Phone3,?Periods,?CreatedDate,?SourceUrl,
?UserOwner,?DistanceToCity,?WCType,?CountPhotos,?CommercialPropertyType,?ServiceType,?IsActive,?IsDeleted,?DeletedDate,?LinkOfObjectGrab);
select last_insert_id();";
cmd.Parameters.Add("?City", (po.City != null ? po.City.Id.ToString() : null));
cmd.Parameters.Add("?CityDistrict", (po.CityDistrict != null ? po.CityDistrict.Id.ToString() : null));
cmd.Parameters.Add("?PropertyType", (po.PropertyType != null ? po.PropertyType.Id.ToString() : null));
cmd.Parameters.Add("?PropertyAction", (po.PropertyAction != null ? po.PropertyAction.Id.ToString() : null));
cmd.Parameters.Add("?Title", po.Title);
cmd.Parameters.Add("?PropertyDescription", po.PropertyDescription);
cmd.Parameters.Add("?RoomCount", po.RoomCount);
cmd.Parameters.Add("?TotalSpace", po.TotalSpace);
cmd.Parameters.Add("?LivingSpace", po.LivingSpace);
cmd.Parameters.Add("?KitchenSpace", po.KitchenSpace);
cmd.Parameters.Add("?BuildingTypeName", (po.BuildingTypeName != null ? po.BuildingTypeName.Id.ToString() : null));
cmd.Parameters.Add("?Floor", po.Floor);
cmd.Parameters.Add("?CountFloors", po.CountFloors);
cmd.Parameters.Add("?IsNewBuilding", po.IsNewBuilding);
cmd.Parameters.Add("?BalconAvailable", po.BalconAvailable);
cmd.Parameters.Add("?BalconSpace", po.BalconSpace);
cmd.Parameters.Add("?isBalconGlassed", po.isBalconGlassed);
cmd.Parameters.Add("?ContactName", po.ContactName);
cmd.Parameters.Add("?Price", po.Price);
cmd.Parameters.Add("?Currency", (po.Currency != null ? po.Currency.Id.ToString() : null));
cmd.Parameters.Add("?PriceForTypeName", (po.PriceForTypeName != null ? po.PriceForTypeName.Id.ToString() : null));
cmd.Parameters.Add("?NoCommission", po.NoCommission);
cmd.Parameters.Add("?Phone1", po.Phone1);
cmd.Parameters.Add("?Phone2", po.Phone2);
cmd.Parameters.Add("?Phone3", po.Phone3);
cmd.Parameters.Add("?Periods", (po.Periods != null ? po.Periods.Id.ToString() : null));
cmd.Parameters.Add("?CreatedDate", po.CreatedDate);
cmd.Parameters.Add("?SourceUrl", po.SourceUrl);
cmd.Parameters.Add("?UserOwner", (po.UserOwner != null ? po.UserOwner.Id.ToString() : null));
cmd.Parameters.Add("?DistanceToCity", po.DistanceToCity);
cmd.Parameters.Add("?WCType", (po.WCType!=null?po.WCType.Id.ToString():null));
cmd.Parameters.Add("?CountPhotos", po.CountPhotos);
cmd.Parameters.Add("?CommercialPropertyType", (po.CommercialPropertyType != null ? po.CommercialPropertyType.Id.ToString() : null));
cmd.Parameters.Add("?ServiceType", (po.ServiceType != null ? po.ServiceType.Id.ToString() : null));
cmd.Parameters.Add("?IsActive", po.IsActive);
cmd.Parameters.Add("?IsDeleted", po.IsDeleted);
cmd.Parameters.Add("?DeletedDate", po.DeletedDate);
cmd.Parameters.Add("?LinkOfObjectGrab", po.LinkOfObjectGrab);
result = Convert.ToInt32(cmd.ExecuteScalar());
oConn.Close();
}
return result;
}
private void button1_Click(object sender, EventArgs args)
{
SaveFileDialog sfd = new SaveFileDialog();
if (sfd.ShowDialog(this) == DialogResult.OK)
{
BinaryFormatter bf = new BinaryFormatter();
List<Runner> runners = new List<Runner>();
Event e = new Event();
List<Result> results = new List<Result>();
List<RadioControl> radious = new List<RadioControl>();
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("");
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from runners where tavid=" + textBox1.Text;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
runners.Add(new Runner()
{
Name = reader["name"] as string,
Class = reader["class"] as string,
club = reader["club"] as string,
dbId = Convert.ToInt32(reader["dbid"])
});
}
e.Runners = runners.ToArray();
reader.Close();
cmd.CommandText = "select * from results where tavid=" + textBox1.Text;
reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(new Result()
{
control = Convert.ToInt32(reader["control"]),
status = Convert.ToInt32(reader["status"]),
time = Convert.ToInt32(reader["time"]),
changed = Convert.ToDateTime(reader["changed"]),
dbId = Convert.ToInt32(reader["dbid"])
});
}
e.Results = results.ToArray();
reader.Close();
cmd.CommandText = "select * from splitcontrols where tavid=" + textBox1.Text;
reader = cmd.ExecuteReader();
while (reader.Read())
{
radious.Add(new RadioControl()
{
classname = reader["classname"] as string,
code = Convert.ToInt32(reader["code"]),
corder = Convert.ToInt32(reader["corder"]),
name = reader["name"] as string
});
}
e.RadioControls = radious.ToArray();
reader.Close();
cmd.CommandText = "select * from login where tavid=" + textBox1.Text;
reader = cmd.ExecuteReader();
if (reader.Read())
{
e.CompDate = Convert.ToDateTime(reader["compDate"]);
e.CompId = Convert.ToInt32(textBox1.Text);
e.CompName = reader["compname"] as string;
e.organizer = reader["organizer"] as string;
e.Public = Convert.ToBoolean(reader["public"]);
}
reader.Close();
FileStream fs = File.Create(sfd.FileName);
bf.Serialize(fs, e);
fs.Close();
conn.Close();
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(textBox2.Text);
try
{
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
double numSeconds = ((TimeSpan)(replayEvent.Results.Max(x => x.changed) - replayEvent.Results.Min(x => x.changed))).TotalSeconds;
cmd.CommandText = "delete from login where tavid=" + replayEvent.CompId;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into login(tavid,user,pass,compName,organizer,compDate,public) values(?id,?user,?pass,?compName,?organizer,?compdate,?public)";
cmd.Parameters.AddWithValue("?id", replayEvent.CompId);
cmd.Parameters.AddWithValue("?user", "");
cmd.Parameters.AddWithValue("?pass", "");
cmd.Parameters.AddWithValue("?compName", replayEvent.CompName);
cmd.Parameters.AddWithValue("?organizer", replayEvent.organizer);
cmd.Parameters.AddWithValue("?compDate", replayEvent.CompDate);
cmd.Parameters.AddWithValue("?public", replayEvent.Public);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "delete from splitcontrols where tavid=" + replayEvent.CompId;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into splitcontrols(tavid,classname,name,code,corder) values(?id,?class,?name,?code,?corder)";
cmd.Parameters.AddWithValue("?id", replayEvent.CompId);
cmd.Parameters.AddWithValue("?class", "");
cmd.Parameters.AddWithValue("?name", "");
cmd.Parameters.AddWithValue("?code", 0);
cmd.Parameters.AddWithValue("?corder", 0);
foreach (var r in replayEvent.RadioControls)
{
cmd.Parameters["?class"].Value = r.classname;
cmd.Parameters["?name"].Value = r.name;
cmd.Parameters["?code"].Value = r.code;
cmd.Parameters["?corder"].Value = r.corder;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = "delete from runners where tavid=" + replayEvent.CompId;
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from results where tavid=" + replayEvent.CompId;
cmd.ExecuteNonQuery();
WebClient wc = new WebClient();
cmd.Parameters.AddWithValue("?compid", replayEvent.CompId);
cmd.Parameters.AddWithValue("?name", "");
cmd.Parameters.AddWithValue("?club", "");
cmd.Parameters.AddWithValue("?class", "");
cmd.Parameters.AddWithValue("?id", 0);
cmd.CommandText = "REPLACE INTO runners VALUES (?compid,?name,?club,?class,0,?id)";
foreach (var r in replayEvent.Runners)
{
cmd.Parameters["?name"].Value = r.Name;
cmd.Parameters["?club"].Value = r.club;
cmd.Parameters["?class"].Value = r.Class;
cmd.Parameters["?id"].Value = r.dbId;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("?compid", replayEvent.CompId);
cmd.Parameters.AddWithValue("?id", 0);
cmd.Parameters.AddWithValue("?control", -1);
cmd.Parameters.AddWithValue("?time", -1);
cmd.Parameters.AddWithValue("?status", -1);
cmd.CommandText = "REPLACE INTO Results VALUES(?compid,?id,?control,?time,?status,Now())";
if (!string.IsNullOrEmpty(textBox3.Text))
{
string startlist;
startlist = HttpUtility.HtmlDecode(wc.DownloadString(textBox3.Text));
foreach (var r in replayEvent.Runners)
{
int rIdx = startlist.IndexOf(r.Name);
if (rIdx > 0)
{
int sIdx = startlist.IndexOf(" class=\"t\">", rIdx);
sIdx += 11;
int eIdx = startlist.IndexOf("</td>", sIdx);
string start = startlist.Substring(sIdx, eIdx - sIdx);
string[] p = start.Split(':');
int starttime = 0;
if (p.Length == 3)
{
starttime = Convert.ToInt32(p[0]) * 360000
+ Convert.ToInt32(p[1]) * 6000 +
+Convert.ToInt32(p[2]) * 100;
}
else
{
starttime = Convert.ToInt32(p[0]) * 360000
+ Convert.ToInt32(p[1]) * 6000;
}
cmd.Parameters["?id"].Value = r.dbId;
cmd.Parameters["?control"].Value = 100;
cmd.Parameters["?time"].Value = starttime;
cmd.Parameters["?status"].Value = 0;
cmd.ExecuteNonQuery();
}
}
}
do
{
DateTime clock = replayEvent.Results.Min(x => x.changed);
DateTime endClock = replayEvent.Results.Max(x => x.changed);
IDbCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "delete from results where tavid = " + replayEvent.CompId + " and control <> 100";
cmd2.ExecuteNonQuery();
while (clock <= endClock)
{
if (backgroundWorker1.CancellationPending)
break;
var next = replayEvent.Results.Where(x => x.changed >= clock).OrderBy(x => x.changed).First().changed;
IEnumerable<Result> changes = null;
if (((TimeSpan)(next - clock)).TotalMinutes > 10)
{
changes = replayEvent.Results.Where(x => x.changed >= clock && x.changed <= next);
clock = next;
}
else
{
changes = replayEvent.Results.Where(x => x.changed == clock);
}
lblTime.Invoke(new MethodInvoker(delegate
{
lblTime.Text = clock.ToString("yyyy-MM-dd HH:mm:ss");
}));
foreach (var c in changes)
{
cmd.Parameters["?id"].Value = c.dbId;
cmd.Parameters["?control"].Value = c.control;
cmd.Parameters["?time"].Value = c.time;
cmd.Parameters["?status"].Value = c.status;
cmd.ExecuteNonQuery();
}
if (jumpToNext)
{
clock = replayEvent.Results.Where(x => x.changed > clock).OrderBy(x => x.changed).First().changed;
jumpToNext = false;
}
else
{
clock = clock.AddSeconds(1);
}
Thread.Sleep(1000);
}
} while (!backgroundWorker1.CancellationPending);
}
finally
{
conn.Close();
}
}
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');
}
public static void GenerateCodeForTable(string tablename, string templatetext, string destination)
{
var template = templatetext.Replace("[TABLE]", tablename.ToLower());
using (var sw = new System.IO.StreamWriter(destination + tablename + ".cs"))
using (var con = new MySql.Data.MySqlClient.MySqlConnection(GetMangosConnectionString))
{
con.Open();
using (var command = con.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = string.Format("select * from {0} limit 1", tablename);
try
{
var sb_object = new StringBuilder();
var sb_Insert_part1 = new StringBuilder();
var sb_Insert_part2 = new StringBuilder();
var sb_Insert_part3 = new StringBuilder();
var sb_Update = new StringBuilder();
var sb_Delete = new StringBuilder();
sb_Insert_part1.Append("INSERT IGNORE INTO `\" + TableName + \"` (");
sb_Insert_part2.Append(" VALUES (");
sb_Insert_part3.Append("\", ");
sb_Update.AppendLine("\t\t\tsb.Append(\"UPDATE `\" + TableName + \"` SET \");");
using (var dr = command.ExecuteReader())
{
for (int i = 0; i < dr.FieldCount; i++)
{
string fieldname = dr.GetName(i).ToLower();
string valuename = fieldname;
if (valuename == "class") { valuename = "class_"; }
if (valuename == "event") { valuename = "event_"; }
if (i != 0)
{
sb_Update.AppendLine("\t\t\tif(" + valuename + " != null)");
sb_Update.AppendLine("\t\t\t{");
if (dr.GetFieldType(i) == typeof(System.String))
{
sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + " + valuename + ".ToSQL() + \"'\");");
}
else if (dr.GetFieldType(i) == typeof(System.Single))
{
sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + ((Decimal)" + valuename + ".Value).ToString() + \"'\");");
}
else
{
sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + " + valuename + ".Value.ToString() + \"'\");");
}
sb_Update.AppendLine("\t\t\t}");
}
if (i == dr.FieldCount - 1)
{
sb_Insert_part1.Append("`" + fieldname + "`{" + (i + 1).ToString() + "})");
sb_Insert_part2.Append("'{" + i.ToString() + "}'{" + (i + 2).ToString() + "});");
if (dr.GetFieldType(i) == typeof(System.String))
{
sb_Insert_part3.Append(valuename + ".ToSQL(), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
}
else if (dr.GetFieldType(i) == typeof(System.Single))
{
sb_Insert_part3.Append("((Decimal)" + valuename + ".GetValueOrDefault()), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
}
else
{
sb_Insert_part3.Append(valuename + ".GetValueOrDefault(), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
}
}
else
{
sb_Insert_part1.Append(string.Format("`{0}`, ", fieldname));
sb_Insert_part2.Append("'{" + i.ToString() + "}', ");
if (dr.GetFieldType(i) == typeof(System.String))
{
sb_Insert_part3.Append(valuename + ".ToSQL(), ");
}
else if (dr.GetFieldType(i) == typeof(System.Single))
{
sb_Insert_part3.Append("((Decimal)" + valuename + ".GetValueOrDefault()), ");
}
else
{
sb_Insert_part3.Append(valuename + ".GetValueOrDefault(), ");
}
}
if (dr.GetFieldType(i) == typeof(System.String))
{
sb_object.AppendLine(string.Format("\t\tpublic {0} {1};", dr.GetFieldType(i), valuename));
}
else
{
sb_object.AppendLine(string.Format("\t\tpublic {0}? {1};", dr.GetFieldType(i), valuename));
}
}
string idvaluename = dr.GetName(0).ToLower();
if (idvaluename == "class") { idvaluename = "class_"; }
if (idvaluename == "event") { idvaluename = "event_"; }
sb_Update.AppendLine("\t\t\t\tsb = sb.Replace(\"\\r\\n\", \", \");");
sb_Update.AppendLine("\t\t\t\tsb.Append(\" WHERE `" + dr.GetName(0).ToLower() + "`='\" + " + idvaluename + ".Value.ToString() + \"';\");");
sb_Update.AppendLine("\t\t\t\tsb = sb.Replace(\", WHERE\", \" WHERE\");");
sb_Delete.Append(" `" + dr.GetName(0).ToLower() + "`='\" + " + idvaluename + ".Value.ToString() + \"';\"");
}
template = template.Replace("[FIELDS]", sb_object.ToString());
template = template.Replace("[INSERT]", sb_Insert_part1.ToString() + sb_Insert_part2.ToString() + sb_Insert_part3.ToString());
template = template.Replace("[UPDATE]", sb_Update.ToString());
template = template.Replace("[DELETE]", sb_Delete.ToString());
sw.Write(template);
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}, {1}", exc.Message, command.CommandText);
}
}
}
}
private void importar()
{
label1.Text = "Iniciando Importação. Aguarde";
label1.Visible = true;
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
limpaTabelas();
label1.Text = "Dados locais apagados";
Application.DoEvents();
//abre conexao
try
{
myconn = new MySql.Data.MySqlClient.MySqlConnection(strconn);
myconn.Open();
}
catch (Exception ex)
{
MessageBox.Show("Não foi possível conectar ao servidor, verifique a sua conexão com a internet.");
return;
}
if (myconn.State == ConnectionState.Open)
{
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
//inicia a transacao
MySql.Data.MySqlClient.MySqlCommand mycommand = myconn.CreateCommand();
mycommand.Connection = myconn;
mycommand.CommandText =
"SELECT `uso_mutuo`.`id`,"+
"`uso_mutuo`.`descricao` "+
"FROM `eletrocad`.`uso_mutuo`";
//mycommand.Parameters.Clear();
//mycommand.Parameters.AddWithValue("estado", cbEstado.Text);
//mycommand.Parameters.AddWithValue("ano", txtAno.Value);
try
{
MySql.Data.MySqlClient.MySqlDataReader reader = mycommand.ExecuteReader();
while (reader.Read())
{
//
//insere nova programação
db.eletrocadDataSet.uso_mutuoRow pipRow;
pipRow = eletrocadDataSet.uso_mutuo.Newuso_mutuoRow();
pipRow.id = reader.GetInt32("id");
pipRow.descricao = reader.GetString("descricao");
eletrocadDataSet.uso_mutuo.Rows.Add(pipRow);
}
uso_mutuoTableAdapter.Update(eletrocadDataSet.uso_mutuo);
eletrocadDataSet.uso_mutuo.AcceptChanges();
uso_mutuoTableAdapter.Fill(eletrocadDataSet.uso_mutuo);
reader.Close();
Cursor.Current = Cursors.Default;
Application.DoEvents();
MessageBox.Show("Informações recuperadas com sucesso");
}
catch (Exception ex)
{
MessageBox.Show("Não foi possível importar: " + ex.Message);
}
finally
{
//myconn.Close();
}
}
myconn.Close();
label1.Visible = false;
Cursor.Current = Cursors.Default;
Application.DoEvents();
}