/// <summary>
/// 构造函数
/// </summary>
public DBHelper(string providerName, string connectionString, string sqlType)
{
this._sqlType = sqlType;
if (string.Compare(sqlType, "Mysql", true) == 0)
{
_connection = new MySqlConnection();
_command = new MySqlCommand();
this._paraPrefix = "?";
}
else
{
_provider = GetDbProvider(providerName);
_connection = _provider.CreateConnection();
_command = _provider.CreateCommand();
if (string.Compare(sqlType, "Oracle", true) == 0)
{
this._paraPrefix = ":";
}
else if (string.Compare(sqlType, "SQLServer", true) == 0)
{
this._paraPrefix = "@";
}
}
_connection.ConnectionString = connectionString;
_command.Connection = _connection;
}
public void AddParameter(DbCommand command, string name, DbType dbType)
{
DbParameter param = command.CreateParameter();
param.ParameterName = name;
param.DbType = dbType;
command.Parameters.Add(param);
}
public void DeleteUser(int id)
{
try
{
connection.OpenConnection();
connection.BeginTransaction();
DbCommand command = new DbCommand("DELETE FROM Users WHERE Id = @id;");
command.Parameters = new SqlParameter[1];
command.Parameters[0] = new SqlParameter("id", id);
command.Type = DbCommand.DbCommandType.DELETE;
connection.ExecNonQuery(command);
if (UserCollection != null)
{
UserCollection.Remove(UserCollection.Find(x => x.Id == id));
}
ReassignTasks(id);
connection.CommitTransaction();
}
catch (Exception e)
{
connection.RollbackTransaction();
Logger.Instance.WriteToLog(e.StackTrace);
throw;
}
finally
{
connection.CloseConnection();
}
}
public void AddParameter(DbCommand command, string name, object value)
{
DbParameter param = command.CreateParameter();
param.ParameterName = name;
param.Value = value ?? (object)DBNull.Value;
command.Parameters.Add(param);
}
protected override bool SameNumberOfParametersAndValues(DbCommand command, object[] values)
{
int returnParameterCount = 0;
int numberOfParametersToStoredProcedure = command.Parameters.Count - returnParameterCount;
int numberOfValuesProvidedForStoredProcedure = values.Length;
return numberOfParametersToStoredProcedure == numberOfValuesProvidedForStoredProcedure;
}
public void AddUser(IUser user)
{
try
{
connection.OpenConnection();
DbCommand command = new DbCommand("INSERT INTO Users (Login, Password, Name, Surname, Email, IsActive, UserType) VALUES (@login, @password, @name, @surname, @email, @isactive, @usertype);");
command.Parameters = new SqlParameter[7];
command.Parameters[0] = new SqlParameter("login", user.Login);
command.Parameters[1] = new SqlParameter("password", user.Password);
command.Parameters[2] = new SqlParameter("name", user.Name);
command.Parameters[3] = new SqlParameter("surname", user.Surname);
command.Parameters[4] = new SqlParameter("email", user.Email);
command.Parameters[5] = new SqlParameter("isactive", user.IsActive.ToString());
command.Parameters[6] = new SqlParameter("usertype", (int)user.UserType);
command.Type = DbCommand.DbCommandType.INSERT;
connection.ExecNonQuery(command);
//collection doesn't contain user Id here;
UserCollection = null;
}
catch (Exception e)
{
Logger.Instance.WriteToLog(e.StackTrace);
throw;
}
finally
{
connection.CloseConnection();
}
}
public void DeleteRisk(int id)
{
try
{
connection.OpenConnection();
DbCommand command = new DbCommand("DELETE FROM Risks WHERE Id = @id;");
command.Parameters = new SqlParameter[1];
command.Parameters[0] = new SqlParameter("id", id);
command.Type = DbCommand.DbCommandType.DELETE;
connection.ExecNonQuery(command);
if (RiskCollection != null)
{
RiskCollection.Remove(RiskCollection.Find(x => x.Id == id));
}
}
catch (Exception e)
{
Logger.Instance.WriteToLog(e.StackTrace);
throw;
}
finally
{
connection.CloseConnection();
}
}
public void SetParameter(DbCommand cmd, string name, object value)
{
if (!cmd.Parameters.Contains(name)) {
throw new ArgumentException($"Parameter {name} not declared.");
}
cmd.Parameters[name].Value = value;
}
public void Run(object o, EventArgs e)
{
Photo[] photos = MainWindow.Toplevel.SelectedPhotos ();
if (photos.Length == 0) {
Console.WriteLine ("no photos selected, returning");
return;
}
DateTime import_time = photos[0].Time;
foreach (Photo p in photos)
if (p.Time > import_time)
import_time = p.Time;
RollStore rolls = App.Instance.Database.Rolls;
Roll roll = rolls.Create(import_time);
foreach (Photo p in photos) {
DbCommand cmd = new DbCommand ("UPDATE photos SET roll_id = :roll_id " +
"WHERE id = :id ",
"roll_id", roll.Id,
"id", p.Id);
App.Instance.Database.Database.ExecuteNonQuery (cmd);
p.RollId = roll.Id;
}
Console.WriteLine ("RetroactiveRoll done: " + photos.Length + " photos in roll " + roll.Id);
}
public BuilderData(DbCommand command, string tableName)
{
TableName = tableName;
Command = command;
Columns = new List<BuilderColumn>();
Where = new List<BuilderColumn>();
WhereString = new List<string>();
}
static void Main(string[] args)
{
var command = new DbCommand(new OracleConnection("oracle"), "SaveChanges()" );
command.Execute();
var command1 = new DbCommand(new SqlConnection("SQL"), "Clear()" );
command1.Execute();
}
public override void AddParameter(DbCommand command, string name, DbType dbType, int size,
ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion,
object value)
{
DbParameter parameter = this.CreateParameter(name, dbType, size,
direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
command.Parameters.Add(parameter);
}
protected static DbParameter AddParameter(DbCommand cmd, string name, object val)
{
DbParameter p = cmd.CreateParameter ();
p.ParameterName = name;
p.Value = val;
cmd.Parameters.Add (p);
return p;
}
private static void PrepareCommand(DbCommand cmd, MySqlConnection conn, CommandType cmdType, string cmdText)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
}
internal EasylinkCommand(DbCommand command, string schemaName)
{
_command = command;
_schemaName = schemaName;
Sqls = new List<string>();
}
public int DeclareParameter(DbCommand cmd, string name, DbType type)
{
if (cmd.Parameters.Contains(name)) {
throw new ArgumentException($"Parameter {name} already declared.");
}
DbParameter param = cmd.CreateParameter();
param.ParameterName = name;
param.DbType = type;
return cmd.Parameters.Add(param);
}
public DataTable ExecuteTable(DbCommand cmd)
{
using (DataSet st = new DataSet())
{
DbDataAdapter ap = CreateDataAdapter();
ap.SelectCommand = cmd;
ap.Fill(st);
return st.Tables[0];
}
}
/// <summary>
/// 여러 ResultSet을 반환할 수 있으므로, DataTable의 컬렉션으로 반환합니다.
/// </summary>
public override IList<DataTable> ExecuteDataTableAsList(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters) {
cmd.ShouldBeInstanceOf<MySqlCommand>("cmd");
return With.TryFunctionAsync(() => Db.ExecuteDataTableAsListAsync((MySqlCommand)cmd,
firstResult,
maxResults,
parameters)
.Result,
() => new List<DataTable>());
}
/// <summary>
/// <paramref name="cmd"/>를 실행하여, 결과를 DataSet으로 반환합니다.
/// </summary>
/// <param name="cmd">실행할 <see cref="DbCommand"/> instance.</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
/// <param name="parameters">collectio of parameters of Command</param>
/// <returns>결과 셋이 담긴 DataSet</returns>
public override DataSet ExecuteDataSet(DbCommand cmd, int firstResult, int maxResults, params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
var result = new DataSet();
var tables = ExecuteDataTableAsList(cmd, firstResult, maxResults, parameters);
if(tables != null)
result.Tables.AddRange(tables.ToArray());
return result;
}
public int ExecuteNonQuery(DbCommand cmd)
{
DbConnection conn = null;
try {
conn = this.GetOpenConnection();
cmd.Connection = conn;
return cmd.ExecuteNonQuery();
} finally {
this.ReleaseConnection(conn);
}
}
protected override void AddParameter(DbCommand command, QueryParameter parameter, object value)
{
DbQueryType sqlType = (DbQueryType)parameter.QueryType;
if (sqlType == null)
sqlType = (DbQueryType)this.provider.Language.TypeSystem.GetColumnType(parameter.Type);
var p = ((MySqlCommand)command).Parameters.Add(parameter.Name, ToMySqlDbType(sqlType.SqlDbType), sqlType.Length);
if (sqlType.Precision != 0)
p.Precision = (byte)sqlType.Precision;
if (sqlType.Scale != 0)
p.Scale = (byte)sqlType.Scale;
p.Value = value ?? DBNull.Value;
}
public IDataReader ExecuteReader(DbCommand cmd)
{
DbConnection conn = null;
try {
conn = this.GetOpenConnection();
cmd.Connection = conn;
CommandBehavior behavior = this.IsSharedConnection() ? CommandBehavior.Default : CommandBehavior.CloseConnection;
return cmd.ExecuteReader(behavior);
} catch {
ReleaseConnection(conn);
throw;
}
}
public override void AddCommandParameters(List<KeyValuePair<string, object>> parameters, DbCommand adoCommand)
{
// configure the parameters
foreach (KeyValuePair<string, object> keyValue in parameters)
{
MySqlCommand command = adoCommand as MySqlCommand;
if (command == null)
throw new ArgumentException("Invalid command received");
command.Parameters.AddWithValue(keyValue.Key, keyValue.Value);
}
}
/// <summary>
/// ���캯��--���ط�����
/// </summary>
/// <param name="connectionstring">���ݿ�����</param>
/// <param name="databasetype">���ݿ������</param>
public DBHelper(string connectionstring, string databasetype)
{
if (databasetype == "MySql.Data.MySqlClient")
{
MyFactory = new MySqlClientFactory();
}
else
{
MyFactory = DbProviderFactories.GetFactory(databasetype);
}
MyConnection = MyFactory.CreateConnection();
MyConnection.ConnectionString = (databasetype.ToString() == "System.Data.OleDb") ? ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + GetDataPath(connectionstring) + ";") : (connectionstring);
MyCommand = MyConnection.CreateCommand();
}
//public static int ExecuteNonQuery(string queryText)
//{
// switch (DatabaseSettings.DbType)
// {
// case Enums.DatabaseType.MsSQL:
// using (SqlConnection sq = new SqlConnection(DatabaseSettings.ConnectionString))
// {
// sq.Open();
// using (SqlCommand sc = new SqlCommand(queryText, sq))
// {
// return sc.ExecuteNonQuery();
// }
// }
// case Enums.DatabaseType.MySQL:
// using (MySqlConnection sq = new MySqlConnection(DatabaseSettings.ConnectionString))
// {
// sq.Open();
// using (MySqlCommand sc = new MySqlCommand(queryText, sq))
// {
// return sc.ExecuteNonQuery();
// }
// }
// default:
// return 0;
// }
//}
public static int ExecuteNonQuery(DbCommand query, DbConnection dc)
{
switch (DatabaseSettings.DbType)
{
case Enums.DatabaseType.MsSQL:
case Enums.DatabaseType.MySQL:
using (query)
{
query.Connection = dc;
return query.ExecuteNonQuery();
}
default:
return -1;
}
}
public int DeclareParamater(DbCommand command, string name, DbType type)
{
if (!command.Parameters.Contains(name)) {
string dbType = type.ToString();
if(type == DbType.DateTime) {
return command.Parameters.Add(new MySqlParameter(name, MySqlDbType.DateTime));
}
else if(type == DbType.Double) {
return command.Parameters.Add(new MySqlParameter(name, MySqlDbType.Double));
}
else {
return command.Parameters.Add(new MySqlParameter(name, dbType));
}
} else {
throw new ArgumentException(string.Format("Parameter {0} already exists", name));
}
}
/// <summary>
/// Gets the ID for the row that was inserted into the database. Only valid when the
/// query contains an auto-increment column and the operation being performed is an insert.
/// </summary>
/// <param name="command">The <see cref="DbCommand"/> that was executed and the last inserted id needs to be acquired for.</param>
/// <returns>
/// The last inserted id for the executed <paramref name="command"/>.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="command"/> is null.</exception>
/// <exception cref="TypeException"><paramref name="command"/> is not of the excepted type.</exception>
/// <exception cref="ArgumentException"><paramref name="command"/> is invalid in some other way.</exception>
public override long GetLastInsertedId(DbCommand command)
{
if (command == null)
throw new ArgumentNullException("command");
var c = command as MySqlCommand;
if (c == null)
{
const string errmsg = "Expected DbCommand `{0}` to be of type `{1}`, but was type `{2}`.";
throw new TypeException(string.Format(errmsg, command, typeof(MySqlCommand), command.GetType()));
}
Debug.Assert(c.LastInsertedId != 0,
"The LastInsertedId shouldn't ever be 0 for MySql since AutoIncrement starts at 1...");
return c.LastInsertedId;
}
/// <summary>
/// Command轉成TSQL
/// </summary>
/// <param name="cmd">欲執行Command</param>
/// <returns></returns>
public string ConvertTSQL(DbCommand cmd)
{
try
{
if (!string.IsNullOrEmpty(cmd.CommandText))//無內容則跳出
{
string sb = (cmd.CommandText);
foreach (DbParameter dp in cmd.Parameters)
{
string pattern;
if (ConvertUtility.IsString(dp.DbType))//是否為文字
{
pattern = dp.ParameterName + @"\W";
Match mc = Regex.Match(sb, pattern);
if (!mc.Success)
break;
sb = Regex.Replace(sb.ToString(),
pattern,
string.Format("'{0}'{1}",
dp.Value, mc.Value[mc.Length - 1]),
RegexOptions.None);
}
else
{
pattern = dp.ParameterName + @"\W";
Match mc = Regex.Match(sb, pattern);
if (!mc.Success)
break;
sb = Regex.Replace(sb.ToString(),
pattern,
string.Format("{0}{1}",
dp.Value, mc.Value[mc.Length - 1]),
RegexOptions.None);
}
}
return sb.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
return string.Empty;
}
public SQLCommand(string query, DbConnection connection, Profiler profiler = null, params object[] arguments)
{
CommandProfiler = profiler;
Query = string.Format(query, arguments);
// All logic assumes MySQL is the "edge case", and the fallback is postgre.
if (connection is MySqlConnection)
{
Query = Query.Replace(":", "?").Replace("at time zone 'UTC'", "");
Query = cast_replace.Replace(Query, "$1");
SqlType = new SqlTypes(MySqlDbType.Int32, MySqlDbType.Text, MySqlDbType.Double, MySqlDbType.Bit, MySqlDbType.VarChar);
}
else
{
SqlType = new SqlTypes(NpgsqlDbType.Integer, NpgsqlDbType.Text, NpgsqlDbType.Double, NpgsqlDbType.Boolean, NpgsqlDbType.Varchar);
}
Command = DatabaseConnectionProvider.GetCommand(Query, connection);
}
private static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (DbParameter p in commandParameters)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
public static object ExecuteScalar(this DbCommand dBCommand, string sqlStatement)
{
dBCommand.CommandText = sqlStatement;
return(dBCommand.ExecuteScalar());
}
private static void RewriteFullTextQuery(DbCommand cmd)
{
if (cmd.CommandText.Contains(DatabaseExtensionFunctions.InterceptFullTextSearchFunction))
{
var parseFtsQuery = new Regex(@"\(\[Rhetos\]\.\[" + DatabaseExtensionFunctions.InterceptFullTextSearchFunction + @"\]\((?<itemId>.+?), (?<pattern>.*?), (?<tableName>.*?), (?<searchColumns>.*?)\)\) = 1", RegexOptions.Singleline);
var ftsQueries = parseFtsQuery.Matches(cmd.CommandText).Cast <Match>();
// Note: When modifying regex, update the error description below!
var itemIdFormat = new Regex(@"^(\[\w+\]|\w+)(\.(\[\w+\]|\w+))+$", RegexOptions.Singleline);
var tableNameFormat = new Regex(@"^N'(?<unquoted>(\[\w+\]|\w+)(\.(\[\w+\]|\w+))+)'$", RegexOptions.Singleline);
var searchColumnsFormat = new Regex(@"^N'(?<unquoted>(\w+|\(\w+(\s*,\s*\w+)*\)|\*))'$", RegexOptions.Singleline);
var patternFormat = new Regex(@"^(\@\w+|N'([^']*('')*)*')$", RegexOptions.Singleline);
foreach (var ftsQuery in ftsQueries.OrderByDescending(m => m.Index))
{
// T-SQL CONTAINSTABLE function does not support parameters for table name and columns list,
// so the LINQ query must contain string literals for this interceptor to construct a valid SQL subquery with CONTAINSTABLE.
// Formatting validations are used here to avoid SQL injection.
string itemId = ftsQuery.Groups["itemId"].Value;
string tableName = ftsQuery.Groups["tableName"].Value;
string searchColumns = ftsQuery.Groups["searchColumns"].Value;
string pattern = ftsQuery.Groups["pattern"].Value;
if (pattern == "CAST(NULL AS varchar(1))")
{
throw new FrameworkException("Invalid FTS search pattern format. Search pattern must not be NULL.");
}
var validations = new[]
{
new { Parameter = "itemId", GeneratedSql = itemId, Format = itemIdFormat,
InfoSql = "a multi-part identifier",
InfoLinq = "a simple property of the queried data structure" },
new { Parameter = "tableName", GeneratedSql = tableName, Format = tableNameFormat,
InfoSql = "a quoted multi-part identifier",
InfoLinq = "a string literal, not a variable," },
new { Parameter = "searchColumns", GeneratedSql = searchColumns, Format = searchColumnsFormat,
InfoSql = "a quoted column, column list or '*' (see CONTAINSTABLE on MSDN)",
InfoLinq = "a string literal, not a variable," },
new { Parameter = "pattern", GeneratedSql = pattern, Format = patternFormat,
InfoSql = "an SQL query parameter or a quoted string without single-quote characters",
InfoLinq = "a simple string variable, not an expression," }, // Not mentioning string literals to simplify the message.
};
foreach (var test in validations)
{
if (!test.Format.IsMatch(test.GeneratedSql))
{
throw new FrameworkException("Invalid FullTextSearch '" + test.Parameter + "' parameter format in LINQ query."
+ " Please use " + test.InfoLinq + " for the '" + test.Parameter + "' parameter of the FullTextSeach method."
+ " The resulting SQL expression (" + test.GeneratedSql + ") should be " + test.InfoSql + ".");
}
}
string ftsSql = string.Format("{0} IN (SELECT [KEY] FROM CONTAINSTABLE({1}, {2}, {3}))",
itemId,
tableNameFormat.Match(tableName).Groups["unquoted"].Value,
searchColumnsFormat.Match(searchColumns).Groups["unquoted"].Value,
pattern);
cmd.CommandText =
cmd.CommandText.Substring(0, ftsQuery.Index)
+ ftsSql
+ cmd.CommandText.Substring(ftsQuery.Index + ftsQuery.Length);
}
}
if (cmd.CommandText.Contains(DatabaseExtensionFunctions.InterceptFullTextSearchFunction))
{
throw new FrameworkException("Error while parsing FTS query. Not all search conditions were handled.");
}
}
/// <summary>
/// Gets rows from the datasource based on the PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID index.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="_employeeId">Employee identification number. Foreign key to Employee.EmployeeID.</param>
/// <param name="_startDate">Date the employee started work in the department.</param>
/// <param name="_departmentId">Department in which the employee worked including currently. Foreign key to Department.DepartmentID.</param>
/// <param name="_shiftId">Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out parameter to get total records for query.</param>
/// <returns>Returns an instance of the <see cref="Nettiers.AdventureWorks.Entities.EmployeeDepartmentHistory"/> class.</returns>
/// <remarks></remarks>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public override Nettiers.AdventureWorks.Entities.EmployeeDepartmentHistory GetByEmployeeIdStartDateDepartmentIdShiftId(TransactionManager transactionManager, System.Int32 _employeeId, System.DateTime _startDate, System.Int16 _departmentId, System.Byte _shiftId, int start, int pageLength, out int count)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "HumanResources.usp_adwTiers_EmployeeDepartmentHistory_GetByEmployeeIdStartDateDepartmentIdShiftId", _useStoredProcedure);
database.AddInParameter(commandWrapper, "@EmployeeId", DbType.Int32, _employeeId);
database.AddInParameter(commandWrapper, "@StartDate", DbType.DateTime, _startDate);
database.AddInParameter(commandWrapper, "@DepartmentId", DbType.Int16, _departmentId);
database.AddInParameter(commandWrapper, "@ShiftId", DbType.Byte, _shiftId);
IDataReader reader = null;
TList <EmployeeDepartmentHistory> tmp = new TList <EmployeeDepartmentHistory>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByEmployeeIdStartDateDepartmentIdShiftId", tmp));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
//Create collection and fill
Fill(reader, tmp, start, pageLength);
count = -1;
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "GetByEmployeeIdStartDateDepartmentIdShiftId", tmp));
}
finally
{
if (reader != null)
{
reader.Close();
}
commandWrapper = null;
}
if (tmp.Count == 1)
{
return(tmp[0]);
}
else if (tmp.Count == 0)
{
return(null);
}
else
{
throw new DataException("Cannot find the unique instance of the class.");
}
//return rows;
}
public void SetParameter(DbCommand cmd, string column, object obj)
{
Db.SetParameterValue(cmd, column, obj);
}
public void AddInParameter(DbCommand cmd, string column, DbType type, object obj)
{
Db.AddInParameter(cmd, column, type, obj);
}
protected override Task EnqueueMessages(DbCommand command, IEnumerable <IMessageDescriptor> messageDescriptors, CancellationToken cancellationToken) => CompletedTask;
protected static FR_L5DIGAPfTID_1318_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5DIGAPfTID_1318 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
var returnStatus = new FR_L5DIGAPfTID_1318_Array();
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
var commandLocation = "CL5_MyHealthClub_Diagnosis.Atomic.Manipulation.SQL.cls_Get_AllProcedures_for_TenantID.sql";
command.CommandText = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(commandLocation)).ReadToEnd();
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ticket", securityTicket);
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "OrderBy", Parameter.OrderBy);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "OrderValue", Parameter.OrderValue);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "StartIndex", Parameter.StartIndex);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "NumberOfElements", Parameter.NumberOfElements);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "SearchCriterium", Parameter.SearchCriterium);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "LanguageID", Parameter.LanguageID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CatalogID", Parameter.CatalogID);
/***For Order**/
string newText = command.CommandText.Replace("@OrderValue", Parameter.OrderValue);
command.CommandText = newText;
/***For Search**/
string newText2 = command.CommandText.Replace("@SearchCriterium", Parameter.SearchCriterium);
command.CommandText = newText2;
List <L5DIGAPfTID_1318> results = new List <L5DIGAPfTID_1318>();
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
try
{
reader.SetOrdinals(new string[] { "HEC_TRE_PotentialProcedureID", "PotentialProcedure_Name_DictID", "PotentialProcedure_Description_DictID", "Code" });
while (reader.Read())
{
L5DIGAPfTID_1318 resultItem = new L5DIGAPfTID_1318();
//0:Parameter HEC_TRE_PotentialProcedureID of type Guid
resultItem.HEC_TRE_PotentialProcedureID = reader.GetGuid(0);
//1:Parameter PotentialProcedure_Name of type Dict
resultItem.PotentialProcedure_Name = reader.GetDictionary(1);
resultItem.PotentialProcedure_Name.SourceTable = "hec_tre_potentialprocedures";
loader.Append(resultItem.PotentialProcedure_Name);
//2:Parameter PotentialProcedure_Description of type Dict
resultItem.PotentialProcedure_Description = reader.GetDictionary(2);
resultItem.PotentialProcedure_Description.SourceTable = "hec_tre_potentialprocedures";
loader.Append(resultItem.PotentialProcedure_Description);
//3:Parameter Code of type String
resultItem.Code = reader.GetString(3);
results.Add(resultItem);
}
}
catch (Exception ex)
{
reader.Close();
throw new Exception("Exception occured durng data retrieval in method cls_Get_AllProcedures_for_TenantID", ex);
}
reader.Close();
//Load all the dictionaries from the datatables
loader.Load();
returnStatus.Result = results.ToArray();
return(returnStatus);
}
} //end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.EmployeeDepartmentHistory objects.</returns>
public override TList <EmployeeDepartmentHistory> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
{
return(new TList <EmployeeDepartmentHistory>());
}
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "HumanResources.usp_adwTiers_EmployeeDepartmentHistory_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
{
searchUsingOR = true;
}
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@EmployeeId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@DepartmentId", DbType.Int16, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShiftId", DbType.Byte, DBNull.Value);
database.AddInParameter(commandWrapper, "@StartDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@EndDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@ModifiedDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|");
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = { '=' };
char[] singleQuote = { '\'' };
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("employeeid ") || clause.Trim().StartsWith("employeeid="))
{
database.SetParameterValue(commandWrapper, "@EmployeeId",
clause.Trim().Remove(0, 10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("departmentid ") || clause.Trim().StartsWith("departmentid="))
{
database.SetParameterValue(commandWrapper, "@DepartmentId",
clause.Trim().Remove(0, 12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shiftid ") || clause.Trim().StartsWith("shiftid="))
{
database.SetParameterValue(commandWrapper, "@ShiftId",
clause.Trim().Remove(0, 7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("startdate ") || clause.Trim().StartsWith("startdate="))
{
database.SetParameterValue(commandWrapper, "@StartDate",
clause.Trim().Remove(0, 9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("enddate ") || clause.Trim().StartsWith("enddate="))
{
database.SetParameterValue(commandWrapper, "@EndDate",
clause.Trim().Remove(0, 7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("modifieddate ") || clause.Trim().StartsWith("modifieddate="))
{
database.SetParameterValue(commandWrapper, "@ModifiedDate",
clause.Trim().Remove(0, 12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList <EmployeeDepartmentHistory> rows = new TList <EmployeeDepartmentHistory>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
{
reader.Close();
}
commandWrapper = null;
}
return(rows);
}
public void AddInParameter(DbCommand cmd, object obj)
{
AddInParameter(cmd, obj, "@");
}
public ENSCTRCotizaciones ObtenerUno(string CodigoCotizacion)
{
DbCommand oCommand = null;
ENSCTRCotizaciones oENSCTRCotizaciones = new ENSCTRCotizaciones();
try
{
oCommand = GenericDataAccess.CreateCommand(dataProviderName, connectionString, "ws_Ctz_CotizacionLeerIAFAS");
GenericDataAccess.AgregarParametro(oCommand, "@NumeroCotizacion", CodigoCotizacion, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCliente", " ", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCorredor", " ", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaInicio", " ", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaFin", " ", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoUsuario", " ", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@Tipo", "C", TipoParametro.STR, Direccion.INPUT);
DbDataReader oDataReader = GenericDataAccess.ExecuteReader(oCommand);
while (oDataReader.Read())
{
oENSCTRCotizaciones.CodigoPerfil = oDataReader["CodigoPerfil"].ToString();
oENSCTRCotizaciones.CodigoCotizacion = oDataReader["CodigoCotizacion"].ToString();
oENSCTRCotizaciones.CodigoCliente = oDataReader["CodigoCliente"].ToString();
oENSCTRCotizaciones.CodigoCIIU = oDataReader["CodigoCIIU"].ToString();
oENSCTRCotizaciones.CodigoCorredor = oDataReader["CodigoCorredor"].ToString();
oENSCTRCotizaciones.FechaInicio = oDataReader["FechaInicio"].ToString();
oENSCTRCotizaciones.FechaFin = oDataReader["FechaFin"].ToString();
oENSCTRCotizaciones.TiempoCobertura = Convert.ToInt32(oDataReader["TiempoCobertura"]);
oENSCTRCotizaciones.CodigoMoneda = oDataReader["CodigoMoneda"].ToString();
oENSCTRCotizaciones.FechaCotizacion = DateTime.Parse(oDataReader["FechaCotizacion"].ToString());
oENSCTRCotizaciones.CodigoUsuarioRegistro = oDataReader["CodigoUsuarioRegistro"].ToString();
oENSCTRCotizaciones.CodigoEstado = oDataReader["CodigoEstado"].ToString();
oENSCTRCotizaciones.FechaHoraModificacion = DateTime.Parse(oDataReader["FechaHoraModificacion"].ToString());
oENSCTRCotizaciones.IdCotizacion = Convert.ToInt32(oDataReader["IdCotizacion"]);
oENSCTRCotizaciones.Origen = oDataReader["Origen"].ToString();
oENSCTRCotizaciones.PSPoliza = oDataReader["PSPoliza"].ToString();
oENSCTRCotizaciones.UbigeoRiesgo = oDataReader["UbigeoRiesgo"].ToString();
oENSCTRCotizaciones.GrupoCIIU = oDataReader["GrupoCIIU"].ToString();
oENSCTRCotizaciones.PrimaManual = oDataReader["PrimaManual"].ToString();
oENSCTRCotizaciones.CodigoEjecutivo = oDataReader["CodigoEjecutivo"].ToString();
oENSCTRCotizaciones.CodigoBrokerAsociado = oDataReader["CodigoBrokerAsociado"].ToString();
oENSCTRCotizaciones.NroFacturacion = oDataReader["NroFacturacion"].ToString();
oENSCTRCotizaciones.EstadoFacturacion = oDataReader["EstadoFacturacion"].ToString();
oENSCTRCotizaciones.CodigoRenovacion = oDataReader["CodigoRenovacion"].ToString();
oENSCTRCotizaciones.EstadoRegistaroTXT = oDataReader["EstadoRegistaroTXT"].ToString();
oENSCTRCotizaciones.CodigoSede = oDataReader["CodigoSede"].ToString();
oENSCTRCotizaciones.EmpresaRUC = oDataReader["EmpresaRUC"].ToString();
oENSCTRCotizaciones.EmpresaNombre = oDataReader["EmpresaNombre"].ToString();
oENSCTRCotizaciones.DescripcionCIIU = oDataReader["DescripcionCIIU"].ToString();
oENSCTRCotizaciones.DescripcionCorredor = oDataReader["DescripcionCorredor"].ToString();
oENSCTRCotizaciones.DescripcionEstado = oDataReader["DescripcionEstado"].ToString();
oENSCTRCotizaciones.CodigoContrato = oDataReader["CodigoContrato"].ToString();
oENSCTRCotizaciones.ProcesoResultado = oDataReader["ProcesoResultado"].ToString();
oENSCTRCotizaciones.ProcesoMensaje = oDataReader["ProcesoMensaje"].ToString();
}
return(oENSCTRCotizaciones);
}
catch (Exception ex)
{
throw new Exception();
}
finally
{
GenericDataAccess.CerrarConexion(oCommand, null);
}
}
/// <summary>
/// Factory method to create a new DataAdapter of the OleDbDataAdapter type.
/// </summary>
/// <param name="cmd">The select fommand for the data adapter.</param>
/// <returns>A populated DataAdapter of the OleDbDataAdapter type.</returns>
protected override DataAdapter CreateDataAdapter(DbCommand cmd)
{
return(new OleDbDataAdapter((OleDbCommand)cmd));
}
public ENSCTRCotizaciones Cotizar(ENSCTRCotizaciones oENSCTRCotizaciones)
{
DbCommand oCommand = null;
List <ENSCTRCotizaciones> oListaSCTRCotizaciones = new List <ENSCTRCotizaciones>();
List <ENSCTRCotizacionesDetalle> oListaSCTRCotizacionesDetalle = new List <ENSCTRCotizacionesDetalle>();
try
{
string sTipoProceso = "C";
double iPorcentajeTasa = 0;
double iPorcentajeCorredor = 0;
double iPorcentajeTasaAdm = 0;
double iPorcentajeCorredorAdm = 0;
double iPorcentajeTasaOpe = 0;
double iPorcentajeCorredorOpe = 0;
double iImportePrimaNeta = 0;
double iImporteDerechoEmision = 0;
double iImporteIGV = 0;
double iImportePrimaTotal = 0;
string sCodigoUsuario = "grojas";
string sCodigoCorredor = "J6969";
string sUbigeoRiesgo = "";
string sFechaInicio = "";
string sFechaFin = "";
string sFechaCotizacion = "";
string sCodigoMoneda = "";
string sProcesoResultado = "";
string sProcesoMensaje = "";
string sFlagExcepcion = "";
sFechaInicio = String.Format("{0:yyyyMMdd}", oENSCTRCotizaciones.dtm_FechaInicio);
sFechaFin = String.Format("{0:yyyyMMdd}", oENSCTRCotizaciones.dtm_FechaFin);
if (oENSCTRCotizaciones.UbigeoRiesgo is null)
{
sUbigeoRiesgo = oENSCTRCotizaciones.CodigoDptoR + oENSCTRCotizaciones.CodigoProvR + oENSCTRCotizaciones.CodigoDistR;
}
else
{
sUbigeoRiesgo = oENSCTRCotizaciones.UbigeoRiesgo;
}
if (oENSCTRCotizaciones.PorcentajeCorredor != 0)
{
iPorcentajeCorredor = oENSCTRCotizaciones.PorcentajeCorredor;
}
else
{
iPorcentajeCorredor = 100;
}
oCommand = GenericDataAccess.CreateCommand(dataProviderName, connectionString, "ws_Ctz_PerCotizacionConsulta");
//ENSCTRCotizaciones oEnListaSCTRCotizaciones = new ENSCTRCotizaciones();
GenericDataAccess.AgregarParametro(oCommand, "@TipoProceso", sTipoProceso, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@EmpresaRUC", oENSCTRCotizaciones.EmpresaRUC, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@EmpresaNombre", oENSCTRCotizaciones.EmpresaNombre, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCorredor", sCodigoCorredor, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCIIU", oENSCTRCotizaciones.CodigoCIIU, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@MontoOperativos", oENSCTRCotizaciones.DetMontoPlanillaOpe, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroOperativos", oENSCTRCotizaciones.DetNumeroTrabajadoresOpe, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@MontoAdministrativos", oENSCTRCotizaciones.DetMontoPlanillaAdm, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroAdministrativos", oENSCTRCotizaciones.DetNumeroTrabajadoresAdm, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoMoneda", oENSCTRCotizaciones.CodigoMoneda, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaInicio", sFechaInicio, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaFin", sFechaFin, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@TiempoCobertura", oENSCTRCotizaciones.TiempoCobertura, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@PorcentajeTasa", iPorcentajeTasa, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@PorcentajeCorredor", iPorcentajeCorredor, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroCotizacion", "", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@Usuario", sCodigoUsuario, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@UbigeoRiesgo", sUbigeoRiesgo, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@Origen", oENSCTRCotizaciones.Origen, TipoParametro.STR, Direccion.INPUT);
DbDataReader oDataReader = GenericDataAccess.ExecuteReader(oCommand);
ENSCTRCotizacionesDetalle oEnListaSCTRCotizaciones1 = new ENSCTRCotizacionesDetalle();
if (oDataReader.HasRows)
{
while (oDataReader.Read())
{
oEnListaSCTRCotizaciones1.CodigoTipoEmpleado = oDataReader["CodigoTipoAsegurado"].ToString();
oEnListaSCTRCotizaciones1.ImportePrimaNeta = Convert.ToDouble(oDataReader["ImportePrimaNeta"]);
oEnListaSCTRCotizaciones1.ImporteDerechoEmision = Convert.ToDouble(oDataReader["ImporteDerechoEmision"]);
oEnListaSCTRCotizaciones1.ImporteIGV = Convert.ToDouble(oDataReader["ImporteIGV"]);
oEnListaSCTRCotizaciones1.ImportePrimaTotal = Convert.ToDouble(oDataReader["ImportePrimaTotal"]);
oEnListaSCTRCotizaciones1.CodigoCotizacion = oDataReader["CodigoTipoAsegurado"].ToString();
sFechaCotizacion = oDataReader["FechaCotizacion"].ToString();
sCodigoMoneda = oDataReader["CodigoMoneda"].ToString();
oEnListaSCTRCotizaciones1.PorcentajeTasa = Convert.ToDouble(oDataReader["PorcentajeTasa"]);
oEnListaSCTRCotizaciones1.PorcentajeCorredor = Convert.ToDouble(oDataReader["PorcentajeCorredor"]);
sProcesoResultado = oDataReader["ProcesoResultado"].ToString();
sProcesoMensaje = oDataReader["ProcesoMensaje"].ToString();
sFlagExcepcion = oDataReader["FlagExcepcion"].ToString();
iImportePrimaNeta = iImportePrimaNeta + oEnListaSCTRCotizaciones1.ImportePrimaNeta;
iImporteDerechoEmision = iImporteDerechoEmision + oEnListaSCTRCotizaciones1.ImporteDerechoEmision;;
iImporteIGV = iImporteIGV + oEnListaSCTRCotizaciones1.ImporteIGV;
iImportePrimaTotal = iImportePrimaTotal + oEnListaSCTRCotizaciones1.ImportePrimaTotal;
if (oEnListaSCTRCotizaciones1.CodigoTipoEmpleado != "2")
{
iPorcentajeTasaAdm = oEnListaSCTRCotizaciones1.PorcentajeTasa;
iPorcentajeCorredorAdm = oEnListaSCTRCotizaciones1.PorcentajeCorredor;
}
else
{
iPorcentajeTasaOpe = oEnListaSCTRCotizaciones1.PorcentajeTasa;
iPorcentajeCorredorOpe = oEnListaSCTRCotizaciones1.PorcentajeCorredor;
}
oListaSCTRCotizacionesDetalle.Add(oEnListaSCTRCotizaciones1);
}
}
oENSCTRCotizaciones.ImportePrimaNeta = iImportePrimaNeta;
oENSCTRCotizaciones.ImporteIGV = iImporteIGV;
oENSCTRCotizaciones.ImportePrimaTotal = iImportePrimaTotal;
oENSCTRCotizaciones.ImporteDerechoEmision = iImporteDerechoEmision;
oENSCTRCotizaciones.PorcentajeCorredor = iPorcentajeCorredorAdm;
oENSCTRCotizaciones.PorcentajeTasa = iPorcentajeTasaAdm;
return(oENSCTRCotizaciones);
// return oListaSCTRCotizacionesDetalle;
}
catch (Exception ex)
{
throw new Excepciones.ManejoExcepciones(ex);
}
finally
{
GenericDataAccess.CerrarConexion(oCommand, null);
}
}
public ENSCTRCotizaciones ObtenerUnoConDetalle(string CodigoCotizacion)
{
DbCommand oCommand = null;
ENSCTRCotizaciones oENSCTRCotizaciones = new ENSCTRCotizaciones();
try
{
oCommand = GenericDataAccess.CreateCommand(dataProviderName, connectionString, "ws_Ctz_CotizacionLeerIAFAS_Detalle");
GenericDataAccess.AgregarParametro(oCommand, "@argCodigoCotizacion", CodigoCotizacion, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@argErrorCode", 1, TipoParametro.INT, Direccion.OUTPUT);
DbDataReader oDataReader = GenericDataAccess.ExecuteReader(oCommand);
while (oDataReader.Read())
{
oENSCTRCotizaciones.CodigoPerfil = oDataReader["CodigoPerfil"].ToString();
oENSCTRCotizaciones.CodigoCotizacion = oDataReader["CodigoCotizacion"].ToString();
oENSCTRCotizaciones.CodigoCliente = oDataReader["CodigoCliente"].ToString();
oENSCTRCotizaciones.CodigoCIIU = oDataReader["CodigoCIIU"].ToString();
oENSCTRCotizaciones.CodigoCorredor = oDataReader["CodigoCorredor"].ToString();
oENSCTRCotizaciones.FechaInicio = oDataReader["FechaInicio"].ToString();
oENSCTRCotizaciones.FechaFin = oDataReader["FechaFin"].ToString();
oENSCTRCotizaciones.TiempoCobertura = Convert.ToInt32(oDataReader["TiempoCobertura"]);
oENSCTRCotizaciones.CodigoMoneda = oDataReader["CodigoMoneda"].ToString();
oENSCTRCotizaciones.FechaCotizacion = DateTime.Parse(oDataReader["FechaCotizacion"].ToString());
oENSCTRCotizaciones.CodigoUsuarioRegistro = oDataReader["CodigoUsuarioRegistro"].ToString();
oENSCTRCotizaciones.CodigoEstado = oDataReader["CodigoEstado"].ToString();
oENSCTRCotizaciones.FechaHoraModificacion = DateTime.Parse(oDataReader["FechaHoraModificacion"].ToString());
oENSCTRCotizaciones.IdCotizacion = Convert.ToInt32(oDataReader["IdCotizacion"]);
oENSCTRCotizaciones.Origen = oDataReader["Origen"].ToString();
oENSCTRCotizaciones.PSPoliza = oDataReader["PSPoliza"].ToString();
oENSCTRCotizaciones.UbigeoRiesgo = oDataReader["UbigeoRiesgo"].ToString();
oENSCTRCotizaciones.GrupoCIIU = oDataReader["GrupoCIIU"].ToString();
oENSCTRCotizaciones.PrimaManual = oDataReader["PrimaManual"].ToString();
oENSCTRCotizaciones.CodigoEjecutivo = oDataReader["CodigoEjecutivo"].ToString();
oENSCTRCotizaciones.CodigoBrokerAsociado = oDataReader["CodigoBrokerAsociado"].ToString();
oENSCTRCotizaciones.NroFacturacion = oDataReader["NroFacturacion"].ToString();
oENSCTRCotizaciones.EstadoFacturacion = oDataReader["EstadoFacturacion"].ToString();
oENSCTRCotizaciones.CodigoRenovacion = oDataReader["CodigoRenovacion"].ToString();
oENSCTRCotizaciones.EstadoRegistaroTXT = oDataReader["EstadoRegistaroTXT"].ToString();
oENSCTRCotizaciones.CodigoSede = oDataReader["CodigoSede"].ToString();
oENSCTRCotizaciones.EmpresaRUC = oDataReader["EmpresaRUC"].ToString();
oENSCTRCotizaciones.EmpresaNombre = oDataReader["EmpresaNombre"].ToString();
oENSCTRCotizaciones.DescripcionCIIU = oDataReader["DescripcionCIIU"].ToString();
oENSCTRCotizaciones.DescripcionCorredor = oDataReader["DescripcionCorredor"].ToString();
oENSCTRCotizaciones.DescripcionEstado = oDataReader["DescripcionEstado"].ToString();
oENSCTRCotizaciones.CodigoContrato = oDataReader["CodigoContrato"].ToString();
oENSCTRCotizaciones.ProcesoResultado = oDataReader["ProcesoResultado"].ToString();
oENSCTRCotizaciones.ProcesoMensaje = oDataReader["ProcesoMensaje"].ToString();
//Detalle Operativos
oENSCTRCotizaciones.DetCodigoCotizacionOpe = oDataReader["DetCodigoCotizacionOpe"].ToString();
oENSCTRCotizaciones.DetCodigoTipoEmpleadoOpe = oDataReader["DetCodigoTipoEmpleadoOpe"].ToString();
oENSCTRCotizaciones.DetImporteDerechoEmisionOpe = Convert.ToDouble(oDataReader["DetImporteDerechoEmisionOpe"]);
//oENSCTRCotizaciones.DetImporteDerechoEmisionPensionOpe = Convert.ToDouble(oDataReader["DetImporteDerechoEmisionPensionOpe"]);
oENSCTRCotizaciones.DetImporteDerechoEmisionPensionOpe = oDataReader["DetImporteDerechoEmisionPensionOpe"] == DBNull.Value
? 0.00
: Convert.ToDouble(oDataReader["DetImporteDerechoEmisionPensionOpe"]);
oENSCTRCotizaciones.DetImporteIGVOpe = Convert.ToDouble(oDataReader["DetImporteIGVOpe"]);
//oENSCTRCotizaciones.DetImporteIGVPensionOpe = Convert.ToDouble(oDataReader["DetImporteIGVPensionOpe"]);
oENSCTRCotizaciones.DetImporteIGVPensionOpe = oDataReader["DetImporteIGVPensionOpe"] == DBNull.Value
? 0.00
: Convert.ToDouble(oDataReader["DetImporteIGVPensionOpe"]);
//oENSCTRCotizaciones.DetImportePrimaNetaOpe = Convert.ToDouble(oDataReader["DetImportePrimaNetaOpe"]);
oENSCTRCotizaciones.DetImportePrimaNetaOpe = oDataReader["DetImportePrimaNetaOpe"] == DBNull.Value
? 0.00
: Convert.ToDouble(oDataReader["DetImportePrimaNetaOpe"]);
//oENSCTRCotizaciones.DetImportePrimaNetaPensionOpe = Convert.ToDouble(oDataReader["DetImportePrimaNetaPensionOpe"]);
oENSCTRCotizaciones.DetImportePrimaNetaPensionOpe = oDataReader["DetImportePrimaNetaPensionOpe"] == DBNull.Value
? 0.00
: Convert.ToDouble(oDataReader["DetImportePrimaNetaPensionOpe"]);
oENSCTRCotizaciones.DetImportePrimaTotalOpe = Convert.ToDouble(oDataReader["DetImportePrimaTotalOpe"]);
//oENSCTRCotizaciones.DetImportePrimaTotalPensionOpe = Convert.ToDouble(oDataReader["DetImportePrimaTotalPensionOpe"]);
oENSCTRCotizaciones.DetItemOpe = oDataReader["DetItemOpe"].ToString();
oENSCTRCotizaciones.DetMontoPlanillaOpe = Convert.ToDouble(oDataReader["DetMontoPlanillaOpe"].ToString());
oENSCTRCotizaciones.DetNumeroTrabajadoresOpe = Convert.ToInt32(oDataReader["DetNumeroTrabajadoresOpe"]);
oENSCTRCotizaciones.DetPorcentajeCorredorOpe = Convert.ToDouble(oDataReader["DetPorcentajeCorredorOpe"]);
oENSCTRCotizaciones.DetPorcentajeTasaOpe = Convert.ToDouble(oDataReader["DetPorcentajeTasaOpe"]);
oENSCTRCotizaciones.DetPorcentajeTasaPensionOpe = Convert.ToDouble(oDataReader["DetPorcentajeTasaPensionOpe"]);
oENSCTRCotizaciones.DetPorcentajeCorredorPensionOpe = Convert.ToDouble(oDataReader["DetPorcentajeCorredorPensionOpe"]);
//Detalle Administrativos
oENSCTRCotizaciones.DetCodigoCotizacionAdm = oDataReader["DetCodigoCotizacionAdm"].ToString();
oENSCTRCotizaciones.DetCodigoTipoEmpleadoAdm = oDataReader["DetCodigoTipoEmpleadoAdm"].ToString();
oENSCTRCotizaciones.DetImporteDerechoEmisionAdm = Convert.ToDouble(oDataReader["DetImporteDerechoEmisionAdm"]);
oENSCTRCotizaciones.DetImporteDerechoEmisionPensionAdm = Convert.ToDouble(oDataReader["DetImporteDerechoEmisionPensionAdm"]);
oENSCTRCotizaciones.DetImporteIGVAdm = Convert.ToDouble(oDataReader["DetImporteIGVAdm"]);
oENSCTRCotizaciones.DetImporteIGVPensionAdm = Convert.ToDouble(oDataReader["DetImporteIGVPensionAdm"]);
oENSCTRCotizaciones.DetImportePrimaNetaAdm = Convert.ToDouble(oDataReader["DetImportePrimaNetaAdm"]);
oENSCTRCotizaciones.DetImportePrimaNetaPensionAdm = Convert.ToDouble(oDataReader["DetImportePrimaNetaPensionAdm"]);
oENSCTRCotizaciones.DetImportePrimaTotalAdm = Convert.ToDouble(oDataReader["DetImportePrimaTotalAdm"]);
oENSCTRCotizaciones.DetImportePrimaTotalPensionAdm = Convert.ToDouble(oDataReader["DetImportePrimaTotalPensionAdm"]);
oENSCTRCotizaciones.DetItemAdm = oDataReader["DetItemAdm"].ToString();
oENSCTRCotizaciones.DetMontoPlanillaAdm = Convert.ToDouble(oDataReader["DetMontoPlanillaAdm"].ToString());
oENSCTRCotizaciones.DetNumeroTrabajadoresAdm = Convert.ToInt32(oDataReader["DetNumeroTrabajadoresAdm"]);
oENSCTRCotizaciones.DetPorcentajeCorredorAdm = Convert.ToDouble(oDataReader["DetPorcentajeCorredorAdm"]);
oENSCTRCotizaciones.DetPorcentajeTasaAdm = Convert.ToDouble(oDataReader["DetPorcentajeTasaAdm"]);
oENSCTRCotizaciones.DetPorcentajeTasaPensionAdm = Convert.ToDouble(oDataReader["DetPorcentajeTasaPensionAdm"]);
oENSCTRCotizaciones.DetPorcentajeCorredorPensionAdm = Convert.ToDouble(oDataReader["DetPorcentajeCorredorPensionAdm"]);
//Nuevos
oENSCTRCotizaciones.Direccion = oDataReader["Direccion"].ToString();
oENSCTRCotizaciones.Ubigeo = oDataReader["Ubigeo"].ToString();
oENSCTRCotizaciones.UbigeoRiesgo = oDataReader["UbigeoRiesgo"].ToString();
oENSCTRCotizaciones.CodigoDpto = oDataReader["CodDpto"].ToString();
oENSCTRCotizaciones.CodigoProv = oDataReader["CodProv"].ToString();
oENSCTRCotizaciones.CodigoDist = oDataReader["CodDist"].ToString();
oENSCTRCotizaciones.CodigoDptoR = oDataReader["CodDptoR"].ToString();
oENSCTRCotizaciones.CodigoProvR = oDataReader["CodProvR"].ToString();
oENSCTRCotizaciones.CodigoDistR = oDataReader["CodDistR"].ToString();
oENSCTRCotizaciones.ImportePrimaNeta = Convert.ToDouble(oDataReader["ImportePrimaNeta"]);
oENSCTRCotizaciones.ImporteIGV = Convert.ToDouble(oDataReader["ImporteIGV"]);
oENSCTRCotizaciones.ImportePrimaTotal = Convert.ToDouble(oDataReader["ImportePrimaTotal"]);
oENSCTRCotizaciones.ImporteDerechoEmision = Convert.ToDouble(oDataReader["ImporteDerechoEmision"]);
oENSCTRCotizaciones.ImportePrimaNetaPension = Convert.ToDouble(oDataReader["ImportePrimaNetaPension"]);
oENSCTRCotizaciones.ImporteIGVPension = Convert.ToDouble(oDataReader["ImporteIGVPension"]);
oENSCTRCotizaciones.ImportePrimaTotalPension = Convert.ToDouble(oDataReader["ImportePrimaTotalPension"]);
oENSCTRCotizaciones.ImporteDerechoEmisionPension = Convert.ToDouble(oDataReader["ImporteDerechoEmisionPension"]);
oENSCTRCotizaciones.PorcentajeTasaPension = Convert.ToDouble(oDataReader["PorcentajeTasaPension"]);
oENSCTRCotizaciones.PorcentajeCorredorPension = Convert.ToDouble(oDataReader["PorcentajeCorredorPension"]);
oENSCTRCotizaciones.PorcentajeTasa = Convert.ToDouble(oDataReader["PorcentajeTasa"]);
oENSCTRCotizaciones.PorcentajeCorredor = Convert.ToDouble(oDataReader["PorcentajeCorredor"]);
oENSCTRCotizaciones.dtm_FechaInicio = DateTime.Parse(oDataReader["FechaInicio"].ToString());
oENSCTRCotizaciones.dtm_FechaFin = DateTime.Parse(oDataReader["FechaFin"].ToString());
}
return(oENSCTRCotizaciones);
}
catch (Exception ex)
{
throw new Exception();
}
finally
{
GenericDataAccess.CerrarConexion(oCommand, null);
}
}
public bool Actualizar(ENSCTRCotizaciones oENSCTRCotizaciones)
{
DbCommand oCommand = null;
try
{
string sTipoProceso = "U";
double iPorcentajeTasa = 0;
double iPorcentajeCorredor = 0;
string sCodigoUsuario = "grojas";
string sCodigoCorredor = "J6969";
string sUbigeoRiesgo = "";
string sFechaInicio = "";
string sFechaFin = "";
sFechaInicio = String.Format("{0:yyyyMMdd}", oENSCTRCotizaciones.dtm_FechaInicio);
sFechaFin = String.Format("{0:yyyyMMdd}", oENSCTRCotizaciones.dtm_FechaFin);
if (oENSCTRCotizaciones.UbigeoRiesgo is null)
{
sUbigeoRiesgo = oENSCTRCotizaciones.CodigoDptoR + oENSCTRCotizaciones.CodigoProvR + oENSCTRCotizaciones.CodigoDistR;
}
else
{
sUbigeoRiesgo = oENSCTRCotizaciones.UbigeoRiesgo;
}
if (oENSCTRCotizaciones.PorcentajeCorredor != 0)
{
iPorcentajeCorredor = oENSCTRCotizaciones.PorcentajeCorredor;
}
else
{
iPorcentajeCorredor = 100;
}
if (oENSCTRCotizaciones.PorcentajeTasa != 0)
{
iPorcentajeTasa = oENSCTRCotizaciones.PorcentajeTasa;
}
else
{
iPorcentajeTasa = 0;
}
oCommand = GenericDataAccess.CreateCommand(dataProviderName, connectionString, "ws_Ctz_PerCotizacionConsulta");
GenericDataAccess.AgregarParametro(oCommand, "@TipoProceso", sTipoProceso, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@EmpresaRUC", oENSCTRCotizaciones.EmpresaRUC, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@EmpresaNombre", oENSCTRCotizaciones.EmpresaNombre, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCorredor", sCodigoCorredor, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoCIIU", oENSCTRCotizaciones.CodigoCIIU, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@MontoOperativos", oENSCTRCotizaciones.DetMontoPlanillaOpe, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroOperativos", oENSCTRCotizaciones.DetNumeroTrabajadoresOpe, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@MontoAdministrativos", oENSCTRCotizaciones.DetMontoPlanillaAdm, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroAdministrativos", oENSCTRCotizaciones.DetNumeroTrabajadoresAdm, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@CodigoMoneda", oENSCTRCotizaciones.CodigoMoneda, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaInicio", sFechaInicio, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@FechaFin", sFechaFin, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@TiempoCobertura", oENSCTRCotizaciones.TiempoCobertura, TipoParametro.INT, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@PorcentajeTasa", iPorcentajeTasa, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@PorcentajeCorredor", iPorcentajeCorredor, TipoParametro.DBL, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@NumeroCotizacion", "", TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@Usuario", sCodigoUsuario, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@UbigeoRiesgo", sUbigeoRiesgo, TipoParametro.STR, Direccion.INPUT);
GenericDataAccess.AgregarParametro(oCommand, "@Origen", oENSCTRCotizaciones.Origen, TipoParametro.STR, Direccion.INPUT);
if (GenericDataAccess.ExecuteNonQuery(oCommand) > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw new Excepciones.ManejoExcepciones(ex);
}
finally
{
GenericDataAccess.CerrarConexion(oCommand, null);
}
}
public override int RegisterResultSetOutParameter(DbCommand statement, int position)
{
return(position);
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if(Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if(Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_LOG_WRH.LOG_WRH_Area_2_QuantityLevel.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_AssignmentID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command,"AssignmentID", ObjectID );
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection,Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "AssignmentID","LOG_WRH_Area_RefID","LOG_WRH_QuantityLevel_RefID","Creation_Timestamp","Tenant_RefID","IsDeleted" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter AssignmentID of type Guid
_AssignmentID = reader.GetGuid(0);
//1:Parameter LOG_WRH_Area_RefID of type Guid
_LOG_WRH_Area_RefID = reader.GetGuid(1);
//2:Parameter LOG_WRH_QuantityLevel_RefID of type Guid
_LOG_WRH_QuantityLevel_RefID = reader.GetGuid(2);
//3:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(3);
//4:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(4);
//5:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(5);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if(_AssignmentID != Guid.Empty){
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
} else {
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction!= null)
Transaction.Commit();
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
Connection.Close();
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
Transaction.Rollback();
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
Connection.Close();
}
catch { }
throw;
}
return retStatus;
}
public override DbDataReader GetResultSet(DbCommand statement)
{
return(statement.ExecuteReader());
}
public static bool InsertSalesInvoice(Int32 SoID, DateTime InvoiceDate, string InvoiceNo,
double taxAmount, double totalAmount,
string Catagory, string Product, Int32 InQuantity, double InPrice, int EnterBy)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_InvoiceDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = SoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvoiceNo";
param.Value = InvoiceNo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@taxamount";
param.Value = taxAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@totalAmount";
param.Value = totalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Catagory";
param.Value = Catagory;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Product";
param.Value = Product;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InQuantity";
param.Value = InQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InPrice";
param.Value = InPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
return(result != -1);
}
private void GetDbParameter(Type colType, DbCommand cmd, string colName, object colValue)
{
if (colName == "SellingTime")
{
if (string.IsNullOrEmpty(colValue.ToString()))
{
colValue = colValue.ToDateTime();
}
}
else if (colName == "Source")
{
colValue = 0;
}
if (colType == Type.GetType("System.Int16") || colType == Type.GetType("System.Byte"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Int16;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.Int32"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Int32;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.Int64"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Int64;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.Decimal") || colType == Type.GetType("System.Float"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Decimal;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.String") || colType == Type.GetType("System.Text"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.DateTime"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.DateTime;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.Date"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Date;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
else if (colType == Type.GetType("System.DBNull"))
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.Object;
param.Value = colValue;
param.ParameterName = colName;
cmd.Parameters.Add(param);
}
}
public static bool InsertSalesInvoicedetails(Int32 Invoiceid, Int32 PoID, Int32 Catagory, Int32 Product,
Int32 POQuantity, double POPrice, double POtax, double POtotalAmount,
Int32 InQuantity, double InPrice, string Intax, double INtotalAmount, Int32 EnterBy, string loaction, string LblProduct, DateTime InvoiceDate)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_InvoiceDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvocieID";
param.Value = Invoiceid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = PoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Catagory";
param.Value = Catagory;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Product";
param.Value = Product;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POQuantity";
param.Value = POQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POPrice";
param.Value = POPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POtax";
param.Value = POtax;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POtotalamoun";
param.Value = POtotalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InQuantity";
param.Value = InQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InPrice";
param.Value = InPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@INtax";
param.Value = Intax;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@INtotalamount";
param.Value = INtotalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@loaction";
param.Value = loaction;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@LblProduct";
param.Value = LblProduct;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
return(result != -1);
}
private static CacheDependency CreateOracleDepenedency(string connectionString, DbCommand dbCommand)
{
OracleCacheDependency depenedency = new OracleCacheDependency(connectionString, dbCommand.CommandText);
if (dbCommand.Parameters.Count > 0)
{
foreach (DbParameter parameter in dbCommand.Parameters)
{
OracleCmdParams dependencyParameter = new OracleCmdParams();
dependencyParameter.Direction = (OracleParameterDirection)((int)parameter.Direction - 1);
switch (parameter.Direction)
{
case ParameterDirection.Input:
dependencyParameter.Direction = OracleParameterDirection.Input;
break;
case ParameterDirection.Output:
dependencyParameter.Direction = OracleParameterDirection.Output;
break;
}
dependencyParameter.Value = parameter.Value;
depenedency.CommandParams.Add(parameter.ParameterName, dependencyParameter);
}
}
return depenedency;
}
public static bool InsertSalesInvoice(out int invoiceID, Int32 PoID, string PONo, DateTime PODate,
string InvoiceNo, DateTime InvoiceDate, double totalAmount, int EnterBy)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_Invoice";
// create a new parameter
DbParameter param = comm.CreateParameter();
param = comm.CreateParameter();
param.ParameterName = "@InvoiceID";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = PoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PONo";
param.Value = PONo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PODate";
param.Value = PODate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvoiceNo";
param.Value = InvoiceNo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@totalAmount";
param.Value = totalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
invoiceID = Int32.Parse(comm.Parameters["@InvoiceID"].Value.ToString());
// result will be 1 in case of success
return(result != -1);
}
public void AddOutParameter(DbCommand cmd, string column, DbType type, int size)
{
Db.AddOutParameter(cmd, column, type, size);
}
/// <summary>
/// Generates a command that can be executed to update a row -- and return the contents of
/// the updated row.
/// </summary>
/// <param name="connection">The connection the command should be associated with</param>
/// <returns>Command to update the row</returns>
public override DbCommand GetCommand(DbConnection connection)
{
Validate.IsNotNull(nameof(connection), connection);
// Process the cells and columns
List <string> declareColumns = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> setComponents = new List <string>();
List <string> outClauseColumns = new List <string>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
// Add the output columns regardless of whether the column is read only
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(metadata.DbColumn, useSemanticEquivalent: true)}");
outClauseColumns.Add($"inserted.{metadata.EscapedName}");
selectColumns.Add(metadata.EscapedName);
// If we have a new value for the column, proccess it now
CellUpdate cellUpdate;
if (cellUpdates.TryGetValue(i, out cellUpdate))
{
string paramName = $"@Value{RowId}_{i}";
setComponents.Add($"{metadata.EscapedName} = {paramName}");
inParameters.Add(new SqlParameter(paramName, AssociatedResultSet.Columns[i].SqlDbType)
{
Value = cellUpdate.Value
});
}
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Update{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the update statement
WhereClause whereClause = GetWhereClause(true);
string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized
? UpdateOutputMemOptimized
: UpdateOutput;
string updateStatement = string.Format(updateStatementFormat,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", setComponents),
string.Join(", ", outClauseColumns),
tempTableName,
whereClause.CommandText);
string validateScript = string.Format(CultureInfo.InvariantCulture, validateUpdateOnlyOneRow,
AssociatedObjectMetadata.EscapedMultipartName,
whereClause.CommandText);
// Step 3) Build the select statement
string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName);
// Step 4) Put it all together into a results object
StringBuilder query = new StringBuilder();
query.AppendLine(declareStatement);
query.AppendLine(validateScript);
query.AppendLine(updateStatement);
query.AppendLine(selectStatement);
query.Append("END");
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
command.Parameters.AddRange(whereClause.Parameters.ToArray());
return(command);
}
/// <summary>
/// DbCommand 에 parameter를 Binding 한다.
/// </summary>
/// <param name="cmd">DbCommand</param>
/// <param name="obj">Binding할 Class</param>
public void AddInParameter(DbCommand cmd, object obj, string header)
{
DbType dbType = DbType.String;
object value = null;
List<string> columns = new List<string>();
if (cmd.CommandType == CommandType.StoredProcedure) columns = GetProcedureColumn(cmd.Connection, cmd.CommandText);
foreach (PropertyInfo property in obj.GetType().GetProperties())
{
DatabaseAttribute[] attribute = (DatabaseAttribute[])property.GetCustomAttributes(typeof(DatabaseAttribute), true);
if (attribute.Length > 0)
{
// Schema 에서 얻어온 Parameter 값이 없을경우에는 맵핑시키지 않는다.
if (columns.Count > 0 && !columns.Contains(attribute[0].Column)) continue;
try
{
value = property.GetValue(obj, null);
// SqlDateTime 형식일 경우
if (property.PropertyType == typeof(SqlDateTime)) dbType = DbType.DateTime;
else if (property.PropertyType == typeof(DateTime)) dbType = DbType.DateTime;
//int형일경우
else if (property.PropertyType == typeof(int)) dbType = DbType.Int32;
//int? 형인경우
else if (property.PropertyType == typeof(int?)) dbType = DbType.Int32;
// decimal 형인경우
else if (property.PropertyType == typeof(decimal)) dbType = DbType.Decimal;
// single 형인경우
else if (property.PropertyType == typeof(Single)) dbType = DbType.Single;
// Bool 형식
else if (property.PropertyType == typeof(bool))
{
dbType = DbType.String;
value = ((bool)value) ? attribute[0].TrueString : attribute[0].FalseString;
}
// Enum 형식
else if (property.PropertyType.IsEnum) dbType = DbType.Int32;
// 기타 형식
else
{
dbType = DbType.String;
if (value == null || string.IsNullOrEmpty(value.ToString())) value = null;
}
attribute[0].IsAssign = true;
if (attribute[0].IsDbTypeSpecified)
{
dbType = attribute[0].DbType;
}
if (attribute[0].ParameterType == ParameterTypeEnum.In)
{
Db.AddInParameter(cmd, header + attribute[0].Column, dbType, value);
//// Domain Dictionary 에서 해당 Column Domain값이 존재할경우
//// 해당 Column Domain 값으로 Length를 설정해준다.
//if (Biz_Domain.GetDomainSize(attribute[0].Column) > 0)
//{
// cmd.Parameters[header + attribute[0].Column].Size = Biz_Domain.GetDomainSize(attribute[0].Column);
//}
}
else if (attribute[0].ParameterType == ParameterTypeEnum.Out)
{
Db.AddOutParameter(cmd, header + attribute[0].Column, dbType, (dbType.ToString().ToLower().IndexOf("int") > -1) ? 0 : attribute[0].ParameterSize);
}
else if (attribute[0].ParameterType == ParameterTypeEnum.InOut)
{
Db.AddParameter(cmd, header + attribute[0].Column, dbType, ((dbType.ToString().ToLower().IndexOf("int") > -1) ? 0 : attribute[0].ParameterSize)
, ParameterDirection.InputOutput, true, 0, 0, null, DataRowVersion.Default, value);
}
}
catch (Exception ex)
{
if (attribute[0].IsCatch) throw ex;
}
}
}
}
public static int ExecuteNonQuery(this DbCommand dBCommand, string sqlStatement)
{
dBCommand.CommandText = sqlStatement;
return(dBCommand.ExecuteNonQuery());
}
public static void TranferVisitedSystemstoJournalTable() // DONE purposely without using any VisitedSystem code.. so we can blow it away later.
{
List <Object[]> ehl = new List <Object[]>();
Dictionary <string, Dictionary <string, double> > dists = new Dictionary <string, Dictionary <string, double> >(StringComparer.CurrentCultureIgnoreCase);
List <EDDiscovery.DB.TravelLogUnit> tlus = EDDiscovery.DB.TravelLogUnit.GetAll().Where(t => t.type == 1).ToList();
using (SQLiteConnectionOld conn = new SQLiteConnectionOld())
{
// 0 1 2
using (DbCommand cmd = conn.CreateCommand("SELECT NameA, NameB, Dist FROM Distances WHERE Status >= 1")) // any distance pairs okay
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object[] vals = new object[3];
reader.GetValues(vals);
string namea = (string)vals[0];
string nameb = (string)vals[1];
double dist = (double)vals[2];
if (!dists.ContainsKey(namea))
{
dists[namea] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase);
}
dists[namea][nameb] = dist;
if (!dists.ContainsKey(nameb))
{
dists[nameb] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase);
}
dists[nameb][namea] = dist;
}
}
}
int olddbver = SQLiteConnectionOld.GetSettingInt("DBVer", 1);
if (olddbver < 7) // 2.5.2
{
System.Diagnostics.Trace.WriteLine("Database too old - unable to migrate travel log");
return;
}
string query;
if (olddbver < 8) // 2.5.6
{
query = "Select Name,Time,Unit,Commander,edsm_sync, -65536 AS Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time";
}
else if (olddbver < 14) // 3.2.1
{
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time";
}
else if (olddbver < 18) // 4.0.2
{
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z, NULL AS id_edsm_assigned From VisitedSystems Order By Time";
}
else
{
// 0 1 2 3 4 5 6 7 8 9
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z,id_edsm_assigned From VisitedSystems Order By Time";
}
using (DbCommand cmd = conn.CreateCommand(query))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
string prev = "";
while (reader.Read())
{
Object[] array = new Object[17];
reader.GetValues(array); // love this call.
string tluname = (string)array[2]; // 2 is in terms of its name.. look it up
if (tluname.StartsWith("EDSM-")) // Don't migrate the entries that were synced from EDSM
{ // We can sync them from EDSM later.
continue;
}
EDDiscovery.DB.TravelLogUnit tlu = tlus.Find(x => x.Name.Equals(tluname, StringComparison.InvariantCultureIgnoreCase));
array[15] = (tlu != null) ? (long)tlu.id : 0; // even if we don't find it, tlu may be screwed up, still want to import
array[16] = null;
if (prev.Length > 0 && dists.ContainsKey((string)array[0]))
{
Dictionary <string, double> _dists = dists[(string)array[0]];
if (_dists.ContainsKey(prev))
{
array[16] = _dists[prev];
}
}
ehl.Add(array);
prev = (string)array[0];
}
}
}
}
using (SQLiteConnectionUser conn = new SQLiteConnectionUser(utc: true))
{
using (DbTransaction txn = conn.BeginTransaction())
{
foreach (Object[] array in ehl)
{
using (DbCommand cmd = conn.CreateCommand(
"Insert into JournalEntries (TravelLogId,CommanderId,EventTypeId,EventType,EventTime,EventData,EdsmId,Synced) " +
"values (@tli,@cid,@eti,@et,@etime,@edata,@edsmid,@synced)", txn))
{
cmd.AddParameterWithValue("@tli", (long)array[15]);
cmd.AddParameterWithValue("@cid", (long)array[3]);
cmd.AddParameterWithValue("@eti", EDDiscovery.EliteDangerous.JournalTypeEnum.FSDJump);
cmd.AddParameterWithValue("@et", "FSDJump");
JObject je = new JObject();
DateTime eventtime = DateTime.SpecifyKind((DateTime)array[1], DateTimeKind.Local).ToUniversalTime();
je["timestamp"] = eventtime.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'");
je["event"] = "FSDJump";
je["StarSystem"] = ((string)array[0]);
if (System.DBNull.Value != array[6] && System.DBNull.Value != array[7] && System.DBNull.Value != array[8])
{
je["StarPos"] = new JArray()
{
array[6], array[7], array[8]
};
}
if (array[16] != null)
{
je["JumpDist"] = (double)array[16];
}
je["EDDMapColor"] = ((long)array[5]);
cmd.AddParameterWithValue("@etime", eventtime);
cmd.AddParameterWithValue("@edata", je.ToString()); // order number - look at the dbcommand above
long edsmid = 0;
if (System.DBNull.Value != array[9])
{
edsmid = (long)array[9];
}
cmd.AddParameterWithValue("@edsmid", edsmid); // order number - look at the dbcommand above
cmd.AddParameterWithValue("@synced", ((bool)array[4] == true) ? 1 : 0);
SQLiteDBClass.SQLNonQueryText(conn, cmd);
}
}
txn.Commit();
}
}
}
/// <summary>
/// Returns rows from the DataSource that meet the parameter conditions.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.EmployeeDepartmentHistory objects.</returns>
public override TList <EmployeeDepartmentHistory> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count)
{
SqlFilterParameterCollection filter = null;
if (parameters == null)
{
filter = new SqlFilterParameterCollection();
}
else
{
filter = parameters.GetParameters();
}
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "HumanResources.usp_adwTiers_EmployeeDepartmentHistory_Find_Dynamic", typeof(EmployeeDepartmentHistoryColumn), filter, orderBy, start, pageLength);
SqlFilterParameter param;
for (int i = 0; i < filter.Count; i++)
{
param = filter[i];
database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue());
}
TList <EmployeeDepartmentHistory> rows = new TList <EmployeeDepartmentHistory>();
IDataReader reader = null;
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
{
reader.Close();
}
commandWrapper = null;
}
return(rows);
}
} //end getall
#endregion
#region GetPaged Methods
/// <summary>
/// Gets a page of rows from the DataSource.
/// </summary>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">Number of rows in the DataSource.</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <remarks></remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.EmployeeDepartmentHistory objects.</returns>
public override TList <EmployeeDepartmentHistory> GetPaged(TransactionManager transactionManager, string whereClause, string orderBy, int start, int pageLength, out int count)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "HumanResources.usp_adwTiers_EmployeeDepartmentHistory_GetPaged", _useStoredProcedure);
if (commandWrapper.CommandType == CommandType.Text &&
commandWrapper.CommandText != null)
{
commandWrapper.CommandText = commandWrapper.CommandText.Replace(SqlUtil.PAGE_INDEX, string.Concat(SqlUtil.PAGE_INDEX, Guid.NewGuid().ToString("N").Substring(0, 8)));
}
database.AddInParameter(commandWrapper, "@WhereClause", DbType.String, whereClause);
database.AddInParameter(commandWrapper, "@OrderBy", DbType.String, orderBy);
database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32, start);
database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32, pageLength);
IDataReader reader = null;
//Create Collection
TList <EmployeeDepartmentHistory> rows = new TList <EmployeeDepartmentHistory>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "GetPaged", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, 0, int.MaxValue);
count = rows.Count;
if (reader.NextResult())
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "GetPaged", rows));
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
commandWrapper = null;
}
return(rows);
}
protected FR_Base Save(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
bool saveDictionary = false;
bool saveORMClass = !Status_IsAlreadySaved || Status_IsDirty;
//If Status Is Dirty (Meaning the data has been changed) or Status_IsAlreadySaved (Meaning the data is in the database, when loaded) just return
if (saveORMClass == false && saveDictionary == false)
{
return FR_Base.Status_OK;
}
#region Verify/Create Connections
//Create Connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//Create Transaction if null
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
#region Dictionary Management
//Save dictionary management
if (saveDictionary == true)
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection,Transaction);
//Save the dictionary or update based on if it has already been saved to the database
if (Status_IsAlreadySaved)
{
loader.Update();
}
else
{
loader.Save();
}
}
#endregion
#region Command Execution
if (saveORMClass == true) {
//Retrieve Querry
string Query = "";
if (Status_IsAlreadySaved == true)
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_LOG_WRH.LOG_WRH_Area_2_QuantityLevel.SQL.Update.sql")).ReadToEnd();
}
else
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_LOG_WRH.LOG_WRH_Area_2_QuantityLevel.SQL.Insert.sql")).ReadToEnd();
}
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = Query;
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "AssignmentID", _AssignmentID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "LOG_WRH_Area_RefID", _LOG_WRH_Area_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "LOG_WRH_QuantityLevel_RefID", _LOG_WRH_QuantityLevel_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Creation_Timestamp", _Creation_Timestamp);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tenant_RefID", _Tenant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsDeleted", _IsDeleted);
try
{
var dbChangeCount = command.ExecuteNonQuery();
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction!= null)
Transaction.Commit();
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
Connection.Close();
}
#endregion
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
Transaction.Rollback();
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
Connection.Close();
}
catch { }
throw;
}
return retStatus;
}
