public static void assignDonor(PatientInfo patientData)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE patient SET assigned_donor=@assigned_donor, assigned_donor_contact = @assigned_donor_contact WHERE patient_id=@patient_id";
msqlCommand.Parameters.AddWithValue("@patient_id", patientData.id);
msqlCommand.Parameters.AddWithValue("@assigned_donor", patientData.assignedDonor);
msqlCommand.Parameters.AddWithValue("@assigned_donor_contact", patientData.donorContact);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
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();
}
}
public static void assignEstimator(estimateInfo estimateToEdit)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE estimate SET estimator=@estimator WHERE appNo=@appNo";
msqlCommand.Parameters.AddWithValue("@estimator", estimateToEdit.estimator);
msqlCommand.Parameters.AddWithValue("@appNo", estimateToEdit.appsNo);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
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 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 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();
}
}
public static void ConnectInsertTocustomerPaymentTable(CustomerPaymentData customerDataObject)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
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;
FeedcustomerData(msqlCommand, customerDataObject);
}
catch (Exception er)
{
}
finally
{
msqlConnection.Close();
}
}
void StateChanged_Executed(object sender, ExecutedEventArgs e)
{
string messgae = "불러오기를 완료하였습니다.";
AMR_MST04Model model = new AMR_MST04Model();
model = (AMR_MST04Model)e.Parameter;
MySqlManage crud = new MySqlManage(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
DataSet ds = new DataSet();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "CALL sp_load(@sno, @datefrom, @dateto);";
cmd.Parameters.AddWithValue("@sno", model.MST04SNO);
cmd.Parameters.AddWithValue("@datefrom", model.From.ToString("yyyy-MM-dd") + " 00");
cmd.Parameters.AddWithValue("@dateto", model.To.ToString("yyyy-MM-dd") + " 00");
ds = crud.CallSPMariaDBTable(crud.Connection, cmd);
if (ds.Tables.Count > 0)
{
model.DataTable = ds.Tables[0];
CurrentForm.SearchComplete(model);
}
CurrentForm.ShowMessage(messgae);
}
public bool Register_AfterCheck(string Username, string Password, string Email, string FullName)
{
MySql.Data.MySqlClient.MySqlConnection con = null;
Database.Configuration.open(ref con);
string sql = @"
INSERT INTO `users`(`level`, `state`, `username`, `email`, `password`, `realname`)
VALUES (@level, @state, @username, @email, @password, @realname)";
var defaultLevel = Util.Config.Get().GetInt32("registration_default_authlevel", 2);
var defaultState = Util.Config.Get().GetInt32("registration_default_state", 0);
using (var rdr = new MySql.Data.MySqlClient.MySqlCommand(sql, con)) {
rdr.Parameters.AddWithValue("@level", defaultLevel);
rdr.Parameters.AddWithValue("@state", defaultState);
rdr.Parameters.AddWithValue("@username", Username);
rdr.Parameters.AddWithValue("@realname", FullName);
rdr.Parameters.AddWithValue("@email", Email);
rdr.Parameters.AddWithValue("@password", Password);
try {
rdr.ExecuteNonQuery(); // Let it throw a error so i can see if it works
} catch(Exception ex) {
throw;
return false;
}
} con.Close();
return true;
}
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 void ConnectInsertToDoListTable(ToDoData tdData)
{
//define the connection reference and initialize it
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO to_do(date_time,to_do,id)" + "VALUES(@date_time,@to_do,@id)";
msqlCommand.Parameters.AddWithValue("@date_time", tdData.date_time);
msqlCommand.Parameters.AddWithValue("@to_do", tdData.to_do);
msqlCommand.Parameters.AddWithValue("@id", tdData.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
/// <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 bool AddAgent(string name, string surname, string phone, string email, string password)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand();
return (dbManager.NonReturnQuery("INSERT INTO Agent (Agent_Name , Agent_Surname, Agent_Phone, Agent_Email, Agent_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + Cryptography.CreateHash(password).ToString() + "');"));
}
private void buttonOK_Click(object sender, EventArgs e)
{
var conn = this.dataset.CreateSQLConnection();
conn.Open();
var query = new MySql.Data.MySqlClient.MySqlCommand(String.Format("call settlement('{0}')",dateTimePickerSettleDate.Value.ToString("yyyy-MM-dd")), conn);
query.ExecuteNonQuery();
this.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 override void SendClientMessage(int Type, string MessageValue)
{
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand();
command.CommandText = "INSERT INTO messages (SessionToken, Type, MessageValue, DateCreated) VALUES (@SessionToken, @Type, @MessageValue, @DateCreated);";
command.Parameters.AddWithValue("@SessionToken", Program.sessionToken);
command.Parameters.AddWithValue("@Type", Type);
command.Parameters.AddWithValue("@MessageValue", MessageValue);
command.Parameters.AddWithValue("@DateCreated", CommonCommunicator.UnixTime());
Database.Command(command);
command.Dispose();
}
public static DataTable GetRemoteDataTable(string command, MySql.Data.MySqlClient.MySqlConnection msqConn)
{
DataTable rdt = new DataTable();
try
{
msqConn.Open();
MySql.Data.MySqlClient.MySqlCommand uploaderCmd = new MySql.Data.MySqlClient.MySqlCommand(command, msqConn);
MySql.Data.MySqlClient.MySqlDataReader onj = uploaderCmd.ExecuteReader();
bool colWasPerf = false;
int colIdx = 0;
List<object> rowValues = new List<object>();
foreach (System.Data.Common.DbDataRecord ro in onj)
{
try
{
// get fields
if (rdt.Columns.Count == 0 && !colWasPerf)
{
for (colIdx = 0; colIdx < ro.FieldCount; colIdx++)
rdt.Columns.Add(ro.GetName(colIdx));
colWasPerf = true;
}
}
catch { }
try
{
for (colIdx = 0; colIdx < ro.FieldCount; colIdx++)
rowValues.Add(ro[colIdx]);
}
catch { }
try
{
rdt.Rows.Add(rowValues.ToArray());
rowValues.Clear();
}
catch { }
}
onj.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
if (msqConn.State == System.Data.ConnectionState.Open)
msqConn.Close();
return rdt;
}
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 override Bitmap LoadImage(int image_id)
{
MySql.Data.MySqlClient.MySqlDataReader myData;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
string SQL;
byte[] rawData;
MemoryStream ms;
UInt32 FileSize;
Bitmap outImage;
SQL = "SELECT image_name, image_size, image_data FROM images WHERE id =";
SQL += image_id.ToString();
try
{
cmd.Connection = Connection;
cmd.CommandText = SQL;
myData = cmd.ExecuteReader();
if (!myData.HasRows)
throw new Exception("There are no blobs to save");
myData.Read();
FileSize = myData.GetUInt32(myData.GetOrdinal("image_size"));
rawData = new byte[FileSize];
myData.GetBytes(myData.GetOrdinal("image_data"), 0, rawData, 0, (Int32)FileSize);
ms = new MemoryStream(rawData);
outImage = new Bitmap(ms);
ms.Close();
ms.Dispose();
myData.Close();
myData.Dispose();
cmd.Dispose();
return outImage;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
return null;
}
}
/// <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);
}
}
}
}
}
}
}
public DataSet DataReport()
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_RapportView", conn);
cmd.CommandType = CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}catch(Exception ex) {
error = ex.Message;
}
return null;
}
public int DoRegisterNewContactusindb(string path)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
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;
//define the command text
msqlcommand.CommandText = "insert into photos(size,img, description)" + "values( @size,@img, @description)";
//add values provided by user
byte[] imgbytes = File.ReadAllBytes(path);
msqlcommand.Parameters.AddWithValue("@size", imgbytes.Length);
msqlcommand.Parameters.AddWithValue("@img", imgbytes);
msqlcommand.Parameters.AddWithValue("@description", "This file is taken from " + path);
msqlcommand.ExecuteNonQuery();
//close the connection
msqlConnection.Close();
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
/*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;
}
/// <summary>
/// Avec les informations sur le véhicule et sur le client, on utilise la procédure stockée SP_Nouvelle_Location pour créer un nouveau
/// contrat.
/// </summary>
/// <returns><c>true</c>, if contrat de location was faired, <c>false</c> otherwise.</returns>
/// <param name="niv">Niv.</param>
/// <param name="iDClient">I D client.</param>
/// <param name="kiloDebut">Kilo debut.</param>
/// <param name="kiloPermis">Kilo permis.</param>
/// <param name="surprimeKmEx">Surprime km ex.</param>
/// <param name="montantMensuel">Montant mensuel.</param>
/// <param name="nbrPaiement">Nbr paiement.</param>
/// <param name="termeLocation">Terme location.</param>
public bool FaireContratDeLocation(string niv, string iDClient, string kiloDebut, string kiloPermis, string surprimeKmEx, string montantMensuel, string nbrPaiement, string termeLocation)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_Nouvelle_Location", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("NIV_Vehicule", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = niv;
cmd.Parameters.Add("ID_Client", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = iDClient;
cmd.Parameters.Add("Kilometrage_Debut_Contrat", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = kiloDebut;
cmd.Parameters.Add("Kilometrage_Permis", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = kiloPermis;
cmd.Parameters.Add("Surprime_Km_excedentaire", MySql.Data.MySqlClient.MySqlDbType.Double).Value = surprimeKmEx;
cmd.Parameters.Add("Montant_Mensuel", MySql.Data.MySqlClient.MySqlDbType.Double).Value = montantMensuel;
cmd.Parameters.Add("Nombre_Paiements", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = nbrPaiement;
cmd.Parameters.Add("Terme_Location", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = termeLocation;
if(termeLocation == "48")
{
cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 4;
}
else if(termeLocation == "32")
{
cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 3;
}
else if (termeLocation == "24")
{
cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 2;
}
else if (termeLocation == "12")
{
cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 1;
}
conn.Open();
cmd.ExecuteNonQuery();
return true;
}catch(Exception ex) {
error = ex.Message;
}
finally{
conn.Close ();
}
return false;
}
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 UpdateResets(int TestID, string MBSN, string OPSN, string ENGINESN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format(
"UPDATE resets SET ", TestID);
if (MBSN != null)
{
_sql += String.Format("mbsn='{0}',", MBSN);
}
if (OPSN != null)
{
_sql += String.Format("opsn='{0}',", OPSN);
}
if (ENGINESN != null)
{
_sql += String.Format("enginesn='{0}',", ENGINESN);
}
_sql += String.Format(
" date='{0}'" +
" WHERE testid='{1}'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), TestID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public Person GetPerson(long ID)
{
MySql.Data.MySqlClient.MySqlConnection conn;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = connection.myConnectionString;
conn.Open();
Person p = new Person();
MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null;
String sqlString = "SELECT * FROM tblpersonnel WHERE ID = " + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlDataReader = cmd.ExecuteReader();
if (mySqlDataReader.Read())
{
p.ID = mySqlDataReader.GetInt32(0);
p.FirstName = mySqlDataReader.GetString(1);
p.LastName = mySqlDataReader.GetString(2);
p.PayRate = mySqlDataReader.GetDouble(3);
p.StartDate = mySqlDataReader.GetDateTime(4);
p.EndDate = mySqlDataReader.GetDateTime(5);
p.Address = mySqlDataReader.GetString(6);
p.State = mySqlDataReader.GetString(7);
p.ZipCode = mySqlDataReader.GetInt64(8);
p.PhoneNumber = mySqlDataReader.GetDouble(9);
return(p);
}
else
{
return(null);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static int DoRegisterNewContact(ContactInfo contactDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
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;
msqlCommand.CommandText = "INSERT INTO contact(contactId,name,mobile,homePhone,officePhone,email,address,faxNumber,remark) "
+ "VALUES(@contactId,@name,@mobile,@homePhone,@officePhone,@email,@address,@faxNumber,@remark)";
msqlCommand.Parameters.AddWithValue("@contactId", contactDetails.id);
msqlCommand.Parameters.AddWithValue("@name", contactDetails.name);
msqlCommand.Parameters.AddWithValue("@mobile", contactDetails.mobileno);
msqlCommand.Parameters.AddWithValue("@homePhone", contactDetails.homeno);
msqlCommand.Parameters.AddWithValue("@officePhone", contactDetails.oficeno);
msqlCommand.Parameters.AddWithValue("@email", contactDetails.email);
msqlCommand.Parameters.AddWithValue("@address", contactDetails.address);
msqlCommand.Parameters.AddWithValue("@faxNumber", contactDetails.faxno);
msqlCommand.Parameters.AddWithValue("@remark", contactDetails.remark);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public List <Component> GetComponents()
{
List <Component> _list = new List <Component>();
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT * FROM components;";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
int _stock;
Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock);
int _yield;
Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield);
_list.Add(new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString()));
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_list);
}
public void metodo()
{
articoli.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 articolo";
cmd.Connection = conn;
adapter.SelectCommand = cmd;
adapter.Fill(articoli);
cmbl = new MySql.Data.MySqlClient.MySqlCommandBuilder(adapter);
dataGridView1.DataSource = articoli;
cmd = new MySql.Data.MySqlClient.MySqlCommand();
string nomecli = textboxcliente.Text;
string piva = getPartitaIvaDiCliente(nomecli);
cmd.CommandText = "Select * from prezzo_articolo_cliente where nomecliente = @nomecli";
cmd.Parameters.AddWithValue("@nomecli", piva);
cmd.Connection = conn;
adapter.SelectCommand = cmd;
adapter.Fill(prezzocliente);
conn.Close();
foreach (DataRow row in prezzocliente.Rows)
{
foreach (DataRow art in articoli.Rows)
{
if (art["codicearticolo"].Equals(row["codicearticolo"]))
{
art["prezzolistino"] = (double)row["prezzocliente"] + 999999;
}
}
}
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
public int GetPrinterIDFromTestBySN(string SN)
{
int _id = 0;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT printersid FROM test WHERE sn='" + SN + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
if (_dataReader.GetValue(0).Equals(null))
{
_id = 0;
}
else
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
}
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_id);
}
public Component GetComponentByPN(string PN)
{
Component _component = null;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = String.Format("SELECT * FROM components WHERE pn='{0}';", PN);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
int _stock;
Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock);
int _yield;
Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield);
_component = new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString());
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_component);
}
public void InsertPrinterDismantleTime(int UserID, int TestID, int PrintersID, DateTime StartDateTime, DateTime EndDateTime, TimeSpan Time)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
//DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
string _sql = String.Format("INSERT INTO dismantledtimes (userid, testid, printersid, start, end, time) VALUES " +
"(" +
//userid
"'{0}', " +
//testid
"'{1}', " +
//printersid
"'{2}', " +
//start
"'{3}', " +
//end
"'{4}', " +
//time
"'{5}'" +
");",
UserID, TestID, PrintersID, StartDateTime.ToString("yyyy-MM-dd HH:mm:ss"), EndDateTime.ToString("yyyy-MM-dd HH:mm:ss"), Time.TotalMinutes);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
// return the creation of an incidence and the data of it
public Incidencia_Data ListarIncidencias(MySql.Data.MySqlClient.MySqlConnection conn)
{
ArrayList Lista_Incidente = new ArrayList();
string Query = "SELECT * FROM Report;";
string SecondQuery = "Select COUNT(*) FROM mydb.Incidencia;";
var Cmd = new MySql.Data.MySqlClient.MySqlCommand(SecondQuery, conn);//command variable
var Reader = Cmd.ExecuteReader();
Reader.Read();
//get quantity of items
int Incidencia_Quantity = Convert.ToInt32(Reader["COUNT(*)"].ToString());
Reader.Close();
Cmd = new MySql.Data.MySqlClient.MySqlCommand(Query, conn);
Reader = Cmd.ExecuteReader();
for (int i = 0; i < Incidencia_Quantity; i++)
{
Reader.Read();
Lista_Incidente.Add(Reader["Id_Incidencia"].ToString());
Lista_Incidente.Add(Reader["Asunto"].ToString());
Lista_Incidente.Add(Reader["Fecha_Registro"].ToString());
Lista_Incidente.Add(GetUser_byID(Reader["Id_Usuario"].ToString()));
Lista_Incidente.Add(Reader["Tipo_Solicitud"].ToString());
Lista_Incidente.Add(Reader["Categoria_Incidente"].ToString());
Lista_Incidente.Add(Reader["Prioridad"].ToString());
Lista_Incidente.Add(Reader["Estado"].ToString());
Lista_Incidente.Add(Reader["Tecnico"].ToString());
}
Incidencia_Data Lista = new Incidencia_Data
{
Activos = Lista_Incidente
};
Reader.Close();
return(Lista);
}
public void UpsertResets(int TestID, string MBSN, string OPSN, string ENGINESN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
_conn.Open();
string _sql = String.Format("SELECT resetsid FROM resets WHERE testid='{0}';", TestID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
int _value = 0;
while (_dataReader.Read())
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _value);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
if (_value == 0)
{
InsertResets(TestID, MBSN, OPSN, ENGINESN);
}
else
{
UpdateResets(TestID, MBSN, OPSN, ENGINESN);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public void Import(string s)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction();
aCommand.Transaction = trc;
try
{
string[] p = s.Split(',');
aCommand.CommandText = Part.GetByCodeSQLStatic(p[0]);
MySql.Data.MySqlClient.MySqlDataReader re = aCommand.ExecuteReader();
Part fpart = Part.GetPart(re);
re.Close();
if (fpart != null)
{
trc.Rollback(); return;
}
Unit u = GetUnit(aCommand, p[3], p[4]);
PartGroup pg = GetPartGroup(aCommand, p[5], p[6]);
Currency ccy = GetCurrency(aCommand, p[7], p[8]);
PartCategory pc = GetPartCategory(aCommand, p[9], "KECIL");
Part part = new Part();
part.CODE = p[0];
part.NAME = p[1];
part.BARCODE = p[2];
part.UNIT = u;
part.PART_GROUP = pg;
part.CURRENCY = ccy;
part.PART_CATEGORY = pc;
aCommand.CommandText = part.GetInsertSQL();
aCommand.ExecuteNonQuery();
trc.Commit();
}
catch (Exception x)
{
trc.Rollback();
throw x;
}
}
protected void Submit(object sender, EventArgs e)
{
String connStr = System.Configuration.ConfigurationManager.ConnectionStrings["WebConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
conn.Open();
queryStr = "SELECT * FROM tasarimdersi.yazarkullanici WHERE KullaniciAdi='" + GirisUsername.Text + "' AND Sifre='" + GirisPassword.Text + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("ID"));
kAdi = reader.GetString(reader.GetOrdinal("KullaniciAdi"));
isim = reader.GetString(reader.GetOrdinal("Isim"));
soyisim = reader.GetString(reader.GetOrdinal("Soyisim"));
email = reader.GetString(reader.GetOrdinal("Email"));
}
if (reader.HasRows)
{
Session["IDSes"] = name;
Session["kAdiSes"] = kAdi;
Session["IsimSes"] = isim;
Session["SoyisimSes"] = soyisim;
Session["EmailSes"] = email;
Response.BufferOutput = true;
Response.Redirect("MainYazar.aspx", false);
}
else
{
uyari.InnerText = "Giriş Başarısız";
}
reader.Close();
conn.Close();
}
public ArrayList ObtenerPlatos(int idioma)
{
ArrayList arrayPlatos = new ArrayList();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader;
String sqlString = "CALL Select_all_plato(" + idioma.ToString() + ");";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conexion);
try
{
mySQLReader = cmd.ExecuteReader();
while (mySQLReader.Read())
{
Plato p = new Plato();
p.id = mySQLReader.GetInt32(0);
p.nombre = mySQLReader.GetString(1);
p.descripcion = "";
p.imagen = mySQLReader.GetString(2);
p.precio = mySQLReader.GetDouble(3);
p.nombre_familia = mySQLReader.GetString(5);
p.id_familia = mySQLReader.GetInt32(6);
arrayPlatos.Add(p);
}
}
catch (Exception)
{
conexion.Close();
Console.WriteLine("Error al hacer la consulta");
}
finally
{
conexion.Close();
}
return(arrayPlatos);
}
public static SettingsData FetcheSettingsData()
{
SettingsData settingsDataObject = null;
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 * from sptinfo;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
msqlReader.Read();
settingsDataObject = new SettingsData();
settingsDataObject.Address = msqlReader.GetString("address");
settingsDataObject.Name = msqlReader.GetString("name");
settingsDataObject.Phone = msqlReader.GetString("phone");
settingsDataObject.Password = msqlReader.GetString("password");
settingsDataObject.BillDisclaimer = msqlReader.GetString("bill_disclaimer");
settingsDataObject.InvoicePrefix = msqlReader.GetString("invoice_prefix");
//settingsDataObject.sptinfo = msqlReader.GetString("id_sptinfo");
}
catch (Exception er)
{
//Assert//.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
return settingsDataObject;
}
//GET METHOD
public AccessPoint GetAccessPointsByName(string name)
{
MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null;
AccessPoint res = new AccessPoint();
string sqlString = "SELECT * FROM accesspoints WHERE Position ='" + name + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connector.conn);
mySqlDataReader = cmd.ExecuteReader();
if (mySqlDataReader.Read())
{
res.Position = mySqlDataReader.GetString(0);
res.Bssid = mySqlDataReader.GetString(1);
res.EquipType = mySqlDataReader.GetString(2);
}
return(res);
}
public void prendiarticoli()
{
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connessione);
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "Select * from articolo";
cmd.Connection = conn;
articoliadapter.SelectCommand = cmd;
articoliadapter.Fill(articoli);
articolibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(articoliadapter);
datagridarticoli.DataSource = articoli;
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
public static void Connected2()
{
MyCon _con = new MyCon();
MySql.Data.MySqlClient.MySqlDataReader rdr = null;
_con.ConnectDB();
using (MySql.Data.MySqlClient.MySqlConnection connect = _con.db.connect()){
connect.Open();
string sql = String.Format("{0}", "SELECT * FROM Customer");
MySql.Data.MySqlClient.MySqlCommand cmd = connect.CreateCommand();
cmd.CommandText = sql;
cmd.CommandTimeout = 600;
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
Console.WriteLine(rdr);
}
connect.Close();
}
}
private void BuscarVentaFechas(DateTime fechaIni, DateTime fechaFin)
{
try
{
MySql.Data.MySqlClient.MySqlCommand sql = new MySql.Data.MySqlClient.MySqlCommand();
sql.CommandText = "SELECT id, fecha, total, estado, abierta FROM venta WHERE (fecha BETWEEN ? AND ?) AND estado=1";
//Recuerda que en cualquier gestor de base de datos, las fechas van en orden yyyy-MM-dd,
//y así te quitas muchos pedos Chava u.u
sql.Parameters.AddWithValue("@fechaIni", fechaIni.ToString("yyyy-MM-dd") + " 00:00:00");
sql.Parameters.AddWithValue("@fechaFin", fechaFin.ToString("yyyy-MM-dd") + " 23:59:59");
dt = Clases.ConexionBD.EjecutarConsultaSelect(sql);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Clases.FuncionesGenerales.MensajeError("Ocurrio un error búscando las ventas. No se pudo conectar a la base de datos.", ex);
}
catch (Exception ex)
{
Clases.FuncionesGenerales.MensajeError("Ocurrio un error búscando las ventas. Ha ocurrido un error genérico.", ex);
}
}
public debtPaid(int summ)
{
InitializeComponent();
paidSumm = summ;
tbxSum.Text = summ.ToString();
//Вывод в ComboBox имен агентов
string cmd = "SELECT * FROM sklad.agents";
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(tposDesktop.Properties.Settings.Default.testConnectionString))
{
MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand(cmd, conn);
DataTable dt = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(com);
da.Fill(dt);
cmbNameAg.DataSource = dt;
cmbNameAg.DisplayMember = "Name";
cmbNameAg.ValueMember = "ID";
//cmbNameAg.SelectedIndex = -1;
}
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
MySql.Data.MySqlClient.MySqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (MySql.Data.MySqlClient.MySqlCommand) CommandsCollection[storedProcedure];
}
else
{
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new MySql.Data.MySqlClient.MySqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (MySql.Data.MySqlClient.MySqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
public static void InserirPanico(long cpf, float lati, float longi, int senha)
{
using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(GetStringDb()))
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO ocorrencias (cpf_cliente, datahora, latitude, longitude, senha_utilizada) VALUES (@cpf_cliente, @datahora, @latitude, @longitude, @senha)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@cpf_cliente", cpf);
cmd.Parameters.AddWithValue("@datahora", DateTime.Now);
cmd.Parameters.AddWithValue("@latitude", lati);
cmd.Parameters.AddWithValue("@longitude", longi);
cmd.Parameters.AddWithValue("@senha", senha);
con.Open();
cmd.ExecuteNonQuery();
con.Clone();
}
}
}
private static void PrepareCommand(MySql.Data.MySqlClient.MySqlCommand cmd, MySql.Data.MySqlClient.MySqlConnection conn, MySql.Data.MySqlClient.MySqlTransaction trans, string cmdText, MySql.Data.MySqlClient.MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (MySql.Data.MySqlClient.MySqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
public void DeleteNoTransaction(Event e, MySql.Data.MySqlClient.MySqlCommand command)
{
OpeningStock st = (OpeningStock)e;//this.Get(e.ID);
m_command = command;
try
{
if (getEventStatus(st.ID) == EventStatus.Confirm)
{
throw new Exception("Revise before delete");
}
m_command.CommandText = OpeningStockItem.DeleteAllByEventSQL(st.ID);
m_command.ExecuteNonQuery();
m_command.CommandText = OpeningStock.DeleteSQL(st.ID);
m_command.ExecuteNonQuery();
}
catch (Exception x)
{
throw x;
}
}
public void ConfirmNoTransaction(int id, MySql.Data.MySqlClient.MySqlCommand command)
{
m_command = command;
try
{
Event events = this.Get(id);
if (events.POSTED) //.EVENT_STATUS == EventStatus.Confirm)
{
throw new Exception("Status is already Posted/Confirm");
}
Period p = AssertValidPeriod(events.TRANSACTION_DATE);
doConfirm(events, p);
events.ProcessConfirm();
this.UpdateStatus(events, true);
updateStockCards(events.EVENT_ITEMS);
}
catch (Exception x)
{
throw x;
}
}
public void Delete(Character ch)
{
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand(
"DELETE FROM CharactersSpecialty WHERE @CharacterID = CharacterID"
, con
);
cmd.Parameters.AddWithValue("@CharacterID", ch.ID);
con.Open();
cmd.ExecuteNonQuery();
cmd = new MySql.Data.MySqlClient.MySqlCommand(
"DELETE FROM Characters WHERE @CharacterID = CharacterID"
, con
);
cmd.Parameters.AddWithValue("@CharacterID", ch.ID);
cmd.ExecuteNonQuery();
con.Close();
}
}
private void employee()
{
conn = new MySql.Data.MySqlClient.MySqlConnection(constr);
conn.Open();
data = "select EmployeeID,Name from hr.hire_emp";
cmd = new MySql.Data.MySqlClient.MySqlCommand(data, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem item = new ListItem();
item.Text = reader["Name"].ToString();
item.Value = reader["EmployeeID"].ToString();
DropDownList1.Items.Add(item);
DropDownList2.Items.Add(item);
}
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
String co = @"Server=127.0.0.1; DATABASE=tahuntsic; UID=root ";
try {
MySql.Data.MySqlClient.MySqlConnection c = new MySql.Data.MySqlClient.MySqlConnection(co);
c.Open();
MySql.Data.MySqlClient.MySqlCommand comm = c.CreateCommand();
comm.CommandText = "SELECT * FROM users";
MySql.Data.MySqlClient.MySqlDataReader r = comm.ExecuteReader();
while (r.Read())
{
Console.WriteLine(r.GetString(0) + "\t" + r.GetString(1) + "\t" + r.GetString(3));
}
} catch (MySql.Data.MySqlClient.MySqlException ex) {
MessageBox.Show(ex.Message);
}
}
private string getUserName(string userName)
{
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sql = "Select salt from quiz_taker_info where email = '" + userName.ToString() + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
mySqlReader = cmd.ExecuteReader();
string salt;
if (mySqlReader.Read())
{
salt = mySqlReader.GetString(0);
}
else
{
salt = null;
}
mySqlReader.Close();
return(salt);
}
private PartCategory GetPartCategory(MySql.Data.MySqlClient.MySqlCommand aCommand, string code, string name)
{
//MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.CommandText = PartCategory.GetByCodeSQLStatic(code.Trim());
MySql.Data.MySqlClient.MySqlDataReader r = aCommand.ExecuteReader();
PartCategory u = PartCategory.GetPartCategory(r);
r.Close();
if (u == null)
{
u = new PartCategory();
u.CODE = code;
u.NAME = name;
aCommand.CommandText = u.GetInsertSQL();
aCommand.ExecuteNonQuery();
aCommand.CommandText = u.GetMaximumIDSQL();
u.ID = Convert.ToInt32(aCommand.ExecuteScalar());
}
return(u);
}
private static void load_db()
{
Core.Log.WritePlain("MySQL", "Connecting to database... (" + Globals.GetInstance().Config.GetValue("SQL_HOSTNAME") + ":3306)");
Globals.GetInstance().GameDatabase = new Core.DB.Instance();
Globals.GetInstance().GameDatabase.SetCredentials(Globals.GetInstance().Config.GetValue("SQL_HOSTNAME"), Globals.GetInstance().Config.GetValue("SQL_USERNAME"), Globals.GetInstance().Config.GetValue("SQL_PASSWORD"), Globals.GetInstance().Config.GetValue("SQL_DATABASE"));
if (!Globals.GetInstance().GameDatabase.TestConnection())
{
Core.Log.WriteError("Couldn't connect to database!");
Console.ReadKey();
Environment.Exit(0);
}
Core.Log.WritePlain("MySQL", "Done!");
Console.WriteLine();
MySql.Data.MySqlClient.MySqlConnection mConnection = Globals.GetInstance().GameDatabase.CreateConnection();
MySql.Data.MySqlClient.MySqlCommand mCommand = new MySql.Data.MySqlClient.MySqlCommand("UPDATE accounts SET online=0 WHERE 1;", mConnection);
mCommand.ExecuteNonQuery();
mConnection.Close();
}
public long insertFile(string refnum, string fileName, Array byteArray, long fileSize, string fileLocation, string origFileName)
{
string sqlInsert = "INSERT INTO IMAGE_DETAILS(REF_ID, FILENAME, IMAGE, FILE_LOCATION, SIZE, ORIG_FILENAAME) " +
"VALUES('" + refnum + "', '" + fileName + "', @IMAGE, '" + fileLocation + "', '" + fileSize + "', '" + origFileName + "')";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlInsert, conn);
conn.Open();
try
{
cmd.Parameters.AddWithValue("@IMAGE", byteArray);
cmd.ExecuteNonQuery();
long id = cmd.LastInsertedId;
return(id);
}
catch (Exception ex)
{
new Exception("Unable to insertFile: ", ex);
}
return(0);
}
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="cmdText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(rdr);
}
catch
{
conn.Close();
throw;
}
}
// Return the update of an incident assigment
public void UpdateAssignment(MySql.Data.MySqlClient.MySqlConnection conn, String Code, String Personnel)
{
DateTime date = DateTime.Today;
String today = date.ToString("d", CultureInfo.CreateSpecificCulture("ja-JP"));
String DB_Change = "UPDATE `mydb`.`Incidencia` SET `Fecha_Atencion` = '" + today + "' WHERE `Id_Incidencia` = " + Code + ";";
var Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm
var executer = Insert.ExecuteNonQuery(); //execute non query
DB_Change = "UPDATE `mydb`.`Reportes` SET `Fecha_Asignacion` = '" + today + "' WHERE `Id_Reportes` = '" + Cedula_ByID(conn, Code) + "';";
Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm
executer = Insert.ExecuteNonQuery(); //execute non query
DB_Change = "UPDATE `mydb`.`Incidencia` SET `Tecnico` = '" + Personnel + "' WHERE `Id_Incidencia` = " + Code + ";";
Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm
executer = Insert.ExecuteNonQuery(); //execute non query
}
