批量插入数据, 将DataTable里的数据批量写入数据库的方法
时间：2021-07-01 10:21:17
帮助过：14人阅读
的批量插入数据, 将DataTable里的数据批量写入数据库的方法
//参考代码
//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()
备注: 此处先标记个思路, 等我这实现完了, 贴个示例
==============
其实早就做完了, 都忘记这回事了... 今天看到这篇, 补一下代码
//枚举
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ETLUtilityDAL.Enums
{
public enum DatabaseType
{
MSSql,
MySql,
MSAccess,
Oracle
}
}
//公共方法
//DALFactory.cs
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
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结点的操作
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;
}
}
}
//接口
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实现
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ETLUtilityDAL.Interfaces;
using ETLUtilityDAL.Enums;
using ETLUt
