public static bool tableExists(string tableName) { string sql = @"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName"; SqlCeCommand cmd = new SqlCeCommand(sql); cmd.Parameters.Add("@TableName", SqlDbType.NVarChar).Value = tableName; string errMsg = "Greska prilikom citanja podataka iz baze."; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, errMsg); bool result = false; if (rdr.Read()) { result = true; } rdr.Close(); return(result); }
public List <KonacanPlasman> findSpraveFinaleKupa(string ime, string prezime) { SqlCeCommand cmd = new SqlCeCommand(findSpraveFinaleKupaSQL); cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime; cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString); List <KonacanPlasman> result = new List <KonacanPlasman>(); while (rdr.Read()) { KonacanPlasman kp = new KonacanPlasman(); loadCommonData(kp, rdr); loadSprava(kp, rdr); kp.TipTakmicenja = TipTakmicenja.FinaleKupa; result.Add(kp); } rdr.Close(); return(result); }
public List <KonacanPlasman> findPreskokTak3(string ime, string prezime) { SqlCeCommand cmd = new SqlCeCommand(findPreskokTak3SQL); cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime; cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString); List <KonacanPlasman> result = new List <KonacanPlasman>(); while (rdr.Read()) { KonacanPlasman kp = new KonacanPlasman(); loadCommonData(kp, rdr); kp.Preskok = Convert.IsDBNull(rdr["rank"]) ? null : (Nullable <short>)rdr["rank"]; kp.TipTakmicenja = TipTakmicenja.StandardnoTakmicenje; result.Add(kp); } rdr.Close(); return(result); }
public List <KonacanPlasman> findVisebojZbirViseKola(string ime, string prezime) { SqlCeCommand cmd = new SqlCeCommand(findVisebojZbirViseKolaSQL); cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime; cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString); List <KonacanPlasman> result = new List <KonacanPlasman>(); while (rdr.Read()) { KonacanPlasman kp = new KonacanPlasman(); loadCommonData(kp, rdr); kp.Viseboj = Convert.IsDBNull(rdr["rank"]) ? null : (Nullable <short>)rdr["rank"]; kp.TipTakmicenja = TipTakmicenja.ZbirViseKola; result.Add(kp); } rdr.Close(); // obavezno, da bi se zatvorila konekcija otvorena u executeReader return(result); }
public List <KonacanPlasman> findSpraveTak1(string ime, string prezime) { SqlCeCommand cmd = new SqlCeCommand(findSpraveTak1SQL); cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime; cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString); List <KonacanPlasman> result = new List <KonacanPlasman>(); while (rdr.Read()) { // NOTE: Vracam sve rezultate koji postoje (ne proveravam postojiTak3 i odvojenoTak3). KonacanPlasman kp = new KonacanPlasman(); loadCommonData(kp, rdr); loadSprava(kp, rdr); kp.TipTakmicenja = (TipTakmicenja)rdr["tip_takmicenja"]; result.Add(kp); } rdr.Close(); return(result); }
public static int getDatabaseVersionNumber() { string tableName = "verzija_baze"; if (!SqlCeUtilities.tableExists(tableName)) { return(0); } string sql = "SELECT broj_verzije FROM " + tableName + " WHERE verzija_id = 1"; SqlCeCommand cmd = new SqlCeCommand(sql); string errMsg = "Greska prilikom citanja podataka iz baze."; SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, errMsg); int result = 0; if (rdr.Read()) { result = rdr.GetInt32(0); } rdr.Close(); return(result); }
// can throw InfrastructureException public List <GimnasticarBilten> findGimnasticariBilten(string ime, string prezime, Nullable <Gimnastika> gimnastika, string nazivKluba) { string WHERE = " where "; if (!String.IsNullOrEmpty(ime)) { findGimnasticariBiltenSQL += WHERE + "lower(g.ime) like @ime"; WHERE = " and "; } if (!String.IsNullOrEmpty(prezime)) { findGimnasticariBiltenSQL += WHERE + "lower(g.prezime) like @prezime"; WHERE = " and "; } if (gimnastika != null) { findGimnasticariBiltenSQL += WHERE + "g.gimnastika = @gimnastika"; WHERE = " and "; } if (!String.IsNullOrEmpty(nazivKluba)) { findGimnasticariBiltenSQL += WHERE + "lower(k.naziv) like @klub"; WHERE = " and "; } findGimnasticariBiltenSQL += " order by g.prezime asc, g.ime asc"; SqlCeCommand cmd = new SqlCeCommand(findGimnasticariBiltenSQL); if (!String.IsNullOrEmpty(ime)) { cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime.ToLower() + '%'; } if (!String.IsNullOrEmpty(prezime)) { cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime.ToLower() + '%'; } if (gimnastika != null) { cmd.Parameters.Add("@gimnastika", SqlDbType.TinyInt).Value = (byte)gimnastika.Value; } if (!String.IsNullOrEmpty(nazivKluba)) { cmd.Parameters.Add("@klub", SqlDbType.NVarChar).Value = nazivKluba.ToLower() + '%'; } SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString); List <GimnasticarBilten> result = new List <GimnasticarBilten>(); while (rdr.Read()) { GimnasticarBilten g = new GimnasticarBilten(); g.Ime = (string)rdr["ime"]; g.Prezime = (string)rdr["prezime"]; g.SrednjeIme = Convert.IsDBNull(rdr["srednje_ime"]) ? null : (string)rdr["srednje_ime"]; g.DanRodj = Convert.IsDBNull(rdr["dan_rodj"]) ? null : (Nullable <byte>)rdr["dan_rodj"]; g.MesecRodj = Convert.IsDBNull(rdr["mesec_rodj"]) ? null : (Nullable <byte>)rdr["mesec_rodj"]; g.GodRodj = Convert.IsDBNull(rdr["god_rodj"]) ? null : (Nullable <short>)rdr["god_rodj"]; g.Gimnastika = (Gimnastika)(byte)rdr["gimnastika"]; g.NazivKluba = Convert.IsDBNull(rdr["naziv_kluba"]) ? null : (string)rdr["naziv_kluba"]; g.MestoKluba = Convert.IsDBNull(rdr["mesto_kluba"]) ? null : (string)rdr["mesto_kluba"]; g.NazivKategorije = Convert.IsDBNull(rdr["naziv_kategorije"]) ? null : (string)rdr["naziv_kategorije"]; result.Add(g); } rdr.Close(); // obavezno, da bi se zatvorila konekcija otvorena u executeReader return(result); }
public static void addColumn(string table, string column, SqlDbType type, Nullable <int> size, object columnValue) { if (SqlCeUtilities.columnExists(table, column)) { return; } string addColumnSql = "ALTER TABLE [" + table + "] ADD COLUMN [" + column + "] " + getSqlType(type, size); string updateColumnSql = "UPDATE [" + table + "] SET [" + column + "] = @" + column; SqlCeCommand addColumnCommand = new SqlCeCommand(addColumnSql); SqlCeCommand updateColumnCommand = new SqlCeCommand(updateColumnSql); if (size != null) { updateColumnCommand.Parameters.Add("@" + column, type, size.Value).Value = columnValue; } else { updateColumnCommand.Parameters.Add("@" + column, type).Value = columnValue; } SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString); string errMsg = "Neuspesna promena baze."; SqlCeTransaction tr = null; try { conn.Open(); tr = conn.BeginTransaction(); addColumnCommand.Connection = conn; addColumnCommand.Transaction = tr; addColumnCommand.ExecuteNonQuery(); updateColumnCommand.Connection = conn; updateColumnCommand.Transaction = tr; updateColumnCommand.ExecuteNonQuery(); tr.Commit(); } catch (SqlCeException e) { // in Open() if (tr != null) { tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException } throw new InfrastructureException(errMsg, e); } catch (InvalidOperationException e) { // in ExecuteNonQuery(), ExecureScalar() if (tr != null) { tr.Rollback(); } throw new InfrastructureException(errMsg, e); } // za svaki slucaj catch (Exception) { if (tr != null) { tr.Rollback(); } throw; } finally { conn.Close(); } }