public override void Update(IEntity en)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction trans = m_connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.Transaction = trans;
try
{
GeneralSetup e = (GeneralSetup)en;
aCommand.CommandText = e.GetUpdateSQL();
aCommand.ExecuteNonQuery();
foreach (string kys in e.AUTONUMBER_LIST.Keys)
{
aCommand.CommandText = e.AUTONUMBER_LIST[kys].GetUpdateSQL();
aCommand.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public override void Delete(IEntity e)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction transac = m_connection.BeginTransaction();
try
{
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.Transaction = transac;
User y = (User)e;
foreach (string kys in y.FORM_ACCESS_LIST.Keys)
{
aCommand.CommandText = y.FORM_ACCESS_LIST[kys].GetDeleteSQL();
aCommand.ExecuteNonQuery();
}
aCommand.CommandText = y.GetDeleteSQL();
aCommand.ExecuteNonQuery();
transac.Commit();
}
catch (Exception x)
{
transac.Rollback();
m_connection.Close();
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
private void buttonSendTest_Click(object sender, EventArgs e)
{
string Connect = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=cp1251";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
if (CheckJPEG == true)
{
checkFolderFtp(url);
// читаем файл в строку
//string fileText = System.IO.File.ReadAllText(filename);
WebClient myWebClient = new WebClient();
Uri ftp_path = new Uri("ftp://*****:*****@31.31.196.162" + url + fileName); // file.txt - файл, который будет в конечном итоге залит; FTPLOGIN - логин к FTP; PASSWORD - пароль к FTP; LOGIN и PASSWORD разделяются двоеточием.
myWebClient.UploadFile(ftp_path, urlName); // anyfile.txt - загружаемый файл на FTP; C:/Files... - путь к загружаемому файлу; ftp_path - конечный путь и имя файла, которое будет на FTP сервере.
string urlFileJPEG = "http://www.imtis.ru/task/jpeg/" + Main.LoginGlobal + "/" + fileName;
myCommand.CommandText = string.Format("INSERT INTO task (TextTask,PicTask,idtest) VALUES('{0}','{1}','{2}')", textBoxVopros.Text, urlFileJPEG, Teacher.idtests);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
CheckJPEG = false;
}
else
{
myCommand.CommandText = string.Format("INSERT INTO task (TextTask,idtest) VALUES('{0}','{1}')", textBoxVopros.Text, Teacher.idtests);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
}
textBoxVopros.Text = "";
myConnection.Close();
}
public static void UpdateAgainstStatus(MySql.Data.MySqlClient.MySqlCommand cmd, GoodReceiveNote grn, GoodReceiveNoteItem grni)
{
cmd.CommandText = grni.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
cmd.CommandText = grn.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
}
private void buttonTest_Click(object sender, EventArgs e)
{
NameTest = textBoxNameTest.Text;
string Connect = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=cp1251";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = string.Format("SELECT id FROM teacher WHERE login='******'", Main.LoginGlobal);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
int idtecher = (int)myCommand.ExecuteScalar(); //результат запроса
myCommand.CommandText = string.Format("INSERT INTO tests (idteacher,NameTest) " + "VALUES('{0}','{1}')", idtecher, NameTest);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
idtests = (int)myCommand.LastInsertedId; //получаем id
if (CheckPDF == true)
{
checkFolderFtp(url);
WebClient myWebClient = new WebClient();
Uri ftp_path = new Uri("ftp://*****:*****@31.31.196.162" + url + fileName); // file.txt - файл, который будет в конечном итоге залит; FTPLOGIN - логин к FTP; PASSWORD - пароль к FTP; LOGIN и PASSWORD разделяются двоеточием.
myWebClient.UploadFile(ftp_path, urlName); // anyfile.txt - загружаемый файл на FTP; C:/Files... - путь к загружаемому файлу; ftp_path - конечный путь и имя файла, которое будет на FTP сервере.
string urlFile = "http://www.imtis.ru/pdf/" + Main.LoginGlobal + "/" + fileName;
myCommand.CommandText = string.Format("UPDATE tests SET pdf = '{0}' WHERE id = '{1}'", urlFile, idtests);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
}
myConnection.Close();
CreatTest test = new CreatTest();
test.Show();
this.Hide();
}
public override void Delete(IEntity e)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction transac = m_connection.BeginTransaction();
try
{
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.Transaction = transac;
Year y = (Year)e;
foreach (Period p in y.PERIODS)
{
aCommand.CommandText = p.GetDeleteSQL();
aCommand.ExecuteNonQuery();
}
aCommand.CommandText = y.GetDeleteSQL();
aCommand.ExecuteNonQuery();
transac.Commit();
}
catch (Exception x)
{
transac.Rollback();
m_connection.Close();
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public static bool setDataBasePath(string dbPath)
{
MySql.Data.MySqlClient.MySqlConnection dbConnection = null;
try
{
databasePath = dbPath;
//connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasePath + ";User Id=admin;Password="******"Server=tx-gu;Database=molic_woz_db;Uid=root;Pwd=kll312;Port=3306;";
dbConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
//'''''' Testa banco de dados '''''''
MySql.Data.MySqlClient.MySqlCommand dbCommand = new MySql.Data.MySqlClient.MySqlCommand();
dbCommand.Connection = dbConnection;
//------------------------------
dbConnection.Open();
dbCommand.CommandText = "SELECT * FROM TB_SIMULATIONS";
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "SELECT * FROM TB_DIALOGS";
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "SELECT * FROM TB_SIGNS";
dbCommand.ExecuteNonQuery();
dbConnection.Close();
//'''''''''''''''''''''''''''''''''''
return(true);
}
catch (Exception ex)
{
dbConnection.Close();
System.Threading.Thread.Sleep(20000);
return(false); //Arquivo de dados inválido
}
}
public static void UpdateAgainstStatus(MySql.Data.MySqlClient.MySqlCommand cmd, DeliveryOrder dor, DeliveryOrderItem doi)
{
cmd.CommandText = doi.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
cmd.CommandText = dor.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
}
public void UpdateLikeMusic(List <String> likemusiclis, String username)
{
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection(constr);
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = this.msqlConnection;
//define the command text
msqlCommand.CommandText = "DELETE FROM user_like_music_list WHERE user_name='" + username + "';";
Console.WriteLine(msqlCommand.CommandText);
try
{
//open the connection
this.msqlConnection.Open();
//use a DataReader to process each record
msqlCommand.ExecuteNonQuery();
for (int j = 0; j < likemusiclis.Count; j++)
{
msqlCommand.CommandText = "INSERT INTO user_like_music_list(user_name,music_path) VALUES('" + username + "','" + getPath(likemusiclis[j]) + "');";
Console.WriteLine(msqlCommand.CommandText);
msqlCommand.ExecuteNonQuery();
}
}
catch (Exception er)
{
Console.WriteLine(er.Message);
//do something with the exception
}
finally
{
//always close the connection
this.msqlConnection.Close();
}
}
public override void Save(IEntity en)
{
Year e = (Year)en;
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction transac = m_connection.BeginTransaction();
try
{
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(e.GetInsertSQL(), m_connection);
aCommand.Transaction = transac;
aCommand.ExecuteNonQuery();
aCommand.CommandText = e.GetMaximumIDSQL();
e.ID = Convert.ToInt32(aCommand.ExecuteScalar());
e.GeneratePeriods();
foreach (Period p in e.PERIODS)
{
aCommand.CommandText = p.GetInsertSQL();
aCommand.ExecuteNonQuery();
}
transac.Commit();
}
catch (Exception x)
{
transac.Rollback();
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
/// <summary>
/// Execute query in the databaxse
/// </summary>
/// <param name="Query">Write either SQl Select statment or name of Stored Procedure </param>
/// <param name="CommandType">Specify command type as Text if you passed Text as Query or
/// StoredProcedure if you passed name of Stored Procedure as Query</param>
private bool Execute_Query_WithTransaction(System.String Query, System.Data.CommandType CmdType, MySql.Data.MySqlClient.MySqlTransaction MyTransaction, System.Boolean UseTransaction)
{
try
{
if (_MyConnection.State == System.Data.ConnectionState.Closed)
{
_MyConnection.Open();
_MyCommand.Connection = _MyConnection;
}
_MyCommand.CommandText = Query;
_MyCommand.CommandType = CmdType;
if (UseTransaction == true)
{
_MyCommand.Transaction = MyTransaction;
}
_MyCommand.CommandTimeout = 0;
_MyCommand.ExecuteNonQuery();
return(true);
}
catch (MySql.Data.MySqlClient.MySqlException Sqex)
{
throw new System.Exception(ErrorCodes.ProcessException(Sqex, "", "", "", ErrorCodes.MySqlExceptionMsg(Sqex)));
}
catch (System.Exception Ex)
{
throw new System.Exception(Ex.Message);
}
}
public override void Save(IEntity p)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction tr = m_connection.BeginTransaction();
User e = (User)p;
try
{
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(e.GetInsertSQL(), m_connection);
aCommand.Transaction = tr;
aCommand.ExecuteNonQuery();
aCommand.CommandText = e.GetMaximumIDSQL();
e.ID = Convert.ToInt32(aCommand.ExecuteScalar());
foreach (string kys in e.FORM_ACCESS_LIST.Keys)
{
aCommand.CommandText = e.FORM_ACCESS_LIST[kys].GetInsertSQL();
aCommand.ExecuteNonQuery();
aCommand.CommandText = e.FORM_ACCESS_LIST[kys].GetMaximumIDSQL();
e.FORM_ACCESS_LIST[kys].ID = Convert.ToInt32(aCommand.ExecuteScalar());
}
tr.Commit();
}
catch (Exception x)
{
tr.Rollback();
e.ID = 0;
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public static void UpdateAgainstStatus(MySql.Data.MySqlClient.MySqlCommand cmd, PurchaseOrder po, PurchaseOrderItem poi)
{
cmd.CommandText = poi.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
cmd.CommandText = po.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
}
public void SaveSettings(int userid, string name, Type type, string value)
{
if (m_connection.State == System.Data.ConnectionState.Closed)
{
m_connection.Open();
}
if (isExist(userid, name, type))
{
string update = String.Format(@"update table_usersettings set
us_value = '{0}'
where user_id = {1}
and us_name = '{2}' and us_type = '{3}' ",
value, userid, name, type.ToString());
m_cmd.CommandText = update;
m_cmd.ExecuteNonQuery();
}
else
{
string insert = String.Format(@"insert into table_usersettings
(user_id,us_name,us_value,us_type)
VALUES ('{0}','{1}','{2}','{3}')", userid, name, value, type.ToString());
m_cmd.CommandText = insert;
m_cmd.ExecuteNonQuery();
}
}
private bool anstempeln(string user, string auftrag)
{
open_db();
comm.CommandText = "INSERT INTO stamps (userid,task,art,jahr,monat,tag,stunde,minute,sekunde,dezimal,quelle) " +
"VALUES ('" + user + "','" + auftrag + "','an','" + jahr + "','" + monat + "','" +
tag + "','" + stunde + "','" + minute + "','" + sekunde + "','" + zeiteinheit + "','INSERT')";
try
{
comm.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
log(ex.Message);
}
comm.CommandText = "UPDATE user SET currenttask='" + auftrag + "' where userid = '" + user + "'";
try
{
comm.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
log(ex.Message);
}
close_db();
setstatus("gestempelt", user + " auf Auftrag " + auftrag + " eingestempelt.");
return(true);
}
public int addUserTransitionUtteranceOption(TTransitionUtterance tTransitionUtterance, List <TDialog> tDialogs)
{
//--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;
//-----------------------------
int transitionId = -1;
int queryResult = 0;
try
{
dbConnection.Open();
while (queryResult <= 0)
{
dbCommand.CommandText = "INSERT INTO TB_TRANSITION_UTTERANCES (simulation_id, history_id, xml_id, description, emitter, isBreakdown, source, target, legend, source_topic) VALUES(" + tTransitionUtterance.simulation_id.ToString() + ", -1, '" + tTransitionUtterance.xml_id + "', '" + tTransitionUtterance.description + "', '" + tTransitionUtterance.emitter + "'," + tTransitionUtterance.isBreakdown.ToString() + ", '" + tTransitionUtterance.source + "', '" + tTransitionUtterance.target + "', " + tTransitionUtterance.legend + ",'" + tTransitionUtterance.source_topic + "')";
queryResult = dbCommand.ExecuteNonQuery();
}
dbCommand.CommandText = "SELECT * FROM TB_TRANSITION_UTTERANCES WHERE (simulation_id = " + tTransitionUtterance.simulation_id.ToString() + ") AND (xml_id = '" + tTransitionUtterance.xml_id + "') AND (history_id = -1)";
dbDataReader = dbCommand.ExecuteReader();
dbDataReader.Read();
transitionId = Convert.ToInt32(dbDataReader["id"]);
dbDataReader.Close();
if (tDialogs != null)
{
for (int countDialogs = 0; countDialogs < tDialogs.Count; countDialogs++)
{
dbCommand.CommandText = "INSERT INTO TB_DIALOGS (transition_id, dialog) VALUES(" + transitionId + ", '" + tDialogs[countDialogs].dialog + "')";
dbCommand.ExecuteNonQuery();
if (tDialogs[countDialogs].signs != null)
{
dbDataReader.Close();
dbCommand.CommandText = "SELECT * FROM TB_DIALOGS WHERE (transition_id = " + transitionId + ") AND (dialog = '" + tDialogs[countDialogs].dialog + "')";
dbDataReader = dbCommand.ExecuteReader();
dbDataReader.Read();
int dialogId = Convert.ToInt32(dbDataReader["id"]);
dbDataReader.Close();
for (int countSigns = 0; countSigns < tDialogs[countDialogs].signs.Count; countSigns++)
{
dbCommand.CommandText = "INSERT INTO TB_SIGNS (dialog_id, sign, sign_value) VALUES(" + dialogId.ToString() + ", '" + tDialogs[countDialogs].signs[countSigns].sign + "', '" + tDialogs[countDialogs].signs[countSigns].sign_value + "')";
dbCommand.ExecuteNonQuery();
}
}
}
}
dbDataReader.Close();
dbConnection.Close();
}
catch (Exception e)
{
dbConnection.Close();
System.Threading.Thread.Sleep(20000);
}
return(queryResult);
}
public override void Update(IEntity en)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction trans = m_connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.Transaction = trans;
try
{
User e = (User)en;
aCommand.CommandText = e.GetUpdateSQL();
aCommand.ExecuteNonQuery();
foreach (string kys in e.FORM_ACCESS_LIST.Keys)
{
if (e.FORM_ACCESS_LIST[kys].ID > 0)
{
aCommand.CommandText = e.FORM_ACCESS_LIST[kys].GetUpdateSQL();
aCommand.ExecuteNonQuery();
}
else
{
aCommand.CommandText = e.FORM_ACCESS_LIST[kys].GetInsertSQL();
aCommand.ExecuteNonQuery();
aCommand.CommandText = e.FORM_ACCESS_LIST[kys].GetMaximumIDSQL();
e.FORM_ACCESS_LIST[kys].ID = Convert.ToInt32(aCommand.ExecuteScalar());
}
}
aCommand.CommandText = FormAccess.GetAllByUserSQL(e.ID);
MySql.Data.MySqlClient.MySqlDataReader r = aCommand.ExecuteReader();
IList luc = FormAccess.GetAllStatic(r);
r.Close();
foreach (FormAccess chk in luc)
{
chk.UPDATED = e.FORM_ACCESS_LIST.Contains(new KeyValuePair <string, FormAccess>(chk.CODE, chk));
}
foreach (FormAccess chk in luc)
{
if (!chk.UPDATED)
{
aCommand.CommandText = chk.GetDeleteSQL();
aCommand.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public static void UpdateAgainstStatus(MySql.Data.MySqlClient.MySqlCommand cmd, EventJournal e, ICustomerInvoiceJournalItem ei)
{
CustomerOutStandingInvoice po = (CustomerOutStandingInvoice)e;
CustomerOutStandingInvoiceItem poi = (CustomerOutStandingInvoiceItem)ei;
cmd.CommandText = poi.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
cmd.CommandText = po.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
}
public static void UpdateAgainstStatus(MySql.Data.MySqlClient.MySqlCommand cmd, EventJournal e, ISupplierInvoiceJournalItem ei)
{
SupplierInvoiceJournal po = (SupplierInvoiceJournal)e;
SupplierInvoiceJournalItem poi = (SupplierInvoiceJournalItem)ei;
cmd.CommandText = poi.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
cmd.CommandText = po.UpdateAgainstStatus();
cmd.ExecuteNonQuery();
}
public override void Save(IEntity en)
{
OpenConnection();
MySql.Data.MySqlClient.MySqlTransaction trans = m_connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand();
aCommand.Connection = m_connection;
aCommand.Transaction = trans;
Part e = (Part)en;
try
{
aCommand.CommandText = e.GetInsertSQL();
this.SavePicture(e.PICTURE, e.CODE);
aCommand.ExecuteNonQuery();
aCommand.CommandText = e.GetMaximumIDSQL();
e.ID = Convert.ToInt32(aCommand.ExecuteScalar());
//add Base Conversion--------------------
UnitConversion unit = new UnitConversion();
unit.BARCODE = e.BARCODE;
unit.CONVERSION_QTY = 1;
unit.CONVERSION_UNIT = e.UNIT;
unit.COST_PRICE = e.COST_PRICE;
unit.ORIGINAL_QTY = 1;
unit.PART = e;
unit.SELL_PRICE = e.SELL_PRICE;
e.UNIT_CONVERSION_LIST.Add(unit);
//---------------------------------------
foreach (UnitConversion uc in e.UNIT_CONVERSION_LIST)
{
aCommand.CommandText = uc.GetInsertSQL();
aCommand.ExecuteNonQuery();
aCommand.CommandText = uc.GetMaximumIDSQL();
uc.ID = Convert.ToInt32(aCommand.ExecuteScalar());
}
trans.Commit();
}
catch (Exception x)
{
trans.Rollback();
en.SetID(0);
foreach (UnitConversion uc in e.UNIT_CONVERSION_LIST)
{
uc.ID = 0;
}
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public static void Save(MySql.Data.MySqlClient.MySqlCommand cmd, VendorBalance sc)
{
cmd.CommandText = sc.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = VendorBalance.SelectMaxIDSQL();
sc.ID = Convert.ToInt32(cmd.ExecuteScalar());
foreach (VendorBalanceEntry sce in sc.VENDOR_BALANCE_ENTRIES)
{
cmd.CommandText = sce.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = VendorBalanceEntry.SelectMaxIDSQL();
sce.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static void Save(MySql.Data.MySqlClient.MySqlCommand cmd, StockCard sc)
{
cmd.CommandText = sc.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCard.SelectMaxIDSQL();
sc.ID = Convert.ToInt32(cmd.ExecuteScalar());
foreach (StockCardEntry sce in sc.STOCK_CARD_ENTRIES)
{
cmd.CommandText = sce.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCardEntry.SelectMaxIDSQL();
sce.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static void Save(MySql.Data.MySqlClient.MySqlCommand cmd, VendorBalanceEntry sce)
{
if (sce.ID == 0)
{
cmd.CommandText = sce.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = VendorBalanceEntry.SelectMaxIDSQL();
sce.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
cmd.CommandText = sce.GetUpdateSQL();
cmd.ExecuteNonQuery();
}
}
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();
}
}
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 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();
}
}
/// <summary>
/// Для выполнения запросов к MySQL без возвращения параметров.
/// </summary>
/// <param name="sql">Текст запроса к базе данных</param>
/// <param name="connection">Строка подключения к базе данных</param>
/// <returns>Возвращает True - ошибка или False - выполнено успешно.</returns>
public static MyResult SqlNoneQuery(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
{
commRC.ExecuteNonQuery();
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;
}
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();
}
}
/// <summary>
/// Create a new Person resource, returning ID
/// </summary>
/// <param name="personToSave"></param>
/// <returns></returns>
public long SavePerson(Person personToSave)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString
};
long id = 0;
try
{
conn.Open(); String sqlString = "INSERT INTO tblPersonnel (FirstName, LastName, PayRate, StartDate, EndDate) VALUES ('" + personToSave.FirstName + "','" + personToSave.LastName + "','" + personToSave.PayRate + "','" + personToSave.StartDate.ToString("yyyy-MM-dd HH:mm:ss") + "','" + personToSave.EndDate.ToString("yyyy-MM-dd HH:mm:ss") + "')";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
id = cmd.LastInsertedId;
return(id);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
return(id);
}
finally
{
conn.Close();
}
}
private void EditAddButton_Click(object sender, RoutedEventArgs e)
{
//Clear box after complete, change tab back to Auto
if (this.EditTextbox.Text != string.Empty)
{
string toCheck = cxHandle.getLastNumber();
toCheck = toCheck.Insert(6, "-");
toCheck = toCheck.Insert(3, ")");
toCheck = toCheck.Insert(0, "(");
// Try to add current name to SQL DB with most recent number
try
{
using (MySql.Data.MySqlClient.MySqlConnection sqlConnection = new MySql.Data.MySqlClient.MySqlConnection(Properties.Resources.SQLConnectionString))
{
MySql.Data.MySqlClient.MySqlCommand sqlCommand = new MySql.Data.MySqlClient.MySqlCommand($@"INSERT INTO callerid(NAME,PHONE1) VALUES ('{EditTextbox.Text}','{toCheck}')");
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show($"SQL Edit Error\n{ex.Message}");
}
// Clear name field and return to main tab on adding
this.EditTextbox.Text = String.Empty;
this.Tabs.SelectedIndex = 0;
// Force CID to update
cxHandle.forceRecheck = !cxHandle.forceRecheck;
}
}
public bool putUser(int id, User user)
{
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sql = "Select * from quiz_taker_info where id = " + id.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
sql = "Update quiz_taker_info set first_name = '" + user.Firstname + "', " +
"middle_name = '" + user.Middlename + "', " +
"last_name = '" + user.Lastname + "', " +
"email = '" + user.Username + "', " +
"group_id = '" + user.GroupId + "', " +
"password = '******' " +
"salt = '" + user.Salt + "' where id = " + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
};
}
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();
}
}
public void RunScriptFile(string ScriptFile)
{
if (!System.IO.File.Exists(ScriptFile))
{
throw new Exception("File '" + ScriptFile + "' does not exist.");
}
System.IO.StreamReader sr = null;
try {
sr = new System.IO.StreamReader(ScriptFile, false);
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnStr)) {
conn.Open();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sr.ReadToEnd();
cmd.ExecuteNonQuery();
}
conn.Close();
}
} catch (Exception ex) {
throw ex;
} finally {
sr.Close();
sr.Dispose();
}
}
public String saveUser(Victim 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 viktim (FBID, UserName, StartDate, Latitude, Longitude, Adress)"
+ "VALUES(" + personToSave.FBID + ",'" + personToSave.UserName + "','" + personToSave.StartDate.ToString("yyyy-MM-dd HH:mm:ss")
+ "','" + personToSave.Latitude + "','" + personToSave.Longitude + "','" + personToSave.Adress + "');";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
//string fbid = cmd.LastInsertedId;
return(personToSave.FBID);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public Int64 sql_procedureUpdate(string sql, MySql.Data.MySqlClient.MySqlConnection use_connect)
{
if (this.connected)
{
Int64 ret = 0;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = use_connect;
try
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
ret = cmd.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
this.lastErrorCode = ex.ErrorCode;
this.lastSqlErrorMessage = "UpdateError " + ex.Number + " has occurred: " + ex.Message;
return(0);
}
return(ret);
}
else
{
this.lastSqlErrorMessage = Projector.Properties.Resources.ErrorMysqlNotConnected;
return(0);
}
}
// displays infomation entered from orderpage
protected void Page_Load(object sender, EventArgs e)
{
string time = DateTime.Now.ToShortTimeString();
string date = DateTime.Now.ToShortDateString();
string total = (string)Session["FinalOrderTotal"];
string fName = (string)Session["FirstName"];
string lName = (string)Session["LastName"];
string country = (string)Session["Country"];
string address = (string)Session["Address"];
string city = (string)Session["City"];
string state = (string)Session["State"];
string zip = (string)Session["Zip"];
lblDateOfPurchaseConf.Text = date + " " + time;
lblPurTotalConf.Text = total;
lblFullNameConf.Text = fName + " " + lName;
lblConCountry.Text = country;
lblAddressConf.Text = address;
lblCityStateZipConf.Text = city + ", " + state + ", " + zip;
Globals.conn.Open();
string deleteQuery = "delete from shopping_cart where CartItemID > 0;";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(deleteQuery, Globals.conn);
cmd.ExecuteNonQuery(); //execute of the sql statement (called nonquery b/c writes/doesn't return anything, returns how many rows were affected, assing to an integer variable)
Globals.conn.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 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 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;
}
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 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 static int AddPhoto(PhotoItem PhotoDetails)
{
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 pics(PhotoID,Description,Name,UploadedOn) "
+ "VALUES(@PhotoID,@Description,@Name,@UploadedOn)";
msqlCommand.Parameters.AddWithValue("@PhotoID", PhotoDetails.PhotoID);
msqlCommand.Parameters.AddWithValue("@Description", PhotoDetails.Description);
msqlCommand.Parameters.AddWithValue("@Name", PhotoDetails.Name);
msqlCommand.Parameters.AddWithValue("@UploadedOn", PhotoDetails.UploadedOn);
msqlCommand.Parameters.AddWithValue("@ImgFile", PhotoDetails.ImgFile);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
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();
}
}
public static int DoRegisterNewDevice(StorageDeviceInfo DeviceDetails)
{
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 Device(DeviceId,lastScannedDate,lastUsedHost,files,remark) "
+ "VALUES(@DeviceId,@lastScannedDate,@lastUsedHost,@files,@filesBackupHere,@remark)";
msqlCommand.Parameters.AddWithValue("@DeviceId", DeviceDetails.deviceId);
msqlCommand.Parameters.AddWithValue("@files", DeviceDetails.files);
msqlCommand.Parameters.AddWithValue("@lastScannedDate", DeviceDetails.lastScannedDate);
msqlCommand.Parameters.AddWithValue("@lastUsedHost", DeviceDetails.lastUsedHost);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private static int DoRegisterNewContactusindb(ContactusInfo NewContactus)
{
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 contactus(id,feedDate,name,address,mobileno,email,type,feedback) "
+ "VALUES(@id,@feedDate,@name,@address,@mobileno,@email,@type,@feedback)";
msqlCommand.Parameters.AddWithValue("@id", NewContactus.id);
msqlCommand.Parameters.AddWithValue("@feedDate", NewContactus.feedDate);
msqlCommand.Parameters.AddWithValue("@name", NewContactus.name);
msqlCommand.Parameters.AddWithValue("@address", NewContactus.address);
msqlCommand.Parameters.AddWithValue("@mobileno", NewContactus.mobileno);
msqlCommand.Parameters.AddWithValue("@email", NewContactus.email);
msqlCommand.Parameters.AddWithValue("@type", NewContactus.type);
msqlCommand.Parameters.AddWithValue("@feedback", NewContactus.feedback);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private void btnStartUpload_Click(object sender, RoutedEventArgs e)
{
//define the connection reference
MySql.Data.MySqlClient.MySqlConnection msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; user id=root;password=technicise;database=photodb;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 pics(Name, Description, ImgFile,UploadedOn)" + "values(@Name,@Description,@ImgFile,@UploadedOn)";
//add values provided by user
string filepath = txtFileName.Text;
msqlcommand.Parameters.AddWithValue("@Name", filepath);
msqlcommand.Parameters.AddWithValue("@Description", txtDescription.Text);
byte[] imgbytes = File.ReadAllBytes("filepath");
// File.WriteAllBytes("c:\\aaa" + DateTime.Now.ToOADate().ToString() + ".jpg", imgbytes);
msqlcommand.Parameters.AddWithValue("@ImgFile", imgbytes);
msqlcommand.Parameters.AddWithValue("@UploadedOn", DateTime.Now);
msqlcommand.ExecuteNonQuery();
//close the connection
msqlConnection.Close();
//empty the text boxes
//rollTextBox.Text = null;
//nameTextBox.Text = null;
MessageBox.Show("Info Added");
// if (string.IsNullOrWhiteSpace(txtFileName.Text))
// {
// MessageBox.Show("Specifiy a file to upload.", "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtFileName.Focus();
// }
// else if (!File.Exists(txtFileName.Text))
// {
// string message = string.Format("Unable to find '{0}'. Please check the file name and try again.", txtFileName.Text);
// MessageBox.Show(message, "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtFileName.Focus();
// }
// else if (string.IsNullOrWhiteSpace(txtDescription.Text))
// {
// MessageBox.Show("Specify the description of the file to upload.", "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtDescription.Focus();
// }
// else
// {
// try
// {
// // Create the REST request.
// string url = ConfigurationManager.AppSettings["serviceUrl"];
// string requestUrl = string.Format("{0}/UploadPhoto/{1}/{2}", url, System.IO.Path.GetFileName(txtFileName.Text), txtDescription.Text);
// HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(requestUrl);
// request.Method = "POST";
// request.ContentType = "text/plain";
// byte[] fileToSend = File.ReadAllBytes(txtFileName.Text);
// request.ContentLength = fileToSend.Length;
// using (Stream requestStream = request.GetRequestStream())
// {
// // Send the file as body request.
// requestStream.Write(fileToSend, 0, fileToSend.Length);
// requestStream.Close();
// }
// using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
// Console.WriteLine("HTTP/{0} {1} {2}", response.ProtocolVersion, (int)response.StatusCode, response.StatusDescription);
// MessageBox.Show("File sucessfully uploaded.", "Upload", MessageBoxButton.OK, MessageBoxImage.Information);
// this.DialogResult = true;
// }
// catch (Exception ex)
// {
// MessageBox.Show("Error during file upload: " + ex.Message, "Upload", MessageBoxButton.OK, MessageBoxImage.Error);
// }
// }
}
public static int DoEnterProduct(ProductInfo NewProduct)
{
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 product(id,name,brand,type,description,availableinshop) "
+ "VALUES(@id,@name,@brand,@type,@description,@availableinshop)";
msqlCommand.Parameters.AddWithValue("@id", NewProduct.id);
msqlCommand.Parameters.AddWithValue("@name", NewProduct.name);
msqlCommand.Parameters.AddWithValue("@brand", NewProduct.brand);
msqlCommand.Parameters.AddWithValue("@type", NewProduct.type);
msqlCommand.Parameters.AddWithValue("@description", NewProduct.description);
msqlCommand.Parameters.AddWithValue("@availableinshop", NewProduct.availableinshop);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static int DoEnterShop(ShopInfo NewShop)
{
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 shop(id,name,tag,type,availableinfloor,rating,description,availableProduct) "
+ "VALUES(@id,@name,@tag,@type,@availableinfloor,@rating,@description,@availableProduct)";
msqlCommand.Parameters.AddWithValue("@id", NewShop.id);
msqlCommand.Parameters.AddWithValue("@name", NewShop.name);
msqlCommand.Parameters.AddWithValue("@tag", NewShop.tag);
msqlCommand.Parameters.AddWithValue("@type", NewShop.type);
msqlCommand.Parameters.AddWithValue("@availableinfloor", NewShop.@availableinfloor);
msqlCommand.Parameters.AddWithValue("@rating", NewShop.rating);
msqlCommand.Parameters.AddWithValue("@description", NewShop.description);
msqlCommand.Parameters.AddWithValue("@availableProduct", NewShop.availableProduct);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static void UpdateVendorTableWithPaymentOnPurchase(double totalBilledAmount, double purchasePaymentAmount, string venIdKey)
{
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;
double dbDue = 0.0;
double dbTurnOver = 0.0;
string cmdStr = "SELECT turn_over,due FROM vendors WHERE vendor_id='" + venIdKey + "';";
msqlCommand.CommandText = cmdStr;
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
dbDue = double.Parse(msqlReader.GetString("due"));
dbTurnOver = double.Parse(msqlReader.GetString("turn_over"));
}
if (msqlConnection.State == System.Data.ConnectionState.Open)
msqlConnection.Close();
//updating the value
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
double newDueDouble = dbDue + totalBilledAmount - purchasePaymentAmount;
String newDue = newDueDouble.ToString();
String newTurnOver = (dbTurnOver + totalBilledAmount).ToString();
msqlCommand.CommandText = "UPDATE vendors SET due='" + newDue + "', turn_over='" + newTurnOver + "' WHERE vendor_id='" + venIdKey + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
//MessageBox.Show("Error: " + MethodBase.GetCurrentMethod().Name + ":" + er.Message);
}
finally
{
if (msqlConnection.State == System.Data.ConnectionState.Open)
msqlConnection.Close();
}
}
public static void EditCustomerPayments(CustomerPaymentData returnEditedCustomerPaymentsData)
{
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 customer_payment SET payment_amount='" + returnEditedCustomerPaymentsData.paymentAmount + "' WHERE payment_id='" + returnEditedCustomerPaymentsData.paymentId + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static void EditProduct(ProductInfo newUpdateProduct)
{
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 product SET name=@name,brand=@brand,type=@type,description=@description WHERE id=@id";
msqlCommand.Parameters.AddWithValue("@name", newUpdateProduct.name);
msqlCommand.Parameters.AddWithValue("@brand", newUpdateProduct.brand);
msqlCommand.Parameters.AddWithValue("@type", newUpdateProduct.type);
msqlCommand.Parameters.AddWithValue("@description", newUpdateProduct.description);
msqlCommand.Parameters.AddWithValue("@id", newUpdateProduct.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static void EditShop(ShopInfo newUpdateShop)
{
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 shop SET name=@name,tag=@tag,type=@type,availableinfloor=@availableinfloor,rating=@rating,description=@description WHERE id=@id";
msqlCommand.Parameters.AddWithValue("@name", newUpdateShop.name);
msqlCommand.Parameters.AddWithValue("@tag", newUpdateShop.tag);
msqlCommand.Parameters.AddWithValue("@type", newUpdateShop.type);
msqlCommand.Parameters.AddWithValue("@availableinfloor", newUpdateShop.availableinfloor);
msqlCommand.Parameters.AddWithValue("@rating", newUpdateShop.rating);
msqlCommand.Parameters.AddWithValue("@description", newUpdateShop.description);
msqlCommand.Parameters.AddWithValue("@id", newUpdateShop.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private static int DoRegisterFeedbackindb(FeedbackInfo NewFeedback)
{
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 feedback(id,feedDate,item,name,email,rate,feedback) "
+ "VALUES(@id,@feedDate,@item,@name,@email,@rate,@feedback)";
msqlCommand.Parameters.AddWithValue("@id", NewFeedback.id);
msqlCommand.Parameters.AddWithValue("@feedDate", NewFeedback.feedDate);
msqlCommand.Parameters.AddWithValue("@item", NewFeedback.item);
msqlCommand.Parameters.AddWithValue("@name", NewFeedback.name);
msqlCommand.Parameters.AddWithValue("@email", NewFeedback.email);
msqlCommand.Parameters.AddWithValue("@rate", NewFeedback.rate);
msqlCommand.Parameters.AddWithValue("@feedback", NewFeedback.feedback);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static int DoRegisterNewPassword(PasswordInfo passwordDetails)
{
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 password(id,name,email,userId,password,secretQuestion,secretAnswer,otherInfo) "
+ "VALUES(@id,@name,@email,@userId,@password,@secretQuestion,@secretAnswer,@otherInfo)";
msqlCommand.Parameters.AddWithValue("@id", passwordDetails.id);
msqlCommand.Parameters.AddWithValue("@name", passwordDetails.name);
msqlCommand.Parameters.AddWithValue("@email", passwordDetails.email);
msqlCommand.Parameters.AddWithValue("@userId", passwordDetails.userId);
msqlCommand.Parameters.AddWithValue("@password", passwordDetails.password);
msqlCommand.Parameters.AddWithValue("@secretQuestion", passwordDetails.scrtqstn);
msqlCommand.Parameters.AddWithValue("@secretAnswer", passwordDetails.scrtans);
msqlCommand.Parameters.AddWithValue("@otherInfo", passwordDetails.otherInfo);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private static int DoRegisterNewuserindb(UserInfo NewUser)
{
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 user(id,userid,passwrd,hints) " + "VALUES(@id,@userid,@passwrd,@hints)";
msqlCommand.Parameters.AddWithValue("@id", NewUser.id);
msqlCommand.Parameters.AddWithValue("@userid", NewUser.userId);
msqlCommand.Parameters.AddWithValue("@passwrd", NewUser.pass);
msqlCommand.Parameters.AddWithValue("@hints", NewUser.hints);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private static int DoEnterNewNoteindb(NoteInfo NewNote)
{
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 note(id,date,note) " + "VALUES(@id,@date,@note)";
msqlCommand.Parameters.AddWithValue("@id", NewNote.id);
msqlCommand.Parameters.AddWithValue("@date", NewNote.gotoDate);
msqlCommand.Parameters.AddWithValue("@note", NewNote.note);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static int DoRegisterNewTask(TaskInfo TaskDetails)
{
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 task(taskId,priority,details) "
+ "VALUES(@taskId,@priority,@details)";
msqlCommand.Parameters.AddWithValue("@taskId", TaskDetails.id);
msqlCommand.Parameters.AddWithValue("@priority", TaskDetails.value);
msqlCommand.Parameters.AddWithValue("@details", TaskDetails.taskDetails);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
