C# DbCommand.ExecuteNonQuery方法代码示例

 public int ExecuteNonQuery(DbCommand cmd)
 {
     DbConnection conn = null;
     try {
         conn = this.GetOpenConnection();
         cmd.Connection = conn;
         return cmd.ExecuteNonQuery();
     } finally {
         this.ReleaseConnection(conn);
     }
 }

 //public static int ExecuteNonQuery(string queryText)
 //{
 //    switch (DatabaseSettings.DbType)
 //    {
 //        case Enums.DatabaseType.MsSQL:
 //            using (SqlConnection sq = new SqlConnection(DatabaseSettings.ConnectionString))
 //            {
 //                sq.Open();
 //                using (SqlCommand sc = new SqlCommand(queryText, sq))
 //                {
 //                    return sc.ExecuteNonQuery();
 //                }
 //            }
 //        case Enums.DatabaseType.MySQL:
 //            using (MySqlConnection sq = new MySqlConnection(DatabaseSettings.ConnectionString))
 //            {
 //                sq.Open();
 //                using (MySqlCommand sc = new MySqlCommand(queryText, sq))
 //                {
 //                    return sc.ExecuteNonQuery();
 //                }
 //            }
 //        default:
 //            return 0;
 //    }
 //}
 public static int ExecuteNonQuery(DbCommand query, DbConnection dc)
 {
     switch (DatabaseSettings.DbType)
     {
         case Enums.DatabaseType.MsSQL:
         case Enums.DatabaseType.MySQL:
             using (query)
             {
                 query.Connection = dc;
                 return query.ExecuteNonQuery();
             }
         default:
             return -1;
     }
 }

 //----------------------------------------------------------------------------------------------------
 /// <summary>
 /// 쿼리 요청 ( UPDATE, INSERT, DELETE ... )
 /// </summary>
 /// <param name="sql">SQL문</param>
 /// <returns>성공 유무</returns>
 //----------------------------------------------------------------------------------------------------
 public bool NonQuery( string sql )
 {
     try
     {
         CloseQuery();
         m_command = m_connection.CreateCommand();
         m_command.CommandType = CommandType.Text;
         m_command.CommandText = sql;
         m_command.ExecuteNonQuery();
         CloseQuery();
         return true;
     }
     catch( Exception ex )
     {
         Log( ex.ToString() );
         m_error = ex.Message;
         CloseQuery();
         return false;
     }
 }

		private void Update11Relation(IDictionary<IDbObject, long> objectGraph, IDbObject o, DbConnection connection, RelationInfo relation, DbCommand command, string mappingTable, List<long> persistentRelation, PropertyInfo propertyInfo)
		{
			var propertyValue = (IDbObject) propertyInfo.GetValue(o, null);
			if (propertyValue != null)
			{
				var itemId = propertyValue.Id;
				if (persistentRelation.Contains(itemId))
				{
					persistentRelation.Remove(propertyValue.Id);
					if (!objectGraph.ContainsKey(propertyValue))
					{
						Update(propertyValue, connection, objectGraph);	
					}
				}
				else
				{
					if (itemId == 0)
					{
						Insert(propertyValue, connection, objectGraph);
					}
					else if (!objectGraph.ContainsKey(propertyValue))
					{
						Update(propertyValue, connection, objectGraph);
					}
					command.CommandText = "UPDATE " + mappingTable + " SET `" + relation.OriginalKey + "` = " + o.Id + " WHERE Id = " + propertyValue.Id;
					command.ExecuteNonQuery();
				}
			}
			if (persistentRelation.Count > 0)
			{
				command.CommandText = "UPDATE " + mappingTable + " SET `" + relation.OriginalKey + "` = NULL WHERE Id = @id";
				command.Prepare();
				command.Parameters.Add(new MySqlParameter("@Id", persistentRelation[0]));
				command.ExecuteNonQuery();
				for (var i = 1; i < persistentRelation.Count; i++)
				{
					command.Parameters["@Id"].Value = persistentRelation[i];
					command.ExecuteNonQuery();
				}
			}		
		}

		private void UpdateNNRelation(IDictionary<IDbObject, long> objectGraph, IDbObject o, DbConnection connection, RelationInfo relation, DbCommand command, string mappingTable, List<long> persistentRelation, PropertyInfo propertyInfo)
		{
			var listCurrentPropertyValue = (IList)propertyInfo.GetValue(o, null);
			if (listCurrentPropertyValue != null)
			{
				for (var i = 0; i < listCurrentPropertyValue.Count; i++)
				{
					var item = (IDbObject)listCurrentPropertyValue[i];
					if (item == null) continue;
					var itemId = item.Id;
					if (persistentRelation.Contains(itemId))
					{
						if (!objectGraph.ContainsKey(item))
						{
							Update(item, connection, objectGraph);
						}
						persistentRelation.Remove(itemId);
					}
					else
					{
						if (itemId == 0)
						{
							Insert(item, connection, objectGraph);
						}
						else if (!objectGraph.ContainsKey(item))
						{
							Update(item, connection, objectGraph);
						}
						try
						{
							command.CommandText = "INSERT INTO " + mappingTable + "  ( `" + relation.OriginalKey + "`, `" + relation.PartnerKey + "`) VALUES (" + o.Id + ", " + item.Id + ")";
							command.ExecuteNonQuery();
						}
						catch
						{
						}
						
					}
				}
			}
			if (persistentRelation.Count > 0)
			{
				command.CommandText = "DELETE FROM " + mappingTable + " WHERE `" + relation.OriginalKey + "` = " + o.Id + " AND `" + relation.PartnerKey + "` = @partnerId";
				command.Prepare();
				command.Parameters.Add(new MySqlParameter("@partnerId", persistentRelation[0]));
				command.ExecuteNonQuery();
				for (var i = 1; i < persistentRelation.Count; i++)
				{
					command.Parameters["@partnerId"].Value = persistentRelation[i];
					command.ExecuteNonQuery();
				}
			}
		}

 public int ExecuteNonQuery(DbCommand command)
 {
     int result = 0;
     doSynchronized(() => {
         DbConnection conn = GetOpenConnection();
         MySqlCommand mysqlCommand = (MySqlCommand)command;
         command.Connection = conn;
         int executeResult = command.ExecuteNonQuery();
         if ((int)mysqlCommand.LastInsertedId > 0) {
             result= (int)mysqlCommand.LastInsertedId;
         } else {
             result = executeResult;
         }
     });
     return result;
 }

 public override int ExecuteNonQuery(DbCommand command)
 {
     if (_Connection.State != System.Data.ConnectionState.Open) _Connection.Open();
     command.Connection = _Connection;
     command.Transaction = _Transaction;
     return command.ExecuteNonQuery();
 }

        /// <summary>
        /// Loads the contents of a local file into the given table.
        /// </summary>
        /// <param name="tablename">The name of the table to load the data into.</param>
        /// <param name="filename">The name of the local file to load the data from.</param>
        /// <param name="command">The MySQL command to perform the update with.</param>
        /// <param name="columns">The comma separated list of columns to load the data into.</param>
        private void LoadLocalFileIntoTable(string tablename, string filename, DbCommand command, string columns)
        {
            Console.WriteLine($"Truncating {tablename}");
            command.CommandText = $"TRUNCATE {tablename}";
            command.ExecuteNonQuery();

            Console.WriteLine($"Loading {tablename} table into database.");

            if (command is MySqlCommand)
            {
                command.CommandText = $"LOAD DATA LOCAL INFILE '{filename}' INTO TABLE {tablename} LINES TERMINATED BY '\r\n'";
            }
            else if (command is NpgsqlCommand)
            {
                string winFilename = new FileInfo(filename).FullName;
                command.CommandText = $"COPY {tablename} ({columns}) FROM '{winFilename}' (DELIMITER '\t')";
            }
            else
            {
                throw new ArgumentException();
            }

            command.ExecuteNonQuery();
        }

 /// <summary>
 /// Updates the usercard IDs in the given table name with new IDs, using the oldtonewcards table.
 /// </summary>
 /// <param name="tablename">The table to update.</param>
 /// <param name="command">The command to update the table with.</param>
 private void UpdateTableWithNewCardIDs(string tablename, DbCommand command)
 {
     command.CommandText = $"UPDATE {tablename} t SET cardid = ( SELECT o.newcardid FROM oldtonewcards o WHERE o.oldcardid = t.cardid )";
     command.ExecuteNonQuery();
 }

        /// <summary>
        /// Truncates existing user tables, and loads data from the old database.
        /// </summary>
        /// <param name="command">The sql command to use.</param>
        private void RefreshDelverFromMagicDb(DbCommand command)
        {
            Console.Write("Refreshing data set from magic_db database.");

            command.CommandText = @"
                INSERT INTO historicalcards
                (SELECT cardid id, name FROM magic_db.oracle)";
            command.ExecuteNonQuery();

            command.CommandText = "TRUNCATE usercards";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO usercards ( SELECT * FROM magic_db.usercards)";
            command.ExecuteNonQuery();

            command.CommandText = "TRUNCATE usercardchanges";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO usercardchanges ( SELECT * FROM magic_db.usercardlog)";
            command.ExecuteNonQuery();

            command.CommandText = "TRUNCATE taglinks";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO taglinks ( SELECT * FROM magic_db.taglinks)";
            command.ExecuteNonQuery();

            command.CommandText = "TRUNCATE deckcards";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO deckcards ( SELECT * FROM magic_db.deckcards)";
            command.ExecuteNonQuery();

            Console.WriteLine("Done");
        }

        /// <summary>
        /// Updates a database using the given command.
        /// </summary>
        /// <param name="command">The command to update the database (includes which database to update)</param>
        /// <param name="refreshFromOldData">Whether to update the database with old data.</param>
        private void UpdateDatabaseWithCommand(DbCommand command, bool refreshFromOldData)
        {
            if (!refreshFromOldData)
            {
                command.CommandText = "TRUNCATE historicalcards";
                command.ExecuteNonQuery();

                command.CommandText = @"
                       INSERT INTO historicalcards
                       (SELECT id, name FROM cards)";
                command.ExecuteNonQuery();
            }

            Console.Write("Loading files into database... ");

            this.LoadLocalFileIntoTable("cards", "temp/cards", command, "id,name,cost,cmc,colour,colouridentity,numcolours,type,subtype,power,numpower,toughness,numtoughness,loyalty,rules");
            this.LoadLocalFileIntoTable("cardsets", "temp/cardsets", command, "cardid,setcode,multiverseid,artist,flavourtext,rarity,collectornum");
            this.LoadLocalFileIntoTable("blocks", "temp/blocks", command, "id,name");
            this.LoadLocalFileIntoTable("sets", "temp/sets", command, "id,code,name,blockid,release_date");
            this.LoadLocalFileIntoTable("types", "temp/types", command, "id,name");
            this.LoadLocalFileIntoTable("subtypes", "temp/subtypes", command, "id,name");
            this.LoadLocalFileIntoTable("cardlinks", "temp/links", command, "cardid_from,cardid_to,link_type");

            Console.WriteLine("Done");

            Console.Write("Creating old-to-new-card-id table... ");

            command.CommandText = "TRUNCATE oldtonewcards";
            command.ExecuteNonQuery();

            command.CommandText = @"
            INSERT INTO oldtonewcards(
                SELECT hc.id, c.id
                FROM cards c
                JOIN historicalcards hc
                ON hc.name = c.name
            )";
            command.ExecuteNonQuery();

            Console.WriteLine("Done");

            Console.Write("Updating old card IDs with new values... ");

            this.UpdateTableWithNewCardIDs("usercards", command);
            this.UpdateTableWithNewCardIDs("usercardchanges", command);
            this.UpdateTableWithNewCardIDs("taglinks", command);
            this.UpdateTableWithNewCardIDs("deckcards", command);

            Console.WriteLine("Done");
        }

        //Update statement
        public void Update(DbCommand update_query)
        {
            try
            {
                //Open connection
                if (this.OpenConnection() != null)
                {

                    //Assign the query using CommandText
                    update_query.Connection = connection;

                    update_query.Prepare();

                    //Execute query
                    update_query.ExecuteNonQuery();

                    //close connection
                    this.CloseConnection();
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
            }
        }

        //Insert statement
        public void Insert(DbCommand insert_query)
        {
            //open connection
            if (this.OpenConnection() != null)
            {

                insert_query.Connection = connection;
                insert_query.Prepare();

                //Execute command
                insert_query.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

 //Delete statement
 public void Delete(DbCommand delete_query)
 {
     try
     {
         if (this.OpenConnection() != null)
         {
             delete_query.Connection = connection;
             delete_query.Prepare();
             delete_query.ExecuteNonQuery();
             this.CloseConnection();
         }
     }
     catch (Exception e)
     {
         Debug.WriteLine(e.Message);
     }
 }