public DataTable GetDataTableWithPaging(string Tablename, int v_PagingStartRecord, int v_PagingInterval, out int r_result, bool v_HasHeader = true, int v_RowsToSkip = 0)
{
if (SchemaTable == null)
{
getSchemaTable();
}
using (OleDbConnection OleDBCon = new OleDbConnection(ConnectionString))
{
if (OleDBCon.State != ConnectionState.Open)
{
OleDBCon.Open();
}
//schRow["TABLE_NAME"].ToString().Trim('\'');
try
{
string strcommand = string.Empty;
if (m_SheetsSpecifications.Where(x => x.StartsWith(Tablename)).Count() > 0)
{
strcommand = "SELECT * , \"" + Tablename + "\" AS [Sys_Sheetname] FROM [" + m_SheetsSpecifications.Where(x => x.StartsWith(Tablename)).FirstOrDefault() + "]";
}
else
{
strcommand = "SELECT * , \"" + Tablename + "\" AS [Sys_Sheetname] FROM [" + Tablename + "]";
}
using (OleDbCommand cmd = new OleDbCommand(strcommand, OleDBCon))
{
DataTable dtTable = new DataTable(RemoveUnusedChars(Tablename));
cmd.CommandType = CommandType.Text;
using (OleDbDataAdapter daGetDataFromSheet = new OleDbDataAdapter(cmd))
{
try
{
daGetDataFromSheet.FillSchema(dtTable, SchemaType.Source);
foreach (DataColumn dCol in dtTable.Columns)
{
if (dCol.DataType != typeof(System.String))
{
dCol.DataType = typeof(System.String);
}
}
r_result = daGetDataFromSheet.Fill(v_PagingStartRecord, v_PagingInterval, dtTable);
if (dtTable.Rows.Count <= v_RowsToSkip)
{
return(null);
}
}
catch (Exception ex)
{
if (ex.Message == "Too many fields defined.")
{
try
{
cmd.CommandText = "SELECT * FROM [" + Tablename + "A1:IU65536]";
daGetDataFromSheet.FillSchema(dtTable, SchemaType.Source);
foreach (DataColumn dCol in dtTable.Columns)
{
if (dCol.DataType != typeof(System.String))
{
dCol.DataType = typeof(System.String);
}
}
r_result = daGetDataFromSheet.Fill(v_PagingStartRecord, v_PagingInterval, dtTable);;
if (dtTable.Rows.Count <= RowsToSkip)
{
return(null);
}
}
catch (Exception exc)
{
{ r_result = 0; return(null); }
}
}
else
// Deleted Sheet
{
r_result = 0; return(null);
}
}
if (RowsToSkip > 0)
{
List <DataRow> lstRows = dtTable.Select().Take(RowsToSkip).ToList();
string strExtraFieldsString = string.Empty;
foreach (DataRow drRow in lstRows)
{
if (string.IsNullOrEmpty(strExtraFieldsString))
{
strExtraFieldsString += string.Join(Constants.vbNewLine, drRow.ItemArray.Where(x => x.ToString() != Tablename).ToArray());
}
else
{
strExtraFieldsString += Constants.vbNewLine + string.Join(Constants.vbNewLine, drRow.ItemArray.Where(x => x.ToString() != Tablename).ToArray());
}
}
if (!string.IsNullOrEmpty(strExtraFieldsString))
{
DataColumn dcCol = new DataColumn("Sys_ExtraFields");
dcCol.MaxLength = 4000;
dcCol.DefaultValue = strExtraFieldsString;
dtTable.Columns.Add(dcCol);
}
dtTable = dtTable.Select().Skip(RowsToSkip).CopyToDataTable();
}
//Get Header if it is starting from zero
if (v_HasHeader && dtTable.Rows.Count > 0)
{
if (v_PagingStartRecord == 0)
{
int intCount = 0;
// Dim intSheetnameidx As Integer = dtTable.Columns.IndexOf("Sys_Sheetname")
// If intSheetnameidx < 0 Then intSheetnameidx = 0
foreach (DataColumn dCol in dtTable.Columns)
{
if (!dCol.ColumnName.StartsWith("Sys_"))
{
string strColumnname = "";
// If intCount <= intSheetnameidx Then
strColumnname = dtTable.Rows[0][intCount].ToString() == "" ? "F" + (intCount) : GetColumnName(dtTable.Rows[0][intCount].ToString().Trim(), dtTable);
// Else
// strColumnname = If(dtTable.Rows(0).Item(intCount).ToString = "", _
// "F" & (intCount + 2), _
// GetColumnName(dtTable.Rows(0).Item(intCount).ToString.Trim, dtTable))
// End If
if (dtTable.Columns.IndexOf(strColumnname) > -1 && dtTable.Rows[0][intCount].ToString() != "")
{
dCol.ColumnName = strColumnname + "_" + intCount.ToString();
}
else
{
dCol.ColumnName = strColumnname;
}
}
intCount += 1;
}
dtTable.Rows[0].Delete();
dtTable.AcceptChanges();
SchemaTable.AsEnumerable().Where(x => x["TABLE_NAME"].ToString() == Tablename).First()["Header"] = String.Join(";", dtTable.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray <String>());
}
else if (SchemaTable.AsEnumerable().Where(x => x["TABLE_NAME"].ToString() == Tablename).First()["Header"].ToString() != "")
{
string strHeader = SchemaTable.AsEnumerable().Where(x => x["TABLE_NAME"].ToString() == Tablename).First()["Header"].ToString();
int idx = 0;
foreach (string strColumn in strHeader.Split(';'))
{
dtTable.Columns[idx].ColumnName = strColumn;
idx++;
}
}
}
foreach (DataColumn dCol in dtTable.Columns)
{
if (dCol.ColumnName.Contains("'"))
{
dCol.ColumnName = dCol.ColumnName.Replace("'", "");
}
}
}
return(dtTable);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + Constants.vbNewLine + string.Format("Sheet:{0}.File:F{1}", Tablename, FilePath));
}
}
}
