Sampson-Li
Sampson.Li学习经验总结博客 学历代表过去,能力代表现在.学习力代表未来!

遐迩之余.做了一下数据导出.因为数据库有许多数据木必要导出来.所以就做了,选择表,动态根据表选择对应的列.然后导出.有些地方需要改进...只是练下手.有时间再改吧

下面直接贴码

首先页面

 <form id="form1" runat="server">
<div>//这里声明一下.其实可以做成无刷新的,使用jquery+ajax. 有空再改吧.
选择表名:<asp:DropDownList runat="server" ID="ddlTable" AutoPostBack="True" OnSelectedIndexChanged="ddlTable_SelectedIndexChanged">
</asp:DropDownList>
</div>
<div>
<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField HeaderText="勾选列">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Text="<%#Container.DataItem%>" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="列名">
<ItemTemplate>
<%#Container.DataItem%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
</form>

后台cs

  protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{ //这里sqlhelper是我的数据库操作类.就不贴出了.ExecuteReader(sql语句是查询数据库里的所有表.这个在我博客的另一篇随笔有详细介绍)
SqlDataReader dr = SqlHelper.ExecuteReader("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
while (dr.Read())
{
this.ddlTable.Items.Add(new ListItem() { Text = dr.GetString(0), Value = dr.GetString(0) });
}
this.ddlTable.DataBind();
}
}
     //根据选择的表.去找出相应的列.这里有点二了.竟然用存储过程..涐晕了.以后再改
protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
{
string tname = this.ddlTable.SelectedValue;
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@tablename", SqlDbType.NVarChar) { Value =tname }
};

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString))
{

IList<string> list = new List<string>();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "test";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parms);
conn.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(reader.GetString(0));
}
}
conn.Close();
this.Gridview1.DataSource = list;
this.Gridview1.DataBind();
}
}
      //这里就是导出txt
protected void Button1_Click(object sender, EventArgs e)
{
string sb="";
foreach (GridViewRow item in Gridview1.Rows)
{
CheckBox ck = item.FindControl("CheckBox1") as CheckBox;
if (ck.Checked)
{
sb += ck.Text + ',';
}
}
if (!string.IsNullOrEmpty(sb))
{
string a = sb.Substring(0,sb.Length- 1);
string sql = "select " + a + " from " + this.ddlTable.SelectedValue;
DataSet ds = SqlHelper.ExecuteDataset(sql);
DataTable dt = ds.Tables[0];
string path=Server.MapPath("/")+this.ddlTable.SelectedValue+".txt";
if (!File.Exists(path))
{
CreateToFile(this.ddlTable.SelectedValue);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j< dt.Columns.Count; j++)
{
File.AppendAllText(path, dt.Rows[i][j].ToString()+"$DD$", Encoding.UTF8);
}
File.AppendAllText(path,"$RR$", Encoding.UTF8);
}
}

}
public void CreateToFile(string name)
{
string FileName = Server.MapPath("/")+name+".txt";
using (StreamWriter SW = new StreamWriter(FileName, true, Encoding.UTF8))
{
SW.AutoFlush = true;
SW.Write(string.Empty);
SW.Close();
}
}

这里就把存储过程贴出来,其实可以不用存储过程的...希望大家要是使用的时候可以直接用sql

 1 USE [FBBlock]
2 GO
3 /****** Object: StoredProcedure [dbo].[test] Script Date: 12/05/2011 09:49:42 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER procedure [dbo].[test]
9 @tablename nvarchar(50)
10 as
11 select name from syscolumns where id=object_id(''+@tablename+'')





posted on 2011-12-05 09:52  Sampson  阅读(536)  评论(0编辑  收藏  举报