-------------------前台
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click"
Text="将数据库中数据导出到文本文件中" />
<br />
<br />
<asp:Button ID="Button2" runat="server" οnclick="Button2_Click"
Text="将文本文件导入到数据库中" Width="291px" />
</div>
</form>
</body>
</html>
------------------后台
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
namespace Sql注入漏洞攻击
{
public partial class 数据导入到出 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string constr = "data source=.;initial catalog=UserDB1;User id=sa;password=admin";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select * from T_Users";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
//1判断是否查询出数据
if (reader.HasRows)
{
//有数据被查询出
//当有数据的时候,就创建文本文件,并向其中写入数据。
using (StreamWriter sw = new StreamWriter(@"E:\2012netClassPtractice\ADO详解\Sql注入漏洞攻击\tblUsers.txt"))
{
while (reader.Read())
{
object objUserName = reader.GetValue(1);
object objPassword = reader.GetValue(2);
string line = string.Format("{0},{1}", objUserName, objPassword);
sw.WriteLine(line);
}
Response.Write("导出完毕!");
}
}
else
{
Response.Write("数据表中没有数据,没有导出任何数据!");
}
}
}
}
}
protected void Button2_Click(object sender, EventArgs e)
{
//1把文本文件导入到数据库的数据表中,先读取文本文件,然后再插入到数据库的数据表中。
using ( StreamReader sr = new StreamReader(@"E:\2012netClassPtractice\ADO详解\Sql注入漏洞攻击\tblUsers.txt"))
{
string constr = "data source=.;initial catalog=UserDB1;User id=sa;password=admin";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "insert into T_Users (FuserName,Fpassword) values(@username,@password)";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
//解决方案2,在循环外定义 参数变量,在循环内赋值。
SqlParameter p1 = new SqlParameter("@username", System.Data.SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@password", System.Data.SqlDbType.VarChar);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
string[] columns = line.Split(',');
#region 测试是否读出了文本文件
//Response.Write(columns[0]+" "+columns[1]+"<br>");
#endregion
#region 将读出的内容放到数据库中
//SqlParameter p1 = new SqlParameter("@username",columns[0]);
//SqlParameter p2 = new SqlParameter("@password", columns[1]);
con.Open();
p1.Value = columns[0];
p2.Value = columns[1];
cmd.ExecuteNonQuery();
//cmd.Parameters.Clear();//解决方法1。
con.Close();
#endregion
}
Response.Write("导入到数据库已完毕");
}
}
}
}
}
}