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