C#读取csv、xls、sql数据库的实现

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

/// <summary>
/// 读取数据并返回数据集
/// </summary>
public class ReadData
{
    private string filename = null;//文件名
    private string filepath = null;//路径
    public ReadData()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 初始化文件路径
    /// </summary>
    /// <param name="cfilepath">文件路径</param>
    /// <param name="cfilename">文件名称</param>
    public ReadData(string cfilepath, string cfilename)
    {
        filename = cfilename;
        filepath = cfilepath;
    }
    /// <summary>
    /// 读取csv格式文件
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <returns></returns>
    public DataSet Readcsv(string sqlstr)
    {
        DataSet ds = new DataSet();
        string strcon = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filepath + ";Extensions=asc,csv,tab,txt";//IMEX=1数据以文本读取
        using (OdbcConnection odbccon = new OdbcConnection(strcon))
        {
            odbccon.Open();
            OdbcCommand odbccom = new OdbcCommand();
            odbccom.Connection = odbccon;
            odbccom.CommandText = sqlstr;
            OdbcDataAdapter odbcda = new OdbcDataAdapter(odbccom);
            odbcda.Fill(ds);
            odbccon.Close();
        }
        return ds;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sqlstr">sql语句读取数据</param>
    /// <param name="c">表的列标</param>
    /// <param name="s1">开始位置</param>
    /// <param name="s2">结束位置</param>
    /// <returns></returns>
    public DataSet Readcsv(string sqlstr, int c, string s1, string s2)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataRow dr;
        DataColumn dc;
        string strcon = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filepath + ";Extensions=asc,csv,tab,txt";//IMEX=1数据以文本读取
        using (OdbcConnection odbccon = new OdbcConnection(strcon))
        {
            odbccon.Open();
            OdbcCommand odbccom = new OdbcCommand();
            odbccom.Connection = odbccon;
            odbccom.CommandText = sqlstr;
            OdbcDataReader odbcdr = odbccom.ExecuteReader();
            for (int i = 0; i < odbcdr.FieldCount; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = odbcdr.GetName(i);
                dc.DataType = odbcdr.GetFieldType(i);
                dt.Columns.Add(dc);
            }
            bool flag = false;
            if (c == 0)
            {
                while (odbcdr.Read())
                {
                    if (Convert.ToDateTime(s1) <= Convert.ToDateTime(odbcdr[c].ToString()))
                    {
                        flag = true;
                        break;
                    }
                }
                if (flag)
                {
                    do
                    {
                        dr = dt.NewRow();
                        for (int j = 0; j < odbcdr.FieldCount; ++j)
                        {
                            dr[j] = odbcdr[j];
                        }
                        dt.Rows.Add(dr);
                        if (Convert.ToDateTime(s2) <= Convert.ToDateTime(odbcdr[c].ToString()))
                        { break; }
                    }
                    while (odbcdr.Read());
                    odbccon.Close();
                    ds.Tables.Add(dt);
                }
            }
            else
            {
                while (odbcdr.Read())
                {
                    if (s1 == odbcdr[c].ToString().Trim())
                    {
                        flag = true;
                        break;
                    }
                }
                if (flag)
                {
                    do
                    {
                        dr = dt.NewRow();
                        for (int j = 0; j < odbcdr.FieldCount; ++j)
                        {
                            dr[j] = odbcdr[j];
                        }
                        dt.Rows.Add(dr);
                        if (s2 == odbcdr[c].ToString().Trim())
                        {
                            break;
                        }
                    }
                    while (odbcdr.Read());
                    odbccon.Close();
                    ds.Tables.Add(dt);
                }
                //Response.Write("<script> alert('输入时间错误！') </script>"); 
            }
        }
        return ds;
    }
    /// <summary>
    /// 读取csv格式文件
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="c"></param>
    /// <param name="s1"></param>
    /// <param name="s2"></param>
    /// <param name="node">节点</param>
    /// <returns></returns>
    public DataSet Readcsv(string sqlstr, int c, string s1, string s2, string node)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataRow dr;
        DataColumn dc;
        string strcon = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filepath + ";Extensions=asc,csv,tab,txt";//IMEX=1数据以文本读取
        using (OdbcConnection odbccon = new OdbcConnection(strcon))
        {
            odbccon.Open();
            OdbcCommand odbccom = new OdbcCommand();
            odbccom.Connection = odbccon;
            odbccom.CommandText = sqlstr;
            OdbcDataReader odbcdr = odbccom.ExecuteReader();
            for (int i = 0; i < odbcdr.FieldCount; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = odbcdr.GetName(i);
                dc.DataType = odbcdr.GetFieldType(i);
                dt.Columns.Add(dc);
            }
            bool flag = false;
            if (c == 0)
            {
                while (odbcdr.Read())
                {
                    if (node == odbcdr[1].ToString().Trim().Remove(5)&&Convert.ToDateTime(s1) <= Convert.ToDateTime(odbcdr[c].ToString()))
                    {
                        flag = true;
                        break;
                    }
                }
                if (flag)
                {
                    do
                    {
                        if (node == odbcdr[1].ToString().Trim().Remove(5))
                        {
                            dr = dt.NewRow();
                            for (int j = 0; j < odbcdr.FieldCount; ++j)
                            {
                                dr[j] = odbcdr[j];
                            }
                            dt.Rows.Add(dr);
                        }
                        if (Convert.ToDateTime(s2) <= Convert.ToDateTime(odbcdr[c].ToString()))
                        { break; }
                    }
                    while (odbcdr.Read());
                    odbccon.Close();
                    ds.Tables.Add(dt);
                }
            }
            else
            {
                while (odbcdr.Read())
                {
                    if (s1 == odbcdr[c].ToString().Trim() && node == odbcdr[1].ToString().Trim().Remove(5))
                    {
                        flag = true;
                        break;
                    }
                }
                if (flag)
                {
                    do
                    {
                        if (node == odbcdr[1].ToString().Trim().Remove(5))
                        {
                            dr = dt.NewRow();
                            for (int j = 0; j < odbcdr.FieldCount; ++j)
                            {
                                dr[j] = odbcdr[j];
                            }
                            dt.Rows.Add(dr);
                        }
                        if (s2 == odbcdr[c].ToString().Trim())
                        {
                            break;
                        }
                    }
                    while (odbcdr.Read());
                    odbccon.Close();
                    ds.Tables.Add(dt);
                }
                //Response.Write("<script> alert('输入时间错误！') </script>"); 
            }
        }
        return ds;
    }
    /// <summary>
    /// 读取xls格式文件
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <returns></returns>
    public DataSet Readxls(string sqlstr)
    {
        DataSet ds = new DataSet();
        string path = Path.Combine(filepath, filename + ".xls");
        string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0";//IMEX=1数据以文本读取
        using (OleDbConnection oledbcon = new OleDbConnection(strcon))
        {
            oledbcon.Open();
            OleDbCommand oledbcom = new OleDbCommand();
            oledbcom.Connection = oledbcon;
            oledbcom.CommandText = sqlstr;
            OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcom);
            oledbda.Fill(ds);
            oledbcon.Close();
        }
        return ds;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sqlstr">sql语句读取数据</param>
    /// <param name="c">表的列标</param>
    /// <param name="s1">开始位置</param>
    /// <param name="s2">结束位置</param>
    /// <returns></returns>
    public DataSet Readxls(string sqlstr, int c, string s1, string s2)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataRow dr;
        DataColumn dc;
        string path = Path.Combine(filepath, filename + ".xls");
        string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0";//IMEX=1数据以文本读取
        using (OleDbConnection odbccon = new OleDbConnection(strcon))
        {
            odbccon.Open();
            OleDbCommand odbccom = new OleDbCommand();
            odbccom.Connection = odbccon;
            odbccom.CommandText = sqlstr;
            OleDbDataReader odbcdr = odbccom.ExecuteReader();
            for (int i = 0; i < odbcdr.FieldCount; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = odbcdr.GetName(i);
                dc.DataType = odbcdr.GetFieldType(i);
                dt.Columns.Add(dc);
            }
            bool flag = false;
            while (odbcdr.Read())
            {
                if (s1 == odbcdr[c].ToString().Trim())
                {
                    flag = true;
                    break;
                }
            }
            if (flag)
            {
                while (odbcdr.Read())
                {
                    dr = dt.NewRow();
                    for (int j = 0; j < odbcdr.FieldCount; ++j)
                    {
                        dr[j] = odbcdr[j];
                    }
                    dt.Rows.Add(dr);
                    if (s2 == odbcdr[c].ToString().Trim())
                    {
                        break;
                    }
                }
                odbccon.Close();
                ds.Tables.Add(dt);
            }
            else { return null; }//Response.Write("<script> alert('输入时间错误！') </script>"); 
        }
        return ds;
    }
    /// <summary>
    /// 读取sql数据库文件
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <returns></returns>
    public DataSet ReadSql(string sqlstr)
    {
        DataSet ds = new DataSet();
        //string strcon = @"uid=sa;password=123456;initial catalog=data;Server=.;Connect Timeout=30";
        string strcon = @"Trusted_connection=SSPI;initial catalog=data;Server=.";
        using (SqlConnection sqlcon = new SqlConnection(strcon))
        {
            sqlcon.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = sqlcon;
            sqlcom.CommandText = sqlstr;
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);
            sqlda.Fill(ds);
            sqlcon.Close();
        }
        return ds;
    }
    /// <summary>
    /// 把数据集中的数据导入sql数据库
    /// </summary>
    /// <param name="ds">导入的数据集</param>
    /// <param name="tablename">表名</param>
    public void DataToSql(DataSet ds, string tablename)
    {
        //string strcon = @"uid=sa;password=123456;initial catalog=data;Server=.;Connect Timeout=30";
        string strcon = @"Trusted_connection=SSPI;initial catalog=data;Server=.";
        using (SqlConnection sqlcon = new SqlConnection(strcon))
        {
            sqlcon.Open();
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.Connection = sqlcon;
            for (int i = 0; i < ds.Tables[0].Rows.Count; ++i)
            {
                sqlcom.CommandText = "insert into " + tablename + " values('" + Convert.ToDateTime(ds.Tables[0].Rows[i][0]) + " ','" + ds.Tables[0].Rows[i][1] + " ','" + ds.Tables[0].Rows[i][2] + " ','" + ds.Tables[0].Rows[i][3] + " ')";
                sqlcom.ExecuteNonQuery();
            }
            sqlcon.Close();
        }
    }
    /// <summary>
    /// 初始化路径
    /// </summary>
    public void clear()
    {
        filename = null;
        filepath = null;
    }
}