数据库语句问题
0
悬赏园豆：20
[已解决问题]
解决于 2012-08-18 08:37
数据库中，怎么做批量删除？批量添加？怎么从外部文档中导入数据到程序中，最终加进数据库？（例如：xls文件），求解答
最佳答案
0
我给你一个小的测试将excel导入到数据库，代码如下：
aspx页面
View Code
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="BulkCopySql.aspx.cs" Inherits="_Default" %>
2
3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4 <html xmlns="http://www.w3.org/1999/xhtml">
5 <head runat="server">
6 <title>无标题页</title>
7 </head>
8 <body>
9 <form id="form1" runat="server">
10 <div>
11 <asp:Button ID="Button1" runat="server" Text="批量插入" OnClick="Button1_Click" />
12 <br />
13 <asp:Button ID="Button2" runat="server" Text="插入到不同字段" OnClick="Button2_Click" />
14 <br />
15 <asp:Button ID="Button3" runat="server" Text="XML复制到Sql"
16 onclick="Button3_Click" />
17 <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
18 </div>
19 </form>
20 </body>
21 </html>
aspx.cs页面
View Code
1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Web.Configuration;
5
6 public partial class _Default : System.Web.UI.Page
7 {
8 protected void Page_Load(object sender, EventArgs e)
9 {
10
11 }
12 //一般的导入
13 protected void Button1_Click(object sender, EventArgs e)
14 {
15 string connectionString = GetConnectionString();
16 // Open a sourceConnection to the AdventureWorks database.
17 using (SqlConnection sourceConnection =
18 new SqlConnection(connectionString))
19 {
20 sourceConnection.Open();
21
22 // Perform an initial count on the destination table.
23 SqlCommand commandRowCount = new SqlCommand(
24 "SELECT COUNT(*) FROM " +
25 "dbo.BulkCopyDemoMatchingColumns;",
26 sourceConnection);
27 long countStart = System.Convert.ToInt32(
28 commandRowCount.ExecuteScalar());
29
30 // Get data from the source table as a SqlDataReader.
31 SqlCommand commandSourceData = new SqlCommand(
32 "SELECT ProductID, Name, " +
33 "ProductNumber " +
34 "FROM Production.Product;", sourceConnection);
35 SqlDataReader reader =
36 commandSourceData.ExecuteReader();
37
38 // Open the destination connection. In the real world you would
39 // not use SqlBulkCopy to move data from one table to the other
40 // in the same database. This is for demonstration purposes only.
41 using (SqlConnection destinationConnection =
42 new SqlConnection(connectionString))
43 {
44 destinationConnection.Open();
45
46 // Set up the bulk copy object.
47 // Note that the column positions in the source
48 // data reader match the column positions in
49 // the destination table so there is no need to
50 // map columns.
51 using (SqlBulkCopy bulkCopy =
52 new SqlBulkCopy(destinationConnection))
53 {
54 bulkCopy.BatchSize = 100;
55 bulkCopy.DestinationTableName =
56 "dbo.BulkCopyDemoMatchingColumns";
57
58 try
59 {
60 // Write from the source to the destination.
61 bulkCopy.WriteToServer(reader);
62 }
63 catch (Exception ex)
64 {
65 Response.Write(ex.Message);
66 }
67 finally
68 {
69 // Close the SqlDataReader. The SqlBulkCopy
70 // object is automatically closed at the end
71 // of the using block.
72 reader.Close();
73 }
74 }
75
76 // Perform a final count on the destination
77 // table to see how many rows were added.
78 long countEnd = System.Convert.ToInt32(
79 commandRowCount.ExecuteScalar());
80
81 Label1.Text = (countEnd - countStart).ToString() + "rows were added.";
82
83 }
84 }
85 }
86 private static string GetConnectionString()
87 // To avoid storing the sourceConnection string in your code,
88 // you can retrieve it from a configuration file.
89 {
90 return WebConfigurationManager.ConnectionStrings["Bulk"].ConnectionString;
91 }
92
93 //当字段不一致时进行映射
94 protected void Button2_Click(object sender, EventArgs e)
95 {
96 string connStr = GetConnectionString();
97 using (SqlConnection conn = new SqlConnection(connStr))
98 {
99 conn.Open();
100 //Get data from source table as a sqldatareader
101 SqlCommand cmd = new SqlCommand("SELECT ProductID, Name, " +
102 "ProductNumber " +
103 "FROM Production.Product;", conn);
104 SqlDataReader reader = cmd.ExecuteReader();
105 using (SqlConnection connDes = new SqlConnection(connStr))
106 {
107 connDes.Open();
108 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connDes))
109 {
110 bulkCopy.ColumnMappings.Add("ProductID", "ProdID");
111 bulkCopy.ColumnMappings.Add("ProductNumber", "ProdNum");
112 bulkCopy.ColumnMappings.Add("Name", "ProdName");
113 bulkCopy.DestinationTableName = "dbo.BulkCopyDemoDifferentColumns";
114 try
115 {
116 bulkCopy.WriteToServer(reader);
117 }
118 catch (Exception ex)
119 {
120 Response.Write(ex.Message);
121 }
122 finally
123 {
124 reader.Close();
125 }
126
127 }
128 }
129
130 }
131 }
132
133 //从xml导入数据
134 protected void Button3_Click(object sender, EventArgs e)
135 {
136 string connStr = GetConnectionString();
137 DataSet ds = new DataSet();
138 DataTable sourceData = new DataTable();
139 ds.ReadXml(Server.MapPath(".")+"\\"+"Product.xml");
140 sourceData = ds.Tables[0];
141 //Destination table
142 using (SqlConnection conn = new SqlConnection(connStr))
143 {
144 conn.Open();
145 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
146 {
147 //Column mapping
148 bulkCopy.ColumnMappings.Add("ProductID", "ProdID");
149 bulkCopy.ColumnMappings.Add("ProductName", "ProdName");
150 bulkCopy.DestinationTableName = "dbo.BulkCopyDemoDifferentColumns";
151 bulkCopy.WriteToServer(sourceData);
152 }
153 }
154 }
155 }
收获园豆：20
其他回答(2)
0
其實這個是很簡單的，我經常處理這樣的數據。最簡單的方法，就是使用EXEL功能，自動生成代碼，通過臨時表，添加數據到DB中
0