public static Collection<MonthReport> ClosePeriod()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
Collection<MonthReport> report;
SqlCommand sqlFunc = db.CreateCommand("SELECT * FROM sfMonthReport()");
using (SqlDataReader reader = db.Select(sqlFunc))
{
report = ReadReport(reader);
}
SqlCommand sqlCom = db.CreateCommand("EXEC spCloseMonthPeriod");
db.ExecuteNonQuery(sqlCom);
return report;
}
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
DbParameter p = null;
p = db.CreateParameter("@RowGuidColumn", SqlDbType.UniqueIdentifier, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.RowGuidColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NVarCharColumn", SqlDbType.NVarChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 100;
p.Value = this.NVarCharColumn;
cm.Parameters.Add(p);
for (int i = 0; i < cm.Parameters.Count; i++)
{
if (cm.Parameters[i].Value == null)
{
cm.Parameters[i].Value = DBNull.Value;
}
}
return(cm);
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
DbParameter p = null;
p = db.CreateParameter("@PK_PrimaryKeyColumn", SqlDbType.BigInt, 19, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.PK_PrimaryKeyColumn;
cm.Parameters.Add(p);
for (int i = 0; i < cm.Parameters.Count; i++)
{
if (cm.Parameters[i].Value == null)
{
cm.Parameters[i].Value = DBNull.Value;
}
}
return(cm);
}
public static int StartNewSeason()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand("EXEC spNewSeason");
return(db.ExecuteNonQuery(sqlCom));
}
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
return(cm);
}
public static int Insert(Customer customer)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_INSERT);
PrepareCommand(sqlCom, customer);
return(db.ExecuteNonQuery(sqlCom));
}
}
public static int Insert(Distillation distillation)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_INSERT);
PrepareCommand(sqlCom, distillation);
return(db.ExecuteNonQuery(sqlCom));
}
}
public static int Update(Season season)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_UPDATE);
PrepareCommand(sqlCom, season);
return(db.ExecuteNonQuery(sqlCom));
}
}
public static int Delete(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_DELETE);
sqlCom.Parameters.AddWithValue("@ID", id);
return(db.ExecuteNonQuery(sqlCom));
}
}
public static int Update(Period period)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_UPDATE);
PrepareCommand(sqlCom, period);
return db.ExecuteNonQuery(sqlCom);
}
}
public static int Update(Reservation reservation)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_UPDATE);
PrepareCommand(sqlCom, reservation);
sqlCom.Parameters.AddWithValue("@ID", reservation.Id);
return(db.ExecuteNonQuery(sqlCom));
}
}
public static Collection <Season> Select()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT);
using (SqlDataReader reader = db.Select(sqlCom))
{
return(Read(reader));
}
}
}
public static Collection <City> SelectByRegion(int regionId)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_BY_REGION);
sqlCom.Parameters.AddWithValue("@Region_Id", regionId);
using (SqlDataReader reader = db.Select(sqlCom))
{
return(Read(reader));
}
}
}
public static Collection <Customer> SelectBySurename(string surename)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_BY_SURENAME);
sqlCom.Parameters.AddWithValue("@surename", surename);
using (SqlDataReader reader = db.Select(sqlCom))
{
return(Read(reader));
}
}
}
public static Collection <Season> SelectClosedSeasons()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_FINISHED);
sqlCom.Parameters.AddWithValue("@finished", 1);
using (SqlDataReader reader = db.Select(sqlCom))
{
return(Read(reader));
}
}
}
public static int Delete(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand("EXEC spDeleteDistillation @id");
sqlCom.Parameters.AddWithValue("@id", id);
int rows = db.ExecuteNonQuery(sqlCom);
if (rows == -1)
{
Console.WriteLine("Distillation record with id {0} can not be deleted.", id);
}
return(rows);
}
}
public static string ActivePeriodName()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_FINISHED);
sqlCom.Parameters.AddWithValue("@finished", 0);
using (SqlDataReader reader = db.Select(sqlCom))
{
Collection<Period> activePeriods = Read(reader);
if (activePeriods.Count == 1)
{
return activePeriods[0].Name;
}
}
}
return null;
}
public static Period Select(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_ID);
sqlCom.Parameters.AddWithValue("@Id", id);
using (SqlDataReader reader = db.Select(sqlCom))
{
Collection<Period> periods = Read(reader);
if (periods.Count == 1)
{
return periods[0];
}
}
}
return null;
}
public static Season Select(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_ID);
sqlCom.Parameters.AddWithValue("@Id", id);
using (SqlDataReader reader = db.Select(sqlCom))
{
Collection <Season> seasons = Read(reader);
if (seasons.Count == 1)
{
return(seasons[0]);
}
}
}
return(null);
}
public static string ActiveSeasonName()
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_FINISHED);
sqlCom.Parameters.AddWithValue("@finished", 0);
using (SqlDataReader reader = db.Select(sqlCom))
{
Collection <Season> unfinishedSeasons = Read(reader);
if (unfinishedSeasons.Count == 1)
{
return(unfinishedSeasons[0].Name);
}
}
}
return(null);
}
public static int Delete(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_DELETE);
sqlCom.Parameters.AddWithValue("@Id", id);
int rows = 0;
try
{
rows = db.ExecuteNonQuery(sqlCom);
}
catch (DatabaseException)
{
Console.WriteLine("Customer with Id {0} can not be deleted. There is distillation referencing him.");
}
return(rows);
}
}
public static Distillation Select(int id)
{
using (SqlServerDatabase db = new SqlServerDatabase())
{
SqlCommand sqlCom = db.CreateCommand(SQL_SELECT_ID);
sqlCom.Parameters.AddWithValue("@Id", id);
sqlCom.Parameters.AddWithValue("@out", SqlDbType.Bit).Direction = ParameterDirection.Output;
//after executing
//bool a = (bool)sqlCom.Parameters["@out"].Value;
using (SqlDataReader reader = db.Select(sqlCom))
{
Collection <Distillation> distillations = Read(reader);
if (distillations.Count == 1)
{
return(distillations[0]);
}
}
}
return(null);
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
DbParameter p = null;
p = db.CreateParameter("@BigIntColumn", SqlDbType.BigInt, 19, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.BigIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@StructuredColumn", SqlDbType.Structured, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.SetTypeName("MyTableType");
var dt = this.StructuredColumn.CreateDataTable();
foreach (var item in this.StructuredColumn.Records)
{
dt.Rows.Add(item.GetValues());
}
p.Value = dt;
cm.Parameters.Add(p);
for (int i = 0; i < cm.Parameters.Count; i++)
{
if (cm.Parameters[i].Value == null)
{
cm.Parameters[i].Value = DBNull.Value;
}
}
return(cm);
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
DbParameter p = null;
p = db.CreateParameter("@BigIntColumn", SqlDbType.BigInt, 19, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.BigIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@IntColumn", SqlDbType.Int, 10, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.IntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@FloatColumn", SqlDbType.Float, 53, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.FloatColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@BinaryColumn", SqlDbType.Binary, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 100;
p.Value = this.BinaryColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@TimestampColumn", SqlDbType.Timestamp, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.TimestampColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@VarBinaryColumn", SqlDbType.VarBinary, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 100;
p.Value = this.VarBinaryColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@BitColumn", SqlDbType.Bit, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.BitColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NCharColumn", SqlDbType.NChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 100;
p.Value = this.NCharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NTextColumn", SqlDbType.NText, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 1073741823;
p.Value = this.NTextColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NVarCharColumn", SqlDbType.NVarChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 100;
p.Value = this.NVarCharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@PK_BigIntColumn", SqlDbType.BigInt, 19, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.PK_BigIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@PK_IntColumn", SqlDbType.Int, 10, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.PK_IntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@PK_FloatColumn", SqlDbType.Float, 53, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.PK_FloatColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@PK_TimestampColumn", SqlDbType.Timestamp, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Value = this.PK_TimestampColumn;
cm.Parameters.Add(p);
for (int i = 0; i < cm.Parameters.Count; i++)
{
if (cm.Parameters[i].Value == null)
{
cm.Parameters[i].Value = DBNull.Value;
}
}
return(cm);
}
public override DbCommand CreateCommand()
{
var db = new SqlServerDatabase("");
var cm = db.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = this.GetStoredProcedureName();
DbParameter p = null;
p = db.CreateParameter("@BigIntColumn", SqlDbType.BigInt, 19, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.BigIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@BinaryColumn", SqlDbType.Binary, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.BinaryColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@ImageColumn", SqlDbType.Image, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 2147483647;
p.Value = this.ImageColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@VarBinaryColumn", SqlDbType.VarBinary, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.VarBinaryColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@BitColumn", SqlDbType.Bit, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.BitColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@CharColumn", SqlDbType.Char, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.CharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NCharColumn", SqlDbType.NChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.NCharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NTextColumn", SqlDbType.NText, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 1073741823;
p.Value = this.NTextColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@NVarCharColumn", SqlDbType.NVarChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.NVarCharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@TextColumn", SqlDbType.Text, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 2147483647;
p.Value = this.TextColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@VarCharColumn", SqlDbType.VarChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 100;
p.Value = this.VarCharColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@XmlColumn", SqlDbType.Xml, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = -1;
p.Value = this.XmlColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@DateTimeColumn", SqlDbType.DateTime, null, 3);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.DateTimeColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@SmallDateTimeColumn", SqlDbType.SmallDateTime, null, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.SmallDateTimeColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@DateColumn", SqlDbType.Date, null, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.DateColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@TimeColumn", SqlDbType.Time, null, 7);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.TimeColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@DateTime2Column", SqlDbType.DateTime2, null, 7);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.DateTime2Column;
cm.Parameters.Add(p);
p = db.CreateParameter("@DecimalColumn", SqlDbType.Decimal, 18, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.DecimalColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@MoneyColumn", SqlDbType.Money, 19, 4);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.MoneyColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@SmallMoneyColumn", SqlDbType.SmallMoney, 10, 4);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.SmallMoneyColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@FloatColumn", SqlDbType.Float, 53, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.FloatColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@IntColumn", SqlDbType.Int, 10, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.IntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@RealColumn", SqlDbType.Real, 24, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.RealColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@UniqueIdentifierColumn", SqlDbType.UniqueIdentifier, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.UniqueIdentifierColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@SmallIntColumn", SqlDbType.SmallInt, 5, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.SmallIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@TinyIntColumn", SqlDbType.TinyInt, 3, 0);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.TinyIntColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@DateTimeOffsetColumn", SqlDbType.DateTimeOffset, null, 7);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Value = this.DateTimeOffsetColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@GeometryColumn", SqlDbType.Udt, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = -1;
p.SetUdtTypeName("geometry");
p.Value = this.GeometryColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@GeographyColumn", SqlDbType.Udt, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = -1;
p.SetUdtTypeName("geography");
p.Value = this.GeographyColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@HierarchyIDColumn", SqlDbType.Udt, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.Input;
p.Size = 892;
p.SetUdtTypeName("hierarchyid");
p.Value = this.HierarchyIDColumn;
cm.Parameters.Add(p);
p = db.CreateParameter("@EnumColumn", SqlDbType.NVarChar, null, null);
p.SourceColumn = p.ParameterName;
p.Direction = ParameterDirection.InputOutput;
p.Size = 20;
p.Value = this.EnumColumn;
cm.Parameters.Add(p);
for (int i = 0; i < cm.Parameters.Count; i++)
{
if (cm.Parameters[i].Value == null)
{
cm.Parameters[i].Value = DBNull.Value;
}
}
return(cm);
}