Find custom table name using CustomTableClassName?

This is some code that I have used in the past...seems to work pretty well

//retrieve dataset of customtableitems by passing in the desired classname as a string //where,orderby(strings) //topN(int) DataSet item = CustomTableItemProvider.GetItems(classname, where, null, topN, orderby); //if the datasource isn't empty, try to create customtablitem if (!DataHelper.DataSourceIsEmpty(item)) { //create custom table item from data row CustomTableItem cti = CustomTableItem.New(classname, item.Tables[0].Rows[0]); if (cti != null) { //do something } }//end empty check

Thank you both, I've used the DataClassInfo to get the table name.

DataClassInfo customTableClassInfo = DataClassInfoProvider.GetDataClass(customTableClassName);
theTableNameToCleanUp = customTableClassInfo.ClassTableName;

@Arindam, are you building a query within your code? I'd suggest checking out the query functionality within the UI. I'm sure you'll find it a bit more robust than you know. The nice thing is you can build a standard query to accept, group, where, order by, topN, etc. and if you need to modify your query, you don't have to go to the code to do so.

@Brenden, yes, I have though about it but it seems right to hardcode the query. The method is shared between multiple scheduled tasks and i'd like to keep everything in one place. I was using the CustomTableItem.Delete but it was very very slow, so I had to delete the rows directly.

/// <summary>
/// give it a table name and date, it will delete rows older than the date if DeleteDataBeforeThisDate is true
/// set DeleteDataBeforeThisDate to false to delete rows inserted AFTER the date
/// </summary>
/// <param name="customTableClassName"></param>
/// <param name="TransactionDate"></param>
/// <param name="DeleteDataBeforeThisDate"></param>
private static int DeleteInvalidDataFromTable(string customTableClassName, DateTime TransactionDate, bool DeleteDataBeforeThisDate)
{
    CustomTableItemProvider UselessSlowTableItemProvider = new CustomTableItemProvider();
    string TheSqlQuery = string.Empty;

    DataClassInfo customTableClassInfo = DataClassInfoProvider.GetDataClass(customTableClassName);

    if (DeleteDataBeforeThisDate)
    {                
        TheSqlQuery = string.Format("DELETE FROM {0} WHERE [ItemCreatedWhen] < '{1}'", customTableClassInfo.ClassTableName, TransactionDate.ToString("s"));
    }
    else
    {
        TheSqlQuery = string.Format("DELETE FROM {0} WHERE [ItemCreatedWhen] >= '{1}'", customTableClassInfo.ClassTableName, TransactionDate.ToString("s"));
    }

    var conn = ConnectionHelper.GetConnection();
    return conn.ExecuteNonQuery(TheSqlQuery, new QueryDataParameters() , QueryTypeEnum.SQLQuery, false);
}

We need to import data from a 100mb text file to a custom table accounting around 150,000 rows and 56 columns, this is done via the schedule task, could you suggest a very fast way to insert data to the table?

Many thanks.

You can use queries for that as well. You can specify your parameters as you normally would OR leave the macros in the query i.e.: ##WHERE## and simply pass that parameter to the query. Doing what I'm suggesting is exactly what you're doing although not hard coding the query in code.

Each time you use the built-in methods for a custom table (insert, update, delete), it also creates a record in the event log of the action that happened. So there is overhead with those methods. To get away from that, you can simply create your queries, call them from code and pass your data to them. As I mentioned, the .Delete, .Insert, etc. methods, perform additional checks and queries which is why they typically take longer.

I use scheduled tasks for this on a regular basis with a lot of clients anywhere from a couple hundred rows of data to a million and if you run your scheduler as a service on the server, it works great. If you're using the site to run the scheduler, then you could see some performance issues depending on what else is going on at the time it runs.

You might also consider doing a TRUNCATE to the table vs. just deleting a range and re-importing all the data, could also help with performance.