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