public static void AddNullableParam(OleDbParameterCollection parameters, long?value, string paramName)
{
if (value == null)
{
parameters.AddWithValue(paramName, DBNull.Value);
}
else
{
parameters.AddWithValue(paramName, value);
}
}
private int GetCountOfSharedState(string path)
{
string getSharedStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
" WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
getSharedStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
OleDbParameter parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
command.CommandText = getSharedStateCount;
object result = command.ExecuteScalar();
if ((result != null) && (result is Int32))
{
count = (Int32)result;
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
public static OleDbParameterCollection CreateParameterCollection(List <KeyValuePair <string, object> > parameters)
{
OleDbParameterCollection pc = OleDAL.Connection.CreateCommand().Parameters;
foreach (KeyValuePair <string, object> para in parameters)
{
pc.AddWithValue(para.Key, para.Value);
}
return(pc);
}
void SetParameterValue(OleDbParameterCollection Params, object value, OleDbType type)
{
if (value != null)
{
Params.Add("?", type).Value = value;
}
else
{
Params.AddWithValue("?", DBNull.Value);
}
}
public void AddWithValueTest()
{
OleDbCommand command = new OleDbCommand();
OleDbParameterCollection parameters = command.Parameters;
// Test with string
OleDbParameter parameter = parameters.AddWithValue("parameterName", "parameterValue");
Assert.AreEqual("parameterValue", parameter.Value);
Assert.AreEqual("parameterName", parameter.ParameterName);
Assert.AreEqual(DbType.AnsiString, parameter.DbType);
Assert.AreEqual(OleDbType.VarChar, parameter.OleDbType);
Assert.AreEqual(1, parameters.Count);
Assert.AreEqual(parameter, parameters[0]);
}
private PersonalizationStateInfoCollection FindSharedState(string path,
int pageIndex,
int pageSize,
out int totalRecords)
{
const string findSharedState =
"SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" +
" FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
" WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
const string orderBy = " ORDER BY Paths.Path ASC";
const string findUserState =
"SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Paths Paths" +
" WHERE PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Paths.Path LIKE @Path";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
OleDbDataReader reader = null;
totalRecords = 0;
try {
try {
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand(findSharedState, connection);
OleDbParameterCollection parameters = command.Parameters;
OleDbParameter parameter;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
command.CommandText += " AND Paths.Path LIKE @Path";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
command.CommandText += orderBy;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
long recordCount = 0;
long lBound = pageIndex * pageSize;
long uBound = lBound + pageSize;
while (reader.Read())
{
recordCount++;
if (recordCount <= lBound || recordCount > uBound)
{
continue;
}
string returnedPath = reader.GetString(0);
DateTime lastUpdatedDate = reader.GetDateTime(1);
int size = reader.GetInt32(2);
// Create temp info since we need to retrieve the corresponding personalization size and count later
stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1));
}
totalRecords = (int)recordCount;
// We need to close the reader in order to make other queries
reader.Close();
command = new OleDbCommand(findUserState, connection);
parameters = command.Parameters;
parameters.AddWithValue("ApplicationId", appId);
parameter = parameters.Add("Path", OleDbType.WChar);
PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();
foreach (PersonalizationStateInfo stateInfo in stateInfoCollection)
{
parameter.Value = stateInfo.Path;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
int sizeOfPersonalizations = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture);
int countOfPersonalizations = reader.GetInt32(1);
reader.Close();
sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
stateInfo.Path, stateInfo.LastUpdatedDate,
stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations));
}
return(sharedStateInfoCollection);
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
if (reader != null)
{
reader.Close();
}
}
}
catch {
throw;
}
}
public override int ResetUserState(string path, DateTime userInactiveSinceDate)
{
path = PersonalizationProviderHelper.CheckAndTrimString(path, "path", false, MaxStringLength);
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try {
try {
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
// Special note: OleDbProvider requires the parameters to be added
// in the same order as appearing in the query text.
string getDeleteUserStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Users.LastActivityDate <= @InactiveSinceDate";
string deleteUserState =
"DELETE FROM aspnet_PagePersonalizationPerUser" +
" WHERE Id IN (SELECT PerUser.Id " +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Users.LastActivityDate <= @InactiveSinceDate";
// Get the count of records that would be deleted
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
OleDbParameter parameter;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
// Note: OleDb provider does not handle datetime that has non-
// zero millisecond, so it needs to be rounded up.
parameter = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
parameter.Value = new DateTime(userInactiveSinceDate.Year, userInactiveSinceDate.Month, userInactiveSinceDate.Day,
userInactiveSinceDate.Hour, userInactiveSinceDate.Minute, userInactiveSinceDate.Second);
if (path != null)
{
const string pathParamQueryText = " AND Paths.Path = @Path";
getDeleteUserStateCount += pathParamQueryText;
deleteUserState += pathParamQueryText;
parameters.AddWithValue("Path", path);
}
deleteUserState += ")";
command.CommandText = getDeleteUserStateCount;
object lookupResult = command.ExecuteScalar();
if ((lookupResult != null) && (lookupResult is Int32))
{
count = (Int32)lookupResult;
if (count > 0)
{
// Do the actual deletion
command.CommandText = deleteUserState;
command.ExecuteNonQuery();
}
}
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch {
throw;
}
return(count);
}
private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username)
{
string getUserStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try {
try {
OleDbParameter parameter;
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
int appId = GetApplicationID(connectionHolder);
parameter = parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
getUserStateCount += " AND Paths.Path LIKE @Path";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
if (username != null)
{
getUserStateCount += " AND Users.UserName LIKE @UserName";
parameter = parameters.Add("UserName", OleDbType.WChar);
parameter.Value = username;
}
if (inactiveSinceDate != DateTime.MinValue)
{
getUserStateCount += " AND Users.LastActivityDate <= @InactiveSinceDate";
// Note: OleDb provider does not handle datetime that has non-
// zero millisecond, so it needs to be rounded up.
parameter = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
}
command.CommandText = getUserStateCount;
object result = command.ExecuteScalar();
if ((result != null) && (result is Int32))
{
count = (Int32)result;
}
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch {
throw;
}
return(count);
}
private PersonalizationStateInfoCollection FindUserState(string path,
DateTime inactiveSinceDate,
string username,
int pageIndex,
int pageSize,
out int totalRecords)
{
const string findUserState =
"SELECT Paths.Path, PerUser.LastUpdatedDate, LEN(PerUser.PageSettings), Users.UserName, Users.LastActivityDate" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId";
const string orderBy = " ORDER BY Paths.Path ASC, Users.UserName ASC";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
OleDbDataReader reader = null;
totalRecords = 0;
try {
try {
OleDbParameter parameter;
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
command.CommandText = findUserState;
if (inactiveSinceDate != DateTime.MinValue)
{
command.CommandText += " AND Users.LastActivityDate <= @InactiveSinceDate";
// Note: OleDb provider does not handle datetime that has non-
// zero millisecond, so it needs to be rounded up.
parameter = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
}
if (path != null)
{
command.CommandText += " AND Paths.Path LIKE @Path";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
if (username != null)
{
command.CommandText += " AND Users.UserName LIKE @UserName";
parameter = parameters.Add("UserName", OleDbType.WChar);
parameter.Value = username;
}
command.CommandText += orderBy;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
long recordCount = 0;
long lBound = pageIndex * pageSize;
long uBound = lBound + pageSize;
while (reader.Read())
{
recordCount++;
if (recordCount <= lBound || recordCount > uBound)
{
continue;
}
string returnedPath = reader.GetString(0);
DateTime lastUpdatedDate = reader.GetDateTime(1);
int size = reader.GetInt32(2);
string returnedUsername = reader.GetString(3);
DateTime lastActivityDate = reader.GetDateTime(4);
stateInfoCollection.Add(new UserPersonalizationStateInfo(
returnedPath, lastUpdatedDate,
size, returnedUsername, lastActivityDate));
}
totalRecords = (int)recordCount;
return(stateInfoCollection);
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
if (reader != null)
{
reader.Close();
}
}
}
catch {
throw;
}
}
public void OleDbParameterCollection_MultipleScenarios_Success()
{
OleDbParameterCollection opc = command.Parameters;
Assert.True(opc.Count == 0);
Assert.False(opc.IsReadOnly);
Assert.False(opc.IsFixedSize);
Assert.False(opc.IsSynchronized);
Assert.Throws <IndexOutOfRangeException>(() => opc[0].ParameterName);
Assert.Throws <IndexOutOfRangeException>(() => opc["@p1"].ParameterName);
Assert.Throws <ArgumentNullException>(() => opc.Add(null));
opc.Add((object)new OleDbParameter());
opc.Add((object)new OleDbParameter());
Collections.IEnumerator enm = opc.GetEnumerator();
Assert.True(enm.MoveNext());
Assert.Equal("Parameter1", ((OleDbParameter)enm.Current).ParameterName);
Assert.True(enm.MoveNext());
Assert.Equal("Parameter2", ((OleDbParameter)enm.Current).ParameterName);
opc.Add(new OleDbParameter(null, null));
opc.Add(null, OleDbType.Integer, 0, null);
Assert.Equal("Parameter4", opc["Parameter4"].ParameterName);
opc.Add(new OleDbParameter("Parameter5", OleDbType.LongVarWChar, 20));
opc.Add(new OleDbParameter(null, OleDbType.WChar, 20, "a"));
opc.RemoveAt(opc[3].ParameterName);
Assert.Equal(-1, opc.IndexOf(null));
Assert.False(opc.Contains(null));
Assert.Throws <IndexOutOfRangeException>(() => opc.RemoveAt(null));
OleDbParameter p = opc[0];
Assert.Throws <ArgumentException>(() => opc.Add((object)p));
Assert.Throws <ArgumentException>(() => new OleDbCommand().Parameters.Add(p));
Assert.Throws <ArgumentNullException>(() => opc.Remove(null));
string pname = p.ParameterName;
p.ParameterName = pname;
p.ParameterName = pname.ToUpper();
p.ParameterName = pname.ToLower();
p.ParameterName = "@p1";
p.ParameterName = pname;
opc.Clear();
opc.Add(p);
opc.Clear();
opc.AddWithValue("@p1", null);
Assert.Equal(-1, opc.IndexOf(p.ParameterName));
opc[0] = p;
Assert.Equal(0, opc.IndexOf(p.ParameterName));
Assert.True(opc.Contains(p.ParameterName));
Assert.True(opc.Contains(opc[0]));
opc[0] = p;
opc[p.ParameterName] = new OleDbParameter(p.ParameterName, null);
opc[p.ParameterName] = (OleDbParameter)OleDbFactory.Instance.CreateParameter();
opc.RemoveAt(0);
new OleDbCommand().Parameters.Clear();
new OleDbCommand().Parameters.CopyTo(new object[0], 0);
Assert.False(new OleDbCommand().Parameters.GetEnumerator().MoveNext());
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Add(0));
Assert.Throws <ArgumentNullException>(() => new OleDbCommand().Parameters.AddRange(null));
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Insert(0, 0));
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Remove(0));
Assert.Throws <ArgumentException>(() => opc.Remove(new OleDbParameter()));
}
