`
yangle926
  • 浏览: 55844 次
  • 性别: Icon_minigender_1
  • 来自: 洛阳
社区版块
存档分类
最新评论

实例讲解用.NET技术将Excel表格中的数据导入到特定的SQL Server数据库中

    博客分类:
  • .NET
阅读更多
    由于管理需要,公司决定上一套信息管理系统,将原来的用Excel所做的记录用管理系统来管理。通过努力,我终于作了一套类似《牛腩新闻发布系统》的客户信息管理系统。可原来的Excel中的数据该如何导入到新的系统中呢。通过两天的不断研习我终于搞定。
    我所用的数据库是基于SQL Server 2005 Express版的,用SQL Server Management Studio Express来管理。数据库结构见图,
其中各个表的说明见图,公司现在所用的Excel数据见图
    首先要做的是将Excel表导入到SQL Server 2005下。我先将Excel导入到Access中,再将Access数据库导入到SQL Server 2000中,然后将SQL Server 2000中的数据库做备份,最后再在SQL Server 2005 Express将数据库还原。最终得到SQL Server 2005中的数据库nbk,表名userinfo,表中的字段有:
id(编号) date(安装日期) name(客户姓名) address(地址) telphone(电话) model(机型) amount(数量) acmodel(配件) inname(安装工姓名)
    剩下的工作就是要在VS中(我用的是VWD 2008 Express Edition)将库nbk中的数据导入到数据库yuajiasys中。
     准备工作:
    为了能够看到出错信息,首先打开web.config文件,将其中的容错处理语句,也就是<customErrors mode="On" defaultRedirect="~\error.htm"></customErrors>一行删除。
    我原来所用的SQLHelper.cs(在DAL层)代码如下:
    
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class SQLHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;

        public SQLHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }

        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        public int ExecuteNonQuery(string sql, CommandType ct)
        {
            int res;
            try
            {
                cmd = new SqlCommand(sql, GetConn());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }

        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn()))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }

        public DataTable ExecuteQuery(string cmdText, CommandType ct)
        {
            cmd = new SqlCommand(cmdText, GetConn());
            DataTable dt = new DataTable();
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            DataTable dt = new DataTable();
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
    }
}

    此方法是用来操作数据库yuajiasys的。为了能够操作数据库nbk,我将SQLHelper复制了一份,命名为SQLHelper1,代码除了将SQLHelper改为SQLHelper1之外,只有一个地方要修改,那就是连接字符串,与nbk建立连接的字符串的写法:string connStr = @"server=PC2009080519VDZ\SQLEXPRESS;database=nbk;uid=sa;pwd=123456";改好之后要对DAL进行“重新生成”。
    真正操作数据的过程:
    新建一个网页copydata,前台什么也不用做,直接处理代码。具体过程如下:
    一、从数据库nbk中查出所有的机型类别名称并加入到数据库yuajiasys的类别表category中。
    直接在页面的Page_Load事件中写,具体代码:
   
string conncategory = "select distinct [model] from userinfo order by [model]";
DataTable camodel = new SQLHelper1().ExecuteQuery(conncategory, CommandType.Text);
string model;
for (int i = 0; i < camodel.Rows.Count; i++)
{
    model = camodel.Rows[i]["model"].ToString();
    new CategroyManager().Insert(model); //调用往类别名称表加添加数据的方法
}
……
//往类别名称表加添加数据的方法
public bool Insert(string Model)
{
     bool flag = false;
     string sql = "insert into category(Model) values(@Model)";
     SqlParameter[] paras = new SqlParameter[]{
        new SqlParameter("@Model",Model)
     };
     int res=sqlhelper.ExecuteNonQuery(sql,paras,CommandType.Text);
     if (res>0)
     {
        flag = true;
     }
     return flag;
}

    二、添加主表记录
     将第一步中的代码注释掉,添加如下代码:
    string conninfo = "select * from userinfo order by date";        
        DataTable dtinfo = new SQLHelper1().ExecuteQuery(conninfo,CommandType.Text);
	   UserInfo usinfo = new UserInfo(); //UserInfo是客户信息实体类
        for (int i = 0; i < dtinfo.Rows.Count; i++)  //根据界面的错误提示,不断修改执行循环的起始值和结束值,至到将数据全部复制完成。一次循环不应超过100
         { 
            string caid = dtinfo.Rows[i]["model"].ToString();
            switch (caid)
            {
                case "大众175升":
                    caid="1";
                    break;
                case "大众180升":
                    caid = "2";
                    break;
                case "大众210升":
                    caid = "3";
                    break;
                case "大众240升":
                    caid = "4";
                    break;
                case "大众300升":
                    caid = "5";
                    break;
                case "冬傲180升":
                    caid = "6";
                    break;
                case "冬傲210升":
                    caid = "7";
                    break;
                case "冬傲240升":
                    caid = "8";
                    break;
                case "冬傲300升":
                    caid = "9";
                    break;
                case "富康200升":
                    caid = "10";
                    break;
                case "富康240升":
                    caid = "11";
                    break;
                case "富康300升":
                    caid = "12";
                    break;
                case "富康420升":
                    caid = "13";
                    break;
                case "金刚240升":
                    caid = "14";
                    break;
                case "金刚300升":
                    caid = "15";
                    break;
                case "金刚360升":
                    caid = "16";
                    break;
                case "金刚420升":
                    caid = "17";
                    break;               
                case "我爱我家150":
                    caid = "18";
                    break;
                case "我爱我家155":
                    caid = "19";
                    break;
                case "我爱我家175":
                    caid = "20";
                    break;
                case "我爱我家180":
                    caid = "21";
                    break;
                case "我爱我家210":
                    caid = "22";
                    break;
                case "我爱我家240":
                    caid = "23";
                    break;
                case "小神童300升":
                    caid = "24";
                    break;
                case "阳光300升":
                    caid = "25";
                    break;
                case "粤佳200升":
                    caid = "26";
                    break;
                case "粤佳240升":
                    caid = "27";
                    break;
                case "粤佳300升":
                    caid = "28";
                    break;
                case "粤佳360升":
                    caid = "29";
                    break;                
                default:
                    caid = "12"; //设为富康300升所对应的ID号
                    break;
            }
            usinfo.UserName = dtinfo.Rows[i]["name"].ToString().Trim();
            usinfo.UserAddress = dtinfo.Rows[i]["address"].ToString().Trim();
            usinfo.UserTelephone = dtinfo.Rows[i]["telphone"].ToString().Trim();
            usinfo.CaId = caid;
            usinfo.InstallationDate = dtinfo.Rows[i]["date"].ToString().Trim();
            usinfo.Amount = dtinfo.Rows[i]["amount"].ToString().Trim();
            usinfo.AccessoriesModel = dtinfo.Rows[i]["acmodel"].ToString().Trim();
            usinfo.InstallationName = dtinfo.Rows[i]["inname"].ToString().Trim();
            usinfo.Notes = "";
            new UserInfoManager().Insert(usinfo);
        }
……

往客户信息表加添加数据的方法
         /// <summary>
        ///  添加新记录
         /// </summary>
        /// <param name="user">客户信息实体类</param>
        /// <returns></returns>
        public bool Insert(UserInfo user)
        {
            bool flag = false;
            string cmdText = "UserInfo_Insert";
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@userName",user.UserName),
                new SqlParameter("@userAddress",user.UserAddress),
                new SqlParameter("@userTelephone",user.UserTelephone),
                new SqlParameter("@caId",user.CaId),
                new SqlParameter("@installationDate",user.InstallationDate),
                new SqlParameter("@amount",user.Amount),
                new SqlParameter("@accessoriesModel",user.AccessoriesModel),
                new SqlParameter("@installationName",user.InstallationName),
                new SqlParameter("@notes",user.Notes)                
            };
            int res = sqlhelper.ExecuteNonQuery(cmdText,paras,CommandType.StoredProcedure);
            if (res>0)
            {
                flag = true;
            }
            return flag;
        }

实体类UserInfo.cs的代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class UserInfo
    {
        private string id;
        private string userName;
        private string userAddress;
        private string userTelephone;
        private string caId;
        private string installationDate;
        private string amount;
        private string accessoriesModel;
        private string installationName;
        private string notes;

        public string Id
        {
            get { return id; }
            set { id = value; }
        }
        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }
        public string UserAddress
        {
            get { return userAddress; }
            set { userAddress = value; }
        }
        public string UserTelephone
        {
            get { return userTelephone; }
            set { userTelephone = value; }
        }
        public string CaId
        {
            get { return caId; }
            set { caId = value; }
        }
        public string InstallationDate
        {
            get { return installationDate; }
            set { installationDate = value; }
        }
        public string Amount
        {
            get { return amount; }
            set { amount = value; }
        }
        public string AccessoriesModel
        {
            get { return accessoriesModel; }
            set { accessoriesModel = value; }
        }
        public string InstallationName
        {
            get { return installationName; }
            set { installationName = value; }
        }
        public string Notes
        {
            get { return notes; }
            set { notes = value; }
        }
        public UserInfo() { }
        public UserInfo(string id,string userName,string userAddress,string	userTelephone,string caId,string installationDate,string amount,string accessoriesModel,string installationName,string notes) 
        {
            this.id = id;
            this.userName = userName;
            this.userAddress = userAddress;
            this.userTelephone = userTelephone;
            this.caId = caId;
            this.installationDate = installationDate;
            this.amount = amount;
            this.accessoriesModel = accessoriesModel;
            this.installationName = installationName;
            this.notes = notes;
        }
        public UserInfo(string userName, string userAddress, string userTelephone, string caId,string installationDate, string amount, string accessoriesModel, string installationName, string notes)
        {
            this.userName = userName;
            this.userAddress = userAddress;
            this.userTelephone = userTelephone;
            this.caId = caId;
            this.installationDate = installationDate;
            this.amount = amount;
            this.accessoriesModel = accessoriesModel;
            this.installationName = installationName;
            this.notes = notes;
        }
    }
}

    说明:由于有两个与数据库的链接存在,在运行过程中会出错,好像是链接池的问题,我不太懂。我的解决办法是不断的修改循环的起始值和结束值,直止将数据全部写完。关于这点,哪位网友有好的解决办法,希望能与之交流。另外,代码中用到一些自己写的操作数据库的方法,如有不明之处,建议看看视频《牛腩新闻发布系统》。
写的比较乱,主要是讲一种思路,有不懂的地方,欢迎与本人联系交流。QQ:747386679。






  • 大小: 32.3 KB
  • 大小: 24.1 KB
  • 大小: 21.6 KB
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics