C# SQLite.SQLiteCommand类代码示例

        public async Task Load(SQLiteConnection connection)
        {
            using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection))
            {
                var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    PlayableBase track;
                    using (var xmlReader = reader.GetTextReader(8))
                        track = (PlayableBase) _serializer.Deserialize(xmlReader);

                    track.Title = reader.GetString(0);
                    track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)];

                    var albumGuid = reader.ReadGuid(2);
                    if (albumGuid != Guid.Empty)
                        track.Album = _albumsProvider.Collection[albumGuid];
                    track.Guid = reader.ReadGuid(3);
                    track.LastTimePlayed = reader.GetDateTime(4);
                    track.MusicBrainzId = reader.GetValue(5)?.ToString();
                    track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6));

                    var coverId = reader.ReadGuid(7);
                    if (coverId != Guid.Empty)
                        track.Cover = _imageProvider.Collection[coverId];

                    Collection.Add(track.Guid, track);
                    Tracks.Add(track);
                }
            }

            _connection = connection;
        }

        /// <summary>
        ///   Removes the old entries (entries that have been in the database more than the specified time) from the database.
        /// </summary>
        /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
        public static void CleanNewsOlderThan(int olderThan)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    DateTime olderDate = DateTime.Now;
                    TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
                    olderDate = olderDate.Subtract(daySpan);

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "DELETE " +
                                                                        "FROM NEWS_STORAGE " +
                                                                        "WHERE NEWSITEM_AQUISITION_DATE <= ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, olderDate);
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        public async Task<List<InventurItem>> GetDataAsync()
        {
            //using (var db = new InventurContext())
            //{
            //    return await (from i in db.InventurItems
            //                  where i.Exported == false
            //                  orderby i.ChangedAt descending
            //                  select i).ToListAsync();
            //}

            using (var command = new SQLiteCommand($"select * from {TABNAME} where Exported=0 orderby ChangedAt DESC", _dbTool.ConnectDb()))
            {
                using (var reader = await command.ExecuteReaderAsync())
                {
                    var items = new List<InventurItem>();
                    if (await reader.ReadAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var i = new InventurItem { ID = Convert.ToInt32(reader["ID"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), ChangedAt = Convert.ToDateTime(reader["ChangedAt"]), EANCode = reader["EANCode"].ToString(), Amount = Convert.ToInt32(reader["Amount"]), Exported = Convert.ToBoolean(reader["Exported"]) };
                            items.Add(i);
                        }
                    }
                    return items;
                }
            }
        }

        //public static int Insert(string dbFile, string sql,)
        public static ObservableCollection<Jewelry> GetAll()
        {
            ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();

            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
            //using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
            {
                conn.Open();

                string sql = string.Format("select * from detail");

                SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                using (SQLiteDataReader dr1 = cmd.ExecuteReader())
                {
                    while (dr1.Read())
                    {
                        var d = dr1;
                        var dd = dr1.GetValue(0);
                        var data = dr1.GetValue(1);
                        var insert = dr1.GetValue(2);
                        var update = dr1.GetValue(3);
                    }
                }

                conn.Close();
            }

            ob.Add(new Jewelry());

            return ob;
        }

        public static void crear()
        {
            SQLiteConnection.CreateFile("db/interna.wpdb");
            ObjConnection.Open();

            string sql1 = "create table cliente " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "nombre VARCHAR(20)," +
                         "direccion VARCHAR(20)," +
                         "telefono VARCHAR(20)" +
                         ")";
            SQLiteCommand command1 = new SQLiteCommand(sql1, ObjConnection);
            command1.ExecuteNonQuery();

            string sql = "create table equipo " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "marca VARCHAR(20)," +
                         "modelo VARCHAR(20)," +
                         "color VARCHAR(20)," +
                         "foto VARCHAR(20)," +
                         "fechaIng VARCHAR(20)," +
                         "fechaEnt VARCHAR(20)," +
                         "pagado INTEGER DEFAULT 0," +
                         "arreglado INTEGER DEFAULT 0," +
                         "entregado INTEGER DEFAULT 0," +
                         "precio FLOAT," +
                         "obser VARCHAR(40),"+
                         "id_cliente INTEGER," +
                         "FOREIGN KEY(id_cliente) REFERENCES cliente(id)" +
                         ")";
            SQLiteCommand command = new SQLiteCommand(sql, ObjConnection);
            command.ExecuteNonQuery();

            ObjConnection.Close();
        }

        public List<AttachmentDetail> getWitAttachments()
        {
            List<AttachmentDetail> attachments;
            try
            {
                sql_con = new SQLiteConnection(Common.localDatabasePath, true);
                sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);

                sql_con.Open();
                SQLiteDataReader reader = sql_cmd.ExecuteReader();

                attachments = new List<AttachmentDetail>();
                while (reader.Read())
                {
                    AttachmentDetail attachment = new AttachmentDetail();
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
                    attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
                    attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
                    attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
                   
                    attachments.Add(attachment);
                }

            }
            catch (SQLiteException e) { throw e; }
            finally { sql_con.Close(); }

            return attachments;

        }

        public bool insertRecord(string sql)
        {
            connectToDb("Invoice.db");

            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            try
            {
                command.ExecuteNonQuery();
            }
            catch(SQLiteException e )
            {
                if (e.ErrorCode == 1)
                {
                    MessageBox.Show("Unable to find database. Program will exit.");
                    Environment.Exit(0);
                }
                else
                {
                    MessageBox.Show("Value already in database"); //exception 19 if more need to be added
                }

                return false;
            }
            dbConnection.Close();
            return true;
        }

        /// <summary>
        /// Constructor creates a database if it doesn't exist and creates the database's tables
        /// </summary>
        public DataBaseRepository()
        {
            // "using " keywords ensures the object is properly disposed.
            using (var conn = new SQLiteConnection(Connectionstring))
            {
                try
                {
                    if (!File.Exists(Startup.dbSource))
                    {
                        SQLiteConnection.CreateFile(Startup.dbSource);
                    }

                    conn.Open();
                    //Create a Table
                    string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
                    SQLiteCommand command = new SQLiteCommand(query, conn);
                    command.ExecuteNonQuery();
                }
                //TODO: Handle try catch better cath a more specific error type. 
                catch (SQLiteException ex )
                {
                    Console.WriteLine(ex.ToString());
                }
                conn.Close();
            }
        }

        public string AddComment(NewsFeedModel comment)
        {
            string status = "";
            string query;
            DataTable dt1 = new DataTable();
            SQLiteCommand cmd;

            try
            {
                dbConn.Open();

                query = "insert into news_feed values (" + comment.UserId + ", '" + comment.FirstName + "', '" + comment.LastName + "', '" + comment.Comment + "', "+ comment.Month +
                        ", " + comment.Day + ", " + comment.Year + ", " + comment.Hour + ", " + comment.Minute + ")";

                cmd = new SQLiteCommand(query, dbConn);
                cmd.ExecuteNonQuery();

                status = "Comment Added";

                dbConn.Close();
            }
            catch (SQLiteException ex)
            {
                Console.Write(ex.ToString());
                status = ex.ToString();
                dbConn.Close();
            }

            return status;
        }

        public ResultItem GetResultItem(int riderNo)
        {
            SQLiteCommand query = new SQLiteCommand();
            query.CommandText = "SELECT rider_no, rider_first, rider_last, rider_dob, phone, email, member FROM [" + Year +
                "_rider] WHERE rider_no = @noparam;";
            query.CommandType = System.Data.CommandType.Text;
            query.Parameters.Add(new SQLiteParameter("@noparam", riderNo));
            query.Connection = ClubConn;
            SQLiteDataReader reader = DoTheReader(query);
            ResultItem item = new ResultItem();

            while (reader.Read())
            {
                item.BackNo = reader.GetInt32(0);
                item.RiderNo = reader.GetInt32(1);
                item.Rider = reader.GetString(2) + " " + reader.GetString(3);
                item.HorseNo = reader.GetInt32(4);
                item.Horse = reader.GetString(5);
                item.ShowNo = reader.GetInt32(6);
                item.ShowDate = reader.GetString(7);
                item.ClassNo = reader.GetInt32(8);
                item.ClassName = reader.GetString(9);
                item.Place = reader.GetInt32(10);
                item.Time = reader.GetDecimal(11);
                item.Points = reader.GetInt32(12);
                item.PayIn = reader.GetDecimal(13);
                item.PayOut = reader.GetDecimal(14);
            }
            reader.Close();
            ClubConn.Close();
            return item;
        }

        public static void ChapterFinished(string mangaTitle)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "UPDATE READING_LIST " +
                                                                        "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
                                                                        "WHERE MANGA_ID = ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
                    sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection)
        {
            DataSet dataSet = new DataSet();
            dataSet.Reset();

            SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection);
            try
            {
                Connection.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
                dataAdapter.Fill(dataSet);
            }
            catch (SQLiteException ex)
            {
                Log.Write(ex);
                //Debug.WriteLine(ex.Message);
                throw; // пересылаем исключение на более высокий уровень
            }
            finally
            {
                Connection.Dispose();
            }

            return dataSet;
        }

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            try

            {

                SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            } catch {

            // Catching exceptions is for communists

            }

            return dt;
        }

 public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
 {
     using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
     {
         using (SQLiteCommand cmd = new SQLiteCommand())
         {
             cmd.Connection = conn;
             conn.Open();
             SQLiteHelper sh = new SQLiteHelper(cmd);
             int countCurrent = 0;
             string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
             try
             {
                 Int32.Parse((string)value);
                 countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
                 conn.Close();
                 if (countCurrent > 0)
                 {
                     return "更新";
                 }
                 else
                 {
                     return "保存";
                 }
             }
             catch (Exception)
             {
                 return "保存";
             }
         }
     }
 }

 private DoorInfo Retrieve(string DoorID)
 {
     var ret = new DoorInfo();
     var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
     var conn = new SQLiteConnection(cs);
     conn.Open();
     var cmd = new SQLiteCommand(conn);
     cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
     cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
     SQLiteDataReader res = null;
     try
     {
         res = cmd.ExecuteReader();
         if (res.HasRows && res.Read())
         {
             ret.DoorID = DoorID;
             ret.Location = res.GetString(1);
             ret.Description = res.GetString(2);
             ret.EventID = res.GetInt64(3);
         }
         return ret;
     }
     catch(Exception ex)
     {
         throw;
     }
     finally
     {
         if (null != res && !res.IsClosed)
             res.Close();
     }
 }