I want to use dapper to query over dbf files. In my file example.dbf I have 7 columns:
- Version- string
- CreatedOn- Datetime
- Features- string
- CreatedBy- long
- ModifiedOn- Datetime
- ModifiedBy- long
- IsDeprecated- bool
public class ExampleDBF : BaseAdminPortalClass
{
public string Version { get; set; }
public DateTime? CreatedOn { get; set; }
public string Features { get; set; }
public long? CreatedBy { get; set; }
public DateTime? ModifiedOn { get; set; }
public long? ModifiedBy { get; set; }
public bool IsDeprecated { get; set; }
}
Below is the query for this-
public static string ExampleQuery(AppVersionsFilter objFilter)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.AppendFormat(@"SELECT {1} Version,CreatedOn,Features,IsDeprecated,CreatedBy,ModifiedOn,ModifiedBy,IsDeprecated FROM {0} av",
GetTableName(Table.app_versions, Database.PineStemDB),
objFilter.Pagination ? "SQL_CALC_FOUND_ROWS" : "");
string strCondition = "WHERE";
if (objFilter.Version != null && objFilter.Version.Count > 0)
{
sbQuery.AppendFormat(" {0} Version IN @Version ",strCondition);
strCondition = "AND";
}
if (!string.IsNullOrWhiteSpace(objFilter.SearchTerm))
{
objFilter.SearchTerm.Trim('\'');
objFilter.SearchTerm = "'%" + objFilter.SearchTerm + "%'";
sbQuery.AppendFormat(" {0} (Version LIKE {1} ) ", strCondition, objFilter.SearchTerm);
strCondition = "AND";
}
if(objFilter.IsDeprecated != null)
{
sbQuery.AppendFormat(" {0} IsDeprecated=@IsDeprecated ", strCondition);
strCondition = "AND";
}
// Sorting
objFilter.SortingColumn = string.IsNullOrWhiteSpace(objFilter.SortingColumn) ? "Version" : objFilter.SortingColumn;
sbQuery.AppendFormat(" ORDER BY {0} {1} ", objFilter.SortingColumn, objFilter.SortingOrder);
// Pagination
if (objFilter.Pagination)
{
sbQuery.AppendFormat(" LIMIT {0},{1} ", objFilter.PageNumber, objFilter.PageLimit);
}
return sbQuery.ToString();
}
And Here is the Repository function for better understanding-
public AdminPortalResponse AppVersions(AppVersionsFilter objFilter)
{
AdminPortalResponse objResponse = new AdminPortalResponse();
try
{
objFilter.SetSqlPagingValues();
string strQuery = Queries.Analytics.AppVersions(objFilter);
IList<AppVersion> lstAppVersions = _objDBContext.Database.Query<AppVersion>(strQuery,objFilter).ToList();
objResponse.FillResponse(lstAppVersions);
if (objFilter.Pagination)
{
objResponse.RecordCount = _objDBContext.Database.ExecuteScalar<int>(Queries.Common.RecordCount());
}
}
catch (Exception ex)
{
objResponse.FillResponse(ex);
}
return objResponse;
}
All the columns are ok except the Datetime type columns.
Error parsing column 2 (CreatedOn= - Object).
- I also tried checking the table in DB if it has the same type of Data, and also checked query we are executing. All positive from this side.
- Not sure why I am getting this issue.
- May someone have a solution for this.
