private void CreateInsertATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string[] cols = dBOutput.Columns.Split(',');
string sql = string.Empty;
if (cols.Contains(dBOutput.KeyColumn))
{
sql = string.Format("Insert into {0}({1},{3}) values({2},-1)", dBOutput.TableName, dBOutput.Columns, string.Join(",", cols.Select(col => ":" + col)), dirtyField);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
else
{
sql = string.Format("Insert into {0}({1},{3},{4}) values({2},-1,:{4})", dBOutput.TableName, dBOutput.Columns, string.Join(",", cols.Select(col => ":" + col)), dirtyField, dBOutput.KeyColumn);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length + 1];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
oracleParameters[cols.Length] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
}
private void DeleteATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string sql = string.Format("Delete {0} where {1} =:{1}", dBOutput.TableName, dBOutput.KeyColumn);
string connStr = GetconStrByDirection(dataSyncDirection);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[1];
oracleParameters[0] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
/// <summary>
/// FES için bankanın kullandığı F_ENCRYPTED_DATA fonksiyonunu çağıran encryption metodu.
/// </summary>
/// <param name="verificationCode"></param>
/// <param name="phoneNumber"></param>
/// <returns></returns>
private string encryptVerificationCode(string verificationCode, string phoneNumber)
{
string result = null;
using (PeakDbContext dbContext = new PeakDbContext()) {
string sql = "select F_ENCRYPTED_DATA(:verificationCode,:phoneNumber) from dual";
Oracle.ManagedDataAccess.Client.OracleParameter pVerificationCode = new Oracle.ManagedDataAccess.Client.OracleParameter("verificationCode", verificationCode);
Oracle.ManagedDataAccess.Client.OracleParameter pPhoneNumber = new Oracle.ManagedDataAccess.Client.OracleParameter("phoneNumber", phoneNumber);
result = dbContext.Database.SqlQuery <string>(sql, pVerificationCode, pPhoneNumber).FirstOrDefault();
}
return(result);
}
public static IDbDataParameter CreateParameter(this DataType dataType)
{
var name = dataType.Name;
switch (dataType.DbType.DatabaseServer)
{
case DatabaseServer.SqlServer:
{
var p = new System.Data.SqlClient.SqlParameter(name, dataType.DbType.SqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
p.Value = GetParameterValue(dataType, dataType.DbType.SqlServerDbType.Value);
return(p);
}
case DatabaseServer.Oracle:
{
var p = new Oracle.ManagedDataAccess.Client.OracleParameter(name, dataType.DbType.OracleServerDbType.Value);
p.Direction = ParameterDirection.Input;
//p.Value = GetParameterValue(this.OracleServerDbType.Value);
return(p);
}
case DatabaseServer.MySql:
{
var p = new MySql.Data.MySqlClient.MySqlParameter(name, dataType.DbType.MySqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
p.Value = GetParameterValue(dataType, dataType.DbType.MySqlServerDbType.Value);
return(p);
}
case DatabaseServer.PostgreSql:
{
var p = new Npgsql.NpgsqlParameter(name, dataType.DbType.PostgreSqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
//p.Value = GetParameterValue(this.MySqlDbType.Value);
return(p);
}
default: throw new InvalidOperationException();
}
}
private void CreateUpdateATableRow(DataSyncDirection dataSyncDirection, DataTable dataTable, int rowNum, DBOutput dBOutput, string dirtyField)
{
string[] cols = dBOutput.Columns.Split(',');
if (cols.Any(c => c.Equals(dBOutput.KeyColumn)))
{
cols.Remove(c => c.Equals(dBOutput.KeyColumn));
}
string sql = string.Format("Update {0} Set {1},{3} Where {2} =:{2}", dBOutput.TableName, string.Join(",", cols.Select(col => " " + col + "=:" + col + " ")),
dBOutput.KeyColumn, dirtyField + "=:" + dirtyField);
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameters = new Oracle.ManagedDataAccess.Client.OracleParameter[cols.Length + 2];
for (int i = 0; i < cols.Length; i++)
{
oracleParameters[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(cols[i], dataTable.Rows[rowNum][cols[i]]);
}
oracleParameters[cols.Length] = new Oracle.ManagedDataAccess.Client.OracleParameter(dirtyField, (object)-1);
oracleParameters[cols.Length + 1] = new Oracle.ManagedDataAccess.Client.OracleParameter(dBOutput.KeyColumn, dataTable.Rows[rowNum][dBOutput.KeyColumn]);
string connStr = GetconStrByDirection(dataSyncDirection);
OracleHelper.ExecuteNonQuery(connStr, sql, oracleParameters);
}
/// <summary>
/// Creates ORA specific parameter
/// </summary>
protected override IDataParameter MakeAndAssignParameter(ValueExpression value)
{
var p = new Oracle.ManagedDataAccess.Client.OracleParameter($"{ParameterNamePrefix}{m_ParamCount++}", value.Value);
return(p);
}
/// <summary>
/// Handle any provider-specific issues with BLOBs here by "washing" the IDataParameter and returning a new one that is set up appropriately for the provider.
/// See MS KnowledgeBase article: http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
/// </summary>
/// <param name="connection">The IDbConnection to use in cleansing the parameter</param>
/// <param name="p">The parameter before cleansing</param>
/// <returns>The parameter after it's been cleansed.</returns>
protected override IDataParameter GetBlobParameter(IDbConnection connection, IDataParameter p)
{
OracleConnection clonedConnection = (OracleConnection)(((ICloneable)connection).Clone());
clonedConnection.Open();
OracleCommand cmd = clonedConnection.CreateCommand();
cmd.CommandText = "declare xx blob;begin dbms_lob.createtemporary(xx, false, 0);:tempblob := xx;end;";
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob tempLob;
tempLob = (OracleLob)(cmd.Parameters[0].Value);
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write((byte[])(p.Value), 0, System.Runtime.InteropServices.Marshal.SizeOf(p.Value));
tempLob.EndBatch();
OracleParameter op = new OracleParameter(p.ParameterName, OracleType.Blob);
op.Value = tempLob;
clonedConnection.Close();
return op;
}
/// <summary>
/// Get an IDataParameter for use in a SQL command
/// </summary>
/// <param name="parameterName">The name of the parameter to create</param>
/// <param name="value">The value of the specified parameter</param>
/// <returns>An IDataParameter object</returns>
public override IDataParameter GetParameter(string parameterName, object value)
{
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
parameter.Size = GetParameterSize(parameterName);
return parameter;
}
/// <summary>
/// Returns an OracleParameter object
/// </summary>
/// <returns>The OracleParameter object</returns>
public override IDataParameter GetParameter()
{
OracleParameter parameter = new OracleParameter();
parameter.Size = 255;
return parameter;
}
internal OracleParameter(RealOracleParameter parameter) => RealParameter = parameter ?? throw new ArgumentNullException(nameof(parameter));
