//public static DataTable ExecuteDataTable(OleDbConnection connection, bool isProc, string commandText)
public DataTable getTable(string connectionString, bool isProc, string commandText)
{
DataTable dt;
dt = OleDbHelper.ExecuteDataTable(connectionString, isProc, commandText);
return(dt);
}
/// <summary>
/// 学生名单导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
string filePath = this.openFileDialog1.FileName;
this.WriteLog("\n你选择了以下文件用于导入学生名单：" + filePath);
string fileExt = System.IO.Path.GetExtension(filePath);
this.WriteLog(string.Format("你选择了{0}文件", fileExt));
switch (fileExt.ToLower())
{
case ".dbf":
string connectionString = string.Format("Provider=VFPOLEDB.1;Data Source='{0}';Collating Sequence=MACHINE;", filePath);
this.TempDataTable = OleDbHelper.ExecuteDataTable(connectionString,
string.Format("select * from {0}", System.IO.Path.GetFileNameWithoutExtension(filePath))
);
WriteLog(System.IO.Path.GetFileNameWithoutExtension(filePath));
if (ImportStudent(this.TempDataTable))
{
this.ShowData();
}
break;
default:
break;
}
this.WriteLog("导入成功。");
}
public static DataTable GetDataTable(string filePath, string headerNames = "*", string sheetName = "Sheet1$")
{
if (!sheetName.EndsWith("$"))
{
sheetName += "$";
}
return(OleDbHelper.ExecuteDataTable(GetConnectionString(filePath), CommandType.Text, $"SELECT {headerNames} FROM [{sheetName}]"));
//using (var conn = new OleDbConnection(GetConnectionString(filePath)))
//{
// try
// {
// conn.Open();
// var cmd = new OleDbCommand(string.Format("SELECT {0} FROM [{1}]", headerNames, sheetName), conn);
// var adapter = new OleDbDataAdapter { SelectCommand = cmd };
// var table = new DataTable();
// adapter.Fill(table);
// conn.Close();
// return table;
// }
// finally
// {
// conn.Close();
// }
//}
}
public override SerializableDictionary <string, string> GetDeafultColumnMapping()
{
SerializableDictionary <string, string> columnMapping = new SerializableDictionary <string, string>();
string sql = this.SQL + " where 1=0";
DataTable table = OleDbHelper.ExecuteDataTable(this.Connection, sql);
IEnumerable <DataColumn> fields = table.Columns.Cast <DataColumn>().Where(col => col.ColumnName != "_row_num");
foreach (var item in fields.Select(row => row.ColumnName).Distinct())
{
columnMapping.Add(item, item);
}
return(columnMapping);
}
private int BulkCopyByPage(ETLInfo etlInfo, int pageSize, int pageIndex, BulkCopy bcp, OleDbConnection sourceConnection, DbConnection DestinationConnection)
{
string sql = etlInfo.SourceTable.ToQuerySQL(pageSize, pageIndex);;
try
{
DataTable dt = OleDbHelper.ExecuteDataTable(sourceConnection, sql);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
bcp.Insert(etlInfo.DestinationTableName, dt, etlInfo.ColumnMapping);
return(dt.Rows.Count);
}
catch (Exception ex)
{
string errorString = string.Format("转移失败,数据源SQL:{0}...", sql);
File.AppendAllText(@"bulkcopy.log", errorString);
throw;
}
}