| public static IfNullOrEmpty ( string value, string defaultValue ) : string | ||
| value | string | |
| defaultValue | string | |
| return | string | |
public DataTable LoadDataTableFromExcel(string excelPath, string tabName = "")
{
string connectionString = string.Format(ExcelOdbcDriver, excelPath);
string sql = string.Format("select * from [{0}$]", Helper.IfNullOrEmpty(tabName, "Sheet1"));
return(OdbcLoadDataTable(connectionString, sql));
}
public string RootGetTableColumnType(DataColumn col)
{
StringBuilder result = new StringBuilder();
if (col.DataType.Name == "Int32" || col.DataType.Name == "Integer" || col.DataType.Name == "Double" || col.DataType.Name == "Decimal" || col.DataType.Name == "Number")
{
//Check for integer
bool isInteger = true;
foreach (DataRow row in col.Table.Rows)
{
int a;
if (!row.IsNull(col) && !int.TryParse(row[col].ToString(), out a))
{
isInteger = false;
break;
}
}
if (isInteger)
{
result.Append(Helper.IfNullOrEmpty(ColumnIntegerType, _defaultColumnIntegerType));
}
else
{
result.Append(Helper.IfNullOrEmpty(ColumnNumericType, _defaultColumnNumericType));
}
}
else if (col.DataType.Name == "DateTime")
{
result.Append(Helper.IfNullOrEmpty(ColumnDateTimeType, _defaultColumnDateTimeType));
}
else
{
int len = col.MaxLength;
if (len <= 0)
{
len = ColumnCharLength;
}
if (ColumnCharLength <= 0)
{
//auto size
len = 1;
foreach (DataRow row in col.Table.Rows)
{
if (row[col].ToString().Length > len)
{
len = row[col].ToString().Length + 1;
}
}
}
result.AppendFormat("{0}({1})", Helper.IfNullOrEmpty(ColumnCharType, _defaultColumnCharType), len);
}
return(result.ToString());
}
public string GetTableCreateCommand(DataTable table)
{
if (MyGetTableCreateCommand != null)
{
return(MyGetTableCreateCommand(table));
}
StringBuilder result = new StringBuilder();
foreach (DataColumn col in table.Columns)
{
if (result.Length > 0)
{
result.Append(',');
}
result.AppendFormat("{0} ", GetTableColumnName(col.ColumnName));
if (col.DataType.Name == "Int32" || col.DataType.Name == "Integer" || col.DataType.Name == "Double" || col.DataType.Name == "Decimal" || col.DataType.Name == "Number")
{
result.Append(Helper.IfNullOrEmpty(ColumnNumericType, _defaultColumnNumericType));
}
else if (col.DataType.Name == "DateTime")
{
result.Append(Helper.IfNullOrEmpty(ColumnDateTimeType, _defaultColumnDateTimeType));
}
else
{
int len = col.MaxLength;
if (len <= 0)
{
len = ColumnCharLength;
}
if (ColumnCharLength <= 0)
{
//auto size
len = 1;
foreach (DataRow row in table.Rows)
{
if (row[col].ToString().Length > len)
{
len = row[col].ToString().Length + 1;
}
}
}
result.AppendFormat("{0}({1})", Helper.IfNullOrEmpty(ColumnCharType, _defaultColumnCharType), len);
}
result.Append(" NULL");
}
return(string.Format("CREATE TABLE {0} ({1})", CleanName(table.TableName), result));
}
public DataTable LoadDataTableFromExcel(string excelPath, string tabName = "")
{
if (MyLoadDataTableFromExcel != null)
{
return(MyLoadDataTableFromExcel(excelPath, tabName));
}
//Copy the Excel file if it is open...
FileHelper.PurgeTempApplicationDirectory();
string newPath = FileHelper.GetTempUniqueFileName(excelPath);
File.Copy(excelPath, newPath, true);
File.SetLastWriteTime(newPath, DateTime.Now);
string connectionString = string.Format(ExcelOdbcDriver, newPath);
string sql = string.Format("select * from [{0}$]", Helper.IfNullOrEmpty(tabName, "Sheet1"));
return(OdbcLoadDataTable(connectionString, sql));
}
public string GetInsertCommand(string sql)
{
return(Helper.IfNullOrEmpty(InsertStartCommand, _defaultInsertStartCommand) + " " + sql + " " + Helper.IfNullOrEmpty(InsertEndCommand, _defaultInsertEndCommand));
}
