static void Main(string[] args) { DataTable nombres = new DataTable(), apellidosP = new DataTable(), apellidosS = new DataTable(); using (var cmd = new MySql.Data.MySqlClient.MySqlCommand("select nombre from nombres order by rand()", new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Database=nueva;Uid=root;Pwd=;SslMode=none"))) { cmd.Connection.Open(); var DA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); DA.Fill(nombres); cmd.CommandText = "select apellido from apellidos order by rand()"; DA.SelectCommand = cmd; DA.Fill(apellidosP); cmd.CommandText = "select apellido from apellidos order by rand()"; DA.SelectCommand = cmd; DA.Fill(apellidosS); foreach (DataRow nombre in nombres.Rows) { for (int i = 0; i < apellidosP.Rows.Count; i++) { cmd.CommandText = "insert into personas (nombres,apellidoP,apellidoS) values ("; cmd.CommandText += string.Format("'{0}','{1}','{2}')", nombre.ItemArray[0], apellidosP.Rows[i].ItemArray[0], apellidosS.Rows[i].ItemArray[0]); cmd.ExecuteNonQuery(); } } } }
public override System.Data.DataTable dttbExecuteSql(string sqlCommand) { System.Data.DataTable dttbRetorno = null; sqlCommand = sqlCommand.Trim().ToUpper(); if (sqlCommand.StartsWith("SELECT")) { try { dttbRetorno = new System.Data.DataTable(); SetCommandSelect(sqlCommand); m_excError = null; OpenConnection(); m_DataAdapter.Fill(dttbRetorno); CloseConnection(); } catch (System.Exception eEcp) { m_excError = eEcp; } } else { vExecuteCommand(sqlCommand); } return(dttbRetorno); }
/// <summary> /// Copies the bugzilla projects. /// </summary> public static DataTable CopyBugzillaProject() { DataTable dtBugzillaProject = new DataTable(); var mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["Bugzilla"].ToString()); var mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT id,name FROM products", mySqlConnection); mySqlConnection.Open(); var mySqlDataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(mySqlCommand); mySqlDataAdapter.Fill(dtBugzillaProject); mySqlConnection.Close(); //if (dtBugzillaProject.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); // } //} if (dtBugzillaProject.Rows.Count > 0) { return(dtBugzillaProject); } else { return(null); } }
private void metodo() { try { string connessione = "Server=localhost;Database=backend;Uid=root;Pwd=root;"; 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.MySqlDataReader reader = cmd.ExecuteReader(); MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(); adapter.SelectCommand = cmd; DataTable table = new DataTable(); adapter.Fill(table); dataGridView1.DataSource = table; dataGridView1.Columns["nome"].Visible = false; dataGridView1.Columns["nomecliente"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; conn.Close(); } /* while (reader.Read()) * { * string valorecorrente = reader["nomecliente"].ToString(); * // listView1.Items.Add(valorecorrente); * listBox1.Items.Add(valorecorrente); * } * } */ catch (MySql.Data.MySqlClient.MySqlException e) { conn.Close(); throw; } }
protected void OnAuthenticate(object sender, AuthenticateEventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["dataConn"].ToString(); using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(connStr)) { string username = Login1.UserName.ToString(); string password = Login1.Password.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("select * from customer where customer_email like @username and customer_password = @password;"); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@password", password); cmd.Connection = con; con.Open(); System.Data.DataSet ds = new System.Data.DataSet(); MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); da.Fill(ds); con.Close(); bool loginSuccessful = ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0)); if (loginSuccessful) { Response.Cookies["true"].Value = "true"; Response.Cookies["true"].Expires = DateTime.Now.AddDays(1); Response.Cookies["username"].Value = username; Response.Cookies["username"].Expires = DateTime.Now.AddDays(1); Response.Redirect("Index.aspx", true); Login1.Visible = false; } else { Response.Redirect("PasswordRecovery.aspx", true); } } }
public VentanaInforme() { InitializeComponent(); ligaDataSet dsTodaLaLiga = new ligaDataSet(); informeDatos.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local; Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource(); dsTodaLaLiga.BeginInit(); reportDataSource1.Name = "DataSet1"; reportDataSource1.Value = dsTodaLaLiga.jugador; informeDatos.LocalReport.DataSources.Add(reportDataSource1); informeDatos.LocalReport.ReportPath = "Report1.rdlc"; dsTodaLaLiga.EndInit(); try { String conexion = ConfigurationManager.ConnectionStrings["Gestion_Jugadores.Properties.Settings.ligaConnectionString"].ConnectionString; MySql.Data.MySqlClient.MySqlDataAdapter JugadoreTableAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter("Select * From jugador as j,equipo as e where j.equipo=e.id", conexion); dsTodaLaLiga.Clear(); JugadoreTableAdapter.Fill(dsTodaLaLiga); informeDatos.RefreshReport(); } catch (Exception exception) { Console.Error.WriteLine(exception); } informeDatos.RefreshReport(); }
/** * Carga el informe en el Windows Forms Host una vez se selecciona * un equipo del ListBox. Tiene un fallo en el que una vez carga un informe * no vuelve a cargar otro hasta que se cierre la ventana y se vuelva a abrir, * he intentado solucionarlo pero sin resultados... mi idea era refrescar o * vaciar el WindowsFormsHost (o incluso la ventana WPF entera) pero no hayé metodo para hacerlo. * */ private void listBoxChangedEvent(object sender, SelectionChangedEventArgs e) { informeDatos.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local; Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource(); ligaDataSet dsTodaLaLiga = new ligaDataSet(); dsTodaLaLiga.BeginInit(); reportDataSource1.Name = "DataSet1"; reportDataSource1.Value = dsTodaLaLiga.jugador; informeDatos.LocalReport.DataSources.Add(reportDataSource1); informeDatos.LocalReport.ReportPath = "Jugadores.rdlc"; dsTodaLaLiga.EndInit(); try { String conexion = ConfigurationManager.ConnectionStrings["LigaManager.Properties.Settings.ligaConnectionString"].ConnectionString; MySql.Data.MySqlClient.MySqlDataAdapter JugadoreTableAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter("Select * from jugador WHERE jugador.equipo = " + (listBox.SelectedIndex + 1), conexion); dsTodaLaLiga.Clear(); JugadoreTableAdapter.Fill(dsTodaLaLiga.jugador); informeDatos.RefreshReport(); } catch (Exception exception) { Console.Error.WriteLine(exception); } }
/// <summary> /// returns one or more datatable from database /// </summary> /// <param name="Query">Write either SQl Select statment or name of Stored Procedure </param> /// <param name="CommandType">Specify command type as Text if you passed Text as Query or StoredProcedure if you passed name of Stored Procedure as Query</param> public System.Data.DataSet Select_TableSet(string Query, System.Data.CommandType CommandType) { System.Data.DataSet DS = new System.Data.DataSet(); try { if (_MyConnection.State == System.Data.ConnectionState.Closed) { _MyConnection.Open(); _MyCommand.Connection = _MyConnection; } _MyCommand.CommandText = Query; _MyCommand.CommandType = CommandType; _MyCommand.CommandTimeout = 0; _MyCommand.CommandText = Query; _MyCommand.CommandType = CommandType; _MyCommand.CommandTimeout = 0; _MyDataAdaptor = new MySql.Data.MySqlClient.MySqlDataAdapter(_MyCommand); _MyDataAdaptor.Fill(DS); return(DS); } catch (MySql.Data.MySqlClient.MySqlException Sqex) { throw new System.Exception(ErrorCodes.ProcessException(Sqex, "", "", "", ErrorCodes.MySqlExceptionMsg(Sqex))); } catch (System.Exception Ex) { throw new System.Exception(Ex.Message); } }
public string Regresaunregistro(String SQL) { DataTable content = new DataTable(); string respuesta = ""; try { MySql.Data.MySqlClient.MySqlConnection Cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["CNN"].ToString()); MySql.Data.MySqlClient.MySqlCommand Cmd = new MySql.Data.MySqlClient.MySqlCommand(SQL, Cnn); MySql.Data.MySqlClient.MySqlDataAdapter Adapt = new MySql.Data.MySqlClient.MySqlDataAdapter(Cmd); Adapt.Fill(content); foreach (DataRow rw in content.Rows) { respuesta = rw[0].ToString(); break; } } catch (Exception ed) { Helper.RegistrarEvento("En funcion Regresunregistro : " + ed.Message); } return(respuesta); }
public void GetImage() { string conString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString(); con = new MySql.Data.MySqlClient.MySqlConnection(conString); con.Open(); queryStr = ""; queryStr = "SELECT imgName, imgHeader, imgContent FROM guner_db.sliderimage"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, con); DataTable dt = new DataTable(); MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); da.Fill(dt); if (dt.Rows.Count > 0 && (string)dt.Rows[0][0] != string.Empty) { rpt.DataSource = dt; rpt.DataBind(); } else { rpt.DataSource = null; rpt.DataBind(); } }
/// <summary> /// Create data base conection /// </summary> /// <param name="connectionString"></param> /// <returns></returns> public DataTable ExecuteDataTable(string SqlStatment) { MySql.Data.MySqlClient.MySqlConnection Cn = new MySql.Data.MySqlClient.MySqlConnection(SQL_CONN_STRING); try { if (Cn.State != ConnectionState.Open) { Cn.Open(); } MySql.Data.MySqlClient.MySqlDataAdapter adtp = new MySql.Data.MySqlClient.MySqlDataAdapter(SqlStatment, Cn); DataSet Ds = new DataSet(); adtp.Fill(Ds); Cn.Close(); return(Ds.Tables[0]); } catch (Exception) { Cn.Close(); return(null); } finally { Cn.Close(); } }
private void dataGrid_Loaded(object sender, RoutedEventArgs e) { try { Dispatcher.BeginInvoke(DispatcherPriority.Background, (ThreadStart) delegate() { mA = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM medics", DataHolder.MySqlConnection); mT = new System.Data.DataTable(); mA.Fill(mT); DataGrid.ItemsSource = mT.DefaultView; DataGrid.Columns[11].Visibility = Visibility.Collapsed; DataGrid.Columns[12].Visibility = Visibility.Collapsed; DataGrid.Columns[13].Visibility = Visibility.Collapsed; DataGrid.Columns[14].Visibility = Visibility.Collapsed; DataGrid.Columns[15].Visibility = Visibility.Collapsed; DataGrid.Columns[16].Visibility = Visibility.Collapsed; DataGrid.Columns[17].Visibility = Visibility.Collapsed; DataGrid.Columns[18].Visibility = Visibility.Collapsed; DataGrid.Columns[19].Visibility = Visibility.Collapsed; DataGrid.Columns[20].Visibility = Visibility.Collapsed; DataGrid.Columns[21].Visibility = Visibility.Collapsed; Pb.Visibility = Visibility.Collapsed; }); } catch (Exception ex) { Console.WriteLine("Y da real MVC."); Kernel.Core.SaveException(ex); BackMainB_Click(sender, e); } }
//Verileri SQL komutuyla çekmek için kullanılan fonksiyon static public DataSet getDataSet(string query, string tableName) { try { //DataSet sınıfından ds isminde nesne oluşturdu //Veritabanı bağlantısı oluşturdu //DataAdapter oluşturdu DataSet ds = new DataSet(); Mycon mycnct = DatabaseInfo.getConnection(); Myad myadapter = new Myad(); // //Gönderilen SQL komutunu DataAdapter yardımıyla çekti //Veritabanı bağlantısını açtı //Çekilen verileri DataSet'in içerisine doldurdu //Veritabanı bağlantısını kapattı //DataSet'i geri döndürdü myadapter.SelectCommand = new Mycom(query, mycnct); mycnct.Open(); myadapter.Fill(ds, tableName); mycnct.Close(); return(ds); // } catch (Exception ex) { //Eğer try kısmında hata alırsak çalışması gereken kod MetroMessageBox.Show(data_entry.ActiveForm, ex.Message, "Database Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(null); // } }
//Look at adding dataset public database() { conn = new MySql.Data.MySqlClient.MySqlConnection(conn_string); conn.Open(); MySql.Data.MySqlClient.MySqlDataAdapter t; string query = "SELECT * from task where 1"; t = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn); task = new System.Data.DataSet(); t.Fill(task, "Task"); query = "SELECT * from task_actions where 1"; t = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn); task_actions = new System.Data.DataSet(); t.Fill(task_actions, "Task Actions"); query = "SELECT * from action WHERE 1"; t = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn); actions = new System.Data.DataSet(); t.Fill(actions, "Actions"); query = "SELECT * from object WHERE 1"; t = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn); this.objects = new System.Data.DataSet(); t.Fill(this.objects, "Objects"); query = "SELECT * from obj_act WHERE 1"; t = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn); this.affordances = new System.Data.DataSet(); t.Fill(this.affordances, "Affordances"); }
private void button1_Click(object sender, EventArgs e) { if (textBox1.Text == "" || textBox2.Text == "") { MessageBox.Show("PLEASE ENTER A VALUE, TRY AGAIN"); } else { comm = new MySql.Data.MySqlClient.MySqlCommand(); comm.Connection = conn; comm.CommandText = "select * from user"; comm.CommandType = CommandType.Text; ds = new DataSet(); adap = new MySql.Data.MySqlClient.MySqlDataAdapter(comm.CommandText, conn); adap.Fill(ds, "user"); dt = ds.Tables["user"]; int t = dt.Rows.Count; // getting last id from the database table dr = dt.Rows[t - 1]; int s1 = int.Parse(dr["id"].ToString()); s1++; String s = s1.ToString(); comm.CommandText = "insert into user values('" + s + "','" + textBox2.Text + "','" + textBox1.Text + "')"; comm.CommandType = CommandType.Text; comm.ExecuteNonQuery(); conn.Close(); this.Hide(); } }
public List <SchemaRow> GetRoutineSchema() { if (string.IsNullOrEmpty(this.SchemaQuery)) { return(new List <SchemaRow>()); } using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnStr)) { conn.Open(); List <SchemaRow> Schema = new List <SchemaRow>(); using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = this.RoutineSchemaQuery; 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.SetParameterAttributes(dr["ROUTINE_NAME"].ToString(), dr["ROUTINE_TYPE"].ToString(), dr)); } return(Schema); } } }
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]; }
} // Fetch public CmsPersistentVariable[] FetchAll() { using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", ""))) { OpenMySqlConnection(conn); string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables; "; MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); DataSet ds = new DataSet(); sqlDA.Fill(ds); List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>(); if (this.hasRows(ds)) { foreach (DataRow dr in ds.Tables[0].Rows) { arrayList.Add(GetFromRow(dr)); } // foreach row } // if there is data return(arrayList.ToArray()); } } // FetchAll
public static DataTable GetData(string strSql) { if (strSql == null || strSql.Length == 0) { return(null); } using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(string.Format(connstring, IP, User, Pass))) { try { conn.Open(); MySql.Data.MySqlClient.MySqlDataAdapter sqlData = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql, conn); DataTable data = new DataTable(); sqlData.Fill(data); conn.Close(); return(data); } catch (Exception ex) { MyLog4Net.Container.Instance.Log.DebugWithDebugView("GetData ret err:" + ex); return(null); } } }
/// <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); }
} // GetFromRow public CmsPersistentVariable Fetch(string name) { if (name.Trim() == "") { return(new CmsPersistentVariable("", null)); } using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", ""))) { OpenMySqlConnection(conn); string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables "; sql += " WHERE Name like @Name"; MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddWithValue("@Name", name); MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); DataSet ds = new DataSet(); sqlDA.Fill(ds); if (this.hasSingleRow(ds)) { DataRow dr = ds.Tables[0].Rows[0]; return(GetFromRow(dr)); } return(new CmsPersistentVariable("", null)); } } // Fetch
public debtPaid(int summ, bool roznica) { InitializeComponent(); paidSumm = summ; tbxSum.Text = summ.ToString(); if (roznica) { rbtPerevod.Visible = false; rbtOther.Visible = false; } //Вывод в ComboBox имен агентов string cmd = "SELECT * FROM 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; } }
/// <summary> /// Выполняет запрос выборки набора строк. /// </summary> /// <param name="sql">Текст запроса к базе данных</param> /// <param name="connection">Строка подключения к базе данных</param> /// <returns>Возвращает набор строк в DataSet.</returns> public static MyResultData SqlReturnDataset(string sql) { MyResultData result = new MyResultData(); try { string connection = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=cp1251"; 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); }
private bool InitTable() { if (lastInit != null && (DateTime.Now - lastInit.Value).TotalHours < 24) { return(true); } lock (init_locker) { if (lastInit != null && (DateTime.Now - lastInit.Value).TotalHours < 24) { return(true); } if (string.IsNullOrEmpty(Config.DBConnString)) { return(false); } using (MySql.Data.MySqlClient.MySqlConnection dbconn = new MySql.Data.MySqlClient.MySqlConnection(Config.DBConnString)) { DateTime begindate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); DateTime endtime = begindate.AddDays(PreDayCount); dbconn.Open(); MySql.Data.MySqlClient.MySqlDataAdapter ada = new MySql.Data.MySqlClient.MySqlDataAdapter(" show create table timewatch;", dbconn); System.Data.DataTable tb = new System.Data.DataTable(); ada.Fill(tb); if (tb.Rows.Count == 0) { return(false); } string createsql = tb.Rows[0][1].ToString(); createsql = createsql.Substring(createsql.IndexOf('(')); ada = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + dbconn.Database + "' " + " and TABLE_NAME like 'timewatch________' order by TABLE_NAME desc limit 1;", dbconn); tb = new System.Data.DataTable(); ada.Fill(tb); if (tb.Rows.Count > 0) { string t = tb.Rows[0][0].ToString(); begindate = DateTime.Parse(string.Format("{0}-{1}-{2}", t.Substring(9, 4), t.Substring(13, 2), t.Substring(15, 2))); begindate = begindate.AddDays(1); } StringBuilder sb = new StringBuilder(); while (begindate.CompareTo(endtime) < 0) { sb.AppendFormat("CREATE TABLE {0} {1};\r\n", "`timewatch" + begindate.ToString("yyyyMMdd") + "`", createsql); begindate = begindate.AddDays(1); } if (sb.Length > 0) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.Connection = dbconn; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sb.ToString(); cmd.ExecuteNonQuery(); } } lastInit = DateTime.Now; return(true); } }
/// <summary> /// 查询 /// </summary> /// <returns></returns> public DataTable Select(MySql.Data.MySqlClient.MySqlCommand cmd) { if (cmd == null) { throw new ArgumentNullException("cmd"); } cmd.Connection = MysqlConnection.Current; lock (cmd.Connection) { try { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd)) { DataTable dt = new DataTable(); da.Fill(dt); return(dt); } } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } }
public void prendidestinazionicliente(string cliente) { clientedestinazione.Clear(); 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_destinazione where nomecliente = @nomecli"; cmd.Parameters.AddWithValue("@nomecli" , cliente); cmd.Connection = conn; destinazioniadapter.SelectCommand = cmd; destinazioniadapter.Fill(clientedestinazione); destinazionibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(destinazioniadapter); datagriddestinazioni.DataSource = clientedestinazione; conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException e) { conn.Close(); throw; } }
} // Fetch public CmsPersistentVariable[] FetchAllWithNamePrefix(string namePrefix) { if (namePrefix.Trim() == "") { return(new CmsPersistentVariable[0]); } using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", ""))) { OpenMySqlConnection(conn); string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables "; sql += " WHERE Name like @Name"; MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddWithValue("@Name", namePrefix + "%"); MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd); DataSet ds = new DataSet(); sqlDA.Fill(ds); List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>(); if (this.hasRows(ds)) { foreach (DataRow dr in ds.Tables[0].Rows) { arrayList.Add(GetFromRow(dr)); } // foreach row } return(arrayList.ToArray()); } } // FetchAllWithNamePrefix
public void prendidestinazionicliente(string cliente) { clientedestinazione.Clear(); 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_destinazione where nomecliente = @nomecli"; cmd.Parameters.AddWithValue("@nomecli", cliente); cmd.Connection = conn; destinazioniadapter.SelectCommand = cmd; destinazioniadapter.Fill(clientedestinazione); destinazionibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(destinazioniadapter); AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection(); foreach (DataRow row in clientedestinazione.Rows) { namesCollection.Add(row["indirizzo"].ToString()); } destinazionetextbox.AutoCompleteCustomSource = namesCollection; destinazionetextbox.AutoCompleteMode = AutoCompleteMode.SuggestAppend; destinazionetextbox.AutoCompleteSource = AutoCompleteSource.CustomSource; conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException e) { conn.Close(); throw; } }
public static DataSet ExecuteDataset(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etc da.FillSchema(ds, SchemaType.Source); da.Fill(ds); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); // Return the dataset return(ds); } } }
private static void RunQueries(object StateInfo) { for (int i = 0; i < 600; i++) { //dotnetuser - MySQL12345!! using (var conn = new MySql.Data.MySqlClient.MySqlConnection(@"Server=localhost;Database=employees;Uid=root")) //using (var comm = new MySql.Data.MySqlClient.MySqlCommand(@"SELECT (emp_no * 3.14) + 10, emp_no, first_name, last_name FROM employees", conn)) using (var comm = new MySql.Data.MySqlClient.MySqlCommand(@"SELECT CAST(mysqldotnet_string(""MySQLCustomClass.CustomMySQLClass"", ""MULTI"", first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name, first_name) AS char) FROM employees.employees", conn)) using (var dt = new System.Data.DataTable()) { comm.CommandTimeout = 600; conn.Open(); using (var da = new MySql.Data.MySqlClient.MySqlDataAdapter(comm)) { var start = DateTime.Now; try { da.Fill(dt); } catch (Exception e) { Console.WriteLine(e.Message); } var stop = DateTime.Now; lock (objLock) { Console.WriteLine("Retrieved {0} records in {1} milliseconds", dt.Rows.Count, (stop - start).TotalMilliseconds); } } conn.Close(); } } }
public FormPay(int contrId, int lastOverflowSum) { lastPverflow = lastOverflowSum; contragId = contrId; InitializeComponent(); dataTable = new DataTable(); string cmd = "SELECT expense.expenseDate, expense.debt, expense.expSum, expense.expenseId, expense.expType, " + "(case when expense.expType = 1 then (select 'Возврат') else (Select 'Продажа') end) as exType " + " FROM expense WHERE expense.contragentId = " + contrId + " AND (expense.debt > 0 or (status=1 and expType=1))"; using (MySql.Data.MySqlClient.MySqlConnection connect = new MySql.Data.MySqlClient.MySqlConnection(tposDesktop.Properties.Settings.Default.testConnectionString)) { MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(cmd, connect); DataTable dt = new DataTable(); MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(command); adapter.Fill(dt); //dt = dataTable; dgvExp.DataSource = dt; dgvExp.Columns["expSum"].HeaderText = "Сумма"; dgvExp.Columns["expenseDate"].HeaderText = "Дата"; dgvExp.Columns["debt"].HeaderText = "Долг"; dgvExp.Columns["expenseDate"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvExp.Columns["expenseId"].Visible = false; dgvExp.Columns["debt"].Width = 200; dgvExp.Columns["expSum"].Width = 200; dgvExp.Columns["expType"].Visible = false; dgvExp.Columns["exType"].HeaderText = "Тип"; dataTable = dt; } rbtnCash.Checked = true; lblError.Visible = false; }
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 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(); } }
/// <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; }
/// <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> /// 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> /// 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; }
/// <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; }
/// <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; }
/// <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; }
/// <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; }