datatable到sqlite
1 public class DataTableToSQLte 2 { 3 private string tableName; 4 5 public string TableName 6 { 7 get { return tableName; } 8 set { tableName = value; } 9 } 10 private string insertHead; 11 12 public string InsertHead 13 { 14 get { return insertHead; } 15 } 16 17 private string[] separators; 18 19 public string[] Separators 20 { 21 get { return separators; } 22 set { separators = value; } 23 } 24 25 private string insertCmdText; 26 27 private int colCount; 28 private string[] fields; 29 30 public DataTableToSQLte(DataTable dt) 31 { 32 List<string> myFields = new List<string>(); 33 List<string> mySeparators = new List<string>(); 34 List<string> valueVars = new List<string>();// insert command text 35 colCount = dt.Columns.Count; 36 37 for (int i = 0; i < colCount; i++) 38 { 39 string colName = dt.Columns[i].ColumnName; 40 myFields.Add(colName); 41 mySeparators.Add(GetSeperator(dt.Columns[i].DataType.ToString())); 42 valueVars.Add("@" + colName); 43 } 44 insertHead = string.Format("insert into {0} ({1})" 45 , dt.TableName 46 , string.Join(",", myFields.ToArray())); 47 separators = mySeparators.ToArray(); 48 49 insertCmdText = string.Format("{0} values ({1})", insertHead 50 , string.Join(",", valueVars.ToArray())); 51 52 fields = myFields.ToArray(); 53 54 } 55 56 private string GetSeperator(string typeName) 57 { 58 string result = string.Empty; 59 switch (typeName) 60 { 61 case "System.String": 62 result = "'"; 63 break; 64 65 default: 66 result = typeName; 67 break; 68 } 69 70 return result; 71 } 72 73 74 public string GenInsertSql(DataRow dr) 75 { 76 List<string> strs = new List<string>(); 77 for (int i = 0; i < colCount; i++) 78 { 79 if (DBNull.Value == dr[i]) //null or DBNull 80 strs.Add("null"); 81 else 82 strs.Add(string.Format("{0}{1}{0}", separators[i], dr[i].ToString())); 83 } 84 return string.Format("{0} values ({1})", insertHead, string.Join(",", strs.ToArray())); 85 } 86 87 public void ImportToSqliteBatch(DataTable dt, string dbFullName) 88 { 89 string strConn = string.Format("data source={0}", dbFullName); 90 using (SQLiteConnection conn = new SQLiteConnection(strConn)) 91 { 92 using (SQLiteCommand insertCmd = conn.CreateCommand()) 93 { 94 insertCmd.CommandText = insertCmdText; 95 conn.Open(); 96 SQLiteTransaction tranction = conn.BeginTransaction(); 97 foreach (DataRow dr in dt.Rows) 98 { 99 for (int i = 0; i < colCount; i++) 100 { 101 object o = null; 102 string paraName = "@" + fields[i]; 103 if (DBNull.Value != dr[fields[i]]) 104 o = dr[fields[i]]; 105 insertCmd.Parameters.AddWithValue(paraName, o); 106 } 107 insertCmd.ExecuteNonQuery(); 108 } 109 tranction.Commit(); 110 } 111 } 112 } 113 114 115 private void Example() 116 { 117 string dbName = AppDomain.CurrentDomain.BaseDirectory + "test.db"; 118 DataTable dt = MyCommon.ConvertXmlToDataTable(MyCommon.ReadXmlStringFromFile("Dt1.xml")); 119 DataTableToSQLte myTabInfo = new DataTableToSQLte(dt); 120 myTabInfo.ImportToSqliteBatch(dt, dbName); 121 //MessageBox.Show("Ok!"); 122 123 124 } 125 }
转载自:https://blog.csdn.net/qq_42678477/article/details/81660682?utm_medium=distribute.pc_relevant_bbs_down.none-task-blog-baidujs-1.nonecase