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; } } } }