一段欣赏的代码共享

 

一段代码共享 包括
1类的定义，典型的面向对象思想的实例
2从xml文件中读取数据
3从excel中读取数据

using System;
using System.Collections;

using System.Data;
using System.Xml;
using System.Data.OleDb;

 

namespace ImportHelper
{

    internal class ImportTableInfo
    {
        private string _name = string.Empty;
        private string _desc = string.Empty;
        private ImportColumnInfoCollection _columnInfos = new ImportColumnInfoCollection();

        public ImportTableInfo()
        {
        }

        public string Name
        {
            get{ return _name;}
            set{ _name = value;}
        }

        public string Description
        {
            get{ return _desc;}
            set{_desc = value;}
        }

        public ImportColumnInfoCollection ColumnInfos
        {
            get{ return _columnInfos;}
            set{_columnInfos = value;}
        }    
    }

    internal class ImportColumnInfo
    {
        private string _name = string.Empty;
        private string _desc = string.Empty;
        private bool _optional = true;

        public ImportColumnInfo(string Name, string Description, string Optional)
        {
            _name = Name;
            _desc = Description;
            try
            {
                _optional = bool.Parse(Optional);
            }
            catch
            {
                _optional = false;
            }
        }

        public string Name
        {
            get{ return _name;}
            set{ _name = value;}
        }

        public string Description
        {
            get{ return _desc;}
            set{_desc = value;}
        }

        public bool Optional
        {
            get{ return _optional;}
            set{_optional = value;}
        }
    }


    internal class ImportColumnInfoCollection:System.Collections.CollectionBase
    {
        public ImportColumnInfoCollection()
        {
        }

        public void Add(ImportColumnInfo columnInfo)
        {
            this.List.Add(columnInfo);
        }

        public ImportColumnInfo this[int i]
        {
            get
            {
                return (ImportColumnInfo)List[i];
            }
            set
            {
                List[i] = value;
            }
        }
    
        public void Remove(ImportColumnInfo columnInfo)
        {
            int i= this.IndexOf(columnInfo);
            if(i>0 && i<this.Count)
            {
                this.RemoveAt(i);
            }
        }

        public int IndexOf(ImportColumnInfo columnInfo)
        {
            for(int i=0;i<this.Count;i++)
            {
                ImportColumnInfo columnInf = this[i];
            
                if(columnInf == columnInfo || (columnInfo != null && columnInf != null && columnInf.Name == columnInfo.Name))
                {
                    return i;
                }
            }
            return -1;            
        }

        
    }

    /**//// <summary>
    /// 模板的辅助类
    /// </summary>
    internal class TemplateHelper
    {
        public TemplateHelper()
        {
            
        }

        //获得填充数据的ImportTableInfo
        public static ImportTableInfo GetImportTableInfo(string tblName)
        {
            if(tblName==null||tblName.Trim()=="")
            {
                throw new ApplicationException("please transfer param!");        
            }

            //获取列集合  读取xml文件
            string strDicPath=System.Configuration.ConfigurationSettings.AppSettings["ImportDataTemplate"].Trim();    //从web.config文件中获取xml文件所在的目录
            string strXmlPath=System.IO.Path.Combine(strDicPath,tblName+".xml");    //模板均以导入目标表 表名来命名
            ImportColumnInfoCollection columnColl=new ImportColumnInfoCollection();
            XmlDocument xmlDoc=new XmlDocument();
            xmlDoc.Load(strXmlPath);
            XmlNode baseNode=xmlDoc.DocumentElement.SelectSingleNode("/Table[@Name='"+tblName.Trim()+"']");
            XmlNodeList nodeList=baseNode.SelectNodes("./Column");
            foreach(XmlNode node in nodeList)
            {
                if(node!=null)
                {
                    columnColl.Add(new ImportColumnInfo(node.Attributes["Name"].InnerText.Trim(),node.Attributes["Description"].InnerText.Trim(),node.Attributes["Optional"].InnerText.Trim()));
                }
            }

            ImportTableInfo tblInfo=new ImportTableInfo();
            tblInfo.Name=tblName.Trim();
            tblInfo.ColumnInfos=columnColl;
            //tblInfo.Description=desc;

            return tblInfo;
        }
    }
    
    /**//// <summary>
    /// 从Excel中获取数据,并以DataSet的方式返回给调用者
    /// Date:2005.01.22
    /// </summary>
    public class ExcelGateway
    {

        private System.Data.OleDb.OleDbConnection excelConnection; //excelConnection连接

        private string excelConnectionString;//连接字符串

        private System.Data.OleDb.OleDbDataAdapter excelAdapter; //执行SQL

        private System.Data.OleDb.OleDbCommand excelCommand;    //执行SQL

        private DataSet dataSet;//数据集

 

        /**//// <summary>
        /// 构造函数,实例化的时候创建一个excelConnection对象
        /// </summary>
        /// <param name="strFilePath">Excel的存放路径</param>
        /// <param name="isQuery">是否对Excel做只读操作</param>
        public ExcelGateway(string strFilePath,bool isQuery)
        {

            //连接字符串

            //查询时
            if(isQuery)

            {

                excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

            }
                //插入更新时
            else

            {

                excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties=Excel 8.0";

            }

            //打开连接
            excelConnection=new OleDbConnection(excelConnectionString);

        }

 

        /**//// <summary>
        /// 打开Excel数据库连接
        /// </summary>
        public void Open()

        {

            //如果当前对象存在
            if(excelConnection!=null)
            {

                excelConnection.Open();

            }

        }

 

        /**//// <summary>

        /// 关闭Excel数据库连接

        /// </summary>

        public void Close()
        {

            //如果当前对象存在
            if(excelConnection!=null)
            {
                excelConnection.Close();

            }

                            

        }

        /**//// <summary>
        /// 以DataSet的方式返回Excel中的数据
        /// </summary>
        /// <param name="sql">查询的SQL</param>
        /// <returns>返回结果集</returns>
        public DataSet ExcuteSqlForDst(string sql)
        {

            
                //实例化Adapter类
                excelAdapter=new OleDbDataAdapter(sql,excelConnection);
                //数据集
                dataSet=new DataSet();
                //填充dataSet
                excelAdapter.Fill(dataSet);

                //返回dataSet
                return(dataSet);            

        }

 

        /**//// <summary>
        /// 通过传递参数的方式,执行Sql语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <param name="SqlParameterValue">参数值</param>
        /// <returns></returns>
        public DataSet ExcuteParameterSqlForDst(string sql,string[] SqlParameter,object[] SqlParameterValue)
        {
                         

            DataSet ds=new DataSet();

            excelAdapter=new OleDbDataAdapter();

            if(SqlParameter.Length==SqlParameterValue.Length)
            {
                excelCommand=new OleDbCommand();

                //对于每一个参数,直接匹配

                for(int i=0;i<SqlParameter.Length;i++)
                {
                    OleDbParameter para=new OleDbParameter();

                    excelCommand.Connection=excelConnection;//数据库连接

                    para.ParameterName=SqlParameter[i];   //参数名

                    para.Value=SqlParameterValue[i];    //参数值

                    excelCommand.Parameters.Add(para);   //增加参数

                }

                try
                {

                    excelAdapter.SelectCommand=excelCommand;

                    excelAdapter.Fill(ds);

                    return(ds);

                }
                catch
                {
                    throw new Exception("获取数据出错!");
                }

            }

            else
            {
                throw new Exception("给定的参数和参数值不匹配！");
            }
        }

 
        /**//// <summary>
        /// 执行不需要返回值的SQL语句,比如插入,删除操作
        /// 如不能正确执行,回滚操作,并抛出失败异常
        /// 如果回滚失败,抛出回滚失败异常
        /// </summary>
        /// <param name="sql">待执行的SQL</param>
        public void ExecuteNoquery(string sql)
        {
            try
            {

                //实例化Adapter类
                excelCommand=new OleDbCommand(sql,excelConnection);

                excelCommand.Transaction=excelConnection.BeginTransaction();
                excelCommand.ExecuteNonQuery();


                try
                {

                    excelCommand.Transaction.Commit();
                }
                catch
                {
                    try
                    {
                        excelCommand.Transaction.Rollback();
                    }

                    catch
                    {
                        throw new Exception("数据库事务回滚失败！");
                    }

                }

                                     

            }

            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

    }

}

附：
1读取的xml文件

<?xml version="1.0" encoding="utf-8" ?> 
<!--配置注意事项
    1,<Table Name="TblName"> 中Name的值为导入数据的目标表的表名
    2,<Column Name="Account" Description="帐号" Optional="False" /> 为导入数据的列，Name 是字段名，Description是描述,Optional为True该列数据是可为空
-->
<Table Name="TblName">
    <Column Name="Account" Description="帐号" Optional="False" />
    <Column Name="UserName" Description="用户名" Optional="False" />
    <Column Name="Password" Description="密码" Optional="False" />
    <Column Name="Department" Description="部门" Optional="False" />
</Table>

2使用ExcelGateway类的代码

private void lbtnImport_Click(object sender, System.EventArgs e)
        {
            string suffix = System.IO.Path.GetExtension(this.UpLoadFile.PostedFile.FileName);        //UpLoadFile是file类型html控件，取得读取文件的扩展名，以判断是否是excel文件
            if(suffix.Trim().ToUpper() != ".XLS" && suffix.Trim().ToUpper() != ".XLT")
            {
                Function.PageAlert("文件类型不正确");        //公用方法弹出提示信息
                return;
            }
            string onlyFileName = System.IO.Path.GetFileName(this.UpLoadFile.PostedFile.FileName);

            string uploadFilePath = System.Configuration.ConfigurationSettings.AppSettings["UploadFilePath"];//文件上传到服务器的目录            if(!System.IO.Directory.Exists(uploadFilePath))
            {
                System.IO.Directory.CreateDirectory(uploadFilePath);
            }
            string savedFilePath = System.IO.Path.Combine(uploadFilePath,onlyFileName);
            this.UpLoadFile.PostedFile.SaveAs(savedFilePath);    //把excel文件上传到服务器

            string[] sheetNames = this.txtSheetName.Text.Trim().Split(new char[',']);//excel的sheet

            try
            {
                string sql="";
                ExcelGateway excelG = new ExcelGateway(savedFilePath,true);
            
                excelG.Open();
                                
                try
                {
                    for(int i=0;i<sheetNames.Length;i++)
                    {
                        sql=string.Format("SELECT * FROM [{0}$]",sheetNames[i]);                    

                        DataSet ds = excelG.ExcuteSqlForDst(sql);//获取到数据填充到DataSet
                       
                        
                    }
                    
            //        InvbitWrapper bitWrapper = new InvbitWrapper();
            //        bitWrapper.ImportInvBitData(ds);
                }
                finally
                {
                    excelG.Close();
                }
            }
            catch(Exception ex)
            {
                Function.PageAlert(ex.ToString());
            }
        }