public cSetTime(int id, string connStr) { ID = -1; db db = new db(connStr); DbDataReader dbRdr; try { if (!db.dbOpen()) { throw new Exception(); } dbRdr = db.getReader("SELECT * FROM ATTENDANCE WHERE ID = " + id); dbRdr.Read(); setTime(dbRdr); dbRdr.Close(); } catch (Exception ex) { MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных"); id = -1; } finally { db.dbClose(); } }
static public DataSet getPerson(string connStr) { string query = "SELECT a.PERSONID, (a.NAME ||' '|| a.FIRSTNAME ||' '|| a.SECONDNAME) FIO FROM PERSON a"; db db = new db(connStr); DataSet ds = new DataSet(); DataTable table = new DataTable(); db.dbOpen(); table.Load(db.getReader(query)); ds.Tables.Add(table); db.dbClose(); return(ds); }
/*public void getTime(int cardid) * { * db db; * DbDataReader dbRdr; * * * cViewSetTime STL; * SetTimeArr.Clear(); * string query = "SELECT a.id, a.CTRLAREAID, a.CARDID, a.PERSONID, a.ADATETIME, a.IS_ENTRANCE, a.COMMENT, (p.name || ' ' || p.FIRSTNAME || ' ' || p.SECONDNAME) as FIO "+ * "FROM ATTENDANCE a "+ * "INNER JOIN PERSON p on a.CARDID = p.PERSONID "+ * "WHERE CARDID = "+cardid + * "order by a.id"; * db = new db(); * try * { * if (!db.dbOpen()) throw new Exception("Ошибка подключения к БД."); * dbRdr = db.getReader(query); * while (dbRdr.Read()) * { * STL = new cViewSetTime(); * STL.setViewSetTime(dbRdr); * SetTimeArr.Add(STL); * } * dbRdr.Close(); * db.dbClose(); * } * catch (Exception ex) * { * MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных"); * } * finally { db.dbClose(); } * } */ public DataSet getTimeE(int cardid, DateTime?startDate, DateTime?endDate, string connStr) { string query = "SELECT a.id, a.CTRLAREAID, a.CARDID, a.PERSONID, a.ADATETIME, a.IS_ENTRANCE, a.COMMENT, (p.name || ' ' || p.FIRSTNAME || ' ' || p.SECONDNAME) as FIO " + "FROM ATTENDANCE a " + "INNER JOIN PERSON p ON a.PERSONID = p.PERSONID " + "INNER JOIN CARD c ON p.PERSONID = c.PERSONID_IMP " + "WHERE c.PERSONID_IMP = " + cardid; if (startDate != null && endDate != null) { query += " AND (ADATETIME BETWEEN " + String.Format("'{0}' ", startDate.Value) + "AND " + String.Format("'{0}' ) ", endDate.Value.AddHours(23.59)); } query += "ORDER BY a.ADATETIME DESC"; db db = new db(connStr); DataSet ds = new DataSet(); DataTable table = new DataTable(); db.dbOpen(); table.Load(db.getReader(query)); ds.Tables.Add(table); db.dbClose(); return(ds); }