public void Equality_True() { var value1 = new SqlDateTime(2030, 03, 01, 11, 05, 54, 0); var value2 = new SqlDateTime(2030, 03, 01, 11, 05, 54, 0); Assert.IsTrue(value1 == value2); }
public void Greater_True() { var value1 = new SqlDateTime(2030, 03, 01, 11, 05, 54, 0); var value2 = new SqlDateTime(2020, 05, 01, 11, 05, 54, 0); Assert.IsTrue(value1 > value2); }
public void GetParameter() { SqlType type = new SqlDateTime(testDateTime, ParameterDirection.Input); TestHelper.AssertSqlParameter(type.GetParameter(), SqlDbType.DateTime, testDateTime); type = new SqlDateTime(null, ParameterDirection.Input); TestHelper.AssertSqlParameter(type.GetParameter(), SqlDbType.DateTime, DBNull.Value); }
public void GetRawValue() { SqlType type = new SqlDateTime(testDateTime, ParameterDirection.Input); Assert.AreEqual(testDateTime, type.GetRawValue()); type = new SqlDateTime(null, ParameterDirection.Input); Assert.Null(type.GetRawValue()); }
public void CreateMetaData() { Assert.Throws<TypeCannotBeUsedAsAClrTypeException>(() => SqlDateTime.GetTypeHandler().CreateMetaData(null)); SqlTypeHandler col = new SqlDateTime(testDateTime, ParameterDirection.Input); var meta = col.CreateMetaData("Test"); Assert.AreEqual(SqlDbType.DateTime, meta.SqlDbType); Assert.AreEqual("Test", meta.Name); }
public void CastDateTimeToString(int year, int month, int day, int hour, int minute, int second, int millis, string expected) { var type = PrimitiveTypes.DateTime(); var date = new SqlDateTime(year, month, day, hour, minute, second, millis); var casted = type.CastTo(date, PrimitiveTypes.String()); Assert.IsNotNull(casted); Assert.IsInstanceOf<SqlString>(casted); Assert.AreEqual(expected, casted.ToString()); }
public void AddDate_Month() { var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32); var value = Select("ADD_DATE", SqlExpression.Constant(date), SqlExpression.Constant("month"), SqlExpression.Constant(2)); Assert.IsNotNull(value); Assert.IsFalse(value.IsNull); Assert.IsInstanceOf<DateType>(value.Type); Assert.IsInstanceOf<SqlDateTime>(value.Value); // TODO: Assert result }
public void Add_TimeSpan_NoDays() { var value = new SqlDateTime(2001, 01, 03, 10, 22, 03, 0); var ts = new SqlDayToSecond(0, 2, 03, 0); var result = new SqlDateTime(); Assert.DoesNotThrow(() => result = value.Add(ts)); Assert.IsFalse(result.IsNull); Assert.AreEqual(2001, result.Year); Assert.AreEqual(01, result.Month); Assert.AreEqual(03, result.Day); Assert.AreEqual(12, result.Hour); Assert.AreEqual(25, result.Minute); }
public void AtCetTimeZone() { var value = new SqlDateTime(2016, 12, 03, 22, 45, 0, 0); var dateTime = value.AtTimeZone("CET"); Assert.AreEqual(2016, dateTime.Year); Assert.AreEqual(12, dateTime.Month); Assert.AreEqual(03, dateTime.Day); Assert.AreEqual(23, dateTime.Hour); Assert.AreEqual(45, dateTime.Minute); Assert.AreEqual(0, dateTime.Second); Assert.AreEqual(0, dateTime.Millisecond); Assert.AreEqual(1, dateTime.Offset.Hours); Assert.AreEqual(0, dateTime.Offset.Minutes); }
public void Add_MonthSpan() { var value = new SqlDateTime(2001, 11, 03, 10, 22, 03, 0); var ms = new SqlYearToMonth(1, 3); var result = new SqlDateTime(); Assert.DoesNotThrow(() => result = value.Add(ms)); Assert.IsFalse(result.IsNull); Assert.AreEqual(2003, result.Year); Assert.AreEqual(02, result.Month); Assert.AreEqual(10, result.Hour); Assert.AreEqual(22, result.Minute); Assert.AreEqual(03, result.Second); Assert.AreEqual(0, result.Millisecond); }
public void AddDayToSecondToDate() { var date = new SqlDateTime(2010, 11, 03, 05, 22, 43, 0); var dts = new SqlDayToSecond(19, 08, 23, 1); var result = date.Add(dts); Assert.IsNotNull(result); Assert.IsFalse(result.IsNull); Assert.AreEqual(2010, result.Year); Assert.AreEqual(11, result.Month); Assert.AreEqual(22, result.Day); Assert.AreEqual(13, result.Hour); Assert.AreEqual(45, result.Minute); Assert.AreEqual(44, result.Second); Assert.AreEqual(0, result.Millisecond); }
public void Operator_Subtract_TimeSpan() { var value = new SqlDateTime(2001, 01, 03, 10, 22, 03, 0); var ts = new SqlDayToSecond(0, 2, 03, 0); var result = new SqlDateTime(); Assert.DoesNotThrow(() => result = value - ts); Assert.IsFalse(result.IsNull); Assert.AreEqual(2001, result.Year); Assert.AreEqual(01, result.Month); Assert.AreEqual(03, result.Day); Assert.AreEqual(8, result.Hour); Assert.AreEqual(19, result.Minute); }
public void String_Convert_TimeStamp() { const string s = "2011-01-23T23:44:21.525 +01:00"; var sqlString = new SqlString(s); var timeStamp = new SqlDateTime(); Assert.DoesNotThrow(() => timeStamp = (SqlDateTime) Convert.ChangeType(sqlString, typeof(SqlDateTime))); Assert.IsFalse(timeStamp.IsNull); Assert.AreEqual(2011, timeStamp.Year); Assert.AreEqual(01, timeStamp.Month); Assert.AreEqual(23, timeStamp.Day); Assert.AreEqual(23, timeStamp.Hour); Assert.AreEqual(44, timeStamp.Minute); Assert.AreEqual(525, timeStamp.Millisecond); Assert.AreEqual(1, timeStamp.Offset.Hours); Assert.AreEqual(0, timeStamp.Offset.Minutes); }
public void String_Convert_Date() { const string s = "2011-01-23"; var sqlString = new SqlString(s); var date = new SqlDateTime(); Assert.DoesNotThrow(() => date = (SqlDateTime) Convert.ChangeType(sqlString, typeof(SqlDateTime))); Assert.IsFalse(date.IsNull); Assert.AreEqual(2011, date.Year); Assert.AreEqual(01, date.Month); Assert.AreEqual(23, date.Day); Assert.AreEqual(0, date.Hour); Assert.AreEqual(0, date.Minute); Assert.AreEqual(0, date.Millisecond); Assert.AreEqual(0, date.Offset.Hours); Assert.AreEqual(0, date.Offset.Minutes); }
/// <summary> /// Purpose: Select method. This method will Select one existing row from the database, based on the Primary Key. /// </summary> /// <returns>DataTable object if succeeded, otherwise an Exception is thrown. </returns> /// <remarks> /// Properties needed for this method: /// <UL> /// <LI>ProduccionId</LI> /// </UL> /// Properties set after a succesful call of this method: /// <UL> /// <LI>ErrorCode</LI> /// <LI>ProduccionId</LI> /// <LI>FacturasId</LI> /// <LI>FacturasPartidas_Id</LI> /// <LI>ProduccionTimeStam</LI> /// </UL> /// Will fill all properties corresponding with a field in the table with the value of the row selected. /// </remarks> public override DataTable SelectOne() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[sp_post_Produccion_SelectOne]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("Produccion"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@iProduccionId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _produccionId)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'sp_post_Produccion_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _produccionId = (Int32)toReturn.Rows[0]["ProduccionId"]; _facturasId = (Int32)toReturn.Rows[0]["FacturasId"]; _facturasPartidas_Id = (Int32)toReturn.Rows[0]["FacturasPartidas_Id"]; _produccionTimeStam = (DateTime)toReturn.Rows[0]["ProduccionTimeStam"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("Produccion::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
/// <summary> /// 根据发文ID修改发文信息 /// </summary> /// <param name="DocSendM">发文信息</param> /// <returns>bool值</returns> public static bool UpdateDocSend(DocSendModel DocSendM) { try { StringBuilder sql = new StringBuilder(); sql.AppendLine("UPDATE officedba.DocSendInfo set "); sql.AppendLine("CompanyCD = @CompanyCD ,"); //sql.AppendLine("DocumentNo = @DocumentNo ,"); sql.AppendLine("SendDocTypeID = @SendDocTypeID ,"); sql.AppendLine("SecretLevel = @SecretLevel ,"); sql.AppendLine("EmerLevel = @EmerLevel ,"); sql.AppendLine("SendDeptID = @SendDeptID ,"); sql.AppendLine("FileNo = @FileNo ,"); sql.AppendLine("FileTitle = @FileTitle ,"); sql.AppendLine("FileDate = @FileDate ,"); sql.AppendLine("MainSend = @MainSend ,"); sql.AppendLine("CCSend = @CCSend ,"); sql.AppendLine("OutCompany = @OutCompany ,"); sql.AppendLine("KeyWord = @KeyWord ,"); sql.AppendLine("FileReason = @FileReason ,"); sql.AppendLine("Description = @Description ,"); sql.AppendLine("RegisterUserID= @RegisterUserID,"); sql.AppendLine("Backer = @Backer ,"); sql.AppendLine("BackerNo = @BackerNo ,"); sql.AppendLine("BackDate = @BackDate ,"); sql.AppendLine("BackContent = @BackContent ,"); sql.AppendLine("UploadDate = @UploadDate ,"); sql.AppendLine("DocumentName = @DocumentName ,"); sql.AppendLine("DocumentURL = @DocumentURL ,"); sql.AppendLine("Remark = @Remark ,"); sql.AppendLine("ModifiedDate = @ModifiedDate ,"); sql.AppendLine("ModifiedUserID= @ModifiedUserID"); sql.AppendLine(" WHERE "); sql.AppendLine("ID = @ID "); SqlParameter[] param = new SqlParameter[26]; param[0] = SqlHelper.GetParameter("@ID ", DocSendM.ID); param[1] = SqlHelper.GetParameter("@CompanyCD ", DocSendM.CompanyCD); param[2] = SqlHelper.GetParameter("@SendDocTypeID ", DocSendM.SendDocTypeID); param[3] = SqlHelper.GetParameter("@SecretLevel ", DocSendM.SecretLevel); param[4] = SqlHelper.GetParameter("@EmerLevel ", DocSendM.EmerLevel); param[5] = SqlHelper.GetParameter("@SendDeptID ", DocSendM.SendDeptID); param[6] = SqlHelper.GetParameter("@FileNo ", DocSendM.FileNo); param[7] = SqlHelper.GetParameter("@FileTitle ", DocSendM.FileTitle); param[8] = SqlHelper.GetParameter("@FileDate", DocSendM.FileDate == null ? SqlDateTime.Null : SqlDateTime.Parse(DocSendM.FileDate.ToString())); param[9] = SqlHelper.GetParameter("@MainSend ", DocSendM.MainSend); param[10] = SqlHelper.GetParameter("@CCSend ", DocSendM.CCSend); param[11] = SqlHelper.GetParameter("@OutCompany ", DocSendM.OutCompany); param[12] = SqlHelper.GetParameter("@KeyWord ", DocSendM.KeyWord); param[13] = SqlHelper.GetParameter("@FileReason ", DocSendM.FileReason); param[14] = SqlHelper.GetParameter("@Description ", DocSendM.Description); param[15] = SqlHelper.GetParameter("@RegisterUserID", DocSendM.RegisterUserID); param[16] = SqlHelper.GetParameter("@Backer ", DocSendM.Backer); param[17] = SqlHelper.GetParameter("@BackerNo ", DocSendM.BackerNo); param[18] = SqlHelper.GetParameter("@BackDate", DocSendM.BackDate == null ? SqlDateTime.Null : SqlDateTime.Parse(DocSendM.BackDate.ToString())); param[19] = SqlHelper.GetParameter("@BackContent ", DocSendM.BackContent); param[20] = SqlHelper.GetParameter("@UploadDate", DocSendM.UploadDate == null ? SqlDateTime.Null : SqlDateTime.Parse(DocSendM.UploadDate.ToString())); param[21] = SqlHelper.GetParameter("@DocumentName ", DocSendM.DocumentName); param[22] = SqlHelper.GetParameter("@DocumentURL ", DocSendM.DocumentURL); param[23] = SqlHelper.GetParameter("@Remark ", DocSendM.Remark); param[24] = SqlHelper.GetParameter("@ModifiedDate", DocSendM.ModifiedDate == null ? SqlDateTime.Null : SqlDateTime.Parse(DocSendM.ModifiedDate.ToString())); param[25] = SqlHelper.GetParameter("@ModifiedUserID", DocSendM.ModifiedUserID); SqlHelper.ExecuteTransSql(sql.ToString(), param); return(SqlHelper.Result.OprateCount > 0 ? true : false); } catch { return(false); } }
public ActualSalesReport(SqlString companyCode, SqlString groupByCode, SqlDateTime month) { CompanyCode = companyCode; GroupByCode = groupByCode; }
public static IEnumerable fnSystemFileCreate ( SqlBinary inFileBinary, SqlString inSourceDirectory, SqlString inSourceFileName, SqlBoolean inOverwrite ) { string formatMessage = string.Empty; // store the results in a list ArrayList resultCollection = new ArrayList(); // logs the time the row had begun processing SqlDateTime startTime = DateTime.Now; // Complete file path string completePath = (string)inSourceDirectory + (string)inSourceFileName; // Check if the destination directory exists if (System.IO.Directory.Exists((string)inSourceDirectory) == false) { // Directory does not exist formatMessage = String.Format("Output directory {0} does not exist. Please create this and try again.", (string)inSourceDirectory); resultCollection.Add(new FileResult(false, formatMessage, STATUS_CODES.FAILURE, null, startTime, DateTime.Now)); return(resultCollection); } // Check if this file already exists if (System.IO.File.Exists(completePath) && inOverwrite == false) { // File already exists formatMessage = String.Format("Output file {0} already exists with no overwriting specified. Please confirm overwrite and try again.", completePath); resultCollection.Add(new FileResult(false, formatMessage, STATUS_CODES.FAILURE, null, startTime, DateTime.Now)); return(resultCollection); } // Create the file try { // binary to file operation if (!inFileBinary.IsNull) { long fsSize = 0; // otherwise convert the stream of bytes to a file FileStream fs = System.IO.File.Create(completePath); fs.Write(inFileBinary.Value, 0, inFileBinary.Value.Length); fsSize = fs.Length; fs.Close(); // Update the success status formatMessage = String.Format("Varbinary data to disk file {0} created successfully.", completePath); resultCollection.Add(new FileResult(true, formatMessage, STATUS_CODES.SUCCESS, fsSize / 1024, startTime, DateTime.Now)); } } catch (Exception ex) { formatMessage = String.Format("Unable to convert varbinary data to disk file {0}: {1}.\r\n{2}.", completePath, ex.Message, ex.StackTrace); resultCollection.Add(new FileResult(false, formatMessage, STATUS_CODES.FAILURE, null, startTime, DateTime.Now)); return(resultCollection); } // All done return(resultCollection); }
public static SqlDouble ToOADate(SqlDateTime d) { // Put your code here return(((DateTime)d).ToOADate()); }
public DataTable SelectByReservation() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[pr_KNET_Payments_SelectByReservation]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("KNET_Reservation_Payments"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@iReservation_ID", SqlDbType.Int, 4, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _reservation_ID)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (SqlInt32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'pr_KNET_Payments_SelectByReservation' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _payment_ID = (Int32)toReturn.Rows[0]["Payment_ID"]; _reservation_ID = (Int32)toReturn.Rows[0]["Reservation_ID"]; _knet_Payment_ID = toReturn.Rows[0]["KNET_Payment_ID"].ToString(); _createdDate = (DateTime)toReturn.Rows[0]["CreatedDate"]; _transVal = toReturn.Rows[0]["TransVal"].ToString(); _paymentStatus = (Int32)toReturn.Rows[0]["PaymentStatus"]; _guest_ID = (Int32)toReturn.Rows[0]["Guest_ID"]; _track_ID = toReturn.Rows[0]["Track_ID"].ToString(); _paymentAmount = (Decimal)toReturn.Rows[0]["PaymentAmount"]; _auth = toReturn.Rows[0]["Authorise"].ToString(); _result = toReturn.Rows[0]["Result"].ToString(); _ref = toReturn.Rows[0]["Ref"].ToString(); _remarks = toReturn.Rows[0]["Remarks"].ToString(); _paymentStatusEn = toReturn.Rows[0]["PaymentStatus_EN"].ToString(); } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("pr_KNET_Payments_SelectByReservation::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
public ForecastCollectionHeader(SqlString companyCode, SqlString customerNumber, SqlDateTime posSalesEndDate) { CompanyCode = companyCode; CustomerNumber = customerNumber; POSSalesEndDate = posSalesEndDate; }
public ForecastCollectionKey(SqlString companyCode, SqlString customerNumber, SqlDateTime posWeekEndDateEnd) { CompanyCode = companyCode; CustomerNumber = customerNumber; POSSalesEndDate = posWeekEndDateEnd; }
// devnote: This method should not be used with SqlDbType.Date and SqlDbType.DateTime2. // With these types the values should be used directly as CLR types instead of being converted to a SqlValue internal static object GetSqlValueFromComVariant(object comVal) { object sqlVal = null; if ((null != comVal) && (DBNull.Value != comVal)) { if (comVal is float) { sqlVal = new SqlSingle((float)comVal); } else if (comVal is string) { sqlVal = new SqlString((string)comVal); } else if (comVal is double) { sqlVal = new SqlDouble((double)comVal); } else if (comVal is byte[]) { sqlVal = new SqlBinary((byte[])comVal); } else if (comVal is char) { sqlVal = new SqlString(((char)comVal).ToString()); } else if (comVal is char[]) { sqlVal = new SqlChars((char[])comVal); } else if (comVal is System.Guid) { sqlVal = new SqlGuid((Guid)comVal); } else if (comVal is bool) { sqlVal = new SqlBoolean((bool)comVal); } else if (comVal is byte) { sqlVal = new SqlByte((byte)comVal); } else if (comVal is short) { sqlVal = new SqlInt16((short)comVal); } else if (comVal is int) { sqlVal = new SqlInt32((int)comVal); } else if (comVal is long) { sqlVal = new SqlInt64((long)comVal); } else if (comVal is decimal) { sqlVal = new SqlDecimal((decimal)comVal); } else if (comVal is DateTime) { // devnote: Do not use with SqlDbType.Date and SqlDbType.DateTime2. See comment at top of method. sqlVal = new SqlDateTime((DateTime)comVal); } else if (comVal is XmlReader) { sqlVal = new SqlXml((XmlReader)comVal); } else if (comVal is TimeSpan || comVal is DateTimeOffset) { sqlVal = comVal; } #if DEBUG else { Debug.Fail("unknown SqlType class stored in sqlVal"); } #endif } return(sqlVal); }
public POS(SqlString companyCode, SqlString customerNumber, SqlString itemNumber, SqlDateTime weekEndDate) { CompanyCode = companyCode; CustomerNumber = customerNumber; ItemNumber = itemNumber; WeekEndDate = weekEndDate; }
public ParameteredCollection(SqlString companyCode, SqlString customerNumber, SqlString itemNumber, SqlDateTime weekEndDate) { ParameterCollection = new HybridDictionary { { "CompanyCode", companyCode }, { "CustomerNumber", customerNumber }, { "ItemNumber", itemNumber }, { "WeekEndDate", weekEndDate } }; }
public override object Aggregate(int[] records, AggregateType kind) { bool hasData = false; try { switch (kind) { case AggregateType.Min: SqlDateTime min = SqlDateTime.MaxValue; for (int i = 0; i < records.Length; i++) { int record = records[i]; if (IsNull(record)) { continue; } if ((SqlDateTime.LessThan(_values[record], min)).IsTrue) { min = _values[record]; } hasData = true; } if (hasData) { return(min); } return(_nullValue); case AggregateType.Max: SqlDateTime max = SqlDateTime.MinValue; for (int i = 0; i < records.Length; i++) { int record = records[i]; if (IsNull(record)) { continue; } if ((SqlDateTime.GreaterThan(_values[record], max)).IsTrue) { max = _values[record]; } hasData = true; } if (hasData) { return(max); } return(_nullValue); case AggregateType.First: // Does not seem to be implemented if (records.Length > 0) { return(_values[records[0]]); } return(null !); // no data => null case AggregateType.Count: int count = 0; for (int i = 0; i < records.Length; i++) { if (!IsNull(records[i])) { count++; } } return(count); } } catch (OverflowException) { throw ExprException.Overflow(typeof(SqlDateTime)); } throw ExceptionBuilder.AggregateException(kind, _dataType); }
public override DataTable SelectOne() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[sp_tblCashVoucher_SelectOne]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("tblCashVoucher"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@sstrSN", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, _strSN)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (SqlInt32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'sp_tblCashVoucher_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _nCashVoucherID = toReturn.Rows[0]["nCashVoucherID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nCashVoucherID"]; _strSN = toReturn.Rows[0]["strSN"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strSN"]; _strDescription = toReturn.Rows[0]["strDescription"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strDescription"]; _strDescription2 = toReturn.Rows[0]["strDescription2"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strDescription2"]; _nCreatedByID = toReturn.Rows[0]["nCreatedByID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nCreatedByID"]; _dtCreatedDate = toReturn.Rows[0]["dtCreatedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtCreatedDate"]; _strRedeemedByID = toReturn.Rows[0]["strRedeemedByID"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strRedeemedByID"]; _dtRedeemedDate = toReturn.Rows[0]["dtRedeemedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtRedeemedDate"]; _strRedeemedBranch = toReturn.Rows[0]["strRedeemedBranch"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strRedeemedBranch"]; _nStatusID = toReturn.Rows[0]["nStatusID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nStatusID"]; _dtStartDate = toReturn.Rows[0]["dtStartDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtStartDate"]; _dtExpiryDate = toReturn.Rows[0]["dtExpiryDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtExpiryDate"]; _dtPrintedDate = toReturn.Rows[0]["dtPrintedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtPrintedDate"]; _strSoldToID = toReturn.Rows[0]["strSoldToID"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strSoldToID"]; _dtSoldDate = toReturn.Rows[0]["dtSoldDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtSoldDate"]; _strSoldBranch = toReturn.Rows[0]["strSoldBranch"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strSoldBranch"]; _mValue = toReturn.Rows[0]["mValue"] == System.DBNull.Value ? SqlMoney.Null : (Decimal)toReturn.Rows[0]["mValue"]; _strBranchCode = toReturn.Rows[0]["strBranchCode"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strBranchCode"]; _nCashVoucherTypeID = toReturn.Rows[0]["nCashVoucherTypeID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nCashVoucherTypeID"]; _nCashVoucherTypeID = toReturn.Rows[0]["nCashVoucherTypeID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nCashVoucherTypeID"]; _nValidDuration = toReturn.Rows[0]["nValidDuration"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nValidDuration"]; _strDurationUnit = toReturn.Rows[0]["strDurationUnit"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strDurationUnit"]; _fSell = toReturn.Rows[0]["fSell"] == System.DBNull.Value ? SqlBoolean.Null : (bool)toReturn.Rows[0]["fSell"]; _strCategoryIDs = toReturn.Rows[0]["strCategoryIDs"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strCategoryIDs"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("TblCashVoucher::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
/// <summary> /// Purpose: Select method. This method will Select one existing row from the database, based on the Primary Key. /// </summary> /// <returns>DataTable object if succeeded, otherwise an Exception is thrown. </returns> /// <remarks> /// Properties needed for this method: /// <UL> /// <LI>StrPackageGroupCode</LI> /// </UL> /// Properties set after a succesful call of this method: /// <UL> /// <LI>ErrorCode</LI> /// <LI>StrPackageGroupCode</LI> /// <LI>StrDescription</LI> /// <LI>MListPrice</LI> /// <LI>NCategoryID</LI> /// <LI>DtValidStart</LI> /// <LI>DtValidEnd</LI> /// </UL> /// Will fill all properties corresponding with a field in the table with the value of the row selected. /// </remarks> public override DataTable SelectOne() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[sp_tblPackageGroup_SelectOne]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("tblPackageGroup"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@sstrPackageGroupCode", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, _strPackageGroupCode)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (SqlInt32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'sp_tblPackageGroup_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _strPackageGroupCode = (string)toReturn.Rows[0]["strPackageGroupCode"]; _strDescription = toReturn.Rows[0]["strDescription"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["strDescription"]; _mListPrice = toReturn.Rows[0]["mListPrice"] == System.DBNull.Value ? SqlMoney.Null : (Decimal)toReturn.Rows[0]["mListPrice"]; _mListPriceWGST = toReturn.Rows[0]["mListPriceWGST"] == System.DBNull.Value ? SqlMoney.Null : (Decimal)toReturn.Rows[0]["mListPriceWGST"]; _nCategoryID = toReturn.Rows[0]["nCategoryID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["nCategoryID"]; _dtValidStart = toReturn.Rows[0]["dtValidStart"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtValidStart"]; _dtValidEnd = toReturn.Rows[0]["dtValidEnd"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["dtValidEnd"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("TblPackageGroup::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
public void F_UpdateCommand(DataTable dt, DataTable dtDataDefine, int iIndex, ref string StrErrorIDLog, SqlCommand sqlcommand, string StrTable) { try { string StrInsertColumn = "", StrInsertValue = ""; for (int i = 0; i < dt.Rows.Count; i++) { StrErrorIDLog = "第" + i.ToString() + "筆資料"; //處理字串 for (int j = 0; j < dt.Columns.Count; j++) { StrErrorIDLog += "設定匯入表格第" + j.ToString() + "筆資料"; if (j > 0) { StrInsertColumn += ","; StrInsertValue += ","; } else { StrInsertColumn = "id,"; StrInsertValue = "@id,"; } StrInsertColumn += dtDataDefine.Rows[j]["資料名稱中文"].ToString(); StrInsertValue += "@" + dtDataDefine.Rows[j]["資料名稱中文"].ToString(); } sqlcommand.CommandText = string.Format(@"INSERT INTO {0} ({1}) VALUES ({2} ) ", (StrTable == StrAMZGuidance)? "AMZGuidanceLine" : "AMZCapacityLine", StrInsertColumn, StrInsertValue); sqlcommand.Parameters.Add("@id", SqlDbType.Int).Value = iIndex; //處理匯入資料 for (int j = 0; j < dt.Columns.Count; j++) { switch (dtDataDefine.Rows[j]["資料格式"]) { case "Varchar": sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.VarChar).Value = dt.Rows[i][j].ToString(); break; case "Nvarchar": sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.NVarChar).Value = dt.Rows[i][j].ToString(); break; case "Int": sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.Int).Value = dt.Rows[i][j].ToString(); break; case "Float": sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.Float).Value = dt.Rows[i][j].ToString(); break; case "Datetime": if (string.IsNullOrEmpty(dt.Rows[i][j].ToString())) { SqlDateTime st = SqlDateTime.Null; sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.DateTime).Value = st; } else { sqlcommand.Parameters.Add("@" + dtDataDefine.Rows[j]["資料名稱中文"], SqlDbType.DateTime).Value = dt.Rows[i][j].ToString(); } break; default: break; } } sqlcommand.ExecuteNonQuery(); sqlcommand.Parameters.Clear(); } } catch (Exception ex) { F_ErrorShow(ex.ToString()); } }
// // GET: /Campaign/BetaTesters2017 public async Task <ActionResult> BetaTesters2017(RegisterViewModel model) { if (ModelState.IsValid) { var user = new ApplicationUser() { UserName = model.UserName }; var result = await UserManager.CreateAsync(user, model.Password); var response = HttpContext.Request.Form["g-recaptcha-response"] == null ? "" : HttpContext.Request.Form["g-recaptcha-response"]; string secretKey = "6Lc5GBoTAAAAAOQFNfUBzRtzN_I-vmyJzGugEx65"; var client = new System.Net.WebClient(); var verificationResultJson = client.DownloadString(string.Format("https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}", secretKey, response)); var verificationResult = JsonConvert.DeserializeObject <CaptchaVerificationResult>(verificationResultJson); if (!verificationResult.Success) { ModelState.AddModelError("", "ERROR: Invalid recaptcha challenge."); } else { if (result.Succeeded) { // Add or update MstUser table try { await SignInAsync(user, isPersistent : false); Data.MagentaTradersDBDataContext db = new Data.MagentaTradersDBDataContext(); var Users = from d in db.MstUsers where d.UserName == model.UserName select d; if (Users.Any()) { var UpdatedUser = Users.FirstOrDefault(); UpdatedUser.AspNetUserId = db.AspNetUsers.Where(d => d.UserName == model.UserName).FirstOrDefault().Id; db.SubmitChanges(); } else { Data.MstUser NewUser = new Data.MstUser(); NewUser.UserName = model.UserName; NewUser.FirstName = model.FirstName == null || model.FirstName.Length == 0 ? "NA" : model.FirstName; NewUser.LastName = model.LastName == null || model.LastName.Length == 0 ? "NA" : model.LastName; NewUser.EmailAddress = model.EmailAddress == null || model.EmailAddress.Length == 0 ? "NA" : model.EmailAddress; NewUser.PhoneNumber = model.PhoneNumber == null || model.PhoneNumber.Length == 0 ? "NA" : model.PhoneNumber; NewUser.Address = model.Address == null || model.Address.Length == 0 ? "" : model.Address; NewUser.ReferralUserName = model.ReferralUserName == null || model.ReferralUserName.Length == 0 ? "" : model.ReferralUserName; NewUser.AspNetUserId = db.AspNetUsers.Where(d => d.UserName == model.UserName).FirstOrDefault().Id; DateTime dateCreated = DateTime.Now; SqlDateTime dateCreatedSQL = new SqlDateTime(new DateTime(dateCreated.Year, + dateCreated.Month, + dateCreated.Day)); NewUser.DateCreated = dateCreatedSQL.Value; db.MstUsers.InsertOnSubmit(NewUser); db.SubmitChanges(); Data.AspNetUserRole NewRole1 = new Data.AspNetUserRole(); NewRole1.AspNetUser = db.AspNetUsers.Where(d => d.UserName == model.UserName).FirstOrDefault(); NewRole1.AspNetRole = db.AspNetRoles.Where(d => d.Name == "Quest").FirstOrDefault(); db.AspNetUserRoles.InsertOnSubmit(NewRole1); db.SubmitChanges(); Data.AspNetUserRole NewRole2 = new Data.AspNetUserRole(); NewRole2.AspNetUser = db.AspNetUsers.Where(d => d.UserName == model.UserName).FirstOrDefault(); NewRole2.AspNetRole = db.AspNetRoles.Where(d => d.Name == "Chart").FirstOrDefault(); db.AspNetUserRoles.InsertOnSubmit(NewRole2); db.SubmitChanges(); Data.AspNetUserRole NewRole3 = new Data.AspNetUserRole(); NewRole3.AspNetUser = db.AspNetUsers.Where(d => d.UserName == model.UserName).FirstOrDefault(); NewRole3.AspNetRole = db.AspNetRoles.Where(d => d.Name == "Web99").FirstOrDefault(); db.AspNetUserRoles.InsertOnSubmit(NewRole3); db.SubmitChanges(); } return(RedirectToAction("Index", "Help")); //return RedirectToAction("Index", "Home"); } catch (Exception e) { ModelState.AddModelError("", "ERROR: Try again. " + e.ToString()); } } else { AddErrors(result); } } } return(View(model)); }
/// <summary> /// Purpose: Select method. This method will Select one existing row from the database, based on the Primary Key. /// </summary> /// <returns>DataTable object if succeeded, otherwise an Exception is thrown. </returns> /// <remarks> /// Properties needed for this method: /// <UL> /// <LI>IdLogXml</LI> /// </UL> /// Properties set after a succesful call of this method: /// <UL> /// <LI>ErrorCode</LI> /// <LI>IdLogXml</LI> /// <LI>LogXml_NombreXml</LI> /// <LI>LogXml_Dir_Xml</LI> /// <LI>LogXml_Dir_XmlOK</LI> /// <LI>LogXml_Up</LI> /// <LI>LogXml_Date</LI> /// <LI>LogXml_TrasId</LI> /// <LI>LogXml_FacturasId</LI> /// </UL> /// Will fill all properties corresponding with a field in the table with the value of the row selected. /// </remarks> public override DataTable SelectOne() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[sp_reg_LogXml_SelectOne]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("LogXml"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@iidLogXml", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _idLogXml)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'sp_reg_LogXml_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _idLogXml = (Int32)toReturn.Rows[0]["idLogXml"]; _logXml_NombreXml = (string)toReturn.Rows[0]["LogXml_NombreXml"]; _logXml_Dir_Xml = (string)toReturn.Rows[0]["LogXml_Dir_Xml"]; _logXml_Dir_XmlOK = (string)toReturn.Rows[0]["LogXml_Dir_XmlOK"]; _logXml_Up = (string)toReturn.Rows[0]["LogXml_Up"]; _logXml_Date = (DateTime)toReturn.Rows[0]["LogXml_Date"]; _logXml_TrasId = toReturn.Rows[0]["LogXml_TrasId"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LogXml_TrasId"]; _logXml_FacturasId = toReturn.Rows[0]["LogXml_FacturasId"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LogXml_FacturasId"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("LogXml::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
static void Main(string[] args) { DateTime dold; switch (args[0]) { case "1": Console.WriteLine("run 1"); AddTable(connString); break; case "2": Console.WriteLine("run 2"); Console.Write("Input Name(input with spaces and [lenght string]<=25): "); string name = Console.ReadLine(); Console.Write("Input Date(format: YYYYMMDD): "); SqlDateTime birthday; try { birthday = SqlDateTime.Parse(Console.ReadLine()); } catch { Random r = new Random(); birthday = GetRandomDate(DateTime.Parse("00:00:00 01.01.1900"), DateTime.Now, r); } Console.Write("Input number Gender(man: 0,woman: 1): "); bool gender = Console.ReadLine() == "1" ? true : false; AddPerson(name, birthday, gender, connString); Console.WriteLine(); break; case "3": Console.WriteLine("run 3"); GetPersonsNames(connString); break; case "4": Console.WriteLine("run 4"); Console.Write("Input number of records: "); int len; try { len = int.Parse(Console.ReadLine()); } catch { len = 10000; } dold = DateTime.Now; AddPersonsRandom(connString, len); Console.WriteLine(DateTime.Now - dold); break; case "5": Console.WriteLine("run 5"); dold = DateTime.Now; Select(connString, "sp_Select"); Console.WriteLine(DateTime.Now - dold); break; case "6": Console.WriteLine("run 6"); dold = DateTime.Now; Select(connString, "sp_SelectOptimized"); Console.WriteLine(DateTime.Now - dold); break; default: Console.WriteLine("Value does not exist.."); Console.WriteLine("HELPERS:"); Console.WriteLine("Input 1 create table"); Console.WriteLine("Input 2 adds person record"); Console.WriteLine("Input 3 retrieves data according to name and birthday criteria"); Console.WriteLine("Input 4 inserts 1000000 random records to table"); Console.WriteLine("Input 5 retrieves data (name and gender) where name starts by 'F' letter"); Console.WriteLine("Input 6 makes optimization of previous method"); Console.WriteLine("Close program.."); break; } }
public static SqlInt32 EventReceive(SqlString Server, SqlString Database, out SqlGuid EventId, out SqlDateTime EventPosted, out SqlDateTime EventReceived, out SqlXml EventArgs, SqlString EventType, SqlString Options) { WindowsIdentity clientId = null; //WindowsImpersonationContext impersonatedUser = null; clientId = SqlContext.WindowsIdentity; bool debug = Options.ToString().Contains("debug"); string ConnectionString = String.Format("Persist Security Info=False;Integrated Security=SSPI;database={0};server={1}", Database.ToString(), Server.ToString()); SqlInt32 ret = 1; try { Version v = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version; SqlMetaData[] m = new SqlMetaData[1] { new SqlMetaData("msg", SqlDbType.NVarChar, 4000) }; SqlDataRecord rec = null; SqlPipe pipe = SqlContext.Pipe; Send(pipe, rec, String.Format("Controller CLR Extensions Version {0} Executing as {1}", v, clientId.Name), debug); EventFunctions.ReceiveEvent(ConnectionString, out EventId, out EventPosted, out EventReceived, out EventArgs, EventType, Options); Send(pipe, rec, String.Format("SqlClr EventReceive {1}.{2} - {3} completed" , ret, Server.ToString(), Database.ToString(), EventType.ToString()), debug); ret = 0; } catch { throw; } return(ret); }
public static DataTable GetOutputPage(SqlTransaction trans, PageSettings pageSettings, Guid userId, int departmentId) { Department department = new Department(trans, departmentId); Worker worker = new Worker(trans, userId); /*if (!Permission.IsUserPermission(trans, userId, department.ObjectID, (int)Department.ActionType.View)) * { * throw new AccessException(userId.ToString(), "Get document template"); * }*/ //worker.PostID SqlParameter[] sps = new SqlParameter[14]; sps[0] = new SqlParameter("@PageIndex", SqlDbType.Int); sps[0].Value = pageSettings.PageIndex; sps[1] = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 50); sps[1].Value = pageSettings.SortColumn; sps[2] = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4); sps[2].Value = pageSettings.SortOrder; sps[3] = new SqlParameter("@NumberOfRows", SqlDbType.Int); sps[3].Value = pageSettings.PageSize; sps[4] = new SqlParameter("@TotalRecords", SqlDbType.Int); sps[4].Direction = ParameterDirection.Output; sps[5] = new SqlParameter("@CreationDate", SqlDbType.DateTime); if (pageSettings.Where.HasRule("CreationDate")) { sps[5].Value = DateTime.Parse(pageSettings.Where.GetRule("CreationDate").Data, CultureInfo.CurrentCulture); } else { sps[5].Value = DBNull.Value; } SqlDateTime cdStart = SqlDateTime.MinValue; if (pageSettings.Where.HasRule("CreationDateStart")) { cdStart = DateTime.Parse(pageSettings.Where.GetRule("CreationDateStart").Data, CultureInfo.CurrentCulture); } sps[6] = new SqlParameter("@CreationDateStart", SqlDbType.DateTime); sps[6].Value = cdStart; SqlDateTime cdEnd = SqlDateTime.MaxValue; if (pageSettings.Where.HasRule("CreationDateEnd")) { cdEnd = DateTime.Parse(pageSettings.Where.GetRule("CreationDateEnd").Data, CultureInfo.CurrentCulture); } sps[7] = new SqlParameter("@CreationDateEnd", SqlDbType.DateTime); sps[7].Value = cdEnd; sps[8] = new SqlParameter("@Number", SqlDbType.NVarChar, 50); sps[8].Value = pageSettings.Where.HasRule("Number") ? pageSettings.Where.GetRule("Number").Data : String.Empty; sps[9] = new SqlParameter("@DepartmentID", SqlDbType.Int); sps[9].Value = departmentId; sps[10] = new SqlParameter("@DocumentCodeID", SqlDbType.Int); int documentCodeId; if (pageSettings.Where.HasRule("DocumentCodeID") && Int32.TryParse(pageSettings.Where.GetRule("DocumentCodeID").Data, out documentCodeId)) { sps[10].Value = documentCodeId; } else { sps[10].Value = DBNull.Value; } sps[11] = new SqlParameter("@Content", SqlDbType.NVarChar); sps[11].Value = pageSettings.Where.HasRule("Content") ? pageSettings.Where.GetRule("Content").Data : String.Empty; sps[12] = new SqlParameter("@OrganizationName", SqlDbType.NVarChar, 256); sps[12].Value = pageSettings.Where.HasRule("OrganizationName") ? pageSettings.Where.GetRule("OrganizationName").Data : String.Empty; sps[13] = new SqlParameter("@WorkerID", SqlDbType.Int); sps[13].Value = worker.ID; DataTable dt = SPHelper.ExecuteDataset(trans, SpNames.OutputPage, sps).Tables[0]; pageSettings.TotalRecords = sps[4].Value != DBNull.Value ? Convert.ToInt32(sps[4].Value) : 0; return(dt); }
/// <summary> /// Writes a value of SqlDateTime to the current writer. /// </summary> /// <param name="val">A value of SqlDateTime to write.</param> public void Write(SqlDateTime val) { this.Write(val.DayTicks); this.Write(val.TimeTicks); }
public void String_Convert_Time() { const string s = "23:44:21.525"; var sqlString = new SqlString(s); var time = new SqlDateTime(); Assert.DoesNotThrow(() => time = (SqlDateTime) Convert.ChangeType(sqlString, typeof(SqlDateTime))); Assert.IsFalse(time.IsNull); Assert.AreEqual(1, time.Year); Assert.AreEqual(1, time.Month); Assert.AreEqual(1, time.Day); Assert.AreEqual(23, time.Hour); Assert.AreEqual(44, time.Minute); Assert.AreEqual(21, time.Second); Assert.AreEqual(525, time.Millisecond); }
public void Extract_Year() { var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32); var value = Select("EXTRACT", SqlExpression.Constant(date), SqlExpression.Constant("year")); Assert.IsNotNull(value); Assert.IsFalse(value.IsNull); Assert.IsInstanceOf<NumericType>(value.Type); Assert.IsInstanceOf<SqlNumber>(value.Value); Assert.AreEqual(new SqlNumber(1980), value.Value); }
public void Lesser_False() { var value1 = new SqlDateTime(2030, 03, 01, 11, 05, 54, 0); var value2 = new SqlDateTime(2020, 05, 01, 11, 05, 54, 0); Assert.IsFalse(value1 < value2); }
public DataTable CheckLockoutByUserName(string userName) { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[pr_Employees_SelectOneByUserName]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("Employees"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 20, 0, "", DataRowVersion.Proposed, userName)); cmdToExecute.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); cmdToExecute.Parameters.Add(new SqlParameter("@ErrorDesc", SqlDbType.VarChar, 100, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, _errorDesc)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (SqlInt32)cmdToExecute.Parameters["@ErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'pr_Employees_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _employee_ID = (Int32)toReturn.Rows[0]["Employee_ID"]; _firstName = (string)toReturn.Rows[0]["FirstName"]; _middleName = toReturn.Rows[0]["MiddleName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["MiddleName"]; _lastName = toReturn.Rows[0]["LastName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["LastName"]; _userName = toReturn.Rows[0]["UserName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["UserName"]; _passwordHash = toReturn.Rows[0]["PasswordHash"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["PasswordHash"]; _role = toReturn.Rows[0]["Role"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["Role"]; _createdDate = toReturn.Rows[0]["CreatedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["CreatedDate"]; _createdUser = toReturn.Rows[0]["CreatedUser"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CreatedUser"]; _supervisor_ID = toReturn.Rows[0]["Supervisor_ID"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["Supervisor_ID"]; _lastModifiedDate = toReturn.Rows[0]["LastModifiedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["LastModifiedDate"]; _lastModifiedUser = toReturn.Rows[0]["LastModifiedUser"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LastModifiedUser"]; _lastLoginDate = toReturn.Rows[0]["LastLoginDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["LastLoginDate"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("Employees::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
public void ToByteArray(bool timeZone) { var value = new SqlDateTime(2001, 01, 03, 10, 22, 03, 0); var bytes = value.ToByteArray(timeZone); var expectedLength = timeZone ? 13 : 11; Assert.IsNotNull(bytes); Assert.AreNotEqual(0, bytes.Length); Assert.AreEqual(expectedLength, bytes.Length); var rebuilt = new SqlDateTime(bytes); Assert.AreEqual(value, rebuilt); }
private static SqlDateTime ToTime(SqlDateTime dateTime) { return new SqlDateTime(0, 0, 0, dateTime.Hour, dateTime.Minute, dateTime.Second, dateTime.Millisecond, dateTime.Offset); }
public void ToDateTimeOffset_WithoutOffset() { var value = new SqlDateTime(2001, 01, 03, 10, 22, 03, 0); var date = value.ToDateTimeOffset(); Assert.AreEqual(2001, date.Year); Assert.AreEqual(01, date.Month); Assert.AreEqual(03, date.Day); Assert.AreEqual(10, date.Hour); Assert.AreEqual(22, date.Minute); Assert.AreEqual(03, date.Second); Assert.AreEqual(0, date.Millisecond); Assert.AreEqual(0, date.Offset.Hours); Assert.AreEqual(0, date.Offset.Minutes); }
public void SqlDateTimeToSqlString() { SqlDateTime testTime = new SqlDateTime(2002, 10, 22, 9, 52, 30); Assert.Equal(testTime.Value.ToString((IFormatProvider)null), ((SqlString)testTime).Value); }
public DataTable SelectDS_CongViec(SqlDateTime tuNgay, SqlDateTime denNgay, SqlDateTime batDauTuNgay, SqlDateTime batDauDenNgay, int id_NhanSu) { SqlCommand scmCmdToExecute = new SqlCommand(); scmCmdToExecute.CommandText = "dbo.[pr_CongViec_Select_MainForm]"; scmCmdToExecute.CommandType = CommandType.StoredProcedure; DataTable dtToReturn = new DataTable("CongViec"); SqlDataAdapter sdaAdapter = new SqlDataAdapter(scmCmdToExecute); // Use base class' connection object scmCmdToExecute.Connection = m_scoMainConnection; try { scmCmdToExecute.Parameters.Add(new SqlParameter("@ID_NhanSu", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, id_NhanSu)); scmCmdToExecute.Parameters.Add(new SqlParameter("@TuNgay", SqlDbType.SmallDateTime, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, tuNgay)); scmCmdToExecute.Parameters.Add(new SqlParameter("@DenNgay", SqlDbType.SmallDateTime, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, denNgay)); scmCmdToExecute.Parameters.Add(new SqlParameter("@BatDauTuNgay", SqlDbType.SmallDateTime, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, batDauTuNgay)); scmCmdToExecute.Parameters.Add(new SqlParameter("@BatDauDenNgay", SqlDbType.SmallDateTime, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, batDauDenNgay)); if (m_bMainConnectionIsCreatedLocal) { // Open connection. m_scoMainConnection.Open(); } else { if (m_cpMainConnectionProvider.IsTransactionPending) { scmCmdToExecute.Transaction = m_cpMainConnectionProvider.CurrentTransaction; } } // Execute query. sdaAdapter.Fill(dtToReturn); return(dtToReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("clsCongViec::SelectDS_CongViec::Error occured.", ex); } finally { if (m_bMainConnectionIsCreatedLocal) { // Close connection. m_scoMainConnection.Close(); } scmCmdToExecute.Dispose(); sdaAdapter.Dispose(); } }
public static DateTime?ToNullable(this SqlDateTime @this) => @this.IsNull ? (DateTime?)null : @this.Value;
public void CastStringToTime() { var exp = SqlExpression.Cast(SqlExpression.Constant(DataObject.String("22:13:01")), PrimitiveTypes.Time()); SqlExpression casted = null; Assert.DoesNotThrow(() => casted = exp.Evaluate()); Assert.IsNotNull(casted); Assert.IsInstanceOf<SqlConstantExpression>(casted); var value = ((SqlConstantExpression)casted).Value; Assert.IsNotNull(value.Value); Assert.IsInstanceOf<DateType>(value.Type); Assert.AreEqual(SqlTypeCode.Time, value.Type.SqlType); // we round the expected value to the result offset because of the UTC parsing logic // of the date type: all we care here is the time component var result = ((SqlDateTime)value.Value); var expected = new SqlDateTime(1, 1, 1, 22, 13, 01, 0, result.Offset); Assert.AreEqual(expected, result); }
public ParameteredCollection(SqlString companyCode, SqlString customerNumber, SqlDateTime weekEndDate, SqlInt16 numOfWeeks) { ParameterCollection = new HybridDictionary { { "CompanyCode", companyCode }, { "CustomerNumber", customerNumber }, { "WeekEndDate", weekEndDate }, { "NumOfWeeks", numOfWeeks } }; }
public void NextDay() { var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32); var value = Select("NEXT_DAY", SqlExpression.Constant(date), SqlExpression.Constant("Wednesday")); Assert.IsNotNull(value); Assert.IsFalse(value.IsNull); Assert.IsInstanceOf<DateType>(value.Type); Assert.IsInstanceOf<SqlDateTime>(value.Value); // TODO: Assert result }
public static SqlBoolean SetTimeValue(SqlString deviceName, SqlByte pointType, SqlByte logicalNumber, SqlByte parameterNumber, SqlDateTime value) { //var deviceNameValue = deviceName.Value; //var timeParameter = new TimeParameter(new Tlp(pointType.Value, logicalNumber.Value, parameterNumber.Value)) { Value = value.Value }; //(DeviceSQL.Watchdog.Worker.Devices.First(device => (device.Name == deviceNameValue)) as Device.ROC.ROCMaster).WriteParameter<TimeParameter>(null, null, null, null, timeParameter); return(true); }
public void SubtractDayToSecondFromDate() { var date = new SqlDateTime(2010, 11, 03, 05, 22, 43, 0); var dts = new SqlDayToSecond(19, 08, 23, 1); var result = date.Subtract(dts); Assert.IsNotNull(result); Assert.IsFalse(result.IsNull); Assert.AreEqual(2010, result.Year); Assert.AreEqual(10, result.Month); Assert.AreEqual(14, result.Day); Assert.AreEqual(20, result.Hour); Assert.AreEqual(59, result.Minute); Assert.AreEqual(42, result.Second); Assert.AreEqual(0, result.Millisecond); }
public void SqlDateTimeToSqlString() { SqlDateTime TestTime = new SqlDateTime(2002, 10, 22, 9, 52, 30); Assert.AreEqual("22/10/2002 9:52:30 AM", ((SqlString)TestTime).Value, "#T01"); }
private static SqlDateTime ToDate(SqlDateTime dateTime) { return new SqlDateTime(dateTime.Year, dateTime.Month, dateTime.Day, 0, 0, 0, 0, dateTime.Offset); }
/// <summary> /// Purpose: Select method. This method will Select one existing row from the database, based on the Primary Key. /// </summary> /// <returns>DataTable object if succeeded, otherwise an Exception is thrown. </returns> /// <remarks> /// Properties needed for this method: /// <UL> /// <LI>Guest_ID</LI> /// </UL> /// Properties set after a succesful call of this method: /// <UL> /// <LI>ErrorCode</LI> /// <LI>Guest_ID</LI> /// <LI>FirstName</LI> /// <LI>MiddleName</LI> /// <LI>LastName</LI> /// <LI>Salutation</LI> /// <LI>Civil_ID</LI> /// <LI>MobileNumber</LI> /// <LI>Email</LI> /// <LI>LastBookingDate</LI> /// <LI>CreatedUser</LI> /// <LI>CreatedDate</LI> /// <LI>LastModifiedUser</LI> /// <LI>LastModifiedDate</LI> /// <LI>PreferredPayment_Mode</LI> /// <LI>Nationality</LI> /// <LI>CardNumber</LI> /// </UL> /// Will fill all properties corresponding with a field in the table with the value of the row selected. /// </remarks> public override DataTable SelectOne() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "dbo.[pr_Guests_SelectOne]"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataTable toReturn = new DataTable("Guests"); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); // Use base class' connection object cmdToExecute.Connection = _mainConnection; try { cmdToExecute.Parameters.Add(new SqlParameter("@iGuest_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _guest_ID)); cmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _errorCode)); if (_mainConnectionIsCreatedLocal) { // Open connection. _mainConnection.Open(); } else { if (_mainConnectionProvider.IsTransactionPending) { cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction; } } // Execute query. adapter.Fill(toReturn); _errorCode = (SqlInt32)cmdToExecute.Parameters["@iErrorCode"].Value; if (_errorCode != (int)LLBLError.AllOk) { // Throw error. throw new Exception("Stored Procedure 'pr_Guests_SelectOne' reported the ErrorCode: " + _errorCode); } if (toReturn.Rows.Count > 0) { _guest_ID = (Int32)toReturn.Rows[0]["Guest_ID"]; _firstName = toReturn.Rows[0]["FirstName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["FirstName"]; _middleName = toReturn.Rows[0]["MiddleName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["MiddleName"]; _lastName = toReturn.Rows[0]["LastName"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["LastName"]; _salutation = toReturn.Rows[0]["Salutation"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["Salutation"]; _gender = toReturn.Rows[0]["Gender"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["Gender"]; _dateOfBirth = toReturn.Rows[0]["DateOfBirth"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["DateOfBirth"]; _civil_ID = toReturn.Rows[0]["Civil_ID"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["Civil_ID"]; _mobileNumber = toReturn.Rows[0]["MobileNumber"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["MobileNumber"]; _email = toReturn.Rows[0]["Email"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["Email"]; _lastBookingDate = toReturn.Rows[0]["LastBookingDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["LastBookingDate"]; _createdUser = toReturn.Rows[0]["CreatedUser"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CreatedUser"]; _createdDate = toReturn.Rows[0]["CreatedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["CreatedDate"]; _lastModifiedUser = toReturn.Rows[0]["LastModifiedUser"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LastModifiedUser"]; _lastModifiedDate = toReturn.Rows[0]["LastModifiedDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["LastModifiedDate"]; _preferredPayment_Mode = toReturn.Rows[0]["PreferredPayment_Mode"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["PreferredPayment_Mode"]; _nationality = toReturn.Rows[0]["Nationality"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["Nationality"]; _passportNumber = toReturn.Rows[0]["PassportNumber"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["PassportNumber"]; _category = toReturn.Rows[0]["Category"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["Category"]; _cardNumber = toReturn.Rows[0]["CardNumber"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["CardNumber"]; _isBlackListed = toReturn.Rows[0]["IsBlackListed"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["IsBlackListed"]; _preferences = toReturn.Rows[0]["Preferrences"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["Preferrences"]; } return(toReturn); } catch (Exception ex) { // some error occured. Bubble it to caller and encapsulate Exception object throw new Exception("Guests::SelectOne::Error occured.", ex); } finally { if (_mainConnectionIsCreatedLocal) { // Close connection. _mainConnection.Close(); } cmdToExecute.Dispose(); adapter.Dispose(); } }
private SqlString ToString(SqlDateTime dateTime) { if (dateTime.IsNull) return SqlString.Null; if (TypeCode == SqlTypeCode.Date) return dateTime.ToDateString(); if (TypeCode == SqlTypeCode.Time) return dateTime.ToTimeString(); if (TypeCode == SqlTypeCode.TimeStamp) return dateTime.ToTimeStampString(); return SqlString.Null; }
public static void CreateActivity(SqlString url, SqlString username, SqlString password, SqlString token, SqlDateTime createdDT, SqlBoolean externalMessage, SqlString employeeId, SqlString actUrl, SqlString actTitle, SqlString actBody) { IChatterSoapService service = new ChatterSoapService(url.Value); service.AllowUntrustedConnection(); service.Login(username.Value, password.Value, token.Value); service.CreateProfileActivity(employeeId.IsNull ? null : employeeId.Value, actUrl.IsNull ? null : actUrl.Value, actTitle.IsNull ? null : actTitle.Value, actBody.IsNull ? null : actBody.Value, createdDT.Value); if (externalMessage.IsTrue) { service.CreateExternalMessage(actUrl.IsNull ? null : actUrl.Value, actTitle.IsNull ? null : actTitle.Value, actBody.IsNull ? null : actBody.Value, employeeId.IsNull ? null : employeeId.Value); } }