C# System.Data.IDbConnection.CreateCommand方法代码示例

        public bool TestConnection(string query)
        {
            try
            {
                IConn = this.GetConnection();

                IConn.Open();

                bool result = false;

                System.Data.IDbCommand cmd = IConn.CreateCommand();

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = query;

                System.Data.IDataReader _reader = cmd.ExecuteReader();

                result = (_reader.FieldCount > 0);

                _reader.Close();
                IConn.Close();

                return result;

            }
            catch (Exception ex) {
                Logger.Error(ex.Message);
            }
            return false;
        }

            public FileSets(LocalListDatabase owner, DateTime time, long[] versions)
            {
                m_connection = owner.m_connection;
                m_filesets = owner.FilesetTimes.ToArray();
                m_tablename = "Filesets-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
				var tmp = owner.GetFilelistWhereClause(time, versions, m_filesets);
				string query = tmp.Item1;
				var args = tmp.Item2;
                
                using(var cmd = m_connection.CreateCommand())
                    cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS SELECT DISTINCT ""ID"" AS ""FilesetID"", ""Timestamp"" AS ""Timestamp"" FROM ""Fileset"" " + query, m_tablename), args);
            }

 public StorageHelper(System.Data.IDbConnection con)
 {
     m_connection = con;
     m_previousTable = "Previous-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
     m_currentTable = "Current-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
     
     m_transaction = m_connection.BeginTransaction();
     
     using(var cmd = m_connection.CreateCommand())
     {
         cmd.Transaction = m_transaction;
         
         cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL, ""FileHash"" TEXT NULL, ""MetaHash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Type"" INTEGER NOT NULL) ", m_previousTable));
         cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL, ""FileHash"" TEXT NULL, ""MetaHash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Type"" INTEGER NOT NULL) ", m_currentTable));
     }
     
     m_insertPreviousElementCommand = m_connection.CreateCommand();
     m_insertPreviousElementCommand.Transaction = m_transaction;
     m_insertPreviousElementCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""FileHash"", ""MetaHash"", ""Size"", ""Type"") VALUES (?,?,?,?,?)", m_previousTable);
     m_insertPreviousElementCommand.AddParameters(5);
     
     m_insertCurrentElementCommand = m_connection.CreateCommand();
     m_insertCurrentElementCommand.Transaction = m_transaction;
     m_insertCurrentElementCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""FileHash"", ""MetaHash"", ""Size"", ""Type"") VALUES (?,?,?,?,?)", m_currentTable);
     m_insertCurrentElementCommand.AddParameters(5);
 }

            public MissingBlockList(string volumename, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
            {
                m_connection = connection;
                m_transaction = new TemporaryTransactionWrapper(m_connection, transaction);
                m_volumename = volumename;
                var tablename = "MissingBlocks-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
                using(var cmd = m_connection.CreateCommand())
                {
                    cmd.Transaction = m_transaction.Parent;
                    cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Restored"" INTEGER NOT NULL) ", tablename));
                    m_tablename = tablename;

                    var blockCount = cmd.ExecuteNonQuery(string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"", ""Restored"") SELECT DISTINCT ""Block"".""Hash"", ""Block"".""Size"", 0 AS ""Restored"" FROM ""Block"",""Remotevolume"" WHERE ""Block"".""VolumeID"" = ""Remotevolume"".""ID"" AND ""Remotevolume"".""Name"" = ? ", m_tablename), volumename);
                    if (blockCount == 0)
                        throw new Exception(string.Format("Unexpected empty block volume: {0}", volumename));

                    cmd.ExecuteNonQuery(string.Format(@"CREATE UNIQUE INDEX ""{0}-Ix"" ON ""{0}"" (""Hash"", ""Size"", ""Restored"")", tablename));
                }

                m_insertCommand = m_connection.CreateCommand();
                m_insertCommand.Transaction = m_transaction.Parent;
                m_insertCommand.CommandText = string.Format(@"UPDATE ""{0}"" SET ""Restored"" = ? WHERE ""Hash"" = ? AND ""Size"" = ? AND ""Restored"" = ? ", tablename);
                m_insertCommand.AddParameters(4);
            }

            public FilesAndMetadata(System.Data.IDbConnection connection, string filetablename, string blocktablename, long blocksize, BlockVolumeReader curvolume)
            {
                m_filetablename = filetablename;
                m_blocktablename = blocktablename;
                m_blocksize = blocksize;
                m_connection = connection;

                using (var c = m_connection.CreateCommand())
                {
                    m_tmptable = "VolumeFiles-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
                    c.CommandText = string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ( ""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL )", m_tmptable);
                    c.ExecuteNonQuery();


                    c.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Hash"", ""Size"") VALUES (?,?)", m_tmptable);
                    c.AddParameters(2);
                    foreach (var s in curvolume.Blocks)
                    {
                        c.SetParameterValue(0, s.Key);
                        c.SetParameterValue(1, s.Value);
                        c.ExecuteNonQuery();
                    }

                    // The index _HashSizeIndex is not needed anymore. Index on "Blocks-..." is used on Join in GetMissingBlocks

                }
            }

              public Basiclist(System.Data.IDbConnection connection, string volumename)
              {
                m_connection = connection;
                m_volumename = volumename;
                m_transaction = m_connection.BeginTransaction();
                var tablename = TABLE_PREFIX + "-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());

                using(var cmd = m_connection.CreateCommand())
                {
                    cmd.Transaction = m_transaction;
                    cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" {1}", tablename, TABLEFORMAT));
                    m_tablename = tablename;
                }
                
                m_insertCommand = m_connection.CreateCommand();
                m_insertCommand.Transaction = m_transaction;
                m_insertCommand.CommandText = string.Format(@"INSERT INTO ""{0}"" {1}", m_tablename, INSERTCOMMAND);
                m_insertCommand.AddParameters(INSERTARGUMENTS);
            }