private void cartelera_Load(object sender, EventArgs e)
{
//frmCiudad ciudad = new frmCiudad();
String numeroSucursal = lblsucursal.Text;
string query = string.Format("select TRHORA.idSucursal,MAPELI.iidpelicula, MAPELI.bimagen FROM TRHORARIO TRHORA, MAPELICULA MAPELI WHERE TRHORA.iidpelicula=MAPELI.iidpelicula and TRHORA.idSucursal = '"+ numeroSucursal +"'");
MySqlCommand comando = new MySqlCommand(query, classCrearConexion.Conexion());
MySqlDataAdapter da = new MySqlDataAdapter(comando);
DataSet ds = new DataSet("MAPELICULA");
da.Fill(ds, "MAPELICULA");
byte[] datos = new byte[0];
DataRow dr = ds.Tables["MAPELICULA"].Rows[0];
datos = (byte[])dr["bimagen"];
System.IO.MemoryStream ms = new System.IO.MemoryStream(datos);
pb1.Image = System.Drawing.Bitmap.FromStream(ms);
/*pb1.Image = Properties.Resources.El_Destino_de_Júpite;
pb1.Refresh();
pb2.Image = Properties.Resources.FF2;
pb2.Refresh();
pb3.Image = Properties.Resources.In_to_the_Woods_En_el_Bosqu;
pb3.Refresh();
pb4.Image = Properties.Resources.Ombis_Alien_Invasion__2015_;
pb4.Refresh();
pb5.Image = Properties.Resources.peliculas_2015_15_e1422754101215;
pb5.Refresh();
pb6.Image = Properties.Resources.peliculas_2015_rapido_furioso_4;
pb6.Refresh();
pb7.Image = Properties.Resources.images;
pb7.Refresh();*/
}
public void AutoIncrementColumns()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
execSQL("INSERT INTO Test VALUES(NULL)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
DataRow row = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(row);
try
{
da.Update(ds);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
ds.Clear();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
cb.Dispose();
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql">sql 语句</param>
/// <param name="tablename">指定DataSet中的表，不指定传null</param>
/// <param name="parameters">参数</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataSet(string sql, string tablename, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(Connection))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
conn.Open();
if ((parameters != null) && (parameters.Length > 0))
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
}
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
if (tablename != null)
{
adapter.Fill(ds, tablename);
}
else
{
adapter.Fill(ds);
}
return ds;
}
}
public void DataViewControl()
{
string myConn = "datasource=localhost;port=3306;username='******';password=''";
string Querry = "select * from aus_work.user;";
MySqlConnection conn = new MySqlConnection(myConn);
//MySqlCommand cmddatabase = new MySqlCommand(Querry, conn);
MySqlDataAdapter dataadap = new MySqlDataAdapter(Querry, conn);
conn.Close();
DataTable userdatatablecontol = new DataTable();
dataadap.Fill(userdatatablecontol);
dataadap.Fill(userdatatable);
dataGridView1.DataSource = userdatatablecontol;
}
System.Data.DataTable IDB.ExecuteToTable(string sql, System.Data.IDbDataParameter[] pars)
{
MySql.Data.MySqlClient.MySqlConnection conn = this.GetNewConn();
try
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 8000;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
da.SelectCommand = cmd;
cmd.CommandText = sql;
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//
conn.Open();
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
return(dt);
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
public static PenjualanDetail FindOneById(int id)
{
Database.OpenConnection();
string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id");
Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter();
da.SelectCommand = new Sql.MySqlCommand(select, Database.conn);
da.SelectCommand.Parameters.AddWithValue("@id", id);
Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Database.CloseConnection();
PenjualanDetail penjualanDetail = new PenjualanDetail();
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
penjualanDetail.setId(Convert.ToInt32(dr["id"]));
penjualanDetail.setBarang(Barang.FindOneById(Convert.ToInt32(dr["id_barang"])));
penjualanDetail.setPenjualan(Penjualan.FindOneById(Convert.ToInt32(dr["id_penjualan"])));
penjualanDetail.setKuantitas(Convert.ToInt32(dr["kuantitas"]));
penjualanDetail.setHargaBarang(Convert.ToDecimal(dr["harga_barang"]));
penjualanDetail.setCreatedAt(Convert.ToDateTime(dr["created_at"]));
penjualanDetail.setUpdatedAt(Convert.ToDateTime(dr["updated_at"]));
return(penjualanDetail);
}
return(null);
}
public static Supplier FindOneById(int id)
{
Database.OpenConnection();
string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id");
Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter();
da.SelectCommand = new Sql.MySqlCommand(select, Database.conn);
da.SelectCommand.Parameters.AddWithValue("@id", id);
Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Database.CloseConnection();
Supplier supp = new Supplier();
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
supp.setId(Convert.ToInt32(dr["id"]));
supp.setNama(dr["nama"].ToString());
supp.setAlamat(dr["alamat"].ToString());
supp.setNomorTelepon(dr["nomor_telepon"].ToString());
supp.setKodePos(dr["kode_pos"].ToString());
supp.setKota(dr["kota"].ToString());
supp.setCreatedAt(Convert.ToDateTime(dr["created_at"]));
supp.setUpdatedAt(Convert.ToDateTime(dr["updated_at"]));
return(supp);
}
return(null);
}
public DataSet getData(string GINNO, string TYPEID, string STAT, string OFFICER, string CUSTVENDCODE, int APPROVE)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT GINNO,ITEMSID,COLORID,COSTINGNO,PONO,QUANTITY,UOM,PRICE," +
"CURRENCYID,KURS,REMARKS,STATUSCODA,QTYPURCHASE,UOMPURCHASE" +
" FROM tblgindetail WHERE ginNO LIKE @ginNO AND TYPEID LIKE @TYPEID AND IFNULL(STAT,1) LIKE @STAT " +
"AND IFNULL(OFFICER,1) LIKE @OFFICER AND CUSTVENDCODE LIKE @CUSTVENDCODE AND APPROVE LIKE @APPROVE";
strQuery.Parameters.AddWithValue("@ginNO", "%" + GINNO + "%");
strQuery.Parameters.AddWithValue("@TYPEID", "%" + TYPEID + "%");
strQuery.Parameters.AddWithValue("@STAT", "%" + STAT + "%");
strQuery.Parameters.AddWithValue("@OFFICER", "%" + OFFICER + "%");
strQuery.Parameters.AddWithValue("@CUSTVENDCODE", "%" + CUSTVENDCODE + "%");
strQuery.Parameters.AddWithValue("@APPROVE", "%" + APPROVE + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblgindetail");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
public static DataTable FindByIdPenjualan(int id_penjualan)
{
Database.OpenConnection();
string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id_penjualan = @id_penjualan");
Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter();
da.SelectCommand = new Sql.MySqlCommand(select, Database.conn);
da.SelectCommand.Parameters.AddWithValue("@id_penjualan", id_penjualan);
Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Database.CloseConnection();
DataTable dt = ds.Tables[0].Clone();
foreach (DataColumn dc in dt.Columns)
{
dc.DataType = typeof(string);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
dt.ImportRow(row);
}
return(dt);
}
public DataSet getData(string ItemsID, string Description, string ItemsTypeID, string Type)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT ITEMSID,DESCRIPTION,ITEMSTYPEID,TYPE,ACTIVE,IMAGE,PATH FROM tblmitems WHERE ITEMSID LIKE @ItemsID AND DESCRIPTION LIKE @Description AND ITEMSTYPEID LIKE @ItemsTypeID AND TYPE LIKE @Type";
strQuery.Parameters.AddWithValue("@ItemsID", "%" + ItemsID + "%");
strQuery.Parameters.AddWithValue("@Description", "%" + Description + "%");
strQuery.Parameters.AddWithValue("@ItemsTypeID", "%" + ItemsTypeID + "%");
strQuery.Parameters.AddWithValue("@Type", "%" + Type + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmitems");
Conn.Putus();
}
catch
{
}
return(ds);
}
public DataSet getData(string USERID, string MODUL)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT *" +
" FROM tblmoduluser WHERE USERID LIKE @USERID AND MODUL LIKE @MODUL";
strQuery.Parameters.AddWithValue("@USERID", "%" + USERID + "%");
strQuery.Parameters.AddWithValue("@MODUL", "%" + MODUL + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmoduluser");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
/// <summary>
/// Devuelve un objeto DataTable a partir de los parámetros pasados.
/// </summary>
/// <param name="strSelectSQL">Sentencia SELECT para llenar el objeto DataTable.</param>
/// <returns></returns>
public System.Data.DataTable getDataTable(string strSelectSQL)
{
MySql.Data.MySqlClient.MySqlDataAdapter oDA = new MySql.Data.MySqlClient.MySqlDataAdapter(strSelectSQL, m_oConn);
System.Data.DataTable oDT = new System.Data.DataTable();
oDA.Fill(oDT);
return(oDT);
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">sql语句(select)</param>
/// <returns>DataTable</returns>
public static DataTable GetQuery(string sql)
{
// 打开
OpenConnecion();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = ConnectionPool_mysql.con;
cmd.CommandTimeout = cmd.Connection.ConnectionTimeout;
if (m_isTransaction == true)
{
cmd.Transaction = ConnectionPool_mysql.trans;
}
DataSet ds = new DataSet();
DataTable dt = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
da.Fill(ds, "tmp");
dt = ds.Tables["tmp"];
if (!m_isTransaction)
{
CloseConnection();
}
return(dt);
}
private static DataTable MySqLTestResult(string connectionString, string query)
{
DataTable table = new DataTable();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;
MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
myAdapter.SelectCommand = cmd;
myAdapter.Fill(table);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
switch (ex.Number)
{
case 0:
break;
case 1045:
break;
}
}
return(table);
}
public DataSet MySqlDataSet(string StoredProcedureName, bool storedProcedure)
{
MySqlConnection __Conn = new MySqlConnection();
DataSet _dt = new DataSet();
//-----------------------------------------------
// set the mysql connection fix configratioin or set configaration
//------------------------------------------------
if (IsMySqlConnected)
{
__Conn = MyConnected;
}
else
{
__Conn = MySql.Configuration();
}
//-----------------------------------------------
try
{
__Conn.Open();
MySqlDataAdapter _MysqlDataAdaper = new MySql.Data.MySqlClient.MySqlDataAdapter(StoredProcedureName, __Conn);
_MysqlDataAdaper.SelectCommand.CommandType = CommandType.StoredProcedure;
_MysqlDataAdaper.Fill(_dt);
__Conn.Close();
Messege = "Success";
return(_dt);
}
catch (Exception er)
{
ExMessege = er;
Messege = er.Message;
return(_dt);
}
}
public override List <Time> TimesinRange(DateTime dateA, DateTime dateB)
{
logger.Info($"TimesInRange, DateA: {dateA} | DateB: {dateB}.........");
Debug.WriteLine($"TimesInRange: {dateA}-{dateB}");
List <Time> times = new List <Time>();
string qry = $"select * from `{TimeTableName}`";
DataTable vt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(qry, _connection);
da.Fill(vt);
foreach (DataRow row in vt.Rows)
{
if (Convert.ToDateTime(row["Date"].ToString()) >= dateA &&
Convert.ToDateTime(row["Date"].ToString()) <= dateB)
{
DateTime inTime = Convert.ToDateTime(row["TimeIn"].ToString());
DateTime outTime = Convert.ToDateTime(row["TimeOut"].ToString());
times.Add(new Time(inTime, outTime)
{
Key = row["Key"].ToString()
});
}
}
logger.Info($"TimesInRange, DateA: {dateA} | DateB: {dateB}.........FINISHED!!! Count: {times.Count}");
return(times);
}
public override List <Day> DaysInRange(DateTime dateA, DateTime dateB)
{
logger.Info($"DaysInRange, DateA: {dateA} | DateB: {dateB}.........");
var times = new List <Day>();
string qry = $"Select * From `{DayTableName}`";
DataTable vt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(qry, _connection);
da.Fill(vt);
foreach (DataRow row in vt.Rows)
{
if (Convert.ToDateTime(row["Date"].ToString()) >= Convert.ToDateTime(dateA) &&
Convert.ToDateTime(row["Date"].ToString()) <= Convert.ToDateTime(dateB))
{
times.Add(new Day(Convert.ToDateTime(row["Date"].ToString()))
{
Details = row["Details"].ToString(),
Times = TimesinRange(Convert.ToDateTime(row["Date"].ToString()), Convert.ToDateTime(row["Date"].ToString()))
});
}
}
logger.Info($"DaysInRange, DateA: {dateA} | DateB: {dateB}.........FINISHED!!! Count: {times.Count}");
return(times);
}
public DataSet getData(string StoreID, string Name, string Address)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT STOREID,NAME,ADDRESS,ACTIVE FROM tblmstore WHERE STOREID LIKE @StoreID AND NAME LIKE @Name AND ADDRESS LIKE @Address ";
strQuery.Parameters.AddWithValue("@StoreID", "%" + StoreID + "%");
strQuery.Parameters.AddWithValue("@Name", "%" + Name + "%");
strQuery.Parameters.AddWithValue("@Address", "%" + Address + "%");
//strQuery.Parameters.AddWithValue("@CategoryID", item);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmstore");
Conn.Putus();
}
catch
{
}
return(ds);
}
public ActionResult PorDepartamento()
{
string cs = @"server=localhost;user id=root;password=pass;persistsecurityinfo=True;database=quejas";
var con = new MySql.Data.MySqlClient.MySqlConnection(cs); con.Open();
var stm = "select count(queja.queja) as QUEJA , departamento.nombre_departamento as Departamento , departamento.iddepartamento from departamento " +
"inner join municipio on departamento.iddepartamento = municipio.iddepartamento " +
"inner join sucursal on municipio.idmunicipio = sucursal.idmunicipio " +
"inner join queja on sucursal.idsucursal = queja.idsucursal " +
"inner join region on departamento.idregion = region.idregion " +
"group by departamento.iddepartamento";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(stm, con);
MySql.Data.MySqlClient.MySqlDataAdapter mySQLDataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataSet data = new DataSet();
mySQLDataAdapter.Fill(data);
var datos = data.Tables[0].Rows;
List <Consulta> detalles = new List <Consulta>();
foreach (DataRow item in datos)
{
detalles.Add(new Consulta()
{
Queja = item.ItemArray[0].ToString(),
Comercio = item.ItemArray[1].ToString(),
Id_comercio = item.ItemArray[2].ToString()
});
}
return(View(detalles));
}
public DataSet getData(string COSTINGNO, string TYPEID, string STAT, string OFFICER, string CUSTVENDCODE, int APPROVE)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT COSTINGNO,QTYORDER,GDIV,TYPEID,CATEGORYID,STAT,DATE,OFFICER," +
"ITEMSID,CUSTVENDCODE,APPROVE,APPROVEDATE,APPROVEBY,COGS,MARGIN,MARGINVALUE,NETTPRICE," +
"DISCOUNT,DISCOUNTVALUE,PPN,PPNVALUE,SELLINGPRICE,KURS,VALUEIN,REVISE,REVISEDATE,REVISEBY" +
" FROM tblcosting WHERE COSTINGNO LIKE @COSTINGNO AND TYPEID LIKE @TYPEID AND IFNULL(STAT,1) LIKE @STAT " +
"AND IFNULL(OFFICER,1) LIKE @OFFICER AND CUSTVENDCODE LIKE @CUSTVENDCODE AND APPROVE LIKE @APPROVE";
strQuery.Parameters.AddWithValue("@COSTINGNO", "%" + COSTINGNO + "%");
strQuery.Parameters.AddWithValue("@TYPEID", "%" + TYPEID + "%");
strQuery.Parameters.AddWithValue("@STAT", "%" + STAT + "%");
strQuery.Parameters.AddWithValue("@OFFICER", "%" + OFFICER + "%");
strQuery.Parameters.AddWithValue("@CUSTVENDCODE", "%" + CUSTVENDCODE + "%");
strQuery.Parameters.AddWithValue("@APPROVE", "%" + APPROVE + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblcosting");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
public static Barang FindOneById(int id)
{
Database.OpenConnection();
string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id");
Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter();
da.SelectCommand = new Sql.MySqlCommand(select, Database.conn);
da.SelectCommand.Parameters.AddWithValue("@id", id);
Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Database.CloseConnection();
Barang barang = new Barang();
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
barang.setId(Convert.ToInt32(dr["id"]));
barang.setNama(dr["nama"].ToString());
barang.setKode(dr["kode"].ToString());
barang.setJumlah(Convert.ToInt32(dr["jumlah"]));
barang.setHargaHpp(Convert.ToDecimal(dr["harga_hpp"]));
barang.setHargaJual(Convert.ToDecimal(dr["harga_jual"]));
barang.setCreatedAt(Convert.ToDateTime(dr["created_at"]));
barang.setUpdatedAt(Convert.ToDateTime(dr["updated_at"]));
return(barang);
}
return(null);
}
public DataSet getData(string GINNO)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT *" +
" FROM tblgindetail WHERE GINNO LIKE @GINNO";
strQuery.Parameters.AddWithValue("@GINNO", "%" + GINNO + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblgindetail");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
public DataSet getData(string CustVendCode, string Name, string Type)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
//strQuery.CommandText = "SELECT CUSTVENDCODE,NAME,TYPE,ACTIVE from tblmcustvend WHERE CUSTVENDCODE LIKE @CustVendCode AND NAME LIKE @Name AND TYPE LIKE @Type";
strQuery.CommandText = "SELECT * from tblmcustvend WHERE CUSTVENDCODE LIKE @CUSTVENDCODE AND NAME LIKE @NAME AND TYPE LIKE @TYPE ";
strQuery.Parameters.AddWithValue("@CUSTVENDCODE", "%" + CustVendCode + "%");
strQuery.Parameters.AddWithValue("@NAME", "%" + Name + "%");
strQuery.Parameters.AddWithValue("@TYPE", "%" + Type + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmcustvend");
Conn.Putus();
}
catch
{
}
return(ds);
}
private void FatuSemana_Load(object sender, EventArgs e)
{
this.label4.DataBindings.Clear();
dataSistema = DateTime.Now.ToShortDateString();
string data="",sDataF="";
data = dataSistema;
DateTime d = Convert.ToDateTime(data);
sDataF = d.ToString("yyyyMMdd");
try
{
MySqlConnection con = new MySqlConnection("SERVER=localhost;" + " DATABASE=banco_rr_sacoles;" + " UID=root;" + "PASSWORD=12345;");
MySqlDataAdapter sql = new MySqlDataAdapter("SELECT SUM( total ) AS total FROM faturamento WHERE data BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and '" + sDataF + "'", con);
//SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);
DataTable dt = new DataTable();
sql.Fill(dt);
BindingSource source = new BindingSource();
source.DataSource = dt;
this.label4.DataBindings.Add("Text", source, "total", true);
string total = label4.Text;
Decimal tt = Convert.ToDecimal(total);
String tot = tt.ToString("N", new CultureInfo("pt-BR"));
tbxFaturamento.Text = ("R$ " + (String.Format("{0:0.00}",tt))).ToString();
con.Close();
}
catch (Exception ex)
{
ex.Message.ToString();
//throw new Exception("Erro de comandos: " + ex.Message);
}
}
public System.Data.DataTable GetData(Query DataQuery)
{
try
{
Connection.Open();
dt = new System.Data.DataTable();
cmd = new MySqlCommand();
cmd.CommandText = DataQuery.toString();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = Connection;
using (da = new MySqlDataAdapter(cmd))
{
da.Fill(dt);
}
Connection.Close();
}
catch (Exception ex)
{
throw new Exception("Unable to get data", ex.InnerException);
}
finally {
Connection.Close();
cmd.Dispose();
}
return(dt);
}
public DataSet getData(string TermOfPayment, string TermType, int Days)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT TERMOFPAYMENT,DAYS,TERMTYPE,ACTIVE FROM tblmtermofpayment WHERE TERMOFPAYMENT LIKE @TermOfPayment AND Days LIKE @Days AND TermType LIKE @TermType";
strQuery.Parameters.AddWithValue("@TermOfPayment", "%" + TermOfPayment + "%");
strQuery.Parameters.AddWithValue("@Days", "%" + Days + "%");
strQuery.Parameters.AddWithValue("@TermType", "%" + TermType + "%");
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmtermofpayment");
Conn.Putus();
}
catch
{
}
return(ds);
}
private void WypelnijGridView()
{
txtDruzyna.Text = "";
txtImie.Text = "";
txtNazwisko.Text = "";
txtData.Text = "";
txtPozycja.Text = "";
txtWaga.Text = "";
txtWzrost.Text = "";
txtNumer.Text = "";
string constr = ConfigurationManager.ConnectionStrings["pol"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM pilkarze"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
public void process(ServiceRequest request, ServiceResponse response)
{
List<Category> list = new List<Category>();
string sqlStr = "select * from category";
MySqlConnection conn = ConnectionManager.getInstance().getConnection();
conn.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(sqlStr, conn);
DataSet ds = new DataSet();
mda.Fill(ds,"table1");
conn.Close();
int count = ds.Tables["table1"].Rows.Count;
for (int i = 0; i < count; i++)
{
Category c = new Category();
c.categoryId = (int)ds.Tables["table1"].Rows[i][0];
c.categoryName = (string)ds.Tables["table1"].Rows[i][1];
list.Add(c);
}
GetCategoryResponse serviceResponse = new GetCategoryResponse();
serviceResponse.categories = list;
response.responseObj = serviceResponse;
response.returnCode = 0;
}
public void fill_dg1()
{
try
{
_connection.Open();
_mySqlCommand.CommandText = @"SELECT
operators.Id_Operator AS 'ID',
operators.Surname AS 'ФИО',
operators.LevelMD AS 'Уровень MD',
operators.LevelUSD AS 'Уровень USD'
FROM
operators
WHERE operators.active = 1
";
_mySqlCommand.Connection = _connection.MySqlConnection;
var dataAdapter = new MySqlDataAdapter(_mySqlCommand.CommandText, _connection.MySqlConnection);
var dset = new DataSet();
dataAdapter.Fill(dset);
dg1.ItemsSource = dset.Tables[0].DefaultView;
_connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void fillgridDef()
{
id = -1;
buttonDel.Visible = false;
buttonEdit.Visible = false;
buttonIns.Visible = false;
buttonCancel.Visible = false;
DBConnect NewcConnection = new DBConnect();
NewcConnection.dbConnection();
MySqlCommand querysql = new MySqlCommand("Select * From deficiencias", DBConnect.db);
try
{
MySqlDataAdapter dados = new MySqlDataAdapter();
dados.SelectCommand = querysql;
DataTable tabela = new DataTable();
dados.Fill(tabela);
BindingSource fonte = new BindingSource();
fonte.DataSource = tabela;
dataGridView1.DataSource = fonte;
this.dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Deficiencia";
dados.Update(tabela);
dataGridView1.AutoResizeColumns();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
DBConnect.db.Close();
textBoxDef.Clear();
}
// display all posted topics under the selected forum
protected void Load_Topics()
{
try
{
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM forum_discussions WHERE forum_name = " + "'" + txtForumName.Text + "'";
//cmd.CommandText = "SELECT * FROM forum_discussions where surname like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or firstname like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or customer_id like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or group_status like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or email like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or institution like " + "'" + txtSearch.Text + "%' and group_name in ('clients') ";
adap = new MySqlDataAdapter(cmd);
ds1 = new DataSet();
adap.Fill(ds1, "forum");
grdTopics.DataSource = ds1.Tables[0];
grdTopics.DataBind();
lblError.Visible = false;
}
catch (Exception err)
{
lblError.Visible = true;
lblError.Text = "Error: " + err.Message;
}
con.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed) con.Open();
DataTable dt = new DataTable();
if (ddlGuestType.Text == "De-Activate")
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels Where Status='De-Activate' ", con);
}
else if (ddlGuestType.Text == "Activate")
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels Where Status='Activate' ", con);
}
else
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels ", con);
}
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
ExportToExcel(dt, "Hotels", ddlGuestType.Text + " Hotel" );
if (con.State == ConnectionState.Open) con.Close();
}
// GET: Test
public ActionResult Index()
{
MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=classicmodels; password=root");
con.Open();
MySqlCommand query = new MySqlCommand("SELECT contactFirstName, contactLastName FROM customers", con);
MySqlDataAdapter adp = new MySqlDataAdapter(query);
DataSet ds = new DataSet();
adp.Fill(ds);
List<String> list = ds.Tables[0].AsEnumerable()
.Select(r => r.Field<String>("contactFirstName"))
.ToList();
ViewBag.List = list;
//List<String> test = new List<String>();
//test.Add("een");
//test.Add("twee");
//test.Add("drie");
//ViewBag.List = test;
return View();
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
DataSet result = null;
using (MySqlConnection conn = GetConnection)
{
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
result = new DataSet();
adapter.Fill(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
public void filldatagrid()
{
string str = sqlcon;
MySqlConnection con = new MySqlConnection(str);
string com = "Select id,name,surname,username,gold,military,political,diplomatic,trade,rev_sum,cost_sum from player where username='******'" ;
MySqlDataAdapter adpt = new MySqlDataAdapter(com, con);
DataSet myDataSet = new DataSet();
adpt.Fill(myDataSet, "player");
DataTable myDataTable = myDataSet.Tables[0];
//DataRow tempRow = null;
DataRow tempRow1 = null;
foreach (DataRow tempRow1_Variable in myDataTable.Rows)
{
tempRow1 = tempRow1_Variable;
label1.Text = Convert.ToString(tempRow1["name"] + " " + tempRow1["surname"]);
}
dataGridView1.DataSource = myDataSet;
dataGridView1.DataMember = "player";
dataGridView1.Columns[0].HeaderText = "Κωδικός Παίκτη";
dataGridView1.Columns[1].HeaderText = "Όνομα Παίκτη";
dataGridView1.Columns[2].HeaderText = "Επώνυμο Παίκτη";
dataGridView1.Columns[3].HeaderText = "Username";
dataGridView1.Columns[4].HeaderText = "Χρύσος";
dataGridView1.Columns[5].HeaderText = "Στρατιωτική Ικανότητα";
dataGridView1.Columns[6].HeaderText = "Πολιτική Ικανότητα";
dataGridView1.Columns[7].HeaderText = "Διπλωματική Ικανότητα";
dataGridView1.Columns[8].HeaderText = "Εμπορική Ικανότητα";
dataGridView1.Columns[9].HeaderText = "Έσοδα";
dataGridView1.Columns[10].HeaderText = "Έξοδα";
}
public Form4()
{
InitializeComponent();
try
{
MySqlConnection con = new MySqlConnection(ruta);
con.Open();
string consulta = "select * from pc_escritorio";
MySqlDataAdapter da = new MySqlDataAdapter(consulta, con);
//lleno
DataSet ds = new DataSet();
da.Fill(ds, "pc_escritorio");
//datos al datagriew
dataGridView1.DataSource = ds.Tables[0];
con.Close();
}
catch (Exception ee)
{
MessageBox.Show("Se produjo un error debido a : " + ee.ToString());
}
}
public DataSet getData(string COSTINGNO, int ID)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT * " +
" FROM tblbom WHERE COSTINGNO=@COSTINGNO AND ID=@ID";
strQuery.Parameters.AddWithValue("@COSTINGNO", COSTINGNO);
strQuery.Parameters.AddWithValue("@ID", ID);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblbom");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
public void SimpleJoinWithPredicate()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT b.id,b.name,a.name as author_name from books b JOIN
authors a ON b.author_id=a.id WHERE b.pages > 300", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
var q = from b in context.Books
join a in context.Authors
on b.Author.Id equals a.Id
where b.Pages > 300
select new
{
bookId = b.Id,
bookName = b.Name,
authorName = a.Name
};
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleJoinWithPredicate);
int i = 0;
foreach (var o in q)
Assert.AreEqual(dt.Rows[i++][0], o.bookId);
Assert.AreEqual(dt.Rows.Count, i);
}
}
public DataSet getData(string ItemsTypeID, string Description, string CategoryID)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT ITEMSTYPEID,DESCRIPTION,ACTIVE,CATEGORYID FROM tblmitemstype WHERE CATEGORYID LIKE @CategoryID AND DESCRIPTION LIKE @Description AND CATEGORYID LIKE @CategoryID ";
strQuery.Parameters.AddWithValue("@ItemsTypeID", "%" + ItemsTypeID + "%");
strQuery.Parameters.AddWithValue("@Description", "%" + Description + "%");
strQuery.Parameters.AddWithValue("@CategoryID", "%" + CategoryID + "%");
//strQuery.Parameters.AddWithValue("@CategoryID", item);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblmitemstype");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
/// <summary>
/// Method in charge of all queries to database
/// </summary>
/// <param name="sql"></param>
/// <returns>DataTable</returns>
public DataTable query(string sql)
{
da = new MySqlDataAdapter(sql, cn);
dt = new DataTable();
da.Fill(dt);
return dt;
}
public static object database_conn(string sqlstring)
{
conn = new MySqlConnection();
conn.ConnectionString = "server=" + Properties.Settings.Default.host + "; port=" + Properties.Settings.Default.port + "; user id=" + Properties.Settings.Default.username + "; password="******"; database=" + Properties.Settings.Default.database;
try
{
conn.Open();
MySqlCommand sql = new MySqlCommand(sqlstring, conn);
DataSet ds = new DataSet();
MySqlDataAdapter DataAdapter = new MySqlDataAdapter();
DataAdapter.SelectCommand = sql;
DataAdapter.Fill(ds, "table1");
return ds;
}
catch (MySqlException myerror)
{
MessageBox.Show("Error Connecting to Database: " + myerror.Message, "Database Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return "";
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void GenerarReporte()
{
// Defino variables y sentencias a ejecutar
string mySqlStatement = "SELECT id_campo, desc_campo, areacampo, areasembrada, unidad, "+
"lineas, plantas, aspersores, mangueras FROM campos";
// Defino el DataSet
dsCampos myDsCampos = new dsCampos();
try
{
// Conexion
MySqlConnection myConexion = new MySqlConnection(Conexion.ConectionString);
// Creo los Data Adapters
MySqlDataAdapter myDACampos = new MySqlDataAdapter(mySqlStatement, myConexion);
// Llenando las tablas de Dataset Tipados
myDACampos.Fill(myDsCampos, "dtCampos");
// Generamos el Reporte
rptCampos informe = new rptCampos();
informe.SetDataSource(myDsCampos);
crViewer.ReportSource = informe;
}
catch (Exception myEx)
{
MessageBox.Show(myEx.Message);
}
}
public void getData(string query)
{
this.dt = new DataTable();
try
{
string constr = ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString;
MySqlConnection con = new MySqlConnection();
con.ConnectionString = constr;
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
if (query.Equals("getStaffDataWithDailyHours"))
{
System.DateTime currentDate = System.DateTime.Now;
cmd.Parameters.AddWithValue("@currentDate", System.Convert.ToDateTime(currentDate).ToString("yyyy-MM-dd"));
}
sda.Fill(dt);
cmd.ExecuteNonQuery();
con.Close();
}
catch
{
System.Diagnostics.Debug.WriteLine("fail !");
}
}
public void efetuarLogin()
{
MySqlConnection con = new MySqlConnection(Form1.conexao);
con.Open();
MySqlCommand comando = new MySqlCommand("Select id,fcNomeAcesso,fcSenha,fcLoja from ubfuncionario where fcNomeAcesso =?Nome and fcSenha =?Senha and fcLoja=?Loja");
comando.Connection = con;
MySqlParameter Parametros = new MySqlParameter();
comando.Parameters.AddWithValue("?Senha", txtSenha.Text);
comando.Parameters.AddWithValue("?Loja", cbLojas.SelectedValue);
comando.Parameters.AddWithValue("?Nome", txtUsuario.Text);
MySqlDataAdapter daLogin = new MySqlDataAdapter(comando);
DataTable dtLogin = new DataTable();
daLogin.Fill(dtLogin);
try
{
idLoja = Int32.Parse(dtLogin.Rows[0][3].ToString());
idUsuario = Int32.Parse(dtLogin.Rows[0][0].ToString());
lblStatus.Text = "LOGIN EFETUADO!";
lblStatus.ForeColor = System.Drawing.Color.Green;
for (int i = 231; i <= 483; i += 50)
{
this.Width = i;
}
}
catch
{
lblStatus.Text ="USUÁRIO INEXISTENTE!";
}
}
public DataTable GetDataTable(
ref MySql.Data.MySqlClient.MySqlConnection _SqlConnection,
string _SQL)
{
// Pass the connection to a command object
MySql.Data.MySqlClient.MySqlCommand _SqlCommand =
new MySql.Data.MySqlClient.MySqlCommand(_SQL, _SqlConnection);
MySql.Data.MySqlClient.MySqlDataAdapter _SqlDataAdapter
= new MySql.Data.MySqlClient.MySqlDataAdapter();
_SqlDataAdapter.SelectCommand = _SqlCommand;
DataTable _DataTable = new DataTable();
_DataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
// Adds or refreshes rows in the DataSet to match those in the data source
try
{
_SqlDataAdapter.Fill(_DataTable);
}
catch (Exception _Exception)
{
// Error occurred while trying to execute reader
// send error message to console (change below line to customize error handling)
// Console.WriteLine(_Exception.Message);
//MessageBox(_Exception);
return(null);
}
return(_DataTable);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
button5.Enabled = true;
selectedItem = listBox1.SelectedItem.ToString();
querry = "SELECT * FROM " + listBox1.SelectedItem.ToString();
connection = new MySqlConnection(connectionString);
dataAdapter = new MySqlDataAdapter(querry, connectionString);
dt = new DataTable();
dataAdapter.Fill(dt);
if (dataGridView1.DataSource == null)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
}
dataGridView1.DataSource = dt;
}
catch (NullReferenceException ex)
{
MessageBox.Show("Please select one of the databases in the list.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show("An error occured!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public Decimal GET_AMOUNT(Decimal _basic_salary)
{
decimal d = 0;
MySqlCommand cmd = new MySqlCommand();
db.SET_COMMAND_PARAMS(cmd, "BENEFIT_SELECT_AMOUNT_BYID");
cmd.Parameters.AddWithValue("_code", code);
cmd.Parameters.AddWithValue("_basic_salary", _basic_salary);
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
DataRow r = dt.Rows[0];
d = Convert.ToDecimal(r["amount"].ToString());
return d;
}
else
{
return 0;
}
}
else {
return 0;
}
}
public void fill_dg10()
{
try
{
_connection.Open();
_mySqlCommand.CommandText = @"SELECT
sensors.Id_Sensor AS 'ID',
sensors.NameSensor AS 'Название датчика'
FROM
sensors
WHERE sensors.active = 1
";
_mySqlCommand.Connection = _connection.MySqlConnection;
var dataAdapter = new MySqlDataAdapter(_mySqlCommand.CommandText, _connection.MySqlConnection);
var dset = new DataSet();
dataAdapter.Fill(dset);
dg10.ItemsSource = dset.Tables[0].DefaultView;
_connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
protected void DeleteData()
{
if (con.State == ConnectionState.Closed) con.Open();
this.cmd = new MySqlCommand("Select * from Packagephotos where PackageKey='" + PackageKey + "' And PackagePhotoKey='" + PackagePhotoKey + "' ", this.con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
string ImageName = dt.Rows[0]["ImageName"].ToString();
try
{
if (File.Exists(Server.MapPath("../Packages/" + ImageName)) == true)
{
File.Delete(Server.MapPath("../Packages/" + ImageName));
}
}
catch (Exception ex)
{
}
}
this.cmd = new MySqlCommand("Delete from Packagephotos where PackagePhotoKey='" + PackagePhotoKey + "' ", this.con);
this.cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open) con.Close(); ;
}
public static DataTable showAllStudent(out string error)
{
try
{
using (MySqlConnection connection = new ConnectionManager().GetDatabaseConnection())
{
using (MySqlCommand command = new MySqlCommand("sp_ShowAllStudent", connection))
{
command.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet dSet = new DataSet();
connection.Open();
adapter.SelectCommand = command;
adapter.Fill(dSet);
connection.Close();
DataTable dt = dSet.Tables[0];
error = null;
return dt;
}
}
}
catch (Exception ex)
{
error = ex.Message;
return null;
}
}
private void btn_ver_Click(object sender, EventArgs e)
{
if (cbExport.SelectedItem == "articulos")
{
MySqlCommand cmdDataBase = new MySqlCommand("select * from articulos ;", bd.cnn);
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = cmdDataBase;
DataTable dbdataset = new DataTable();
sda.Fill(dbdataset);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbdataset;
dataGridView1.DataSource = bSource;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Seleccione el Item", "", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
public EditAlat(Alat alat)
{
InitializeComponent();
this.alat = alat;
status = new List<KV>();
status.Add(new KV("Baik", 1));
status.Add(new KV("Rusak", 0));
comboBox_Status.ItemsSource = status;
comboBox_Status.DisplayMemberPath = "Key";
comboBox_Status.SelectedValuePath = "Value";
comboBox_Status.SelectedValue = (alat.KondisiAlat) ? 1 : 0;
textbox_Laboratorium.Text = alat.Lokasi;
string query = "SELECT * FROM master_inventory_type";
using (MySqlCommand cmd = new MySqlCommand(query, db.ConnectionManager.Connection)) {
try {
DataTable dataSet = new DataTable();
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd)) {
dataAdapter.Fill(dataSet);
comboBox_Jenis_Barang.ItemsSource = dataSet.DefaultView;
comboBox_Jenis_Barang.DisplayMemberPath = dataSet.Columns["nama"].ToString();
comboBox_Jenis_Barang.SelectedValuePath = dataSet.Columns["id"].ToString();
comboBox_Jenis_Barang.SelectedValue = alat.IdJenis;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex) {
MessageBox.Show(ex.Message);
}
}
}
public String getUniqueName()
{
String unique_name;
unique_name = "-1";
MySqlConnection con = new MySqlConnection();
con.ConnectionString = MySQLDatabase.getConnectionString();
DataTable dt = new DataTable();
MySqlDataAdapter adpt = new MySqlDataAdapter("SELECT name FROM scientific_names where used = 0", con);
adpt.Fill(dt);
if (dt.Rows.Count != 0)
{
unique_name = dt.Rows[0]["name"].ToString();
con.Close();
con = new MySqlConnection();
con.ConnectionString = MySQLDatabase.getConnectionString();
con.Open();
String commandText =" UPDATE scientific_names SET used = 1 WHERE ( name = '" + unique_name + "')";
MySqlCommand comm = new MySqlCommand(commandText, con);
comm.ExecuteNonQuery();
con.Close();
}
return unique_name;
}
private void CrystalReportViewer1_Loaded(object sender, RoutedEventArgs e)
{
ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
conn.ConnectionString = Properties.Settings.Default.abab1199_qsyncConnectionString;
try
{
cmd.CommandText = "SELECT * FROM quotes WHERE QuoteNumber = " + qnpass.Text;
cmd.Connection = conn;
myAdapter.SelectCommand = cmd;
myAdapter.Fill(myData);
myReport.Load(reportLocation);
myReport.Database.Tables[0].SetDataSource(myData.Tables[0]);
myReport.SetParameterValue("Quote", qnpass.Text);
myReport.SetParameterValue("Quote_Number", qnpass.Text);
CrystalReportViewer1.ViewerCore.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message, "Database Connectivity Error!.",
MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public static DataView affichgrid(DataGridView dgv)
{
string ConnectionString = "SERVER=localhost;" + "DATABASE=gestioncommerciale;" + "UID=root;" + "PASSWORD=freedomity;";
MySqlConnection _Conn = DAL.Connexion.connect();
_Conn.Open();
DataTable dt = new DataTable();
string strSql = "SELECT * FROM client";
MySqlCommand cmd = new MySqlCommand(strSql, _Conn);
//OleDbDataAdapter joue le rôle de pont entre DataSet et une source de données pour la récupération et l'enregistrement de données.
MySqlDataAdapter da = new MySqlDataAdapter(strSql, ConnectionString);
// un cache en mémoire des données récupérées d'une source de données,
DataSet ds = new DataSet();
da.Fill(dt);
da.Fill(ds, "client");
dgv.DataSource = dt.DefaultView;
return (ds.Tables["client"].DefaultView);
}