将数据存储在Excel中到SQL Server数据库 [英] store data in excel to sql server database

问题描述

我创建了一个项目.
我在其中使用了标签，文件上传，gridview和按钮.

这是按钮单击事件中的代码:

I am created a project.
I used a label, fileupload, gridview and button in it.

This is the code in button click event :

SqlConnection con = new SqlConnection(/*connection string*/);
       FileUpload1.SaveAs(Server.MapPath("~//App_Data//") + FileUpload1.FileName);
       string path = Server.MapPath("~//App_Data//") + FileUpload1.FileName;
       try
       {
           //for .xls file
           System.Data.OleDb.OleDbConnection MyConnection;
           DataSet DtSet;
           System.Data.OleDb.OleDbDataAdapter MyCommand;
           MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
           MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
           DtSet = new System.Data.DataSet();
           MyCommand.Fill(DtSet, "[Sheet1$]");
           MyConnection.Close();
           GridView1.DataSource = DtSet.Tables[0];
           GridView1.DataBind();

           SqlDataAdapter adpter = new SqlDataAdapter("Select * from StudentList", con);
           adpter.Update(DtSet.Tables[0]);
           Response.Write("successfully Imported values to SQL Server");

       }
       catch (Exception ex)
       {
           Response.Write(ex.Message);
       }

上面的代码成功执行.它从excel检索数据并显示在GridView中，但DataSet表行未保存在Sql Server中创建的表中.

我想使用断开连接的模式.


将MapPath行中的反斜杠更改为斜杠，以便代码着色器可以应对.
[/Edit]

The above code executes successfully. It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server.

I want to use disconnected mode.


Changed backslashes to slashes in the MapPath lines so that the code colourizer could cope.
[/Edit]

推荐答案

"，MyConnection)； DtSet = 新 System.Data.DataSet(); MyCommand.Fill(DtSet，"

", MyConnection); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet, "[Sheet1

" ) ; MyConnection.Close(); GridView1.DataSource = DtSet.Tables [ 0 ]; GridView1.DataBind(); SqlDataAdapter adpter = 新建 SqlDataAdapter(" ，骗局)； adpter.Update(DtSet.Tables [ 0 ]); Response.Write(" )； } 捕获(例外) { Response.Write(ex.Message); }

"); MyConnection.Close(); GridView1.DataSource = DtSet.Tables[0]; GridView1.DataBind(); SqlDataAdapter adpter = new SqlDataAdapter("Select * from StudentList", con); adpter.Update(DtSet.Tables[0]); Response.Write("successfully Imported values to SQL Server"); } catch (Exception ex) { Response.Write(ex.Message); }

上面的代码成功执行.它从excel检索数据并显示在GridView中，但DataSet表行未保存在Sql Server中创建的表中.

我想使用断开连接的模式.


将MapPath行中的反斜杠更改为斜杠，以便代码着色器可以应对.
[/Edit]

The above code executes successfully. It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server.

I want to use disconnected mode.


Changed backslashes to slashes in the MapPath lines so that the code colourizer could cope.
[/Edit]

我认为如果您先将其存储到数据库中并从中选择它会更好.请在此处查看更多信息，以及如何使用SqlBulkCopy的示例.

http://stackoverflow.com/questions/3664067/import-data-from- excel-into-multiple-tables [ ^ ]

附带说明:在您的代码示例中，最好尽可能晚地创建连接(con)，这样您就不会使用不必要的资源并保持连接占用，即使直到以后.

祝你好运！

I think it would work better if you would store it into the database first and select it from there. Have a look here for more more info and an example how to use SqlBulkCopy for that.

http://stackoverflow.com/questions/3664067/import-data-from-excel-into-multiple-tables[^]

As a side note: In your code example it would be somewhat better to create the connection (con) as late as possible so you don''t use unnecessary resources and keep a connection occupied even if it isn''t used until later.

Good luck!

这篇关于将数据存储在Excel中到SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！