linq to csv_返回基础-保持简单并增强嗅觉-从Linq到CSV

linq to csv

I was working with a friend recently on a side thing they were doing. They wanted to create an "Export" function for some small bit of data and start it from their website.

我最近正和一个朋友一起做他们正在做的附带事情。 他们想为少量数据创建一个“导出”功能，然后从他们的网站启动它。

• You'd hit a URL after logging in
  您登录后点击了网址
• Some data would come out of a database
  一些数据会从数据库中出来
• You'd get a .CSV file downloaded
  您将下载一个.CSV文件
• You could open it in Excel or whatever.
  您可以在Excel或任何其他方式中打开它。

I spoke to my friend and they said it was cool to share their code for this post. This post isn't meant to be a WTF or OMG look at that code, as is it meant to talk about some of the underlying issues. There's few things going on here and it's not all their fault, but it smells.

我和我的朋友聊天，他们说分享这篇文章的代码很酷。 这篇文章并不是要让WTF或OMG查看该代码，而是要谈论一些潜在的问题。 这里发生的事情很少，这不是他们的全部错，而是闻到了。

• They are using a Page when a IHttpHandler will do.

  当IHttpHandler可以使用时，他们正在使用Page。

  • Not a huge deal, but there's overhead in making a Page, and they're not using any of the things that make a Page a Page. The call to ClearContents is an attempt at telling the Page to back off. It's easier to just not have a page.
    没什么大不了的，但是制作Page的开销很大，而且他们没有使用任何使Page成为Page的东西。 对ClearContents的调用是试图告诉Page退回。 没有页面会更容易。

• They're "thrashing" a bit in the Page_Load, basically "programming by coincidence."

  它们在Page_Load中有点“颠簸”，基本上是“巧合编程”。

  • They're not 100% sure of what needs to be done, so they're doing everything until it works. Even setting defaults many times or calling methods that set properties and they setting those properties again.
    他们不是100％不确定需要做什么，因此他们会尽一切努力直到可行为止。 甚至多次设置默认值或调用设置属性的方法，然后它们再次设置这些属性。
  • This means a few things. First, HTTP is subtle. Second, the Response APIs are confusing (less so in .NET 4) and it's easy to do the same thing in two ways.
    这意味着几件事。 首先，HTTP很微妙。 其次，Response API令人困惑(在.NET 4中更是如此)，并且可以通过两种方式轻松完成同一件事。

  They're "thrashing" a bit in the Page_Load, basically "programming by coincidence."

  它们在Page_Load中有点“颠簸”，基本上是“巧合编程”。

• They're not using using() or IDisposable

  他们没有使用using()或IDisposable

  • They're cleaning up MemoryStreams and StreamWriters, but if an exception happens, things'll get cleaned up whenever. It's not tight in a "cleaning up after yourself" deterministic (when it needs to be) kind of way.
    他们正在清理MemoryStreams和StreamWriters，但是如果发生异常，则将在任何时候清理它们。 确定性地(必要时)采用“自己清理”的方法并不严格。
  • They're calling Flush() when it's not really needed, again programming by coincidence. "I think this needs to be done and it doesn't break..."
    他们在不需要的时候调用Flush()，再次巧合地编程。 “我认为这是必须做的，而且不会中断……”

• Old school data access

  老学校数据访问

  • Not bad, pre se, but it could be easier to write and easier to read. DataAdapters, DataSets, are a hard way to do data access once you've used Linq to SQL, EF or NHibernate.
    本质上还不错，但是可能更容易编写和阅读。 一旦使用Linq到SQL，EF或NHibernate，DataAdapters，DataSets是进行数据访问的一种困难方法。

• Re-Throwing an Exception via "throw ex"

  通过“ throw ex”重新抛出异常

  • When you want to re-throw an exception, ALWAYS just throw; or you'll lose your current call stack and it'll be hard to debug your code.
    当您想重新抛出异常时，总是抛出异常。 否则您将丢失当前的调用堆栈，并且很难调试代码。

• Not reusable at all

  完全不可重用

  • Now, reuse isn't the end-all, but it's nice. If this programmer wants different kinds of exports, they'll need to extract a lot from the spaghetti.
    现在，重用并不是最终目的，但是很好。 如果该程序员想要不同种类的出口，则需要从意大利面条中提取很多东西。

Here's what they came up with first.

这是他们首先想到的。

Note that this code is not ideal. This is the before. Don't use it. 

请注意，此代码并不理想。 这是以前。 不要使用它。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace FooFoo
{
    public partial class Download : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            System.IO.MemoryStream ms = CreateMemoryFile();

            byte[] byteArray = ms.ToArray();
            ms.Flush();
            ms.Close();

            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.Cookies.Clear();
            Response.Cache.SetCacheability(HttpCacheability.Private);
            Response.CacheControl = "private";
            Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
            Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
            Response.AppendHeader("Pragma", "cache");
            Response.AppendHeader("Expires", "60");
            Response.ContentType = "text/comma-separated-values";
            Response.AddHeader("Content-Disposition", "attachment; filename=FooFoo.csv");
            Response.AddHeader("Content-Length", byteArray.Length.ToString());
            Response.BinaryWrite(byteArray);
        }

        public System.IO.MemoryStream CreateMemoryFile()
        {
            MemoryStream ReturnStream = new MemoryStream();

            try
            {
                string strConn = ConfigurationManager.ConnectionStrings["FooFooConnectionString"].ToString();
                SqlConnection conn = new SqlConnection(strConn);
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [FooFoo] ORDER BY id ASC", conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "FooFoo");
                DataTable dt = ds.Tables["FooFoo"];

                //Create a streamwriter to write to the memory stream
                StreamWriter sw = new StreamWriter(ReturnStream);

                int iColCount = dt.Columns.Count;

                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write(dt.Columns[i]);
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }

                sw.WriteLine();
                int intRows = dt.Rows.Count;

                // Now write all the rows.
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {

                        if (!Convert.IsDBNull(dr[i]))
                        {
                            string str = String.Format("\"{0:c}\"", dr[i].ToString()).Replace("\r\n", " ");
                            sw.Write(str);
                        }
                        else
                        {
                            sw.Write("");
                        }

                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.WriteLine();
                }

                sw.Flush();
                sw.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return ReturnStream;
        }

    }
}

I don't claim to be a good programmer, but I do OK. I went over my concerns with my friend, and suggested first an HttpHandler. I started with Phil's basic abstract HttpHandler (based on my super basic HttpHandler boilerplate). I could have certainly done by just implementing IHttpHandler, but I like this way. They're about the same # of lines of code. The important part is in HandleRequest (or ProcessRequest).

我并没有声称自己是一名优秀的程序员，但我可以。 我和朋友一起解决了我的问题，并建议了一个HttpHandler。 我从Phil的基本抽象HttpHandler (基于我的超级基本HttpHandler样板)开始。 我当然可以只实现IHttpHandler，但是我喜欢这种方式。 它们大约是相同的代码行数。 重要的部分在HandleRequest(或ProcessRequest)中。

namespace FooFoo
{
    public class ExportHandler : BaseHttpHandler
    {
        public override void HandleRequest(HttpContext context)
        {
            context.Response.AddHeader("Content-Disposition", "attachment; filename=FooFoo.csv");
            context.Response.Cache.SetCacheability(HttpCacheability.Private);

            var dc = new FooFooDataContext();
            string result = dc.FooFooTable.ToCsv();
            context.Response.Write(result);
        }

        public override bool RequiresAuthentication
        {
            get { return true; }
        }

        public override string ContentMimeType
        {
            get { return "text/comma-separated-values"; }
        }

        public override bool ValidateParameters(HttpContext context)
        {
            return true;
        }
    }
}

At the time I wrote this, I was writing how I wished the code would look. I didn't have a "ToCsv()" method, but I was showing my friend how I though the could should be separated. Even better if there was a clean DAL (Data Access Layer) and Business Layer along with a service for turning things into CSV, but this isn't too bad. ToCsv() in this example is a theoretical extension method to take an IEnumerable of something and output it as CSV. I started writing it, but then decided to Google with Bing, and found a decent direction to start with at Mike Hadlow's blog. He didn't include all the code in the post, but it saved me some typing, so thanks Mike!

在写这篇文章的时候，我正在写我希望代码看起来如何。 我没有“ ToCsv()”方法，但我向朋友展示了我应该如何分隔罐头。 如果有一个干净的DAL(数据访问层)和业务层，以及将事物转换为CSV的服务，那就更好了，但这还不错。 此示例中的ToCsv()是一种理论扩展方法，用于获取IEnumerable并将其输出为CSV。 我开始编写它，但是后来决定与Bing一起去Google，并在Mike Hadlow的博客中找到了一个不错的开始。 他没有在帖子中包含所有代码，但是它为我节省了一些打字时间，所以谢谢Mike！

namespace FooFoo
{
    public static class LinqToCSV
    {
        public static string ToCsv<T>(this IEnumerable<T> items)
            where T : class
        {
            var csvBuilder = new StringBuilder();
            var properties = typeof(T).GetProperties();
            foreach (T item in items)
            {
                string line = string.Join(",",properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
                csvBuilder.AppendLine(line);
            }
            return csvBuilder.ToString();
        }

        private static string ToCsvValue<T>(this T item)
        {
            if(item == null) return "\"\"";

            if (item is string)
            {
                return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
            }
            double dummy;
            if (double.TryParse(item.ToString(), out dummy))
            {
                return string.Format("{0}", item);
            }
            return string.Format("\"{0}\"", item);
        }
    }
}

This creates an extension method that lets me call something.toCsv() on anything IEnumerable. It'll spin through the properties (yes, I know that could have been a LINQ statement also, but I like a nice ForEach sometimes. Feel free to fix this up in the comments! ;) ) and build up the Comma Separated Values.

这创建了一个扩展方法，使我可以在任何IEnumerable上调用something.toCsv()。 它将遍历属性(是的，我知道也可能是LINQ语句，但有时我喜欢一个不错的ForEach。请在注释中随时进行修正！;))并建立逗号分隔值。

At some point, it really should format Dates as {0:u} but as of now, it works identically as the before code and attempts to rectify most of the issues brought up. Of course, one could take something like this as far and make it as robust as they like.

在某些时候，它确实应该将Dates的格式设置为{0：u}，但到目前为止，它的功能与以前的代码相同，并试图纠正大多数出现的问题。 当然，可以采取这样的措施，使其尽可能强大。

As Mike points out, you can also do little projections to control the output:

正如Mike所指出的，您还可以做一些小的投影来控制输出：

string csv = things.Select(t => new { Id = t.Id, Name = t.Name, Date = t.Date, Child = t.Child.Name }).AsCsv();

Your thoughts, Dear Reader?

您的想法，亲爱的读者？

Related Links

相关链接

• Good Exception Management Rules of Thumb

  良好异常管理经验法则

• Cargo-cult programming

  货物崇拜编程

• LINQ to CSV using DynamicObject

  LINQ to CSV使用DynamicObject

• Mike Hadlow's LINQ ToCsv()

  Mike Hadlow的LINQ ToCsv()

• Recommended: Open Source "FileHelpers" for importing and exporting Fixed and Delimited files with .NET from Marco Meli

  推荐：开源“ FileHelpers”，用于从Marco Meli用.NET导入和导出固定和定界文件

• Eric White on Linq to Text and Linq to CSV

  Eric White在Linq上发送文本和Linq到CSV

• Reading data FROM CSV files using LINQ

  使用LINQ从CSV文件读取数据

• Think Linq 

  想想Linq

翻译自: https://www.hanselman.com/blog/back-to-basics-keep-it-simple-and-develop-your-sense-of-smell-from-linq-to-csv

linq to csv