var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);
connection.Open();
var command = new SqlCommand();
var query = new StringBuilder("SELECT db_owner.vms.name AS 'Vm Name', db_owner.vms.vmhost AS 'ESX Host', db_owner.host.Cluster, db_owner.vms.UID AS 'vCenter', db_owner.vms.PowerState AS 'Powered ON/OFF', db_owner.vms.Guest AS 'Operating System' FROM db_owner.vms JOIN db_owner.host ON db_owner.vms.VMHost = db_owner.host.Name");
var selectedIndices = objectList.GetSelectedIndices();
switch (selectedIndices.Length)
{
case 0:
break;
case 1:
query.Append(" WHERE vmhost LIKE @vmhost");
command.Parameters.Add("@vmhost", SqlDbType.VarChar, 50).Value = $"%{objectList.SelectedItem.Text}%";
break;
default:
for (var i = 0; i < selectedIndices.Length; i++)
{
var index = selectedIndices[i];
// var paramName = $"@UID";
var paramName = $"@vmhost{index}";
query.Append($" {(i == 0 ? "Where" : "OR")} VMHOST LIKE {paramName}");
command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";
}
break;
}
query.Append(" order by db_owner.host.Cluster ");
command.CommandText = query.ToString();
command.Connection = connection;
SqlDataReader dr = command.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();Alt key while selecting code in VS to remove whitespace from the whole block or the editor in this site will do it for you too. ALWAYS think about what you can do to help us help you, which includes making your code as readable as possible. I have fixed it for you on this occasion.System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
dr.RenderControl(htw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();
dr in post #3 is an SqlDataReader. That doesn't have a RenderControl() method. What is dr in post #6 such that it has an RenderControl() method? Again, as stated above, you need to give us enough code to see what you are doing.dr.RenderControl() above you would simply have something like this pseudo-code:sw.WriteLine("Column1Name, Column2Name, Column3Name");
foreach(var row in rowData)
{
line = string.Format("{0}, {1}, {2}", row[0], row[1], row[2]);
sw.WriteLine(line);
}String.Join() to minimize the amount of code written, but what I should above is to show you things in the simplest way possible.One thing to keep in mind is that that code assumes that none of the data contains field or line delimiters. If that is or might be the case then you need to quote the field values, which also means that you need to escape quotes. In that case,
string.Join is a good option:line = @"""" + string.Join(@""",""", row.Select(f => f.ToString().Replace(@"""", @""""""))) + @"""";那么您最终会得到以下结果：
SqlDataReader as the data source:var names = new List<string>();
for(int i = 0; i < dataReader.FieldCount; i++)
names.Add(dataReader.GetName(i));
writer.WriteLine(string.Join(", ", names));
while (dataReader.Read())
{
var values = new List<string>();
for(int i = 0; < dataReader.FieldCount; i++)
values.Add(dataReader[i].ToString());
writer.WriteLine(string.Join(", ", values));
}DataTable to a file or stream:public static class DataReaderExtensions
{
public static void ToCsv(this DbDataReader source, Stream stream)
{
using (var writer = new StreamWriter(stream))
{
var columnIndexes = Enumerable.Range(0, source.FieldCount).ToArray();
writer.WriteLine(string.Join(",", columnIndexes.Select(source.GetName)));
while (source.Read())
{
writer.WriteLine(@"""" + string.Join(@""",""", columnIndexes.Select(i => source.GetValue(i).ToString().Replace(@"""", @""""""))) + @"""");
}
}
}
public static void ToCsv(this DbDataReader source, string filePath)
{
using (var stream = File.Create(filePath))
{
source.ToCsv(stream);
}
}
}
public static class DataTableExtensions
{
public static void ToCsv(this DataTable source, Stream stream)
{
using (var writer = new StreamWriter(stream))
{
var columns = source.Columns.Cast<DataColumn>().ToArray();
writer.WriteLine(string.Join(",", columns.Select(c => c.ColumnName)));
foreach (DataRow row in source.Rows)
{
writer.WriteLine(@"""" + string.Join(@""",""", columns.Select(c => row[c].ToString().Replace(@"""", @""""""))) + @"""");
}
}
}
public static void ToCsv(this DataTable source, string filePath)
{
using (var stream = File.Create(filePath))
{
source.ToCsv(stream);
}
}
}using (var reader = command.ExecuteReader())
{
reader.ToCsv(filePath);
}adapter.Fill(table);
using (var stream = new MemoryStream())
{
table.ToCsv(stream);
// Use stream here.
} Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Employee.csv");
Response.Charset = "";
Response.ContentType = "application/text";
GridView1.AllowPaging = false;
GridView1.DataBind();
StringBuilder columnbind = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
columnbind.Append(GridView1.Columns[k].HeaderText + ',');
}
columnbind.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
columnbind.Append(GridView1.Rows[i].Cells[k].Text + ',');
}
columnbind.Append("\r\n");
}
Response.Output.Write(columnbind.ToString());
Response.Flush();
Response.End();
