博客开通第二十八天
导入数据
//导入按钮
private void btnImport_Click(object sender, EventArgs e)
{
string[] filepaths = addDataFile.getFilePath();
if (filepaths == null)
return;
try
{
foreach (string filepath in filepaths)
{
string sqlStr = "select * from " + Path.GetFileName(filepath);
dtTable = Tool.readCSVToDataTable(sqlStr,filepath); //Tool.readCSVToDataTable(sqlStr,filepath)是Tool类里的一个方法
//内容是
// 读取CSV文件至DataTable
///public static DataTable readCSVToDataTable(string sqlStr, string filepath)
///{
///DataTable dtTable = null;
///using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
///Path.GetDirectoryName(filepath) + ";Extended Properties=Text;"))
///{
///dtTable = new DataTable();
///OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStr, conn);
///dtTable.Clear();
///adapter.Fill(dtTable);
///return dtTable;
///}
///}
dataToDabase(dtTable);
}
}
catch (Exception ex)
{
MessageDialog.ShowError(ex.Message);
}
}
//导入数据到数据库
public void dataToDabase(DataTable dtTable)
{
int addCount = dtTable.Rows.Count;
string sqlSelect = "select * from " + talbe;
int deleteCount = DBObject.QueryCount(sqlSelect);
string sqlDeleteStr = "truncate table " + talbe;
DBObject.ExecuteSql(Tool.DB_NAME,sqlDeleteStr);
//添加
DataTableToDataBase(Tool.DB_CONNECT, talbe, dtTable);
DataImportFinish dataImportFinish = new DataImportFinish(addCount, deleteCount);
panel2.Controls.Clear();
panel2.Controls.Add(dataImportFinish);
btnImport.Hide();
btnOver.Visible = true;
}
/// 将读取的数据放入到dataBase中
private void DataTableToDataBase(string connectionStr,string tableName,DataTable dt)
{
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名
sqlbulkcopy.WriteToServer(dt);
}