C# SqliteDataAdapter类代码示例

 public static int Fill(string commandText, SqliteConnection connection, DataTable dataTable)
 {
     int res = 0;
     using (SqliteCommand command = new SqliteCommand())
     {
         command.Connection = connection;
         command.CommandText = commandText;
         using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(command))
         {
             res = sqlDAdapter.Fill(dataTable);
         }
     }
     return res;
 }

        public override IEnumerable<INodeFactory> GetNodeChildren( IContext context )
        {
            var filter = context.Filter;
            var sql = "SELECT * FROM " + Name;
            if( ! String.IsNullOrEmpty( filter ) )
            {
                sql += " WHERE ( " + filter + " )";
            }
            var command = new SqliteCommand(sql , _context.Connection);
            var table = new DataTable( Name );
            
            context.WriteVerbose( sql );
            using( var data = new SqliteDataAdapter(command) )
            {
                data.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                data.Fill(table);
            }

            foreach( DataRow row in table.Rows )
            {
                yield return new SQLiteRecordNodeFactory( _context, row);
            }
        }

 private void setupRegionSettingsCommands(SqliteDataAdapter da, SqliteConnection conn)
 {
     da.InsertCommand = createInsertCommand("regionsettings", ds.Tables["regionsettings"]);
     da.InsertCommand.Connection = conn;
     da.UpdateCommand = createUpdateCommand("regionsettings", "regionUUID=:regionUUID", ds.Tables["regionsettings"]);
     da.UpdateCommand.Connection = conn;
 }

        /// <summary>
        ///
        /// </summary>
        /// <param name="da"></param>
        /// <param name="conn"></param>
        private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn)
        {
            da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]);
            da.InsertCommand.Connection = conn;

            da.UpdateCommand = createUpdateCommand("landaccesslist", "LandUUID=:landUUID", "AccessUUID=:AccessUUID", ds.Tables["landaccesslist"]);
            da.UpdateCommand.Connection = conn;

            SqliteCommand delete = new SqliteCommand("delete from landaccesslist where LandUUID= :LandUUID and AccessUUID= :AccessUUID");
            delete.Parameters.Add(createSqliteParameter("LandUUID", typeof(String)));
            delete.Parameters.Add(createSqliteParameter("AccessUUID", typeof(String)));
            da.DeleteCommand = delete;
            da.DeleteCommand.Connection = conn;
        }

 /// <summary>
 ///
 /// </summary>
 /// <param name="da"></param>
 /// <param name="conn"></param>
 private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
 {
     da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
     da.InsertCommand.Connection = conn;
 }

        // Temporary attribute while this is experimental

        /***********************************************************************
         *
         *  Public Interface Functions
         *
         **********************************************************************/

        /// <summary>
        /// <list type="bullet">
        /// <item>Initialises RegionData Interface</item>
        /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
        /// </list>
        /// </summary>
        /// <param name="connectionString">the connection string</param>
        public void Initialise(string connectionString)
        {
            try
            {
                if (Util.IsWindows())
                    Util.LoadArchSpecificWindowsDll("sqlite3.dll");

                m_connectionString = connectionString;

                ds = new DataSet("Region");

                m_log.Info("[SQLITE REGION DB]: Sqlite - connecting: " + connectionString);
                m_conn = new SqliteConnection(m_connectionString);
                m_conn.Open();

                SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
                primDa = new SqliteDataAdapter(primSelectCmd);

                SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
                shapeDa = new SqliteDataAdapter(shapeSelectCmd);
                // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);

                SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
                itemsDa = new SqliteDataAdapter(itemsSelectCmd);

                SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
                terrainDa = new SqliteDataAdapter(terrainSelectCmd);

                SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
                landDa = new SqliteDataAdapter(landSelectCmd);

                SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
                landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);

                SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
                regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);

                SqliteCommand regionWindlightSelectCmd = new SqliteCommand(regionWindlightSelect, m_conn);
                regionWindlightDa = new SqliteDataAdapter(regionWindlightSelectCmd);

                SqliteCommand regionEnvironmentSelectCmd = new SqliteCommand(regionEnvironmentSelect, m_conn);
                regionEnvironmentDa = new SqliteDataAdapter(regionEnvironmentSelectCmd);

                SqliteCommand regionSpawnPointsSelectCmd = new SqliteCommand(regionSpawnPointsSelect, m_conn);
                regionSpawnPointsDa = new SqliteDataAdapter(regionSpawnPointsSelectCmd);

                // This actually does the roll forward assembly stuff
                Migration m = new Migration(m_conn, Assembly, "RegionStore");
                m.Update();

                lock (ds)
                {
                    ds.Tables.Add(createPrimTable());
                    setupPrimCommands(primDa, m_conn);

                    ds.Tables.Add(createShapeTable());
                    setupShapeCommands(shapeDa, m_conn);

                    ds.Tables.Add(createItemsTable());
                    setupItemsCommands(itemsDa, m_conn);

                    ds.Tables.Add(createTerrainTable());
                    setupTerrainCommands(terrainDa, m_conn);

                    ds.Tables.Add(createLandTable());
                    setupLandCommands(landDa, m_conn);

                    ds.Tables.Add(createLandAccessListTable());
                    setupLandAccessCommands(landAccessListDa, m_conn);

                    ds.Tables.Add(createRegionSettingsTable());
                    setupRegionSettingsCommands(regionSettingsDa, m_conn);

                    ds.Tables.Add(createRegionWindlightTable());
                    setupRegionWindlightCommands(regionWindlightDa, m_conn);

                    ds.Tables.Add(createRegionEnvironmentTable());
                    setupRegionEnvironmentCommands(regionEnvironmentDa, m_conn);

                    ds.Tables.Add(createRegionSpawnPointsTable());
                    setupRegionSpawnPointsCommands(regionSpawnPointsDa, m_conn);

                    // WORKAROUND: This is a work around for sqlite on
                    // windows, which gets really unhappy with blob columns
                    // that have no sample data in them.  At some point we
//.........这里部分代码省略.........

 public DataAdapter ToDataAdapter()
 {
     SqliteDataAdapter adapter = new SqliteDataAdapter();
     SqliteCommandBuilder builder = new SqliteCommandBuilder( adapter );
     adapter.SelectCommand = _context.CreateCommand( ToSelect() );
     return adapter;
 }

        /// <summary>
        /// Обработва новните във временните таблици
        /// </summary>
        public void TmpNewsRepair(SqliteCommand aCmmndNews)
        {
            // Изтрива старите новини
            aCmmndNews.CommandText =
               "DELETE FROM tmp_news " +
               " WHERE news_pubdate < @FromDate ";
            aCmmndNews.Parameters.Clear();
            aCmmndNews.Parameters.Add("@FromDate", DbType.Date).Value = DateTime.Now.AddMonths(-1);
            aCmmndNews.ExecuteNonQuery();

            // Изтрива новини с бъдеща дата
            aCmmndNews.CommandText =
               "DELETE FROM tmp_news " +
               " WHERE news_pubdate > @FromDate ";
            aCmmndNews.Parameters.Clear();
            aCmmndNews.Parameters.Add("@FromDate", DbType.Date).Value = DateTime.Now.AddDays(1);
            aCmmndNews.ExecuteNonQuery();
            //
            aCmmndNews.Parameters.Clear();
            // Изтрива дублиращите се новини
            aCmmndNews.CommandText =
                "DELETE FROM tmp_news " +
                " WHERE tmp_news.news_id in (SELECT tmp_news.news_id " +
                                           " FROM news " +
                                           " INNER JOIN tmp_news " +
                                           "    ON (news.news_guid = tmp_news.news_guid) " +
                                           "   AND (news.news_site_id = tmp_news.news_site_id)); ";
            aCmmndNews.ExecuteNonQuery();

            // Изтрива новините без картинки
            aCmmndNews.CommandText =
                "DELETE FROM tmp_news " +
                " WHERE news_id NOT IN ( SELECT news_id FROM tmp_enclosure ) ";
            OnLog(LogMessageType.Event, "- Изтрити новините без картинки: " + aCmmndNews.ExecuteNonQuery().ToString());

            // Новини за добавяне
            using (DataTable dtNews = new DataTable())
            {
                aCmmndNews.CommandText =
                    "SELECT tmp_news.news_id " +
                    " FROM tmp_news ";
                using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(aCmmndNews))
                {
                    dtNews.Clear();
                    sqlDAdapter.Fill(dtNews);
                }
                // Ако няма новини - КРАЙ
                if (dtNews.Rows.Count == 0)
                { return; }
            }
            // Определя типа на Enclosure
            aCmmndNews.CommandText =
                "UPDATE tmp_enclosure  " +
                "   SET encl_id = (SELECT encl.encl_id " +
                                 " FROM t_enclosure encl " +
                                 " WHERE tmp_enclosure.encl_type = encl.encl_type); ";
            aCmmndNews.ExecuteNonQuery();

            // Проверка за непознат тип на Enclosure
            aCmmndNews.CommandText = "SELECT tmp_enclosure.encl_type "
                                   + " FROM tmp_enclosure "
                                   + " WHERE ( tmp_enclosure.encl_id = 0 ); ";
            using (SqliteDataReader dr = aCmmndNews.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    string sMsg = "Неразпознат тип на 'Enclosure' !";
                    if (dr.Read())
                    {
                        sMsg += dr["encl_type"].ToString();
                    }
                    throw new Exception(sMsg);
                }
                dr.Close();
            }

            // Категории на новините
            /* прецаква кодовата таблица
            // Стандартизира news_category
            aCmmndNews.CommandText =
                    "UPDATE tmp_news " +
                    " SET news_category = lower(trim(news_category)); ";
            aCmmndNews.ExecuteNonQuery();
            */

            // Определя категориите на новините
            SetNewsCategory(aCmmndNews);
            while (HasNewCategory(aCmmndNews))
            {
                AddNewCategory(aCmmndNews);
                SetNewsCategory(aCmmndNews);
            }
            // Връзка м/у таблициците с категории
            aCmmndNews.CommandText =
                "INSERT INTO x_news_nweb " +
                " ( news_category_id, nweb_category_id ) " +
                " SELECT nct.news_category_id " +
//.........这里部分代码省略.........

        /// <summary>
        ///
        /// </summary>
        /// <param name="da"></param>
        /// <param name="conn"></param>
        private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn)
        {
            da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]);
            da.InsertCommand.Connection = conn;

            da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]);
            da.UpdateCommand.Connection = conn;

            SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID");
            delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
            delete.Connection = conn;
            da.DeleteCommand = delete;
        }

 /// <summary>
 /// XML: Подкатегории
 /// </summary>
 private void WriteXML_Subcategories(SqliteConnection aSqlCnnctn)
 {
     using (DataTable dtNews = new DataTable())
     {
         using (SqliteCommand sqlCmmnd = new SqliteCommand())
         {
             sqlCmmnd.Connection = aSqlCnnctn;
             sqlCmmnd.CommandText =
                 "SELECT nct.* " +
                 " FROM tn_categories_sub nct ";
             using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(sqlCmmnd))
             {
                 dtNews.Clear();
                 sqlDAdapter.Fill(dtNews);
             }
         }
         //
         using (StreamWriter swNews = new StreamWriter(_ApplPath + "\\send_news\\tn_categories_sub.xml", false, Encoding.UTF8))
         {
             swNews.WriteLine("<rss version=\"2.0\">");
             swNews.WriteLine("<channel>");
             swNews.WriteLine("<title>presata.co.cc</title>");
             swNews.WriteLine("<link>http://presata.co.cc</link>");
             swNews.WriteLine("<description>Новини - presata.co.cc</description>");
             swNews.WriteLine("<copyright>http://presata.co.cc/</copyright>");
             swNews.WriteLine("<lastBuildDate>" + DateTime.Now.ToString() + "</lastBuildDate>");
             //
             foreach (DataRow dr in dtNews.Rows)
             {
                 swNews.WriteLine("<item>");
                 swNews.WriteLine("<n_category_sub_id>" + dr["n_category_sub_id"].ToString() + "</n_category_sub_id>");
                 swNews.WriteLine("<n_category_id>" + dr["n_category_id"].ToString() + "</n_category_id>");
                 swNews.WriteLine("<n_category_sub>" + dr["n_category_sub"].ToString() + "</n_category_sub>");
                 swNews.WriteLine("<f_sort>" + dr["f_sort"].ToString() + "</f_sort>");
                 swNews.WriteLine("<is_valid>" + dr["is_valid"].ToString() + "</is_valid>");
                 swNews.WriteLine("</item>");
             }
             swNews.WriteLine("</channel>");
             swNews.WriteLine("</rss>");
             swNews.Close();
         }
     }
 }

 /// <summary>
 /// XML: Информация за новините
 /// </summary>
 private void WriteXML_NewsInfo(SqliteConnection aSqlCnnctn, Int64 aNewsID)
 {
     using (DataTable dtNews = new DataTable())
     {
         using (SqliteCommand sqlCmmnd = new SqliteCommand())
         {
             //
             sqlCmmnd.Connection = aSqlCnnctn;
             sqlCmmnd.CommandText = "SELECT * "
                                  + " FROM news "
                                  + " WHERE news.news_id > " + aNewsID.ToString()
                                  + " ORDER BY news.news_id; ";
             using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(sqlCmmnd))
             {
                 dtNews.Clear();
                 sqlDAdapter.Fill(dtNews);
             }
         }
         //
         using (StreamWriter swNews = new StreamWriter(_ApplPath_NewsInfoXML, false, Encoding.UTF8))
         {
             swNews.WriteLine("<rss version=\"2.0\">");
             swNews.WriteLine("<channel>");
             swNews.WriteLine("<title>presata.co.cc</title>");
             swNews.WriteLine("<link>http://presata.co.cc</link>");
             swNews.WriteLine("<description>Новини - presata.co.cc</description>");
             swNews.WriteLine("<copyright>http://presata.co.cc/</copyright>");
             swNews.WriteLine("<lastBuildDate>" + DateTime.Now.ToString() + "</lastBuildDate>");
             swNews.WriteLine("<newsid>" + aNewsID.ToString() + "</newsid>");
             //
             foreach (DataRow dr in dtNews.Rows)
             {
                 swNews.WriteLine("<item>");
                 swNews.WriteLine("<guid>" + dr["news_id"].ToString() + "</guid>");
                 swNews.WriteLine("<link>" + dr["news_link"].ToString() + "</link>");
                 swNews.WriteLine("</item>");
             }
             swNews.WriteLine("</channel>");
             swNews.WriteLine("</rss>");
             swNews.Close();
         }
     }
 }

 /// <summary>
 /// XML: Новините
 /// </summary>
 private void WriteXML_News(SqliteConnection aSqlCnnctn, Int64 aNewsID)
 {
     using (DataTable dtNews = new DataTable())
     {
         using (SqliteCommand sqlCmmnd = new SqliteCommand())
         {
             sqlCmmnd.Connection = aSqlCnnctn;
             sqlCmmnd.CommandText = "SELECT * "
                                  + " FROM news sws "
                                  + "    , x_news_nweb snn "
                                  + " WHERE sws.news_id > " + aNewsID.ToString()
                                  + "   AND sws.news_category_id = snn.news_category_id "
                                  + "   AND snn.nweb_category_id > 0 "
                                  + " ORDER BY sws.news_id; ";
             using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(sqlCmmnd))
             {
                 dtNews.Clear();
                 sqlDAdapter.Fill(dtNews);
             }
         }
         //
         using (StreamWriter swNews = new StreamWriter(_ApplPath_NewsXML, false, Encoding.UTF8))
         {
             swNews.WriteLine("<rss version=\"2.0\">");
             swNews.WriteLine("<channel>");
             swNews.WriteLine("<title>presata.co.cc</title>");
             swNews.WriteLine("<link>http://presata.co.cc</link>");
             swNews.WriteLine("<description>Новини - presata.co.cc</description>");
             swNews.WriteLine("<copyright>http://presata.co.cc/</copyright>");
             swNews.WriteLine("<lastBuildDate>" + DateTime.Now.ToString() + "</lastBuildDate>");
             swNews.WriteLine("<newsid>" + aNewsID.ToString() + "</newsid>");
             //
             foreach (DataRow dr in dtNews.Rows)
             {
                 swNews.WriteLine("<item>");
                 swNews.WriteLine("<guid>" + dr["news_id"].ToString() + "</guid>");
                 swNews.WriteLine("<title>" + ConvertToXMLText(dr["news_title"].ToString()) + "</title>");
                 swNews.WriteLine("<description>" + ConvertToXMLText(dr["news_description"].ToString()) + "</description>");
                 swNews.WriteLine("<body>" + ConvertToXMLText(dr["news_body"].ToString()) + "</body>");
                 swNews.WriteLine("<pubDate>" + Convert.ToDateTime(dr["news_pubdate"]).ToString("yyyy-MM-dd HH:mm:ss") + "</pubDate>");
                 swNews.WriteLine("<author>" + dr["news_site_id"].ToString() + "</author>");
                 swNews.WriteLine("<category>" + dr["nweb_category_id"].ToString() + "</category>");
                 swNews.WriteLine("</item>");
             }
             swNews.WriteLine("</channel>");
             swNews.WriteLine("</rss>");
             swNews.Close();
         }
     }
 }

 private void setupRegionEnvironmentCommands(SqliteDataAdapter da, SqliteConnection conn)
 {
     da.InsertCommand = createInsertCommand("regionenvironment", ds.Tables["regionenvironment"]);
     da.InsertCommand.Connection = conn;
     da.UpdateCommand = createUpdateCommand("regionenvironment", "region_id=:region_id", ds.Tables["regionenvironment"]);
     da.UpdateCommand.Connection = conn;
 }

 private void setupRegionSpawnPointsCommands(SqliteDataAdapter da, SqliteConnection conn)
 {
     da.InsertCommand = createInsertCommand("spawn_points", ds.Tables["spawn_points"]);
     da.InsertCommand.Connection = conn;
     da.UpdateCommand = createUpdateCommand("spawn_points", "RegionID=:RegionID", ds.Tables["spawn_points"]);
     da.UpdateCommand.Connection = conn;
 }

        /// <summary>
        /// <list type="bullet">
        /// <item>Initialises Inventory interface</item>
        /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
        /// <item>use default URI if connect string string is empty.</item>
        /// </list>
        /// </summary>
        /// <param name="dbconnect">connect string</param>
        public void Initialise(string dbconnect)
        {
            if (!m_Initialized)
            {
                m_Initialized = true;

                if (Util.IsWindows())
                    Util.LoadArchSpecificWindowsDll("sqlite3.dll");

                if (dbconnect == string.Empty)
                {
                    dbconnect = "URI=file:inventoryStore.db,version=3";
                }
                m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
                conn = new SqliteConnection(dbconnect);

                conn.Open();

                Assembly assem = GetType().Assembly;
                Migration m = new Migration(conn, assem, "InventoryStore");
                m.Update();

                SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
                invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
                //            SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);

                SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
                invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);

                ds = new DataSet();

                ds.Tables.Add(createInventoryFoldersTable());
                invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
                setupFoldersCommands(invFoldersDa, conn);
                CreateDataSetMapping(invFoldersDa, "inventoryfolders");
                m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");

                ds.Tables.Add(createInventoryItemsTable());
                invItemsDa.Fill(ds.Tables["inventoryitems"]);
                setupItemsCommands(invItemsDa, conn);
                CreateDataSetMapping(invItemsDa, "inventoryitems");
                m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");

                ds.AcceptChanges();
            }
        }