C# SQLiteCommand.CreateParameter方法代码示例

        /// <summary>
        /// Connects to a SQLite3-Database
        /// </summary>
        /// <param name="path">Path of the Database</param>
        public Database(string path)
        {
            connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + path + ";Version=3;New=True;Compress=True";
            connection.Open();

            // Create required databases
            createTable("pictures", "path VARCHAR(255) PRIMARY KEY NOT NULL, up INT NOT NULL DEFAULT 0, down INT NOT NULL DEFAULT 0");

            // Prepare prepared statements, duh.
            qRandomPicture = new SQLiteCommand(connection);
            qRandomPicture.CommandText = "SELECT path FROM pictures WHERE (up - down) > (SELECT AVG(up-down)-2 FROM pictures) ORDER BY RANDOM() LIMIT 1";

            qInsertPicture = new SQLiteCommand(connection);
            qInsertPicture.CommandText = "INSERT OR IGNORE INTO pictures (path) VALUES (@path)";
            qInsertPath = qInsertPicture.CreateParameter();
            qInsertPath.ParameterName = "@path";
            qInsertPicture.Parameters.Add(qInsertPath);

            qVoteUp = new SQLiteCommand(connection);
            qVoteUp.CommandText = "UPDATE pictures SET up = up + 1 WHERE path = @path";
            qVoteUpPath = qVoteUp.CreateParameter();
            qVoteUpPath.ParameterName = "@path";
            qVoteUp.Parameters.Add(qVoteUpPath);

            qVoteDown = new SQLiteCommand(connection);
            qVoteDown.CommandText = "UPDATE pictures SET down = down + 1 WHERE path = @path";
            qVoteDownPath = qVoteDown.CreateParameter();
            qVoteDownPath.ParameterName = "@path";
            qVoteDown.Parameters.Add(qVoteDownPath);
        }

    public static int Insert(SQLiteConnection connection, String tableName, Dictionary<String, String> data)
    {
        String columns = "";
        String values = "";
        foreach (KeyValuePair<String, String> val in data)
        {
            columns += String.Format(" {0},", val.Key.ToString());
            values += String.Format(" ?,");
        }
        columns = columns.Substring(0, columns.Length - 1);
        values = values.Substring(0, values.Length - 1);
        try
        {
            string sql = String.Format("insert into {0}({1}) values({2});", tableName, columns, values);
            SQLiteCommand dbCommand = new SQLiteCommand(connection);

            foreach (KeyValuePair<String, String> val in data)
            {
                SQLiteParameter para = dbCommand.CreateParameter();
                para.Value = val.Value;
                dbCommand.Parameters.Add(para);
            }

            dbCommand.CommandText = sql;
            int rowsUpdated = dbCommand.ExecuteNonQuery();

            dbCommand.CommandText = "SELECT last_insert_rowid();";
            return int.Parse(dbCommand.ExecuteScalar().ToString()); // returned index
        }
        catch (Exception fail)
        {
            MessageBox.Show(fail.Message);
        }
        return 0;
    }

 internal override bool IsTablePresent(string tableName)
 {
     SQLiteCommand command = new SQLiteCommand((SQLiteConnection)Connection);
     command.CommandText = "SELECT * FROM sqlite_master WHERE type='table' AND [email protected]";
     SQLiteParameter nameParametr = command.CreateParameter();
     nameParametr.ParameterName = "@name";
     nameParametr.Value = tableName;
     command.Parameters.Add("@name", System.Data.DbType.String);
     command.Parameters["@name"].Value = tableName;
     bool result = command.ExecuteScalar() != null;
     command.Dispose();
     return result;
 }

        public void Import()
        {
            //see if we hava a database, if not, create one
              if (!File.Exists(_dataFile))
              {
            _InitDatabase();
              }

              var raw = File.ReadLines(_file).GetEnumerator();
              //prime the data, so the first pull gets a value
              raw.MoveNext();

              var count = 0;
              var data = _GetData(raw);

              using (var conn = new SQLiteConnection(_connString))
              {
            conn.Open();

            do
            {
              using (var cmd = new SQLiteCommand(conn))
              {
            using (var trans = conn.BeginTransaction())
            {
              cmd.CommandText = "insert into domains (name) values(?)";
              var param = cmd.CreateParameter();
              cmd.Parameters.Add(param);

              foreach (var itm in data)
              {
                param.Value = itm;
                cmd.ExecuteNonQuery();
              }

              trans.Commit();
            }
              }

              count += data.Count();
              Console.WriteLine(string.Format("Imported {0:#,#} items...", count));

              data = _GetData(raw);
            } while (data.Count() > 0);

            conn.Close();
              }
        }

 public void InsertMetadata(String configurationFile)
 {
     SQLiteConnection conn = new SQLiteConnection(String.Format("Data source={0}", fileName));
     conn.Open();
     using (conn)
     {
         using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO ___Metadata([Data]) VALUES(@Data)", conn))
         {
             SQLiteParameter p = cmd.CreateParameter();
             p.ParameterName = "@Data";
             p.DbType = System.Data.DbType.Xml;
             p.Value = System.IO.File.ReadAllText(configurationFile);
             cmd.Parameters.Add(p);
             cmd.ExecuteNonQuery();
         }
     }
 }

        /// <summary>
        /// Reads all rows out of the local flat-files for the specified day and table
        /// </summary>
        /// <param name="day">The day to grab rows from.</param>
        /// <param name="tableName">Name of the table to grab data from.</param>
        /// <param name="getDataAfterTime">Determines if data should be pulled from all rows before the timestamp in the day parameter or after</param>
        /// <returns>Returns a DataTable that contains all rows from for a specified day starting at the specified time</returns>
        public static DataTable ReadDay(DateTime day, TableName tableName, bool getDataAfterTime)
        {
            string path = Path.Combine(Directory.GetCurrentDirectory(), string.Format("analysis {0}{1}{2}.db", day.Year, day.Month.ToString().PadLeft(2, '0'), day.Day.ToString().PadLeft(2, '0')));
            if (!File.Exists(path)) {
                return null;
            }
            DataTable dataTable = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path + ";Version=3")) {
                string format = (((day.Hour == 0) && (day.Minute == 0)) && (day.Second == 0)) ? "Select * From {0}" : ("Select * From {0} Where CreateTime " + (getDataAfterTime ? "> " : "< ") + "@cDate"); //day.ToString("G"));
                using (SQLiteCommand command = new SQLiteCommand(string.Format(format, tableName.ToString()), connection)) {
                    var param = command.CreateParameter();
                    param.Value = day.ToString("G");
                    param.ParameterName = "@cDate";
                    command.Parameters.Add(param);

                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command)) {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }

        //Takes SQLCOMMAND as parameter and run the command aginst the Database.
        public static void insetToDatabase( Message mail)
        {
            dbConnection.Open();
             //SQLiteCommand command = new SQLiteCommand(SQLCOMMAND, dbConnection);
             //command.ExecuteNonQuery();
             //dbConnection.Close();

             SQLiteCommand cmd = new SQLiteCommand(dbConnection);
             SQLiteParameter param = cmd.CreateParameter();

             string SQLCOMMAND = @"INSERT INTO MailList (MessageId, Receiver, Sender, Date, Subject, Message) VALUES ('" + mail.Headers.MessageId + "','" + mail.Headers.To + "','" + mail.Headers.From + "','" + mail.Headers.DateSent + "','" + mail.Headers.Subject + "', @param)";

             cmd.CommandText = SQLCOMMAND;
             param.ParameterName = "param";
             MessagePart html = mail.FindFirstHtmlVersion();
             MessagePart plainText = mail.FindFirstPlainTextVersion();
            StringBuilder builder = new StringBuilder();

            if (html != null)
                param.Value = builder.Append(html.GetBodyAsText()).ToString();

            else
                param.Value = plainText.GetBodyAsText();

             cmd.Parameters.Add(param);
             try
             {
                 cmd.ExecuteNonQuery();
             }
             catch (Exception)
             {

             }
                 //always close the connection!!
             finally { dbConnection.Close(); }
        }

        private void WriteBlobRow(UUID uuid, MemoryStream ms)
        {
            const string INSERT_CMD = "INSERT OR REPLACE INTO StateData(item_id, state_data) VALUES(@itemId, @stateBlob)";
            using (SQLiteCommand cmd = new SQLiteCommand(INSERT_CMD, _connection))
            {
                SQLiteParameter itemIdParam = cmd.CreateParameter();
                itemIdParam.ParameterName = "@itemId";
                itemIdParam.Value = uuid.Guid.ToString("N");

                SQLiteParameter stateBlobParam = cmd.CreateParameter();
                stateBlobParam.ParameterName = "@stateBlob";
                stateBlobParam.Value = ms.ToArray();

                cmd.Parameters.Add(itemIdParam);
                cmd.Parameters.Add(stateBlobParam);

                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Only called during startup, loads the state data for the given item id off the disk
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public VM.RuntimeState LoadStateFromDisk(UUID itemId)
        {
            try
            {
                lock (_connection)
                {
                    const string FIND_CMD = "SELECT state_data FROM StateData WHERE item_id = @itemId";
                    using (SQLiteCommand cmd = new SQLiteCommand(FIND_CMD, _connection))
                    {
                        SQLiteParameter itemIdParam = cmd.CreateParameter();
                        itemIdParam.ParameterName = "@itemId";
                        itemIdParam.Value = itemId.Guid.ToString("N");

                        cmd.Parameters.Add(itemIdParam);

                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                //int bufSz = reader.GetBytes(0, 0, null, 0, 0);
                                byte[] buffer = (byte[])reader[0];//reader.GetBytes(

                                using (MemoryStream stateStream = new MemoryStream(buffer))
                                {
                                    SerializedRuntimeState runstate = ProtoBuf.Serializer.Deserialize<SerializedRuntimeState>(stateStream);
                                    if (runstate != null)
                                    {
                                        return runstate.ToRuntimeState();
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                _log.ErrorFormat("[Phlox]: Could not load state for {0} script will be reset. Error was {1}",
                    itemId, e);
            }

            return null;
        }

 /// <summary>
 /// PreparedStatementを設定する
 /// </summary>
 /// <param name="cmd">パラメータを指定するオブジェクト</param>
 /// <param name="param">パラメータ</param>
 private void createParam(SQLiteCommand cmd,Object[] param)
 {
     if (param == null || cmd == null) { return; }
     foreach (Object p in param)
     {
         SQLiteParameter sp = cmd.CreateParameter();
         if (p is String)
         {
             sp.DbType = System.Data.DbType.String;
             sp.Value = (String)p;
         }
         else if (p is Int16)
         {
             sp.DbType = System.Data.DbType.Int16;
             sp.Value = (int)p;
         }
         else if (p is Int32)
         {
             sp.DbType = System.Data.DbType.Int32;
             sp.Value = (int)p;
         }
         cmd.Parameters.Add(sp);
     }
 }

        private void InsertEntryToFAM(Object e)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = conn;

                cmd.CommandText = "INSERT OR IGNORE INTO FAM(@timestamp, @pid, @filehandle, @filename, @user, @action, @accessmask)";

                SQLiteParameter param = cmd.CreateParameter();
                param.ParameterName = "timestamp";
                param.DbType = DbType.DateTime;

                param = cmd.CreateParameter();
                param.ParameterName = "pid";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filehandle";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filename";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "user";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "action";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "accessmask";
                param.DbType = DbType.Int32;

                cmd.ExecuteNonQuery();
            }
        }

        private void CreateTables()
        {
            // Table : Types
            RunCommand("CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, TotalSize INTEGER)");

            cmdInsertType = cxion.PrepareCommand("INSERT INTO Types(Id, Name, MethodTable, Count, TotalSize) VALUES (@Id, @Name, @MethodTable, @Count, @TotalSize)");
            paramId_InsertType = cmdInsertType.CreateParameter("Id");
            paramName_InsertType = cmdInsertType.CreateParameter("Name");
            paramMethodTable_InsertType = cmdInsertType.CreateParameter("MethodTable");
            paramCount_InsertType = cmdInsertType.CreateParameter("Count");
            paramTotalSize_InsertType = cmdInsertType.CreateParameter("TotalSize");

            // Table : Instances
            RunCommand("CREATE TABLE Instances (TypeId INTEGER, Address INTEGER)");

            cmdInsertInstance = cxion.PrepareCommand("INSERT INTO Instances(TypeId, Address) VALUES (@TypeId, @Address)");
            paramTypeId_InsertInstance = cmdInsertInstance.CreateParameter("TypeId");
            paramAddress_InsertInstance = cmdInsertInstance.CreateParameter("Address");

            // Table: References
            RunCommand("CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress INTEGER)");
            cmdInsertReference = cxion.PrepareCommand("INSERT INTO InstanceReferences(InstanceAddress, RefByAddress ) VALUES (@InstanceAddress, @RefByAddress)");
            paramInstanceAddress_InsertReference = cmdInsertReference.CreateParameter("InstanceAddress");
            paramRefByAddress_InsertReference = cmdInsertReference.CreateParameter("RefByAddress");
    }

        private static void PrepareStatement(ref SQLiteCommand command, params object[] args)
        {
            foreach (var arg in args)
            {
                SQLiteParameter Field = command.CreateParameter();

                command.Parameters.Add(Field);

                Field.Value = arg;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void TrimOpenAccessTimer_Elapsed(object sender, ElapsedEventArgs e)
        {
            CLogger.WriteLog(ELogLevel.INFO, "In Timer fired");

            Win32ProcessInfo procInfo;
            try
            {
                Process[] explorerProcList = Process.GetProcessesByName("explorer.exe");
                Process explorerInfo;
                DateTime timestamp = DateTime.Now;
                timestamp = timestamp.AddMinutes(-TRIM_OPEN_ACCESS_INTERVAL);

                for (int i = 0; i < explorerProcList.Length; i++)
                {
                    explorerInfo = explorerProcList[i];
                    procInfo = new Win32ProcessInfo();
                    procInfo.processID = explorerInfo.Id;
                    procInfo.timeStamp = timestamp;
                    lock(killedProcessList)
                    {
                        killedProcessList.Add(procInfo);
                    }
                }
            }
            catch (Exception exc)
            {
                CLogger.WriteLog(ELogLevel.INFO, "Exception occurred while opening explorer.exe but its ok to proceed." +
                                                    " System error: " +exc.Message);
            }

            /* Get the list of processes killed from table
             * Delete the entries from OPENACCESS with those pids which have entries older by
             * TRIM_OPEN_ACCESS_INTERVAL minutes */

            // Make a copy of the killedProcessList
            List<Win32ProcessInfo> currentList;
            lock (killedProcessList)
            {
                currentList = new List<Win32ProcessInfo>(killedProcessList);
                killedProcessList.Clear();
            }

            try
            {
                for (int i = currentList.Count; i > 0; i--)
                {
                    procInfo = currentList[i-1];

                    DateTime timeStamp = DateTime.Now;
                    timeStamp = timeStamp.AddMinutes(-TRIM_OPEN_ACCESS_INTERVAL);

                    if (timeStamp >= procInfo.timeStamp)
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = "DELETE FROM OPENACCESS WHERE pid =" + procInfo.processID +
                                            " AND timestamp <= @TS";

                            SQLiteParameter param = cmd.CreateParameter();
                            param.ParameterName = "@TS";
                            param.DbType = DbType.DateTime;
                            param.Value = procInfo.timeStamp;
                            cmd.Parameters.Add(param);

                            int rowsAffected = cmd.ExecuteNonQuery();
                            CLogger.WriteLog(ELogLevel.DEBUG, "Removed pid: " + procInfo.processID);
                            currentList.RemoveAt(i-1);
                        }
                    }
                }

                /* Add the remaining elements in the current list to the killedProcessList
                 * which might have changed when were deleting the records
                 *
                 */
                lock (killedProcessList)
                {
                    killedProcessList.AddRange(currentList);
                }
            }
            catch (Exception exc)
            {
                CLogger.WriteLog(ELogLevel.ERROR, exc.Message + "\n" + exc.StackTrace);
            }
        }

        private SQLiteParameter AddFilterParameter(SQLiteCommand command, string paramName, DbType paramType, object paramValue)
        {
            try
            {
                SQLiteParameter param = command.CreateParameter();
                param.ParameterName = paramName;

                if (paramType == DbType.String)
                    param.Value = string.Format("%{0}%", paramValue);
                else
                    param.Value = string.Format("{0}", paramValue);

                command.Parameters.Add(param);
                return param;
            }
            catch (Exception ex)
            {
                log.Fatal("Error fatal al agregar parametro de filtro.", ex);
                throw;
            }
        }