.net编程

开发平台：
C#

MatCode.cs：源码内容
							using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using com.etong.BusinessRule.Zwf;
using com.etong.SqlDataConnect;
namespace com.etong.BusinessRule.MatCode
{
    public class MatCode
    {
        private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);       
        public MatCode()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /*************************************************************************************************************  
                                             * Module： 物资代码管理
                                             * Desc： 添加、更新、删除、查询记录
                                             * Author：徐秀梅
                                             * Date： 2007-03
        **************************************************************************************************************/
        /// <summary>
        /// 添加物资代码记录
        /// </summary>
        /// <returns>返回一bool类型值</returns>
        public bool MatCodeAdd(string matcode,string matname,string matdecs)
        {
            QueryParameterCollection Params = new QueryParameterCollection();
            Params.Clear();          
            Params.Add("@matcode", matcode);
            Params.Add("@matname", matname);
            Params.Add("@matdecs", matdecs);
            string sql = "insert into tb_sys_MatCode([MatCode],[MatName],[MatDecs]) values (@matcode,@matname,@matdecs)";
            MSDA.Open();
            NewObj obj=new NewObj();
            bool returnvalue = obj.insert(sql, Params, CommandType.Text);           
            MSDA.Close();
            return returnvalue; 
        }
        /// <summary>
        ///更新物资代码记录
        /// </summary>
        /// <returns>返回一bool类型值</returns>
        public bool MatCodeUpdate(int id, string matcode, string matname, string matdecs)
        {
            QueryParameterCollection Params = new QueryParameterCollection();
            Params.Clear();
            Params.Add("@id", id);
            Params.Add("@matcode", matcode);
            Params.Add("@matname", matname);
            Params.Add("@matdecs", matdecs);
            string sql = "update tb_sys_MatCode set [MatCode]=@matcode,[MatName]=@matname,[MatDecs]=@matdecs where [ID]=@id";
            MSDA.Open();
            NewObj obj = new NewObj();
            bool returnvalue = obj.Update(sql, Params, CommandType.Text);
            MSDA.Close();
            return returnvalue;
        }
        /// <summary>
        ///删除物资代码记录
        /// </summary>        
        public void MatCodeDelete(int id)
        {
            string sql = "delete from tb_sys_MatCode where [ID]='" + id + "'";
            MSDA.Open();
            MSDA.ExecuteNonQuery(CommandType.Text, sql, null);
            MSDA.Close();
        }
        /// <summary>
        /// 查找指定或全部物资代码记录
        /// </summary>
        /// <param name="SearchTxt">查找内容</param>
        /// <param name="i">查找关键字,例:1:物资代码；2:物资代码名称；3:物资代码ID;非1,2,3且SearchTxt为空:查找全部</param>
        public DataView MatCodeSearch(string SearchTxt, string i)
        {
            string sql= "select [ID],[MatCode],[MatName],[MatDecs] from tb_sys_MatCode";
            if (i == "1" && SearchTxt != "")
            {
               sql = sql + "  where [MatCode]='" + SearchTxt + "'";
            }
            if (i == "2" && SearchTxt != "")
            {
               sql = sql+ " where [MatName]='" + SearchTxt + "'";
            }
            if (i == "3" && SearchTxt != "")
            {
                sql = sql + " where [ID]='" + SearchTxt + "'";
            }  
            DataSet ds = new DataSet();
            MSDA.Open();
            NewObj newobject = new NewObj();
           
            DataView dv = newobject.Search(sql, null, CommandType.Text).Tables[0].DefaultView;
           
            MSDA.Close();
            return dv; 
        }
        /// <summary>
        /// 判断物资代码是否符合要求
        /// </summary>
        /// <param name="SearchTxt">物资代码</param>  
        ///  /// <returns>返回一int类型值,返回值-2:物资代码没校验码;-1:物资代码错误;0:物资代码校验码为0;其他:物资代码啊校验码为返回值</returns>
        public bool MatCodeCheck(string matcode)
        {
            bool flag = false;
            char[] str=null;
            int len = matcode.Length;
            for (int i = 0; i < len; i++)
            {
                str = matcode.ToCharArray();
            }
            if (len<=5|| len == 8 || len == 10)
            {
              flag = true;
            }
            if (len == 14)
            {
                //判断校验位是否正确
                int checkid=0;
                for (int i =12; i >= 0; i--,i--)
                {                  
                    checkid +=int.Parse( str[i].ToString());                     
                }
                checkid = checkid * 3;
                for (int i = 11; i >= 0; i--,i--)
                {
                    checkid +=int.Parse( str[i].ToString()); 
                }
                char[] checkstr = null;
                int len2 = checkid.ToString().Length;
                for (int i = 0; i < len2; i++)
                {
                    checkstr =checkid.ToString().ToCharArray();
                }                
                if (Convert.ToInt32( checkstr[len2-1].ToString()) != 0)
                {
                    checkid = 10 - Convert.ToInt32(checkstr[len2-1].ToString());
                }
                else
                    checkid = 0;
                if (int.Parse(str[len-1].ToString()) == checkid)
                    flag = true;
                
            }
            return flag;
 
        }
    }
 public class InExcel
    {
        private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
        public InExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /*************************************************************************************************************  
                                             * Module： Excel表格导入
                                             * Desc： 导入
                                             * Author：徐秀梅
                                             * Date： 2007-04
        **************************************************************************************************************/
        /// <summary>
        /// 导入Excel表格原始数据
        /// </summary>
        /// <returns>返回一bool类型值</returns>
        public string inexcel(string sFile)
        {
            string message = "";
            //取出表格中数据放在数据集中
            MSDA.Open();            
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();            
            string sql = "select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" + sFile + ";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$";
            ds = MSDA.ExecuteDataset(CommandType.Text, sql, null, ds, "excel");
            MSDA.Close();
            dt = ds.Tables["excel"];
            if (dt!=null||dt.Rows.Count > 0)
            {
                //Excel表格存在数据就删除tb_sys_MatCode表的数据
                MSDA.Open();
                string delsql = "delete from tb_sys_MatCode";
                MSDA.ExecuteNonQuery(CommandType.Text, delsql, null);
                MSDA.Close();
                //向tb_sys_MatCode表写入Excel表格导入的数据
                for (int i =0; i <dt.Rows.Count; i++)
                {
                    MatCode mymatcode = new MatCode();
                    if (dt.Rows[i][0].ToString() == "")
                    {
                        message = "第" + (i+1) + "行物资代码不能为空!";
                        return message;
                    }                    
                    string matcode = dt.Rows[i][0].ToString();
                    DataView dv = mymatcode.MatCodeSearch(matcode, "1");
                    if (dv.Count > 0)
                    {
                        message = "第" + (i + 1) + "行物资代码已经存在!";
                        return message;
                    }
                    if (mymatcode.MatCodeCheck(matcode)==false)
                    {
                        message = "第" + (i + 1) + "行物资代码格式错误!";
                        return message;
                    }
                    string matname = dt.Rows[i][2].ToString();
                    string matdecs = dt.Rows[i][1].ToString();
                    string insertsql = "insert into tb_sys_MatCode([MatCode],[MatName],[MatDecs]) values('"+matcode+"','"+matname+"','"+matdecs+"')";
                    MSDA.Open();
                    SqlTransaction tran = MSDA.BeginTransaction();
                    try
                    {
                        MSDA.ExecuteNonQuery(CommandType.Text,insertsql,null);
                        tran.Commit();                        
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;                       
                    }
                    finally
                    {
                        MSDA.Close();
                    }
                }
            }
            return message;
        }
    }
}

						