c#中sqlite将datatable复制到另一个数据库中(目的数据库需要先创建表结构)

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     }