大量数据导入操作, 也就是直接将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 database26 private void ImportMySQL()27 {28 DataTable orderDetail = new DataTable("ItemDetail");29 DataColumn c = new DataColumn(); // always30 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 entries36 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 entries48 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 read59 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 try71 {72 File.Delete(Server.MapPath(strFile));73 }74 catch (Exception ex)75 {76 string str = ex.Message;77 }78 }79 }View Code3. 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 Oracle14 }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 #endregion108 }109 110 /// <summary>111 /// 根据数据库的类型获得指定数据库名称的数据库访问工具类DBHelper112 /// </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 #region119 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 #endregion133 }134 135 /// <summary>136 /// 获得当前正在使用的固定数据库名称的数据库类型的访问工具类Helper137 /// </summary>138 /// <returns>Interface, 根据不同的数据库类型返回不同的工具类的实现</returns>139 public static IDBHelper GetDBHelper()140 {141 return GetDBHelper(currentDB);142 }143 144 /// <summary>145 /// 获得当前正在使用的指定据库名称的数据库类型的访问工具类Helper146 /// </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 try100 {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 public static List<ETLXmlNode> GetAllNodesFromXMLFile(string xmlFileFullName) 21 { 22 List<ETLXmlNode> xmlNodeLst = new List<ETLXmlNode>(); 23 24 using (Stream stream = new FileStream(xmlFileFullName, FileMode.Open, FileAccess.Read)) 25 using (XmlTextReader xmlTxtReader = new XmlTextReader(stream)) 26 { 27 int fathId = 0; //root node: 0 28 int elemCount = 1; //Element Count 29 XmlNodeType nodeType; 30 ETLXmlNode xNode; 31 ETLXmlNodeStack nodStack = ETLXmlNodeStack.CreateETLXmlNodeStack(); 32 while (xmlTxtReader.Read()) 33 { 34 xNode = null; 35 nodeType = xmlTxtReader.NodeType; 36 switch (nodeType) 37 { 38 case XmlNodeType.Element: 39 bool isEmpty = false; 40 if (isEmpty = xmlTxtReader.IsEmptyElement) 41 xNode = new ETLXmlNode(elemCount, xmlTxtReader.Name, ETLXmlNodeType.OddNode, fathId); 42 else 43 xNode = new ETLXmlNode(elemCount, xmlTxtReader.Name, ETLXmlNodeType.EvenNode, fathId); 44 45 fathId = elemCount; 46 elemCount ; 47 48 //Deal with the Attribute 49 if (xmlTxtReader.HasAttributes) 50 { 51 NameValueCollection nvc = xNode.NodeAttributes; 52 for (int i = 0; i < xmlTxtReader.AttributeCount; i ) 53 { 54 xmlTxtReader.MoveToAttribute(i); 55 nvc.Add(xmlTxtReader.Name, xmlTxtReader.Value); 56 } 57 } 58 if (isEmpty) 59 { 60 xmlNodeLst.Add(xNode); 61 fathId = xNode.FatherNodeId; 62 } 63 else 64 ETLXmlNodeStack.Push(xNode); 65 break; 66 case XmlNodeType.EndElement: 67 xNode = ETLXmlNodeStack.Pop(); 68 xmlNodeLst.Add(xNode); 69 fathId = xNode.FatherNodeId; 70 break; 71 case XmlNodeType.Text: 72 xNode = ETLXmlNodeStack.Pop(); 73 xNode.NodeText = xmlTxtReader.Value; 74 ETLXmlNodeStack.Push(xNode); 75 break; 76 default: 77 break; 78 } 79 } 80 } 81 82 return xmlNodeLst; 83 } 84 #endregion 85 86 87 #region XPath --- Not Implement 88 #endregion 89 90 #region XPathExpress --- Not Implement 91 92 #endregion 93 94 #region Common 95 public static bool IsValidateXmlFile(string xmlSchemaFileFullName) 96 { 97 bool result = false; 98 using (Stream stream = new FileStream(xmlSchemaFileFullName, FileMode.Open, FileAccess.Read)) 99 using (XmlTextReader xmlTxtReader = new XmlTextReader(stream))100 {101 XmlSchema schema = XmlSchema.Read(stream, new ValidationEventHandler(dealSchemaValidation));102 XmlReaderSettings settings = new XmlReaderSettings();103 settings.Schemas.Add(schema);104 settings.ValidationType = ValidationType.Schema;105 settings.ValidationEventHandler = new ValidationEventHandler(dealSchemaValidation); 106 107 //Execute Validate108 try109 {110 while (xmlTxtReader.Read())111 { }112 result = true;113 }114 catch (XmlSchemaValidationException xsve)115 {116 result = false;117 throw xsve;118 }119 }120 return result;121 }122 123 private static void dealSchemaValidation(object sender, System.Xml.Schema.ValidationEventArgs e)124 {125 throw new XmlSchemaValidationException(string.Format("Validation Error, Error Level:{0}\r\n. Error Details:\r\n{1}", e.Severity, e.Message));126 }127 #endregion128 129 static void TestMethod()130 {131 #region 使用XML流处理, 每次只处理一个节点, 速度快, 但缺点是: 不支持结构化查询, 适合从头到尾一次性处理 132 //使用xml流输出字符 133 using (System.Xml.XmlWriter xmlwriter = System.Xml.XmlWriter.Create("Output.xml")) 134 { 135 xmlwriter.WriteStartDocument(); 136 xmlwriter.WriteStartElement("human"); //</humen> 137 xmlwriter.WriteStartElement("man"); //子元素 138 //写元素属性 139 xmlwriter.WriteAttributeString("name", "father"); //属性 140 xmlwriter.WriteString("Mike"); //文本区 141 xmlwriter.WriteEndElement(); 142 xmlwriter.WriteElementString("women", "jean"); //<women>jean</women> 143 xmlwriter.WriteStartElement("children"); 144 xmlwriter.WriteAttributeString("name", "kiddy"); 145 xmlwriter.WriteString("nickey kiddy"); //文本区 146 xmlwriter.WriteEndElement(); 147 xmlwriter.WriteEndElement(); 148 }149 #endregion150 #region 使用优化的XPath--XPathDocument类, 速度快, 也支持结构化的查询方式. 缺点: 只能读不能写 151 //1.创建XPathDocument对象 152 System.Xml.XPath.XPathDocument xpdoc = new System.Xml.XPath.XPathDocument("XMLOperation.xml"); 153 //2.通过导航器进行查找 154 System.Xml.XPath.XPathNavigator xpnav = xpdoc.CreateNavigator(); 155 //3.经过编译的XPath 156 string xpath = "/configuration/system.web/httpHandlers/cellphone"; 157 System.Xml.XPath.XPathExpression xpe = System.Xml.XPath.XPathExpression.Compile(xpath); 158 //4.使用导航器的Select迭代器进行查找, 查找的结果还是导航器 159 System.Xml.XPath.XPathNavigator resultNav = xpnav.SelectSingleNode(xpe); 160 Console.WriteLine("----------XPathDocument的查询单个结果----------"); 161 Console.WriteLine(resultNav.Value); 162 //查找多个结果 163 Console.WriteLine("----------XPathDocument的查询多个结果----------"); 164 xpath = "/configuration/system.web/httpHandlers/add/@type"; //查找add元素的type属性内容 165 xpe = System.Xml.XPath.XPathExpression.Compile(xpath); 166 System.Xml.XPath.XPathNodeIterator xpniter = xpnav.Select(xpe); 167 foreach (System.Xml.XPath.XPathNavigator xpn in xpniter) 168 { 169 Console.WriteLine(xpn.Value); 170 } 171 #endregion172 }173 }174 175 /// <summary>176 /// 用List模拟堆栈操作, 用于读取XML中的结点177 /// </summary>178 public class ETLXmlNodeStack179 {180 private List<ETLXmlNode> _xmlStack;181 182 private ETLXmlNodeStack()183 {184 this._xmlStack = new List<ETLXmlNode>(100);185 }186 187 private static readonly ETLXmlNodeStack inner;188 static ETLXmlNodeStack()189 {190 inner = new ETLXmlNodeStack();191 }192 193 public static ETLXmlNodeStack ETLXmlNodeStackInfo194 {195 get196 {197 return inner;198 }199 }200 201 public static int Count202 {203 get204 {205 return inner._xmlStack.Count;206 }207 }208 209 public static ETLXmlNodeStack CreateETLXmlNodeStack()210 {211 return inner;212 }213 214 public static void Push(ETLXmlNode etlXmlNode)215 {216 inner._xmlStack.Add(etlXmlNode);217 }218 219 public static ETLXmlNode Pop()220 {221 ETLXmlNode result = null;222 if (inner._xmlStack != null && inner._xmlStack.Count > 0)223 {224 result = inner._xmlStack[inner._xmlStack.Count - 1];225 inner._xmlStack.RemoveAt(inner._xmlStack.Count - 1);226 }227 return result;228 }229 }230 }View Code
//接口
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.Data; 7 using ETLUtilityDAL.Enums; 8 9 namespace ETLUtilityDAL.Interfaces10 {11 /// <summary>12 /// Utility Of Data Access Layer13 /// </summary>14 public interface IDBHelper15 {16 #region BaseExecute17 int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues);18 T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues);19 T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues);20 DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues);21 DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues);22 #endregion23 24 #region TxtExecute25 int TxtExecuteNonQuery(string sqlText);26 int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues);27 T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues);28 T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues);29 DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues);30 DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues);31 #endregion32 33 #region SpExecute34 int SpExecuteNonQuery(string sqlText);35 int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues);36 T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues);37 T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues);38 DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues);39 DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues);40 #endregion41 42 #region Common43 bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction);44 bool BulkInsert(DataTable dataTable);45 bool BulkInsert(DataSet dataSet);46 47 string DBName { get; }48 T GetConnection<T>();49 #endregion50 }51 }View Code
//MsAccess实现
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using ETLUtilityDAL.Interfaces; 7 using ETLUtilityDAL.Enums; 8 using ETLUtilityDAL.Common; 9 using System.Data; 10 using System.Data.OleDb; 11 using System.Data.SqlClient; 12 13 namespace ETLUtilityDAL.Implement 14 { 15 public class MSAccessDBHelper : IDBHelper 16 { 17 private DatabaseType _dBVender = DatabaseType.MSAccess; 18 private string _dbName; 19 20 public MSAccessDBHelper() 21 { 22 this._dbName = ""; 23 } 24 25 public MSAccessDBHelper(string dbName) 26 { 27 this._dbName = dbName; 28 } 29 30 #region IDBHelper 成员 31 32 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 33 { 34 int result = 0; 35 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) 36 { 37 OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); 38 oleDbCmd.CommandType = cmdType; 39 try 40 { 41 fillParameters(oleDbCmd, paramNames, paramValues); 42 oleDbCon.Open(); 43 result = oleDbCmd.ExecuteNonQuery(); 44 } 45 catch (OleDbException ode) 46 { 47 throw ode; 48 } 49 } 50 return 0; 51 } 52 53 public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 54 { 55 T result = default(T); 56 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) 57 { 58 OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); 59 oleDbCmd.CommandType = cmdType; 60 try 61 { 62 fillParameters(oleDbCmd, paramNames, paramValues); 63 oleDbCon.Open(); 64 result = (T)Convert.ChangeType(oleDbCmd.ExecuteScalar(), typeof(T)); 65 } 66 catch (OleDbException ode) 67 { 68 throw ode; 69 } 70 } 71 return result; 72 } 73 74 public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 75 { 76 T result = default(T); 77 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) 78 { 79 OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); 80 oleDbCmd.CommandType = cmdType; 81 try 82 { 83 fillParameters(oleDbCmd, paramNames, paramValues); 84 oleDbCon.Open(); 85 result = (T)Convert.ChangeType(oleDbCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); 86 } 87 catch (OleDbException ode) 88 { 89 throw ode; 90 } 91 } 92 return result; 93 } 94 95 public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 96 { 97 DataTable result = new DataTable(); 98 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) 99 {100 OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon);101 oleDbCmd.CommandType = cmdType;102 try103 {104 fillParameters(oleDbCmd, paramNames, paramValues);105 OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd);106 oledDbDA.Fill(result);107 }108 catch (OleDbException ode)109 {110 throw ode;111 }112 }113 return result;114 }115 116 public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)117 {118 DataSet result = new DataSet();119 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>())120 {121 OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon);122 oleDbCmd.CommandType = cmdType;123 try124 {125 fillParameters(oleDbCmd, paramNames, paramValues);126 OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd);127 oledDbDA.Fill(result);128 }129 catch (OleDbException se)130 {131 throw se;132 }133 }134 return result;135 }136 137 public int TxtExecuteNonQuery(string sqlText)138 {139 return ExecNonQuery(sqlText, CommandType.Text, null, null);140 }141 142 public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues)143 {144 return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues);145 }146 147 public T TxtExecuteScalar<T>(string sqlText,string[] paramNames, object[] paramValues)148 {149 return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues);150 }151 152 public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues)153 {154 return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues);155 }156 157 public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues)158 {159 return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues);160 }161 162 public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues)163 {164 return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues);165 }166 167 public int SpExecuteNonQuery(string sqlText)168 {169 return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null);170 }171 172 public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues)173 {174 return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues);175 }176 177 public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues)178 {179 return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);180 }181 182 public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues)183 {184 return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);185 }186 187 public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues)188 {189 return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues);190 }191 192 public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues)193 {194 return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues);195 }196 #endregion197 198 private void fillParameters(OleDbCommand oleDbCmd, string[] paramNames, object[] paramValues)199 {200 if (paramNames == null && paramNames.Length == 0)201 return;202 if (paramValues == null && paramValues.Length == 0)203 return;204 205 if (paramNames.Length != paramValues.Length)206 throw new ArgumentException("The Name Count of parameters does not match its Value Count! ");207 208 string name;209 object value;210 for (int i = 0; i < paramNames.Length; i )211 {212 name = paramNames[i];213 value = paramValues[i];214 if (value != null)215 oleDbCmd.Parameters.AddWithValue(name, value);216 else217 oleDbCmd.Parameters.AddWithValue(name, DBNull.Value);218 }219 }220 221 #region IDBHelper Members222 223 public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction)224 {225 bool result = false;226 OleDbTransaction sqlTran = (OleDbTransaction)Convert.ChangeType(sqlTrasaction, typeof(OleDbTransaction));227 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>())228 {229 OleDbCommand oleDbCmd = new OleDbCommand(string.Format("select * from {0}", dataTable.TableName), oleDbCon);230 oleDbCmd.CommandType = CommandType.Text;231 try232 {233 OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd);234 OleDbCommandBuilder oledCmdBuilder = new OleDbCommandBuilder(oledDbDA);235 oledDbDA.Update(dataTable);236 result = true;237 }238 catch (OleDbException ode)239 {240 result = false;241 throw ode;242 }243 }244 return result;245 }246 247 public bool BulkInsert(DataTable dataTable)248 {249 bool result = false;250 if (dataTable != null && dataTable.Rows.Count > 0)251 {252 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>())253 {254 oleDbCon.Open();255 OleDbTransaction sqlTran = oleDbCon.BeginTransaction(IsolationLevel.ReadCommitted);256 object obj = null;257 result = BulkInsert(obj, dataTable, sqlTran);258 }259 }260 return result;261 }262 263 public bool BulkInsert(DataSet dataSet)264 {265 bool result = false;266 if (dataSet != null && dataSet.Tables.Count > 0)267 {268 using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>())269 {270 oleDbCon.Open();271 OleDbTransaction sqlTran = oleDbCon.BeginTransaction(IsolationLevel.ReadCommitted);272 object obj = null;273 if (dataSet.Tables.Count == 1)274 result = BulkInsert(obj, dataSet.Tables[0], sqlTran);275 else276 {277 foreach (DataTable dt in dataSet.Tables)278 {279 result = BulkInsert(obj, dt, sqlTran);280 if (!result)281 break;282 }283 }284 }285 }286 return result;287 }288 289 public string DBName290 {291 get { return this._dbName; }292 }293 294 295 public T GetConnection<T>()296 {297 T result = default(T);298 if (string.IsNullOrEmpty(this._dbName))299 result = DALFactory.GetDatabaseConnection<T>(this._dBVender);300 else301 result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName);302 return result;303 }304 305 #endregion306 }307 }View Code
//MsSqlServer实现
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using ETLUtilityDAL.Interfaces; 7 using ETLUtilityDAL.Enums; 8 using ETLUtilityDAL.Common; 9 using ETLUtilityModel.Common; 10 using System.Data; 11 using System.Data.SqlClient; 12 13 14 namespace ETLUtilityDAL.Implement 15 { 16 public class MSSqlDBHelper : IDBHelper 17 { 18 private DatabaseType _dBVender = DatabaseType.MSSql; 19 private string _dbName; 20 21 public MSSqlDBHelper() 22 { 23 this._dbName = ""; 24 } 25 26 public MSSqlDBHelper(string dbName) 27 { 28 this._dbName = dbName; 29 } 30 31 #region IDBHelper 成员 32 33 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 34 { 35 int result = 0; 36 using (SqlConnection sqlCon = GetConnection<SqlConnection>()) 37 { 38 SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); 39 sqlCmd.CommandType = cmdType; 40 try 41 { 42 fillParameters(sqlCmd, paramNames, paramValues); 43 sqlCon.Open(); 44 result = sqlCmd.ExecuteNonQuery(); 45 } 46 catch (SqlException se) 47 { 48 throw se; 49 } 50 } 51 return result; 52 } 53 54 public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 55 { 56 T result = default(T); 57 using (SqlConnection sqlCon = GetConnection<SqlConnection>()) 58 { 59 SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); 60 sqlCmd.CommandType = cmdType; 61 try 62 { 63 fillParameters(sqlCmd, paramNames, paramValues); 64 sqlCon.Open(); 65 result = (T)Convert.ChangeType(sqlCmd.ExecuteScalar(),typeof(T)); 66 } 67 catch (SqlException se) 68 { 69 throw se; 70 } 71 } 72 return result; 73 } 74 75 public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 76 { 77 T result = default(T); 78 using (SqlConnection sqlCon = GetConnection<SqlConnection>()) 79 { 80 SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); 81 sqlCmd.CommandType = cmdType; 82 try 83 { 84 fillParameters(sqlCmd, paramNames, paramValues); 85 sqlCon.Open(); 86 result = (T)Convert.ChangeType(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); 87 } 88 catch (SqlException se) 89 { 90 throw se; 91 } 92 } 93 return result; 94 } 95 96 public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 97 { 98 DataTable result = new DataTable(); 99 using (SqlConnection sqlCon = GetConnection<SqlConnection>())100 {101 SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon);102 sqlCmd.CommandType = cmdType;103 try104 {105 fillParameters(sqlCmd, paramNames, paramValues);106 SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);107 sqlDA.Fill(result);108 }109 catch (SqlException se)110 {111 throw se;112 }113 }114 return result;115 }116 117 public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)118 {119 DataSet result = new DataSet();120 using (SqlConnection sqlCon = GetConnection<SqlConnection>())121 {122 SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon);123 sqlCmd.CommandType = cmdType;124 try125 {126 fillParameters(sqlCmd, paramNames, paramValues);127 SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);128 sqlDA.Fill(result);129 }130 catch (SqlException se)131 {132 throw se;133 }134 }135 return result;136 }137 138 public int TxtExecuteNonQuery(string sqlText)139 {140 return ExecNonQuery(sqlText, CommandType.Text, null, null);141 }142 143 public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues)144 {145 return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues);146 }147 148 public T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues)149 {150 return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues);151 }152 153 public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues)154 {155 return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues);156 }157 158 public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues)159 {160 return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues);161 }162 163 public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues)164 {165 return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues);166 }167 168 public int SpExecuteNonQuery(string sqlText)169 {170 return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null);171 }172 173 public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues)174 {175 return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues);176 }177 178 public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues)179 {180 return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);181 }182 183 public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues)184 {185 return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);186 }187 188 public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues)189 {190 return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues);191 }192 193 public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues)194 {195 return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues);196 }197 198 public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction)199 {200 bool result = false;201 202 SqlBulkCopy sqlBC = (SqlBulkCopy)Convert.ChangeType(sqlBulkCopy, typeof(SqlBulkCopy));203 SqlTransaction sqlTran = (SqlTransaction)Convert.ChangeType(sqlTrasaction, typeof(SqlTransaction));204 try205 {206 sqlBC.DestinationTableName = dataTable.TableName;207 208 //Mapping Destination Field of Database Table209 for (int i = 0; i < dataTable.Columns.Count; i )210 {211 sqlBC.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName);212 }213 214 //TestCode215 //string xx = TestUtility.GetColumnDataTypeOfDataTale(dataTable);216 217 //Write DataTable218 sqlBC.WriteToServer(dataTable);219 220 sqlTran.Commit();221 result = true;222 }223 catch(SqlException ex)224 {225 result = false;226 sqlTran.Rollback();227 throw ex;228 }229 finally230 {231 //T、T1给默认值为Null, 由系统调用GC232 sqlBC.Close();233 sqlBulkCopy = default(T);234 sqlTrasaction = default(T1);235 }236 return result;237 }238 239 public bool BulkInsert(DataTable dataTable)240 {241 bool result = false;242 if (dataTable != null && dataTable.Rows.Count > 0)243 {244 using (SqlConnection sqlCon = GetConnection<SqlConnection>())245 {246 sqlCon.Open();247 SqlTransaction sqlTran = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);248 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepIdentity, sqlTran))249 {250 sqlBulkCopy.BatchSize = 20000;251 sqlBulkCopy.BulkCopyTimeout = 60;252 result = BulkInsert(sqlBulkCopy,dataTable,sqlTran);253 }254 }255 }256 return result;257 }258 259 public bool BulkInsert(DataSet dataSet)260 {261 bool result = false;262 if (dataSet != null && dataSet.Tables.Count > 0)263 {264 using (SqlConnection sqlCon = GetConnection<SqlConnection>())265 {266 sqlCon.Open();267 SqlTransaction sqlTran = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);268 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepIdentity, sqlTran))269 {270 sqlBulkCopy.BatchSize = 20000;271 sqlBulkCopy.BulkCopyTimeout = 60;272 if (dataSet.Tables.Count == 1)273 result = BulkInsert(sqlBulkCopy, dataSet.Tables[0], sqlTran);274 else275 {276 foreach (DataTable dt in dataSet.Tables)277 {278 result = BulkInsert(sqlBulkCopy, dt, sqlTran);279 if (!result)280 break;281 }282 }283 }284 }285 }286 return result;287 }288 289 290 public string DBName291 {292 get { return this._dbName; }293 }294 295 296 public T GetConnection<T>()297 {298 T result = default(T);299 if (string.IsNullOrEmpty(this._dbName))300 result = DALFactory.GetDatabaseConnection<T>(this._dBVender);301 else302 result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName);303 return result;304 }305 #endregion306 307 private void fillParameters(SqlCommand cmd, string[] paramNames, object[] paramValues)308 {309 if (paramNames == null && paramNames.Length == 0)310 return;311 if (paramValues == null && paramValues.Length == 0)312 return;313 314 if(paramNames.Length != paramValues.Length)315 throw new ArgumentException("The Name Count of parameters does not match its Value Count! ");316 317 string name;318 object value;319 for (int i = 0; i < paramNames.Length; i )320 {321 name = paramNames[i];322 value = paramValues[i];323 if (value != null)324 cmd.Parameters.AddWithValue(name, value);325 else326 cmd.Parameters.AddWithValue(name, DBNull.Value);327 }328 }329 }330 }View Code
//MySql实现
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using ETLUtilityDAL.Interfaces; 7 using ETLUtilityDAL.Enums; 8 using ETLUtilityDAL.Common; 9 using MySql.Data.MySqlClient; 10 using System.Data; 11 using System.Collections.Specialized; 12 using System.IO; 13 14 namespace ETLUtilityDAL.Implement 15 { 16 public class MySqlDBHelper : IDBHelper 17 { 18 private DatabaseType _dBVender = DatabaseType.MySql; 19 private readonly string _tmpBasePath = AppDomain.CurrentDomain.BaseDirectory; 20 private readonly string _tmpCSVFilePattern = "Temp\\{0}.csv"; //0表示文件名称 21 private string _dbName; 22 23 public MySqlDBHelper() 24 { 25 this._dbName = ""; 26 } 27 28 public MySqlDBHelper(string dbName) 29 { 30 this._dbName = dbName; 31 } 32 33 #region IDBHelper 成员 34 35 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 36 { 37 int result = 0; 38 using (MySqlConnection mySqlCon = GetConnection <MySqlConnection>()) 39 { 40 MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); 41 mySqlCmd.CommandType = cmdType; 42 try 43 { 44 fillParameters(mySqlCmd, paramNames, paramValues); 45 mySqlCon.Open(); 46 result = mySqlCmd.ExecuteNonQuery(); 47 } 48 catch (MySqlException mse) 49 { 50 throw mse; 51 } 52 } 53 return 0; 54 } 55 56 public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 57 { 58 T result = default(T); 59 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) 60 { 61 MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); 62 mySqlCmd.CommandType = cmdType; 63 try 64 { 65 fillParameters(mySqlCmd, paramNames, paramValues); 66 mySqlCon.Open(); 67 result = (T)Convert.ChangeType(mySqlCmd.ExecuteScalar(), typeof(T)); 68 } 69 catch (MySqlException mse) 70 { 71 throw mse; 72 } 73 } 74 return result; 75 } 76 77 public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 78 { 79 T result = default(T); 80 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) 81 { 82 MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); 83 mySqlCmd.CommandType = cmdType; 84 try 85 { 86 fillParameters(mySqlCmd, paramNames, paramValues); 87 mySqlCon.Open(); 88 result = (T)Convert.ChangeType(mySqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); 89 } 90 catch (MySqlException mse) 91 { 92 throw mse; 93 } 94 } 95 return result; 96 } 97 98 public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) 99 {100 DataTable result = new DataTable();101 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>())102 {103 MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon);104 mySqlCmd.CommandType = cmdType;105 try106 {107 fillParameters(mySqlCmd, paramNames, paramValues);108 MySqlDataAdapter mySqlDA = new MySqlDataAdapter(mySqlCmd);109 mySqlDA.Fill(result);110 }111 catch (MySqlException mse)112 {113 throw mse;114 }115 }116 return result;117 }118 119 public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)120 {121 DataSet result = new DataSet();122 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>())123 {124 MySqlCommand sqlCmd = new MySqlCommand(sqlText, mySqlCon);125 sqlCmd.CommandType = cmdType;126 try127 {128 fillParameters(sqlCmd, paramNames, paramValues);129 MySqlDataAdapter mySqlDA = new MySqlDataAdapter(sqlCmd);130 mySqlDA.Fill(result);131 }132 catch (MySqlException mse)133 {134 throw mse;135 }136 }137 return result;138 }139 140 public int TxtExecuteNonQuery(string sqlText)141 {142 return ExecNonQuery(sqlText, CommandType.Text, null, null);143 }144 145 public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues)146 {147 return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues);148 }149 150 public T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues)151 {152 return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues);153 }154 155 public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues)156 {157 return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues);158 }159 160 public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues)161 {162 return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues);163 }164 165 public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues)166 {167 return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues);168 }169 170 public int SpExecuteNonQuery(string sqlText)171 {172 return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null);173 }174 175 public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues)176 {177 return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues);178 }179 180 public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues)181 {182 return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);183 }184 185 public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues)186 {187 return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues);188 }189 190 public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues)191 {192 return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues);193 }194 195 public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues)196 {197 return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues);198 }199 200 public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction)201 {202 bool result = false;203 string tmpCsvPath = this._tmpBasePath string.Format(this._tmpCSVFilePattern, DateTime.Now.Ticks.ToString());204 string tmpFolder = tmpCsvPath.Remove(tmpCsvPath.LastIndexOf("\\"));205 206 if (!Directory.Exists(tmpFolder))207 Directory.CreateDirectory(tmpFolder);208 209 FileHelper.WriteDataTableToCSVFile(dataTable, tmpCsvPath); //Write to csv File210 211 MySqlBulkLoader sqlBC = (MySqlBulkLoader)Convert.ChangeType(sqlBulkCopy, typeof(MySqlBulkLoader));212 MySqlTransaction sqlTran = (MySqlTransaction)Convert.ChangeType(sqlTrasaction, typeof(MySqlTransaction));213 try214 {215 sqlBC.TableName = dataTable.TableName;216 sqlBC.FieldTerminator = "|";217 sqlBC.LineTerminator = "\r\n";218 sqlBC.FileName = tmpCsvPath;219 sqlBC.NumberOfLinesToSkip = 0;220 221 StringCollection strCollection = new StringCollection();222 //Mapping Destination Field of Database Table223 for (int i = 0; i < dataTable.Columns.Count; i )224 {225 strCollection.Add(dataTable.Columns[i].ColumnName);226 }227 sqlBC.Columns = strCollection;228 229 //Write DataTable230 sqlBC.Load();231 232 sqlTran.Commit();233 result = true;234 }235 catch (MySqlException mse)236 {237 result = false;238 sqlTran.Rollback();239 throw mse;240 }241 finally242 {243 //T、T1给默认值为Null, 由系统调用GC244 sqlBC = null;245 sqlBulkCopy = default(T);246 sqlTrasaction = default(T1);247 }248 File.Delete(tmpCsvPath);249 return result;250 }251 252 public bool BulkInsert(DataTable dataTable)253 {254 bool result = false;255 if (dataTable != null && dataTable.Rows.Count > 0)256 {257 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>())258 {259 mySqlCon.Open();260 MySqlTransaction sqlTran = mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted);261 MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(mySqlCon);262 sqlBulkCopy.Timeout = 60;263 264 result = BulkInsert(sqlBulkCopy, dataTable, sqlTran);265 }266 }267 return result;268 }269 270 public bool BulkInsert(DataSet dataSet)271 {272 bool result = false;273 if (dataSet != null && dataSet.Tables.Count > 0)274 {275 using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>())276 {277 mySqlCon.Open();278 MySqlTransaction sqlTran = mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted);279 MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(mySqlCon);280 sqlBulkCopy.Timeout = 60;281 282 if (dataSet.Tables.Count == 1)283 result = BulkInsert(sqlBulkCopy, dataSet.Tables[0], sqlTran);284 else285 {286 foreach (DataTable dt in dataSet.Tables)287 {288 result = BulkInsert(sqlBulkCopy, dt, sqlTran);289 if (!result)290 break;291 }292 }293 }294 }295 return result;296 }297 298 public string DBName299 {300 get { return this._dbName; }301 }302 303 304 public T GetConnection<T>()305 {306 T result = default(T);307 if (string.IsNullOrEmpty(this._dbName))308 result = DALFactory.GetDatabaseConnection<T>(this._dBVender);309 else310 result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName);311 return result;312 }313 314 #endregion315 316 private void fillParameters(MySqlCommand mySqlCmd, string[] paramNames, object[] paramValues)317 {318 if (paramNames == null || paramNames.Length == 0)319 return;320 if (paramValues == null || paramValues.Length == 0)321 return;322 323 if (paramNames.Length != paramValues.Length)324 throw new ArgumentException("The Name Count of parameters does not match its Value Count! ");325 326 string name;327 object value;328 for (int i = 0; i < paramNames.Length; i )329 {330 name = paramNames[i];331 value = paramValues[i];332 if (value != null)333 mySqlCmd.Parameters.AddWithValue(name, value);334 else335 mySqlCmd.Parameters.AddWithValue(name, DBNull.Value);336 }337 }338 }339 }View Code
来源：http://www.icode9.com/content-2-174901.html
联系客服
