由于管理需要，公司决定上一套信息管理系统，将原来的用Excel所做的记录用管理系统来管理。通过努力，我终于作了一套类似《牛腩新闻发布系统》的客户信息管理系统。可原来的Excel中的数据该如何导入到新的系统中呢。通过两天的不断研习我终于搞定。
我所用的数据库是基于SQL Server Express版的，用SQL Server Management Studio Express来管理。数据库结构见图[img]/upload/attachment/156261/e71d8b1f-eba4-31b6-8a45-528d07eba88a.jpg[/img],
其中各个表的说明见图[img]/upload/attachment/156263/367fb587-e045-3f65-9df4-d5247658d43c.jpg[/img],公司现在所用的Excel数据见图[img]/upload/attachment/156265/bce2ae23-db55-3124-86a1-5562da8bebe9.jpg[/img]。
首先要做的是将Excel表导入到SQL Server 下。我先将Excel导入到Access中，再将Access数据库导入到SQL Server 2000中，然后将SQL Server 2000中的数据库做备份，最后再在SQL Server Express将数据库还原。最终得到SQL Server 中的数据库nbk,表名userinfo,表中的字段有：
id(编号) date(安装日期) name(客户姓名) address(地址) telphone(电话) model(机型) amount(数量) acmodel（配件） inname(安装工姓名)
剩下的工作就是要在VS中(我用的是VWD 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()); mandType = 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())) { mandType = 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(); mandType = 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()); mandType = 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=PC080519VDZ\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,stringuserTelephone,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。
