大量數據導入操作, 也就是直接將DataTable里的內容寫入到資料庫 通用方法: 拼接Insert語句, 好土鱉 1. MS Sql Server: 使用SqlBulkCopy 2. MySql: adapter.update()批量更新 MySqlBulkLoader, 這個是從文件裡邊到的, ...
大量數據導入操作, 也就是直接將DataTable里的內容寫入到資料庫
通用方法: 拼接Insert語句, 好土鱉
1. MS Sql Server: 使用SqlBulkCopy
2. MySql:
adapter.update()批量更新
MySqlBulkLoader, 這個是從文件裡邊到的, 有個實現是先將DATATable編程CSV文件, 在用MySqlBulkLoader導入MySql
原文引用於Rocho.J的批量插入數據, 將DataTable里的數據批量寫入資料庫的方法
//參考代碼
1 Function to create .csv file from DataTable (you can skip this, if you already have csv file) 2 public static void CreateCSVfile(DataTable dtable, string strFilePath) 3 { 4 StreamWriter sw = new StreamWriter(strFilePath, false); 5 int icolcount = dtable.Columns.Count; 6 foreach (DataRow drow in dtable.Rows) 7 { 8 for (int i = 0; i < icolcount; i++) 9 { 10 if (!Convert.IsDBNull(drow[i])) 11 { 12 sw.Write(drow[i].ToString()); 13 } 14 if (i < icolcount - 1) 15 { 16 sw.Write(","); 17 } 18 } 19 sw.Write(sw.NewLine); 20 } 21 sw.Close(); 22 sw.Dispose(); 23 } 24 25 //2. Import data into MySQL database 26 private void ImportMySQL() 27 { 28 DataTable orderDetail = new DataTable("ItemDetail"); 29 DataColumn c = new DataColumn(); // always 30 orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32"))); 31 orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32"))); 32 orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32"))); 33 orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32"))); 34 orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal"))); 35 orderDetail.Columns["total"].Expression = "value/(length*breadth)"; //Adding dummy entries 36 DataRow dr = orderDetail.NewRow(); 37 dr["ID"] = 1; 38 dr["value"] = 50; 39 dr["length"] = 5; 40 dr["breadth"] = 8; 41 orderDetail.Rows.Add(dr); 42 dr = orderDetail.NewRow(); 43 dr["ID"] = 2; 44 dr["value"] = 60; 45 dr["length"] = 15; 46 dr["breadth"] = 18; 47 orderDetail.Rows.Add(dr); //Adding dummy entries 48 string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;"; 49 string strFile = "/TempFolder/MySQL" + DateTime.Now.Ticks.ToString() + ".csv"; //Create directory if not exist... Make sure directory has required rights.. 50 if (!Directory.Exists(Server.MapPath("~/TempFolder/"))) 51 Directory.CreateDirectory(Server.MapPath("~/TempFolder/")); //If file does not exist then create it and right data into it.. 52 if (!File.Exists(Server.MapPath(strFile))) 53 { 54 FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write); 55 fs.Close(); 56 fs.Dispose(); 57 } 58 //Generate csv file from where data read 59 CreateCSVfile(orderDetail, Server.MapPath(strFile)); 60 using (MySqlConnection cn1 = new MySqlConnection(connectMySQL)) 61 { 62 cn1.Open(); 63 MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1); 64 bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database... 65 bcp1.FieldTerminator = ","; 66 bcp1.LineTerminator = "\r\n"; 67 bcp1.FileName = Server.MapPath(strFile); 68 bcp1.NumberOfLinesToSkip = 0; 69 bcp1.Load(); //Once data write into db then delete file.. 70 try 71 { 72 File.Delete(Server.MapPath(strFile)); 73 } 74 catch (Exception ex) 75 { 76 string str = ex.Message; 77 } 78 } 79 }View Code
3. MS Access: 只能用批量更新了, adapter.update()
備註: 此處先標記個思路, 等我這實現完了, 貼個示例
==============
其實早就做完了, 都忘記這回事了... 今天看到這篇, 補一下代碼
//枚舉
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 namespace ETLUtilityDAL.Enums 7 { 8 public enum DatabaseType 9 { 10 MSSql, 11 MySql, 12 MSAccess, 13 Oracle 14 } 15 }View Code
//公共方法
//DALFactory.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.Configuration; 7 using ETLUtilityDAL.Enums; 8 using ETLUtilityDAL.Interfaces; 9 using ETLUtilityDAL.Implement; 10 using System.Data.SqlClient; 11 12 namespace ETLUtilityDAL.Common 13 { 14 /// <summary> 15 /// 資料庫訪問工廠, 用於產生相應類型的資料庫實例 16 /// </summary> 17 public class DALFactory 18 { 19 private static readonly Dictionary<string, string> dictConnectionStrs = new Dictionary<string, string>(); 20 private static readonly DatabaseType currentDB = (DatabaseType)Enum.Parse(typeof(DatabaseType), ConfigurationManager.AppSettings["CurrentDatabase"]); 21 22 /// <summary> 23 /// 靜態構造函數, 用於初始化資料庫連接串字典 24 /// </summary> 25 static DALFactory() 26 { 27 getConnectionDictionary(); 28 } 29 30 private static void getConnectionDictionary() 31 { 32 ConnectionStringSettingsCollection cssc = ConfigurationManager.ConnectionStrings; 33 string tempConStr = ""; 34 foreach (string str in Enum.GetNames(typeof(DatabaseType))) 35 try 36 { 37 tempConStr = cssc[str.Trim().ToLower()].ConnectionString; 38 if (!string.IsNullOrEmpty(tempConStr)) 39 dictConnectionStrs.Add(str, tempConStr); 40 } 41 catch (Exception ex) 42 { 43 //throw ex; 44 } 45 } 46 47 /// <summary> 48 /// 返回連接串字典以供查看 49 /// </summary> 50 public static Dictionary<string,string> ConnectionStringsDictionary 51 { 52 get { return dictConnectionStrs; } 53 } 54 55 /// <summary> 56 /// 根據資料庫的類型獲得有固定資料庫名稱的泛型類型的資料庫連接對象 57 /// </summary> 58 /// <typeparam name="T">T類型, 表示泛型類型的資料庫連接對象</typeparam> 59 /// <param name="dbType">System.Enum類型, 表示資料庫的類型</param> 60 /// <returns>T類型, 返回泛型類型的資料庫連接對象</returns> 61 public static T GetDatabaseConnection<T>(DatabaseType dbType) 62 { 63 string dbTypeStr = Enum.GetName(typeof(DatabaseType), dbType); 64 if(dictConnectionStrs.Keys.Contains(dbTypeStr)) 65 return (T)Convert.ChangeType(Activator.CreateInstance(typeof(T), dictConnectionStrs[dbTypeStr]),typeof(T)); 66 else 67 return default(T); 68 } 69 70 /// <summary> 71 /// 根據資料庫的類型獲得指定資料庫名稱的泛型類型的資料庫連接對象 72 /// </summary> 73 /// <typeparam name="T">T類型, 表示泛型類型的資料庫連接對象</typeparam> 74 /// <param name="dbType">System.Enum類型, 表示資料庫的類型</param> 75 /// <param name="dbName">System.String, 表示指定的資料庫名稱</param> 76 /// <returns>T類型, 返回泛型類型的資料庫連接對象</returns> 77 public static T GetDatabaseConnection<T>(DatabaseType dbType, string dbName) 78 { 79 string dbTypeStr = Enum.GetName(typeof(DatabaseType), dbType); 80 if (dictConnectionStrs.Keys.Contains(dbTypeStr) && !string.IsNullOrEmpty(dbName)) 81 return (T)Convert.ChangeType(Activator.CreateInstance(typeof(T), dictConnectionStrs[dbTypeStr].Replace("*",dbName)), typeof(T)); 82 else 83 return default(T); 84 } 85 86 /// <summary> 87 /// 根據資料庫的類型獲得固定資料庫名稱的資料庫訪問工具類DBHelper 88 /// </summary> 89 /// <param name="dbType">System.Enum類型, 表示資料庫的類型</param> 90 /// <returns>Interface, 根據不同的資料庫類型返回不同的工具類的實現</returns> 91 public static IDBHelper GetDBHelper(DatabaseType dbType) 92 { 93 #region 94 switch (dbType) 95 { 96 case DatabaseType.MSSql: 97 return new MSSqlDBHelper(); 98 case DatabaseType.MSAccess: 99 return new MSAccessDBHelper(); 100 case DatabaseType.MySql: 101 return new MySqlDBHelper(); 102 case DatabaseType.Oracle: 103 goto default; 104 default: 105 return null; 106 } 107 #endregion 108 } 109 110 /// <summary> 111 /// 根據資料庫的類型獲得指定資料庫名稱的資料庫訪問工具類DBHelper 112 /// </summary> 113 /// <param name="dbType">System.Enum類型, 表示資料庫的類型</param> 114 /// <param name="dbName">System.String, 表示指定的資料庫名稱</param> 115 /// <returns>Interface, 根據不同的資料庫名稱和類型返回不同的工具類的實現</returns> 116 public static IDBHelper GetDBHelper(DatabaseType dbType, string dbName) 117 { 118 #region 119 switch (dbType) 120 { 121 case DatabaseType.MSSql: 122 return new MSSqlDBHelper(dbName); 123 case DatabaseType.MSAccess: 124 return new MSAccessDBHelper(dbName); 125 case DatabaseType.MySql: 126 return new MySqlDBHelper(dbName); 127 case DatabaseType.Oracle: 128 goto default; 129 default: 130 return null; 131 } 132 #endregion 133 } 134 135 /// <summary> 136 /// 獲得當前正在使用的固定資料庫名稱的資料庫類型的訪問工具類Helper 137 /// </summary> 138 /// <returns>Interface, 根據不同的資料庫類型返回不同的工具類的實現</returns> 139 public static IDBHelper GetDBHelper() 140 { 141 return GetDBHelper(currentDB); 142 } 143 144 /// <summary> 145 /// 獲得當前正在使用的指定據庫名稱的資料庫類型的訪問工具類Helper 146 /// </summary> 147 /// <returns>Interface, 根據不同的資料庫名稱和類型返回不同的工具類的實現</returns> 148 public static IDBHelper GetDBHelper(string dbName) 149 { 150 return GetDBHelper(currentDB,dbName); 151 } 152 } 153 }View Code
//FileHelper.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.IO; 7 using System.Data; 8 9 namespace ETLUtilityDAL.Common 10 { 11 public class FileHelper 12 { 13 public static string ReadFileToString(string fileFullPath, Encoding codeType) 14 { 15 string result = ""; 16 if (string.IsNullOrEmpty(fileFullPath)) 17 throw new ArgumentNullException( "fileFullPath","File path can not be null or empty! "); 18 using (FileStream fileStream = new FileStream(fileFullPath, FileMode.OpenOrCreate, FileAccess.Read)) 19 { 20 if (!File.Exists(fileFullPath)) 21 throw new FileNotFoundException("File not found! "); 22 } 23 24 using (StreamReader sReader = new StreamReader(fileFullPath, codeType)) 25 { 26 try 27 { 28 result = sReader.ReadToEnd(); 29 } 30 catch (Exception ex) 31 { 32 throw new IOException(ex.Message); 33 } 34 } 35 return result; 36 } 37 38 public static string ReadFileToString(string fileFullPath) 39 { 40 return ReadFileToString(fileFullPath, Encoding.Default); 41 } 42 43 public static void WriteDataTableToCSVFile(DataTable dataTable, string fileFullPath, Encoding codeType) 44 { 45 using (Stream stream = new FileStream(fileFullPath,FileMode.Create,FileAccess.Write)) 46 using (StreamWriter swriter = new StreamWriter(stream, codeType)) 47 { 48 try 49 { 50 int icolcount = dataTable.Columns.Count; 51 foreach (DataRow drow in dataTable.Rows) 52 { 53 for (int i = 0; i < icolcount; i++) 54 { 55 if (!Convert.IsDBNull(drow[i])) 56 { 57 swriter.Write(drow[i].ToString()); 58 } 59 if (i < icolcount - 1) 60 { 61 swriter.Write("|"); 62 } 63 } 64 swriter.Write(swriter.NewLine); 65 } 66 } 67 catch (Exception ex) 68 { 69 throw new IOException(ex.Message); 70 } 71 } 72 } 73 74 public static void WriteDataTableToCSVFile(DataTable dataTable, string fileFullPath) 75 { 76 WriteDataTableToCSVFile(dataTable, fileFullPath, Encoding.Default); 77 } 78 79 public static string[] GetFileFullPathList(string directoryPath, string fileType, bool IsRecursive) 80 { 81 return IsRecursive ? Directory.GetFiles(directoryPath, fileType, SearchOption.AllDirectories) : Directory.GetFiles(directoryPath, fileType, SearchOption.TopDirectoryOnly); 82 } 83 84 public static string[] GetSubDirectorys(string directoryPath, string containsName, bool IsRecursive) 85 { 86 return IsRecursive ? Directory.GetDirectories(directoryPath, containsName, SearchOption.AllDirectories) : Directory.GetDirectories(directoryPath, containsName, SearchOption.TopDirectoryOnly); 87 } 88 89 public static void WriteStringToFile(string fileFullPath, bool isAppend ,string fileContent) 90 { 91 WriteStringToFile(fileFullPath, isAppend, fileContent, Encoding.Default); 92 } 93 94 public static void WriteStringToFile(string fileFullPath, bool isAppend, string fileContent, Encoding codeType) 95 { 96 //using (FileStream fileStream = new FileStream(fileFullPath, FileMode.OpenOrCreate, FileAccess.Write)) 97 using (StreamWriter sWriter = new StreamWriter(fileFullPath,isAppend,codeType)) 98 { 99 try 100 { 101 if (!File.Exists(fileFullPath)) 102 File.Create(fileFullPath); 103 sWriter.Write(fileContent); 104 } 105 catch (Exception ex) 106 { 107 throw new IOException(ex.Message); 108 } 109 } 110 } 111 } 112 }View Code
//XMLHelper.cs, 用List模擬堆棧實現XML結點的操作
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.IO; 7 using System.Xml; 8 using System.Xml.XPath; 9 using System.Xml.Schema; 10 using ETLUtilityDAL.Enums; 11 using System.Collections.Specialized; 12 using ETLUtilityModel; 13 using ETLUtilityModel.Enums; 14 15 namespace ETLUtilityDAL.Common 16 { 17 public class XMLHelper 18 { 19 #region XMLStream 20
