从ASP.NET中的dbo表中删除数据 [英] Delete data from dbo table in ASP.NET

问题描述

Hello Friends，

我创建了一个项目ExcelChart.aspx，其中来自excel文件Sample.xls的数据已被复制到dbo表 - ExcelTable.dbo。该表的详细信息已通过gridview控件显示在网页上。

此外，相同的dbo表也通过图表用于图形表示。



我使用下面的代码将数据从Excel文件复制到dbo表并显示在gridview控件中：

 字符串 strConnection =  数据源=。\\SQLEXPRESS; AttachDbFilename = | DataDirectory | \\ExcelChart.mdf; Integrated Security = True; User Instance = True; 
  //  文件上传路径 
  string  path = FileUploadexcel.PostedFile.FileName; 
  string  fileBasePath = Server.MapPath（  〜/ Excel中/）; 
  string  fileName = Path.GetFileName（ this  .FileUploadexcel.FileName）; 
  string  fullFilePath = fileBasePath + fileName; 
  //  创建Excel工作簿的连接字符串 
  string  excelConnectionString =  @  Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + fullFilePath +  ;扩展属性= Excel 12.0;持久安全信息=假; 
  //  创建与Excel工作簿的连接 
 OleDbConnection excelConnection =  new  OleDbConnection（excelConnectionString）; 
  //  创建OleDbCommand以从Excel获取数据 
 OleDbCommand cmd =  new  OleDbCommand（ 选择[Carats]，[Value]，[月份来自[Sheet1 $]，excelConnection）; 
 excelConnection.Open（）; 
 OleDbDataReader dReader; 
 dReader = cmd.ExecuteReader（）; 
 SqlBulkCopy sqlBulk =  new  SqlBulkCopy（strConnection）; 
  //  提供目的地表名称 
 sqlBulk.DestinationTableName =   ExcelTable; 
 sqlBulk.WriteToServer（dReader）; 
 excelConnection.Close（）; 
 OleDbDataAdapter da =  new  OleDbDataAdapter（cmd）; 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds）; 
 GridViewexcel.DataSource = ds.Tables [ 0 ]; 
 GridViewexcel.DataBind（）; 
 da.Dispose（）; 

以上代码首次正常运行执行。但是在页面加载或刷新时，数据再次从excel文件复制到dbo表。但是当dbo表中的Month列设置为主键时，行sqlBulk.WriteToServer（dReader）;给我复制重复值的错误。

我已经尝试了一百万个其他代码来设置相同但却没有这样做。

现在我在想是否有可能在页面加载时从dbo表中删除记录，并在dbo表中再次复制excel文件中的详细信息。

请帮忙。如果以上概念是可能的，我将非常乐意获得一些帮助。

非常感谢。



我有什么试过：



sqlBulk.WriteToServer（dReader）;给出重复记录的错误，无法在dbo表中复制。

解决方案

，excelConnection）;
excelConnection.Open（）;
OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnection）;
< span class =code-comment> // 提供目的地表名称
sqlBulk.DestinationTableName = ExcelTable;
sqlBulk.WriteToServer（dReader）;
excelConnection.Close（）;
OleDbDataAdapter da = new OleDbDataAdapter（cmd）;
DataSet ds = new DataSet（）;
da.Fill（ds）;
GridViewexcel.DataSource = ds.Tables [ 0 ];
GridViewexcel.DataBind（）;
da.Dispose（）;

以上代码适用于t他第一次执行。但是在页面加载或刷新时，数据再次从excel文件复制到dbo表。但是当dbo表中的Month列设置为主键时，行sqlBulk.WriteToServer（dReader）;给我复制重复值的错误。

我已经尝试了一百万个其他代码来设置相同但却没有这样做。

现在我在想是否有可能在页面加载时从dbo表中删除记录，并在dbo表中再次复制excel文件中的详细信息。

请帮忙。如果以上概念是可能的，我将非常乐意获得一些帮助。

非常感谢。



我有什么试过：



sqlBulk.WriteToServer（dReader）;给出重复记录的错误，无法在dbo表中复制。

解决方案是使用Sessions和Viewstate，它们在下面的链接中描述：防止页面刷新上的重复记录插入：ASP联盟 [ ^ ]

Hello Friends,
I have created a project ExcelChart.aspx where in the data from an excel file Sample.xls has been copied to a dbo table - ExcelTable.dbo. The details of the table is been displayed on the webpage via a gridview control.
Also the same dbo table is been used for a graphical representation via Chart.

I have used the below code to copy the data from Excel file to dbo table and to display in gridview control:

String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ExcelChart.mdf;Integrated Security=True;User Instance=True";
//file upload path
string path = FileUploadexcel.PostedFile.FileName;
string fileBasePath = Server.MapPath("~/Excel/");
string fileName = Path.GetFileName(this.FileUploadexcel.FileName);
string fullFilePath = fileBasePath + fileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [Carats],[Value],[Month] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "ExcelTable";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridViewexcel.DataSource = ds.Tables[0];
GridViewexcel.DataBind();
da.Dispose();

The above code works fine for the first time execution. However on page load or on refresh the data is again been copied from excel file to dbo table. But as the Month column in dbo table is set to primary key the line sqlBulk.WriteToServer(dReader); gives me error for copying duplicate values.
I have tried a million other codes to set the same but failed to do so.
Now i was thinking if it is possible to delete records from dbo table on page load and let the details from excel file be copied again in the dbo table.
Please help. If the above concept is possible i will be really glad to gain some help.
Thanks a lot.

What I have tried:

sqlBulk.WriteToServer(dReader); gives error of duplicate records cannot be copied in the dbo table.

解决方案

", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); //Give your Destination table name sqlBulk.DestinationTableName = "ExcelTable"; sqlBulk.WriteToServer(dReader); excelConnection.Close(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); GridViewexcel.DataSource = ds.Tables[0]; GridViewexcel.DataBind(); da.Dispose();

The above code works fine for the first time execution. However on page load or on refresh the data is again been copied from excel file to dbo table. But as the Month column in dbo table is set to primary key the line sqlBulk.WriteToServer(dReader); gives me error for copying duplicate values.
I have tried a million other codes to set the same but failed to do so.
Now i was thinking if it is possible to delete records from dbo table on page load and let the details from excel file be copied again in the dbo table.
Please help. If the above concept is possible i will be really glad to gain some help.
Thanks a lot.

What I have tried:

sqlBulk.WriteToServer(dReader); gives error of duplicate records cannot be copied in the dbo table.

the solution is by using Sessions and Viewstate which is described in the below link: Preventing Duplicate Record Insertion on Page Refresh: ASP Alliance[^]

这篇关于从ASP.NET中的dbo表中删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！