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(-1);
}
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 = -1;
if (rdr.Read())
{
result = rdr.GetInt32(0);
}
rdr.Close();
return(result);
}
public static void dropReferentialConstraint(string table, string referencedTable)
{
string findConstraintSQL = @"
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_TABLE_NAME = @Table AND UNIQUE_CONSTRAINT_TABLE_NAME = @ReferencedTable";
SqlCeCommand cmd = new SqlCeCommand(findConstraintSQL);
cmd.Parameters.Add("@Table", SqlDbType.NVarChar).Value = table;
cmd.Parameters.Add("@ReferencedTable", SqlDbType.NVarChar).Value = referencedTable;
string errMsg = "Greska prilikom citanja podataka iz baze.";
SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, errMsg);
if (!rdr.Read())
{
throw new Exception("Constraint does not exist.");
}
string constraintName = (string)rdr["CONSTRAINT_NAME"];
// NOTE: Izgleda da dodavanje parametara (pomocu @parameterName) radi samo kada je parametar sa desne
// strane znaka jednakosti (kao u findConstraintSQL). Zato ovde koristim spajanje stringova.
string dropConstraintSQL = "ALTER TABLE " + table + " DROP CONSTRAINT " + constraintName;
SqlCeCommand cmd2 = new SqlCeCommand(dropConstraintSQL);
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd2.Connection = conn;
cmd2.Transaction = tr;
int result = cmd2.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();
}
}
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();
}
}