public override void BuildDataParams()
{
this.DataParams = new PersistentLib.SqlParamDictionary();
System.Data.IDbDataParameter p_Id = this.SqlFactory.CreateParam("@Id", System.Data.SqlDbType.UniqueIdentifier);
p_Id.SourceColumn = "Id";
this.DataParams.Add("@Id", p_Id);
System.Data.IDbDataParameter p_ContextID = this.SqlFactory.CreateParam("@ContextID", System.Data.SqlDbType.VarChar);
p_ContextID.SourceColumn = "ContextID";
this.DataParams.Add("@ContextID", p_ContextID);
System.Data.IDbDataParameter p_EndpointID = this.SqlFactory.CreateParam("@EndpointID", System.Data.SqlDbType.VarChar);
p_EndpointID.SourceColumn = "EndpointID";
this.DataParams.Add("@EndpointID", p_EndpointID);
System.Data.IDbDataParameter p_KeyGeneration = this.SqlFactory.CreateParam("@KeyGeneration", System.Data.SqlDbType.VarChar);
p_KeyGeneration.SourceColumn = "KeyGeneration";
this.DataParams.Add("@KeyGeneration", p_KeyGeneration);
System.Data.IDbDataParameter p_ExpiryTime = this.SqlFactory.CreateParam("@ExpiryTime", System.Data.SqlDbType.DateTime);
p_ExpiryTime.SourceColumn = "ExpiryTime";
this.DataParams.Add("@ExpiryTime", p_ExpiryTime);
System.Data.IDbDataParameter p_RollingExpiryTime = this.SqlFactory.CreateParam("@RollingExpiryTime", System.Data.SqlDbType.DateTime);
p_RollingExpiryTime.SourceColumn = "RollingExpiryTime";
this.DataParams.Add("@RollingExpiryTime", p_RollingExpiryTime);
System.Data.IDbDataParameter p_SessionSecurityTokenValue = this.SqlFactory.CreateParam("@SessionSecurityTokenValue", System.Data.SqlDbType.VarBinary);
p_SessionSecurityTokenValue.SourceColumn = "SessionSecurityTokenValue";
this.DataParams.Add("@SessionSecurityTokenValue", p_SessionSecurityTokenValue);
System.Data.IDbDataParameter p_SessionSecurityTokenID = this.SqlFactory.CreateParam("@SessionSecurityTokenID", System.Data.SqlDbType.VarChar);
p_SessionSecurityTokenID.SourceColumn = "SessionSecurityTokenID";
this.DataParams.Add("@SessionSecurityTokenID", p_SessionSecurityTokenID);
System.Data.IDbDataParameter p_UserName = this.SqlFactory.CreateParam("@UserName", System.Data.SqlDbType.VarChar);
p_UserName.SourceColumn = "UserName";
this.DataParams.Add("@UserName", p_UserName);
}
public override void BuildDataParams()
{
base.BuildDataParams();
System.Data.IDbDataParameter p_CurrentTime = this.SqlFactory.CreateParam("@CurrentTime", System.Data.SqlDbType.DateTime);
p_CurrentTime.SourceColumn = "CurrentTime";
this.DataParams.Add("@CurrentTime", p_CurrentTime);
}
public virtual System.Collections.Generic.List <System.Data.IDbDataParameter> BuildParams__TokenKey()
{
System.Collections.Generic.List <System.Data.IDbDataParameter> paramlist = new System.Collections.Generic.List <System.Data.IDbDataParameter>();
System.Data.IDbDataParameter p_TokenKey = SqlFactory.CreateParam("@TokenKey", System.Data.SqlDbType.VarChar);
SetParameterValue(p_TokenKey, _TokenKey);
paramlist.Add(p_TokenKey);
return(paramlist);
}
public static System.Data.IDbDataParameter CreateParameterObject(string paramName, System.Data.ParameterDirection paramDirection,
object paramValue, DbEngine dbEngine = DbEngine.MSSQLServer, DbConnectivity connectivity = DbConnectivity.AdoDotNet)
{
System.Data.IDbDataParameter param = CreateParameterObject(dbEngine, connectivity);
param.ParameterName = paramName;
param.Direction = paramDirection;
param.Value = paramValue;
return(param);
}
public virtual System.Collections.Generic.List <System.Data.IDbDataParameter> BuildParams__EndpointID_ContextID()
{
System.Collections.Generic.List <System.Data.IDbDataParameter> paramlist = new System.Collections.Generic.List <System.Data.IDbDataParameter>();
System.Data.IDbDataParameter p_EndpointID = SqlFactory.CreateParam("@EndpointID", System.Data.SqlDbType.VarChar);
SetParameterValue(p_EndpointID, _EndpointID);
paramlist.Add(p_EndpointID);
System.Data.IDbDataParameter p_ContextID = SqlFactory.CreateParam("@ContextID", System.Data.SqlDbType.VarChar);
SetParameterValue(p_ContextID, _ContextID);
paramlist.Add(p_ContextID);
return(paramlist);
}
protected virtual void GetEntityDeleteParameter(System.Data.IDbCommand CM, BaseEntitie ent)
{
ColumnCollection _column = ent.Column;
for (int i = 0; i < _column.Count; i++)
{
System.Data.IDbDataParameter sParameter = CM.CreateParameter();
sParameter.ParameterName = _column[i].FieldName;
sParameter.Value = _column[i].FieldValue;
sParameter.DbType = _column[i].FieldType;
CM.Parameters.Add(sParameter);
}
}
public System.Data.IDbDataParameter[] GetParameters(IDbHelper dbHelper)
{
if (Parameters.Count == 0)
{
return(null);
}
System.Data.IDbDataParameter[] result = new System.Data.IDbDataParameter[Parameters.Count];
for (int i = 0; i <= Parameters.Count - 1; i++)
{
result[i] = Parameters[i].GetIDbDataParameter(dbHelper);
}
return(result);
}
internal System.Data.IDbDataParameter CreateParameter(string parameterName, object value, System.Data.DbType type)
{
System.Data.IDbDataParameter param = _providerFactory.CreateParameter();
param.ParameterName = parameterName;
param.Value = value;
param.DbType = type;
if (param.DbType == System.Data.DbType.DateTime)
{
if (param is System.Data.OleDb.OleDbParameter odp)
{
odp.OleDbType = System.Data.OleDb.OleDbType.Date;
}
}
return(param);
}
/// <summary>
/// NHibernate는 BinaryBlobSqlType을 기본적으로 varbinary(8000)으로 변경한다. 이를 Image 형식으로 변경히기 위해 재정의하였다.
/// </summary>
/// <param name="dbParam"></param>
/// <param name="name"></param>
/// <param name="sqlType"></param>
protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name,
NHibernate.SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
if (sqlType is NHibernate.SqlTypes.BinarySqlType)
{
if (IsDebugEnabled)
{
log.Debug("BinaryBlobSqlType이므로 SqlDbType.Image 로 변경합니다.");
}
var parameter = (SqlParameter)dbParam;
parameter.SqlDbType = SqlDbType.Image;
}
}
public override void BuildDataParams()
{
this.DataParams = new PersistentLib.SqlParamDictionary();
System.Data.IDbDataParameter p_Id = this.SqlFactory.CreateParam("@Id", System.Data.SqlDbType.UniqueIdentifier);
p_Id.SourceColumn = "Id";
this.DataParams.Add("@Id", p_Id);
System.Data.IDbDataParameter p_TokenKey = this.SqlFactory.CreateParam("@TokenKey", System.Data.SqlDbType.VarChar);
p_TokenKey.SourceColumn = "TokenKey";
this.DataParams.Add("@TokenKey", p_TokenKey);
System.Data.IDbDataParameter p_ExpirationTime = this.SqlFactory.CreateParam("@ExpirationTime", System.Data.SqlDbType.DateTime);
p_ExpirationTime.SourceColumn = "ExpirationTime";
this.DataParams.Add("@ExpirationTime", p_ExpirationTime);
System.Data.IDbDataParameter p_SecurityToken = this.SqlFactory.CreateParam("@SecurityToken", System.Data.SqlDbType.VarBinary);
p_SecurityToken.SourceColumn = "SecurityToken";
this.DataParams.Add("@SecurityToken", p_SecurityToken);
}
private System.Data.IDbCommand GetDbCommand(ISqlMapper sqlMapper, string statementName, StrObjectDict paramObject, StrObjectDict dictParam, System.Collections.Generic.IDictionary <string, System.Data.ParameterDirection> dictParmDirection, System.Data.CommandType cmdType)
{
System.Data.IDbCommand result;
if (cmdType == System.Data.CommandType.Text)
{
result = this.GetDbCommand(sqlMapper, statementName, paramObject);
}
else
{
IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;
IMappedStatement mappedStatement = sqlMapper.GetMappedStatement(statementName);
ISqlMapSession sqlMapSession = new SqlMapSession(sqlMapper);
if (sqlMapper.LocalSession != null)
{
sqlMapSession = sqlMapper.LocalSession;
}
else
{
sqlMapSession = sqlMapper.OpenConnection();
}
RequestScope requestScope = statement.Sql.GetRequestScope(mappedStatement, paramObject, sqlMapSession);
mappedStatement.PreparedCommand.Create(requestScope, sqlMapSession, statement, paramObject);
System.Data.IDbCommand dbCommand = sqlMapSession.CreateCommand(cmdType);
dbCommand.CommandText = requestScope.IDbCommand.CommandText;
if (cmdType != System.Data.CommandType.StoredProcedure || dictParam == null)
{
result = dbCommand;
}
else
{
foreach (System.Collections.Generic.KeyValuePair <string, object> current in dictParam)
{
string text = current.Key.ToString();
System.Data.IDbDataParameter dbDataParameter = dbCommand.CreateParameter();
dbDataParameter.ParameterName = text;
dbDataParameter.Value = current.Value;
if (dictParmDirection != null && dictParmDirection.ContainsKey(text))
{
dbDataParameter.Direction = dictParmDirection[text];
}
dbCommand.Parameters.Add(dbDataParameter);
}
result = dbCommand;
}
}
return(result);
}
/// <summary>
/// Execute a Stored Proc with the passed in parameters.
/// </summary>
/// <param name="connString"></param>
/// <param name="sprocName"></param>
/// <param name="parameters"></param>
public virtual void ExecuteNonQuery(string connString, string sprocName, Dictionary <string, object> parameters)
{
IDbCommand command = _connection.CreateCommand();
command.CommandText = sprocName;
command.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null && parameters.Count > 0)
{
foreach (KeyValuePair <string, object> paramKvp in parameters)
{
System.Data.IDbDataParameter sprocParam = command.CreateParameter();
sprocParam.ParameterName = paramKvp.Key;
sprocParam.Value = (paramKvp.Value == null) ? DBNull.Value : paramKvp.Value;
command.Parameters.Add(sprocParam);
}
}
command.ExecuteNonQuery();
}
/// <summary>
/// Ejecuta un procedimiento almacenado en la base de datos,utilizando los parametros.
/// </summary>
/// <param name="ProcedimientoAlmacenado">Procedimiento Almacenado</param>
/// <param name="Parametros">Parametros</param>
/// <returns>Nro de instrucciones realizadas</returns>
public int Ejecutar(string ProcedimientoAlmacenado, params System.Object[] Parametros)
{
System.Data.IDbCommand Com = Comando(ProcedimientoAlmacenado);
CargarParametros(Com, Parametros);
int Resp = Com.ExecuteNonQuery();
for (int i = 0; i < Com.Parameters.Count; i++)
{
System.Data.IDbDataParameter Par =
(System.Data.IDbDataParameter)Com.Parameters[i];
if (Par.Direction == System.Data.ParameterDirection.InputOutput ||
Par.Direction == System.Data.ParameterDirection.Output)
{
Parametros.SetValue(Par.Value, i);
}
}
return(Resp);
}
public void InsertTest()
{
MyProcInstData mpdata = new MyProcInstData()
{
ID = 11,
Name = "niasdja",
ProcInstID = 222,
Value = "value"
};
DbMap map = new DbMapMySql("");
System.Data.IDbDataParameter[] arrr = new System.Data.IDbDataParameter[] { };
string sql = map.GeneralUpdateRef<MyProcInstData>(mpdata, new List<string>() { "Name", "Birthday" }, ref arrr);
Console.WriteLine(sql);
foreach (IDbDataParameter p in arrr)
{
Console.WriteLine(p.ParameterName + "," + p.Value + "," + p.Value.GetType());
}
}
public bool MapCommandFromBusinessObject <T>(T sourceData, ref IDbCommand command, string cmdPrefix, bool ignoreConflict)
{
bool status = false;
if (ValidateObject(command))
{
try
{
foreach (PropertyInfo property in sourceData.GetType().GetProperties())
{
object bindValue = property.GetValue(sourceData, null);
if (property != null)
{
if (property.PropertyType == typeof(string))
{
string tempString = Convert.ToString(bindValue);
if (string.IsNullOrEmpty(tempString))
{
bindValue = null;
}
}
}
System.Data.IDbDataParameter param =
ObjectFactory.CreateParameterObject(this.SystemCoreManager.DbEngine, this.SystemCoreManager.DbConnctivity);
param.ParameterName = cmdPrefix + property.Name;
param.Value = bindValue;
command.Parameters.Add(param);
status = true;
}
}
catch (Exception ex)
{
throw new GenericFrameworkException(new GenericFrameworkError()
{
ErrorType = ErrorType.SysError,
ErrorCode = ErrorCode.SysError_GenericDataMapUtilityError_MapCommandFromBusinessObject,
ErrorMessage = ex.Message
});
}
}
return(status);
}
protected override void InitializeParameter(System.Data.IDbDataParameter p, Common.NamedParameter parameter, object value)
{
var sqlType = parameter.SqlType;
if (parameter.SqlType != null)
{
if (sqlType.Length > 0)
{
p.Size = sqlType.Length;
}
if (sqlType.Precision > 0)
{
p.Precision = sqlType.Precision;
}
if (sqlType.Scale > 0)
{
p.Scale = sqlType.Scale;
}
if (sqlType.Required)
{
(p as DbParameter).IsNullable = false;
}
if (value != null && value is DateTime)
{
var dt = (DateTime)value;
switch (sqlType.DbType)
{
case DBType.DateTime:
case DBType.NVarChar:
sqlType = SqlType.Get(DBType.NVarChar, 100);
value = dt.ToString(fmt);
break;
}
}
}
p.ParameterName = parameter.Name;
p.Value = value ?? DBNull.Value;
ConvertDBTypeToNativeType(p, sqlType.DbType);
}
public void InsertTest()
{
MyProcInstData mpdata = new MyProcInstData()
{
ID = 11,
Name = "niasdja",
ProcInstID = 222,
Value = "value"
};
DbMap map = new DbMapMySql("");
System.Data.IDbDataParameter[] arrr = new System.Data.IDbDataParameter[] { };
string sql = map.GeneralUpdateRef <MyProcInstData>(mpdata, new List <string>()
{
"Name", "Birthday"
}, ref arrr);
Console.WriteLine(sql);
foreach (IDbDataParameter p in arrr)
{
Console.WriteLine(p.ParameterName + "," + p.Value + "," + p.Value.GetType());
}
}
public void SetDbType(System.Data.IDbDataParameter param, Enum dbType)
{
var sqlDbParam = (SqlParameter)param;
sqlDbParam.SqlDbType = (SqlDbType)dbType;
}
private IDbDataParameter _CreateParameter(string szParameter, object sdtObject, ParameterDirection pdDirection, System.Data.IDbDataParameter sParameter)
{
sParameter.ParameterName = szParameter;
sParameter.Value = sdtObject;
sParameter.Direction = pdDirection;
return(sParameter);
}
protected override void InitializeParameter(System.Data.IDbDataParameter p, NamedParameter parameter, object value)
{
p.ParameterName = parameter.Name;
p.Value = value ?? DBNull.Value;
var sqlType = parameter.SqlType;
if (parameter.SqlType != null)
{
if (sqlType.Length > 0)
{
p.Size = sqlType.Length;
}
if (sqlType.Precision > 0)
{
p.Precision = sqlType.Precision;
}
if (sqlType.Scale > 0)
{
p.Scale = sqlType.Scale;
}
if (sqlType.Required)
{
(p as DbParameter).IsNullable = false;
}
switch (sqlType.DbType)
{
case DBType.NChar:
case DBType.NVarChar:
{
var str = value as string;
if (string.IsNullOrEmpty(str))
{
p.Size = 2;
}
else
{
p.Size = str.Length * 2;
}
break;
}
case DBType.Char:
case DBType.VarChar:
{
var str = value as string;
if (string.IsNullOrEmpty(str))
{
p.Size = 1;
}
else
{
p.Size = str.Length * 1;
}
break;
}
case DBType.Guid:
parameter.sqlType = SqlType.Get(DBType.Binary, 16);
if (value is Guid)
{
p.Value = ((Guid)value).ToByteArray();
}
else if (value != null)
{
p.Value = (((Guid?)value).Value).ToByteArray();
}
break;
case DBType.Binary:
case DBType.Image:
if (value is Guid)
{
p.Value = ((Guid)value).ToByteArray();
parameter.sqlType = SqlType.Get(DBType.Binary, 16);
}
else if (value is Guid?)
{
p.Value = ((Guid?)value).Value.ToByteArray();
parameter.sqlType = SqlType.Get(DBType.Binary, 16);
}
break;
}
}
ConvertDBTypeToNativeType(p, parameter.sqlType.DbType);
}
public void SetDbType(System.Data.IDbDataParameter param, Enum dbType)
{
param.DbType = (DbType)dbType;
}
public override void SetValue(System.Data.IDbDataParameter parameter, Guid value)
{
parameter.Value = value.ToString();
}
public override void SetValue(System.Data.IDbDataParameter parameter, Guid value)
{
var guidAsBytes = value.ToByteArray();
parameter.Value = guidAsBytes;
}
public override void SetValue(System.Data.IDbDataParameter parameter, List <RolePermissions> value)
{
parameter.Value = JsonConvert.SerializeObject(value);
}
public System.Data.IDbCommand SetupDbCommand(Update updateCommand, IConnection connection)
{
var FieldList = new System.Text.StringBuilder();
var DbCommand = connection.DbConnection.CreateCommand();
foreach (IColumnValue Fld in updateCommand.ColumnValues)
{
string FieldParam;
if (Fld.Value is qGen.SqlFunctions)
{
switch (((qGen.SqlFunctions)(Fld.Value)))
{
case SqlFunctions.Now:
FieldParam = "NOW()";
break;
default:
throw new NotImplementedException();
}
}
else if (Fld.Value is qGen.SqlExpression)
{
FieldParam = Fld.Value.ToString();
}
else
{
if (connection.DbConnection is System.Data.Odbc.OdbcConnection)
{
FieldParam = "?";
}
else
{
FieldParam = "@" + Fld.ColumnName;
}
}
if (FieldList.Length == 0)
{
FieldList.Append(@"""" + Fld.ColumnName + @"""=" + FieldParam);
}
else
{
FieldList.Append(@", """ + Fld.ColumnName + @"""=" + FieldParam);
}
if (FieldParam == "?" || FieldParam.Substring(0, 1) == "@")
{
System.Data.IDbDataParameter Param = connection.Factory.Driver.GetParameter();
Param.ParameterName = "@" + Fld.ColumnName;
if (Fld.Value is DbDateTime && Fld.Value != null)
{
Param.Value = ((DbDateTime)(Fld.Value)).Value;
}
else if (Fld.Value != null && Fld.Value.GetType().IsEnum)
{
Param.Value = System.Convert.ToInt32(Fld.Value);
}
else
{
Param.Value = Fld.Value;
}
if (Fld.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
Param.DbType = System.Data.DbType.Binary;
}
// FIXME: no debería hacer una excepción para OdbcDriver
if (connection.Factory.Driver is OdbcDriver && Fld.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
((System.Data.Odbc.OdbcParameter)Param).OdbcType = System.Data.Odbc.OdbcType.VarBinary;
}
DbCommand.Parameters.Add(Param);
}
}
DbCommand.CommandText = @"UPDATE """ + string.Join <string>(",", updateCommand.Tables) + @""" SET " + FieldList.ToString() + " WHERE " + this.SqlText(updateCommand.WhereClause);
return(DbCommand);
}
