C# Sqlite.SqliteCommand类代码示例

        public static void Init(string databaseFullPath)
        {
            try
            {
                if (!File.Exists(databaseFullPath))
                {
                    throw new Exception("Could not find the cards database.");
                }

                _cards = new Dictionary<int, NamedCard>();

                using (SqliteConnection connection = new SqliteConnection("Data Source=" + databaseFullPath))
                {
                    connection.Open();

                    using (IDbCommand command = new SqliteCommand(
                        "SELECT datas.id, ot, alias, setcode, type, level, race, attribute, atk, def, texts.name, texts.desc"
                        + " FROM datas INNER JOIN texts ON datas.id = texts.id",
                        connection))
                    {
                        using (IDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                LoadCard(reader);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex);
            }
        }

		public static int DoNonQuery (SqliteConnection connection, string command_text, string[] param_names, object[] param_args)
		{
			int ret = 0;

			using (SqliteCommand command = new SqliteCommand ()) {
				command.Connection = connection;
				command.CommandText = command_text;

				if (param_names != null) {
					if (param_args == null || param_names.Length != param_args.Length)
						throw new ArgumentException ("param_names, param_args", "param_names and param_args should have same number of items");
					for (int i = 0; i < param_names.Length; ++i)
						command.Parameters.AddWithValue (param_names [i], param_args [i]);
				}

				while (true) {
					try {
						ret = command.ExecuteNonQuery ();
						break;
					} catch (SqliteException e) {
						if (e.ErrorCode == SQLiteErrorCode.Busy) {
							Thread.Sleep (50);
						} else {
							throw;
						}
					} catch (Exception e) {
						Log.Error (e, "SQL that caused the exception: {0}", command_text);
						throw;
					}
				}
			}

			return ret;
		}

        public static void Init()
        {
            try
            {
                _cards = new Dictionary<int, CardData>();

                string currentPath = Assembly.GetExecutingAssembly().Location;
                currentPath = Path.GetDirectoryName(currentPath) ?? "";
                string absolutePath = Path.Combine(currentPath, "cards.cdb");

                if (!File.Exists(absolutePath))
                {
                    throw new Exception("Could not find the cards database.");
                }

                using (SqliteConnection connection = new SqliteConnection("Data Source=" + absolutePath))
                {
                    connection.Open();

                    const string select =
                        "SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " +
                        "FROM datas INNER JOIN texts ON datas.id = texts.id";

                    using (SqliteCommand command = new SqliteCommand(select, connection))
                    using (SqliteDataReader reader = command.ExecuteReader())
                        InitCards(reader);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex);
            }
        }

        /// <summary>
        /// Returns all categories.
        /// </summary>
        public IEnumerable<Category> GetAllCategories()
        {
            var categories = new List<Category>();

            using (var connection = new SqliteConnection("Data Source=" + dbPath))
            using (var query = new SqliteCommand("SELECT * FROM Categories", connection))
            {
                connection.Open();

                var reader = query.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    var category = new Category();
                    category.Id = int.Parse(reader["id"].ToString());
                    category.Name = reader ["name"].ToString();

                    categories.Add(category);
                }

                reader.Close();
            }

            return categories;
        }

 public void NonQuery( string cmdstr )
 {
     using (var cmd = new SqliteCommand( con )) {
         cmd.CommandText = cmdstr;
         cmd.ExecuteNonQuery();
     }
 }

		public void DateTimeConvert ()
		{
			var dateTime = new DateTime (2016, 9, 15, 12, 1, 53);
			var guid = Guid.NewGuid ();

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlCreate = "CREATE TABLE TestTable (ID uniqueidentifier PRIMARY KEY, Modified datetime)";
				using (var cmd = new SqliteCommand (sqlCreate, connection)) {
					cmd.ExecuteNonQuery ();
				}

				var sqlInsert = "INSERT INTO TestTable (ID, Modified) VALUES (@id, @mod)";
				using (var cmd = new SqliteCommand (sqlInsert, connection)) {
					cmd.Parameters.Add (new SqliteParameter ("@id", guid));
					cmd.Parameters.Add (new SqliteParameter ("@mod", dateTime));
					cmd.ExecuteNonQuery ();
				}
			}

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlSelect = "SELECT * from TestTable";
				using (var cmd = new SqliteCommand (sqlSelect, connection))
				using (var reader = cmd.ExecuteReader ()) {
					while (reader.Read ()) {
						Assert.AreEqual (guid, reader.GetGuid (0), "#1");
						Assert.AreEqual (dateTime, reader.GetDateTime (1), "#2");
					}
				}
			}
		}

        public static List<string[]> ExecuteStringCommand(SqliteCommand command, int columncount)
        {
            try
            {
            var values = new List<string[]>();
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                var row = new List<string>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row.Add(reader[i].ToString());
                }
                values.Add(row.ToArray());
            }
            reader.Close();
            return values;

            }
            catch (Exception ex)
            {
            MessageBox.Show(ex.Message);
            return new List<string[]>();
            }
        }

    public void UpdateOrders()
    {
        MainClass.StatusMessage("Получаем таблицу заказов...");

        string sql = "SELECT orders.id, orders.customer, orders.contract, orders.address, orders.phone1, orders.phone2, orders.arrval, orders.deadline_s, orders.deadline_e FROM orders ";
        SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection) QSMain.ConnectionDB);

        using(SqliteDataReader rdr = cmd.ExecuteReader())
        {
            OrdersListStore.Clear();
            while (rdr.Read())
            {
                OrdersListStore.AppendValues(rdr.GetInt32(rdr.GetOrdinal("id")),
                    rdr["customer"].ToString(),
                    rdr["contract"].ToString(),
                    rdr["phone1"].ToString() + rdr["phone2"].ToString(),
                    rdr["address"].ToString(),
                    String.Format("{0:d}", rdr["arrval"]),
                    DateWorks.GetDateRangeText(DBWorks.GetDateTime(rdr, "deadline_s", new DateTime()), DBWorks.GetDateTime(rdr, "deadline_e", new DateTime()))
                );
            }

        }
        MainClass.StatusMessage("Ok");
    }

        /// <summary>
        /// Returns all subscribed feeds.
        /// </summary>
        public IEnumerable<Feed> GetAllFeeds()
        {
            var feeds = new List<Feed>();

            using (var connection = new SqliteConnection("Data Source=" + dbPath))
            using (var query = new SqliteCommand("SELECT * FROM Feeds", connection))
            {
                connection.Open();

                var reader = query.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    var feed = new Feed();
                    feed.Id = int.Parse(reader["id"].ToString());
                    feed.Name = reader ["name"].ToString();
                    feed.Url = reader ["url"].ToString();
                    feed.LastUpdated = DateTime.Parse (reader ["LastUpdated"].ToString ());
                    feed.CategoryId = int.Parse(reader["categoryId"].ToString());

                    feeds.Add(feed);
                }

                reader.Close();
            }

            return feeds;
        }

        public void Fill(int id)
        {
            ItemId = id;
            NewItem = false;

            MainClass.StatusMessage(String.Format ("Запрос выставки №{0}...", id));
            string sql = "SELECT exhibition.* FROM exhibition WHERE exhibition.id = @id";
            try
            {
                SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection) QSMain.ConnectionDB);

                cmd.Parameters.AddWithValue("@id", id);

                using(SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    rdr.Read();

                    labelID.Text = rdr["id"].ToString();
                    entryName.Text = rdr["name"].ToString();
                    entryPhone1.Text = DBWorks.GetString(rdr, "phone", "");
                    entryPhone2.Text = DBWorks.GetString(rdr, "phone2", "");
                    textAddress.Buffer.Text = DBWorks.GetString(rdr, "address", "");
                }

                MainClass.StatusMessage("Ok");
                this.Title = entryName.Text;
            }
            catch (Exception ex)
            {
                QSMain.ErrorMessageWithLog(this, "Ошибка получения информации о номенклатуре!", logger, ex);
            }
            TestCanSave();
        }

 public IList<SyncItem> GetAll()
 {
     List<SyncItem> returnData = new List<SyncItem>();
     using (var conn = _provider.GetConnection())
     {
         conn.Open();
         using (SqliteCommand command =
             new SqliteCommand(
                 string.Format(
                     @"SELECT Id, FileKey, CloudAssetId,
         CloudLastUpdatedDate, CloudCreatedDate, IsDeleted,
         Size FROM {0}", SyncItem.TableName), conn))
         {
             var reader = command.ExecuteReader();
             while (reader.Read())
             {
                 SyncItem item = new SyncItem();
                 item.Id = reader.GetInt32(0);
                 item.FileKey = reader.GetString(1);
                 item.CloudAssetId = reader.GetString(2);
                 item.CloudLastUpdated = new DateTime(reader.GetInt64(3));
                 item.CloudCreated = new DateTime(reader.GetInt64(4));
                 item.IsDeleted = reader.GetBoolean(5);
                 returnData.Add(item);
             }
         }
         conn.Close();
     }
     return returnData;
 }

        public GUIhosts(GUImain gui, SqliteConnection connection, SqliteCommand command)
        {
            InitializeComponent();
            InitializeList();

            this.gui = gui;
            _connection = connection;
            _command = command;

            using (_command = new SqliteCommand("SELECT host, port, password FROM hosts ORDER BY id DESC", _connection))
            {

                using (SqliteDataReader reader = _command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        String host = gui.GetSafeString(reader, 0);
                        String port = gui.GetSafeString(reader, 1);
                        String password = gui.GetSafeString(reader, 2);

                        String[] items = { host, port, password };
                        ListViewItem item = new ListViewItem(items);
                        list.Items.Add(item);
                    }
                }
            }
        }

    void addRow(string sName, string sNote)
    {
        SqliteCommand cmd=new SqliteCommand(con);
        cmd.CommandText=
            string.Format("Insert into Cars (id,name,note) VALUES(NULL,'{0}','{1}');",
                          sName,sNote);
        da.InsertCommand=cmd;
        da.InsertCommand.ExecuteNonQuery();

        DataTable dt = ds.Tables[0];
        DataRow dr = dt.NewRow();
        dr["name"]=sName;
        dr["note"]=sNote;
        dr["id"]=getNewID();
        dt.Rows.Add(dr);

        System.Console.WriteLine(da.InsertCommand.CommandText);

        dt.AcceptChanges();
        ds.AcceptChanges();

        da.Update(dt);

        doRefresh();
    }

		public void Create()
		{
			try {
				if(File.Exists(dbPath)) {
					cnn.Dispose();
					// We want to start with a fresh db for each full run
					// The database is created on the first open()
					File.Delete(dbPath);
				}
			}
			catch(Exception e) {
				throw e;
			}

			try {
				using (var createCommand = new SqliteCommand ("CREATE TABLE Company (RecordId int, Name text);", cnn))
				using (var insertCommand = new SqliteCommand ("INSERT INTO Company VALUES (1, 'Test CO')", cnn)) {
					cnn.Open();
					createCommand.ExecuteNonQuery();
					insertCommand.ExecuteNonQuery();
				}
			}
			catch(Exception e) {
				Console.WriteLine (e);
				throw new AssertionException ("Create table failed", e);
			}
			finally {
				cnn.Close();  
			}
		}

		private void MakeSureTableExists()
		{
			using (SqliteCommand sqc = new SqliteCommand(connection)) {
				sqc.CommandText = "CREATE TABLE IF NOT EXISTS `Files` (`id` INTEGER NOT NULL PRIMARY KEY, `path` TEXT NOT NULL, `changedate` INTEGER NOT NULL, `size` INTEGER NOT NULL, `hash` TEXT NOT NULL)";
				sqc.ExecuteNonQuery();
			}
		}