批量插入数据, 将DataTable里的数据批量写入数据库的方法
大量数据导入操作, 也就是直接将DataTable里的内容写入到数据库
通用方法: 拼接Insert语句, 好土鳖
1. MS Sql Server: 使用SqlBulkCopy
2. MySql:
adapter.update()批量更新
MySqlBulkLoader, 这个是从文件里边到的, 有个实现是先将DATATable编程CSV文件, 在用MySqlBulkLoader导入MySql
//参考代码
View Code
//1. Function to create .csv file from DataTable (you can skip this, if you already have csv file) public static void CreateCSVfile(DataTable dtable, string strFilePath) { StreamWriter sw = new StreamWriter(strFilePath, false); int icolcount = dtable.Columns.Count; foreach (DataRow drow in dtable.Rows) { for (int i = 0; i < icolcount; i++) { if (!Convert.IsDBNull(drow[i])) { sw.Write(drow[i].ToString()); } if (i < icolcount - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); sw.Dispose(); } //2. Import data into MySQL database private void ImportMySQL() { DataTable orderDetail = new DataTable("ItemDetail"); DataColumn c = new DataColumn(); // always orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal"))); orderDetail.Columns["total"].Expression = "value/(length*breadth)"; //Adding dummy entries DataRow dr = orderDetail.NewRow(); dr["ID"] = 1; dr["value"] = 50; dr["length"] = 5; dr["breadth"] = 8; orderDetail.Rows.Add(dr); dr = orderDetail.NewRow(); dr["ID"] = 2; dr["value"] = 60; dr["length"] = 15; dr["breadth"] = 18; orderDetail.Rows.Add(dr); //Adding dummy entries string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;"; string strFile = "/TempFolder/MySQL" + DateTime.Now.Ticks.ToString() + ".csv"; //Create directory if not exist... Make sure directory has required rights.. if (!Directory.Exists(Server.MapPath("~/TempFolder/"))) Directory.CreateDirectory(Server.MapPath("~/TempFolder/")); //If file does not exist then create it and right data into it.. if (!File.Exists(Server.MapPath(strFile))) { FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write); fs.Close(); fs.Dispose(); } //Generate csv file from where data read CreateCSVfile(orderDetail, Server.MapPath(strFile)); using (MySqlConnection cn1 = new MySqlConnection(connectMySQL)) { cn1.Open(); MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1); bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database... bcp1.FieldTerminator = ","; bcp1.LineTerminator = "\r\n"; bcp1.FileName = Server.MapPath(strFile); bcp1.NumberOfLinesToSkip = 0; bcp1.Load(); //Once data write into db then delete file.. try { File.Delete(Server.MapPath(strFile)); } catch (Exception ex) { string str = ex.Message; } } }
3. MS Access: 只能用批量更新了, adapter.update()
备注: 此处先标记个思路, 等我这实现完了, 贴个示例
==============
其实早就做完了, 都忘记这回事了... 今天看到这篇, 补一下代码
//枚举
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ETLUtilityDAL.Enums { public enum DatabaseType { MSSql, MySql, MSAccess, Oracle } }
//公共方法
//DALFactory.cs
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using ETLUtilityDAL.Enums; using ETLUtilityDAL.Interfaces; using ETLUtilityDAL.Implement; using System.Data.SqlClient; namespace ETLUtilityDAL.Common { /// <summary> /// 数据库访问工厂, 用于产生相应类型的数据库实例 /// </summary> public class DALFactory { private static readonly Dictionary<string, string> dictConnectionStrs = new Dictionary<string, string>(); private static readonly DatabaseType currentDB = (DatabaseType)Enum.Parse(typeof(DatabaseType), ConfigurationManager.AppSettings["CurrentDatabase"]); /// <summary> /// 静态构造函数, 用于初始化数据库连接串字典 /// </summary> static DALFactory() { getConnectionDictionary(); } private static void getConnectionDictionary() { ConnectionStringSettingsCollection cssc = ConfigurationManager.ConnectionStrings; string tempConStr = ""; foreach (string str in Enum.GetNames(typeof(DatabaseType))) try { tempConStr = cssc[str.Trim().ToLower()].ConnectionString; if (!string.IsNullOrEmpty(tempConStr)) dictConnectionStrs.Add(str, tempConStr); } catch (Exception ex) { //throw ex; } } /// <summary> /// 返回连接串字典以供查看 /// </summary> public static Dictionary<string,string> ConnectionStringsDictionary { get { return dictConnectionStrs; } } /// <summary> /// 根据数据库的类型获得有固定数据库名称的泛型类型的数据库连接对象 /// </summary> /// <typeparam name="T">T类型, 表示泛型类型的数据库连接对象</typeparam> /// <param name="dbType">System.Enum类型, 表示数据库的类型</param> /// <returns>T类型, 返回泛型类型的数据库连接对象</returns> public static T GetDatabaseConnection<T>(DatabaseType dbType) { string dbTypeStr = Enum.GetName(typeof(DatabaseType), dbType); if(dictConnectionStrs.Keys.Contains(dbTypeStr)) return (T)Convert.ChangeType(Activator.CreateInstance(typeof(T), dictConnectionStrs[dbTypeStr]),typeof(T)); else return default(T); } /// <summary> /// 根据数据库的类型获得指定数据库名称的泛型类型的数据库连接对象 /// </summary> /// <typeparam name="T">T类型, 表示泛型类型的数据库连接对象</typeparam> /// <param name="dbType">System.Enum类型, 表示数据库的类型</param> /// <param name="dbName">System.String, 表示指定的数据库名称</param> /// <returns>T类型, 返回泛型类型的数据库连接对象</returns> public static T GetDatabaseConnection<T>(DatabaseType dbType, string dbName) { string dbTypeStr = Enum.GetName(typeof(DatabaseType), dbType); if (dictConnectionStrs.Keys.Contains(dbTypeStr) && !string.IsNullOrEmpty(dbName)) return (T)Convert.ChangeType(Activator.CreateInstance(typeof(T), dictConnectionStrs[dbTypeStr].Replace("*",dbName)), typeof(T)); else return default(T); } /// <summary> /// 根据数据库的类型获得固定数据库名称的数据库访问工具类DBHelper /// </summary> /// <param name="dbType">System.Enum类型, 表示数据库的类型</param> /// <returns>Interface, 根据不同的数据库类型返回不同的工具类的实现</returns> public static IDBHelper GetDBHelper(DatabaseType dbType) { #region switch (dbType) { case DatabaseType.MSSql: return new MSSqlDBHelper(); case DatabaseType.MSAccess: return new MSAccessDBHelper(); case DatabaseType.MySql: return new MySqlDBHelper(); case DatabaseType.Oracle: goto default; default: return null; } #endregion } /// <summary> /// 根据数据库的类型获得指定数据库名称的数据库访问工具类DBHelper /// </summary> /// <param name="dbType">System.Enum类型, 表示数据库的类型</param> /// <param name="dbName">System.String, 表示指定的数据库名称</param> /// <returns>Interface, 根据不同的数据库名称和类型返回不同的工具类的实现</returns> public static IDBHelper GetDBHelper(DatabaseType dbType, string dbName) { #region switch (dbType) { case DatabaseType.MSSql: return new MSSqlDBHelper(dbName); case DatabaseType.MSAccess: return new MSAccessDBHelper(dbName); case DatabaseType.MySql: return new MySqlDBHelper(dbName); case DatabaseType.Oracle: goto default; default: return null; } #endregion } /// <summary> /// 获得当前正在使用的固定数据库名称的数据库类型的访问工具类Helper /// </summary> /// <returns>Interface, 根据不同的数据库类型返回不同的工具类的实现</returns> public static IDBHelper GetDBHelper() { return GetDBHelper(currentDB); } /// <summary> /// 获得当前正在使用的指定据库名称的数据库类型的访问工具类Helper /// </summary> /// <returns>Interface, 根据不同的数据库名称和类型返回不同的工具类的实现</returns> public static IDBHelper GetDBHelper(string dbName) { return GetDBHelper(currentDB,dbName); } } }
//FileHelper.cs
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Data; namespace ETLUtilityDAL.Common { public class FileHelper { public static string ReadFileToString(string fileFullPath, Encoding codeType) { string result = ""; if (string.IsNullOrEmpty(fileFullPath)) throw new ArgumentNullException( "fileFullPath","File path can not be null or empty! "); using (FileStream fileStream = new FileStream(fileFullPath, FileMode.OpenOrCreate, FileAccess.Read)) { if (!File.Exists(fileFullPath)) throw new FileNotFoundException("File not found! "); } using (StreamReader sReader = new StreamReader(fileFullPath, codeType)) { try { result = sReader.ReadToEnd(); } catch (Exception ex) { throw new IOException(ex.Message); } } return result; } public static string ReadFileToString(string fileFullPath) { return ReadFileToString(fileFullPath, Encoding.Default); } public static void WriteDataTableToCSVFile(DataTable dataTable, string fileFullPath, Encoding codeType) { using (Stream stream = new FileStream(fileFullPath,FileMode.Create,FileAccess.Write)) using (StreamWriter swriter = new StreamWriter(stream, codeType)) { try { int icolcount = dataTable.Columns.Count; foreach (DataRow drow in dataTable.Rows) { for (int i = 0; i < icolcount; i++) { if (!Convert.IsDBNull(drow[i])) { swriter.Write(drow[i].ToString()); } if (i < icolcount - 1) { swriter.Write("|"); } } swriter.Write(swriter.NewLine); } } catch (Exception ex) { throw new IOException(ex.Message); } } } public static void WriteDataTableToCSVFile(DataTable dataTable, string fileFullPath) { WriteDataTableToCSVFile(dataTable, fileFullPath, Encoding.Default); } public static string[] GetFileFullPathList(string directoryPath, string fileType, bool IsRecursive) { return IsRecursive ? Directory.GetFiles(directoryPath, fileType, SearchOption.AllDirectories) : Directory.GetFiles(directoryPath, fileType, SearchOption.TopDirectoryOnly); } public static string[] GetSubDirectorys(string directoryPath, string containsName, bool IsRecursive) { return IsRecursive ? Directory.GetDirectories(directoryPath, containsName, SearchOption.AllDirectories) : Directory.GetDirectories(directoryPath, containsName, SearchOption.TopDirectoryOnly); } public static void WriteStringToFile(string fileFullPath, bool isAppend ,string fileContent) { WriteStringToFile(fileFullPath, isAppend, fileContent, Encoding.Default); } public static void WriteStringToFile(string fileFullPath, bool isAppend, string fileContent, Encoding codeType) { //using (FileStream fileStream = new FileStream(fileFullPath, FileMode.OpenOrCreate, FileAccess.Write)) using (StreamWriter sWriter = new StreamWriter(fileFullPath,isAppend,codeType)) { try { if (!File.Exists(fileFullPath)) File.Create(fileFullPath); sWriter.Write(fileContent); } catch (Exception ex) { throw new IOException(ex.Message); } } } } }
//XMLHelper.cs, 用List模拟堆栈实现XML结点的操作
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Xml; using System.Xml.XPath; using System.Xml.Schema; using ETLUtilityDAL.Enums; using System.Collections.Specialized; using ETLUtilityModel; using ETLUtilityModel.Enums; namespace ETLUtilityDAL.Common { public class XMLHelper { #region XMLStream public static List<ETLXmlNode> GetAllNodesFromXMLFile(string xmlFileFullName) { List<ETLXmlNode> xmlNodeLst = new List<ETLXmlNode>(); using (Stream stream = new FileStream(xmlFileFullName, FileMode.Open, FileAccess.Read)) using (XmlTextReader xmlTxtReader = new XmlTextReader(stream)) { int fathId = 0; //root node: 0 int elemCount = 1; //Element Count XmlNodeType nodeType; ETLXmlNode xNode; ETLXmlNodeStack nodStack = ETLXmlNodeStack.CreateETLXmlNodeStack(); while (xmlTxtReader.Read()) { xNode = null; nodeType = xmlTxtReader.NodeType; switch (nodeType) { case XmlNodeType.Element: bool isEmpty = false; if (isEmpty = xmlTxtReader.IsEmptyElement) xNode = new ETLXmlNode(elemCount, xmlTxtReader.Name, ETLXmlNodeType.OddNode, fathId); else xNode = new ETLXmlNode(elemCount, xmlTxtReader.Name, ETLXmlNodeType.EvenNode, fathId); fathId = elemCount; elemCount++; //Deal with the Attribute if (xmlTxtReader.HasAttributes) { NameValueCollection nvc = xNode.NodeAttributes; for (int i = 0; i < xmlTxtReader.AttributeCount; i++) { xmlTxtReader.MoveToAttribute(i); nvc.Add(xmlTxtReader.Name, xmlTxtReader.Value); } } if (isEmpty) { xmlNodeLst.Add(xNode); fathId = xNode.FatherNodeId; } else ETLXmlNodeStack.Push(xNode); break; case XmlNodeType.EndElement: xNode = ETLXmlNodeStack.Pop(); xmlNodeLst.Add(xNode); fathId = xNode.FatherNodeId; break; case XmlNodeType.Text: xNode = ETLXmlNodeStack.Pop(); xNode.NodeText = xmlTxtReader.Value; ETLXmlNodeStack.Push(xNode); break; default: break; } } } return xmlNodeLst; } #endregion #region XPath --- Not Implement #endregion #region XPathExpress --- Not Implement #endregion #region Common public static bool IsValidateXmlFile(string xmlSchemaFileFullName) { bool result = false; using (Stream stream = new FileStream(xmlSchemaFileFullName, FileMode.Open, FileAccess.Read)) using (XmlTextReader xmlTxtReader = new XmlTextReader(stream)) { XmlSchema schema = XmlSchema.Read(stream, new ValidationEventHandler(dealSchemaValidation)); XmlReaderSettings settings = new XmlReaderSettings(); settings.Schemas.Add(schema); settings.ValidationType = ValidationType.Schema; settings.ValidationEventHandler += new ValidationEventHandler(dealSchemaValidation); //Execute Validate try { while (xmlTxtReader.Read()) { } result = true; } catch (XmlSchemaValidationException xsve) { result = false; throw xsve; } } return result; } private static void dealSchemaValidation(object sender, System.Xml.Schema.ValidationEventArgs e) { throw new XmlSchemaValidationException(string.Format("Validation Error, Error Level:{0}\r\n. Error Details:\r\n{1}", e.Severity, e.Message)); } #endregion static void TestMethod() { #region 使用XML流处理, 每次只处理一个节点, 速度快, 但缺点是: 不支持结构化查询, 适合从头到尾一次性处理 //使用xml流输出字符 using (System.Xml.XmlWriter xmlwriter = System.Xml.XmlWriter.Create("Output.xml")) { xmlwriter.WriteStartDocument(); xmlwriter.WriteStartElement("human"); //</humen> xmlwriter.WriteStartElement("man"); //子元素 //写元素属性 xmlwriter.WriteAttributeString("name", "father"); //属性 xmlwriter.WriteString("Mike"); //文本区 xmlwriter.WriteEndElement(); xmlwriter.WriteElementString("women", "jean"); //<women>jean</women> xmlwriter.WriteStartElement("children"); xmlwriter.WriteAttributeString("name", "kiddy"); xmlwriter.WriteString("nickey kiddy"); //文本区 xmlwriter.WriteEndElement(); xmlwriter.WriteEndElement(); } #endregion #region 使用优化的XPath--XPathDocument类, 速度快, 也支持结构化的查询方式. 缺点: 只能读不能写 //1.创建XPathDocument对象 System.Xml.XPath.XPathDocument xpdoc = new System.Xml.XPath.XPathDocument("XMLOperation.xml"); //2.通过导航器进行查找 System.Xml.XPath.XPathNavigator xpnav = xpdoc.CreateNavigator(); //3.经过编译的XPath string xpath = "/configuration/system.web/httpHandlers/cellphone"; System.Xml.XPath.XPathExpression xpe = System.Xml.XPath.XPathExpression.Compile(xpath); //4.使用导航器的Select迭代器进行查找, 查找的结果还是导航器 System.Xml.XPath.XPathNavigator resultNav = xpnav.SelectSingleNode(xpe); Console.WriteLine("----------XPathDocument的查询单个结果----------"); Console.WriteLine(resultNav.Value); //查找多个结果 Console.WriteLine("----------XPathDocument的查询多个结果----------"); xpath = "/configuration/system.web/httpHandlers/add/@type"; //查找add元素的type属性内容 xpe = System.Xml.XPath.XPathExpression.Compile(xpath); System.Xml.XPath.XPathNodeIterator xpniter = xpnav.Select(xpe); foreach (System.Xml.XPath.XPathNavigator xpn in xpniter) { Console.WriteLine(xpn.Value); } #endregion } } /// <summary> /// 用List模拟堆栈操作, 用于读取XML中的结点 /// </summary> public class ETLXmlNodeStack { private List<ETLXmlNode> _xmlStack; private ETLXmlNodeStack() { this._xmlStack = new List<ETLXmlNode>(100); } private static readonly ETLXmlNodeStack inner; static ETLXmlNodeStack() { inner = new ETLXmlNodeStack(); } public static ETLXmlNodeStack ETLXmlNodeStackInfo { get { return inner; } } public static int Count { get { return inner._xmlStack.Count; } } public static ETLXmlNodeStack CreateETLXmlNodeStack() { return inner; } public static void Push(ETLXmlNode etlXmlNode) { inner._xmlStack.Add(etlXmlNode); } public static ETLXmlNode Pop() { ETLXmlNode result = null; if (inner._xmlStack != null && inner._xmlStack.Count > 0) { result = inner._xmlStack[inner._xmlStack.Count - 1]; inner._xmlStack.RemoveAt(inner._xmlStack.Count - 1); } return result; } } }
//接口
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using ETLUtilityDAL.Enums; namespace ETLUtilityDAL.Interfaces { /// <summary> /// Utility Of Data Access Layer /// </summary> public interface IDBHelper { #region BaseExecute int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues); T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues); T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues); DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues); DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues); #endregion #region TxtExecute int TxtExecuteNonQuery(string sqlText); int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues); T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues); T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues); DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues); DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues); #endregion #region SpExecute int SpExecuteNonQuery(string sqlText); int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues); T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues); T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues); DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues); DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues); #endregion #region Common bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction); bool BulkInsert(DataTable dataTable); bool BulkInsert(DataSet dataSet); string DBName { get; } T GetConnection<T>(); #endregion } }
//MsAccess实现
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using ETLUtilityDAL.Interfaces; using ETLUtilityDAL.Enums; using ETLUtilityDAL.Common; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; namespace ETLUtilityDAL.Implement { public class MSAccessDBHelper : IDBHelper { private DatabaseType _dBVender = DatabaseType.MSAccess; private string _dbName; public MSAccessDBHelper() { this._dbName = ""; } public MSAccessDBHelper(string dbName) { this._dbName = dbName; } #region IDBHelper 成员 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { int result = 0; using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); oleDbCmd.CommandType = cmdType; try { fillParameters(oleDbCmd, paramNames, paramValues); oleDbCon.Open(); result = oleDbCmd.ExecuteNonQuery(); } catch (OleDbException ode) { throw ode; } } return 0; } public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); oleDbCmd.CommandType = cmdType; try { fillParameters(oleDbCmd, paramNames, paramValues); oleDbCon.Open(); result = (T)Convert.ChangeType(oleDbCmd.ExecuteScalar(), typeof(T)); } catch (OleDbException ode) { throw ode; } } return result; } public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); oleDbCmd.CommandType = cmdType; try { fillParameters(oleDbCmd, paramNames, paramValues); oleDbCon.Open(); result = (T)Convert.ChangeType(oleDbCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); } catch (OleDbException ode) { throw ode; } } return result; } public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataTable result = new DataTable(); using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); oleDbCmd.CommandType = cmdType; try { fillParameters(oleDbCmd, paramNames, paramValues); OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd); oledDbDA.Fill(result); } catch (OleDbException ode) { throw ode; } } return result; } public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataSet result = new DataSet(); using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(sqlText, oleDbCon); oleDbCmd.CommandType = cmdType; try { fillParameters(oleDbCmd, paramNames, paramValues); OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd); oledDbDA.Fill(result); } catch (OleDbException se) { throw se; } } return result; } public int TxtExecuteNonQuery(string sqlText) { return ExecNonQuery(sqlText, CommandType.Text, null, null); } public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues) { return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteScalar<T>(string sqlText,string[] paramNames, object[] paramValues) { return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues) { return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues); } public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues); } public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues); } public int SpExecuteNonQuery(string sqlText) { return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null); } public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues) { return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues) { return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues) { return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues) { return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues) { return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues); } #endregion private void fillParameters(OleDbCommand oleDbCmd, string[] paramNames, object[] paramValues) { if (paramNames == null && paramNames.Length == 0) return; if (paramValues == null && paramValues.Length == 0) return; if (paramNames.Length != paramValues.Length) throw new ArgumentException("The Name Count of parameters does not match its Value Count! "); string name; object value; for (int i = 0; i < paramNames.Length; i++) { name = paramNames[i]; value = paramValues[i]; if (value != null) oleDbCmd.Parameters.AddWithValue(name, value); else oleDbCmd.Parameters.AddWithValue(name, DBNull.Value); } } #region IDBHelper Members public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction) { bool result = false; OleDbTransaction sqlTran = (OleDbTransaction)Convert.ChangeType(sqlTrasaction, typeof(OleDbTransaction)); using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { OleDbCommand oleDbCmd = new OleDbCommand(string.Format("select * from {0}", dataTable.TableName), oleDbCon); oleDbCmd.CommandType = CommandType.Text; try { OleDbDataAdapter oledDbDA = new OleDbDataAdapter(oleDbCmd); OleDbCommandBuilder oledCmdBuilder = new OleDbCommandBuilder(oledDbDA); oledDbDA.Update(dataTable); result = true; } catch (OleDbException ode) { result = false; throw ode; } } return result; } public bool BulkInsert(DataTable dataTable) { bool result = false; if (dataTable != null && dataTable.Rows.Count > 0) { using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { oleDbCon.Open(); OleDbTransaction sqlTran = oleDbCon.BeginTransaction(IsolationLevel.ReadCommitted); object obj = null; result = BulkInsert(obj, dataTable, sqlTran); } } return result; } public bool BulkInsert(DataSet dataSet) { bool result = false; if (dataSet != null && dataSet.Tables.Count > 0) { using (OleDbConnection oleDbCon = GetConnection<OleDbConnection>()) { oleDbCon.Open(); OleDbTransaction sqlTran = oleDbCon.BeginTransaction(IsolationLevel.ReadCommitted); object obj = null; if (dataSet.Tables.Count == 1) result = BulkInsert(obj, dataSet.Tables[0], sqlTran); else { foreach (DataTable dt in dataSet.Tables) { result = BulkInsert(obj, dt, sqlTran); if (!result) break; } } } } return result; } public string DBName { get { return this._dbName; } } public T GetConnection<T>() { T result = default(T); if (string.IsNullOrEmpty(this._dbName)) result = DALFactory.GetDatabaseConnection<T>(this._dBVender); else result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName); return result; } #endregion } }
//MsSqlServer实现
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using ETLUtilityDAL.Interfaces; using ETLUtilityDAL.Enums; using ETLUtilityDAL.Common; using ETLUtilityModel.Common; using System.Data; using System.Data.SqlClient; namespace ETLUtilityDAL.Implement { public class MSSqlDBHelper : IDBHelper { private DatabaseType _dBVender = DatabaseType.MSSql; private string _dbName; public MSSqlDBHelper() { this._dbName = ""; } public MSSqlDBHelper(string dbName) { this._dbName = dbName; } #region IDBHelper 成员 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { int result = 0; using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); sqlCon.Open(); result = sqlCmd.ExecuteNonQuery(); } catch (SqlException se) { throw se; } } return result; } public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); sqlCon.Open(); result = (T)Convert.ChangeType(sqlCmd.ExecuteScalar(),typeof(T)); } catch (SqlException se) { throw se; } } return result; } public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); sqlCon.Open(); result = (T)Convert.ChangeType(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); } catch (SqlException se) { throw se; } } return result; } public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataTable result = new DataTable(); using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd); sqlDA.Fill(result); } catch (SqlException se) { throw se; } } return result; } public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataSet result = new DataSet(); using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { SqlCommand sqlCmd = new SqlCommand(sqlText, sqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd); sqlDA.Fill(result); } catch (SqlException se) { throw se; } } return result; } public int TxtExecuteNonQuery(string sqlText) { return ExecNonQuery(sqlText, CommandType.Text, null, null); } public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues) { return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues) { return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues) { return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues); } public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues); } public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues); } public int SpExecuteNonQuery(string sqlText) { return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null); } public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues) { return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues) { return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues) { return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues) { return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues) { return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues); } public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction) { bool result = false; SqlBulkCopy sqlBC = (SqlBulkCopy)Convert.ChangeType(sqlBulkCopy, typeof(SqlBulkCopy)); SqlTransaction sqlTran = (SqlTransaction)Convert.ChangeType(sqlTrasaction, typeof(SqlTransaction)); try { sqlBC.DestinationTableName = dataTable.TableName; //Mapping Destination Field of Database Table for (int i = 0; i < dataTable.Columns.Count; i++) { sqlBC.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName); } //TestCode //string xx = TestUtility.GetColumnDataTypeOfDataTale(dataTable); //Write DataTable sqlBC.WriteToServer(dataTable); sqlTran.Commit(); result = true; } catch(SqlException ex) { result = false; sqlTran.Rollback(); throw ex; } finally { //T、T1给默认值为Null, 由系统调用GC sqlBC.Close(); sqlBulkCopy = default(T); sqlTrasaction = default(T1); } return result; } public bool BulkInsert(DataTable dataTable) { bool result = false; if (dataTable != null && dataTable.Rows.Count > 0) { using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { sqlCon.Open(); SqlTransaction sqlTran = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepIdentity, sqlTran)) { sqlBulkCopy.BatchSize = 20000; sqlBulkCopy.BulkCopyTimeout = 60; result = BulkInsert(sqlBulkCopy,dataTable,sqlTran); } } } return result; } public bool BulkInsert(DataSet dataSet) { bool result = false; if (dataSet != null && dataSet.Tables.Count > 0) { using (SqlConnection sqlCon = GetConnection<SqlConnection>()) { sqlCon.Open(); SqlTransaction sqlTran = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepIdentity, sqlTran)) { sqlBulkCopy.BatchSize = 20000; sqlBulkCopy.BulkCopyTimeout = 60; if (dataSet.Tables.Count == 1) result = BulkInsert(sqlBulkCopy, dataSet.Tables[0], sqlTran); else { foreach (DataTable dt in dataSet.Tables) { result = BulkInsert(sqlBulkCopy, dt, sqlTran); if (!result) break; } } } } } return result; } public string DBName { get { return this._dbName; } } public T GetConnection<T>() { T result = default(T); if (string.IsNullOrEmpty(this._dbName)) result = DALFactory.GetDatabaseConnection<T>(this._dBVender); else result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName); return result; } #endregion private void fillParameters(SqlCommand cmd, string[] paramNames, object[] paramValues) { if (paramNames == null && paramNames.Length == 0) return; if (paramValues == null && paramValues.Length == 0) return; if(paramNames.Length != paramValues.Length) throw new ArgumentException("The Name Count of parameters does not match its Value Count! "); string name; object value; for (int i = 0; i < paramNames.Length; i++) { name = paramNames[i]; value = paramValues[i]; if (value != null) cmd.Parameters.AddWithValue(name, value); else cmd.Parameters.AddWithValue(name, DBNull.Value); } } } }
//MySql实现
View Code
using System; using System.Collections.Generic; using System.Linq; using System.Text; using ETLUtilityDAL.Interfaces; using ETLUtilityDAL.Enums; using ETLUtilityDAL.Common; using MySql.Data.MySqlClient; using System.Data; using System.Collections.Specialized; using System.IO; namespace ETLUtilityDAL.Implement { public class MySqlDBHelper : IDBHelper { private DatabaseType _dBVender = DatabaseType.MySql; private readonly string _tmpBasePath = AppDomain.CurrentDomain.BaseDirectory; private readonly string _tmpCSVFilePattern = "Temp\\{0}.csv"; //0表示文件名称 private string _dbName; public MySqlDBHelper() { this._dbName = ""; } public MySqlDBHelper(string dbName) { this._dbName = dbName; } #region IDBHelper 成员 public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { int result = 0; using (MySqlConnection mySqlCon = GetConnection <MySqlConnection>()) { MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); mySqlCmd.CommandType = cmdType; try { fillParameters(mySqlCmd, paramNames, paramValues); mySqlCon.Open(); result = mySqlCmd.ExecuteNonQuery(); } catch (MySqlException mse) { throw mse; } } return 0; } public T ExecScalar<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); mySqlCmd.CommandType = cmdType; try { fillParameters(mySqlCmd, paramNames, paramValues); mySqlCon.Open(); result = (T)Convert.ChangeType(mySqlCmd.ExecuteScalar(), typeof(T)); } catch (MySqlException mse) { throw mse; } } return result; } public T ExecReader<T>(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { T result = default(T); using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); mySqlCmd.CommandType = cmdType; try { fillParameters(mySqlCmd, paramNames, paramValues); mySqlCon.Open(); result = (T)Convert.ChangeType(mySqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T)); } catch (MySqlException mse) { throw mse; } } return result; } public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataTable result = new DataTable(); using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { MySqlCommand mySqlCmd = new MySqlCommand(sqlText, mySqlCon); mySqlCmd.CommandType = cmdType; try { fillParameters(mySqlCmd, paramNames, paramValues); MySqlDataAdapter mySqlDA = new MySqlDataAdapter(mySqlCmd); mySqlDA.Fill(result); } catch (MySqlException mse) { throw mse; } } return result; } public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues) { DataSet result = new DataSet(); using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { MySqlCommand sqlCmd = new MySqlCommand(sqlText, mySqlCon); sqlCmd.CommandType = cmdType; try { fillParameters(sqlCmd, paramNames, paramValues); MySqlDataAdapter mySqlDA = new MySqlDataAdapter(sqlCmd); mySqlDA.Fill(result); } catch (MySqlException mse) { throw mse; } } return result; } public int TxtExecuteNonQuery(string sqlText) { return ExecNonQuery(sqlText, CommandType.Text, null, null); } public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues) { return ExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteScalar<T>(string sqlText, string[] paramNames, object[] paramValues) { return ExecScalar<T>(sqlText, CommandType.Text, paramNames, paramValues); } public T TxtExecuteReader<T>(string sqlText, string[] paramNames, object[] paramValues) { return ExecReader<T>(sqlText, CommandType.Text, paramNames, paramValues); } public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataTable(sqlText, CommandType.Text, paramNames, paramValues); } public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues) { return ExecDataSet(sqlText, CommandType.Text, paramNames, paramValues); } public int SpExecuteNonQuery(string sqlText) { return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null); } public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues) { return ExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteScalar<T>(string spName, string[] paramNames, object[] paramValues) { return ExecScalar<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public T SpExecuteReader<T>(string spName, string[] paramNames, object[] paramValues) { return ExecReader<T>(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues) { return ExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues); } public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues) { return ExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues); } public bool BulkInsert<T, T1>(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction) { bool result = false; string tmpCsvPath = this._tmpBasePath + string.Format(this._tmpCSVFilePattern, DateTime.Now.Ticks.ToString()); string tmpFolder = tmpCsvPath.Remove(tmpCsvPath.LastIndexOf("\\")); if (!Directory.Exists(tmpFolder)) Directory.CreateDirectory(tmpFolder); FileHelper.WriteDataTableToCSVFile(dataTable, tmpCsvPath); //Write to csv File MySqlBulkLoader sqlBC = (MySqlBulkLoader)Convert.ChangeType(sqlBulkCopy, typeof(MySqlBulkLoader)); MySqlTransaction sqlTran = (MySqlTransaction)Convert.ChangeType(sqlTrasaction, typeof(MySqlTransaction)); try { sqlBC.TableName = dataTable.TableName; sqlBC.FieldTerminator = "|"; sqlBC.LineTerminator = "\r\n"; sqlBC.FileName = tmpCsvPath; sqlBC.NumberOfLinesToSkip = 0; StringCollection strCollection = new StringCollection(); //Mapping Destination Field of Database Table for (int i = 0; i < dataTable.Columns.Count; i++) { strCollection.Add(dataTable.Columns[i].ColumnName); } sqlBC.Columns = strCollection; //Write DataTable sqlBC.Load(); sqlTran.Commit(); result = true; } catch (MySqlException mse) { result = false; sqlTran.Rollback(); throw mse; } finally { //T、T1给默认值为Null, 由系统调用GC sqlBC = null; sqlBulkCopy = default(T); sqlTrasaction = default(T1); } File.Delete(tmpCsvPath); return result; } public bool BulkInsert(DataTable dataTable) { bool result = false; if (dataTable != null && dataTable.Rows.Count > 0) { using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { mySqlCon.Open(); MySqlTransaction sqlTran = mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted); MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(mySqlCon); sqlBulkCopy.Timeout = 60; result = BulkInsert(sqlBulkCopy, dataTable, sqlTran); } } return result; } public bool BulkInsert(DataSet dataSet) { bool result = false; if (dataSet != null && dataSet.Tables.Count > 0) { using (MySqlConnection mySqlCon = GetConnection<MySqlConnection>()) { mySqlCon.Open(); MySqlTransaction sqlTran = mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted); MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(mySqlCon); sqlBulkCopy.Timeout = 60; if (dataSet.Tables.Count == 1) result = BulkInsert(sqlBulkCopy, dataSet.Tables[0], sqlTran); else { foreach (DataTable dt in dataSet.Tables) { result = BulkInsert(sqlBulkCopy, dt, sqlTran); if (!result) break; } } } } return result; } public string DBName { get { return this._dbName; } } public T GetConnection<T>() { T result = default(T); if (string.IsNullOrEmpty(this._dbName)) result = DALFactory.GetDatabaseConnection<T>(this._dBVender); else result = DALFactory.GetDatabaseConnection<T>(this._dBVender, this._dbName); return result; } #endregion private void fillParameters(MySqlCommand mySqlCmd, string[] paramNames, object[] paramValues) { if (paramNames == null || paramNames.Length == 0) return; if (paramValues == null || paramValues.Length == 0) return; if (paramNames.Length != paramValues.Length) throw new ArgumentException("The Name Count of parameters does not match its Value Count! "); string name; object value; for (int i = 0; i < paramNames.Length; i++) { name = paramNames[i]; value = paramValues[i]; if (value != null) mySqlCmd.Parameters.AddWithValue(name, value); else mySqlCmd.Parameters.AddWithValue(name, DBNull.Value); } } } }
