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); }