/// <summary>
/// Converts a SqlCeDataReader to a DataSet
/// <param name='reader'>
/// SqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(SqlCeDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
private ProfileInfoCollection GetProfilesForQuery(SqlCeParameter[] args, ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("PageIndex must be greater than -1", "pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("PageSize must be greater than 0", "pageSize");
}
long lowerBound = pageIndex * pageSize;
long upperBound = (long)pageSize - 1 + lowerBound;
if (upperBound > Int32.MaxValue)
{
throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.", "pageIndex and pageSize");
}
SqlCeDataReader reader = null;
totalRecords = 0;
try
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(
@"
SELECT
aspnet_Users.UserName,
aspnet_Users.IsAnonymous,
aspnet_Users.LastActivityDate,
aspnet_Profile.LastUpdatedDate,
DATALENGTH(aspnet_Profile.PropertyNames) + DATALENGTH(aspnet_Profile.PropertyValuesString) + DATALENGTH(aspnet_Profile.PropertyValuesBinary)
FROM
aspnet_Users,
aspnet_Profile
WHERE
aspnet_Users.UserId = aspnet_Profile.UserId
AND ApplicationId = @ApplicationId
AND
(
@ProfileAuthOptions = 2
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)"
, conn))
{
cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", SqlDbType.Int, (int)authenticationOption));
cmd.Parameters.Add(CreateInputParam("@PageLowerBound", SqlDbType.Int, lowerBound));
cmd.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize));
foreach (SqlCeParameter arg in args)
{
cmd.Parameters.Add(arg);
switch (arg.ParameterName)
{
case "@InactiveSinceDate":
cmd.CommandText += " AND (@InactiveSinceDate IS NULL OR aspnet_Users.LastActivityDate <= @InactiveSinceDate)";
break;
case "@UserNameToMatch":
cmd.CommandText += " AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))";
break;
}
}
// append paging
cmd.CommandText += " ORDER BY aspnet_Users.Username OFFSET @PageLowerBound ROWS FETCH NEXT @PageSize ROWS ONLY";
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
ProfileInfoCollection profiles = new ProfileInfoCollection();
while (reader.Read())
{
string username;
DateTime dtLastActivity, dtLastUpdated;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
int size = reader.GetInt32(4);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
}
totalRecords = profiles.Count;
if (reader.NextResult())
{
if (reader.Read())
{
totalRecords = reader.GetInt32(0);
}
}
return(profiles);
}
}
}
catch
{
throw;
}
}