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