. Net select database table \ column to export data

In addition. export data. because the database has a lot of data to export. select a table and dynamically select the corresponding columns Based on the table. then export. some areas need to be improved... it's just a matter of practice. change it later.

Paste the following code directly

First page

<Form id = "form1" runat = "server">
<Div> // declare it here. In fact, it can be used as a refreshing method. Use jquery + ajax. Change it if you have time.
Select a table name: <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 = "select columns">
<ItemTemplate>
<Asp: CheckBox ID = "CheckBox1" runat = "server" Text = "<% # Container. DataItem %>"/>
</ItemTemplate>
</Asp: TemplateField>
<Asp: TemplateField HeaderText = "column name">
<ItemTemplate>
<% # Container. DataItem %>
</ItemTemplate>
</Asp: TemplateField>
</Columns>
</Asp: GridView>
</Div>
<Asp: Button ID = "Button1" runat = "server" Text = "Button" OnClick = "button#click"/>
</Form>

Backend cs

Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{// Here, sqlhelper is my database operation class. It will not be posted. ExecuteReader (the SQL statement is used to query all tables in the database. This is another article on my blog with a detailed introduction)
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 ();
}
}

// Find the corresponding columns Based on the selected table. This is a bit of a problem. The stored procedure is used. The system will be dizzy.
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 ();
}
}

// Here is the txt Export
Protected void button#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 ();
}
}

Here we will post the stored procedure. In fact, you don't need to use the stored procedure. I hope you can directly use SQL when using it.

 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+'')