mysql批量新增20000数据_批量插入数据, 将DataTable里的数据批量写入数据库的方法...

大量数据导入操作, 也就是直接将DataTable里的内容写入到数据库

通用方法: 拼接Insert语句, 好土鳖

?

1. MS Sql Server:?? 使用SqlBulkCopy

2. MySql:

adapter.update()批量更新

MySqlBulkLoader, 这个是从文件里边到的, 有个实现是先将DATATable编程CSV文件, 在用MySqlBulkLoader导入MySql

//参考代码

1 Function to create .csv file from DataTable (you can skip this, if you already have csv file)

2 public static void CreateCSVfile(DataTable dtable, string strFilePath)

3 {

4 StreamWriter sw = new StreamWriter(strFilePath, false);

5 int icolcount = dtable.Columns.Count;

6 foreach (DataRow drow in dtable.Rows)

7 {

8 for (int i = 0; i < icolcount; i )

9 {

10 if (!Convert.IsDBNull(drow[i]))

11 {

12 sw.Write(drow[i].ToString());

13 }

14 if (i < icolcount - 1)

15 {

16 sw.Write(",");

17 }

18 }

19 sw.Write(sw.NewLine);

20 }

21 sw.Close();

22 sw.Dispose();

23 }

24

25 //2. Import data into MySQL database

26 private void ImportMySQL()

27 {

28 DataTable orderDetail = new DataTable("ItemDetail");

29 DataColumn c = new DataColumn(); // always

30 orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));

31 orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32")));

32 orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32")));

33 orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32")));

34 orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal")));

35 orderDetail.Columns["total"].Expression = "value/(length*breadth)"; //Adding dummy entries

36 DataRow dr = orderDetail.NewRow();

37 dr["ID"] = 1;

38 dr["value"] = 50;

39 dr["length"] = 5;

40 dr["breadth"] = 8;

41 orderDetail.Rows.Add(dr);

42 dr = orderDetail.NewRow();

43 dr["ID"] = 2;

44 dr["value"] = 60;

45 dr["length"] = 15;

46 dr["breadth"] = 18;

47 orderDetail.Rows.Add(dr); //Adding dummy entries

48 string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;";

49 string strFile = "/TempFolder/MySQL" DateTime.Now.Ticks.ToString() ".csv"; //Create directory if not exist... Make sure directory has required rights..

50 if (!Directory.Exists(Server.MapPath("~/TempFolder/")))

51 Directory.CreateDirectory(Server.MapPath("~/TempFolder/")); //If file does not exist then create it and right data into it..

52 if (!File.Exists(Server.MapPath(strFile)))

53 {

54 FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write);

55 fs.Close();

56 fs.Dispose();

57 }

58 //Generate csv file from where data read

59 CreateCSVfile(orderDetail, Server.MapPath(strFile));

60 using (MySqlConnection cn1 = new MySqlConnection(connectMySQL))

61 {

62 cn1.Open();

63 MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1);

64 bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database...

65 bcp1.FieldTerminator = ",";

66 bcp1.LineTerminator = "\r\n";

67 bcp1.FileName = Server.MapPath(strFile);

68 bcp1.NumberOfLinesToSkip = 0;

69 bcp1.Load(); //Once data write into db then delete file..

70 try

71 {

72 File.Delete(Server.MapPath(strFile));

73 }

74 catch (Exception ex)

75 {

76 string str = ex.Message;

77 }

78 }

79 }

View Code

3. MS Access: 只能用批量更新了, adapter.update()

备注:?? 此处先标记个思路, 等我这实现完了, 贴个示例

==============

其实早就做完了, 都忘记这回事了... 今天看到这篇, 补一下代码

//枚举

1 using System;

2 using System.Collections.Generic;

3 using System.Linq;

4 using System.Text;

5

6 namespace ETLUtilityDAL.Enums

7 {

8 public enum DatabaseType

9 {

10 MSSql,

11 MySql,

12 MSAccess,

13 Oracle

14 }

15 }

View Code

//公共方法

//DALFactory.cs

1 using System;

2 using System.Collections.Generic;

3 using System.Linq;

4 using System.Text;

5

6 using System.Configuration;

7 using ETLUtilityDAL.Enums;

8 using ETLUtilityDAL.Interfaces;

9 using ETLUtilityDAL.Implement;

10 using System.Data.SqlClient;

11

12 namespace ETLUtilityDAL.Common

13 {

14 ///

15 /// 数据库访问工厂, 用于产生相应类型的数据库实例

16 ///

17 public class DALFactory

18 {

19 private static readonly Dictionary dictConnectionStrs = new Dictionary();

20 private static readonly DatabaseType currentDB = (DatabaseType)Enum.Parse(typeof(DatabaseType), ConfigurationManager.AppSettings["CurrentDatabase"]);

21

22 ///

23 /// 静态构造函数, 用于初始化数据库连接串字典

24 ///

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 ///

48 /// 返回连接串字典以供查看

49 ///

50 public static Dictionary ConnectionStringsDictionary

51 {

52 get { return dictConnectionStrs; }

53 }

54

55 ///

56 /// 根据数据库的类型获得有固定数据库名称的泛型类型的数据库连接对象

57 ///

58 /// T类型, 表示泛型类型的数据库连接对象

59 /// System.Enum类型, 表示数据库的类型

60 /// T类型, 返回泛型类型的数据库连接对象

61 public static T GetDatabaseConnection(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 ///

71 /// 根据数据库的类型获得指定数据库名称的泛型类型的数据库连接对象

72 ///

73 /// T类型, 表示泛型类型的数据库连接对象

74 /// System.Enum类型, 表示数据库的类型

75 /// System.String, 表示指定的数据库名称

76 /// T类型, 返回泛型类型的数据库连接对象

77 public static T GetDatabaseConnection(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 ///

87 /// 根据数据库的类型获得固定数据库名称的数据库访问工具类DBHelper

88 ///

89 /// System.Enum类型, 表示数据库的类型

90 /// Interface, 根据不同的数据库类型返回不同的工具类的实现

91 public static IDBHelper GetDBHelper(DatabaseType dbType)

92 {

93 #region

94 switch (dbType)

95 {

96 case DatabaseType.MSSql:

97 return new MSSqlDBHelper();

98 case DatabaseType.MSAccess:

99 return new MSAccessDBHelper();

100 case DatabaseType.MySql:

101 return new MySqlDBHelper();

102 case DatabaseType.Oracle:

103 goto default;

104 default:

105 return null;

106 }

107 #endregion

108 }

109

110 ///

111 /// 根据数据库的类型获得指定数据库名称的数据库访问工具类DBHelper

112 ///

113 /// System.Enum类型, 表示数据库的类型

114 /// System.String, 表示指定的数据库名称

115 /// Interface, 根据不同的数据库名称和类型返回不同的工具类的实现

116 public static IDBHelper GetDBHelper(DatabaseType dbType, string dbName)

117 {

118 #region

119 switch (dbType)

120 {

121 case DatabaseType.MSSql:

122 return new MSSqlDBHelper(dbName);

123 case DatabaseType.MSAccess:

124 return new MSAccessDBHelper(dbName);

125 case DatabaseType.MySql:

126 return new MySqlDBHelper(dbName);

127 case DatabaseType.Oracle:

128 goto default;

129 default:

130 return null;

131 }

132 #endregion

133 }

134

135 ///

136 /// 获得当前正在使用的固定数据库名称的数据库类型的访问工具类Helper

137 ///

138 /// Interface, 根据不同的数据库类型返回不同的工具类的实现

139 public static IDBHelper GetDBHelper()

140 {

141 return GetDBHelper(currentDB);

142 }

143

144 ///

145 /// 获得当前正在使用的指定据库名称的数据库类型的访问工具类Helper

146 ///

147 /// Interface, 根据不同的数据库名称和类型返回不同的工具类的实现

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 (