首页 > 代码库 > 原创 Datareader 导出为csv文件方法

原创 Datareader 导出为csv文件方法

DataReader 是游标只读数据, 如果是大数据导出,用Datatable 将耗费巨大内存资源。因为Datatable 其实就是内存中的一个数据表

代码如下

/// <summary>        /// SqlDataReader 生成csv文件        /// </summary>        /// <param name="fileNameCsv">文件名(包含文件路径)</param>        /// <param name="dr">数据表</param>        /// <param name="hideColumnNames">要隐藏的列名</param>        /// <param name="encoding">编码【默认:GB2312】</param>        /// <returns></returns>        protected static void DownloadCsv(string fileNameCsv, string fileNameZip, SqlDataReader dr, string[] hideColumnNames, string encoding="gb2312")        {            if (dr != null)            {                try                {                    Dictionary<string, string> hideCol = new Dictionary<string, string>();                    foreach (string item in hideColumnNames)                    {                        hideCol.Add(item.Trim(), item);                    }                    if (!File.Exists(fileNameCsv))                    {                        using (StreamWriter sw = new StreamWriter(fileNameCsv, false, Encoding.GetEncoding(encoding)))                        {                            string fieldName = string.Empty;                            StringBuilder sb = new StringBuilder();                            //写入表头                            for (int i = 0; i < dr.FieldCount; i++)                            {                                fieldName = dr.GetName(i);                                if (hideCol.ContainsKey(fieldName) == true || fieldName == "")                                    continue;                                else                                {                                    sb.Append(fieldName);                                                                      sb.Append(",");                                                                    }                            }                            sw.WriteLine(sb.ToString().TrimEnd(‘,‘));                            sb.Clear();                            //写入导出数据                            while (dr.Read())                            {                                //遍历每一列                                for (int i = 0; i < dr.FieldCount; i++)                                {                                    fieldName = dr.GetName(i);                                    if (hideCol.ContainsKey(fieldName) == true || fieldName == "")                                        continue;                                    else                                    {                                        if (!Convert.IsDBNull(dr[i]))                                        {                                            string content = string.Format("\"{0}\"", dr[i].ToString().Replace("\"", "\"\""));                                            sb.Append(content);                                        }                                        else                                        {                                            sb.Append("\"\"");                                        }                                                                                sb.Append(",");                                                                           }                                }                                sw.WriteLine(sb.ToString().TrimEnd(‘,‘));                                sb.Clear();//每行数据结束清空已经写入文本的数据                            }                        }                    }                    dr.Close();                    string filePath = fileNameCsv.Substring(0, fileNameCsv.LastIndexOf(@"\")+1);//+1 包含最后一个斜杠                    CreateZipFile(filePath, fileNameZip);//创建压缩文件                }                catch(Exception ex)                {                    if(!dr.IsClosed)                    {                        dr.Close();                    }                    LogHelper.WriteErrorLog("ProcessDocument.DownloadCsv", ex.Message);                }            }        }