public Fruits Create(Fruits t)
{
MySqlConnection conn = DB.DBUtils.GetDBConnection();
conn.OpenAsync();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText =
"INSERT INTO warehouse.fruit(FruitType,DealerCountry,Price,Count) VALUES(?FruitType,?DealerCountry,?Price,?Count)";
cmd.Connection = conn;
cmd.Parameters.Add("?Price", MySqlDbType.VarChar).Value = t.Price;
cmd.Parameters.Add("?Count", MySqlDbType.VarChar).Value = t.Count;
cmd.Parameters.Add("?FruitType", MySqlDbType.VarChar).Value = t.Type;
cmd.Parameters.Add("?DealerCountry", MySqlDbType.VarChar).Value = t.Country;
cmd.ExecuteNonQueryAsync();
conn.Clone();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(t);
}
public Beef Create(Beef t)
{
MySqlConnection conn = DB.DBUtils.GetDBConnection();
conn.OpenAsync();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO warehouse.beef(MeatFactoryName,MeatProductType,Price,Count,ReciveDate) VALUES(?MeatFactoryName,?MeatProductType,?Price,?Count,now())";
cmd.Connection = conn;
cmd.Parameters.Add("?Price", MySqlDbType.VarChar).Value = t.Price;
cmd.Parameters.Add("?Count", MySqlDbType.VarChar).Value = t.Count;
cmd.Parameters.Add("?MeatFactoryName", MySqlDbType.VarChar).Value = t.MeatFactoryName;
cmd.Parameters.Add("?MeatProductType", MySqlDbType.VarChar).Value = t.MeatProductType;
cmd.ExecuteNonQueryAsync();
conn.Clone();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(t);
}
public void DeleteById(int id)
{
MySqlConnection conn = DB.DBUtils.GetDBConnection();
conn.OpenAsync();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "DELETE from Fruit where id=" + id + ";";
cmd.Connection = conn;
cmd.ExecuteNonQueryAsync();
conn.Clone();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public void UpdateById(int id, Fruits t)
{
MySqlConnection conn = DB.DBUtils.GetDBConnection();
conn.OpenAsync();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "UPDATE Fruit fruit SET FruitType = ?FruitType, DealerCountry = ?DealerCountry, Price = ?Price, Count = ?Count WHERE ID = " + id + ";";
cmd.Connection = conn;
cmd.Parameters.Add("?Price", MySqlDbType.VarChar).Value = t.Price;
cmd.Parameters.Add("?Count", MySqlDbType.VarChar).Value = t.Count;
cmd.Parameters.Add("?FruitType", MySqlDbType.VarChar).Value = t.Type;
cmd.Parameters.Add("?DealerCountry", MySqlDbType.VarChar).Value = t.Country;
cmd.ExecuteNonQueryAsync();
conn.Clone();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public void UpdateById(int id, Beef t)
{
MySqlConnection conn = DB.DBUtils.GetDBConnection();
conn.OpenAsync();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "UPDATE Beef SET MeatFactoryName = @MeatFactoryName, MeatProductType = @MeatProductType, Price = ?Price, Count = ?Count WHERE ID = " + id + ";";
cmd.Connection = conn;
cmd.Parameters.Add("?Price", MySqlDbType.VarChar).Value = t.Price;
cmd.Parameters.Add("?Count", MySqlDbType.VarChar).Value = t.Count;
cmd.Parameters.Add("?MeatFactoryName", MySqlDbType.VarChar).Value = t.MeatFactoryName;
cmd.Parameters.Add("?MeatProductType", MySqlDbType.VarChar).Value = t.MeatProductType;
cmd.ExecuteNonQueryAsync();
conn.Clone();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public bool comboSave(ArrayList delSqls, string itemMapSql, ArrayList mapSetSqls)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlCon = null;
try
{
using (mySqlCon = new MySqlConnection(strConn))
{
if (mySqlCon != null && mySqlCon.State == ConnectionState.Closed)
{
mySqlCon.Open();
}
mySqlCmd.Connection = mySqlCon;
mySqlCmd.Transaction = mySqlCon.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
//删除原有对照
if (delSqls != null && delSqls.Count > 0)
{
foreach (var item in delSqls)
{
mySqlCmd.CommandText = item.ToString();
mySqlCmd.ExecuteNonQuery();
}
}
//保存ProductItemMap并返回主键
mySqlCmd.CommandText = itemMapSql;
int rid = Convert.ToInt32(mySqlCmd.ExecuteScalar());
//保存ProductMapSet
if (mapSetSqls != null && mapSetSqls.Count > 0)
{
foreach (var item in mapSetSqls)
{
mySqlCmd.CommandText = string.Format(item.ToString(), rid);
mySqlCmd.ExecuteNonQuery();
}
}
mySqlCmd.Transaction.Commit();
return true;
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("ProductItemMapDao.comboSave(ArrayList delSqls, string itemMapSql, ArrayList mapSetSqls)-->" + ex.Message, ex);
}
finally
{
if (mySqlCon != null && mySqlCon.State == ConnectionState.Open)
{
mySqlCon.Clone();
}
}
}
private void LoadEasyData()
{
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
try
{
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM easy";
MySqlDataAdapter adapt = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
history.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
catch (Exception)
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
public void Update()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("update schedule set " +
"t_id = @t_id, " +
"t_start = @t_start, " +
"t_finish = @t_finish, " +
"r_id = @r_id " +
"where s_id = @s_id", conn);
cmd.Parameters.Add( new MySqlParameter("s_id", MySqlDbType.Int32)).Value = this.ScheduleId;
cmd.Parameters.Add( new MySqlParameter("t_id", MySqlDbType.Int32)).Value = this.TaskId;
cmd.Parameters.Add( new MySqlParameter("t_start", MySqlDbType.DateTime)).Value = this.Start;
cmd.Parameters.Add( new MySqlParameter("t_finish", MySqlDbType.DateTime)).Value = this.Finish;
cmd.Parameters.Add( new MySqlParameter("r_id", MySqlDbType.Int32)).Value = this.ResourceId;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
bool probarCon()
{
bool correcto = false;
//Destruimos cualquier conexion existente
if (conDB != null)
conDB.Clone();
//Creamos la cadena de conexion
string conStr = String.Format("server={0};port={1};user id={2}; password={3}; " +
"database={4};" +
"Allow Zero Datetime=False;Convert Zero Datetime=True",
txtServidor.Text, txtPuerto.Text, txtUsuario.Text, txtContrasena.Text, cmbBaseDatos.Text);
try
{
conDB = new MySqlConnection(conStr);
conDB.Open();
//Se deshabilitan las cajas de texto
txtContrasena.Enabled = false;
txtPuerto.Enabled = false;
txtServidor.Enabled = false;
txtUsuario.Enabled = false;
cmbBaseDatos.Enabled = false;
probada = true;
MessageBox.Show("La conexión ha sido probada correctamente", "Correcto");
correcto = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ha ocurrido el siguiente error:");
}
finally
{
if (conDB != null)
conDB.Clone();
}
return correcto;
}
private void btnExaminarArchivo2_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialogCal = new OpenFileDialog();
if (openFileDialogCal.ShowDialog() == DialogResult.OK)
{
string filepathcal = openFileDialogCal.FileName, straux;
bool tituloColumnas = false;
int columnMatricula = 0, columnNombreAlumno = 0, columnApPaterno = 0, columnApMaterno = 0, columnCRN = 0;
var reader = new StreamReader(File.OpenRead(filepathcal));
List<string> listMatricula = new List<string>();
List<string> listNombreAlumno = new List<string>();
List<string> listApPaterno = new List<string>();
List<string> listApMaterno = new List<string>();
List<string> listCRN = new List<string>();
List<string> aux = new List<string>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
if (tituloColumnas == false)//Comparación con doble '='
{
for (int i = 0; i < values.Count(); i++)
{
if (values[i].Contains("Matricula"))
columnMatricula = i;
else if (values[i].Contains("Nombre del Alumno"))
columnNombreAlumno = i;
else if (values[i].Contains("Apellido Paterno"))
columnApMaterno = i;
else if (values[i].Contains("Apellido Materno"))
columnApPaterno = i;
else if (values[i].Contains("CRN"))
{
columnCRN = i;
tituloColumnas = true;
break;
}
}
}
else
{
/* for (int i = 0; i <= 33554; i++)
{
string j = (values[columnMatricula]);
if (j != (values[i]))
{
listMatricula.Add(values[columnMatricula]);
}
}*/
straux = values[columnMatricula] + ";" + values[columnNombreAlumno] + ";" + values[columnApPaterno] + ";" + values[columnApMaterno];
aux.Add(straux);
aux = aux.Distinct().ToList();
/*
listMatricula.Add(values[columnMatricula]);
listNombreAlumno.Add(values[columnNombreAlumno]);
listApPaterno.Add(values[columnApPaterno]);
listApMaterno.Add(values[columnApMaterno]);
*/
//listCRN.Add(values[columnCRN]);
}
}
for (int i = 0; i < aux.Count(); i++)
{
var auxline = aux[i];
var auxvalues = auxline.Split(';');
listMatricula.Add(auxvalues[0]);
listNombreAlumno.Add(auxvalues[1]);
listApPaterno.Add(auxvalues[3]);
listApMaterno.Add(auxvalues[2]);
}
MessageBox.Show("Archivo cargado correctamente");
string MyConString = ("SERVER = localhost;" + "DATABASE = proyecto_final_bd;" + "UID = root;" + "PASSWORD = ;");
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
connection.Open();
for (int n = 0; n < listMatricula.Count; n++)
{
string query = "INSERT INTO alumnos(matricula, nombre, apellidoP, apellidoM) VALUES('" + listMatricula[n] + "','" + listNombreAlumno[n] + "','" + listApPaterno[n] + "','" + listApMaterno[n] + "')";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
}
connection.Clone();
}
}
public void Save()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("insert into tasks (t_title, t_time_req, t_depends, t_type) values (@name, @time, @depends, @type)", conn);
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
cmd.Parameters.Add( new MySqlParameter("time", MySqlDbType.Int32)).Value = this.TimeRequired;
cmd.Parameters.Add( new MySqlParameter("depends", MySqlDbType.Int32)).Value = this.Depends;
cmd.Parameters.Add( new MySqlParameter("type", MySqlDbType.Int32)).Value = this.TaskType;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public static void DataInitializer()
{
string dirPath = "D:/Documents/Bengkel App/Data/";
string accFile = "Account Jurnal.xlsx";
string invFile = "Inv.xlsx";
string citFile = "City.xlsx";
string balJournal = "BalanceJournal.xlsx";
try
{
// todo: read excel inventory and acc and insert into temporary table
DataTable resultInv = DataExportImportUtils.CreateDataTableFromExcel(dirPath + invFile, true);
DataTable resultAcc = DataExportImportUtils.CreateDataTableFromExcel(dirPath + accFile, true);
DataTable resultCit = DataExportImportUtils.CreateDataTableFromExcel(dirPath + citFile, true);
DataTable resultCatJournal = DataExportImportUtils.CreateDataTableFromExcel(dirPath + balJournal, true);
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_catjournal` (
`Code` varchar(100) DEFAULT NULL,
`Name` varchar(100) DEFAULT NULL,
`Description` varchar(100) DEFAULT NULL,
`Value` varchar(100) DEFAULT NULL,
`Parent` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_cit` (
`Id` varchar(100) DEFAULT NULL,
`Code` varchar(100) DEFAULT NULL,
`Name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_inv` (
`Kode` varchar(100) DEFAULT NULL,
`Nama` varchar(100) DEFAULT NULL,
`Qmin` double DEFAULT NULL,
`Unit` varchar(10) DEFAULT NULL,
`Chusr` int(11) DEFAULT NULL,
`Chtime` varchar(100) DEFAULT NULL,
`Jenis` varchar(100) DEFAULT NULL,
`Price` double DEFAULT NULL,
`ACC` varchar(45) DEFAULT NULL,
`LAMA` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_acc` (
`Kode` varchar(100) DEFAULT NULL,
`Nama` varchar(100) DEFAULT NULL,
`Induk` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
if (resultCatJournal != null)
{
resultCatJournal.ExportDataTableToCsv(dirPath + "catjournal.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_catjournal";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "catjournal.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// insert into database main ref
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO `references` (`Code`, `Name`, `Description`, `Value`, `ParentId`)
SELECT REPLACE(REPLACE(`code`, '\r', ''), '\n', ''), `Name`, `Description`, `Value`,
(SELECT `Id` FROM `references` WHERE `Code`=REPLACE(REPLACE(`parent`, '\r', ''), '\n', ''))
FROM temp_catjournal";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
// using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
// {
// MySqlCommand cmd = conn.CreateCommand();
// cmd.CommandText = @"UPDATE `references` a
// SET a.ParentId = (SELECT z.Id FROM (SELECT x.*, y.Code `Kode` FROM `references` x, temp_catjournal y WHERE x.Code=y.Parent) z WHERE z.Kode=a.Code)";
// cmd.CommandType = CommandType.Text;
// conn.Open();
// cmd.ExecuteNonQuery();
// conn.Clone();
// }
}
if (resultCit != null)
{
resultCit.ExportDataTableToCsv(dirPath + "city.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_cit";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "city.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// insert into database city
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO cities (`Code`, `Name`)
SELECT `Code`, `Name`
FROM temp_cit";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
if (resultInv != null)
{
// fix typing error
foreach (DataRow iRow in resultInv.Rows)
{
if (iRow["Kode"].ToString().Contains("["))
{
iRow["Kode"] = iRow["Kode"].ToString().Replace("[", "");
}
if (iRow["Unit"].ToString().Contains("PV"))
{
iRow["Unit"] = "PC";
}
}
// convert to csv
resultInv.ExportDataTableToCsv(dirPath + "inv.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_inv";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "inv.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// generate main sparepart data
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO spareparts (`Code`, `Name`, `StockQty`, `UnitReferenceId`, `CategoryReferenceId`, `CreateDate`, `CreateUserId`, `ModifyDate`, `ModifyUserId`, `Status`)
SELECT Kode, Nama, 0,
IFNULL((SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTUNIT') AND a.`Code`=Unit),
(SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTUNIT') AND a.`Code`='-')),
IFNULL((SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTCATEGORY') AND a.`Code`=SUBSTR(Kode, 1, LENGTH(a.`Code`))),
(SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTCATEGORY') AND a.`Code`='-')),
current_date(), (SELECT Id FROM users WHERE UserName='******'),
current_date(), (SELECT Id FROM users WHERE UserName='******'), 1
FROM temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
if (resultAcc != null)
{
foreach (DataRow row in resultAcc.Rows)
{
row["Kode"] = row["Kode"].ToString().Trim();
try
{
row["Induk"] = row["Induk"].ToString();
}
catch { }
}
// convert to csv
resultAcc.ExportDataTableToCsv(dirPath + "acc.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_acc";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "acc.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE temp_acc SET Induk=REPLACE(REPLACE(Induk, '\r', ''), '\n', '')";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
// generate main account data
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO journalmasters (`Code`, `Name`, `ParentId`)
SELECT `Kode`, `Nama`,
(SELECT a.Id FROM journalmasters a, temp_acc b WHERE a.Code=b.Induk)
FROM temp_acc";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE journalmasters a
SET a.ParentId = (SELECT z.Id FROM (SELECT x.*, y.Kode FROM journalmasters x, temp_acc y WHERE x.Code=y.Induk) z WHERE z.Kode=a.Code)";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_catjournal";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_cit";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_acc";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("update groups set g_name=@name, tt_id=@tt_id where g_id=@id", conn);
cmd.Parameters.Add( new MySqlParameter("id", MySqlDbType.Int32)).Value = this.Id;
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
cmd.Parameters.Add( new MySqlParameter("tt_id", MySqlDbType.Int32)).Value = this.TaskType;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public void Update()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("update tasks set t_title=@name, t_time_req=@time, t_depends=@depends, t_type=@type where t_id=@id", conn);
cmd.Parameters.Add( new MySqlParameter("id", MySqlDbType.Int32)).Value = this.Id;
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
cmd.Parameters.Add( new MySqlParameter("time", MySqlDbType.Int32)).Value = this.TimeRequired;
cmd.Parameters.Add( new MySqlParameter("depends", MySqlDbType.Int32)).Value = this.Depends;
cmd.Parameters.Add( new MySqlParameter("type", MySqlDbType.Int32)).Value = this.TaskType;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public void Update()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("update resources set r_name=@name, r_active=@active where r_id=@id", conn);
cmd.Parameters.Add( new MySqlParameter("id", MySqlDbType.Int32)).Value = this.Id;
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
cmd.Parameters.Add( new MySqlParameter("active", MySqlDbType.Bit)).Value = this.Active;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public void Save()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("insert into groups (g_name, tt_id) values (@name, @tt_id)", conn);
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
cmd.Parameters.Add( new MySqlParameter("tt_id", MySqlDbType.Int32)).Value = this.TaskType;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
private DataTable GetTable(string sql)
{
using (MySqlConnection connection = new MySqlConnection("server=localhost;user=root;database=lzbankdatabase;port=3306;password=root;"))
{
DataSet ds = new DataSet();
connection.Open();
try
{
MySqlDataAdapter da = new MySqlDataAdapter();
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.CommandTimeout = 600;
da.SelectCommand = cmd;
da.Fill(ds, "ds");
}
catch
{ }
connection.Clone();
return ds.Tables[0];
}
}
public void Save()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("insert into schedule (t_id, t_start, t_finish, r_id) values (@t_id, @t_start, @t_finish, @r_id)", conn);
cmd.Parameters.Add( new MySqlParameter("t_id", MySqlDbType.Int32)).Value = this.TaskId;
cmd.Parameters.Add( new MySqlParameter("t_start", MySqlDbType.DateTime)).Value = this.Start;
cmd.Parameters.Add( new MySqlParameter("t_finish", MySqlDbType.DateTime)).Value = this.Finish;
cmd.Parameters.Add( new MySqlParameter("r_id", MySqlDbType.Int32)).Value = this.ResourceId;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
/// <summary>
/// Initialize connection to DB
/// </summary>
/// <param name="host">Host</param>
/// <param name="database">Database</param>
/// <param name="user">User</param>
/// <param name="password">Password</param>
/// <returns>Return true if connection success</returns>
public static bool Initialize(string host, string database, string user, string password)
{
if (_connection != null && _connection.State == ConnectionState.Open)
{
CloseConnection();
}
var connectionString = "SERVER=" + host + ";UID=" + user + ";PASSWORD="******"SERVER=" + host + ";DATABASE=" + database + ";UID=" + user + ";PASSWORD=" + password;
_connection = new MySqlConnection(connectionDbString);
if (OpenConnection())
{
CreateTables();
return true;
}
return false;
}
private void button2_Click(object sender, EventArgs e)
{
string MyConnectionString = "Server=serwer1585870.home.pl;Database=18659907_0000002;Uid=18659907_0000002;Pwd=#bazalukasza;";
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
try
{
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "select * FROM tb_movies";
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView2.DataSource = ds.Tables[0].DefaultView;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
public void db_connect ()
{
string server = "10.3.30.82";
string user = "******";
string password = "";
string database = "jscom";
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "server=" + server + ";Uid=" + user + ";Pwd=" + password + ";database=" + database;
try
{
conn.Open();
}
catch (Exception)
{
MessageBox.Show("x");
}
finally
{
conn.Clone();
}
}
public void ViewDB()
{
String database = "class" + classSec + "_db";
String connectionStr = "Server=localhost; Port=3306; Database=" + database +"; Uid=root; Pwd=admin;";
MySqlConnection connection = new MySqlConnection(connectionStr);
connection.Open();
try
{
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM studentinfo";
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
catch(Exception)
{
throw;
}
finally
{
if(connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
public void Delete()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("delete from groups where g_id = @id", conn);
cmd.Parameters.Add( new MySqlParameter("id", MySqlDbType.Int32)).Value = this.Id;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public void Save()
{
MySqlConnection conn = new MySqlConnection(Globals.ConnectionString);
MySqlCommand cmd = new MySqlCommand("insert into tasktypes (tt_name) values (@name)", conn);
cmd.Parameters.Add( new MySqlParameter("name", MySqlDbType.VarChar)).Value = this.Name;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
public static void Main(string[] args)
{
MySqlConnection mySqlConnection = new MySqlConnection (
"Database=dbprueba;Data Source=localhost;User Id=root;Password=root"
);
mySqlConnection.Open ();
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand ();
mySqlCommand.CommandText = "SELECT * FROM articulo";
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader ();
showColumnNames (mySqlDataReader);
showRows (mySqlDataReader);
mySqlDataReader.Close ();
mySqlConnection.Clone ();
}
public override void executeQuery(string queryString)
{
try {
using (var conn = new MySqlConnection(connString))
{
conn.Open();
if (queryString.IndexOf("CREATE DATABASE") != -1)
conn.ChangeDatabase(ServerSettings.GetSetting("MySQL-DBName"));
MySqlCommand cmd = new MySqlCommand(queryString, conn);
cmd.ExecuteNonQuery();
conn.Clone();
conn.Dispose();
}
}
catch (Exception e)
{
Server.Log(e);
}
}
public void Cadastro(Modelo mo)
{
string caminho = "SERVER=localhost;DATABASE=dbexemplo;UID=root;PASS="******"INSERT INTO tbexemplo(nome, email) VALUES('{0}', '{1}');", mo.Nome, mo.Email);
MySqlCommand comandos = new MySqlCommand(inserir, conexao);
comandos.ExecuteNonQuery();
conexao.Clone();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}