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(); }