public virtual void Add(IDbDataParameter item)
{
if (item == null)
throw new ArgumentException("Item can not be null");
parameters.Add(item);
}
/// <summary>
/// Selects NET_Profile__default values from Database and assigns them to the appropriate DO_NET_Profile__default property.
/// </summary>
/// <param name="IFProfile_in">IFProfile</param>
/// <param name="dbConnection_in">Database connection, making the use of Database Transactions possible on a sequence of operations across the same or multiple DataObjects</param>
/// <returns>null if NET_Profile__default doesn't exists at Database</returns>
public static SO_NET_Profile__default getObject(
long IFProfile_in,
DBConnection dbConnection_in
) {
SO_NET_Profile__default _output = null;
DBConnection _connection = (dbConnection_in == null)
? DO__Utilities.DBConnection_createInstance(
DO__Utilities.DBServerType,
DO__Utilities.DBConnectionstring,
DO__Utilities.DBLogfile
)
: dbConnection_in;
IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
_connection.newDBDataParameter("IFProfile_", DbType.Int64, ParameterDirection.InputOutput, IFProfile_in, 0)
};
_connection.Execute_SQLFunction("sp0_NET_Profile__default_getObject", _dataparameters);
if (dbConnection_in == null) { _connection.Dispose(); }
if (_dataparameters[0].Value != DBNull.Value) {
_output = new SO_NET_Profile__default();
if (_dataparameters[0].Value == System.DBNull.Value) {
_output.IFProfile = 0L;
} else {
_output.IFProfile = (long)_dataparameters[0].Value;
}
_output.HasChanges = false;
return _output;
}
return null;
}
public IDbDataParameter[] GetParameters(DbManager db, object obj)
{
var parameters = new IDbDataParameter[_parameters.Count];
for (var i = 0; i < _parameters.Count; i++)
{
var info = _parameters[i];
//parameters[i] = db.Parameter(info.ParameterName, info.MemberMapper.GetValue(obj));
var mmi = info.MemberMapper.MapMemberInfo;
var val = info.MemberMapper.GetValue(obj);
if (val == null && mmi.Nullable/* && mmi.NullValue == null*/)
{
//replace value with DbNull
val = DBNull.Value;
}
if (mmi.IsDbTypeSet)
{
parameters[i] = mmi.IsDbSizeSet
? db.Parameter(info.ParameterName, val, info.MemberMapper.DbType, mmi.DbSize)
: db.Parameter(info.ParameterName, val, info.MemberMapper.DbType);
}
else
{
parameters[i] = db.Parameter(info.ParameterName, val);
}
}
return parameters;
}
public int UpdateCityByID(int id,String name)
{
IDbDataParameter[] idb = new IDbDataParameter[2];
idb[0] = createParameter("@Ci_Id", id);
idb[1] = createParameter("@Name", name);
return ExecuteNonQuery("UpdateCityByID",idb);
}
/// <summary>
/// 探索运行时的存储过程,返回IDbDataParameter参数数组.
/// 初始化参数值为 DBNull.Value.
/// </summary>
/// <param>一个有效的数据库连接</param>
/// <param>存储过程名称</param>
/// <param>是否包含返回值参数</param>
/// <returns>返回IDbDataParameter参数数组</returns>
private static IDbDataParameter[] DiscoverSpParameterSet(IDbConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("IDbConnection 连接对象为NULL");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存储过程名称为null");
IDbCommand cmd = DataBaseFactory.GetIDbCommand();//. new IDbCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
connection.Open();
// 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
// SqlCommandBuilder.DeriveParameters(cmd); //这里考虑一下如何是区分不同数据库的填充
connection.Close();
// 如果不包含返回值参数,将参数集中的每一个参数删除.
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
// 创建参数数组
IDbDataParameter[] discoveredParameters = new IDbDataParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化参数值为 DBNull.Value.
foreach (IDbDataParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
public override void Initialize(IDatabaseEngine engine, IDbCommand command)
{
string innerParameterName = engine.GetParameterName(ParameterName);
sqlSnippet = sqlSnippet.Replace(REPLACEMENT_VALUE, innerParameterName);
_innerParameter = command.CreateParameter();
_innerParameter.ParameterName = innerParameterName;
}
protected override IDbDataParameter OnParameterCreated(IDbDataParameter parameter, IDacParameter dacParameter)
{
var dbParam = (SqlParameter)parameter;
var setSize = false;
if (DbSchemaInfo.Parameters.ShouldBeDateTime(dacParameter))
{
dbParam.DbType = DbType.DateTime;
return dbParam;
}
if (DbSchemaInfo.Parameters.ShouldBeNonUnicodeString(dacParameter))
{
dbParam.SqlDbType = SqlDbType.VarChar;
setSize = true;
}
else if (DbSchemaInfo.Parameters.ShouldBeUnicodeString(dacParameter))
{
dbParam.SqlDbType = SqlDbType.NVarChar;
setSize = true;
}
if (setSize)
{
dbParam.Size = (dacParameter.Value.ToStringOrNull() ?? string.Empty).Length;
return dbParam;
}
return dbParam;
}
/// <summary>
/// 记录 Sql 执行过程。
///
/// 把 SQL 语句及参数，写到 SQL_TRACE_FILE 配置所对应的文件中。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="connectionSchema"></param>
public override void LogDbAccessed(string sql, IDbDataParameter[] parameters, DbConnectionSchema connectionSchema)
{
if (_sqlTraceFile == null)
{
_sqlTraceFile = ConfigurationHelper.GetAppSettingOrDefault("SQL_TRACE_FILE", string.Empty);
}
if (_sqlTraceFile.Length > 0)
{
var content = sql;
if (parameters.Length > 0)
{
var pValues = parameters.Select(p =>
{
var value = p.Value;
if (value is string)
{
value = '"' + value.ToString() + '"';
}
return value;
});
content += Environment.NewLine + "Parameters:" + string.Join(",", pValues);
}
content = DateTime.Now + Environment.NewLine + "Database: " + connectionSchema.Database + Environment.NewLine + content + "\r\n\r\n\r\n";
AppendAllText(_sqlTraceFile, content);
}
}
public override void InitDbParam(IDbDataParameter p, Type fieldType)
{
var sqlParam = (SqlParameter)p;
sqlParam.SqlDbType = SqlDbType.Udt;
sqlParam.IsNullable = (fieldType.IsGenericType && fieldType.GetGenericTypeDefinition() == typeof(Nullable<>));
sqlParam.UdtTypeName = ColumnDefinition;
}
public override void InitDbParam(IDbDataParameter p, Type fieldType)
{
var sqlParam = (SqlParameter)p;
sqlParam.SqlDbType = SqlDbType.Udt;
sqlParam.IsNullable = fieldType.IsNullableType();
sqlParam.UdtTypeName = ColumnDefinition;
}
public override void SetParameterValue(IDbDataParameter parameter, object value)
{
if (parameter.DbType == DbType.DateTime2)
parameter.DbType = DbType.DateTime;
base.SetParameterValue(parameter, value);
}
protected override void SetParameterType(IDbDataParameter parameter, DataType dataType)
{
// Do some magic to workaround 'Data type mismatch in criteria expression' error
// in JET for some european locales.
//
switch (dataType)
{
// OleDbType.Decimal is locale aware, OleDbType.Currency is locale neutral.
//
case DataType.Decimal :
case DataType.VarNumeric :
((OleDbParameter)parameter).OleDbType = OleDbType.Decimal; return;
//((OleDbParameter)parameter).OleDbType = OleDbType.Currency; return;
// OleDbType.DBTimeStamp is locale aware, OleDbType.Date is locale neutral.
//
case DataType.DateTime :
case DataType.DateTime2 : ((OleDbParameter)parameter).OleDbType = OleDbType.Date; return;
case DataType.Text : ((OleDbParameter)parameter).OleDbType = OleDbType.LongVarChar; return;
case DataType.NText : ((OleDbParameter)parameter).OleDbType = OleDbType.LongVarWChar; return;
//case DataType.Int32 : ((OleDbParameter)parameter).OleDbType = OleDbType.Integer; return;
}
base.SetParameterType(parameter, dataType);
}
public SqliteGisModelReader(string fileName)
{
mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3");
mDataConnection.Open();
SQLiteCommand getModelCommand = mDataConnection.CreateCommand();
getModelCommand.CommandText = "SELECT CorrectionConstant, CorrectionParameter FROM Model";
IDataReader reader = getModelCommand.ExecuteReader();
while (reader.Read())
{
mCorrectionConstant = reader.GetInt32(0);
mCorrectionParameter = reader.GetDouble(1);
}
reader.Close();
List<string> outcomeLabels = new List<string>();
getModelCommand.CommandText = "SELECT OutcomeLabel FROM Outcome ORDER BY OutcomeID";
reader = getModelCommand.ExecuteReader();
while (reader.Read())
{
outcomeLabels.Add(reader.GetString(0));
}
reader.Close();
mOutcomeLabels =outcomeLabels.ToArray();
mGetParameterCommand = mDataConnection.CreateCommand();
mGetParameterCommand.CommandText = "SELECT PredicateParameter.OutcomeID, PredicateParameter.Parameter FROM PredicateParameter INNER JOIN Predicate ON PredicateParameter.PredicateID = Predicate.PredicateID WHERE Predicate.PredicateLabel = ?";
mPredicateParameter = new SQLiteParameter();
mPredicateParameter.DbType = DbType.String;
mPredicateParameter.Size = 255;
mGetParameterCommand.Parameters.Add(mPredicateParameter);
}
public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
{
if (parameter.Value is string && parameter.DbType == DbType.Guid)
parameter.DbType = DbType.AnsiString;
base.AttachParameter(command, parameter);
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter)
this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
else
base.InitializeParameter(dbParam, name, sqlType);
}
public ProductAttrValueInfo GetProductAttrValueInfo(string _proID,int _attrID)
{
ProductAttrValueInfo productAttrValueInfo = null;
StringBuilder sql= new StringBuilder();
sql.Append("SELECT * FROM");
sql.Append(" [Tbl_ProductAttrValue]");
sql.Append(" WHERE");
sql.Append(" [ProID]=@ProID");
sql.Append(" AND");
sql.Append(" [AttrID]=@AttrID");
using(NetShopHelper dbhelper=new NetShopHelper())
{
IDbDataParameter[] p_Parms = new IDbDataParameter[]{
dbhelper.CreateParameter("@ProID",_proID),
dbhelper.CreateParameter("@AttrID",_attrID)
};
using(IDataReader reader=dbhelper.ExecuteReader(sql.ToString(),p_Parms))
{
if(reader.Read())
{
productAttrValueInfo = ProductAttrValueInfo.SetValue(reader);
}
}
}
return productAttrValueInfo;
}
public HowTo_Execute_SQLFunction() {
long _iduser = 0L;
DBConnection _con = DBConnectionsupport.CreateInstance(
// set your db server type here
DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
_con.newDBDataParameter(
"IDUser_",
DbType.Int64,
ParameterDirection.Input,
_iduser,
0
)
};
// call you function
_con.Execute_SQLFunction(
"sp0_User_delObject",
_dataparameters
);
_con.Dispose(); _con = null;
}
public void UseStoredProcedure(string name, object args)
{
name.MustNotBeEmpty();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = name;
var provider = _db.Provider;
var paramDict = CreateParamsDictionary(args);
var allp = _cmd.Parameters;
IDbDataParameter p = null;
p = _cmd.CreateParameter();
provider.SetupParameter(p, "ReturnValue", 0);
allp.Add(p);
_return = p;
p.Direction = ParameterDirection.ReturnValue;
foreach (var kv in paramDict)
{
p = _cmd.CreateParameter();
var pname = kv.Key;
if (kv.Key.StartsWith("_"))
{
pname = kv.Key.Substring(1);
p.Direction = ParameterDirection.Output;
}
provider.SetupParameter(p, pname, kv.Value);
allp.Add(p);
_output.Add(p);
}
}
/// <summary>
/// Executes a stored procedure.
/// </summary>
/// <param name="storedProcedure">Name of stored procedure to execute.</param>
/// <param name="parameters">Parameters to pass to stored procedure.</param>
/// <exception cref="MulhollandException">Wraps any data exception which may occur.</exception>
public void ExecuteNonQuery(string storedProcedure, IDbDataParameter[] parameters)
{
IDbCommand cmd = null;
try
{
cmd = new SqlCommand(storedProcedure, new SqlConnection(_connectionString));
cmd.CommandType = CommandType.StoredProcedure;
AttachParametersToCommand(cmd, parameters);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new MulhollandException("Encoutered an error performing data operation.", exc);
}
finally
{
if (cmd != null)
{
CloseConnection(cmd);
cmd.Dispose();
}
}
}
/// <summary>
/// Executes astored procedure opening up an IDataReader to process the result.
/// </summary>
/// <param name="storedProcedure">Name of stored procedure to execute.</param>
/// <param name="parameters">Parameters to pass to stored procedure.</param>
/// <returns>IDataReader</returns>
/// <exception cref="MulhollandException">Wraps any data exception which may occur.</exception>
public IDataReader ExecuteReader(string storedProcedure, IDbDataParameter[] parameters)
{
IDbCommand cmd = null;
IDataReader reader = null;
try
{
cmd = new SqlCommand(storedProcedure, new SqlConnection(_connectionString));
cmd.CommandType = CommandType.StoredProcedure;
AttachParametersToCommand(cmd, parameters);
cmd.Connection.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception exc)
{
throw new MulhollandException("Encoutered an error performing data operation.", exc);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
return reader;
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
if (sqlType.DbType == DbType.Currency)
dbParam.DbType = DbType.Decimal;
}
private static void SetDefaultParameterSize(IDbDataParameter dbParam, SqlType sqlType)
{
switch (dbParam.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
dbParam.Size = MaxAnsiStringSize;
break;
case DbType.Binary:
if (sqlType is BinaryBlobSqlType)
{
dbParam.Size = MaxBinaryBlobSize;
}
else
{
dbParam.Size = MaxBinarySize;
}
break;
case DbType.Decimal:
dbParam.Precision = MaxPrecision;
dbParam.Scale = MaxScale;
break;
case DbType.String:
case DbType.StringFixedLength:
dbParam.Size = IsText(dbParam, sqlType) ? MaxStringClobSize : MaxStringSize;
break;
case DbType.DateTime2:
dbParam.Size = MaxDateTime2;
break;
case DbType.DateTimeOffset:
dbParam.Size = MaxDateTimeOffset;
break;
}
}
protected override void InitializeParameter(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.DateTime:
p.DbType = DbType.String;
if (p.Value != DBNull.Value && !object.Equals(null, p.Value))
p.Value = ((DateTime)p.Value).ToString("yyyy/MM/dd HH:mm:ss");
break;
case DBType.Int64:
p.DbType = DbType.Int32;
if (p.Value != DBNull.Value && !object.Equals(null, p.Value))
p.Value = System.Convert.ToInt32((long)p.Value);
break;
}
}
//p.DbType = SqlType.ToDbType(parameter.sqlType.DbType);
ConvertDBTypeToNativeType(p, sqlType.DbType);
}
protected override IDbDataParameter OnParameterCreated(IDbDataParameter parameter, IDacParameter dacParameter)
{
var dbParam = (SqlCeParameter)parameter;
if (DbSchemaInfo.Parameters.ShouldBeDateTime(dacParameter))
{
dbParam.DbType = DbType.DateTime;
return dbParam;
}
if (DbSchemaInfo.Parameters.ShouldBeJson(dacParameter))
{
dbParam.SqlDbType = SqlDbType.NText;
dbParam.Size = (dacParameter.Value.ToStringOrNull() ?? string.Empty).Length;
return dbParam;
}
if (DbSchemaInfo.Parameters.ShouldBeUnicodeString(dacParameter))
{
dbParam.SqlDbType = SqlDbType.NVarChar;
var len = (dacParameter.Value.ToStringOrNull() ?? string.Empty).Length;
if (len > MaxLenOfStringBeforeEscalating)
throw new SisoDbException(ExceptionMessages.SqlCe4_ToLongIndividualStringValue);
dbParam.Size = len;
return dbParam;
}
return dbParam;
}
public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
{
if (parameter.DbType == DbType.DateTime2)
parameter.DbType = DbType.DateTime;
base.AttachParameter(command, parameter);
}
//Datetime is a special case - we need to convert to string properly
public override void SetDbParameterValue(IDbDataParameter parameter, object value)
{
if (value != null && value is DateTime)
parameter.Value = SQLiteTypeRegistry.DateTimeToString(value);
else
base.SetDbParameterValue(parameter, value);
}
public HowTo_Execute_SQLFunction_returnSomevalue() {
long _iduser = 0L;
DBConnection _con = DBConnectionsupport.CreateInstance(
// set your db server type here
DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
_con.newDBDataParameter(
"IDUser_",
DbType.Int64,
ParameterDirection.Input,
_iduser,
0
)
};
// call you function and get returning value
bool _result = (bool)_con.Execute_SQLFunction(
"fnc0_User_isObject",
_dataparameters,
DbType.Boolean, // returning value db type
0 // returning value size, if applicable
);
_con.Dispose(); _con = null;
}
public HowTo_Execute_SQLFunction_returnDataTable() {
long _idgroup_search = 1L;
DBConnection _con = DBConnectionsupport.CreateInstance(
// set your db server type here
DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
_con.newDBDataParameter(
"IDGroup_search_",
DbType.Int64,
ParameterDirection.Input,
_idgroup_search,
0
)
};
// you now have a DataTable populated
// with results from your sql function
DataTable _datatable
= _con.Execute_SQLFunction_returnDataTable(
"sp0_User_Record_open_byGroup_fullmode",
_dataparameters
);
_con.Dispose(); _con = null;
}
/// <summary>
/// Selects vNET_User values from Database (based on the search condition) and assigns them to the appropriate DO0_vNET_User property.
/// </summary>
/// <param name="IDUser_search_in">IDUser search condition</param>
/// <param name="dbConnection_in">Database connection, making the use of Database Transactions possible on a sequence of operations across the same or multiple DataObjects</param>
/// <returns>null if vNET_User doesn't exists at Database</returns>
public static SO_vNET_User getObject_byUser(
long IDUser_search_in,
DBConnection dbConnection_in
) {
SO_vNET_User _output = null;
DBConnection _connection = (dbConnection_in == null)
? DO__utils.DBConnection_createInstance(
DO__utils.DBServerType,
DO__utils.DBConnectionstring,
DO__utils.DBLogfile
)
: dbConnection_in;
IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
_connection.newDBDataParameter("IDUser_search_", DbType.Int64, ParameterDirection.Input, IDUser_search_in, 0),
_connection.newDBDataParameter("IDUser", DbType.Int64, ParameterDirection.Output, null, 0),
_connection.newDBDataParameter("IFApplication", DbType.Int32, ParameterDirection.Output, null, 0),
_connection.newDBDataParameter("Login", DbType.AnsiString, ParameterDirection.Output, null, 255),
_connection.newDBDataParameter("Name", DbType.AnsiString, ParameterDirection.Output, null, 255),
_connection.newDBDataParameter("EMail", DbType.AnsiString, ParameterDirection.Output, null, 255)
};
_connection.Execute_SQLFunction(
"sp0_vNET_User_getObject_byUser",
_dataparameters
);
if (dbConnection_in == null) { _connection.Dispose(); }
if (_dataparameters[1].Value != DBNull.Value) {
_output = new SO_vNET_User();
if (_dataparameters[1].Value == System.DBNull.Value) {
_output.IDUser = 0L;
} else {
_output.IDUser = (long)_dataparameters[1].Value;
}
if (_dataparameters[2].Value == System.DBNull.Value) {
_output.IFApplication_isNull = true;
} else {
_output.IFApplication = (int)_dataparameters[2].Value;
}
if (_dataparameters[3].Value == System.DBNull.Value) {
_output.Login_isNull = true;
} else {
_output.Login = (string)_dataparameters[3].Value;
}
if (_dataparameters[4].Value == System.DBNull.Value) {
_output.Name_isNull = true;
} else {
_output.Name = (string)_dataparameters[4].Value;
}
if (_dataparameters[5].Value == System.DBNull.Value) {
_output.EMail_isNull = true;
} else {
_output.EMail = (string)_dataparameters[5].Value;
}
return _output;
}
return null;
}
static void SetUdtTypeName(IDbDataParameter parameter, object value)
{
var sqlParameter = parameter as System.Data.SqlClient.SqlParameter;
var valueType = value.GetType();
if (sqlParameter != null)
sqlParameter.UdtTypeName = _udtTypeNameResolvers.Select(_=>_(valueType)).FirstOrDefault(_=>!string.IsNullOrEmpty(_));
}
internal static void SetParamValue(this IOrmLiteDialectProvider dialectProvider, IDbDataParameter p, object value, Type propType, FieldDefinition fieldDef = null)
{
if (fieldDef != null)
{
value = dialectProvider.GetFieldValue(fieldDef, value);
var valueType = value?.GetType();
if (valueType != null && valueType != propType)
{
dialectProvider.InitDbParam(p, valueType);
}
}
else
{
value = dialectProvider.GetFieldValue(propType, value);
var valueType = value?.GetType();
if (valueType != null && valueType != propType)
{
dialectProvider.InitDbParam(p, valueType);
}
}
p.Value = value == null
? DBNull.Value
: p.DbType == DbType.String
? value.ToString()
: value;
}
//[STAThread]
public static void Main(string[] args)
{
//DateTime myDateTime = DateTime.Parse("2019-11-06 12:11:11.1234567");
//Console.WriteLine(myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + " 刻度数：" + myDateTime.Ticks);
//myDateTime = DateTime.Parse("2019-11-06 12:11:11.1234560");
//Console.WriteLine(myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + " 刻度数：" + myDateTime.Ticks);
//Console.ReadKey();
bool isDebug = false;
ITest test = null;
string fileName = string.Empty;
DatabaseType databaseType = DatabaseType.None;
string s = args != null && args.Length > 0 ? args[0] : null;
if (!string.IsNullOrEmpty(s))
{
databaseType = (DatabaseType)Convert.ToByte(s);
}
// 命令拦截
var interceptor = new DbCommandInterceptor
{
OnExecuting = cmd =>
{
var writer = System.IO.File.AppendText(fileName);
writer.WriteLine(cmd.CommandText);
if (cmd.Parameters != null)
{
for (int i = 0; i < cmd.Parameters.Count; i++)
{
IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
writer.Write("-- ");
writer.Write(p.ParameterName);
writer.Write(" = ");
writer.Write(p.Value == null ? string.Empty : (p.Value is byte[] ? XfwCommon.BytesToHex((byte[])p.Value, true, true) : p.Value));
writer.Write(", DbType = {0}, ", p.DbType);
if (p.Size != default(int))
{
writer.Write("Size = {0}, ", p.Size);
}
if (p.Precision != default(byte))
{
writer.Write("Precision = {0}, ", p.Precision);
}
if (p.Scale != default(byte))
{
writer.Write("Scale = {0}, ", p.Scale);
}
if (p.Direction != ParameterDirection.Input)
{
writer.Write("Direction = {0}, ", p.Direction);
}
writer.WriteLine();
if (i == cmd.Parameters.Count - 1)
{
writer.WriteLine();
}
}
}
writer.Close();
},
OnExecuted = cmd => { }
};
DbInterception.Add(interceptor);
foreach (DatabaseType item in Enum.GetValues(typeof(DatabaseType)))
{
if (item == DatabaseType.None)
{
continue;
}
DatabaseType myDatabaseType = item;
if (!string.IsNullOrEmpty(s))
{
myDatabaseType = databaseType;
}
var obj = Activator.CreateInstance(null, string.Format("TZM.XFramework.UnitTest.{0}.{0}Test", myDatabaseType));
test = (ITest)(obj.Unwrap());
test.IsDebug = isDebug;
string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
fileName = baseDirectory + @"\Log_" + myDatabaseType + ".sql";
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
if (test != null)
{
Console.WriteLine(myDatabaseType + " BEGIN");
test.Run(myDatabaseType);
Console.WriteLine(myDatabaseType + " END");
}
if (!string.IsNullOrEmpty(s))
{
break;
}
}
Console.WriteLine("回车退出~");
Console.ReadLine();
}
protected override string GetUdtTypeName(IDbDataParameter parameter)
{
return(((System.Data.SqlClient.SqlParameter)parameter).UdtTypeName);
}
public override void SetValue(IDbDataParameter parameter, List <FinancialEvidence> value)
{
parameter.Value = JsonConvert.SerializeObject(value);
}
public virtual void SetParameter(IDbDataParameter parameter, string name, DataType dataType, object value)
{
switch (dataType)
{
case DataType.Char:
case DataType.NChar:
case DataType.VarChar:
case DataType.NVarChar:
case DataType.Text:
case DataType.NText:
if (value is DateTimeOffset)
{
value = ((DateTimeOffset)value).ToString("yyyy-MM-ddTHH:mm:ss.ffffff zzz");
}
else if (value is DateTime)
{
var dt = (DateTime)value;
value = dt.ToString(
dt.Millisecond == 0
? dt.Hour == 0 && dt.Minute == 0 && dt.Second == 0
? "yyyy-MM-dd"
: "yyyy-MM-ddTHH:mm:ss"
: "yyyy-MM-ddTHH:mm:ss.fff");
}
else if (value is TimeSpan)
{
var ts = (TimeSpan)value;
value = ts.ToString(
ts.Days > 0
? ts.Milliseconds > 0
? "d\\.hh\\:mm\\:ss\\.fff"
: "d\\.hh\\:mm\\:ss"
: ts.Milliseconds > 0
? "hh\\:mm\\:ss\\.fff"
: "hh\\:mm\\:ss");
}
break;
case DataType.Image:
case DataType.Binary:
case DataType.Blob:
case DataType.VarBinary:
if (value is Binary)
{
value = ((Binary)value).ToArray();
}
break;
case DataType.Int64:
if (value is TimeSpan)
{
value = ((TimeSpan)value).Ticks;
}
break;
case DataType.Xml:
if (value is XDocument)
{
value = value.ToString();
}
else if (value is XmlDocument)
{
value = ((XmlDocument)value).InnerXml;
}
break;
}
parameter.ParameterName = name;
SetParameterType(parameter, dataType);
parameter.Value = value ?? DBNull.Value;
}
public override void SetValue(IDbDataParameter parameter, MessageNumber value)
{
parameter.Value = value == MessageNumber.NotSet ? -1 : value.Value;
}
//private List<PermisoComponente> ObtenerPermisos(string idPermiso)
//{
// try
// {
// var lstPermisos = new List<PermisoComponente>();
// var pars = new IDbDataParameter[1];
// string query = "";
// if (!String.IsNullOrEmpty(idPermiso))
// {
// var where = idPermiso;
// pars[0] = _accesoBaseDeDatos.CrearParametro("@Condicion", Convert.ToInt32(where));
// //query = $@"with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
// // from PermisoPermiso SP2
// // where sp2.IdPadre = @Condicion
// // UNION ALL
// // select sp.IdPadre, sp.IdHijo
// // from PermisoPermiso sp
// // inner join Recursivo r on r.IdHijo = sp.IdPadre
// // )
// // select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
// // from recursivo r
// // inner join Permiso p on (r.IdHijo = p.Id)
// // ";
// query = $@"with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
// from PermisoPermiso SP2
// where sp2.IdPadre = @Condicion
// UNION ALL
// select sp.IdPadre, sp.IdHijo
// from PermisoPermiso sp
// inner join Recursivo r on r.IdHijo = sp.IdPadre
// )
// select perper.IdPadre, perper.IdHijo, p.Id, p.Descripcion, p.EsPermiso
// from PermisoPermiso perper
// inner join Permiso p on (perper.IdHijo = p.Id)
// where p.Id = @Condicion
// union all
// select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
// from recursivo r
// inner join Permiso p on (r.IdHijo = p.Id)
// ";
// }
// else
// {
// pars = null;
// query = $@"with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
// from PermisoPermiso SP2
// where sp2.IdPadre is null
// UNION ALL
// select sp.IdPadre, sp.IdHijo
// from PermisoPermiso sp
// inner join Recursivo r on r.IdHijo = sp.IdPadre
// )
// select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
// from recursivo r
// inner join Permiso p on (r.IdHijo = p.Id)
// ";
// }
// var dtPermisos = _accesoBaseDeDatos.Seleccionar(query, pars);
// if (dtPermisos.Rows.Count == 0)
// return null;
// foreach (DataRow row in dtPermisos.Rows)
// {
// int idPadre = 0;
// if (row["IdPadre"] != DBNull.Value)
// {
// idPadre = Convert.ToInt32(row["IdPadre"]);
// }
// var id = Convert.ToInt32(row["Id"]);
// var descripcion = row["Descripcion"].ToString().Trim();
// var esPermiso = Convert.ToBoolean(row["EsPermiso"].ToString().Trim());
// PermisoComponente componente;
// if (!esPermiso)
// componente = new GrupoPermiso();
// else
// componente = new BE.Composite.Permiso();
// componente.Id = id;
// componente.Descripcion = descripcion;
// var padre = ObtenerComponente(idPadre, lstPermisos);
// if (padre == null)
// {
// lstPermisos.Add(componente);
// }
// else
// {
// padre.Agregar(componente);
// }
// }
// return lstPermisos;
// }
// catch (Exception e)
// {
// Console.WriteLine(e);
// throw;
// }
//}
private PermisoComponente ObtenerPermisos(string idPermiso)
{
try
{
var lstPermisos = new List <PermisoComponente>();
var pars = new IDbDataParameter[1];
string query = "";
if (!String.IsNullOrEmpty(idPermiso))
{
var where = idPermiso;
pars[0] = _accesoBaseDeDatos.CrearParametro("@Condicion", Convert.ToInt32(where));
//query = $@"with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
// from PermisoPermiso SP2
// where sp2.IdPadre = @Condicion
// UNION ALL
// select sp.IdPadre, sp.IdHijo
// from PermisoPermiso sp
// inner join Recursivo r on r.IdHijo = sp.IdPadre
// )
// select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
// from recursivo r
// inner join Permiso p on (r.IdHijo = p.Id)
// ";
query = $@"with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
from PermisoPermiso SP2
where sp2.IdPadre = @Condicion
UNION ALL
select sp.IdPadre, sp.IdHijo
from PermisoPermiso sp
inner join Recursivo r on r.IdHijo = sp.IdPadre
)
select perper.IdPadre, perper.IdHijo, p.Id, p.Descripcion, p.EsPermiso
from PermisoPermiso perper
right join Permiso p on (perper.IdHijo = p.Id)
where p.Id = @Condicion
union all
select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
from recursivo r
inner join Permiso p on (r.IdHijo = p.Id)
";
}
else
{
pars = null;
query = $@" with Recursivo as ( select sp2.IdPadre, sp2.IdHijo
from PermisoPermiso SP2
where sp2.IdPadre is null
UNION ALL
select sp.IdPadre, sp.IdHijo
from PermisoPermiso sp
inner join Recursivo r on r.IdHijo = sp.IdPadre
)
select r.IdPadre, r.IdHijo, p.Id, p.Descripcion, p.EsPermiso
from recursivo r
right join Permiso p on (r.IdHijo = p.Id)
";
}
var dtPermisos = _accesoBaseDeDatos.Seleccionar(query, pars);
if (dtPermisos.Rows.Count == 0)
{
return(null);
}
foreach (DataRow row in dtPermisos.Rows)
{
int idPadre = 0;
if (row["IdPadre"] != DBNull.Value)
{
idPadre = Convert.ToInt32(row["IdPadre"]);
}
var id = Convert.ToInt32(row["Id"]);
var descripcion = row["Descripcion"].ToString().Trim();
var esPermiso = Convert.ToBoolean(row["EsPermiso"].ToString().Trim());
PermisoComponente componente;
if (!esPermiso)
{
componente = new GrupoPermiso();
}
else
{
componente = new BE.Composite.Permiso();
}
componente.Id = id;
componente.Descripcion = descripcion;
var padre = ObtenerComponente(idPadre, lstPermisos);
if (padre == null)
{
lstPermisos.Add(componente);
}
else
{
padre.Agregar(componente);
}
}
return(lstPermisos[0]);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public static void SetValue(IDbDataParameter parameter, object value) => handler.SetValue(parameter, value);
public override void SetValue(IDbDataParameter parameter, JObject value)
{
parameter.Value = value?.ToString(Formatting.None);
((NpgsqlParameter)parameter).NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
//Just a simple List<string> type handler implementation
public override void SetValue(IDbDataParameter parameter, List <string> value)
{
parameter.Value = string.Join(",", value);
}
public override void SetValue(IDbDataParameter parameter, RatingValue value)
{
// ... null, range checks etc ...
parameter.DbType = System.Data.DbType.Int32;
parameter.Value = value.Value;
}
public override void SetValue(IDbDataParameter parameter, T value)
{
SetValueWasCalled = true;
parameter.Value = value;
}
/// <summary>
/// 修改数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="con"></param>
/// <param name="t"></param>
/// <param name="trans"></param>
/// <returns></returns>
public int Edit <T>(T t) where T : class, new()
{
var con = CreateConnection();
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
List <IDbDataParameter> parameters = new List <IDbDataParameter>();
StringBuilder s_where = new StringBuilder();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
int count = propertyInfo.GetCustomAttributes(false).Where(a => a is KeyAttribute).Count();
if (count <= 0)
{
continue;
}
object obj = propertyInfo.GetValue(t, null);
IDbDataParameter parameter = null;
if (obj == null)
{
parameter = CreateParameter(propertyInfo.Name, DBNull.Value);
}
else
{
parameter = CreateParameter(propertyInfo.Name, obj);
}
parameter.DbType = SetDbType(obj);
if (obj.GetType() == typeof(string))
{
var tmp_sla = propertyInfo.GetCustomAttributes(false).Where(a => a is StringLengthAttribute).FirstOrDefault();
if (tmp_sla != null)
{
parameter.Size = (tmp_sla as StringLengthAttribute).Length;
}
}
parameters.Add(parameter);
s_where.AppendAnd(propertyInfo.Name + "=" + ParameterPrefix + propertyInfo.Name);
}
if (parameters.Count <= 0)
{
throw new Exception("实体未设置KeyAttribute属性");
}
string tblName = typeof(T).Name;
DataSet ds = new DataSet();
var cmd = con.CreateCommand();
cmd.CommandText = "select * from " + tblName + " where " + s_where.ToString();
cmd.CommandType = CommandType.Text;
foreach (var pp in parameters)
{
cmd.Parameters.Add(pp);
}
if (dbTransaction != null)
{
cmd.Transaction = dbTransaction;
}
var dapt = CreateDataAdapter();
dapt.SelectCommand = cmd;
dapt.Fill(ds);
if (ds.Tables[0].Rows.Count <= 0)
{
throw new Exception("未找到符合条件的记录");
}
DataRow row = ds.Tables[0].Rows[0];
row.BeginEdit();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//过滤Ignore和Key属性
int count = propertyInfo.GetCustomAttributes(false).Where(a => a is IgnoreAttribute || a is KeyAttribute).Count();
if (count > 0)
{
continue;
}
object obj = propertyInfo.GetValue(t, null);
if (obj == null)
{
row[propertyInfo.Name] = DBNull.Value;
}
else
{
row[propertyInfo.Name] = obj;
}
}
row.EndEdit();
var scb = CreateCommandBuilder();
scb.DataAdapter = (System.Data.Common.DbDataAdapter)dapt;
return(dapt.Update(ds));
}
/// <summary>
/// 拆分表达式树
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="type"></param>
/// <returns></returns>
public string BinaryExpressionHandler(Expression left, Expression right, ExpressionType type, SqlOpertionHelper helper, Enum_Opertion opertion, ref List <IDbDataParameter> List_SqlParameter)
{
StringBuilder sb = new StringBuilder();
if (opertion == Enum_Opertion.Where)
{
sb.Append("(");
}
string needParKey = "=,>,<,>=,<=,<>";
string leftPar = RouteExpressionHandler(left, helper, opertion, ref List_SqlParameter);
string typeStr = null;
if (opertion == Enum_Opertion.Where)
{
typeStr = ExpressionTypeCast(type);
}
else if (opertion == Enum_Opertion.Set)
{
typeStr = ExpressionTypeCastSet(type);
}
var isRight = needParKey.IndexOf(typeStr) > -1;
string rightPar = RouteExpressionHandler(right, helper, opertion, ref List_SqlParameter, isRight);
if (!ContainsKey(leftPar) && !ContainsKey(rightPar))
{
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + leftPar)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + leftPar + Count;
if (rightPar.ToLower() == "true")
{
parameter.Value = true;
}
else if (rightPar.ToLower() == "false")
{
parameter.Value = false;
}
else
{
parameter.Value = rightPar;
}
List_SqlParameter.Add(parameter);
rightPar = "@" + leftPar + Count;
}
string appendLeft = leftPar;
sb.Append(appendLeft);//字段名称
if (rightPar.ToUpper() == "NULL")
{
if (typeStr == "=")
{
rightPar = " IS NULL ";
}
else if (typeStr == "<>")
{
rightPar = " IS NOT NULL ";
}
}
else
{
sb.Append(typeStr);
}
sb.Append(rightPar);
if (opertion == Enum_Opertion.Where)
{
sb.Append(")");
}
return(sb.ToString());
}
public IEnumerable <LibraryItem> GetAllItemsSortedByYearOfPublishing(Enums.Order order)
{
List <LibraryItem> list = new List <LibraryItem>();
using (IDbConnection connection = Common.CreateConnection())
{
connection.Open();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAll";
IDbDataParameter sortingMode = Common.CreateParameter("@sortParam", DbType.Int32, (int)order);
cmd.Parameters.Add(sortingMode);
var reader = cmd.ExecuteReader();
string type;
while (reader.Read())
{
type = (string)reader["Type"];
if (type == "patent")
{
Patent patent = new Patent()
{
Country = (string)reader["country"],
DateOfPublication = (DateTime)reader["date_of_publication"],
Id = (int)reader["library_item_ID"],
Name = (string)reader["title"],
Note = (string)reader["note"],
NumberOfPages = (int)reader["number_of_pages"],
RegNumber = (string)reader["reg_number"],
SubmissionDocuments = (DateTime)reader["date_of_submission"],
YearOfPublishing = (int)reader["YearOfPublication"],
};
XElement xmlAuthor = XElement.Parse((string)reader["authors"]);
patent.Authors = new HashSet <Author>(Common.GetAuthorsFromXML(xmlAuthor));
list.Add(patent);
}
else if (type == "paper")
{
PaperIssue pi = new PaperIssue()
{
CityOfPublishing = (string)reader["cityOfPublication"],
DateOfIssue = (DateTime)reader["dateOfIssue"],
Id = (int)reader["library_item_ID"],
IssueNumber = (int)reader["number_of_issue"],
Note = (string)reader["note"],
NumberOfPages = (int)reader["number_of_pages"],
Paper = new Paper()
{
Id = (int)reader["id"],
Issn = (string)reader["ISSN"],
Name = (string)reader["title"]
},
Publisher = new Publisher
{
Id = (int)reader["publisherId"],
PublisherName = (string)reader["publisherName"],
},
YearOfPublishing = (int)reader["YearOfPublication"],
};
list.Add(pi);
}
else if (type == "book")
{
Book book = new Book()
{
CityOfPublishing = (string)reader["city_of_publishing"],
Id = (int)reader["library_item_ID"],
ISBN = (string)reader["ISBN"],
Name = (string)reader["title"],
NumberOfPages = (int)reader["number_of_pages"],
Note = (string)reader["note"],
Publisher = new Publisher()
{
Id = (int)reader["publisher_Id"],
PublisherName = (string)reader["publisherName"]
},
YearOfPublishing = (int)reader["YearOfPublication"]
};
XElement xmlAuthor = XElement.Parse((string)reader["authors"]);
book.Authors = new HashSet <Author>(Common.GetAuthorsFromXML(xmlAuthor));
}
else
{
throw new Exception("Unexpected type of an object in database");
}
}
}
return(list.ToArray());
}
public IDbDataParameter CreateParameter()
{
IDbDataParameter prm = _instance.CreateParameter();
return(prm);
}
public IEnumerable <LibraryItem> GetBooksAndPatentsByAuthor(Author author)
{
List <LibraryItem> list = new List <LibraryItem>();
using (var connection = Common.CreateConnection())
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "GetBooksAndPatentsByAuthor";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter authorId = Common.CreateParameter("authorId", DbType.Int32, author.Id);
var reader = cmd.ExecuteReader();
string type;
while (reader.Read())
{
type = (string)reader["Type"];
if (type == "patent")
{
Patent patent = new Patent()
{
Country = (string)reader["country"],
DateOfPublication = (DateTime)reader["date_of_publication"],
Id = (int)reader["library_item_ID"],
Name = (string)reader["title"],
Note = (string)reader["note"],
NumberOfPages = (int)reader["number_of_pages"],
RegNumber = (string)reader["reg_number"],
SubmissionDocuments = (DateTime)reader["date_of_submission"],
YearOfPublishing = (int)reader["YearOfPublication"],
};
XElement xmlAuthor = XElement.Parse((string)reader["authors"]);
patent.Authors = new HashSet <Author>(Common.GetAuthorsFromXML(xmlAuthor));
list.Add(patent);
}
else if (type == "book")
{
Book book = new Book()
{
CityOfPublishing = (string)reader["city_of_publishing"],
Id = (int)reader["library_item_ID"],
ISBN = (string)reader["ISBN"],
Name = (string)reader["title"],
NumberOfPages = (int)reader["number_of_pages"],
Note = (string)reader["note"],
Publisher = new Publisher()
{
Id = (int)reader["publisher_Id"],
PublisherName = (string)reader["publisherName"]
},
YearOfPublishing = (int)reader["YearOfPublication"]
};
XElement xmlAuthor = XElement.Parse((string)reader["authors"]);
book.Authors = new HashSet <Author>(Common.GetAuthorsFromXML(xmlAuthor));
}
else
{
throw new Exception("Unexpected type of an object in database");
}
}
}
return(list);
}
public override void SetValue(IDbDataParameter parameter, decimal value)
{
parameter.Value = value;
}
//將值存回 db 時由 object 轉為 json
public void SetValue(IDbDataParameter parameter, object value)
{
parameter.Value = (value == null) ? (object)DBNull.Value : JsonConvert.SerializeObject(value);
parameter.DbType = DbType.String;
}
internal static IDbDataParameter AsDbParameter(this QueryField queryField, IDbDataParameter parameter)
{
parameter.ParameterName = queryField.Field.Name;
parameter.Value = queryField.Parameter.Value ?? DBNull.Value;
return(parameter);
}
/// <summary>
/// Adds the parameter to command.
/// </summary>
/// <param name="cmd">The CMD.</param>
/// <param name="paramName">Name of the param.</param>
/// <param name="dbType">Type of the db.</param>
/// <param name="paramValue">The param value.</param>
/// <param name="direction">The direction.</param>
/// <returns></returns>
public static IDbDataParameter AddParameter(this IDbCommand cmd, string paramName,
DbType dbType, object paramValue, ParameterDirection direction)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = paramName;
parameter.Direction = direction;
parameter.DbType = dbType;
if (paramValue == null || paramValue is DBNull)
{
parameter.Value = DBNull.Value;
}
else
{
switch (dbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
parameter.Value = paramValue;
break;
case DbType.Int16:
parameter.Value = (short)paramValue;
break;
case DbType.Int32:
parameter.Value =
(int)paramValue == int.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Int64:
parameter.Value =
(long)paramValue == long.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Boolean:
parameter.Value = (bool)paramValue;
break;
case DbType.Byte:
parameter.Value = (byte)paramValue;
break;
case DbType.Currency:
parameter.Value = (decimal)paramValue;
break;
case DbType.Date:
parameter.Value =
(DateTime)paramValue == DateTime.MinValue.Date ? DBNull.Value : paramValue;
break;
case DbType.DateTime:
DateTime date = (DateTime)paramValue;
if (date == DateTime.MinValue)
{
parameter.Value = DBNull.Value;
}
else if (date.Year < 1900)
{
parameter.Value = date.AddYears(1900 - date.Year);
}
else
{
parameter.Value = date;
}
break;
case DbType.Decimal:
parameter.Value = (decimal)paramValue;
break;
case DbType.Double:
parameter.Value =
(double)paramValue == double.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Object:
parameter.Value = paramValue;
break;
case DbType.SByte:
parameter.Value =
(sbyte)paramValue == sbyte.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Single:
parameter.Value =
(float)paramValue == float.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Time:
parameter.Value =
(TimeSpan)paramValue == TimeSpan.MinValue ? DBNull.Value : paramValue;
break;
case DbType.UInt16:
parameter.Value =
(ushort)paramValue == ushort.MinValue ? DBNull.Value : paramValue;
break;
case DbType.UInt32:
parameter.Value =
(uint)paramValue == uint.MinValue ? DBNull.Value : paramValue;
break;
case DbType.UInt64:
parameter.Value =
(ulong)paramValue == ulong.MinValue ? DBNull.Value : paramValue;
break;
case DbType.VarNumeric:
parameter.Value =
(decimal)paramValue == decimal.MinValue ? DBNull.Value : paramValue;
break;
case DbType.Binary:
parameter.Size = ((byte[])paramValue).Length;
parameter.Value = paramValue;
break;
case DbType.Guid:
parameter.Value =
(Guid)paramValue == Guid.Empty ? DBNull.Value : paramValue;
break;
default:
parameter.Value = DBNull.Value;
break;
}
}
cmd.Parameters.Add(parameter);
return(parameter);
}
protected override string GetProviderTypeName(IDbDataParameter parameter)
{
dynamic p = parameter;
return(p.IfxType.ToString());
}
protected virtual void SetParameterType(IDbDataParameter parameter, DataType dataType)
{
DbType dbType;
switch (dataType)
{
case DataType.Char: dbType = DbType.AnsiStringFixedLength; break;
case DataType.VarChar: dbType = DbType.AnsiString; break;
case DataType.NChar: dbType = DbType.StringFixedLength; break;
case DataType.NVarChar: dbType = DbType.String; break;
case DataType.Blob:
case DataType.VarBinary: dbType = DbType.Binary; break;
case DataType.Boolean: dbType = DbType.Boolean; break;
case DataType.SByte: dbType = DbType.SByte; break;
case DataType.Int16: dbType = DbType.Int16; break;
case DataType.Int32: dbType = DbType.Int32; break;
case DataType.Int64: dbType = DbType.Int64; break;
case DataType.Byte: dbType = DbType.Byte; break;
case DataType.UInt16: dbType = DbType.UInt16; break;
case DataType.UInt32: dbType = DbType.UInt32; break;
case DataType.UInt64: dbType = DbType.UInt64; break;
case DataType.Single: dbType = DbType.Single; break;
case DataType.Double: dbType = DbType.Double; break;
case DataType.Decimal: dbType = DbType.Decimal; break;
case DataType.Guid: dbType = DbType.Guid; break;
case DataType.Date: dbType = DbType.Date; break;
case DataType.Time: dbType = DbType.Time; break;
case DataType.DateTime: dbType = DbType.DateTime; break;
case DataType.DateTime2: dbType = DbType.DateTime2; break;
case DataType.DateTimeOffset: dbType = DbType.DateTimeOffset; break;
case DataType.Variant: dbType = DbType.Object; break;
case DataType.VarNumeric: dbType = DbType.VarNumeric; break;
default: return;
}
parameter.DbType = dbType;
}
protected override string GetProviderTypeName(IDbDataParameter parameter)
{
return(((System.Data.SqlClient.SqlParameter)parameter).SqlDbType.ToString());
}
internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime)
{
DateTime started = DateTime.Now;
IDataReader reader = null;
IDbCommand cmd = null;
bool bConnOpenedHere = EnsureConnectionOpen(conn);
IDbTransaction transaction = conn.BeginTransaction();
DataSet retv = new DataSet();
try
{
cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout;
cmd.Transaction = transaction;
cmd.CommandText = cmdText;
DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter idb = cmd.CreateParameter();
cmd.Parameters.Add(HelperFunctions.BuildParameter(idb, paramList[i]));
if (reqLenTypes.Contains(idb.DbType))
{
if (idb is OdbcParameter)
{
(idb as OdbcParameter).Size = paramList[i].Size;
}
else if (idb is CtreeSqlParameter)
{
(idb as CtreeSqlParameter).Size = paramList[i].Size;
}
}
}
switch (connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.FaircomAdapter:
{
IDbDataAdapter adap = GetAdapter(connType, cmd);
retv.BeginInit();
//Since the FillSchema and Fill functions return the same instance of 'DataTable'
//There is probably a better way of doing this, but for sake of explination
//Read the db schema
bool bSchemaFound = false;
DataTable[] dta = adap.FillSchema(retv, SchemaType.Source);
DataTable clone = null;
if (dta.Length > 0)
{
bSchemaFound = true;
dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable'
retv.Tables.Remove(dta[0]); //Drop the table from the dataset
clone = dta[0].Clone(); //Clone the results
dta[0].TableName = "Table"; //Rename the datatable instance back to table
}
adap.Fill(retv); //Fill 'Table' with the actual results
if (bSchemaFound && clone != null)
{
retv.Tables.Add(clone); //Now add the 'schematable' back to the results
}
retv.EndInit();
break;
}
default:
{
DataTable dt;
reader = cmd.ExecuteReader();
if (reader.FieldCount > 0)
{
retv.Tables.Add(dt = new DataTable("Table"));
retv.Tables.Add(reader.GetSchemaTable());
switch (connType)
{
case EDtxReaderType.FaircomReader:
case EDtxReaderType.Reader:
{
dt.Load(reader, LoadOption.OverwriteChanges);
}
break;
case EDtxReaderType.FaircomReaderSafe:
case EDtxReaderType.ReaderSafe:
default:
{
bool columnsBuilt = false;
while (reader.Read())
{
if (columnsBuilt == false)
{
BuildColumnData(dt, reader);
columnsBuilt = true;
}
AddDataRow(dt, reader);
}
}
break;
}
}
break;
}
}
transaction.Commit();
executionTime = DateTime.Now - started;
//Now update parameter inputs with their respective values
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
if (p.Direction != ParameterDirection.Input)
{
paramList[i].OutValue = p.Value;
}
}
}
catch (Exception e)
{
transaction.Rollback();
executionTime = DateTime.Now - started;
HelperFunctions.LogException(e, false);
throw e;
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (bConnOpenedHere)
{
conn.Close();
}
}
return(retv);
}
public override void SetValue(IDbDataParameter parameter, Blarg value)
{
parameter.Value = ((object)value.Value) ?? DBNull.Value;
}
public IList <String> SaveInfo(EstudianteDTO obj)
{
List <String> list = new List <String>();
try
{
using (Conn = new Connection().Conexion)
{
IDbCommand comm = Conn.CreateCommand();
IDbDataParameter dp = comm.CreateParameter();
comm.Connection = Conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "guardarEstudiante";
//AÑADIR PARAMETROS AL PROCEDIMIENTO ALMACENADO
dp = comm.CreateParameter();
dp.ParameterName = "@Id";
dp.Value = obj.Id;
comm.Parameters.Add(dp);
dp = comm.CreateParameter();
dp.ParameterName = "@Nombre";
dp.Value = obj.Nombre;
comm.Parameters.Add(dp);
dp = comm.CreateParameter();
dp.ParameterName = "@Apellido";
dp.Value = obj.Apellido;
comm.Parameters.Add(dp);
dp = comm.CreateParameter();
dp.ParameterName = "@Semestre";
dp.Value = obj.Semestre;
comm.Parameters.Add(dp);
dp = comm.CreateParameter();
dp.ParameterName = "@Documento";
dp.Value = obj.Documento;
comm.Parameters.Add(dp);
dp = comm.CreateParameter();
dp.ParameterName = "@IdMunicipio";
dp.Value = obj.IdMunicipio;
comm.Parameters.Add(dp);
Conn.Open();
IDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
int columns = dr.FieldCount;
while (dr.Read())
{
for (int i = 0; i < columns; i++)
{
list.Add(dr.GetValue(i).ToString().Trim());
}
}
}
}
catch (Exception ex)
{
list.Add(String.Format("Error: {0}", ex.Message));
}
return(list);
}
public override void SetValue(IDbDataParameter parameter, TIdentity value)
{
parameter.Value = value.Id;
}
// Parameters are converted by Microsoft.Data.Sqlite public override void SetValue(IDbDataParameter parameter, T value) => parameter.Value = value;
/// <summary>
/// 解析表达式
/// </summary>
/// <param name="exp"></param>
/// <param name="isRight"></param>
/// <returns></returns>
public string RouteExpressionHandler(Expression exp, SqlOpertionHelper helper, Enum_Opertion opertion, ref List <IDbDataParameter> List_SqlParameter, bool isRight = false)
{
if (exp is BinaryExpression)
{
BinaryExpression be = (BinaryExpression)exp;
//重新拆分树,形成递归
return(BinaryExpressionHandler(be.Left, be.Right, be.NodeType, helper, opertion, ref List_SqlParameter));
}
else if (exp is MemberExpression)
{
MemberExpression mExp = (MemberExpression)exp;
if (isRight)//按表达式右边值
{
var obj = Expression.Lambda(mExp).Compile().DynamicInvoke();
if (obj is Enum)
{
obj = (int)obj;
}
return(obj + "");
}
return(mExp.Member.Name);
}
else if (exp is NewArrayExpression)
{
#region 数组
NewArrayExpression naExp = (NewArrayExpression)exp;
StringBuilder sb = new StringBuilder();
foreach (Expression expression in naExp.Expressions)
{
sb.AppendFormat(",{0}", RouteExpressionHandler(expression, helper, opertion, ref List_SqlParameter));
}
return(sb.Length == 0 ? "" : sb.Remove(0, 1).ToString());
#endregion
}
else if (exp is MethodCallExpression)
{
MethodCallExpression cExp = (MethodCallExpression)exp;
if (isRight)
{
return(Expression.Lambda(exp).Compile().DynamicInvoke() + "");
}
#region 数据库Like方法
if (cExp.Method.Name == "Contains" || cExp.Method.Name.ToLower() == "like")
{
if (cExp.Arguments.Count == 1)
{
var ParmName = cExp.Object.ToString();
var ParmLastName = ParmName.Split('.').Last();
string Search_Value = RouteExpressionHandler(cExp.Arguments[0], helper, opertion, ref List_SqlParameter, true);
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = "%" + Search_Value + "%";
List_SqlParameter.Add(parameter);
return(ParmLastName + " like @" + ParmLastName + Count);
}
else if (cExp.Arguments.Count == 2)
{
MemberExpression ConstantExp = cExp.Arguments[0] as MemberExpression;
string ParmLastName = "";
if (ConstantExp != null)
{
ParmLastName = ConstantExp.Member.Name;
}
else
{
UnaryExpression NewNameExp = cExp.Arguments[0] as UnaryExpression;
MemberExpression proexp = NewNameExp.Operand as MemberExpression;
ParmLastName = proexp.Member.Name;
}
string Search_Value = RouteExpressionHandler(cExp.Arguments[1], helper, opertion, ref List_SqlParameter, true);
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = "%" + Search_Value + "%";
List_SqlParameter.Add(parameter);
return(ParmLastName + " like @" + ParmLastName + Count);
}
}
#endregion
#region 数据库In函数
else if (cExp.Method.Name == "ContainsIn" || cExp.Method.Name.ToLower() == "in")
{
if (cExp.Arguments.Count == 1)
{
var ParmName = cExp.Object.ToString();
var ParmLastName = ParmName.Split('.').Last();
MemberExpression ConstantExp = cExp.Arguments[0] as MemberExpression;
var obj = (List <string>)Expression.Lambda(ConstantExp).Compile().DynamicInvoke();
StringBuilder builder = new StringBuilder();
if (obj != null && obj.Count > 0)
{
builder.Append(ParmLastName).Append(" in (");
foreach (var item in obj)
{
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = item;
List_SqlParameter.Add(parameter);
builder.Append("@").Append(ParmLastName).Append(Count).Append(",");
}
builder.Remove(builder.Length - 1, 1).Append(") ");
}
else
{
builder.Append(ParmLastName).Append(" in (-1)");
}
return(builder.ToString());
}
else if (cExp.Arguments.Count == 2)
{
MemberExpression NameExp = cExp.Arguments[0] as MemberExpression;
string ParmLastName = "";
if (NameExp != null)
{
ParmLastName = NameExp.Member.Name;
}
else
{
UnaryExpression NewNameExp = cExp.Arguments[0] as UnaryExpression;
MemberExpression proexp = NewNameExp.Operand as MemberExpression;
ParmLastName = proexp.Member.Name;
}
MemberExpression ConstantExp = cExp.Arguments[1] as MemberExpression;
var obj = (List <string>)Expression.Lambda(ConstantExp).Compile().DynamicInvoke();
StringBuilder builder = new StringBuilder();
if (obj != null && obj.Count > 0)
{
builder.Append(ParmLastName).Append(" in (");
foreach (var item in obj)
{
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = item;
List_SqlParameter.Add(parameter);
builder.Append("@").Append(ParmLastName).Append(Count).Append(",");
}
builder.Remove(builder.Length - 1, 1).Append(") ");
}
else
{
builder.Append(ParmLastName).Append(" in (-1)");
}
return(builder.ToString());
}
}
#endregion
#region 数据库NotIn函数
else if (cExp.Method.Name == "ContainsNotIn" || cExp.Method.Name.ToLower() == "notin")
{
if (cExp.Arguments.Count == 1)
{
var ParmName = cExp.Object.ToString();
var ParmLastName = ParmName.Split('.').Last();
MemberExpression ConstantExp = cExp.Arguments[0] as MemberExpression;
var obj = (List <string>)Expression.Lambda(ConstantExp).Compile().DynamicInvoke();
StringBuilder builder = new StringBuilder();
if (obj != null && obj.Count > 0)
{
builder.Append(ParmLastName).Append(" not in (");
foreach (var item in obj)
{
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = item;
List_SqlParameter.Add(parameter);
builder.Append("@").Append(ParmLastName).Append(Count).Append(",");
}
builder.Remove(builder.Length - 1, 1).Append(") ");
}
else
{
builder.Append(ParmLastName).Append(" not in (-1)");
}
return(builder.ToString());
}
else if (cExp.Arguments.Count == 2)
{
MemberExpression NameExp = cExp.Arguments[0] as MemberExpression;
string ParmLastName = "";
if (NameExp != null)
{
ParmLastName = NameExp.Member.Name;
}
else
{
UnaryExpression NewNameExp = cExp.Arguments[0] as UnaryExpression;
MemberExpression proexp = NewNameExp.Operand as MemberExpression;
ParmLastName = proexp.Member.Name;
}
MemberExpression ConstantExp = cExp.Arguments[1] as MemberExpression;
var obj = (List <object>)Expression.Lambda(ConstantExp).Compile().DynamicInvoke();
StringBuilder builder = new StringBuilder();
if (obj != null && obj.Count > 0)
{
builder.Append(ParmLastName).Append(" not in (");
foreach (var item in obj)
{
var Count = List_SqlParameter.Where(p => p.ParameterName.Contains("@" + ParmLastName)).Count();
IDbDataParameter parameter = helper.GetParameter();
parameter.ParameterName = "@" + ParmLastName + Count;
parameter.Value = item;
List_SqlParameter.Add(parameter);
builder.Append("@").Append(ParmLastName).Append(Count).Append(",");
}
builder.Remove(builder.Length - 1, 1).Append(") ");
}
else
{
builder.Append(ParmLastName).Append(" not in (-1)");
}
return(builder.ToString());
}
}
#endregion
//在这里解析方法
throw new Exception("暂不支持");
}
else if (exp is ConstantExpression)
{
#region 常量
ConstantExpression cExp = (ConstantExpression)exp;
if (cExp.Value == null)
{
return("null");
}
else
{
SqlParameter parameter = new SqlParameter("cExp", cExp.Value);
return(cExp.Value.ToString());
}
#endregion
}
else if (exp is UnaryExpression)
{
UnaryExpression ue = ((UnaryExpression)exp);
return(RouteExpressionHandler(ue.Operand, helper, opertion, ref List_SqlParameter, isRight));
}
return(null);
}
