C# ITransaction.CreateCommand方法代码示例

    // User profiles

    public static IDbCommand SelectUserProfilesCommand(ITransaction transaction, Guid? profileId, string name,
        out int profileIdIndex, out int nameIndex)
    {
      ISQLDatabase database = transaction.Database;
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "SELECT PROFILE_ID, NAME FROM USER_PROFILES";

      IList<string> filters = new List<string>(2);
      if (profileId.HasValue)
        filters.Add("[email protected]_ID");
      if (!string.IsNullOrEmpty(name))
        filters.Add("[email protected]");

      if (filters.Count > 0)
        result.CommandText += " WHERE " + StringUtils.Join(" AND ", filters);

      if (profileId.HasValue)
        database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
      if (!string.IsNullOrEmpty(name))
        database.AddParameter(result, "NAME", name, typeof(string));

      profileIdIndex = 0;
      nameIndex = 1;
      return result;
    }

    public static IDbCommand SelectAllSubSchemaNames(ITransaction transaction, out int nameIndex)
    {
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "SELECT SUBSCHEMA_NAME FROM MEDIAPORTAL_BASIS";

      nameIndex = 0;
      return result;
    }

 public static IDbCommand DeleteAttachedClientCommand(ITransaction transaction, string systemId)
 {
     var result = transaction.CreateCommand();
     result.CommandText = "DELETE FROM ATTACHED_CLIENTS WHERE SYSTEM_ID = @SYSTEM_ID";
     ISQLDatabase database = transaction.Database;
     database.AddParameter(result, "SYSTEM_ID", systemId, typeof(string));
     return result;
 }

    public static IDbCommand SelectAllMediaItemAspectMetadataCreationDatesCommand(ITransaction transaction,
        out int aspectIdIndex, out int creationDateIndex)
    {
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "SELECT MIAM_ID, CREATION_DATE FROM MIA_TYPES";

      aspectIdIndex = 0;
      creationDateIndex = 1;
      return result;
    }

    public static IDbCommand SelectAllMediaItemAspectMetadataCommand(ITransaction transaction,
        out int aspectIdIndex, out int serializationIndex)
    {
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "SELECT MIAM_ID, MIAM_SERIALIZATION FROM MIA_TYPES";

      aspectIdIndex = 0;
      serializationIndex = 1;
      return result;
    }

 public static IDbCommand SelectVersionBySubschemaCommand(ITransaction transaction, string subSchemaName,
   out int versionMajorParameterIndex, out int versionMinorParameterIndex)
 {
   IDbCommand result = transaction.CreateCommand();
   result.CommandText = "SELECT VERSION_MAJOR, VERSION_MINOR FROM MEDIAPORTAL_BASIS WHERE SUBSCHEMA_NAME = @SUBSCHEMA_NAME";
   ISQLDatabase database = transaction.Database;
   database.AddParameter(result, "SUBSCHEMA_NAME", subSchemaName, typeof(string));
   versionMajorParameterIndex = 0;
   versionMinorParameterIndex = 1;
   return result;
 }

 public static IDbCommand UpdateAttachedClientDataCommand(ITransaction transaction, string systemId, SystemName system,
     string clientName)
 {
     var result = transaction.CreateCommand();
     result.CommandText = "UPDATE ATTACHED_CLIENTS SET LAST_HOSTNAME = @LAST_HOSTNAME, LAST_CLIENT_NAME = @LAST_CLIENT_NAME WHERE SYSTEM_ID = @SYSTEM_ID";
     ISQLDatabase database = transaction.Database;
     database.AddParameter(result, "LAST_HOSTNAME", system == null ? null : system.HostName, typeof(string));
     database.AddParameter(result, "LAST_CLIENT_NAME", clientName, typeof(string));
     database.AddParameter(result, "SYSTEM_ID", systemId, typeof(string));
     return result;
 }

 public static IDbCommand InsertAttachedClientCommand(ITransaction transaction, string systemId, string hostName,
     string clientName)
 {
     var result = transaction.CreateCommand();
     result.CommandText = "INSERT INTO ATTACHED_CLIENTS (SYSTEM_ID, LAST_HOSTNAME, LAST_CLIENT_NAME) VALUES (@SYSTEM_ID, @LAST_HOSTNAME, @LAST_CLIENT_NAME)";
     ISQLDatabase database = transaction.Database;
     database.AddParameter(result, "SYSTEM_ID", systemId, typeof(string));
     database.AddParameter(result, "LAST_HOSTNAME", hostName, typeof(string));
     database.AddParameter(result, "LAST_CLIENT_NAME", clientName, typeof(string));
     return result;
 }

 public static IDbCommand UpdateSubSchemaCommand(ITransaction transaction, string subSchemaName,
     int versionMajor, int versionMinor)
 {
   IDbCommand result = transaction.CreateCommand();
   result.CommandText = "UPDATE MEDIAPORTAL_BASIS SET [email protected]_MAJOR, [email protected]_MINOR WHERE [email protected]_NAME";
   ISQLDatabase database = transaction.Database;
   database.AddParameter(result, "VERSION_MAJOR", versionMajor, typeof(int));
   database.AddParameter(result, "VERSION_MINOR", versionMinor, typeof(int));
   database.AddParameter(result, "SUBSCHEMA_NAME", subSchemaName, typeof(string));
   return result;
 }

        public static IDbCommand SelectDlnaMediaInfoCommand(ITransaction transaction, out int systemIdIndex,
            out int lastHostNameIndex, out int lastClientNameIndex)
        {
            var result = transaction.CreateCommand();

            //result.CommandText = "SELECT SYSTEM_ID, LAST_HOSTNAME, LAST_CLIENT_NAME FROM DLNA_MEDIA_INFO";

            systemIdIndex = 0;
            lastHostNameIndex = 1;
            lastClientNameIndex = 2;
            return result;
        }

    public static IDbCommand SelectAttachedClientsCommand(ITransaction transaction, out int systemIdIndex,
        out int lastHostNameIndex, out int lastClientNameIndex)
    {
      IDbCommand result = transaction.CreateCommand();

      result.CommandText = "SELECT SYSTEM_ID, LAST_HOSTNAME, LAST_CLIENT_NAME FROM ATTACHED_CLIENTS";

      systemIdIndex = 0;
      lastHostNameIndex = 1;
      lastClientNameIndex = 2;
      return result;
    }

 public static IDbCommand CreateMediaItemAspectMetadataCommand(ITransaction transaction, Guid miamId,
     string name, string serialization)
 {
   IDbCommand result = transaction.CreateCommand();
   result.CommandText = "INSERT INTO MIA_TYPES (MIAM_ID, NAME, CREATION_DATE, MIAM_SERIALIZATION) VALUES (@MIAM_ID, @NAME, @CREATION_DATE, @MIAM_SERIALIZATION)";
   ISQLDatabase database = transaction.Database;
   database.AddParameter(result, "MIAM_ID", miamId, typeof(Guid));
   database.AddParameter(result, "NAME", name, typeof(string));
   database.AddParameter(result, "CREATION_DATE", DateTime.Now, typeof(DateTime));
   database.AddParameter(result, "MIAM_SERIALIZATION", serialization, typeof(string));
   return result;
 }

 public static IDbCommand CreateUserAdditionalDataCommand(ITransaction transaction, Guid profileId, string dataKey, string additionalData)
 {
   IDbCommand result = transaction.CreateCommand();
   result.CommandText = "INSERT INTO USER_ADDITIONAL_DATA (PROFILE_ID, DATA_KEY, ADDITIONAL_DATA) VALUES (@PROFILE_ID, @DATA_KEY, @ADDITIONAL_DATA)";
   ISQLDatabase database = transaction.Database;
   database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
   database.AddParameter(result, "DATA_KEY", dataKey, typeof(string));
   database.AddParameter(result, "ADDITIONAL_DATA", additionalData, typeof(string));
   return result;
 }

    // User additional data

    public static IDbCommand SelectUserAdditionalDataCommand(ITransaction transaction, Guid profileId, string dataKey, out int additionalDataIndex)
    {
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "SELECT ADDITIONAL_DATA FROM USER_ADDITIONAL_DATA WHERE [email protected]_ID AND [email protected]_KEY";
      ISQLDatabase database = transaction.Database;
      database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
      database.AddParameter(result, "DATA_KEY", dataKey, typeof(string));

      additionalDataIndex = 0;
      return result;
    }

    public static IDbCommand DeleteUserMediaItemDataCommand(ITransaction transaction, Guid profileId, Guid? mediaItemId, string dataKey)
    {
      IDbCommand result = transaction.CreateCommand();
      result.CommandText = "DELETE FROM USER_MEDIA_ITEM_DATA WHERE [email protected]_ID";
      if (mediaItemId.HasValue)
        result.CommandText += " AND [email protected]_ITEM_ID";
      if (!string.IsNullOrEmpty(dataKey))
        result.CommandText += " AND [email protected]_KEY";

      ISQLDatabase database = transaction.Database;
      database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));

      if (mediaItemId.HasValue)
        database.AddParameter(result, "MEDIA_ITEM_ID", mediaItemId.Value, typeof(Guid));

      if (!string.IsNullOrEmpty(dataKey))
        database.AddParameter(result, "DATA_KEY", dataKey, typeof(string));

      return result;
    }