private void FrmCheckProduct_Load(object sender, EventArgs e)
{
string select = @"SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'GROUP_CONCAT((CASE Storeid when ',
Storeid,
' then quantity else NULL END)) AS ',
Storename
)
) INTO @sql
FROM ProductInStock;
SET @sql = CONCAT('SELECT Productid, ProductName, ', @sql, '
FROM ProductInStock
GROUP BY ProductId, ProductName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";
MySql.Data.MySqlClient.MySqlConnection cnn = DBUtility.getConnection();
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(select, cnn); //c.con is the connection string
MySql.Data.MySqlClient.MySqlCommandBuilder commandBuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = ds.Tables[0];
}
public static DataTable Query(string sql)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString()))
{
conn.Open();
var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, conn);
var ds = new DataSet();
var reader = adapter.Fill(ds);
conn.Close();
return ds.Tables[0];
}
}
protected void cmdrecuperar_Click(object sender, EventArgs e)
{
string correo = txtmail.Text;
try
{
Conexion conn = new Conexion();
conn.IniciarConexion();
string sql = "select log.password from log inner join user on log.id_user = user.id_user where user.email like '" + correo + "'";
string password = "";
MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql , conn.GetConexion);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable tabla = ds.Tables[0];
foreach (DataRow dr in tabla.Rows)
password = dr.Field<string>("password", DataRowVersion.Default);
conn.CerrarConexion();
adapter.Dispose();
ds.Dispose();
tabla.Dispose();
MailMessage mail = new MailMessage();
mail.To.Add(correo);
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "My pisarron 2 Recuperar contraseña ";
string Body = "Hola " + Environment.NewLine +
"su contraseña es <b>" + password + "</b>";
mail.Body = Body;
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.live.com";
smtp.Port = 587;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new System.Net.NetworkCredential
("*****@*****.**", "linux2012" );
smtp.EnableSsl = true;
smtp.Send(mail);
}
catch(Exception ex)
{
Log.Set_Log_Error(ex.Message, "Recuperar contraseña");
exito.InnerHtml = "<p><b>Ocurrio un error al momento de procesar su solicitud</b></p>";
}
exito.InnerHtml = "<p>Se le ha enviado un correo con su contraseña ... revisar <b>" + correo + "</b></p>";
}
/*Devolvemos un datatable con el resultado de la consulta que esta como paramatro en la bd cuyo string de conexion esta el parametro conexion del fichero de confi
guracon*/
public System.Data.DataTable pasar_consulta_datatable(string consulta)
{
MySql.Data.MySqlClient.MySqlConnection mscon = new MySql.Data.MySqlClient.MySqlConnection(Properties.Settings.Default.conexion);
MySql.Data.MySqlClient.MySqlDataAdapter mda = new MySql.Data.MySqlClient.MySqlDataAdapter(consulta, mscon);
mda.SelectCommand.CommandTimeout = 0;
System.Data.DataTable dt = new System.Data.DataTable();
mda.Fill(dt);
mscon.Close();
return dt;
}
public DataSet DataReport()
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_RapportView", conn);
cmd.CommandType = CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}catch(Exception ex) {
error = ex.Message;
}
return null;
}
public void prendiclienti()
{
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connessione);
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "Select * from cliente";
cmd.Connection = conn;
MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(clienti);
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
void ImgShow()
{
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString();
con = new MySql.Data.MySqlClient.MySqlConnection(conString);
con.Open();
queryStr = "";
queryStr = "SELECT imgID,imgName FROM guner_db.sliderimage";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, con);
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
public debtPaid(int summ)
{
InitializeComponent();
paidSumm = summ;
tbxSum.Text = summ.ToString();
//Вывод в ComboBox имен агентов
string cmd = "SELECT * FROM sklad.agents";
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(tposDesktop.Properties.Settings.Default.testConnectionString))
{
MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand(cmd, conn);
DataTable dt = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(com);
da.Fill(dt);
cmbNameAg.DataSource = dt;
cmbNameAg.DisplayMember = "Name";
cmbNameAg.ValueMember = "ID";
//cmbNameAg.SelectedIndex = -1;
}
}
private void CrystalReportViewer1_Loaded(object sender, RoutedEventArgs e)
{
ReportDocument rd = 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 = "server=abledoors.net.au;uid = abab1199_qadacc;" +
"pwd=@mjK%U5eyNOK;database=abab1199_qsync;convert zero datetime=True;persistsecurityinfo=True";
try
{
cmd.CommandText = "SELECT quotes.QuoteNumber, quotes.Company, quotes.Address, quotes.ContactPerson, " +
"quotes.Phone, quotes.Mobile, quotes.Email, quotes.StrataNo, quotes.RefNo, quotes.JobLocation, " +
"quotes.SiteContact, quotes.SCPhone, quotes.SCMobile, quotes.SCEmail, quotes.Representative FROM quotes;" +
"SELECT quoteitems.QNL, quoteitems.ItemDescription FROM quoteitems";
cmd.Connection = conn;
myAdapter.SelectCommand = cmd;
myAdapter.Fill(myData);
rd.Load("E:/OneDrive - Your IT Link/Your IT Link/Development/QMS 2018/QSync V4/VS 2019/QSync/QSync/QSync/test/Quote.rpt");
// myReport.Load("E:/OneDrive - Your IT Link/Your IT Link/Development/QMS 2018/QSync V4/VS 2019/QSync/QSync/QSync/test/Quote.rpt");
rd.Database.Tables[0].SetDataSource(myData.Tables[0]);
rd.Database.Tables[1].SetDataSource(myData.Tables[1]);
// myViewer.ReportSource = myReport;
rd.SetParameterValue("Quote", qnpass.Text);
rd.SetParameterValue("Quote_Number", qnpass.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params MySql.Data.MySqlClient.MySqlParameter[] cmdParms)
{
using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
return(ds);
}
}
}
//public string AddField(ref DataTable dtTarget, string name, int index)
//{
// int columnIndex = dtTarget.Columns.IndexOf(name);
// if (columnIndex < 0)
// {
// return $"Column '{name}' was not found in the target table.";
// }
// else
// {
// FieldMapping.Add(index, new CsvFieldMapping(name, index, columnIndex));
// return "";
// }
//}
public string GetTargetTableStructure(string tableName, MySql.Data.MySqlClient.MySqlConnection connection, ref DataTable dtTarget)
{
string errorMessage = "";
System.Console.WriteLine($"Getting the table structure of: '{tableName}'");
try
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand($"DELETE FROM {tableName}", connection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
cmd.Dispose();
using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter($"SELECT * FROM {tableName} WHERE 1=2", connection))
{
adapter.Fill(dtTarget);
}
}
catch (System.Exception e)
{
errorMessage = e.Message;
}
return errorMessage;
}
private void LoadReceipt(string No)
{
reportViewer1.LocalReport.DataSources.Clear();
Certificate v = new Certificate();
Customer c = new Customer();
Company y = new Company();
v = Certificate.Select(No);
y = Company.Select();
/**Company Customer***/
//Microsoft.Reporting.WinForms.ReportParameter rp = new Microsoft.Reporting.WinForms.ReportParameter("image", y.Image);
//this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp });
MySQL.Close();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM certificate WHERE no='" + No + "'", MySQL.Conn);
DataSet ds = new DataSet();
da.Fill(ds);
MySql.Data.MySqlClient.MySqlDataAdapter da2 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT product.name AS itemID ,product.code AS self,product.description AS tax,casetransaction.cost,casetransaction.date as date,casetransaction.no as no,casetransaction.total as total,casetransaction.qty as qty,casetransaction.cost,casetransaction.created,casetransaction.sync,casetransaction.height,casetransaction.limits,casetransaction.weight,casetransaction.setting,casetransaction.instruction,casetransaction.period FROM casetransaction LEFT join product ON casetransaction.itemID = product.id WHERE casetransaction.no='" + No + "'", MySQL.Conn);
DataSet ds2 = new DataSet();
da2.Fill(ds2);
MySql.Data.MySqlClient.MySqlDataAdapter da3 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM icd10 WHERE no='" + No + "'", MySQL.Conn);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
ReportDataSource datasource2 = new ReportDataSource("DataSet2", ds2.Tables[0]);
ReportDataSource datasource3 = new ReportDataSource("DataSet3", ds3.Tables[0]);
reportViewer1.LocalReport.DataSources.Add(datasource);
reportViewer1.LocalReport.DataSources.Add(datasource2);
reportViewer1.LocalReport.DataSources.Add(datasource3);
reportViewer1.RefreshReport();
}
public static System.Net.IPEndPoint GetGPSGatewayRouter(string gpsCode)
{
MySql.Data.MySqlClient.MySqlConnection gpsDBCon = null;
try
{
gpsDBCon = new MySql.Data.MySqlClient.MySqlConnection();
gpsDBCon.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SelpConnectionStr"].ToString();
gpsDBCon.Open();
System.Net.IPEndPoint g = null;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "select gps_cachetable.value from gps_cachetable where gps_cachetable.key='gw" + gpsCode + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = gpsDBCon;
System.Data.DataSet ds = new DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
gpsDBCon.Close();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
string config = dr[0].ToString().Trim();
string[] ss = config.Split(":".ToCharArray());
g = new System.Net.IPEndPoint(System.Net.IPAddress.Parse(ss[0].Trim()), int.Parse(ss[1].Trim()));
}
return g;
}
catch (System.Exception ex)
{
PES.Beehive.Logging.Logger.Error("获取" + gpsCode + "网关配置信息失败", null);
throw ex;
}
finally
{
if (gpsDBCon != null)
gpsDBCon.Close();
}
}
protected override DataSet GetDataSet(string sql, object[] param = null)
{
var com = new MySql.Data.MySqlClient.MySqlCommand(sql, _conn)
{
CommandType = CommandType.Text,
};
if (param != null && param is MySql.Data.MySqlClient.MySqlParameter[])
{
com.Parameters.AddRange((MySql.Data.MySqlClient.MySqlParameter[])param);
}
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
}
DataSet ds = new DataSet();
using (var da = new MySql.Data.MySqlClient.MySqlDataAdapter(com))
{
da.Fill(ds);
}
return(ds);
}
public static DataSet Query(string connString, string sQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, sQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
return(ds);
}
}
}
public static string getvalue(string SQLString)
{
using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
string str = "";
DataSet ds = new DataSet();
try
{
connection.Open();
MySql.Data.MySqlClient.MySqlDataAdapter command = new MySql.Data.MySqlClient.MySqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
if (ds.Tables[0].Rows.Count > 0)
{
str = ds.Tables[0].Rows[0][0].ToString();
}
return(str);
}
}
public DataTable filterTravelRecords(String from, String to, Boolean isReleased)
{
using (MySql.Data.MySqlClient.MySqlConnection con = Database.getConnection())
{
try
{
DataTable table = new DataTable();
String sql = null;
if (isReleased)
{
sql = "SELECT tbltravelinfo.travelinfo_id,tblholders.holder_name AS Employee,tblbags.bag_name AS Bag,tbltravelinfo.travel_location AS Destination,tbltravelinfo.from_date AS DateReleased,tbltravelinfo.to_date AS DateReturned,tbltravelinfo.isdone FROM ((tbltravelinfo INNER JOIN tblholders ON tbltravelinfo.holder_id = tblholders.holder_id) INNER JOIN tblbags ON tbltravelinfo.bag_id = tblbags.bag_id) WHERE (tbltravelinfo.from_date >= @from AND tbltravelinfo.from_date <= @to) ORDER BY tbltravelinfo.travelinfo_id DESC;";
}
else
{
sql = "SELECT tbltravelinfo.travelinfo_id,tblholders.holder_name AS Employee,tblbags.bag_name AS Bag,tbltravelinfo.travel_location AS Destination,tbltravelinfo.from_date AS DateReleased,tbltravelinfo.to_date AS DateReturned,tbltravelinfo.isdone FROM ((tbltravelinfo INNER JOIN tblholders ON tbltravelinfo.holder_id = tblholders.holder_id) INNER JOIN tblbags ON tbltravelinfo.bag_id = tblbags.bag_id) WHERE (tbltravelinfo.to_date >= @from AND tbltravelinfo.to_date <= @to) ORDER BY tbltravelinfo.travelinfo_id DESC;";
}
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sql, con);
command.Parameters.AddWithValue("@from", from);
command.Parameters.AddWithValue("@to", to);
MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(command);
adapter.Fill(table);
return(table);
}
catch (MySql.Data.MySqlClient.MySqlException exception)
{
System.Windows.Forms.MessageBox.Show(exception.Message);
return(null);
}
finally
{
con.Close();
}
}
}
/// <summary>
/// Copies the employee leave.
/// </summary>
public static void CopyEmployeeLeave()
{
DataTable dtEmployeeLeave = new DataTable();
var mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["OrangeHr"].ToString());
var mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM vw_empleaveForPMOscar", mySqlConnection);
mySqlConnection.Open();
var mySqlDataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(mySqlCommand);
mySqlDataAdapter.Fill(dtEmployeeLeave);
mySqlConnection.Close();
if (dtEmployeeLeave.Rows.Count > 0)
{
ExecuteNonQuery("DELETE FROM EmployeeLeave");
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ToString());
sqlConnection.Open();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection))
{
sqlBulkCopy.DestinationTableName = "EmployeeLeave";
sqlBulkCopy.WriteToServer(dtEmployeeLeave);
}
}
}
private System.Data.Common.DbDataReader SelectReaderQuery(string sqlquery)
{
//MySql.Data.MySqlClient.MySqlConnection.ClearPool(sqlConnection);
Stopwatch timer = new Stopwatch();
timer.Start();
queryCount++;
sqlquery = sqlquery.Replace("\\", "\\\\");
Connect();
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
dataAdapter.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand(sqlquery, sqlConnection);
System.Data.Common.DbDataReader resultReader = dataAdapter.SelectCommand.ExecuteReader();
dataAdapter.SelectCommand.Dispose();
dataAdapter.Dispose();
timer.Stop();
queryTime += timer.ElapsedTicks;
PushQuery(sqlquery, timer.ElapsedTicks / 10000000.0);
return resultReader;
}
private DataTable SelectQuery(string sqlquery)
{
//MySql.Data.MySqlClient.MySqlConnection.ClearPool(sqlConnection);
Stopwatch timer = new Stopwatch();
timer.Start();
queryCount++;
sqlquery = sqlquery.Replace("\\", "\\\\");
Connect();
DataTable resultTable = new DataTable();
try
{
PushQuery(sqlConnection.State.ToString(), 0.0);
//DataSet resultSet = new DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
dataAdapter.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand(sqlquery, sqlConnection);
long qt = timer.ElapsedTicks;
PushQuery("Query", qt / 10000000.0);
//dataAdapter.Fill(resultSet);
System.Data.Common.DbDataReader resultReader = dataAdapter.SelectCommand.ExecuteReader();
resultTable.Load(resultReader);
resultReader.Close();
resultReader.Dispose();
PushQuery("Fill", (timer.ElapsedTicks - qt) / 10000000.0);
dataAdapter.SelectCommand.Dispose();
dataAdapter.Dispose();
//resultTable = resultSet.Tables[0];
timer.Stop();
queryTime += qt;
PushQuery(sqlquery, timer.ElapsedTicks / 10000000.0);
return resultTable;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
PushQuery(sqlquery, 0.0);
throw new System.Exception(ex.ToString());
}
catch (System.Exception ex)
{
PushQuery(sqlquery, 0.0);
throw new System.Exception(ex.ToString());
}
}
//Finalmente, es el turno de definir CrearDataAdapter, el cual aprovecha el método Comando para crear el comando necesario.
protected override System.Data.IDataAdapter CrearDataAdapter(string procedimientoAlmacenado, params Object[] args)
{
var da = new MySql.Data.MySqlClient.MySqlDataAdapter((MySql.Data.MySqlClient.MySqlCommand)Comando(procedimientoAlmacenado));
if (args.Length != 0)
CargarParametros(da.SelectCommand, args);
return da;
}
private void BorderEventrs()
{
try {
CDatabaseCon DataConn = new CDatabaseCon();
MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
System.Data.DataTable table = new System.Data.DataTable();
string com = "SHOW TABLES;";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(com, DataConn.GetConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
labels.Add(new System.Windows.Forms.Label()
{
AutoSize = false,
Size = new System.Drawing.Size(250, 23),
Location = new System.Drawing.Point(0, i * 23),
TextAlign = System.Drawing.ContentAlignment.MiddleLeft,
BackColor = PBorderEvents.Drawing.BackColor,
ForeColor = PBorderEvents.Drawing.ForeColor,
Text = table.Rows[i].ItemArray[0].ToString()
});
}
foreach (System.Windows.Forms.Label label in labels)
{
ListTables.Controls.Add(label);
}
foreach (System.Windows.Forms.Label label in labels)
{
label.MouseEnter += (s, e) => {
try {
label.BackColor = System.Drawing.Color.FromArgb(0x33, 0x33, 0x34);
}
catch (System.Exception Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message); }
};
label.MouseLeave += (s, e) => {
try {
label.BackColor = PBorderEvents.Drawing.BackColor;
}
catch (System.Exception Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message); }
};
label.Click += (s, e) => {
try {
CDatabaseCon DataConn1 = new CDatabaseCon();
MySql.Data.MySqlClient.MySqlDataAdapter adapter1 = new MySql.Data.MySqlClient.MySqlDataAdapter();
System.Data.DataTable table1 = new System.Data.DataTable();
string com1 = $"SELECT * FROM {label.Text};";
MySql.Data.MySqlClient.MySqlCommand command1 = new MySql.Data.MySqlClient.MySqlCommand(com1, DataConn1.GetConnection());
adapter.SelectCommand = command1;
adapter.Fill(table1);
CDataTable.PDataBase.DataGridView.DataSource = table1;
CDataTable.PDataBase.DataGridView.Columns[0].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.None;
CDataTable.PDataBase.DataGridView.Columns[0].Resizable = System.Windows.Forms.DataGridViewTriState.False;
CDataTable.PDataBase.DataGridView.Columns[0].Width = 25;
foreach (System.Windows.Forms.DataGridViewColumn column in CDataTable.PDataBase.DataGridView.Columns)
{
column.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable;
}
}
catch (System.Exception Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message); }
};
}
}
}
catch (System.Exception Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message); }
}
private void LoadReceipt(string No)
{
reportViewer1.LocalReport.DataSources.Clear();
Invoice v = new Invoice();
Customer c = new Customer();
Company y = new Company();
v = Invoice.Select(No);
c = Customer.Select(v.CustomerID);
y = Company.Select();
/**Company Customer***/
if (v.Category.Contains("Purchase"))
{
Microsoft.Reporting.WinForms.ReportParameter rpImage = new Microsoft.Reporting.WinForms.ReportParameter("customerImage", Helper.CompanyImage);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpImage });
Microsoft.Reporting.WinForms.ReportParameter rpName = new Microsoft.Reporting.WinForms.ReportParameter("customerName", Helper.CompanyName);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpName });
Microsoft.Reporting.WinForms.ReportParameter rpAddress = new Microsoft.Reporting.WinForms.ReportParameter("customerAddress", Helper.CompanyAddress + " " + y.City + " " + y.State + " " + y.Zip);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpAddress });
Microsoft.Reporting.WinForms.ReportParameter rpContact = new Microsoft.Reporting.WinForms.ReportParameter("customerContact", Helper.CompanyContact);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpContact });
/**Company information***/
Microsoft.Reporting.WinForms.ReportParameter rp2Name = new Microsoft.Reporting.WinForms.ReportParameter("companyName", c.Name);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Name });
Microsoft.Reporting.WinForms.ReportParameter rp2Address = new Microsoft.Reporting.WinForms.ReportParameter("companyAddress", c.Address + " " + c.City + " " + c.State + " " + c.Zip);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Address });
Microsoft.Reporting.WinForms.ReportParameter rp2Contact = new Microsoft.Reporting.WinForms.ReportParameter("companyContact", c.Contact);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Contact });
Microsoft.Reporting.WinForms.ReportParameter rp = new Microsoft.Reporting.WinForms.ReportParameter("image", c.Image);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp });
}
if (v.Category.Contains("Sale"))
{
Microsoft.Reporting.WinForms.ReportParameter rp = new Microsoft.Reporting.WinForms.ReportParameter("image", Helper.CompanyImage);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp });
Microsoft.Reporting.WinForms.ReportParameter rpImage = new Microsoft.Reporting.WinForms.ReportParameter("customerImage", c.Image);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpImage });
Microsoft.Reporting.WinForms.ReportParameter rpName = new Microsoft.Reporting.WinForms.ReportParameter("customerName", c.Name);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpName });
Microsoft.Reporting.WinForms.ReportParameter rpAddress = new Microsoft.Reporting.WinForms.ReportParameter("customerAddress", c.Address + " " + c.City + " " + c.State + " " + c.Zip);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpAddress });
Microsoft.Reporting.WinForms.ReportParameter rpContact = new Microsoft.Reporting.WinForms.ReportParameter("customerContact", c.Contact);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rpContact });
/**Company information***/
Microsoft.Reporting.WinForms.ReportParameter rp2Name = new Microsoft.Reporting.WinForms.ReportParameter("companyName", y.Name);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Name });
Microsoft.Reporting.WinForms.ReportParameter rp2Address = new Microsoft.Reporting.WinForms.ReportParameter("companyAddress", Helper.CompanyAddress + " " + y.City + " " + " " + y.State + " " + y.Zip);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Address });
Microsoft.Reporting.WinForms.ReportParameter rp2Contact = new Microsoft.Reporting.WinForms.ReportParameter("companyContact", y.Contact);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp2Contact });
}
/**total tax balance**/
Microsoft.Reporting.WinForms.ReportParameter rp3Total = new Microsoft.Reporting.WinForms.ReportParameter("total", v.Total.ToString("n2"));
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp3Total });
Microsoft.Reporting.WinForms.ReportParameter rp3Tax = new Microsoft.Reporting.WinForms.ReportParameter("tax", v.Tax.ToString("n2"));
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp3Tax });
Microsoft.Reporting.WinForms.ReportParameter rp3Bal = new Microsoft.Reporting.WinForms.ReportParameter("balance", v.Balance.ToString("n2"));
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp3Bal });
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT product.name AS itemID ,casetransaction.cost,casetransaction.date as date,casetransaction.no as no,casetransaction.total as total,casetransaction.qty as qty,casetransaction.cost,casetransaction.created,casetransaction.sync FROM casetransaction LEFT join product ON casetransaction.itemID = product.id WHERE casetransaction.no='" + No + "'", MySQL.Conn);
DataSet ds = new DataSet();
da.Fill(ds);
MySql.Data.MySqlClient.MySqlDataAdapter da2 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM invoice WHERE no='" + No + "'", MySQL.Conn);
DataSet ds2 = new DataSet();
da2.Fill(ds2);
ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
ReportDataSource datasource2 = new ReportDataSource("DataSet2", ds2.Tables[0]);
reportViewer1.LocalReport.DataSources.Add(datasource);
reportViewer1.LocalReport.DataSources.Add(datasource2);
reportViewer1.RefreshReport();
}
/// <summary>
/// Donne les véhicules disponble à la location.
/// </summary>
/// <returns>The disponible.</returns>
public DataSet VehiculeDisponible()
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_VoitureDispoLocation", conn);
cmd.CommandType = CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}catch (Exception ex){
error = ex.Message;
}
return null;
}
public MySQLComDbContext()
{
this._connectionString = SystemUtils.Config.MySQLDbConStr;
this._DBAdapter = new SqlDataAdapter();
this._connection = new SqlConnection(this._connectionString);
}
public void InsertBatch <T>(string connection, List <T> models, int batch = 0) where T : class, IModel
{
using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
{
c.Open();
using (var transaction = c.BeginTransaction())
{
try
{
var map = _sql?.Configuration?.GetMap <T>();
if (map == null)
{
throw new Exception($"Class Map for:{typeof(T).Name} could not be found.");
}
var name = map.TableName;
var table = models.ToDataTable();
if (table.Rows.Count == 0)
{
return;
}
var builder = new StringBuilder();
builder.Append("SELECT TABLE_NAME");
builder.Append(", COLUMN_NAME");
builder.Append(", DATA_TYPE");
builder.Append(", CHARACTER_MAXIMUM_LENGTH");
builder.Append(", CHARACTER_OCTET_LENGTH");
builder.Append(", NUMERIC_PRECISION");
builder.Append(", NUMERIC_SCALE AS SCALE");
builder.Append(", COLUMN_DEFAULT");
builder.Append(", IS_NULLABLE");
builder.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
builder.Append(" WHERE TABLE_NAME = @Table");
var schema = new List <Schema>();
//get table schema (e.g. names and datatypes for mapping)
using (var command = new MySqlData.MySqlClient.MySqlCommand(builder.ToString(), c))
{
var parameter = new MySqlData.MySqlClient.MySqlParameter();
parameter.Value = map.TableName;
parameter.ParameterName = "@Table";
parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.String;
command.Parameters.Add(parameter);
using (var sql = new MySqlData.MySqlClient.MySqlDataAdapter(command))
{
var result = new DataTable();
var parameters = map.Properties
.Where(x => x.Ignored == false)
.Where(x => x.IsReadOnly == false)
.Where(x => x.KeyType == KeyType.NotAKey);
sql.Fill(result);
schema = (from p in parameters
join s in result.AsEnumerable() on p.ColumnName equals s.Field <string>("COLUMN_NAME")
select new Schema()
{
ColumnName = s.Field <string>("COLUMN_NAME"),
DataType = s.Field <string>("DATA_TYPE"),
Size = s.Field <object>("CHARACTER_OCTET_LENGTH")
}).ToList();
}
}
using (var command = new MySqlData.MySqlClient.MySqlCommand($"INSERT INTO {map.TableName} ({string.Join(",", schema.Select(x => x.ColumnName))}) VALUES ({string.Join(",", schema.Select(x => $"@{x.ColumnName}"))});", c))
{
command.UpdatedRowSource = UpdateRowSource.None;
foreach (var type in schema)
{
var parameter = new MySqlData.MySqlClient.MySqlParameter();
parameter.ParameterName = $"@{type.ColumnName}";
parameter.SourceColumn = type.ColumnName;
switch (type.DataType.ToLower())
{
case "varchar":
case "char":
case "text":
parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.String;
parameter.Size = Int32.Parse(type.Size.ToString());
break;
case "datetime":
parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.DateTime;
break;
case "int":
parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.Int32;
break;
default:
throw new NotImplementedException();
}
command.Parameters.Add(parameter);
}
using (var adapter = new MySqlData.MySqlClient.MySqlDataAdapter())
{
adapter.InsertCommand = command;
var timer = Stopwatch.StartNew();
_log.LogTrace <MySqlQuery>($"Bulk Insert on {name}. {models.Count} rows queued for insert.");
timer.Start();
if (batch > 0)
{
adapter.UpdateBatchSize = 100;
}
adapter.Update(table);
transaction.Commit();
_log.LogTrace <MySqlQuery>($"Bulk Insert on {name} complete in: {timer.Elapsed.ToString(@"hh\:mm\:ss\:fff")}");
}
}
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
c.Close();
}
}
}
}
/// <summary>
/// Выполняет запрос выборки набора строк.
/// </summary>
/// <param name="sql">Текст запроса к базе данных</param>
/// <param name="connection">Строка подключения к базе данных</param>
/// <returns>Возвращает набор строк в DataSet.</returns>
public static MyResultData SqlReturnDataset(string sql, string connection)
{
MyResultData result = new MyResultData();
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
{
MySql.Data.MySqlClient.MySqlDataAdapter AdapterP = new MySql.Data.MySqlClient.MySqlDataAdapter();
AdapterP.SelectCommand = commRC;
DataSet ds1 = new DataSet();
AdapterP.Fill(ds1);
result.ResultData = ds1.Tables[0];
}
catch (Exception ex)
{
result.HasError = true;
result.ErrorText = ex.Message;
}
connRC.Close();
}
catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером.
{
result.ErrorText = ex.Message;
result.HasError = true;
}
return result;
}
/// <summary>
/// Recherche les locations du client avec son ID.
/// </summary>
/// <returns>The location no telephone.</returns>
/// <param name="noTelephone">No telephone.</param>
public DataSet RechercheLocationClient(string ID)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand ("SP_TrouverLocationDuClient", conn); ///Va prendre le ID du client pour lui sortir ces informations.
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add ("ID", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = ID;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter ();
DataSet ds = new DataSet ();
da.SelectCommand = cmd;
da.Fill (ds);
return ds;
}catch(Exception ex){
error = ex.Message;
}
return null;
}
/// <summary>
/// Fait à peu près la même chose que RetourMontantDu. La différence est qu'elle retourne un string ID_De_Location.
/// Fait une deuxième méthode pour des questions de problème avec les accesseurs. DataSet reste NULL même quand valeur attribué
/// </summary>
/// <returns>The identifier location paiement fenetre.</returns>
/// <param name="ID">I.</param>
public string RetourIDLocationPaiementFenetre(string ID)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_MontantDuIDLocation",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = ID;
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet IDLocation = new DataSet();
data.SelectCommand = cmd;
data.Fill(IDLocation);
return IDLocation.Tables[0].Rows[0].ItemArray[1].ToString();
}catch(Exception ex){
error = ex.Message;
}
return null;
}
/// <summary>
/// Trouver les clients avec les même noms de famille.
/// </summary>
/// <returns>The client avec nom famille.</returns>
/// <param name="nomFamille">Nom famille.</param>
public DataSet FindClientAvecNomFamille(string nomFamille)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_TrouverClientNomFamille", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("NomFamille", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = nomFamille;
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}catch (Exception ex){
error = ex.Message;
}
return null;
}
private void SearchB_Click(object sender, RoutedEventArgs e)
{
Dispatcher.BeginInvoke(DispatcherPriority.Normal, (ThreadStart) delegate()
{
if (CByName.IsChecked == true)
{
Pb.Visibility = Visibility.Visible;
mT.Clear();
mA.Dispose();
mA = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM medics WHERE `Name` = '" + SearchBox.Text + "'ORDER BY Name", Database.DataHolder.MySqlConnection);
mT = new System.Data.DataTable();
mA.Fill(mT);
if (mA == null)
{
return;
}
if (mT.Rows.Count == 0)
{
mT.Rows.Add(new object[mT.Columns.Count]);
}
DataGrid.ItemsSource = mT.DefaultView;
Pb.Visibility = Visibility.Hidden;
}
else if (CByBar.IsChecked == true)
{
Pb.Visibility = Visibility.Visible;
mT.Clear();
mA.Dispose();
mA = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM medics WHERE `Barcode` = '" + SearchBox.Text + "'ORDER BY Name", Database.DataHolder.MySqlConnection);
mT = new System.Data.DataTable();
mA.Fill(mT);
if (mA == null)
{
return;
}
if (mT.Rows.Count == 0)
{
mT.Rows.Add(new object[mT.Columns.Count]);
}
DataGrid.ItemsSource = mT.DefaultView;
Pb.Visibility = Visibility.Hidden;
}
else if (CBySub.IsChecked == true)
{
Pb.Visibility = Visibility.Visible;
mT.Clear();
mA.Dispose();
mA = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM medics WHERE `ScientificName` = '" + SearchBox.Text + "'ORDER BY Name", Database.DataHolder.MySqlConnection);
mT = new System.Data.DataTable();
mA.Fill(mT);
if (mA == null)
{
return;
}
if (mT.Rows.Count == 0)
{
mT.Rows.Add(new object[mT.Columns.Count]);
}
DataGrid.ItemsSource = mT.DefaultView;
Pb.Visibility = Visibility.Hidden;
}
});
}
protected override System.Data.IDataAdapter getDataAdapter(string storedProcedure, params Object[] Args)
{
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter((MySql.Data.MySqlClient.MySqlCommand) getCommand(storedProcedure));
if(Args.Length!=0)
{
loadParameters(da.SelectCommand, Args);
}
return (System.Data.IDbDataAdapter) da;
}
public MySQLComDbContext(string sConnectionString)
{
this._connectionString = sConnectionString;
this._DBAdapter = new SqlDataAdapter();
this._connection = new SqlConnection(this._connectionString);
}
/// <summary>
/// Recherche les clients avec le numéro de téléphone.
/// </summary>
/// <returns>The client no telephone.</returns>
/// <param name="noTelephone">No telephone.</param>
public DataSet RechercheClientNoTelephone(string noTel)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand ("SP_TrouverClient", conn);// elle retourne tous les informations de table client
// avec le numéro de téléphone.
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add ("Tel", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = noTel;
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter ();
DataSet dSet = new DataSet ();
data.SelectCommand = cmd;
data.Fill (dSet);
return dSet;
}catch(Exception ex){
error = ex.Message;
}
return null;
}
public ADOModelResponse ExecuteQuery(ADOModelRequest QueryParams)
{
ADOModelResponse oRetorno = new ADOModelResponse();
//get connection context
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(CurrentConnectionString);
try
{
//create mysql command
MySql.Data.MySqlClient.MySqlCommand CurrentCommand = new MySql.Data.MySqlClient.MySqlCommand();
CurrentCommand.Connection = Conn;
CurrentCommand.CommandText = QueryParams.CommandText;
//Time for scripts execution in seconds
CurrentCommand.CommandTimeout = 100;
if (QueryParams.Parameters != null && QueryParams.Parameters.Count > 0)
{
QueryParams.Parameters.All(param =>
{
CurrentCommand.Parameters.Add((MySql.Data.MySqlClient.MySqlParameter)param);
return(true);
});
}
CurrentCommand.CommandType = QueryParams.CommandType;
//validate method to execute
switch (QueryParams.CommandExecutionType)
{
case enumCommandExecutionType.NonQuery:
using (CurrentCommand.Connection)
{
CurrentCommand.Connection.Open();
oRetorno.NonQueryResult = CurrentCommand.ExecuteNonQuery();
CurrentCommand.Connection.Close();
}
break;
case enumCommandExecutionType.Scalar:
using (CurrentCommand.Connection)
{
CurrentCommand.Connection.Open();
oRetorno.ScalarResult = CurrentCommand.ExecuteScalar();
CurrentCommand.Connection.Close();
}
break;
case enumCommandExecutionType.DataTable:
oRetorno.DataTableResult = new DataTable();
using (MySql.Data.MySqlClient.MySqlDataAdapter dat = new MySql.Data.MySqlClient.MySqlDataAdapter(CurrentCommand))
{
oRetorno.NonQueryResult = dat.Fill(oRetorno.DataTableResult);
}
break;
case enumCommandExecutionType.DataSet:
oRetorno.DataSetResult = new DataSet();
using (MySql.Data.MySqlClient.MySqlDataAdapter dads = new MySql.Data.MySqlClient.MySqlDataAdapter(CurrentCommand))
{
oRetorno.NonQueryResult = dads.Fill(oRetorno.DataSetResult);
}
break;
default:
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
return(oRetorno);
}
/// <summary>
/// Utilise la procédure stocké Sp_MontantDuIDLocation avec un ID_Client comme valeur d'entrée et un Select de Montant_Mensuel et ID_de_Locations
/// </summary>
/// <returns>The montant du.</returns>
/// <param name="ID">I.</param>
public string RetourMontantDu(string ID)
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_MontantDuIDLocation",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = ID;
MySql.Data.MySqlClient.MySqlDataAdapter dataA = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet montant = new DataSet();
dataA.SelectCommand = cmd;
dataA.Fill(montant);
return montant.Tables[0].Rows[0].ItemArray[0].ToString() + "\nNuméro de Location : " + montant.Tables[0].Rows[0].ItemArray[1].ToString();
}catch(Exception ex){
error = ex.Message;
}
return null;
}
private void button2_Click(object sender, EventArgs e)
{
try
{
string s_imsi = string.Empty;
System.ComponentModel.BackgroundWorker work = new System.ComponentModel.BackgroundWorker();
work.ProgressChanged += delegate(object me, System.ComponentModel.ProgressChangedEventArgs ev)
{
this.Invoke(new MethodInvoker(delegate
{
this.toolStripProgressBar1.Value = ev.ProgressPercentage;
}));
};
work.WorkerReportsProgress = true;
System.Collections.Generic.List <PCSC.GSMAlgorithm> gsmresult = new System.Collections.Generic.List <PCSC.GSMAlgorithm>();
this._simcard.Connect(this.comboBox1.SelectedItem);
if (this._simcard.Connected)
{
((Button)sender).Enabled = false;
work.ReportProgress(10);
Form2 pinCode = new Form2();
int pinrequired = -1;
while (pinrequired < 0)
{
try
{
pinrequired = Convert.ToInt32(this._simcard.Authenticated);
System.Threading.Thread.Sleep(100);
}
catch { }
}
if (pinrequired == 0)
{
work.ReportProgress(20);
pinCode.FormClosed += delegate(object form_, FormClosedEventArgs ev_)
{
Form f = form_ as Form;
if (f.DialogResult == DialogResult.OK)
{
byte[] pincode = new byte[] { 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF };
byte[] received_pincode = Encoding.ASCII.GetBytes(f.Controls["textBox1"].Text);
for (int i = 0; i < received_pincode.Length; i++)
{
pincode[i] = received_pincode[i];
}
this._simcard.PIN1 = pincode;
}
};
while (!this._simcard.Authenticated && this._simcard.TryPIN1 > 0 && pinCode.DialogResult != DialogResult.Cancel)
{
pinCode.Text = string.Format("Code PIN - {0} essais", this._simcard.TryPIN1);
pinCode.Controls["textBox1"].ResetText();
pinCode.ShowDialog(this);
}
if (this._simcard.Authenticated)
{
work.ReportProgress(30);
/*
* PCSC.Rand rand = new PCSC.Rand();
* PCSC.GSMAlgorithm response = this._simcard.RunGSMAlgorithm(rand.ToByteArray());
*
* this.textBox1.Lines = new string[]
* {
* "IMSI = " + Core.Utility.ByteArray.ToString(this._simcard.IMSI),
* "RAND = " + rand.ToString(),
* "SRES = " + Core.Utility.ByteArray.ToString(response.SRes),
* "KC = " + Core.Utility.ByteArray.ToString(response.Kc),
* };
*/
s_imsi = Core.Utility.ByteArray.ToString(this._simcard.IMSI);
for (int i = 0; i < 3; i++)
{
gsmresult.Add(this._simcard.RunGSMAlgorithm(new PCSC.Rand().ToByteArray()));
}
work.ReportProgress(40);
}
}
else
{
/*
* PCSC.Rand rand = new PCSC.Rand();
* PCSC.GSMAlgorithm response = this._simcard.RunGSMAlgorithm(rand.ToByteArray());
*
* this.textBox1.Lines = new string[]
* {
* "IMSI = " + Core.Utility.ByteArray.ToString(this._simcard.IMSI),
* "RAND = " + rand.ToString(),
* "SRES = " + Core.Utility.ByteArray.ToString(response.SRes),
* "KC = " + Core.Utility.ByteArray.ToString(response.Kc),
* };
*/
s_imsi = Core.Utility.ByteArray.ToString(this._simcard.IMSI);
for (int i = 0; i < 3; i++)
{
gsmresult.Add(this._simcard.RunGSMAlgorithm(new PCSC.Rand().ToByteArray()));
}
work.ReportProgress(40);
}
s_imsi = string.Format("{0}@wlan.mnc{2}.mcc{1}.3gppnetwork.org", s_imsi, s_imsi.Substring(1, 3).PadLeft(3, '0'), s_imsi.Substring(4, 2).PadLeft(3, '0'));
object[] prm = new object[] { s_imsi, gsmresult };
work.DoWork += new System.ComponentModel.DoWorkEventHandler(delegate(object me, System.ComponentModel.DoWorkEventArgs ev)
{
try
{
object[] args = ev.Argument as object[];
IniFile config = new IniFile("config.ini");
MySql.Data.MySqlClient.MySqlConnection sqlconn = new MySql.Data.MySqlClient.MySqlConnection()
{
ConnectionString = string.Format("server={0};uid={1};pwd={2};database={3}", config.Read("SQLHOST", "config"), config.Read("SQLUSER", "config"), Encoding.ASCII.GetString(Convert.FromBase64String(config.Read("SQLPWD", "config"))), config.Read("SQLDB", "config"))
};
sqlconn.Open();
System.Data.DataSet ds = new System.Data.DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(string.Format("SELECT username,attribute,op,value FROM radcheck WHERE username = '******'", args[0]), sqlconn);
da.Fill(ds);
((System.ComponentModel.BackgroundWorker)me).ReportProgress(45);
if (ds.Tables[0].Rows.Count == 0)
{
ds.Tables[0].Rows.Add(new object[] { args[0], "Auth-Type", ":=", "eap" });
ds.Tables[0].Rows.Add(new object[] { args[0], "EAP-Type", ":=", "sim" });
((System.ComponentModel.BackgroundWorker)me).ReportProgress(50);
for (int k = 0; k < 3; k++)
{
ds.Tables[0].Rows.Add(new object[] { args[0], string.Format("EAP-Sim-RAND{0}", k + 1), ":=", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList <PCSC.GSMAlgorithm>)args[1])[k].Rand).ToLower()) });
ds.Tables[0].Rows.Add(new object[] { args[0], string.Format("EAP-Sim-SRES{0}", k + 1), ":=", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList <PCSC.GSMAlgorithm>)args[1])[k].SRes).ToLower()) });
ds.Tables[0].Rows.Add(new object[] { args[0], string.Format("EAP-Sim-KC{0}", k + 1), ":=", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList <PCSC.GSMAlgorithm>)args[1])[k].Kc).ToLower()) });
((System.ComponentModel.BackgroundWorker)me).ReportProgress(50 + ((k + 1) * 10));
}
new MySql.Data.MySqlClient.MySqlCommandBuilder(da);
da.Update(ds);
((System.ComponentModel.BackgroundWorker)me).ReportProgress(90);
ev.Result = "La carte SIM a correctement été ajoutée pour l'authentification EAP-SIM !";
}
else
{
ev.Result = "La carte SIM a déjà été enregistrée pour l'authentification EAP-SIM !";
}
/*
* MySql.Data.MySqlClient.MySqlCommand ins = new MySql.Data.MySqlClient.MySqlCommand()
* {
* CommandText = string.Format("INSERT INTO radcheck(username,attribute,op,value) VALUES (@username, @attribute, @op, @value);"),
* Connection = sqlconn
* };
* ins.Prepare();
*
* ins.Parameters.AddWithValue("@username", args[0]);
* ins.Parameters.AddWithValue("@attribute", "Auth-Type");
* ins.Parameters.AddWithValue("@op", ":=");
* ins.Parameters.AddWithValue("@value", "eap");
*
* ins.ExecuteNonQuery();
*
* ins = new MySql.Data.MySqlClient.MySqlCommand()
* {
* CommandText = string.Format("INSERT INTO radcheck(username,attribute,op,value) VALUES (@username, @attribute, @op, @value);"),
* Connection = sqlconn
* };
* ins.Prepare();
*
* ins.Parameters.AddWithValue("@username", args[0]);
* ins.Parameters.AddWithValue("@attribute", "EAP-Type");
* ins.Parameters.AddWithValue("@op", ":=");
* ins.Parameters.AddWithValue("@value", "sim");
*
* ins.ExecuteNonQuery();
*
* for (int o = 0; o < 3; o++)
* {
* ins = new MySql.Data.MySqlClient.MySqlCommand()
* {
* CommandText = string.Format("INSERT INTO radcheck(username,attribute,op,value) VALUES (@username, @attribute, @op, @value);"),
* Connection = sqlconn
* };
* ins.Prepare();
*
* ins.Parameters.AddWithValue("@username", args[0]);
* ins.Parameters.AddWithValue("@attribute", string.Format("EAP-Sim-RAND{0}", o + 1));
* ins.Parameters.AddWithValue("@op", ":=");
* ins.Parameters.AddWithValue("@value", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList<PCSC.GSMAlgorithm>)args[1])[o].Rand).ToLower()));
*
* ins.ExecuteNonQuery();
*
* ins = new MySql.Data.MySqlClient.MySqlCommand()
* {
* CommandText = string.Format("INSERT INTO radcheck(username,attribute,op,value) VALUES (@username, @attribute, @op, @value);"),
* Connection = sqlconn
* };
* ins.Prepare();
*
* ins.Parameters.AddWithValue("@username", args[0]);
* ins.Parameters.AddWithValue("@attribute", string.Format("EAP-Sim-SRES{0}", o + 1));
* ins.Parameters.AddWithValue("@op", ":=");
* ins.Parameters.AddWithValue("@value", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList<PCSC.GSMAlgorithm>)args[1])[o].SRes).ToLower()));
*
* ins.ExecuteNonQuery();
*
* ins = new MySql.Data.MySqlClient.MySqlCommand()
* {
* CommandText = string.Format("INSERT INTO radcheck(username,attribute,op,value) VALUES (@username, @attribute, @op, @value);"),
* Connection = sqlconn
* };
* ins.Prepare();
*
* ins.Parameters.AddWithValue("@username", args[0]);
* ins.Parameters.AddWithValue("@attribute", string.Format("EAP-Sim-KC{0}", o + 1));
* ins.Parameters.AddWithValue("@op", ":=");
* ins.Parameters.AddWithValue("@value", string.Format("0x{0}", Core.Utility.ByteArray.ToString(((System.Collections.Generic.IList<PCSC.GSMAlgorithm>)args[1])[o].Kc).ToLower()));
*
* ins.ExecuteNonQuery();
* }
*/
sqlconn.Close();
((System.ComponentModel.BackgroundWorker)me).ReportProgress(100);
}
catch (Exception ex) { MessageBox.Show(ex.Message, ex.Source); ev.Result = "La carte SIM n'a pas pu être enregistrée pour l'authentification EAP-SIM !"; }
});
work.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(delegate(object me, System.ComponentModel.RunWorkerCompletedEventArgs ev)
{
this.textBox1.Invoke(new MethodInvoker(delegate
{
this.textBox1.Lines = new string[]
{
ev.Result.ToString(),
"Vous pouvez retirer la carte SIM du lecteur.",
Environment.NewLine,
"IMSI : " + s_imsi.Substring(1)
};
this._simcard.Dispose();
pinCode = null;
if (!this._simcard.Connected)
{
((Button)sender).Enabled = true;
}
this.button1_Click(sender, e);
}));
});
work.RunWorkerAsync(prm);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//Finalmente, es el turno de definir CrearDataAdapter, el cual aprovecha el método Comando para crear el comando necesario.
protected override System.Data.IDataAdapter CrearDataAdapterSql(string comandoSql)
{
var da = new MySql.Data.MySqlClient.MySqlDataAdapter((MySql.Data.MySqlClient.MySqlCommand)ComandoSql(comandoSql));
return da;
}
protected override System.Data.IDataAdapter getDataAdapter(string storedProcedure)
{
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter((MySql.Data.MySqlClient.MySqlCommand) getCommand(storedProcedure));
return (System.Data.IDbDataAdapter) da;
}
/// <summary>
/// Montrer les clients qui sont dans la base de donnée.
/// </summary>
/// <returns>The clients enregistre.</returns>
public DataSet MontrerClientsEnregistre()
{
try{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_SelectClient", conn);
cmd.CommandType = CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter();
DataSet client = new DataSet();
data.SelectCommand = cmd;
data.Fill (client);
return client;
}catch(Exception ex){
error = ex.Message;
}
return null;
}
public List <SchemaRow> GetSchema()
{
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
if (this.ColumnSchemaQuery.IndexOf(this.TableNamePlaceHolder) == -1)
{
throw new Exception("Required placeholder for table name: '" + this.TableNamePlaceHolder + "'.");
}
}
List <SchemaRow> Schema = new List <SchemaRow>();
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnStr)) {
conn.Open();
//Using single result set
if (!string.IsNullOrEmpty(this.SchemaQuery))
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.SchemaQuery;
DataTable dtSchema = new DataTable();
using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd)) {
da.Fill(dtSchema);
}
conn.Close();
foreach (DataRow dr in dtSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(dr["TABLE_NAME"].ToString(), dr["TABLE_TYPE"].ToString(), dr));
}
return(Schema);
}
}
//Way of the Table/Column
//Retrieve table schema first
List <SchemaRow> TableSchema = new List <SchemaRow>();
if (!string.IsNullOrEmpty(this.TableSchemaQuery))
{
//Using table schema query
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.TableSchemaQuery;
DataTable dtTableSchema = new DataTable();
using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd)) {
da.Fill(dtTableSchema);
}
TableSchema = this.GetInitialTables(dtTableSchema);
}
}
else
{
//Get by default using GetSchema
DataTable dtTableSchema = new DataTable();
dtTableSchema = conn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables);
TableSchema = this.GetInitialTables(dtTableSchema);
}
//Get columns for each table
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
//Use column schema query
foreach (SchemaRow tsr in TableSchema)
{
DataTable dtColumnSchema = new DataTable();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.ColumnSchemaQuery.Replace(this.TableNamePlaceHolder, tsr.Name);
using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd)) {
da.Fill(dtColumnSchema);
}
}
//Get column schema
foreach (DataRow dr in dtColumnSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(tsr.Name, tsr.Type, dr));
}
}
}
else
{
//Get by default using DataReader GetSchemaTable
//I can't figure this out either. We will not use generic get schema.
}
conn.Close();
}
return(Schema);
}
public void reportFromDB()
{
sql = "call FinalReportSimplified();";
MySql.Data.MySqlClient.MySqlDataAdapter dscmd = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, cnn);
MySql.Data.MySqlClient.MySqlDataAdapter Ids = new MySql.Data.MySqlClient.MySqlDataAdapter("select banner_id from instructor order by banner_id;", cnn);
MySql.Data.MySqlClient.MySqlDataAdapter Fname = new MySql.Data.MySqlClient.MySqlDataAdapter("select first_name from instructor order by first_name;", cnn);
MySql.Data.MySqlClient.MySqlDataAdapter Lname = new MySql.Data.MySqlClient.MySqlDataAdapter("select last_name from instructor order by last_name;", cnn);
string IdList = stringifySet(Ids);
string firstNameList = stringifySet(Fname);
string lastNamesList = stringifySet(Lname);
DataSet ds = new DataSet();
dscmd.Fill(ds);
getSheetInfo();
for (int row = 0; row <= ds.Tables[0].Rows.Count - 1; row++)
{
for (int col = 0; col <= ds.Tables[0].Columns.Count - 1; col++)
{
if (col == 1) //Adding leasing 0's to course number
{
final_report.Cells[row + 2, col + 1].NumberFormat = "@";
final_report.Cells[row + 2, col + 1] = ds.Tables[0].Rows[row].ItemArray[col].ToString();
}
else if (col == 5) //Professor First Names Col
{
var cell = (Range)final_report.Cells[row + 2, col + 1];
cell.Validation.Delete();
cell.Validation.Add(XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
XlFormatConditionOperator.xlBetween,
firstNameList,
Type.Missing);
cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;
if (ds.Tables[0].Rows[row].ItemArray[col].ToString() != null || ds.Tables[0].Rows[row].ItemArray[col].ToString() == "")
{
cell.Value = ds.Tables[0].Rows[row].ItemArray[col].ToString();
}
}
else if (col == 6) //Professor Last Names Col
{
var cell = (Range)final_report.Cells[row + 2, col + 1];
cell.Validation.Delete();
cell.Validation.Add(XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
XlFormatConditionOperator.xlBetween,
lastNamesList,
Type.Missing);
cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;
if (ds.Tables[0].Rows[row].ItemArray[col].ToString() != null || ds.Tables[0].Rows[row].ItemArray[col].ToString() == "")
{
cell.Value = ds.Tables[0].Rows[row].ItemArray[col].ToString();
}
}
else //Every other column
{
data = ds.Tables[0].Rows[row].ItemArray[col].ToString();
final_report.Cells[row + 2, col + 1] = data;
}
}
}
}
