1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Web;  5 using System.Data;  6 using System.Data.OleDb;  7 using System.Configuration;  8 using System.Data.SqlClient;  9  10 namespace insertData 11 { 12     class FileSvr 13     { 14         /// <summary> 15         /// 获取DataRow的真实长度 16         /// </summary> 17         /// <param name="dr">DataRow</param> 18         /// <returns>真实长度</returns> 19         public int getRowLen(DataRow dr) 20         { 21             int i = 0; 22             for (; i < dr.ItemArray.Length; i++) 23                 if (dr[i].ToString().Trim() == "") break; 24             return i; 25         } 26         /// <summary> 27         /// //Excel指定的工作表数据导入Datable 28         /// </summary> 29         /// <param name="fileUrl">Excel路径</param> 30         /// <param name="sheetName">工作表名</param> 31         /// <returns></returns> 32         public DataTable getExcelDatatable(string fileUrl, string sheetName)//Excel指定的工作表数据导入Datable 33         { 34             //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据； 35             const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties=‘Excel 12.0; HDR=Yes; IMEX=1‘"; 36  37             DataTable dt = null; 38             //建立连接 39             OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl)); 40             try 41             { 42                 //打开连接 43                 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) 44                 { 45                     conn.Open(); 46                 } 47  48                 //查询sheet中的数据 49                 string strSql = "select * from [" + sheetName + "$]"; 50                 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); 51                 DataSet ds = new DataSet(); 52                 da.Fill(ds); 53                 dt = ds.Tables[0]; 54                 return dt; 55             } 56             catch (Exception exc) 57             { 58                 throw exc; 59             } 60             finally 61             { 62                 conn.Close(); 63                 conn.Dispose(); 64             } 65         } 66 public void InsertRoute(DataTable dt, string strConnection,int dir)//从DataTable导入数据到数据库 67         { 68             int retVal = 0; 69             int count0 = 0; 70             int count = 0; 71             int routeNo; 72             SqlConnection conn = new SqlConnection(strConnection); 73             conn.Open(); 74             string strSql; 75             SqlCommand sqlCmd = new SqlCommand(); 76             sqlCmd.Connection = conn; 77             sqlCmd.Parameters.Add("@编号", System.Data.SqlDbType.Int, 4); 78             sqlCmd.Parameters.Add("@线路号", System.Data.SqlDbType.Int, 4); 79             sqlCmd.Parameters.Add("@行向", System.Data.SqlDbType.Int, 4); 80             sqlCmd.Parameters.Add("@线路名称", System.Data.SqlDbType.NVarChar, 50); 81             sqlCmd.Parameters.Add("@始发站点", System.Data.SqlDbType.NVarChar, 50); 82             sqlCmd.Parameters.Add("@开往方向", System.Data.SqlDbType.NVarChar, 50); 83             sqlCmd.Parameters.Add("@更新时间", System.Data.SqlDbType.DateTime, 4); 84             foreach (DataRow dr in dt.Rows)//遍历行数据 85             { 86                 strSql= "update 线路 set 线路号=@线路号, 行向=@行向, 线路名称=@线路名称, 始发站点=@始发站点, 开往方向=@开往方向,更新时间=@更新时间 where 编号=@编号"; 87                 sqlCmd.CommandText = strSql; 88                 routeNo =Convert.ToInt32(dr[0].ToString().Trim()); 89                 sqlCmd.Parameters["@编号"].Value = http://www.mamicode.com/routeNo+dir*100000; 90                 sqlCmd.Parameters["@线路号"].Value=http://www.mamicode.com/routeNo; 91                 sqlCmd.Parameters["@行向"].Value=http://www.mamicode.com/dir; 92                 sqlCmd.Parameters["@线路名称"].Value=http://www.mamicode.com/routeNo+"路"; 93                 sqlCmd.Parameters["@始发站点"].Value=http://www.mamicode.com/dr[1].ToString(); 94                 sqlCmd.Parameters["@开往方向"].Value=http://www.mamicode.com/dr[getRowLen(dr)-1].ToString(); 95                 sqlCmd.Parameters["@更新时间"].Value =http://www.mamicode.com/ DateTime.Now; 96                 retVal = sqlCmd.ExecuteNonQuery(); 97                 if (retVal == 0) 98                 { 99                     strSql = @"insert into 线路( 编号, 线路号, 行向, 线路名称, 始发站点, 开往方向, 更新时间) values(@编号,@线路号,@行向,@线路名称,@始发站点,@开往方向,@更新时间)";100                     sqlCmd.CommandText = strSql;101                     retVal = sqlCmd.ExecuteNonQuery();102                     count++;103                     Console.WriteLine("插入线路{0}成功！", routeNo);104                 }105                 else106                 {107                     count0++;108                     Console.WriteLine("更新线路{0}成功！", routeNo);109                 }110             }111             conn.Close();112             Console.WriteLine("插入{0}条、更新{1}条线路",count, count0);113         }114 }