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