public CityForeCast[] GetForecastFromDatabase()
{
//SqlConnection connection = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDb;Initial Catalog=weatherForecast;Integrated Security=True;Pooling=False");
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(@"Server=eu-cdbr-azure-west-c.cloudapp.net;Database=BDMeteo;Uid=b95badd8e1dbad;Pwd=bde4c7b6;Pooling=True");
connection.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM tablemeteo", connection);
MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader();
List<CityForeCast> forecasts = new List<CityForeCast>();
while (reader.Read())
{
CityForeCast forecast = new CityForeCast();
forecast.City = (string)reader["City"];
forecast.description = (string)reader["Description"];
forecast.MaxTemp = (decimal)reader["Temperature"];
forecasts.Add(forecast);
}
reader.Close(); // Fermer le reader avant de fermer la connection
connection.Close();
return forecasts.ToArray();
//var ctx = new weatherForecastEntities();
//var forecast = ctx.Tables.Select(f => new CityForeCast()
//{
// City = f.City,
// description = f.Description,
// MaxTemp = (decimal)f.Temperature
//});
//return forecast;
}
public static string FetchePassword()
{
string passwordStr = string.Empty;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
msqlCommand.CommandText = "Select password from sptinfo;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
msqlReader.Read();
passwordStr = msqlReader.GetString("password");
}
catch (Exception er)
{
//Assert//.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
return passwordStr;
}
public static IDbConnection Open()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection(s_connectionString);
connection.Open();
return(connection);
}
/// <summary>
/// Для выполнения запросов к MySQL с возвращением 1 параметра.
/// </summary>
/// <param name="sql">Текст запроса к базе данных</param>
/// <param name="connection">Строка подключения к базе данных</param>
/// <returns>Возвращает значение при успешном выполнении запроса, текст ошибки - при ошибке.</returns>
public static MyResult SqlScalar(string sql, string connection)
{
MyResult result = new MyResult();
try
{
MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
connRC.Open();
try
{
result.ResultText = commRC.ExecuteScalar().ToString();
result.HasError = false;
}
catch (Exception ex)
{
result.ErrorText = ex.Message;
result.HasError = true;
}
connRC.Close();
}
catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером.
{
result.ErrorText = ex.Message;
result.HasError = true;
}
return result;
}
public Character GetCharacter(int id)
{
Character character = null;
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand
("SELECT CharacterID, Name, Description, UserID, CharacterID, Level "
+ "FROM Characters WHERE CharacterID = @CharacterID"
, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CharacterID", id);
con.Open();
var rdrCharacter = cmd.ExecuteReader();
while (rdrCharacter.Read())
{
character = new Character();
character.ID = rdrCharacter.GetInt32("CharacterID");
character.Name = rdrCharacter["Name"].ToString();
character.Description = rdrCharacter["Description"].ToString();
character.UserID = rdrCharacter.GetString("UserID");
character.Level = (int)rdrCharacter["Level"];
character.Specialties = GetCharacterSpecialties(character).ToList();
}
con.Close();
}
return(character);
}
public IEnumerable <Specialty> GetCharacterSpecialties(Character character)
{
var specialties = new List <Specialty>();
var systemdb = new TephraSystemDataAccessLayer();
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand(
"SELECT ID, CharacterID, Ch.SpecialtyID " +
"FROM CharactersSpecialty AS Ch JOIN Specialties AS Sp ON Sp.SpecialtyID = Ch.SpecialtyID " +
"WHERE CharacterID = @CharacterID ORDER BY ID"
, con
);
cmd.Parameters.AddWithValue("@CharacterID", character.ID);
con.Open();
var rdrCharacter = cmd.ExecuteReader();
while (rdrCharacter.Read())
{
var specialtyID = rdrCharacter.GetInt32("SpecialtyID");
var specialty = systemdb.GetSpecialty(specialtyID);
specialty.ID = rdrCharacter.GetInt32("ID");
specialty.CharacterID = rdrCharacter.GetInt32("CharacterID");
specialties.Add(specialty);
}
con.Close();
}
return(specialties);
}
private void DeleteStock(string stockToDelete)
{
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
msqlCommand.CommandText = "DELETE FROM stock WHERE id= @vendorIdToDelete";
msqlCommand.Parameters.AddWithValue("@vendorIdToDelete", stockToDelete);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static void EditSptPassword(string passwordStr)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
int idSptinfo = 1;
msqlCommand.CommandText = "UPDATE sptinfo SET password='******' WHERE id_sptinfo='" + idSptinfo + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
//MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void EditSptInfo(SettingsData returnEditedsettingsData)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
int idSptinfo = 1;
msqlCommand.CommandText = "UPDATE sptinfo SET name='" + returnEditedsettingsData.Name + "', address='" + returnEditedsettingsData.Address + "', phone='" +
returnEditedsettingsData.Phone + "', bill_disclaimer='" + returnEditedsettingsData.BillDisclaimer + "', invoice_prefix='" + returnEditedsettingsData.InvoicePrefix +
"' WHERE id_sptinfo='" + idSptinfo + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void OpenMySqlConnection(MySql.Data.MySqlClient.MySqlConnection Connection)
{
if (Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken)
{
Connection.Open();
}
}
public List <string> GetComments()
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["GroupsConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
try
{
MySql.Data.MySqlClient.MySqlDataReader reader;
DataTable msg = new DataTable();
conn.Open();
List <string> comments = new List <string>();
queryStr = "SELECT gc.* FROM app_groups.group_comments gc WHERE gc.id_group = " + id;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
msg.Load(reader = cmd.ExecuteReader());
foreach (DataRow dr in msg.Rows)
{
comments.Add(dr["comment"]);
}
reader.Close();
return(comments);
}
catch (Exception ex)
{
//throw ex;
}
}
private static void RunQueries(object StateInfo)
{
for (int i = 0; i < 600; i++)
{
//dotnetuser - MySQL12345!!
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(@"Server=localhost;Database=employees;Uid=root"))
//using (var comm = new MySql.Data.MySqlClient.MySqlCommand(@"SELECT (emp_no * 3.14) + 10, emp_no, first_name, last_name FROM employees", conn))
using (var comm = new MySql.Data.MySqlClient.MySqlCommand(@"SELECT CAST(mysqldotnet_string(""MySQLCustomClass.CustomMySQLClass"", ""MULTI"", first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name) AS char) FROM employees.employees", conn))
using (var dt = new System.Data.DataTable())
{
comm.CommandTimeout = 600;
conn.Open();
using (var da = new MySql.Data.MySqlClient.MySqlDataAdapter(comm))
{
var start = DateTime.Now;
try
{
da.Fill(dt);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
var stop = DateTime.Now;
lock (objLock)
{
Console.WriteLine("Retrieved {0} records in {1} milliseconds", dt.Rows.Count, (stop - start).TotalMilliseconds);
}
}
conn.Close();
}
}
}
public IEnumerable <BeansPersona> GetListPersonaRepository()
{
IEnumerable <BeansPersona> oData = null;
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
try
{
//oData = connection.Query<BeansPersona>("ppanana.mysql.database.azure.com.examen.SP_GetPersona", commandType: System.Data.CommandType.StoredProcedure);
oData = connection.Query <BeansPersona>("SELECT nId ID , vNombres Nombres , vApellidos Apellidos , nEdad Edad , dteFechaNacimiento FechaNacimiento FROM personas", System.Data.CommandType.Text);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Dispose();
connection.Close();
}
}
return(oData);
}
public IEnumerable <Character> GetCharacters(string userID)
{
var characters = new List <Character>();
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand
("SELECT Name, Description, CharacterID, Level FROM Characters WHERE UserID = @UserID", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@UserID", userID);
con.Open();
var rdrCharacter = cmd.ExecuteReader();
while (rdrCharacter.Read())
{
var character = new Character();
character.Name = rdrCharacter["Name"].ToString();
character.Description = rdrCharacter["Description"].ToString();
character.ID = (int)rdrCharacter["CharacterID"];
character.UserID = userID;
character.Level = (int)rdrCharacter["Level"];
characters.Add(character);
}
con.Close();
}
return(characters);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtValueAddedTax.Text))
{
MessageBox.Show("Field is empty.");
return;
}
using (MySql.Data.MySqlClient.MySqlConnection connection = SalesAndInventory.Classes.Configuration.getConnection())
{
try
{
connection.Open();
string sql = "UPDATE users SET vat = @vat WHERE user_id = @userId";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection);
command.Parameters.AddWithValue("@userId", frmHome.userId);
command.Parameters.AddWithValue("@vat", txtValueAddedTax.Text);
command.ExecuteNonQuery();
frmHome.vat = int.Parse(txtValueAddedTax.Text);
MessageBox.Show("Successfully Updated.");
this.Close();
}
catch (MySql.Data.MySqlClient.MySqlException exception)
{
MessageBox.Show(exception.Message);
}
}
}
protected void submitEventMethod(object sender, EventArgs e)
{
String connS = System.Configuration.ConfigurationManager.ConnectionStrings["VideogameStore_String"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connS);
conn.Open();
query = "SELECT * FROM tbl_dipendente WHERE COD_dp=" + Convert.ToInt32(usernameTextBox.Text) + " AND password='******';";
command = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
reader = command.ExecuteReader();
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("name"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("LoggedIn.aspx", false);
}
else
{
passwordTextBox.Text = "User non trovato";
}
reader.Close();
conn.Close();
}
public Stream ShowEmpImage3(Int32 empno, String empno1)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
queryStr = "";
queryStr = "SELECT imagepram FROM param WHERE codeuser= @CD AND numzone= @NM";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CD", empno);
cmd.Parameters.AddWithValue("@NM", empno1);
conn.Open();
object img = cmd.ExecuteScalar();
try
{
return(new MemoryStream((byte[])img));
}
catch
{
return(null);
}
finally
{
conn.Close();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
txtSalvageValue.Text = Wahventory.classes.Format.formatToPeso(salvageValue);
generate();
DialogResult result = MessageBox.Show(null, "Are you sure you want to update the depreciation to your item?", "Update depreciation", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (result == System.Windows.Forms.DialogResult.Yes)
{
using (MySql.Data.MySqlClient.MySqlConnection connection = Database.getConnection())
{
try
{
connection.Open();
String sql = "UPDATE tbldepreciation SET salvage_value = @salvageValue,lifespan = @lifeSpan,depreciation = @deprec WHERE depreciation_id = @deprecId;";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection);
command.Parameters.AddWithValue("@salvageValue", Format.formatToDecimal(txtSalvageValue.Text));
command.Parameters.AddWithValue("@lifeSpan", txtLifespan.Text);
command.Parameters.AddWithValue("@deprecId", depreciationId);
command.Parameters.AddWithValue("@deprec", Format.formatToDecimal(lblDepreciation.Text));
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show(null, "Depreciation was successfully updated to your item.", "Depreciation", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}finally{
connection.Close();
}
}
}
}
protected void LogIn()
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
query = "SELECT * FROM collectionapp.users WHERE Username='******' AND PWord='" + TextBox2.Text + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
id = reader.GetString(reader.GetOrdinal("ID"));
firstname = reader.GetString(reader.GetOrdinal("FirstName"));
IsAdmin = reader.GetString(reader.GetOrdinal("IsAdmin"));
}
if (reader.HasRows)
{
Session["Firstname"] = firstname;
Session["ID"] = id;
Session["Admin"] = IsAdmin;
Response.BufferOutput = true;
Response.Redirect("Collection.aspx", false);
}
else
{
Label1.Visible = true;
}
reader.Close();
conn.Close();
}
//UpdateSupplier takes the supplier's old ID before any updates, and updates the database with the values the user entered
//Returns true is successful, false otherwise
public Boolean UpdateSupplier(Int32 oldId)
{
//Connect to the Database and run the Update command
string connStr = "server=csdatabase.eku.edu;user=stu_csc340;database=csc340_db;port=3306;password=Colonels18;";
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = "UPDATE memonSupplier SET supplierID = " + this.SupplierId +
", name = '" + this.Name + "', phone = " + this.Phone +
" , supplierType = '" + this.SupplierType + "' WHERE "
+ "supplierID= " + oldId + ";";
Console.WriteLine(sql);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
//if the supplier was succesfully updated, return true, else it will return false by default
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("Supplier Updated");
return(true);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Console.WriteLine("Done.");
return(false);
}
/*public ArrayList getFriends(long ID)
* {
* MySql.Data.MySqlClient.MySqlConnection conn;
* string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
* conn = new MySql.Data.MySqlClient.MySqlConnection();
* try
* {
* conn.ConnectionString = myConnectionString;
* conn.Open();
* ArrayList personArrayL = new ArrayList();
* MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
*
* String sqlString = "SELECT FriendFBID FROM friends WHERE UserFBID=" + ID;
*
* MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
*
*
* mySQLReader = cmd.ExecuteReader();
* while (mySQLReader.Read())
* {
* Friends f = new Friends();
* f.FriendFBID = mySQLReader.GetInt64(0);
* personArrayL.Add(f);
* }
*
*
* return personArrayL;
* }
* catch (MySql.Data.MySqlClient.MySqlException ex)
* {
* throw ex;
* }
* finally
* {
* conn.Close();
* }
*
*
* }*/
public String saveFriend(Friends personToSave)
{
MySql.Data.MySqlClient.MySqlConnection conn;
//string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
string myConnectionString = connectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
String sqlString = "INSERT INTO Friends (UserFBID, FriendFBID)"
+ "VALUES('" + personToSave.UserFBID + "','" + personToSave.FriendFBID + "');";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(personToSave.UserFBID);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public List <User> GetUsers()
{
List <User> ret = null;
string conStr = Secrets.MoodleConnectionString; //ConfigurationManager.ConnectionStrings["Moodle"].ConnectionString;
var con = new MySql.Data.MySqlClient.MySqlConnection(conStr);
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
string str = string.Format("SELECT us.id, us.firstname, us.lastname FROM mdl_user us ORDER BY us.lastname ASC");
var command = new MySql.Data.MySqlClient.MySqlCommand(str, con);
var reader = command.ExecuteReader();
User u = new User();
if (reader.HasRows)
{
ret = new List <User>();
while (reader.Read())
{
u = new User();
u.UserId = Convert.ToInt32(reader[reader.GetOrdinal("id")].ToString());
u.FullName = reader[reader.GetOrdinal("firstname")].ToString() + " " + reader[reader.GetOrdinal("lastname")].ToString();
ret.Add(u);
}
}
return(ret);
}
public THistory getLastHistory(int simulationId)
{
//--Data Base Access Variables--
MySql.Data.MySqlClient.MySqlCommand dbCommand = new MySql.Data.MySqlClient.MySqlCommand();
MySql.Data.MySqlClient.MySqlConnection dbConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
MySql.Data.MySqlClient.MySqlDataReader dbDataReader;
dbCommand.Connection = dbConnection;
//------------------------------
dbCommand.CommandText = "SELECT * FROM TB_HISTORYS WHERE (simulation_id = " + simulationId.ToString() + ") ORDER BY dialogDateTime DESC";
try
{
dbConnection.Open();
THistory tHistory;
dbDataReader = dbCommand.ExecuteReader();
dbDataReader.Read();
tHistory = new THistory(Convert.ToInt32(dbDataReader["simulation_id"]), Convert.ToInt32(dbDataReader["user_tu_id"]), dbDataReader["user_note"].ToString(), Convert.ToInt32(dbDataReader["designer_tu_id"]), dbDataReader["designer_note"].ToString(), Convert.ToDateTime(dbDataReader["dialogDateTime"]));
tHistory.id = Convert.ToInt32(dbDataReader["id"].ToString());
dbDataReader.Close();
dbConnection.Close();
return(tHistory);
}
catch
{
dbConnection.Close();
System.Threading.Thread.Sleep(20000);
return(null);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public override System.Data.IDbConnection GetOpenConnection()
{
DMSFrameException.ThrowIfNullEmpty(this.ConnectionString);
System.Data.IDbConnection conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
conn.Open();
return(conn);
}
public void selectUserTransitionUtteranceOption(int transitionId, string userNote)
{
//--Data Base Access Variables--
MySql.Data.MySqlClient.MySqlCommand dbCommand = new MySql.Data.MySqlClient.MySqlCommand();
MySql.Data.MySqlClient.MySqlConnection dbConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
dbCommand.Connection = dbConnection;
//-----------------------------
int queryResult = -1;
try
{
dbConnection.Open();
TTransitionUtterance tTransitionUtterance = getTransitionUtterance(transitionId);
THistory tHistory = new THistory(tTransitionUtterance.simulation_id, tTransitionUtterance.id, userNote, -1, "", DateTime.Now);
addHistory(tHistory);
do
{
dbCommand.CommandText = "DELETE FROM TB_TRANSITION_UTTERANCES WHERE simulation_id = " + tTransitionUtterance.simulation_id + " AND history_id = -1";
queryResult = dbCommand.ExecuteNonQuery();
//Futuramente deletar os que não possuem correspondência.
} while (queryResult < 0);
}
catch
{
System.Threading.Thread.Sleep(20000);
}
dbConnection.Close();
}
public void Add(Character character)
{
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand(
"INSERT INTO Characters(UserID, Name, Description, Level) " +
"VALUES(@UserID, @Name, @Description, @Level)"
, con
);
cmd.Parameters.AddWithValue("@UserID", character.UserID);
cmd.Parameters.AddWithValue("@Name", character.Name);
cmd.Parameters.AddWithValue("@Description", character.Description);
cmd.Parameters.AddWithValue("@Level", character.Level);
con.Open();
cmd.ExecuteNonQuery();
cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT LAST_INSERT_ID()", con);
var CharacterID = Convert.ToInt32(cmd.ExecuteScalar());
foreach (var specialty in character.Specialties)
{
AddCharacterSpecialty(CharacterID, specialty);
}
con.Close();
}
}
private bool InitTable()
{
if (lastInit != null && (DateTime.Now - lastInit.Value).TotalHours < 24)
{
return(true);
}
lock (init_locker)
{
if (lastInit != null && (DateTime.Now - lastInit.Value).TotalHours < 24)
{
return(true);
}
if (string.IsNullOrEmpty(Config.DBConnString))
{
return(false);
}
using (MySql.Data.MySqlClient.MySqlConnection dbconn = new MySql.Data.MySqlClient.MySqlConnection(Config.DBConnString))
{
DateTime begindate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
DateTime endtime = begindate.AddDays(PreDayCount);
dbconn.Open();
MySql.Data.MySqlClient.MySqlDataAdapter ada = new MySql.Data.MySqlClient.MySqlDataAdapter(" show create table timewatch;", dbconn);
System.Data.DataTable tb = new System.Data.DataTable();
ada.Fill(tb);
if (tb.Rows.Count == 0)
{
return(false);
}
string createsql = tb.Rows[0][1].ToString();
createsql = createsql.Substring(createsql.IndexOf('('));
ada = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + dbconn.Database + "' " +
" and TABLE_NAME like 'timewatch________' order by TABLE_NAME desc limit 1;", dbconn);
tb = new System.Data.DataTable();
ada.Fill(tb);
if (tb.Rows.Count > 0)
{
string t = tb.Rows[0][0].ToString();
begindate = DateTime.Parse(string.Format("{0}-{1}-{2}", t.Substring(9, 4), t.Substring(13, 2), t.Substring(15, 2)));
begindate = begindate.AddDays(1);
}
StringBuilder sb = new StringBuilder();
while (begindate.CompareTo(endtime) < 0)
{
sb.AppendFormat("CREATE TABLE {0} {1};\r\n", "`timewatch" + begindate.ToString("yyyyMMdd") + "`", createsql);
begindate = begindate.AddDays(1);
}
if (sb.Length > 0)
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = dbconn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
lastInit = DateTime.Now;
return(true);
}
}
protected void submitEventMethod(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "select * from webappdemo.userregistration where username='******'and password='******'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("firstname")) + " " +
reader.GetString(reader.GetOrdinal("middlename")) + " " +
reader.GetString(reader.GetOrdinal("lastname")) + " ";
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("LoggedIn.aspx", false);
}
else
{
passwordTextBox.Text = "invalid user";
}
reader.Close();
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string server = txtHost.Text;
string port = txtPort.Text;
string uid = txtUser.Text;
string password = txtPass.Text;
string bd = txtBD.Text;
string connectionString;
connectionString = "server=" + server + ";" + "uid=" +
uid + ";" + "pwd=" + password + ";" + "port=" + port + ";" + "database=" + bd + ";";
try
{
connection = new MySql.Data.MySqlClient.MySqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
MessageBox.Show("Conexion extiosa... presione OK para continuar");
TreeViewMySQL view = new TreeViewMySQL(connection, bd);
view.Show();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error de conexion");
}
}
public void prendidestinazionicliente(string cliente)
{
clientedestinazione.Clear();
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connessione);
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "Select * from cliente_destinazione where nomecliente = @nomecli";
cmd.Parameters.AddWithValue("@nomecli", cliente);
cmd.Connection = conn;
destinazioniadapter.SelectCommand = cmd;
destinazioniadapter.Fill(clientedestinazione);
destinazionibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(destinazioniadapter);
AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
foreach (DataRow row in clientedestinazione.Rows)
{
namesCollection.Add(row["indirizzo"].ToString());
}
destinazionetextbox.AutoCompleteCustomSource = namesCollection;
destinazionetextbox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
destinazionetextbox.AutoCompleteSource = AutoCompleteSource.CustomSource;
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
public static void Insert(string price, string createtime)
{
try
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(CONNSTR))
using (var cmd = new MySql.Data.MySqlClient.MySqlCommand($"INSERT INTO N(PLATFORM,TYPE,PRICE,UNIT,DEAL_TIME) VALUE('Huobi','BTC','{price}','USDT','{createtime}')", conn))
{
conn.Open();
//cmd.CommandText = ;
cmd.ExecuteNonQuery();
}
Console.WriteLine($"[DB]PRICE:{price};TIME{createtime}");
}
catch (Exception ex)
{
Console.WriteLine($"[DB][ERROR]{ex.Message.Replace("\n", " ").Replace("\r", " ")}");
try
{
System.IO.File.AppendAllText($"{System.Environment.CurrentDirectory}/error{DateTime.Now.ToString("yyyyMMdd")}.log", $"[DB][ERROR]{ex.Message.Replace("\n", " ").Replace("\r", " ")}\r\n");
}
catch
{
}
}
}
private bool TestDeConexion()
{
MySql.Data.MySqlClient.MySqlConnection Conexion = new MySql.Data.MySqlClient.MySqlConnection(TraerCadenaDeConexion());
try
{
Conexion.Open();
MessageBox.Show("Conexion realizada correctamente");
return(true);
}
catch (Exception ex)
{
string Mensaje = ex.Message;
if (ex.InnerException != null)
{
Mensaje += Environment.NewLine + "Inner Exception:" + ex.InnerException.Message;
}
MessageBox.Show(Mensaje, "Test De Conexion", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
finally
{
if (Conexion.State == ConnectionState.Open)
{
Conexion.Close();
}
Conexion = null;
}
}
public static DataTable GetData(string strSql)
{
if (strSql == null || strSql.Length == 0)
{
return(null);
}
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(string.Format(connstring, IP, User, Pass)))
{
try
{
conn.Open();
MySql.Data.MySqlClient.MySqlDataAdapter sqlData = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql, conn);
DataTable data = new DataTable();
sqlData.Fill(data);
conn.Close();
return(data);
}
catch (Exception ex)
{
MyLog4Net.Container.Instance.Log.DebugWithDebugView("GetData ret err:" + ex);
return(null);
}
}
}
public Stream ShowEmpImage1(Int32 empno)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
queryStr = "";
queryStr = "SELECT attachement FROM email WHERE id = @ID ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", empno);
conn.Open();
object img = cmd.ExecuteScalar();
try
{
return(new MemoryStream((byte[])img));
}
catch
{
return(null);
}
finally
{
conn.Close();
}
}
//执行 sql 语句字符串,0 表示 执行成功
public static bool ExecuteSql(string strSql)
{
if (strSql == null || strSql.Length == 0)
{
return(true);
}
bool nRet = false;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(string.Format(connstring, IP, User, Pass)))
{
try
{
conn.Open();
MySql.Data.MySqlClient.MySqlScript script = new MySql.Data.MySqlClient.MySqlScript(conn, strSql);
int ret = script.Execute();
conn.Close();
nRet = true;
}
catch (Exception ex)
{
MyLog4Net.Container.Instance.Log.DebugWithDebugView("ExecuteSql ret err:" + ex);
nRet = false;
}
}
return(nRet);
}
public void OpenConnection()
{
if (string.IsNullOrEmpty(MySqlServer) || string.IsNullOrEmpty(MySqlUser) || string.IsNullOrEmpty(MySqlDatabase))
{
throw new ArgumentException();
}
if (mMySqlConn.State != ConnectionState.Open)
{
mMySqlConn.ConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", MySqlServer, MySqlUser, MySqlPassword, MySqlDatabase);
try
{
mMySqlConn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException)
{
}
}
else
{
MessageBox.Show("The connection is already open");
}
if (mMySqlConn.State != ConnectionState.Open)
{
throw new TimeoutException("Can't connect to the server.");
}
}
private void button1_Click(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(string.Format("Server={1};Port={2};Uid={3};Pwd={4};Database={0};", "sagaeco", "127.0.0.001", "3306", "root", "lh630206"));
string sqlstr = string.Format("INSERT INTO `login`(`username`,`password`,`deletepass`) VALUES ('{0}','{1}','{2}')", this.textBox1.Text, GetMD5(this.textBox2.Text), GetMD5(this.textBox3.Text));
con.Open();
MySql.Data.MySqlClient.MySqlHelper.ExecuteNonQuery(con, sqlstr, null);
}
private void Form1_Load(object sender, EventArgs e)
{
var cn = new MySql.Data.MySqlClient.MySqlConnection();
cn.ConnectionString = "Server=mysql2.altaortopedia.com.br;Database=altaortopedia;Uid=altaortopedia;Pwd=alta@38072264";
cn.Open();
cn.Close();
}
private void button1_Click_1(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection myconn = null;
myconn = new MySql.Data.MySqlClient.MySqlConnection("Database=qsgj;Data Source=127.0.0.1;User Id=root;Password=;pooling=false;CharSet=utf8;port=3306");
myconn.Open();
myconn.Close();
}
public static int Execute(string sql)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString()))
{
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
}
public static DataTable Query(string sql)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString()))
{
conn.Open();
var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, conn);
var ds = new DataSet();
var reader = adapter.Fill(ds);
conn.Close();
return ds.Tables[0];
}
}
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();
}
}
private static MySql.Data.MySqlClient.MySqlConnection OpenDbConnection()
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password="******";database=" + dbmsCurrent + ";persist security info=False");
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
return msqlConnection;
}
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;
}
private void ConnectFetchFromSaleslistTable()
{
//define the connection reference and initialize it
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
TimeSpan diff = (TimeSpan)(endDatePicker.SelectedDate - startDatePicker.SelectedDate);
msqlCommand.CommandText = "SELECT * FROM saleslist where date(saleslist.dateSales) >= DATE_SUB( @enddate, INTERVAL @diff DAY);";
msqlCommand.Parameters.AddWithValue("@enddate", endDatePicker.SelectedDate);
msqlCommand.Parameters.AddWithValue("@diff", diff.Days);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_salesDataCollection.Clear();
while (msqlReader.Read())
{
SalesData salesData = new SalesData();
salesData.customerId = msqlReader.GetString("customerId");
salesData.customerName = msqlReader.GetString("customerName"); //add
salesData.dateSales = msqlReader.GetDateTime("dateSales");
salesData.invoiceNo = msqlReader.GetString("invoiceNo");
salesData.payment = msqlReader.GetDouble("payment");
salesData.totalAmount = msqlReader.GetDouble("totalAmount");
//salesData.serialNo = (_salesDataCollection.Count + 1).ToString();
_salesDataCollection.Add(salesData);
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void ConnectFetchFromSaleslistTable(string invoiceNumber)
{
//define the connection reference and initialize it
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT * FROM salesBilling WHERE invoiceNo = @invoiceNo;";
msqlCommand.Parameters.AddWithValue("@invoiceNo", invoiceNumber);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
double totalVat = 0.0;
while (msqlReader.Read())
{
BillingData billedData = new BillingData();
billedData.amount = msqlReader.GetDouble("amount");
billedData.productName = msqlReader.GetString("description");
billedData.quantity = msqlReader.GetDouble("quantity");
billedData.vat = msqlReader.GetDouble("vat");
billedData.calVat = Convert.ToDouble(billedData.amount) * Convert.ToDouble(billedData.vat) * (.01); ;
totalVat += billedData.calVat;
billedData.rate = msqlReader.GetDouble("rate");
billedData.serialNo = billingItemListView.Items.Count + 1; //msqlReader.GetString("serialNo");
_billingCollection.Add(billedData);
vatAmount.Content = totalVat;
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public void Connect()
{
m_connString = System.Configuration.ConfigurationManager.ConnectionStrings["db-utos"].ConnectionString;
Disconnect();
try
{
m_connection = new MySql.Data.MySqlClient.MySqlConnection();
m_connection.ConnectionString = m_connString;
m_connection.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Loads XML from local database
/// </summary>
public void ImportFromMySQL(Database.DatabaseConfig xmlSource, Database.DatabaseConfig destination)
{
_xmlSource = xmlSource;
_destination = destination;
XMLImport importer = new XMLImport();
Database.MySQL.Database myDB = new Database.MySQL.Database(_destination);
string connString = string.Format(DBConnectString, _xmlSource.ServerIp, _xmlSource.Schema, _xmlSource.Username, _xmlSource.Password);
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.Open();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT `fishbaseId`, `genus`, `species`, `xmlSummary`, `xmlPointData`, `xmlCommonNames`, `xmlPhotos` FROM `fishbaseraw`;";
using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
Model.FishClass fc = importer.Import(
reader.GetInt32("fishbaseId"),
reader.GetString("genus"),
reader.GetString("species"),
reader.GetString("xmlSummary"),
reader.GetString("xmlPointData"),
reader.GetString("xmlCommonNames"),
reader.GetString("xmlPhotos")
);
if (fc != null)
{
try
{
myDB.CreateFish(fc);
}
catch (Database.Exceptions.CreatureAlreadyExists ex)
{
myDB.UpdateFish(fc);
}
}
}
}
}
}
}
/*ejecuta la consulta que se pasa como parametro cogiendo la base de datos cuyo string de conexion esta en el parametro
conexion del fichero de configuracion*/
public void ejecutar_sql(string sql)
{
try
{
MySql.Data.MySqlClient.MySqlConnection mscon = new MySql.Data.MySqlClient.MySqlConnection(Properties.Settings.Default.conexion);
mscon.Open();
MySql.Data.MySqlClient.MySqlCommand mscom = new MySql.Data.MySqlClient.MySqlCommand(sql, mscon);
mscom.ExecuteNonQuery();
mscon.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
LinkException le = new LinkException(e.Message);
throw le;
}
}
private static int DoRegisterNewEmployeeInDb(EmployeeDetails employeeDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=Mmm_mb;persist security info=False");
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "INSERT INTO employees(employee_id,employee_name,employee_dob,employee_address,employee_phone_no,employee_email,employee_join_date,department,salary,employee_manager_id) "
+ "VALUES(@employee_id,@employee_name,@employee_dob,@employee_address,@employee_phone_no,@employee_email,@employee_join_date,@department,@salary,@employee_manager_id)";
msqlCommand.Parameters.AddWithValue("@employee_id", employeeDetails.employeeId);
msqlCommand.Parameters.AddWithValue("@employee_name", employeeDetails.employeeName);
msqlCommand.Parameters.AddWithValue("@employee_dob", employeeDetails.employeeDob);
msqlCommand.Parameters.AddWithValue("@employee_address", employeeDetails.employeeAddress);
msqlCommand.Parameters.AddWithValue("@employee_phone_no", employeeDetails.employeePhoneNumber);
msqlCommand.Parameters.AddWithValue("@employee_email", employeeDetails.employeeEmail);
msqlCommand.Parameters.AddWithValue("@employee_join_date", employeeDetails.employeeJoinDate);
msqlCommand.Parameters.AddWithValue("@department", employeeDetails.employeeDepartment);
msqlCommand.Parameters.AddWithValue("@salary", employeeDetails.employeeSalary);
msqlCommand.Parameters.AddWithValue("@employee_manager_id", employeeDetails.employeeManagerId);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
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 Button_Click(object sender, RoutedEventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; user id=root;password=technicise;database=my_schema;persist security info=false");
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlcommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlcommand.Connection = msqlConnection;
//define the command text
msqlcommand.CommandText = "insert into phonebook(id,name,mobileno)" + "values(@id,@name,@mobileno)";
//add values provided by user
msqlcommand.Parameters.AddWithValue("@id",id.Text);
msqlcommand.Parameters.AddWithValue("@name", name.Text);
msqlcommand.Parameters.AddWithValue("@mobileno", mobileno.Text);
msqlcommand.ExecuteNonQuery();
//close the connection
msqlConnection.Close();
//empty the text boxes
id.Text = null;
name.Text = null;
mobileno.Text = null;
MessageBox.Show("Info Added");
}
public void Install(Castle.Windsor.IWindsorContainer container, Castle.MicroKernel.SubSystems.Configuration.IConfigurationStore store)
{
List<Setting> settings;
using(var connection = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
{
connection.Open();
settings = connection.Query<Setting>("SELECT SettingName, SettingValue FROM settings WHERE SettingName LIKE 'facebook%'").ToList();
}
var facebookConfig = new FacebookConfig
{
FacebookAppSecret = settings.FirstOrDefault(s => s.SettingName == "FacebookAppSecret").SettingValue,
FacebookAppId = settings.FirstOrDefault(s => s.SettingName == "FacebookAppId").SettingValue,
OAuthCallbackPath = "/facebookoauth.axd"
};
container.Register(Component.For<FacebookConfig>().LifestyleSingleton().Instance(facebookConfig));
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
MySql.Data.MySqlClient.MySqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (MySql.Data.MySqlClient.MySqlCommand) CommandsCollection[storedProcedure];
}
else
{
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new MySql.Data.MySqlClient.MySqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (MySql.Data.MySqlClient.MySqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
protected void LoginButton_Click(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
using (conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM db_9f3dca_webapp.user_registration WHERE username='******' AND password='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("username"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("loggedIn.aspx", false);
}
else
{
LoginError.Text = "Invalid username password combination!";
}
reader.Close();
conn.Close();
}
}
/// <summary>
/// Artificial constructor called when the plugin is loaded
/// Uses the obsolete mysql_connection.ini if connect string is empty.
/// </summary>
/// <param name="connect">connect string</param>
public void Initialise(string connect)
{
if (connect != String.Empty)
{
database = new MySQLManager(connect);
}
else
{
m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead");
IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
settingPooling, settingPort);
}
// This actually does the roll forward assembly stuff
Assembly assem = GetType().Assembly;
using (MySql.Data.MySqlClient.MySqlConnection dbcon = new MySql.Data.MySqlClient.MySqlConnection(connect))
{
dbcon.Open();
Migration m = new Migration(dbcon, assem, "LogStore");
// TODO: After rev 6000, remove this. People should have
// been rolled onto the new migration code by then.
TestTables(m);
m.Update();
}
}
public static System.Net.IPEndPoint GetGPSGatewayRouter(string gpsCode)
{
MySql.Data.MySqlClient.MySqlConnection gpsDBCon = null;
try
{
gpsDBCon = new MySql.Data.MySqlClient.MySqlConnection();
gpsDBCon.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SelpConnectionStr"].ToString();
gpsDBCon.Open();
System.Net.IPEndPoint g = null;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "select gps_cachetable.value from gps_cachetable where gps_cachetable.key='gw" + gpsCode + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = gpsDBCon;
System.Data.DataSet ds = new DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
gpsDBCon.Close();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
string config = dr[0].ToString().Trim();
string[] ss = config.Split(":".ToCharArray());
g = new System.Net.IPEndPoint(System.Net.IPAddress.Parse(ss[0].Trim()), int.Parse(ss[1].Trim()));
}
return g;
}
catch (System.Exception ex)
{
PES.Beehive.Logging.Logger.Error("获取" + gpsCode + "网关配置信息失败", null);
throw ex;
}
finally
{
if (gpsDBCon != null)
gpsDBCon.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;
}
}
public void TopUpCustomer(string fromTenantCode, string toTenantCode, decimal money)
{
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
{
conn.Open();
var tran = conn.BeginTransaction();
string sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}';",
money, fromTenantCode);
sql += string.Format(@" update movo_tenant_account set Balance = Balance + {0} where TenantCode = '{1}';",
money, toTenantCode);
MySqlHelper.ExecuteNonQuery(conn, sql);
EMTenantAccountDetail fromTenantDetail = new EMTenantAccountDetail()
{
ConsumeMoney = money,
ConsumeType = EnumConsumeType.RechargeToCustomer,
OperateType = EnumOperateType.Outlay,
CreateTime = DateTime.Now,
TargetTenantCode = toTenantCode,
TenantCode = fromTenantCode,
OperatorTenantCode = fromTenantCode,
};
IEMTenantAccountDetailService serv = new EMTenantAccountDetailService();
serv.Insert(conn,fromTenantDetail);
EMTenantAccountDetail toTenantDetail = new EMTenantAccountDetail()
{
ConsumeMoney = money,
ConsumeType = EnumConsumeType.AccountIncome,
OperateType = EnumOperateType.Income,
CreateTime = DateTime.Now,
TenantCode = toTenantCode,
OperatorTenantCode = fromTenantCode,
};
serv.Insert(conn,toTenantDetail);
tran.Commit();
}
}
private void EditVendorPayments(VendorPaymentData _vendorPaymentToEdit)
{
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
msqlCommand.CommandText = "UPDATE vendor_payment SET payment_amount='" + _vendorPaymentToEdit.paymentAmount + "' WHERE payment_id='" + _vendorPaymentToEdit.paymentId + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
