C# SQLiteConnection.CreateCommand方法代码示例

 public void TestQuery()
 {
     var builder = new SQLiteConnectionStringBuilder();
       builder.DataSource = "test.db";
       using (DbConnection connection = new SQLiteConnection(builder.ToString()))
       {
     connection.Open();
     using (var cmd1 = connection.CreateCommand())
     {
       cmd1.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='table_test';";
       var reader = cmd1.ExecuteReader();
       if (reader.Read())
       {
     var tableName = reader.GetString(0);
     System.Diagnostics.Trace.WriteLine(String.Format("table name={0}", tableName));
       }
       else
       {
     using (var cmd2 = connection.CreateCommand())
     {
       cmd2.CommandText = @"Create Table 'table_test' (num Integer, str)";
       cmd2.ExecuteNonQuery();
     }
       }
     }
       }
 }

		public SqliteGisModelReader(string fileName)
		{
			mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3");
			mDataConnection.Open();

			SQLiteCommand getModelCommand = mDataConnection.CreateCommand();
			getModelCommand.CommandText = "SELECT CorrectionConstant, CorrectionParameter FROM Model";
			IDataReader reader = getModelCommand.ExecuteReader();
			while (reader.Read())
			{
				mCorrectionConstant = reader.GetInt32(0);
				mCorrectionParameter = reader.GetDouble(1);
			}
			reader.Close();

            List<string> outcomeLabels = new List<string>();

			getModelCommand.CommandText = "SELECT OutcomeLabel FROM Outcome ORDER BY OutcomeID";
			reader = getModelCommand.ExecuteReader();
			while (reader.Read())
			{
				outcomeLabels.Add(reader.GetString(0));
			}
			reader.Close();
			mOutcomeLabels =outcomeLabels.ToArray();

			mGetParameterCommand = mDataConnection.CreateCommand();
			mGetParameterCommand.CommandText = "SELECT PredicateParameter.OutcomeID, PredicateParameter.Parameter FROM PredicateParameter INNER JOIN Predicate ON PredicateParameter.PredicateID = Predicate.PredicateID WHERE Predicate.PredicateLabel = ?";
            mPredicateParameter = new SQLiteParameter();
			mPredicateParameter.DbType = DbType.String;
			mPredicateParameter.Size = 255;
            mGetParameterCommand.Parameters.Add(mPredicateParameter);
		}

		/// <summary>Write data with a given key to cache</summary>
		/// <param name="key">Key of data to be stored</param>
		/// <param name="data">Data to be stored</param>
		public void Store(string key, string data)
		{
			SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
			try
			{
				DateTime now = DateTime.Now; 
				connection.Open();

				SQLiteCommand command = connection.CreateCommand();
				command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?";
				command.Parameters.Add("kw", DbType.String).Value = key;
				command.ExecuteNonQuery();	// out with the old

				command = connection.CreateCommand();
				command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)";
				command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day;
				command.Parameters.Add("kw", DbType.String).Value = key;
				command.Parameters.Add("dt", DbType.String).Value = data;
				command.ExecuteNonQuery();	// in with the new
			}
			finally
			{
				connection.Close();
			}
		}

		/// <summary>
		/// コマンドを実行.
		/// </summary>
		/// <param name="inputCmd">inputed command</param>
		/// <param name="conn">SQLite connection</param>
		public static void executeCommand(String inputCmd, SQLiteConnection conn)
		{
			if (inputCmd.ToLower().StartsWith("select"))
			{
				try
				{
					SQLiteCommand command = conn.CreateCommand();
					command = conn.CreateCommand();
					command.CommandText = inputCmd;
					SQLiteDataReader reader = command.ExecuteReader();
					if (!reader.HasRows)
					{
						Console.WriteLine("no row selected");
					}
					int fc = reader.FieldCount;
					while (reader.Read())
					{
						String[] row = new String[fc];
						for (int i = 0; i < fc; i++)
						{
							row[i] = reader.GetValue(i).ToString();
						}
						Console.WriteLine(String.Join("\t", row));
					}
					Console.WriteLine();
					Console.WriteLine(reader.StepCount + " row(s) selected");
				}
				catch (SQLiteException e)
				{
					Console.WriteLine(e.Message);
				}
			}
		}

        public void Cleaup()
        {
            //drop all the tables from the db
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", DB_FILE_PATH)))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE records";
                    cmd.ExecuteNonQuery();
                }

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE datasets";
                    cmd.ExecuteNonQuery();
                }

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE kvstore";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public GamesRepository()
        {
            bool buildSchema = false;
            if (!File.Exists(DatabaseFile))
            {
                Directory.CreateDirectory(DatabasePath);
                SQLiteConnection.CreateFile(DatabaseFile);
                buildSchema = true;
            }

            DatabaseConnection = new SQLiteConnection(ConString);
            DatabaseConnection.Open();
            using (SQLiteCommand com = DatabaseConnection.CreateCommand())
            {
                com.CommandText =
                    "PRAGMA automatic_index=FALSE; PRAGMA synchronous=OFF; PRAGMA auto_vacuum=INCREMENTAL; PRAGMA foreign_keys=ON; PRAGMA encoding='UTF-8';";
                com.ExecuteScalar();
            }

            if (buildSchema)
            {
                using (SQLiteCommand com = DatabaseConnection.CreateCommand())
                {
                    string md = Resource1.MakeDatabase;
                    com.CommandText = md;
                    com.ExecuteNonQuery();
                }
            }

            using (SQLiteCommand com = DatabaseConnection.CreateCommand())
            {
                UpdateDatabase.Update(DatabaseConnection);
            }
        }

        public MainWindow()
        {
            InitializeComponent();

            if (!File.Exists("pix_database.sqlite"))
            {
                SQLiteConnection.CreateFile("pix_database.sqlite");
                new_db = true;
            }

            db = new SQLiteConnection("Data Source=pix_database.sqlite;Version=3;");
            db.Open();

            SQLiteCommand createTables = db.CreateCommand();

            String bookTable = "CREATE TABLE IF NOT EXISTS Book" +
                "(ISBN TEXT PRIMARY KEY NOT NULL," +
                "Title TEXT NOT NULL," +
                "Published DATETIME NOT NULL," +
                "Author TEXT NOT NULL," +
                "Pages INT NOT NULL," +
                "Publisher TEXT NOT NULL);";

            String courseTable = "CREATE TABLE IF NOT EXISTS CourseBook" +
                "(Course TEXT NOT NULL," +
                "ISBN TEXT NOT NULL," +
                "PRIMARY KEY (Course, ISBN)," +
                "FOREIGN KEY (ISBN) REFERENCES Book(ISBN));";

            createTables.CommandText = bookTable;
            createTables.ExecuteNonQuery();
            createTables.Parameters.Clear();
            createTables.CommandText = courseTable;
            createTables.ExecuteNonQuery();
            createTables.Dispose();

            if (new_db)
            {
                SQLiteCommand insertInitial = db.CreateCommand();

                String book351 = "INSERT INTO Book VALUES ('0-672-33690-1', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');" +
                    "INSERT INTO Book VALUES ('978-0-672-33690-4', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');";

                String course351 = "INSERT INTO CourseBook VALUES ('CSCI351', '978-0-672-33690-4');" +
                    "INSERT INTO CourseBook VALUES ('CSCI351', '0-672-33690-1');";

                insertInitial.CommandText = book351;
                insertInitial.ExecuteNonQuery();
                insertInitial.Parameters.Clear();
                insertInitial.CommandText = course351;
                insertInitial.ExecuteNonQuery();
                insertInitial.Dispose();
            }
        }

        public static void CreateKaraokeDatabase(
            FileInfo theDatabaseFileInfo)
        {
            SQLiteConnection.CreateFile(theDatabaseFileInfo.FullName);

            using (SQLiteConnection theConnection = new SQLiteConnection(
                DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
            {
                try
                {
                    theConnection.Open();

                    using (SQLiteCommand theCommand = theConnection.CreateCommand())
                    {
                        StringBuilder theCommandText = new StringBuilder();

                        theCommandText.Append("CREATE TABLE [Settings] (");
                        theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,");
                        theCommandText.Append("[Key] [text],");
                        theCommandText.Append("[Value] [text])");

                        theCommand.CommandText = theCommandText.ToString();

                        theCommand.ExecuteNonQuery();
                    }

                    using (SQLiteCommand theCommand = theConnection.CreateCommand())
                    {
                        StringBuilder theCommandText = new StringBuilder();

                        theCommandText.Append("CREATE TABLE [Tracks] (");
                        theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,");
                        theCommandText.Append("[Path] [text],");
                        theCommandText.Append("[Details] [text],");
                        theCommandText.Append("[Extension] [text],");
                        theCommandText.Append("[Rating] [integer] DEFAULT 0,");
                        theCommandText.Append("[Checksum] [text],");
                        theCommandText.Append("[ToBeDeleted] [boolean] DEFAULT 0)");

                        theCommand.CommandText = theCommandText.ToString();

                        theCommand.ExecuteNonQuery();
                    }
                }
                finally
                {
                    theConnection.Close();
                }
            }
        }

 internal static void DeleteHopsIngredient(int hopsIngredientId, SQLiteConnection connection)
 {
     using (SQLiteCommand deleteIngredientCommand = connection.CreateCommand())
     {
         deleteIngredientCommand.CommandText = "DELETE FROM HopsIngredients WHERE id = @id";
         deleteIngredientCommand.Parameters.AddWithValue("id", hopsIngredientId);
         deleteIngredientCommand.ExecuteNonQuery();
     }
     using (SQLiteCommand deleteJunctionCommand = connection.CreateCommand())
     {
         deleteJunctionCommand.CommandText = "DELETE FROM HopsInRecipe WHERE hopsIngredient = @id";
         deleteJunctionCommand.Parameters.AddWithValue("id", hopsIngredientId);
         deleteJunctionCommand.ExecuteNonQuery();
     }
 }

        private void loadHeroDiary()
        {
            // Load the latest Hero Diary entries
            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;"))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where [email protected] order by Diary_ID desc limit 1000";
                    cmd.CommandText = commandText;
                    cmd.Parameters.AddWithValue("@HeroName", this.HeroName);

                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da = new SQLiteDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds);

                    BindingSource bindingSource = new BindingSource();
                    bindingSource.DataSource = ds.Tables[0];
                    grdDiary.DataSource = bindingSource;
                    grdDiary.AutoGenerateColumns = true;
                    grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
                    grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                }
            }
        }

        private bool CreateDatabase()
        {
            SQLiteConnection tempConnection = null;
            try
            {
                SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
                tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
                tempConnection.Open();

                SQLiteCommand command = tempConnection.CreateCommand();
                command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
                command.ExecuteNonQuery();

                tempConnection.Close();
            }
            catch(Exception e)
            {
                errors.Add("Tietokannan luominen epäonnistui");
                return false;
            }
            finally
            {
                if(tempConnection != null) tempConnection.Close();
            }

            return true;
        }

        public void create(string file)
        {
            db_file = file;
            conn = new SQLiteConnection("Data Source=" + db_file);
            if (!File.Exists(db_file))
            {
                conn.Open();
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.CommandText = @"CREATE TABLE `erogamescape` (
                                        `id`	INTEGER NOT NULL,
                                        `title`	TEXT,
                                        `saleday`	TEXT,
                                        `brand`	TEXT,
                                        PRIMARY KEY(id));
                                        CREATE TABLE `tableinfo` (
                                        `tablename`	TEXT NOT NULL,
                                        `version`	INTEGER,
                                        PRIMARY KEY(tablename))";
                    command.ExecuteNonQuery();
                    command.CommandText = @"INSERT INTO  tableinfo VALUES('erogamescape',0)";
                    command.ExecuteNonQuery();

                }
                conn.Close();
            }
        }

		public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
		{
			_databaseFullPath = databaseFullPath;
			_destructorType = destructorType;
			_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
			
			if (!File.Exists(_databaseFullPath))
			{
				SQLiteConnection.CreateFile(_databaseFullPath);
				Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
			}
			else
			{
				Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
			}

			using (var connection = new SQLiteConnection(_connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
					Log.Verbose("About to create or check for the Entities table in the EntityStore database.");
					cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
					cmd.CommandType = CommandType.Text;
					cmd.ExecuteNonQuery();
					Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
				}
			}
		}

 /// <summary>
 /// delete specific cookie
 /// </summary>
 /// <param name="id"></param>
 public static void DeleteCookie(object id)
 {
     if (!string.IsNullOrEmpty(GetFFCookiePath()))
     {
         using (var conn = new SQLiteConnection("Data Source=" + GetFFCookiePath()))
         {
             using (SQLiteCommand cmd = conn.CreateCommand())
             {
                 try
                 {
                     cmd.CommandText = "delete from moz_cookies where id=" + id;
                     conn.Open();
                     int res = cmd.ExecuteNonQuery();
                     if (res != 1)
                     { }
                 }
                 finally
                 {
                     cmd.Dispose();
                     conn.Close();
                 }
             }
         }
     }
 }

        public static DataTable SearchMusicDatabase(
            FileInfo theDatabaseFileInfo,
            string theCriteria)
        {
            using (DataSet theDataSet = new DataSet())
            {
                using (SQLiteConnection theConnection = new SQLiteConnection(
                    DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
                {
                    try
                    {
                        theConnection.Open();

                        using (SQLiteCommand theCommand = theConnection.CreateCommand())
                        {
                            StringBuilder theCommandBuilder = new StringBuilder(
                                "SELECT [ID], [Path], [Details], [Extension], 0, " +
                                    "[Path] || '\\' || [Details] || [Extension] AS [FullPath] ");
                            theCommandBuilder.Append("FROM [Tracks] ");
                            theCommandBuilder.Append("WHERE ");

                            bool IsFirstParameter = true;
                            foreach (string thisCriteria in theCriteria.Split(' '))
                            {
                                if (!IsFirstParameter)
                                {
                                    theCommandBuilder.Append(" AND ");
                                }
                                theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)");

                                SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter();
                                theCommand.Parameters.Add(thisCriteriaParameter);
                                theCommand.Parameters.Add(thisCriteriaParameter);

                                thisCriteriaParameter.Value = String.Format(
                                    CultureInfo.CurrentCulture,
                                    "%{0}%",
                                    thisCriteria.ToString());

                                IsFirstParameter = false;
                            }

                            theCommandBuilder.Append(" ORDER BY [Path], [Details]");

                            theCommand.CommandText = theCommandBuilder.ToString();

                            using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand))
                            {
                                theAdapter.Fill(theDataSet);
                            }
                        }
                    }
                    finally
                    {
                        theConnection.Close();
                    }
                }
                return theDataSet.Tables[0];
            }
        }