C# SqliteDataAdapter.Fill方法代码示例

 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);
            }
        }

        /// <summary>
        /// Зарежда новите новини
        /// </summary>
        public void TmpNewsTransfer(SqliteCommand aCmmndNews, SqliteCommand aCmmndEncl)
        {
            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; }

            Int32 iNewsID = 0;
            // ID на последната новина
            aCmmndNews.CommandText =
                "SELECT ifnull(max(news.news_id),0) AS news_id " +
                " FROM news ";
            using (SqliteDataReader dr = aCmmndNews.ExecuteReader())
            {
                if (dr.HasRows && dr.Read())
                {
                    if (dr["news_id"].ToString().Equals(""))
                    {
                        iNewsID = 1;
                    }
                    else
                    {
                        iNewsID = Convert.ToInt32(dr["news_id"]) + 1;
                    }
                }
                else
                {
                    throw new Exception("Exception: TmpNewsTransfer");
                }
                dr.Close();
            }

            // Новини
            aCmmndNews.CommandText = "INSERT INTO news ( news_id, news_site_id, news_guid, news_title, news_link, news_description, news_body, news_comments, news_pubdate, news_category_id ) "
                                   + " SELECT @news_id "
                                   + "      , tmp_news.news_site_id "
                                   + "      , tmp_news.news_guid "
                                   + "      , tmp_news.news_title "
                                   + "      , tmp_news.news_link "
                                   + "      , tmp_news.news_description "
                                   + "      , tmp_news.news_body "
                                   + "      , tmp_news.news_comments "
                                   + "      , tmp_news.news_pubdate "
                                   + "      , tmp_news.news_category_id "
                                   + " FROM tmp_news "
                                   + " WHERE ( tmp_news.news_id = @tmp_news_id ); ";
            aCmmndNews.Parameters.Clear();
            aCmmndNews.Parameters.Add("@news_id", DbType.Int32);
            aCmmndNews.Parameters.Add("@tmp_news_id", DbType.Int32);

            // Новини - Картинки
            aCmmndEncl.CommandText = "INSERT INTO enclosure ( news_id, encl_link, encl_id ) "
                                   + " SELECT @news_id "
                                   + "      , tmp_enclosure.encl_link "
                                   + "      , tmp_enclosure.encl_id "
                                   + " FROM tmp_enclosure "
                                   + " WHERE ( tmp_enclosure.news_id = @tmp_news_id ); ";
            aCmmndEncl.Parameters.Clear();
            aCmmndEncl.Parameters.Add("@news_id", DbType.Int32);
            aCmmndEncl.Parameters.Add("@tmp_news_id", DbType.Int32);

            foreach (DataRow dr in dtNews.Rows)
            {
                //
                aCmmndNews.Parameters["@news_id"].Value = iNewsID;
                aCmmndNews.Parameters["@tmp_news_id"].Value = dr["news_id"];

                aCmmndNews.ExecuteNonQuery();

                aCmmndEncl.Parameters["@news_id"].Value = iNewsID;
                aCmmndEncl.Parameters["@tmp_news_id"].Value = dr["news_id"];

                aCmmndEncl.ExecuteNonQuery();

                iNewsID++;
            }
        }

        /// <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 " +
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                    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
                    // need to actually find a proper way to handle this.
                    try
                    {
                        primDa.Fill(ds.Tables["prims"]);
                    }
                    catch (Exception e)
                    {
                        m_log.ErrorFormat("[SQLITE REGION DB]: Caught fill error on prims table :{0}", e.Message);
                    }

                    try
                    {
                        shapeDa.Fill(ds.Tables["primshapes"]);
                    }
                    catch (Exception e)
                    {
                        m_log.ErrorFormat("[SQLITE REGION DB]: Caught fill error on primshapes table :{0}", e.Message);
                    }

                    try
                    {
                        itemsDa.Fill(ds.Tables["primitems"]);
                    }
                    catch (Exception e)
                    {
                        m_log.ErrorFormat("[SQLITE REGION DB]: Caught fill error on primitems table :{0}", e.Message);
                    }

                    try
                    {
                        terrainDa.Fill(ds.Tables["terrain"]);
                    }
                    catch (Exception e)
                    {
                        m_log.ErrorFormat("[SQLITE REGION DB]: Caught fill error on terrain table :{0}", e.Message);
                    }

        /// <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();
            }
        }

 /// <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();
         }
     }
 }