/// <summary> /// This method configures oracle dependency /// </summary> private static void AddDependency() { // Change notification previleges must be granted for OracleDBDependency to work // -- run this script // -- 'grant change notification to username' without quotes where username // is to replaced with one being used by the db instance // Notification based DBDependency // JDBC connectionString string key = "Item:1"; string connectionString = System.Configuration.ConfigurationManager.AppSettings["conn-string"]; string commandText = "SELECT PRODUCTID, rowId, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID = 1"; CacheDependency oracleDependency = new OracleCacheDependency(connectionString, commandText); CacheItem cacheItem = new CacheItem("OracleDependentValue"); cacheItem.Dependency = oracleDependency; _cache.Add(key, cacheItem); // Any record that is modified in the database is invalidated in cache and thus the cache item is removed. // To Verify modify the record in the database and uncomment the code below // The code checks if the record is removed form the cache after modification. //if (_cache.Contains(key)) //{ // Console.WriteLine("Oracle dependency did not work as expected. " + "Check your connection string and sql command syntax."); //} //else //{ // Console.WriteLine("Item:1 removed due to oracle dependency."); //} }
private static CacheDependency CreateOracleDepenedency(string connectionString, DbCommand dbCommand) { OracleCacheDependency depenedency = new OracleCacheDependency(connectionString, dbCommand.CommandText); if (dbCommand.Parameters.Count > 0) { foreach (DbParameter parameter in dbCommand.Parameters) { OracleCmdParams dependencyParameter = new OracleCmdParams(); dependencyParameter.Direction = (OracleParameterDirection)((int)parameter.Direction - 1); switch (parameter.Direction) { case ParameterDirection.Input: dependencyParameter.Direction = OracleParameterDirection.Input; break; case ParameterDirection.Output: dependencyParameter.Direction = OracleParameterDirection.Output; break; } dependencyParameter.Value = parameter.Value; depenedency.CommandParams.Add(parameter.ParameterName, dependencyParameter); } } return depenedency; }
public void Create(DependencyCreateArgs args) { DBDependencyCreateArgs arg=(DBDependencyCreateArgs)args; if (args == null) throw new ArgumentException(); OracleConnection conn = (OracleConnection)arg.DbOp.GetConnection(); using (OracleCommand cmd = new OracleCommand(arg.Sql, conn)) { instance = new OracleCacheDependency(cmd); } }
public void Create(DependencyCreateArgs args) { DBDependencyCreateArgs arg = (DBDependencyCreateArgs)args; if (args == null) { throw new ArgumentException(); } OracleConnection conn = (OracleConnection)arg.DbOp.GetConnection(); using (OracleCommand cmd = new OracleCommand(arg.Sql, conn)) { instance = new OracleCacheDependency(cmd); } }
/// <summary> /// /// </summary> /// <param name="command"></param> /// <param name="expHint"></param> /// <param name="beginQuoteIndex"></param> /// <param name="endQuoteIndex"></param> public ExpirationHint CreateDependencyFromString(ref string command, ref byte[] data, string cacheId, ref int beginQuoteIndex, ref int endQuoteIndex, bool isBulkOps, object items, ref int currentXtDep) { bool isInner = false; string interimCommand = null; int interimBeginIndex = 0, interimEndIndex = 0; ExpirationHint expirationHint = null; AggregateExpirationHint aggregateExpiration = null; do { beginQuoteIndex += interimEndIndex; UpdateDelimIndexes(command, '\r', ref beginQuoteIndex, ref endQuoteIndex); if (endQuoteIndex < 0) { break; } interimCommand = command.Substring(beginQuoteIndex + 1, endQuoteIndex - beginQuoteIndex - 1).Remove(0, 1); if (interimCommand == string.Empty) { break; } if (interimCommand.StartsWith("\"")) { endQuoteIndex = beginQuoteIndex; break; } interimBeginIndex = interimEndIndex = 0; if (interimCommand.StartsWith("INNER") && !isInner) { isInner = true; aggregateExpiration = new AggregateExpirationHint(); } else if (interimCommand.StartsWith("FILEDEPENDENCY") || interimCommand.StartsWith("KEYDEPENDENCY") ) { string value = null; DateTime startAfter; ArrayList list = new ArrayList(); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); while (true) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); value = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); int valueBeginIndex = 0, valueEndIndex = 0; if (value.Equals("STARTAFTER")) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); startAfter = new DateTime(Convert.ToInt64(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1))); interimBeginIndex += valueBeginIndex; interimEndIndex += valueEndIndex; break; } else { list.Add(value); } } if (interimCommand.StartsWith("KEYDEPENDENCY")) { expirationHint = new KeyDependency((string[])list.ToArray(typeof(string)), startAfter); } else { expirationHint = new FileDependency((string[])list.ToArray(typeof(string)), startAfter); } } else if (interimCommand.StartsWith("EXTDEPENDENCY")) { if (!isBulkOps) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); int extensibleDepStartIndex = Convert.ToInt32(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); int extensibleDepEndIndex = Convert.ToInt32(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); byte[] value = null; byte[] extensibleDependency = new byte[extensibleDepEndIndex - extensibleDepStartIndex]; value = new byte[extensibleDepStartIndex]; MemoryStream mStream = new MemoryStream(data); mStream.Read(value, 0, extensibleDepStartIndex); mStream.Read(extensibleDependency, 0, extensibleDepEndIndex - extensibleDepStartIndex); mStream.Close(); data = value; expirationHint = (ExtensibleDependency)Serialization.Formatters.CompactBinaryFormatter.FromByteBuffer(extensibleDependency, cacheId); } else { ArrayList userItems = items as ArrayList; UserBinaryObject userBinObj = userItems[currentXtDep++] as UserBinaryObject; if (userBinObj != null) { expirationHint = (ExtensibleDependency)Serialization.Formatters.CompactBinaryFormatter.FromByteBuffer(userBinObj.GetFullObject(), cacheId); } } } else if (interimCommand.StartsWith("SQL7DEPENDENCY") || interimCommand.StartsWith("OLEDBDEPENDENCY") ) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string connectionString = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string cacheKey = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); if (interimCommand.StartsWith("OLEDBDEPENDENCY")) { expirationHint = new OleDbCacheDependency(connectionString, cacheKey); } else { expirationHint = new Sql7CacheDependency(connectionString, cacheKey); } } else if (interimCommand.StartsWith("YUKONDEPENDENCY")) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string connectionString = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string queryString = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string commandType = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); CommandType cmdType = (CommandType)Convert.ToInt32(commandType); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string cmdParamId = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); if (interimCommand.StartsWith("ORACLEDEPENDENCY")) { Hashtable cmdParams = new Hashtable(); if (cmdParamId != string.Empty) { while (true) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); if (interimEndIndex == -1) { break; } string key = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string type = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); Runtime.Dependencies.OracleCmdParamsType oracleType = (Runtime.Dependencies.OracleCmdParamsType)Convert.ToInt32(type); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); object value = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string direction = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); Runtime.Dependencies.OracleParameterDirection oracleParamDirection = (Runtime.Dependencies.OracleParameterDirection)Convert.ToInt32(direction); OracleCommandParams oracleParams = new OracleCommandParams(oracleType, value, oracleParamDirection); cmdParams.Add(key, oracleParams); } } if (cmdParams.Count > 0) { expirationHint = new OracleCacheDependency(connectionString, queryString, cmdType, cmdParams); } else { expirationHint = new OracleCacheDependency(connectionString, queryString, cmdType, new Hashtable()); } } else { Hashtable cmdParams = new Hashtable(); if (cmdParamId != string.Empty) { while (true) { UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); if (interimEndIndex == -1) { break; } string key = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string type = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); SqlDbType sqlType = (SqlDbType)Convert.ToInt32(type); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string direction = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); ParameterDirection paramDirection = (ParameterDirection)Convert.ToInt32(direction); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string dbtype = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); DbType dbType = (DbType)Convert.ToInt32(dbtype); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string cmpOptions = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); SqlCompareOptions compareOptions = (SqlCompareOptions)Convert.ToInt32(cmpOptions); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string srcVer = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); DataRowVersion srcVersion = (DataRowVersion)Convert.ToInt32(srcVer); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); object value = interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1); if (value.ToString() == "#") { value = null; } UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); bool isNullable = Convert.ToBoolean(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); int localeId = Convert.ToInt32(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); int offset = Convert.ToInt32(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); byte precision = Convert.ToByte(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); byte scale = Convert.ToByte(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); int size = Convert.ToInt32(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string sourceColumn = (interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); if (sourceColumn == "#") { sourceColumn = ""; } UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); bool sourceColumnNullMapping = Convert.ToBoolean(interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); object sqlValue = (interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); if (sqlValue.ToString() == "#") { sqlValue = null; } UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string typeName = (interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); if (typeName == "#") { typeName = ""; } UpdateDelimIndexes(interimCommand, '"', ref interimBeginIndex, ref interimEndIndex); string udtTypeName = (interimCommand.Substring(interimBeginIndex + 1, interimEndIndex - interimBeginIndex - 1)); if (udtTypeName == "#") { udtTypeName = ""; } SqlCmdParams sqlParams = new SqlCmdParams(sqlType, value); sqlParams.CmpInfo = compareOptions; sqlParams.Direction = paramDirection; sqlParams.IsNullable = isNullable; sqlParams.LocaleID = localeId; sqlParams.Offset = offset; sqlParams.Precision = precision; sqlParams.Scale = scale; sqlParams.ParamSize = size; sqlParams.SourceColumn = sourceColumn; sqlParams.SourceColumnNullMapping = sourceColumnNullMapping; sqlParams.SqlValue = sqlValue; sqlParams.SrcVersion = srcVersion; sqlParams.TypeName = typeName; sqlParams.UdtName = udtTypeName; cmdParams.Add(key, sqlParams); } } if (cmdParams.Count > 0) { expirationHint = new SqlYukonCacheDependency(connectionString, queryString, cmdType, cmdParams); } else { expirationHint = new SqlYukonCacheDependency(connectionString, queryString, cmdType, new Hashtable()); } } } if (interimCommand != "INNER" && isInner && expirationHint != null) { aggregateExpiration.Add(expirationHint); } } while (endQuoteIndex > -1); return(aggregateExpiration == null ? expirationHint : aggregateExpiration); }
public CacheDependency GetOracleCacheDependency(string query) { OracleCacheDependency dependency; //using (OracleConnection con = new OracleConnection(connectionString)) //{ OracleConnection con = new OracleConnection(connectionString); con.Open(); OracleCommand queryCommand = new OracleCommand(query, con); dependency = new OracleCacheDependency(queryCommand); queryCommand.ExecuteReader(); //} return dependency; }
public Dictionary<string, string> test() { string cacheKey = "deptNOs"; string command = "SELECT ZKID,SBKSDM FROM CW_YB_KSDZ"; var cache = System.Web.HttpContext.Current.Cache; var mapping = new Dictionary<string, string>(); if (cache.Get(cacheKey) != null) { mapping = (Dictionary<string, string>)cache.Get(cacheKey); } else { using (OracleConnection con = new OracleConnection(connectionString)) { con.Open(); //CacheDependency cd = GetOracleCacheDependency(command); OracleCommand queryCommand = new OracleCommand(command, con); OracleCacheDependency cd = new OracleCacheDependency(queryCommand); var reader = queryCommand.ExecuteReader(); while (reader.Read()) { mapping.Add(reader.GetValue(0).ToString(), reader.GetString(1)); } cache.Add(cacheKey, mapping, cd, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); } } return mapping; }
public static ExpirationHint GetExpirationHintObj(Alachisoft.NCache.Common.Protobuf.Dependency dependency, bool resyncOnExpiration, string serializationContext) { AggregateExpirationHint hints = new AggregateExpirationHint(); if (dependency != null && dependency.keyDep != null && dependency.keyDep.Count > 0) { for (int i = 0; i < dependency.keyDep.Count; i++) { Alachisoft.NCache.Caching.AutoExpiration.KeyDependency keyDep = new Alachisoft.NCache.Caching.AutoExpiration.KeyDependency(dependency.keyDep[i].keys.ToArray(), new DateTime(dependency.keyDep[i].startAfter)); hints.Add(keyDep); } } if (dependency != null && dependency.fileDep != null && dependency.fileDep.Count > 0) { for (int i = 0; i < dependency.fileDep.Count; i++) { Alachisoft.NCache.Caching.AutoExpiration.FileDependency fileDep = new Alachisoft.NCache.Caching.AutoExpiration.FileDependency(dependency.fileDep[i].filePaths.ToArray(), new DateTime(dependency.fileDep[i].startAfter)); hints.Add(fileDep); } } if (dependency != null && dependency.oleDbDep != null && dependency.oleDbDep.Count > 0) { for (int i = 0; i < dependency.oleDbDep.Count; i++) { OleDbCacheDependency oleDb = new OleDbCacheDependency(dependency.oleDbDep[i].connectionString, dependency.oleDbDep[i].dbCacheKey); hints.Add(oleDb); } } if (dependency != null && dependency.sql7Dep != null && dependency.sql7Dep.Count > 0) { for (int i = 0; i < dependency.sql7Dep.Count; i++) { Sql7CacheDependency sql7Dep = new Sql7CacheDependency(dependency.sql7Dep[i].connectionString, dependency.sql7Dep[i].dbCacheKey); hints.Add(sql7Dep); } } if (dependency != null && dependency.xtDep != null && dependency.xtDep.Count > 0) { try { for (int i = 0; i < dependency.xtDep.Count; i++) { IFormatter formater = new BinaryFormatter(); byte[] buffer = dependency.xtDep[i].data; object obj = null; using (MemoryStream stream = new MemoryStream(buffer)) { obj = formater.Deserialize(stream); } Alachisoft.NCache.Caching.AutoExpiration.ExtensibleDependency xtDep = new Alachisoft.NCache.Caching.AutoExpiration.ExtensibleDependency( (Runtime.Dependencies.ExtensibleDependency)obj); hints.Add(xtDep); } } catch (SerializationException ex) { throw new OperationFailedException(ex.Message); } } if (dependency != null && dependency.oracleDep != null && dependency.oracleDep.Count > 0) { for (int i = 0; i < dependency.oracleDep.Count; i++) { Hashtable parameters = new Hashtable(); for (int pc = 0; pc < dependency.oracleDep[i].param.Count; pc++) { OracleCommandParams commandParam = new OracleCommandParams((Runtime.Dependencies.OracleCmdParamsType)dependency.oracleDep[i].param[pc].cmdParam.dbType, dependency.oracleDep[i].param[pc].cmdParam.value, (Runtime.Dependencies.OracleParameterDirection)dependency.oracleDep[i].param[pc].cmdParam.direction); parameters.Add(dependency.oracleDep[i].param[pc].key, commandParam); } OracleCacheDependency oraDep = new OracleCacheDependency(dependency.oracleDep[i].connectionString, dependency.oracleDep[i].query, (CommandType)dependency.oracleDep[i].commandType, parameters); hints.Add(oraDep); } } if (dependency != null && dependency.yukonDep != null && dependency.yukonDep.Count > 0) { for (int i = 0; i < dependency.yukonDep.Count; i++) { Hashtable parameters = new Hashtable(); for (int pc = 0; pc < dependency.yukonDep[i].param.Count; pc++) { YukonCommandParam yukonParam = dependency.yukonDep[i].param[pc].cmdParam; SqlCmdParams commandParam = new SqlCmdParams((SqlDbType)yukonParam.dbType, yukonParam.value); commandParam.CmpInfo = (System.Data.SqlTypes.SqlCompareOptions)yukonParam.cmpOptions; commandParam.Direction = (ParameterDirection)yukonParam.direction; commandParam.IsNullable = yukonParam.isNullable; commandParam.LocaleID = yukonParam.localeId; commandParam.Offset = yukonParam.offset; commandParam.Precision = (byte)yukonParam.precision; commandParam.Scale = (byte)yukonParam.scale; commandParam.ParamSize = yukonParam.size; commandParam.SourceColumn = yukonParam.sourceColumn; commandParam.SourceColumnNullMapping = yukonParam.sourceColumnNull; commandParam.SqlValue = yukonParam.sqlValue; commandParam.SrcVersion = (DataRowVersion)yukonParam.version; commandParam.DbType = (SqlDbType)yukonParam.typeId; commandParam.TypeName = yukonParam.typeName; commandParam.UdtName = yukonParam.udtTypeName; if (!yukonParam.nullValueProvided) { string val = yukonParam.value as string; if (val != null) { if (commandParam.DbType == SqlDbType.Binary || commandParam.DbType == SqlDbType.VarBinary || commandParam.DbType == SqlDbType.Image || commandParam.DbType == SqlDbType.Timestamp) { System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding(); commandParam.Value = encoding.GetBytes(val); } else if (commandParam.DbType == SqlDbType.DateTime || commandParam.DbType == SqlDbType.DateTime2 || commandParam.DbType == SqlDbType.Date || commandParam.DbType == SqlDbType.SmallDateTime) { commandParam.Value = new DateTime(Convert.ToInt64(val)); } else if (commandParam.DbType == SqlDbType.Time) { commandParam.Value = new TimeSpan(Convert.ToInt64(val)); } else if (commandParam.DbType == SqlDbType.DateTimeOffset) { string[] dateOffset = val.Split(new char[] { ',' }); commandParam.Value = new DateTimeOffset(new DateTime(Convert.ToInt64(dateOffset[0])), TimeSpan.FromMinutes(Convert.ToInt32(dateOffset[1]))); } else if (commandParam.DbType == SqlDbType.Money || commandParam.DbType == SqlDbType.SmallMoney || commandParam.DbType == SqlDbType.Decimal) { commandParam.Value = Convert.ToDecimal(val); } else if (commandParam.DbType == SqlDbType.Int) { commandParam.Value = Convert.ToInt32(val); } else if (commandParam.DbType == SqlDbType.BigInt) { commandParam.Value = Convert.ToInt64(val); } else if (commandParam.DbType == SqlDbType.SmallInt) { commandParam.Value = Convert.ToInt16(val); } else if (commandParam.DbType == SqlDbType.Real) { commandParam.Value = Convert.ToSingle(val); } else if (commandParam.DbType == SqlDbType.UniqueIdentifier) { commandParam.Value = System.Data.SqlTypes.SqlGuid.Parse(val); } else if (commandParam.DbType == SqlDbType.TinyInt) { commandParam.Value = Convert.ToByte(val); } else if (commandParam.DbType == SqlDbType.Float) { commandParam.Value = Convert.ToDouble(val); } else if (commandParam.DbType == SqlDbType.Bit) { commandParam.Value = Convert.ToBoolean(val); } else { commandParam.Value = val; } } else { commandParam.Value = DBNull.Value; } } else { commandParam.Value = DBNull.Value; } parameters.Add(dependency.yukonDep[i].param[pc].key, commandParam); } SqlYukonCacheDependency yukonDep = new SqlYukonCacheDependency(dependency.yukonDep[i].connectionString, dependency.yukonDep[i].query, (CommandType)dependency.yukonDep[i].commandType, parameters); hints.Add(yukonDep); } } if (dependency != null && dependency.NosDep != null && dependency.NosDep.Count > 0) { for (int i = 0; i < dependency.NosDep.Count; i++) { Hashtable parameters = new Hashtable(); for (int pc = 0; pc < dependency.NosDep[i].param.Count; pc++) { parameters.Add(dependency.NosDep[i].param[pc].key, dependency.NosDep[i].param[pc].value); } NosDBCacheDependency oraDep = new NosDBCacheDependency(dependency.NosDep[i].connectionString, dependency.NosDep[i].query, dependency.NosDep[i].timeout, parameters); hints.Add(oraDep); } } if (resyncOnExpiration) { hints.SetBit(ExpirationHint.NEEDS_RESYNC); } IList <ExpirationHint> expHints = hints.Hints; if (expHints.Count == 0) { return(null); } if (expHints.Count == 1) { return(expHints[0]); } return(hints); }
public string GetProject(string hospitalCode, string settlementType, string projectType) { string controlCode = ""; string cacheKey = "projectCode_control"; var cache = HttpContext.Current.Cache; var mapping_control = new List<FeesProject>(); var mapping = new List<FeesProject>(); if (cache.Get(cacheKey) != null) { mapping_control = (List<FeesProject>)cache.Get(cacheKey); } else { using (OracleConnection con = new OracleConnection(connectionString)) { con.Open(); string sql = "select sbdm,jslx,lb,yydm from cw_yb_tydzb"; //select m.ybbm,m.jslx,t.lb,t.yydm from cw_yb_ypml m,( union all select m.ybbm,m.jslx,t.lb,t.yydm from cw_yb_zlml m,(select * from cw_yb_tydzb where lb='zl' ) t where m.zxbm=t.sbdm"; OracleCommand queryCommand = new OracleCommand(sql, con); OracleCacheDependency dependency = new OracleCacheDependency(queryCommand); var reader = queryCommand.ExecuteReader(); while (reader.Read()) { FeesProject feesProject = new FeesProject(); feesProject.ControlCode = reader.GetValue(0).ToString(); feesProject.SettlementType = reader.GetValue(1).ToString(); feesProject.ProjectType = reader.GetValue(2).ToString(); feesProject.ProjectCode = reader.GetValue(3).ToString(); mapping_control.Add(feesProject); } cache.Add(cacheKey, mapping_control, dependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); } } string command = ""; if (projectType == "yp") { cacheKey = "projectCode_medicinal"; command = "select ybbm,jslx,'yp' lb,zxbm from cw_yb_ypml"; } else { cacheKey = "projectCode_zl"; command = "select ybbm,jslx,'zl' lb,zxbm from cw_yb_zlml"; } if (cache.Get(cacheKey) != null) { mapping = (List<FeesProject>)cache.Get(cacheKey); } else { using (OracleConnection con = new OracleConnection(connectionString)) { con.Open(); OracleCommand queryCommand = new OracleCommand(command, con); OracleCacheDependency dependency = new OracleCacheDependency(queryCommand); var reader = queryCommand.ExecuteReader(); while (reader.Read()) { FeesProject feesProject = new FeesProject(); feesProject.ControlCode = reader.GetValue(0).ToString(); feesProject.SettlementType = reader.GetValue(1).ToString(); feesProject.ProjectType = reader.GetValue(2).ToString(); feesProject.ProjectCode = reader.GetValue(3).ToString(); mapping.Add(feesProject); } cache.Add(cacheKey, mapping, dependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); } } FeesProject project = mapping_control.Find(p => p.ProjectType == projectType && p.ProjectCode == hospitalCode && p.SettlementType == settlementType); if (project != null) { FeesProject projectCode = mapping.Find(p => p.ProjectCode == project.ControlCode && p.SettlementType == settlementType); if (projectCode != null) { controlCode = projectCode.ControlCode; } } return controlCode; }
public Dictionary<string, string> GetMapping(string cacheKey, string command) { var cache = HttpContext.Current.Cache; var mapping = new Dictionary<string, string>(); if (cache.Get(cacheKey) != null) { mapping = (Dictionary<string, string>)cache.Get(cacheKey); } else { using (OracleConnection con = new OracleConnection(connectionString)) { con.Open(); OracleCommand queryCommand = new OracleCommand(command, con); OracleCacheDependency dependency = new OracleCacheDependency(queryCommand); var reader = queryCommand.ExecuteReader(); while (reader.Read()) { if (!mapping.ContainsKey(reader.GetValue(0).ToString())) { mapping.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString()); } } cache.Add(cacheKey, mapping, dependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); } } return mapping; }
public string GetDoctorCode(string doctorCode, string unitCode, string externalCode) { //接口代码 string interfaceCode = ""; if (unitCode != "" && externalCode != "") { //如果单位代码以A打头,则表示居民医保;如果是Y打头,则表示职工医保 if (unitCode.Substring(0, 1) == "A") { interfaceCode = "xcjm"; } else if (unitCode.Substring(0, 1) == "Y") { interfaceCode = "xczg"; } //如果外部编号不包含“-”,则表示本级,如果包含,则表示市内或者异地,如果是3306打头的是sn,否则认为是yd if (externalCode.IndexOf('-') < 0) { interfaceCode += "_bj"; } else if (externalCode.Substring(0, 4) == "3306") { interfaceCode += "_sn"; } else { interfaceCode += "_yd"; } string cacheKey = "doctorCode"; var cache = HttpContext.Current.Cache; var mapping = new List<Doctor>(); if (cache.Get(cacheKey) != null) { mapping = (List<Doctor>)cache.Get(cacheKey); } else { using (OracleConnection con = new OracleConnection(connectionString)) { con.Open(); string sql = "Select sbysdm,ysid,jkdm from cw_yb_ysxx"; OracleCommand queryCommand = new OracleCommand(sql, con); OracleCacheDependency dependency = new OracleCacheDependency(queryCommand); var reader = queryCommand.ExecuteReader(); while (reader.Read()) { Doctor doctor = new Doctor(); doctor.DoctorCode = reader.GetValue(0).ToString(); doctor.DoctorID = reader.GetValue(1).ToString(); doctor.InterfaceCode = reader.GetValue(2).ToString(); mapping.Add(doctor); } cache.Add(cacheKey, mapping, dependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); } } Doctor currentDoctor = mapping.Find(p => p.DoctorID == doctorCode && p.InterfaceCode == interfaceCode); if (currentDoctor != null) { doctorCode = currentDoctor.DoctorCode; } } return doctorCode; }
public static CacheDependency GetCacheDependency(Dependency dependency) { CacheDependency cacheDependency = null; if (dependency == null) { return(null); } if (dependency.keyDep.Count > 0) { for (int i = 0; i < dependency.keyDep.Count; i++) { AddToDependency(ref cacheDependency, new Runtime.Dependencies.KeyDependency( dependency.keyDep[i].keys.ToArray(), new DateTime(dependency.keyDep[i].startAfter))); } } if (dependency.fileDep.Count > 0) { for (int i = 0; i < dependency.fileDep.Count; i++) { AddToDependency(ref cacheDependency, new Runtime.Dependencies.FileDependency( dependency.fileDep[i].filePaths.ToArray(), new DateTime(dependency.fileDep[i].startAfter))); } } if (dependency.oleDbDep.Count > 0) { for (int i = 0; i < dependency.oleDbDep.Count; i++) { AddToDependency(ref cacheDependency, new CacheDependency(null, null, DBDependencyFactory.CreateOleDbCacheDependency( dependency.oleDbDep[i].connectionString, dependency.oleDbDep[i].dbCacheKey))); } } if (dependency.sql7Dep.Count > 0) { for (int i = 0; i < dependency.sql7Dep.Count; i++) { AddToDependency(ref cacheDependency, new CacheDependency(null, null, DBDependencyFactory.CreateSqlCacheDependency( dependency.sql7Dep[i].connectionString, dependency.sql7Dep[i].dbCacheKey))); } } if (dependency.oracleDep.Count > 0) { for (int i = 0; i < dependency.oracleDep.Count; i++) { Dictionary <string, OracleCmdParams> parameters = new Dictionary <string, OracleCmdParams>(); for (int pc = 0; pc < dependency.oracleDep[i].param.Count; pc++) { OracleCmdParams commandParam = new OracleCmdParams(); commandParam.Direction = (OracleParameterDirection)dependency.oracleDep[i].param[pc].cmdParam.direction; commandParam.Type = (OracleCmdParamsType)dependency.oracleDep[i].param[pc].cmdParam.dbType; commandParam.Value = dependency.oracleDep[i].param[pc].cmdParam.value; parameters.Add(dependency.oracleDep[i].param[pc].key, commandParam); } OracleCacheDependency oraDep = new OracleCacheDependency(dependency.oracleDep[i].connectionString, dependency.oracleDep[i].query, (OracleCommandType)dependency.oracleDep[i].commandType, parameters); AddToDependency(ref cacheDependency, oraDep); } } if (dependency.yukonDep.Count > 0) { for (int i = 0; i < dependency.yukonDep.Count; i++) { Dictionary <string, SqlCmdParams> parameters = new Dictionary <string, SqlCmdParams>(); for (int pc = 0; pc < dependency.yukonDep[i].param.Count; pc++) { YukonCommandParam yukonParam = dependency.yukonDep[i].param[pc].cmdParam; SqlCmdParams commandParam = new SqlCmdParams(); commandParam.CompareInfo = (SqlCmpOptions)yukonParam.cmpOptions; commandParam.Direction = (SqlParamDirection)yukonParam.direction; commandParam.IsNullable = yukonParam.isNullable; commandParam.LocaleID = yukonParam.localeId; commandParam.Offset = yukonParam.offset; commandParam.Precision = (byte)yukonParam.precision; commandParam.Scale = (byte)yukonParam.scale; commandParam.Size = yukonParam.size; commandParam.SourceColumn = yukonParam.sourceColumn; commandParam.SourceColumnNullMapping = yukonParam.sourceColumnNull; commandParam.SqlValue = yukonParam.sqlValue; commandParam.Type = (CmdParamsType)yukonParam.typeId; commandParam.TypeName = yukonParam.typeName; commandParam.UdtTypeName = yukonParam.udtTypeName; if (!yukonParam.nullValueProvided) { string val = yukonParam.value as string; if (val != null) { if (commandParam.Type == CmdParamsType.Binary || commandParam.Type == CmdParamsType.VarBinary || /*commandParam.Type == CmdParamsType.Image ||*/ commandParam.Type == CmdParamsType.Timestamp) { System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding(); commandParam.Value = encoding.GetBytes(val); } else if (commandParam.Type == CmdParamsType.DateTime || commandParam.Type == CmdParamsType.DateTime2 || commandParam.Type == CmdParamsType.Date || commandParam.Type == CmdParamsType.SmallDateTime) { commandParam.Value = new DateTime(Convert.ToInt64(val)); } else if (commandParam.Type == CmdParamsType.Time) { commandParam.Value = new TimeSpan(Convert.ToInt64(val)); } else if (commandParam.Type == CmdParamsType.DateTimeOffset) { string[] dateOffset = val.Split(new char[] { ',' }); commandParam.Value = new DateTimeOffset(new DateTime(Convert.ToInt64(dateOffset[0])), TimeSpan.FromMinutes(Convert.ToInt32(dateOffset[1]))); } else if (commandParam.Type == CmdParamsType.Money || commandParam.Type == CmdParamsType.SmallMoney || commandParam.Type == CmdParamsType.Decimal) { commandParam.Value = Convert.ToDecimal(val); } else if (commandParam.Type == CmdParamsType.Int) { commandParam.Value = Convert.ToInt32(val); } else if (commandParam.Type == CmdParamsType.BigInt) { commandParam.Value = Convert.ToInt64(val); } else if (commandParam.Type == CmdParamsType.SmallInt) { commandParam.Value = Convert.ToInt16(val); } else if (commandParam.Type == CmdParamsType.Real) { commandParam.Value = Convert.ToSingle(val); } else if (commandParam.Type == CmdParamsType.UniqueIdentifier) { commandParam.Value = new Guid(val); } else if (commandParam.Type == CmdParamsType.TinyInt) { commandParam.Value = Convert.ToByte(val); } else if (commandParam.Type == CmdParamsType.Float) { commandParam.Value = Convert.ToDouble(val); } else if (commandParam.Type == CmdParamsType.Bit) { commandParam.Value = Convert.ToBoolean(val); } else { commandParam.Value = val; } } } parameters.Add(dependency.yukonDep[i].param[pc].key, commandParam); } SqlCacheDependency yukonDep = new SqlCacheDependency(dependency.yukonDep[i].connectionString, dependency.yukonDep[i].query, (SqlCommandType)dependency.yukonDep[i].commandType, parameters); AddToDependency(ref cacheDependency, yukonDep); } } if (dependency.NosDep.Count > 0) { for (int i = 0; i < dependency.NosDep.Count; i++) { Dictionary <string, object> parameters = new Dictionary <string, object>(); NosDbDependency nosDependency = dependency.NosDep[i]; for (int pc = 0; pc < nosDependency.param.Count; pc++) { parameters.Add(nosDependency.param[pc].key, nosDependency.param[pc].value); } NosDBDependency NosDep = new NosDBDependency(nosDependency.connectionString, nosDependency.query, parameters, nosDependency.timeout); AddToDependency(ref cacheDependency, NosDep); } } return(cacheDependency); }