private void lc(object sender, MouseEventArgs e) { c.Open(); DataSet ds = new DataSet(); string query = "select ID,pname,bill,pbill from pdetails where date=@bc "; OleDbCommand cmd = new OleDbCommand(query, c); cmd.Parameters.Add("@bc", OleDbType.Date).Value = dateTimePicker1.Value.Date; OleDbDataReader dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); /*ds.Tables.Add(dt); OleDbDataAdapter da = new OleDbDataAdapter(); da.Fill(dt);*/ dataGridView1.DataSource = dt.DefaultView; c.Close(); try { c.Open(); String str = @"SELECT SUM(pbill) FROM pdetails WHERE date=@bb;"; OleDbCommand comm2 = new OleDbCommand(str, c); comm2.Parameters.Add("@bb", OleDbType.Date).Value = dateTimePicker1.Value.Date; bill = Convert.ToDouble(comm2.ExecuteScalar()); label3.Text = bill.ToString() + "/-"; } catch(Exception ex) { MessageBox.Show("selected date miss match"); c.Close(); } c.Close(); }
public String GetLastIDInserted(String pStrDBTable) { String mStrSQL = "SELECT IDENT_CURRENT('" + pStrDBTable + "')"; String mStrLastIDInserted = ""; try { oCommand = new SqlCommand(mStrSQL,oConnection); oReader = oCommand.ExecuteReader(); oTable = new DataTable(); oTable.Load(oReader); foreach (DataRow oDataRow in oTable.Rows) { foreach (DataColumn oDataColumn in oTable.Columns) { mStrLastIDInserted = oDataRow[oDataColumn.ColumnName].ToString(); } } } catch (DataException Ex) { mStrLastIDInserted = ""; } oCommand = null; oReader = null; oTable = null; return mStrLastIDInserted; }
public HashSum(SqlDataReader reader, String secret = "PZS") { DataTable dt = new DataTable(); dt.Load(reader); dt.TableName = secret; this.dt = dt; }
protected void btnAdminCalEdit_Click(object sender, EventArgs e) { lblAdminCalBlockDate.Visible = true; txtAdminCalBlockDate.Visible = true; grdviewAdminCalendar.Visible = true; conn = new SqlConnection(connstr); cmd = new SqlCommand("select * from AdminCalendar", conn); System.Data.DataTable dt = new System.Data.DataTable(); conn.Open(); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { dt.Load(rdr); grdviewAdminCalendar.DataSource = dt; grdviewAdminCalendar.DataBind(); clear(); } else { dt.Load(rdr); grdviewAdminCalendar.DataSource = dt; grdviewAdminCalendar.EmptyDataText = "No Record Found"; grdviewAdminCalendar.DataBind(); } FillHolidayDataset(); conn.Close(); clear(); }
/// <summary> /// Unose se postojeće primke (bez njihovih stavki) u datagrid /// </summary> private void UnesiPodatkeUDatagrid() { string upit = "SELECT \"Primke\".\"idPrimke\", \"Primke\".datum::varchar(10) AS \"Datum dospijeća\", \"Primke\".dobavljac, \"Dobavljaci\".\"nazivDobavljaca\" AS \"Naziv dobavljača\", \"Primke\".placeno AS \"Plaćeno\" FROM \"Primke\" JOIN \"Dobavljaci\" ON \"Primke\".dobavljac = \"Dobavljaci\".\"idDobavljaca\" ORDER BY 2"; using (NpgsqlDataReader dr = DB.Instance.dohvati_podatke(upit)) { System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(dr); while (dr.Read()) { dt.Load(dr); } Primke_datagrid.DataSource = dt; //skrivamo stupce u kojima se nalaze ID primke i ID dobavljaca Primke_datagrid.Columns[0].Visible = false; Primke_datagrid.Columns[2].Visible = false; //zaključavamo datagrid kako bi spriječili direktne promjene u njemu (za to imamo obrazac) Primke_datagrid.ReadOnly = true; //sprječavamo da korisnik selektira više redaka Primke_datagrid.MultiSelect = false; //omogućavamo full row select umjesto single column select Primke_datagrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //sprječavamo korisnika da dodaje nove zapise u datagrid Primke_datagrid.AllowUserToAddRows = false; } }
public static DataTable CallQueryProcedure(string ProcudereName) { DataTable rows = new DataTable(); try { string ConnectionString = connection(); SqlConnection Conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(ProcudereName, Conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; Conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); rows.Load(rdr); //cmd.CommandText =ProcudereName; return rows; } catch (Exception ex) { return rows; } }
/// <summary> /// 排序 /// </summary> /// <param name="list"></param> /// <param name="orderByTypes"></param> /// <param name="orderByUnqueField"></param> /// <returns></returns> public static List <DataRow> OrderByDataRow(this IEnumerable <DataRow> list, List <OrderByDictionary> orderByTypes, OrderByDictionary orderByUnqueField) { orderByTypes.Add(orderByUnqueField); var dt = list.AsEnumerable().CopyToDataTable(); var guidType = typeof(Guid); var sqlGuidType = typeof(SqlGuid); System.Data.DataTable dtByConvertGuidToSqlGuid = new System.Data.DataTable(); foreach (DataColumn it in dt.Columns) { var isGuid = it.DataType == guidType; if (isGuid) { dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, sqlGuidType); } else { dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, it.DataType); } } //将dataTable中guid换转成sqlguid,这样排序才会和SQL一致 dtByConvertGuidToSqlGuid.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges); var view = dtByConvertGuidToSqlGuid.AsDataView(); view.Sort = string.Join(",", orderByTypes.Select(it => string.Format(" {0} {1} ", it.OrderByField, it.IsAsc ? "ASC" : "DESC"))); var reval = view.ToTable().AsEnumerable().ToList(); orderByTypes.Remove(orderByUnqueField); return(reval); }
/// <summary> /// Read data from database. /// </summary> /// <param name="query"></param> /// <returns></returns> protected DataTable ReadData(string query) { if (this._Connection.State != System.Data.ConnectionState.Open) { throw new Exception("Database not connected."); } try { using (SQLiteCommand command = new SQLiteCommand(query, this._Connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { using (System.Data.DataTable table = new System.Data.DataTable()) { table.Load(reader); return(table); } } } } catch (SQLiteException e) { MessageBox.Show(e.Message); } return(null); }
public DataTable getOrderByID(String pStrID) { DataTable dt = new DataTable(); SqlConnection conn = Program.CreateConnection(); SqlCommand cmd = new SqlCommand(); try { using (conn) { conn.Open(); cmd.Connection = conn; cmd.CommandText = "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM Orders WHERE OrderID='" + pStrID + "' " + "UNION " + "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM RejectedOrders WHERE OrderID='" + pStrID + "' " + "UNION " + "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM ApprovedOrders WHERE OrderID='" + pStrID + "'"; dt.Load(cmd.ExecuteReader()); } } catch (SqlException) { conn.Close(); } return dt; }
// executes a command and returns the results as a DataTable object public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.LogError(ex); throw; } finally { // Close the connection command.Connection.Close(); } return table; }
//Export private DataTable GetSimpleQueryResult(string Query) { //실패시 반환용 DataTable dataTable = new DataTable(); try { using (OracleCommand cmd = new OracleCommand(Query, conn)) { conn.Open(); using (OracleDataReader reader = cmd.ExecuteReader()) { dataTable.Load(reader); conn.Close(); return(dataTable); } } } catch (Exception ex) { //TODO: 외부로 직접적인 에러 메세지 노출되지 않도록 하기 //MessageBox.Show(ex.Message); conn.Close(); return(dataTable); } }
private void dataGridViewRequirementList_CellClick(object sender, DataGridViewCellEventArgs e) { if (e.RowIndex != dataGridViewRequirementList.RowCount - 1) { SqlDataReader dr = dbConnection.query("select productdetails.productID from productdetails where itemname='" + dataGridViewRequirementList.Rows[e.RowIndex].Cells[0].Value.ToString() + "'"); dr.Read(); string productID = dr[0].ToString(); string sqlqry = "select productionorder.orderID,customer.customername,productionorder.quantity,count(product.productID) from product,productionorder,customer where product.productID=productionorder.productID and customer.customer_ID=productionorder.customerID and productionorder.orderstatus=0 group by productionorder.orderstatus,productionorder.orderID,product.productID,productionorder.customerID,product.salestatus,productionorder.productID,productionorder.quantity,customer.customername having product.salestatus='INSTOCK' and product.productID='" + productID + "'"; dr = dbConnection.query(sqlqry); DataTable dt = new DataTable(); dt.Load(dr); dataGridViewRequirementByCust.DataSource = dt; if (gridviewbuttonstat == false) { DataGridViewButtonColumn dgbut = new DataGridViewButtonColumn(); dgbut.HeaderText = "sell product"; dgbut.Text = "Sell"; dgbut.UseColumnTextForButtonValue = true; dataGridViewRequirementByCust.Columns.Add(dgbut); dataGridViewRequirementByCust.Columns[2].HeaderText = "Quantity Required"; dataGridViewRequirementByCust.Columns[3].HeaderText = "Stock Available"; dataGridViewRequirementByCust.Update(); gridviewbuttonstat = true; this.Width = this.Width + 410; dataGridViewRequirementByCust.Visible = true; label3.Visible = true; } } }
private void InfoUser() { using (SqlConnection conn = new SqlConnection(@"Data Source=AYE\SQLEXPRESS;Initial Catalog=Bank;Integrated Security=True")) { try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT [Name], [Surname],[Patronymic],[Phone],[Adress],[E-Mail],[DateOfBirth],[PlaceOfBirth] FROM [User] WHERE [IDUser] = @IdUser "; cmd.Parameters.AddWithValue(@"IdUser", UserId); SqlDataReader reader = cmd.ExecuteReader(); table = new System.Data.DataTable(); table.Load(reader); reader.Close(); Head.Text = table.Rows[0][1].ToString() + " " + table.Rows[0][0].ToString() + " " + table.Rows[0][2].ToString(); LabAdress.Text = table.Rows[0][4].ToString(); LabPhone.Text = table.Rows[0][3].ToString(); LabEmail.Text = table.Rows[0][5].ToString(); LabDataOfBirth.Text = (table.Rows[0][6].ToString()); LabDataOfBirth.Text = LabDataOfBirth.Text.Remove(10); LabPlaceOfBirth.Text = table.Rows[0][7].ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } } }
public bool ExecuteProc(String strProcName, ref System.Data.DataTable table, params SqlParameter[] aryProcParams) { if (m_dbConnect.State == System.Data.ConnectionState.Closed) { m_dbConnect.Open(); } SqlCommand dbCmd = m_dbConnect.CreateCommand(); dbCmd.CommandText = strProcName; dbCmd.CommandType = CommandType.StoredProcedure; if (aryProcParams != null) { dbCmd.Parameters.AddRange(aryProcParams); // Past the params } SqlDataReader dbReader = dbCmd.ExecuteReader(); if (dbReader == null) { return(false); } table.Load(dbReader); dbReader.Close(); return(true); }
public static System.Data.DataTable GetTableFromServerArgs(string procedureName, params string [] ArgsValues) { SqlConnection sqlConnection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; System.Data.DataTable table = new System.Data.DataTable(); cmd.CommandText = procedureName; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 1000; cmd.Connection = sqlConnection; sqlConnection.Open(); foreach (string param in ArgsValues) { string paramName = param.Substring(0, param.IndexOf("-")); string paramValue = null; if (param.IndexOf("-") != param.Length - 1) { paramValue = param.Substring(param.IndexOf("-") + 1, param.Length - param.IndexOf("-") - 1); } cmd.Parameters.AddWithValue(paramName, paramValue); } reader = cmd.ExecuteReader(); table.Load(reader); sqlConnection.Close(); return(table); }
/// <summary> /// 执行查询返回DataTable /// </summary> /// <param name="sql">Sql语句</param> /// <returns>成功返回DataTable,失败则返回 null</returns> static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn) { if (Conn is System.Data.SQLite.SQLiteConnection) { var cmd = Conn.CreateCommand() as System.Data.SQLite.SQLiteCommand; cmd.CommandText = sql; cmd.CommandTimeout = 180; var da = new System.Data.SQLite.SQLiteDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } if (Conn is OracleConnection) { var cmd = Conn.CreateCommand() as OracleCommand; cmd.CommandText = sql; cmd.CommandTimeout = 180; var da = new OracleDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } else { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.IDataReader reader = ExecuteQuery(sql, Conn); dt.Load(reader); return(dt); } }//ExecuteQueryToDataTable(string sql)
private void ShowAppointmentData() { conn = new SqlConnection(connstr); cmd = new SqlCommand("SELECT * from Appointment WHERE Patient_Name LIKE @Name AND Dentist=@Dentist", conn); cmd.Parameters.AddWithValue("@Name", ViewAppointmentTextBox.Text + "%"); cmd.Parameters.AddWithValue("@Dentist", Globals.CurrentUser); System.Data.DataTable dt = new System.Data.DataTable(); try { conn.Open(); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { dt.Load(rdr); AppointmentDataGridView.DataSource = dt; } else { } } catch (Exception) { MessageBox.Show("No data in table"); } }
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { Console.WriteLine("Worker Complete"); if (errorMsg == null) { // dgv.Columns.Clear(); // dgv.Rows.Clear(); try { System.Data.DataTable dataTable = new System.Data.DataTable(); dataTable.Load(reader); dgv.DataSource = dataTable; lblBottomMessage.Text = "Results: " + dataTable.Rows.Count; MessageBox.Show("Search Complete", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ee) { lblBottomMessage.Text = "Failed to display results"; MessageBox.Show("Failed to display results\n" + ee.Message, "Oops", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { lblBottomMessage.Text = errorMsg; } }
public OperationResult <List <DataRow> > SubmitQuery(string sql, CommandType commandType, IEnumerable <KeyValuePair <string, object> > parameters) => PerformDataBaseCall(sql, commandType, parameters, command => { System.Data.DataTable result = new System.Data.DataTable(); using (var reader = command.ExecuteReader()) result.Load(reader); return(result.AsEnumerable().ToList()); });
public static string GetName(int id) { string _out; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["PrimaryDatabase"].ConnectionString); con.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT name FROM States WHERE id =" + Convert.ToString(id); using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr != null && sdr.HasRows) { DataTable dt = new DataTable(); dt.Load(sdr); _out = dt.Rows[0]["name"].ToString(); return _out; } } } return null; }
/// <summary> /// Selects and retrieves values from the database /// </summary> /// <param name="query">The selection statement</param> /// <returns>A DataTable with the retrieved values></returns> public static DataTable RetrieveQuery(string query) { if (Regex.IsMatch(query, @"-{2,}")) { throw new SQLInjectionException(); } using (OracleConnection c = new OracleConnection(@connectionstring)) { try { c.Open(); OracleCommand cmd = new OracleCommand(@query); cmd.Connection = c; try { OracleDataReader r = cmd.ExecuteReader(); DataTable result = new DataTable(); result.Load(r); c.Close(); return result; } catch (OracleException e) { Console.Write(e.Message); throw; } } catch (OracleException e) { Console.Write(e.Message); return new DataTable(); } } }
private void LoadOperators() { var dt = new System.Data.DataTable(); if (dataGridView1.DataSource != null) { dataGridView1.DataSource = null; } var q = "select * from operators"; using (var con = new SqlConnection(Config.ConString)) { var cmd = new SqlCommand(q, con); con.Open(); var dr = cmd.ExecuteReader(); dt.Load(dr); con.Close(); dr.Close(); } dataGridView1.DataSource = dt; dataGridView1.Columns[0].Visible = false; dataGridView1.Columns[1].Width = 60; dataGridView1.Columns[2].Width = 150; dataGridView1.Columns[3].Width = 150; }
public static DataTable datatablemonths() { SqlConnection con = new SqlConnection(cs); DataTable table = new DataTable(); try { SqlCommand cmd = new SqlCommand("getmonths", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { table.Load(dr); dr.Close(); } } catch (Exception) { } finally { con.Close(); } return table; }
//search private void searchProjectTxtBox_TextChanged(object sender, EventArgs e) { string qry = ""; string projectNameTxt = searchProjectTxtBox.Text; qry = "Select p.type, p.date, p.Category,p.amount, s.first_name from office_expenses p inner join staff s on s.staff_id=p.staff_id where s.first_name like '%" + projectNameTxt + "%' or p.Category like '%" + projectNameTxt + "%'"; try { MySqlDataReader reader = DBConnection.getData(qry); if (reader.HasRows) { System.Data.DataTable table1 = new System.Data.DataTable(); table1.Load(reader); metroGrid1.DataSource = table1; } else { reader.Close(); } } catch (Exception exc) { MessageBox.Show("Invalid data!\n" + exc.StackTrace, "Project finder", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db"); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch { // Catching exceptions is for communists } return dt; }
public System.Data.DataTable SelectUsersInfo(int ID) { string query = "select * from fullusersinfo as a where a.ID <>" + ID; System.Data.DataTable tempUserList = new System.Data.DataTable(); if (this.openConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, dbConnect); MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list tempUserList.Load(dataReader); //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return(tempUserList); } else { return(tempUserList); } }
public static Boolean UploadClients() { var db = new BHelpContext(); var filePath = @"c:\TEMP\BH Food Client List-Table 1.csv"; DataTable csvtable = new DataTable(); using (CsvReader csvReader = new CsvReader(new StreamReader(filePath), true)) { csvtable.Load(csvReader); } foreach (DataRow row in csvtable.Rows) { Client client = new Client() { Active = true, LastName = row[0].ToString(), FirstName = row[1].ToString(), StreetNumber = row[2].ToString(), StreetName = row[3].ToString(), City = row[4].ToString(), Zip = row[5].ToString(), Phone = row[6].ToString(), Notes = row[13].ToString() }; db.Clients.Add(client); db.SaveChanges(); //System.Diagnostics.Debug.WriteLine(client.FirstName, client.LastName); } return(true); }
public System.Data.DataTable SelectGroupsInfo() { string query = "select * from groupinfoview"; System.Data.DataTable tempList = new System.Data.DataTable(); if (this.openConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, dbConnect); MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list tempList.Load(dataReader); //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return(tempList); } else { return(tempList); } }
private void PopulateLocation() { DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString)) { string query = "Select * from Locations"; SqlCommand cmd = new SqlCommand(query, con); if (con.State != ConnectionState.Open) { con.Open(); } dt.Load(cmd.ExecuteReader()); } direcFrom.DataSource = dt; direcFrom.DataTextField = "LocName"; direcFrom.DataValueField = "LocID"; direcFrom.DataBind(); direcTo.DataSource = dt; direcTo.DataTextField = "LocName"; direcTo.DataValueField = "LocID"; direcTo.DataBind(); }
public System.Data.DataTable SelectRolesInfo() { string query = "select a.id, a.name as `Название`, IF((`a`.`allowRead` = 1),'Да','Нет') AS `Чтение`, IF((`a`.`allowWrite` = 1), 'Да', 'Нет') AS `Запись`,IF((`a`.`Admin` = 1), 'Да', 'Нет') AS `Полный доступ` from roles as a"; System.Data.DataTable tempList = new System.Data.DataTable(); if (this.openConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, dbConnect); MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list tempList.Load(dataReader); //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return(tempList); } else { return(tempList); } }
public static string GetDesc() { string _out = string.Empty; using (SqlConnection con = new SqlConnection()) { con.ConnectionString = Connections.GetConnection; con.Open(); using (SqlCommand cmd = new SqlCommand("SELECT DESCRIPTION FROM Table1 WHERE Id=1", con)) { cmd.CommandType = System.Data.CommandType.Text; try { using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr != null && sdr.HasRows) { DataTable dt = new DataTable(); dt.Load(sdr); _out = dt.Rows[0][0].ToString(); return _out; } } } catch (Exception e) { return e.Message.ToString(); } } } return _out; }
private System.Data.DataTable getLabelDataFromView() { var strSQL = "Select * from LABEL where [KEY] = @sqlPara order by _NullFlags"; System.Data.DataTable result = new System.Data.DataTable(); var paraKey = new SqlParameter("@sqlPara", SqlDbType.Char, 14); paraKey.Value = packingKey; var sqlConn = new SqlConnection(dbConnStr); try { sqlConn.Open(); SqlCommand sCmd = new SqlCommand(strSQL, sqlConn); sCmd.Parameters.Add(paraKey); var value = sCmd.ExecuteReader(); result.Load(value); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "執行 execQuery() 出現問題"); } finally { sqlConn.Close(); } return(result);; }
public void BindPrimaryGrid() { try { int clientId = Convert.ToInt32(Session["ClId"]); DateTime from = Convert.ToDateTime(txtFromDate.Text); DateTime to = Convert.ToDateTime(txtToDate.Text); BLSelectProject blselPr = new BLSelectProject(); SqlDataReader dr = blselPr.GetProjectByAddInv1(clientId, from, to); DataTable dt = new DataTable(); if (dr.HasRows) { dt.Load(dr); GdVwSelPrjct.DataSource = dt.DefaultView; GdVwSelPrjct.DataBind(); dr.Close(); } else { GdVwSelPrjct.DataSource = null; GdVwSelPrjct.DataBind(); lblErrorMsg.Text = "Records not Available"; } } catch (Exception ex) { lblCatchError.Text = ex.Message.ToString(); } }
//Поиск абонента по ОКПО protected void lbFindOKPO_Click(object sender, EventArgs e) { /* * Поиск по базе водосбыта */ if (tbCorporateOKPO.Text.Length >= 4) { try { FbConnection fbCn = new FbConnection("Database=water_wdk;Server=db3;User=viewer_;Password=VIEWER_;Role=viewer;"); FbDataAdapter fbDa = new FbDataAdapter(); FbCommand fbCmd = new FbCommand(); //DataTable dt = new DataTable(); fbCn.Open(); fbCmd = new FbCommand(string.Format(@"select delzero(d.id_dog)||' / '||r.name_ray||' район' dogovor,d.id_doc, d.okpo,d.bank,d.r_s,d.mfo, h.full_name,h.ur_address, case k.gr when 3 then 1 when 4 then 1 when 5 then 1 when 6 then 1 else 0 end budzhet,h.inn, h.id_svid, d.info_1 kontact, d.v_litse,d.na_osnovanii,d.otv_litso,d.contact_phone,d.ot_imeni,h.sys_no,s.name_no from dogovor d left join rayon r on r.id_ray=d.id_ray left join history h on h.id_doc=d.id_doc left join history n on n.id_doc=h.id_doc and (n.datebeg>h.datebeg or n.datebeg=h.datebeg and n.kod>h.kod) left join kods k on k.podgr=h.podgr left join spr_sys_no s on s.id_no=h.sys_no where d.okpo like '%'||{0}||'%' and d.date_close_d is null and n.kod is null", tbCorporateOKPO.Text), fbCn); FbDataReader dr = fbCmd.ExecuteReader(); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(dr); if (dt.Rows.Count > 0) { repCorporate.DataSource = dt; repCorporate.DataBind(); litSearch.Text = ""; } else { repCorporate.DataBind(); litSearch.Text = "Нет данных"; } dr.Close(); fbCn.Close(); } catch (Exception ex) { SetMessege("Ошибка", "Сбой подключения к базе данных <br>" + ex.Message); } } else { SetMessege("Предупреждение", "Для поиска необходимо ввести минимум 4 символа."); } }
private void ourMediaForm_Load(object sender, EventArgs e) { string qString = @"SELECT * FROM Genre;"; string cString = @"Server=tcp:xj78qcdgml.database.windows.net,1433;Database=OurMedia;User ID=john@xj78qcdgml;Password=25b7Xl21211;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"; DataTable dTable = new DataTable(); //make sure only one set of data is populated if(genreCombobox.Items.Count > 0) genreCombobox.Items.Clear(); SqlConnection sConnection = new SqlConnection(cString); sConnection.Open(); using(SqlCommand sCommand = new SqlCommand(qString, sConnection)) { SqlDataReader sReader = sCommand.ExecuteReader(); dTable.Load(sReader); } genreCombobox.DataSource = dTable; genreCombobox.ValueMember = dTable.Columns[0].ToString(); genreCombobox.DisplayMember = dTable.Columns[1].ToString(); sConnection.Close(); }
public void LoadDataReader(IDataReader pDataReader) { DataTable DT = new System.Data.DataTable(); DT.Load(pDataReader); dataSet.Tables.Add(DT); }
private void ShowAllBookBank() { string sqlUnit; sqlUnit = "SELECT * FROM BANKDOC ORDER BY BankDocId"; SqlDataReader dr; // สร้าง data reader ชื่อ dr com = new SqlCommand(); com.CommandType = CommandType.Text; com.CommandText = sqlUnit; // ดึง sqlunit com.Connection = Conn; // เชื่อมกะ db dr = com.ExecuteReader(); // รันชุดคำสั่ง if (dr.HasRows) { DataTable dt = new DataTable(); // สร้าง object datatable มาชื่อ dt dt.Load(dr); // ให้ dt โหลดข้อมูลออกมา //CheckResult = dt.Rows.Count; dgvSelectBookBank.DataSource = dt; // แสดงรายชื่อ ลงไปยัง dgv unit ( Datagrid view) FormatdgvHead();// FormatHeadDgv } else { //CheckResult = 0; dgvSelectBookBank.DataSource = null; // ล้างข้อมูลออก } dr.Close(); // ปิด object dr }
private void dateTimeTo_ValueChanged_1(object sender, EventArgs e) { DateTime dateTime = dateTimeTo.Value; if (dateTime < dateTimefrom.Value) { MessageBox.Show("Invalid 'from' date"); } else { try { MySqlDataReader reader = DBConnection.getData("SELECT u.log_id as LogID, s.u_name as Username, u.logged_in_dateTime as LoginTime, u.logged_out_datetime as LogoutTime," + " u.detail as Detail, u.ip as IP FROM user_log u, user s where s.user_id = u.user_id and date(u.logged_in_dateTime) >= '" + dateTimefrom.Value.ToString("yyyy/MM/dd") + "' and date(u.logged_out_dateTime) <= '" + dateTimeTo.Value.ToString("yyyy/MM/dd") + "';"); if (reader.HasRows) { System.Data.DataTable table = new System.Data.DataTable(); table.Load(reader); LogGrid.DataSource = table; } else { reader.Close(); } } catch (Exception exc) { MessageBox.Show("Invalid data!\n" + exc.StackTrace, "User Log finder", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
/// <summary> /// Get all designation information /// </summary> /// <returns></returns> public DataTable GetDesignationAll() { DataTable tblEmpInfo = new DataTable(); SqlDataReader rdr = null; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT [Id],[DesignationName] FROM [dbo].[Designation]"; try { if (sqlConn.State == ConnectionState.Closed) sqlConn.Open(); rdr = cmd.ExecuteReader(); tblEmpInfo.Load(rdr); } catch (Exception exp) { throw (exp); } finally { if (sqlConn.State == ConnectionState.Open) sqlConn.Close(); } return tblEmpInfo; }
private void metroTextBox1_TextChanged(object sender, EventArgs e) { DataTable dt = new DataTable(); try { MySqlDataReader reader = DBConnection.getData("SELECT u.log_id as LogID, s.u_name as Username, u.logged_in_dateTime as LoginTime, u.logged_out_datetime as LogoutTime, " + " u.detail as Detail, u.ip as IP FROM user_log u, user s where s.user_id = u.user_id and s.u_name LIKE'%" + metroTextBox1.Text.ToString() + "%'"); if (reader.HasRows) { System.Data.DataTable table = new System.Data.DataTable(); table.Load(reader); LogGrid.DataSource = table; } else { reader.Close(); } } catch (Exception exc) { MessageBox.Show("Invalid data!\n" + exc.StackTrace, "User Log finder", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void fillComboSemana() { // Step 1 MySqlConnection MyConexion = new MySqlConnection(Conexion.ConectionString); // Step 2 MyConexion.Open(); // Step 3 MySqlCommand MyCommand = new MySqlCommand("SELECT idsemana, semana FROM semanas ORDER BY idsemana ASC", MyConexion); // Step 4 MySqlDataReader MyReader; MyReader = MyCommand.ExecuteReader(); // Step 5 DataTable MyDataTable = new DataTable(); MyDataTable.Columns.Add("idsemana", typeof(int)); MyDataTable.Columns.Add("semana", typeof(string)); MyDataTable.Load(MyReader); // Step 6 cmbSemana.ValueMember = "idsemana"; cmbSemana.DisplayMember = "semana"; cmbSemana.DataSource = MyDataTable; // Step 7 MyConexion.Close(); }
public d.DataTable GetAllProducts() { var dt = new d.DataTable(); using (var conn = new ss.SqlConnection(this.ConnString)) { using (var cmd = new ss.SqlCommand("select * from Products", conn)) { try { conn.Open(); dt.Load(cmd.ExecuteReader()); } catch (Exception ex) { string exceptionMessage = ex.Message; string innerExceptionMessage = (ex.InnerException is Exception) ? ex.InnerException.Message : ""; dt.Rows.Add(dt.NewRow()); dt.Columns.Add(new d.DataColumn("ErrorMessage")); dt.Rows[0][0] = exceptionMessage + " " + innerExceptionMessage; } } } return(dt); }
/************************************************************************ * Function: 根据sql语句获取DataTable * In: string sql, DbConnection * Out: DataTable * Author: HYF * Date: 2017-04-28 ************************************************************************/ public System.Data.DataTable GetDataTable(string sql) { if (this.DBConn == null) { //Global.LogServer.Add(new LogInfo("Error", "DBUnit:GetDataTable 数据库连接对象为空", (int)EnumLogLevel.ERROR)); return(null); } try { if (this.DBConn.State == System.Data.ConnectionState.Closed) { this.DBConn.Open(); } if (this.DBConn.State == ConnectionState.Open) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.IDataReader reader = GetDataReader(sql); dt.Load(reader); return(dt); } } catch (Exception ex) { //Global.LogServer.Add(new LogInfo("Error", "DBUnit:GetDataTable 错误信息:" + ex.Message, (int)EnumLogLevel.ERROR)); return(null); } return(null); }
public DataTable ExecuteReader(string query) { try { MySqlCommand myCommand = new MySqlCommand(query, myConnection); MySqlDataReader reader = myCommand.ExecuteReader(); if (reader.HasRows) { System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(reader); reader.Close(); return(dt); } else { reader.Close(); return(null); } } catch (MySqlException ex) { MessageBox.Show(ex.Message); return(null); } }
//--------------------------------------------------- //Obtener PerInterface "Get_PerInterface" //-------------------------------------------------- public DataTable Get_PerInterface() { DataTable dt = new DataTable(); try { clsConection Obj = new clsConection(); string Cadena = Obj.GetConexionString("Naylamp"); using (SqlConnection cn = new SqlConnection(Cadena)) { cn.Open(); using (SqlCommand cm = new SqlCommand()) { cm.CommandText = "[usp_Get_PerInterface]"; cm.CommandType = CommandType.StoredProcedure; cm.Connection = cn; using (SqlDataReader dr = cm.ExecuteReader()) dt.Load(dr); } } } catch (Exception) { throw; } return dt; }
//Get_CtaCteNumeracion public DataTable Get_CtaCteNumeracion(BE_ReqCtaCteNumeracion Request) { DataTable dt = new DataTable(); try { clsConection Obj = new clsConection(); string Cadena = Obj.GetConexionString("Naylamp"); using (SqlConnection cn = new SqlConnection(Cadena)) { cn.Open(); using (SqlCommand cm = new SqlCommand()) { cm.CommandText = "usp_Get_CtaCteNumeracion"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.AddWithValue("cPerJurCodigo", Request.cPerJurCodigo); cm.Connection = cn; using (SqlDataReader dr = cm.ExecuteReader()) dt.Load(dr); } } } catch (Exception) { throw; } return dt; }
/// <summary> /// load all account /// </summary> /// <param name="table"></param> public void LoadAccount(ref DataGridView table) { string query = "SELECT ID, TITLE, CREATED, MODIFIED FROM ACCOUNT"; if (this._Connection.State != System.Data.ConnectionState.Open) { throw new Exception("Database not connected."); } try { using (SQLiteCommand command = new SQLiteCommand(query, this._Connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { using (System.Data.DataTable dt = new System.Data.DataTable()) { dt.Load(reader); table.DataSource = dt; } } } } catch (SQLiteException e) { MessageBox.Show(e.Message); } }
/// <summary>Carrega um <see cref="DataTable" /> a partir de um <see cref="SqlCommand" />.</summary> /// <param name="comando">Objeto a partir do qual o <see cref="DataTable" /> é carregado.</param> /// <returns>Retorna o novo <see cref="DataTable" />.</returns> public static DataTable GeraDataTable(SqlCommand comando) { var dataTable = new DataTable(); SqlDataReader reader = comando.ExecuteReader(); dataTable.Load(reader); return dataTable; }
public void BindData() { try { DataTable dtData = new System.Data.DataTable(); using (SqlConnection dbCon = new SqlConnection(@"Data Source=DESKTOP-S9RU5TT\USMAN;Initial Catalog=Lab11;Integrated Security=True")) { using (SqlCommand cmdGetData = new SqlCommand("SELECT TOP 4 * FROM Book", dbCon)) { if (dbCon.State == ConnectionState.Closed) { dbCon.Open(); } using (SqlDataReader drGetData = cmdGetData.ExecuteReader()) { dtData.Load(drGetData); for (int iCount = 0; iCount < dtData.Rows.Count; iCount++) { byte[] storedImage = (byte[])dtData.Rows[iCount]["Image"]; Image newImage; MemoryStream stream = new MemoryStream(storedImage); newImage = Image.FromStream(stream); } } } } } catch (Exception ex) { } }
private void ExportAllRecord(ref System.Data.DataTable dt) { if (m_RecordModule.DatabaseConnection.State == System.Data.ConnectionState.Closed) { m_RecordModule.DatabaseConnection.Open(); } try { SqlCommand cmd = new SqlCommand("Proc_Record_ExportRecord", m_RecordModule.DatabaseConnection); cmd.CommandType = CommandType.StoredProcedure; SqlParameter cmdParameter1 = new SqlParameter( "@LanguageCode", SqlDbType.Char, 3, ParameterDirection.Input, true, 0, 0, "@LanguageCode", DataRowVersion.Current, m_strActiveLanguage); cmd.Parameters.Add(cmdParameter1); SqlDataReader dr = cmd.ExecuteReader(); dt.Load(dr); dr.Close(); } catch (System.Exception e) { DevComponents.DotNetBar.MessageBoxEx.Show(e.Message); } }
public static List<NetworkProcessor> CreateNetProcs() { AppLog.WriteLine(5, "STATUS", "Entered IRCStatistician.Program.CreateNetProcs()."); List<NetworkProcessor> returnList = new List<NetworkProcessor>(); // Get the tbn_networks table MySqlCommand NetworkCmd = new MySqlCommand("SELECT * FROM " + Config.SQLTablePrefix + "networks", MyDBConn.Connection); MySqlDataReader NetworkDataReader = NetworkCmd.ExecuteReader(); DataTable NetworkTable = new DataTable(); NetworkTable.Load(NetworkDataReader); NetworkDataReader.Close(); // Get the tbn_channels table MySqlCommand ChannelCmd = new MySqlCommand("SELECT * FROM " + Config.SQLTablePrefix + "channels", MyDBConn.Connection); MySqlDataReader ChannelDataReader = ChannelCmd.ExecuteReader(); DataTable ChannelTable = new DataTable(); ChannelTable.Load(ChannelDataReader); ChannelDataReader.Close(); // Organize them together. foreach (DataRow CurNetwork in NetworkTable.Rows) { NetworkProcessor tempLW = new NetworkProcessor(CurNetwork); foreach (DataRow CurChannel in ChannelTable.Rows) { if (Convert.ToInt32(CurChannel["networkid"]) == tempLW.Network.Id) { tempLW.Network.Channels.Add(CurChannel["name"].ToString(), new Channel(CurChannel["name"].ToString())); } } returnList.Add(tempLW); } return returnList; }
protected override CommandResult <DataTable> OnExecute(object commandParameter) { var result = new CommandResult <DataTable>(); var param = commandParameter as LoadPinOrderParameter; using (CoreContext context = new CoreContext()) { var acc = param.MemberAccount; var conn = context.Database.GetDbConnection(); conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = $@"select p.Recid, p.MIN_COUNT,p.CONFIG,p.MAX_DATE,p.`STATUS`,o.CREATE_DATE,o.ORDER_NO from pin_order o left join pin_info p on o.MAIN_ID=p.RECID where o.`STATUS`=1 and p.`STATUS`!=0 and o.MEMBER_ACCOUNT=@p0 ORDER BY p.recid desc limit {param.PageSize * (param.PageIndex - 1)},{param.PageSize}"; cmd.Parameters.Add(new MySqlParameter("@p0", acc)); var reader = cmd.ExecuteReader(); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(reader); reader.Close(); conn.Close(); result.Data = dt; //result.Data = context.PinOrder.FromSql(@"select p.END_DATE,p.MIN_COUNT,p.CONFIG,p.MAX_DATE,p.`STATUS`,o.CREATE_DATE from pin_order o left join pin_info p on o.MAIN_ID=p.RECID // where o.`STATUS`=1 and p.`STATUS`!=0 and o.MEMBER_ACCOUNT=@p0", param.MemberAccount).ToList(); } return(result); }
private DataTable getCategories(out DataTable dtCategories) { dtCategories = new DataTable(); try { using (SqlConnection nwndConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString)) { SqlCommand cmdCategories = new SqlCommand(); cmdCategories.Connection = nwndConnection; cmdCategories.CommandText = "SELECT * FROM CATEGORIES"; cmdCategories.CommandType = System.Data.CommandType.Text; nwndConnection.Open(); dtCategories.Load(cmdCategories.ExecuteReader()); nwndConnection.Close(); dtCategories.Columns["CategoryID"].ReadOnly = true; } } catch (Exception ex) { throw; } return dtCategories; }
private void montaGrid() { reader = comandoSql.ExecuteReader(); _dataTable = new System.Data.DataTable(); _dataTable.Load(reader); gridProducao.DataSource = _dataTable; }
/** * @author Felipe de Oliveira * * Retorna um <code>DataTable</code> apartir de uma string que indica a tabela. * São necessários o Nome da tabela. * */ public DataTable RetornarTabela(string tabela) { //Abrindo conexão com o banco de dados DbConnection conexao = GetConnection(); DbCommand comando = GetComando(conexao); //Informando parametros a serem passados comando.CommandText = CommandType.Text.ToString(); comando.CommandText = "SELECT * FROM " + tabela + ""; //Criando instancia de um dataTable que armazenara toda a tabela DataTable dataTable = new DataTable(); try { DbDataReader reader = GetDataReader(comando); dataTable.Load(reader); //Fechando o DbDataReader reader.Close(); reader.Dispose(); comando.Dispose(); } catch(Exception e) { MessageBox.Show("Erro ao tentar mostrar tabela. Mais informações:\n" + e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } //Fechando a conexão com o banco de dados conexao.Close(); conexao.Dispose(); return dataTable; }
public DataTable query(String sql) { lock (syncQuery) { SqlConnection sqlConn = getConnection(); if (sqlConn.State != ConnectionState.Open) { sqlConn.Open(); } DataTable dt = new DataTable(); SqlCommand sqlcmd = new SqlCommand(sql, sqlConn); try { SqlDataReader dr = sqlcmd.ExecuteReader(); dt.Load(dr); } catch (SqlException ae) { log.Info(ae.Message.ToString()); log.Info(sql); } finally { sqlcmd.Dispose(); sqlConn.Close(); } return dt; } }
protected void Load_Grid() { string connectionString; connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString(); SqlConnection con = new SqlConnection(connectionString); try { con.Open(); } catch (Exception) { con.Close(); return; throw; } SqlCommand cmd = new SqlCommand("SELECT C.*, A.agent_name FROM Customers AS C INNER JOIN Users AS U ON C.user_id = U.user_id INNER JOIN Employees AS E ON E.user_id = U.user_id INNER JOIN Agents AS A ON A.agent_id = E.agent_id", con); SqlDataReader dr = cmd.ExecuteReader(); DataTable tablo = new DataTable(); tablo.Load(dr); CustomerGrid.DataSource = tablo.DefaultView; CustomerGrid.DataBind(); dr.Close(); con.Close(); }
public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams) { var dt = new DataTable(); using (var conn = new SqlCeConnection(connStr)) using (var cmd = new SqlCeCommand(query, conn)) { try { SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString); engine.Upgrade(conn.ConnectionString); } catch { } cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sqlParams); conn.Open(); dt.Load(cmd.ExecuteReader()); } return dt; }
/// <summary> /// Reads the selected file and places the contents into a DataTable. /// </summary> /// <returns>Datatable of students in file</returns> public DataTable ReadFile() { DataTable data = new DataTable(); try { using (cmd = con.CreateCommand()) { cmd.CommandText = string.Format("SELECT * from [{0}]", file.Name); con.Open(); using (dr = cmd.ExecuteReader()) { data.Load(dr); } } } catch (OleDbException oExe) { Debug.WriteLine(oExe.Message); } finally { con.Close(); } return data; }
public static DataTable GetUser(string username, int userID) { DataTable user = new DataTable(); using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString)) { using (SqlCommand objComm = new SqlCommand("SELECT userID, firstName, lastName, address, city, phone, email, username FROM [user]", objConn)) { if (username != string.Empty) { objComm.CommandText += " WHERE username=@username"; objComm.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username; } else if (userID > 0) { objComm.CommandText += " WHERE userID=@userID"; objComm.Parameters.Add("@userID", SqlDbType.Int).Value = userID; } objConn.Open(); using (SqlDataReader reader = objComm.ExecuteReader()) { if (reader.HasRows) { user = new DataTable(); user.Load(reader); } } } } return user; }