Exemple #1
0
        public void ExecuteDataToAccess(string accessFileFullpath, string querySql, DateTime startTime, DateTime endTime)
        {
            //获取数据表构架
            DataTable dt = this.GetTableStructure(querySql, startTime, endTime);

            if (dt.Columns.Count == 0)
            {
                throw new Exception($"执行语句[{DataBaseHelper.CovertedExecuteSql(querySql, startTime, endTime)}]查询,数据表架构列为空");
            }
            DataFieldTypeCollection dataTypeCollection = SQLiteHelper.GetDataStruct(dt);
            string accessConnString = DataBaseHelper.CreateAccessFileConnectionString(accessFileFullpath);

            //Insert语句
            string insertSql = DataBaseHelper.CreateInsertSql(dataTypeCollection);

            //创建SQLite数据库
            AccessHelper.CreateDataBase(accessConnString, accessFileFullpath, dataTypeCollection);

            using (OleDbConnection targetconn = new OleDbConnection(accessConnString))
            {
                targetconn.Open();
                using (IDbCommand targetcmd = new OleDbCommand(insertSql, targetconn))
                {
                    //targetcmd.CommandText = insertSql;

                    #region 数据库操作部分
                    using (IDbConnection sourceconn = new SqlConnection(SQlServerHelper.MyConnectStr))
                    {
                        sourceconn.Open();
                        using (IDataReader sourceReader = this.ExecuteReader(querySql, startTime, endTime))
                        {
                            while (sourceReader.Read())
                            {
                                foreach (DataFieldType type in dataTypeCollection)
                                {
                                    int len = sourceReader[type.FiledName].ToString().Length;
                                    if (len > 255)
                                    {
                                        OleDbParameter oldb = new OleDbParameter(type.FieldParameterName, OleDbType.VarChar, len + 5);
                                        oldb.Value = sourceReader[type.FiledName].ToString();
                                        targetcmd.Parameters.Add(oldb);
                                    }
                                    else
                                    {
                                        targetcmd.Parameters.Add(new OleDbParameter(type.FieldParameterName, sourceReader[type.FiledName].ToString()));
                                    }
                                }

                                targetcmd.ExecuteNonQuery();
                                targetcmd.Parameters.Clear();
                            }

                            sourceReader.Close();
                        }

                        sourceconn.Close();
                    }
                    #endregion

                    targetcmd.Dispose();
                }

                targetconn.Close();
            }
        }