学习.net快半个月了,今天实现数据的导入导出,在公司用sql server2000,在家用sql server2005,测试都通过,只是改了下配件文件的连接字符串。
第一步,为了便于测试,首先要在数据库建立一个数据库和一张表,以及几条数据记录。
我现在用sql server2005操作,首先在SQL Server Management Studio用windows身份验证登录,在里面建一数据库叫mis,再建一张表叫users,三个字段就够了,下面是一条简单的SQL语句:
create table users ( userID int primary key, userName varchar(50), address varchar(50) )
然后随便加几条记录。最后添加一个登录用户叫mis ,密码也是mis ,选择SQL Server身份验证,选择默认数据库,将用户mis设置为数据库角色db_owner ,最后用mis确保成功登录。第二步,在VS2005中新建网站,直接用default.aspx来添加就行,我的default.aspx页面代码如下:<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>无标题页</title> </head> <body> <form id="form1" runat="server"> <asp:Button ID="readFromDB" runat="server" OnClick="readFromDB_Click" Text="从数据库读取数据" /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" Font-Names="Arial" Font-Size="12px" GridLines="Horizontal" RowStyle-HorizontalAlign="Center" Width="50%"> <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" /> <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" /> <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" /> <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" /> <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" HorizontalAlign="Center" /> <AlternatingRowStyle BackColor="#F7F7F7" /> </asp:GridView> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Import" runat="server" OnClick="Import_Click" Text="导入" /> <asp:Button ID="output" runat="server" OnClick="output_Click" Text="导出" /> </form> </body> </html>
里面有三个按钮,分别是“从数据库读取数据”,“导入”,“导出”,一个Gridview控件,一个FileUpload控件。
第三步,在配置文件web.config添加连接字符串,方便使用。
<appSettings> <add key="strConn" value="Data Source=127.0.0.1;Initial Catalog=mis;User Id=mis;Password=mis"/> </appSettings>第三步,对按钮“从数据库读取数据”的编写代码当单按钮时,先从配置文件获取连接字符串,然后连接数据库,读取数据放在DataSet中,将DataSet作为GridView的数据源来绑定,这样GridView就会显示数据库里的数据。代码如下://从数据库读取数据,并绑定到GridView protected void readFromDB_Click(object sender, EventArgs e) { string strConn = ConfigurationManager.AppSettings["strConn"].ToString(); DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(strConn)) { string sql = "select * from users"; SqlDataAdapter sda = new SqlDataAdapter(sql, conn); sda.Fill(ds, "users");//Fill方法会判断连接是否打开,没有则隐式打开,使用完后关闭 } //将DS绑定到GridView GridView1.DataSource = ds.Tables["users"]; GridView1.DataBind(); }
第四步,对“导入”按钮编写响应代码。//从excel导入到数据库 protected void Import_Click(object sender, EventArgs e) { string filePath = ""; string getErrMsg = ""; DataSet excelDs = new DataSet(); if (FileUpload1.PostedFile.FileName == "") { Response.Write("<script language=javascript>alert('请选择要上传的文件!');</script>"); return; } //从Excel读取数据 filePath = FileUpload1.PostedFile.FileName; string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/";Data Source=" + filePath; OleDbConnection excelConn = new OleDbConnection(connString); OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn); try { ExcelDA.Fill(excelDs, "users"); } catch (Exception err) { Response.Write(err.Message); } finally { excelConn.Close(); excelConn = null; } //将数据写入数据库 if (excelDs.Tables[0].Rows.Count != 0) { string sql = ""; string strConn = ConfigurationManager.AppSettings["strConn"]; SqlConnection sqlConn = new SqlConnection(strConn); sqlConn.Open(); SqlCommand myCommand = sqlConn.CreateCommand(); SqlTransaction myTrans = sqlConn.BeginTransaction(); myCommand.Transaction = myTrans; try { for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++) { sql = "insert into users(userID, userName, address) values('" + excelDs.Tables[0].Rows[i]["ID"].ToString() + "','" + excelDs.Tables[0].Rows[i]["用户名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["地址"].ToString() + "')"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); } myTrans.Commit(); } catch (Exception ex) { getErrMsg = ex.Message.ToString(); Response.Write(ex.Message.ToString()); myTrans.Rollback(); } finally { sqlConn.Close(); sqlConn = null; } } //返回提示信息 if (getErrMsg == "" || getErrMsg == null) { Response.Write("<script language='Javascript'>alert('导入成功!')</script>"); return; } else { Response.Write("<script language='Javascript'>alert('导入失败!')</script>"); return; } }
第五步,编写按钮“导出“的响应代码
protected void output_Click(object sender, EventArgs e)
{
string strConn = ConfigurationManager.AppSettings["strConn"].ToString();
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(strConn))
{
string sql = "select * from users";
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
sda.Fill(ds, "users");//Fill方法会判断连接是否打开,没有则隐式打开,使用完后关闭
}
StringBuilder strData = new StringBuilder();
strData.AppendLine("用户ID/t"+"用户名/t"+"地址/t");//如excel文件开头ID,当打开时会提示SYLE格式错误,所以尽量不要用ID开头
foreach (DataRow dr in ds.Tables[0].Rows)
{
strData.Append(dr.ItemArray[0].ToString() + "/t");
strData.Append(dr.ItemArray[1].ToString() + "/t");
strData.AppendLine(dr.ItemArray[2].ToString() + "/t");
}
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");
resp.AppendHeader("Content-Disposition", "attachment;filename= output.xls");
resp.ContentType = "application/ms-excel";
resp.Write(strData.ToString());
resp.Flush();
resp.End();
}