这个使用Datagrid DataSet将excel内容导入SQL Server有问题么?现在导入一个excel含数据量65535条,需要6分钟的时间。。。慢呀~~~能否改良?
------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Web.Security;
namespace grid
{
/// <summary>
/// phone_segment 的摘要说明。
/// </summary>
public class phone_segment : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button importbtn;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.TextBox txtimportcount;
protected System.Web.UI.WebControls.Button deletebtn;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.TextBox txtdelete;
protected System.Web.UI.WebControls.Button goonbtn;
protected System.Web.UI.HtmlControls.HtmlInputFile fileOpen;
protected System.Web.UI.WebControls.Button exitbtn;
static int importcount = 0;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(!Page.IsPostBack)
{
if(Session["name"] == null)
{
this.Response.Write("<script>alert('请登录')</script>");
this.Response.Redirect("login.aspx",true);
}
else
deletebtn.Attributes.Add("onclick","return confirm('您确定要删除所有记录吗?');");
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.importbtn.Click += new System.EventHandler(this.importbtn_Click);
this.deletebtn.Click += new System.EventHandler(this.deletebtn_Click);
this.goonbtn.Click += new System.EventHandler(this.goonbtn_Click);
this.exitbtn.Click += new System.EventHandler(this.exitbtn_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void importbtn_Click(object sender, System.EventArgs e)
{
if (fileOpen.PostedFile.FileName == "")
{
Response.Write(" <script language=javascript>alert('请选择要上传的文件!'); </script>");
return;
}
else
{
try
{
System.IO.FileInfo fif = new System.IO.FileInfo(fileOpen.PostedFile.FileName);
string source1 = fif.DirectoryName;
string source2 = fif.Name;
string source = source1 + "//" + source2;
int index = source2.IndexOf(".");
string filetype = source2.Substring(index,source2.Length-index);
if (filetype != ".xls")
{
this.Response.Write("<script>alert('导入文件格式不正确!')</script>");
return;
}
else
{
//连接数据库,判断数据库里是否有数据
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand cmd = conn.CreateCommand();
//查询数据库
cmd.CommandText = " select * from phone_segment";
conn.Open();
SqlDataAdapter Adapter=new SqlDataAdapter();
Adapter.SelectCommand=cmd;
DataSet myDs=new DataSet();
int num = Adapter.Fill(myDs);
conn.Close();
if( num > 0)
{
this.Response.Write("<script language=javascript>alert('请先删除数据库里所有的记录,在重新导入!'); </script>");
return;
}
else
{
//fileOpen.PostedFile.SaveAs(fileOpen.PostedFile.FileName);
string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
string path = HttpContext.Current.Server.MapPath(urlPath);
string excelPath = path + source2;
fileOpen.PostedFile.SaveAs(excelPath);
string Connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '"+ excelPath +"'; Extended Properties = Excel 8.0";
string query = "SELECT * FROM [sheet1$]";
OleDbCommand oleCommand = new OleDbCommand(query,new OleDbConnection(Connstr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
//将Excel的sheet1表内容填充到DataSet对象
oleAdapter.Fill(myDataSet,"[Sheet1$]");
// int i=oleAdapter.Fill(myDataSet); //datagrid中的行数
System.IO.File.Delete(excelPath);//删除临时文件
oleCommand.Dispose(); //释放资源
oleAdapter.Dispose();
//插入数据库
int count = myDataSet.Tables[0].Rows.Count;
for (int i = 0; i < count ; i++)
{
string telset = myDataSet.Tables[0].Rows[i][0].ToString();
string code = myDataSet.Tables[0].Rows[i][1].ToString();
string city = myDataSet.Tables[0].Rows[i][2].ToString();
cmd.CommandText = " insert into phone_segment values('"+telset+"', '"+code+"','"+city+"')";
conn.Open();
cmd.ExecuteNonQuery();
importcount++;
conn.Close();
}
this.Response.Write("<script>alert('导入记录成功')</script>");
this.txtimportcount.Text = importcount.ToString();
myDataSet.Dispose();
}
}//else
}//try
catch(Exception ex)
{
this.Response.Write(ex.Message);
}
}//else
}
private void deletebtn_Click(object sender, System.EventArgs e)
{
//连接数据库,判断数据库里是否有数据
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand cmd = conn.CreateCommand();
//查看数据库中的记录数
cmd.CommandText = "select * from phone_segment";
conn.Open();
SqlDataAdapter Adapter=new SqlDataAdapter();
Adapter.SelectCommand=cmd;
DataSet myDs=new DataSet();
int num = Adapter.Fill(myDs);
conn.Close();
//删除数据库
cmd.CommandText = "delete from phone_segment";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
//查询数据库
cmd.CommandText = "select * from phone_segment";
conn.Open();
SqlDataAdapter Adapter1=new SqlDataAdapter();
Adapter1.SelectCommand=cmd;
DataSet myDs1=new DataSet();
int i = Adapter1.Fill(myDs1);
conn.Close();
if( i == 0)
{
this.txtdelete.Text = num.ToString();
this.Response.Write("<script>alert('删除所有的记录成功')</script>");
return;
}
else
{
this.Response.Write("<script>alert('删除所有的记录失败')</script>");
return;
}
}
private void goonbtn_Click(object sender, System.EventArgs e)
{
if (fileOpen.PostedFile.FileName == "")
{
Response.Write(" <script language=javascript>alert('请选择要上传的文件!'); </script>");
return;
}
else
{
try
{
//连接数据库,判断数据库里是否有数据
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand cmd = conn.CreateCommand();
System.IO.FileInfo fif = new System.IO.FileInfo(fileOpen.PostedFile.FileName);
string source1 = fif.DirectoryName;
string source2 = fif.Name;
string source = source1 + "//" + source2;
int index = source2.IndexOf(".");
string filetype = source2.Substring(index,source2.Length-index);
if (filetype != ".xls")
{
this.Response.Write("<script>alert('导入文件格式不正确!')</script>");
return;
}
else
{
//fileOpen.PostedFile.SaveAs(fileOpen.PostedFile.FileName);
string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
string path = HttpContext.Current.Server.MapPath(urlPath);
string excelPath = path + source2;
fileOpen.PostedFile.SaveAs(excelPath);
string Connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '"+ excelPath +"'; Extended Properties = Excel 8.0";
string query = "SELECT * FROM [sheet1$]";
OleDbCommand oleCommand = new OleDbCommand(query,new OleDbConnection(Connstr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
//将Excel的sheet1表内容填充到DataSet对象
oleAdapter.Fill(myDataSet,"[Sheet1$]");
// int i=oleAdapter.Fill(myDataSet); //datagrid中的行数
System.IO.File.Delete(excelPath);//删除临时文件
oleCommand.Dispose(); //释放资源
oleAdapter.Dispose();
//插入数据库
int count = myDataSet.Tables[0].Rows.Count;
for (int i = 0; i < count ; i++)
{
string telset = myDataSet.Tables[0].Rows[i][0].ToString();
string code = myDataSet.Tables[0].Rows[i][1].ToString();
string city = myDataSet.Tables[0].Rows[i][2].ToString();
cmd.CommandText = " insert into phone_segment values('"+telset+"', '"+code+"','"+city+"')";
conn.Open();
cmd.ExecuteNonQuery();
importcount++;
conn.Close();
}
this.Response.Write("<script>alert('导入记录成功')</script>");
this.txtimportcount.Text = importcount.ToString();
myDataSet.Dispose();
}//else
}//try
catch(Exception ex)
{
this.Response.Write(ex.Message);
}
}//else
}
private void exitbtn_Click(object sender, System.EventArgs e)
{
this.Response.Redirect("forward.aspx",true);
}
}
}
------------------------