ADO.NET Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. ADO.NET Tutorial

Insert, Update, And Delete data in a database using ADO.NET with a SqlCommand

Back to: ADO.NET Tutorial

Using ADO.NET, you can insert, update, and delete data in a database using SqlCommand. Here's how each operation works:

Insert Data

The INSERT SQL statement adds new records to a database table.

Example Code

using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
// Connection string string connectionString = "your_connection_string_here";
// SQL INSERT query
string insertQuery = "INSERT INTO YourTable (Name, Age) VALUES (@Name, @Age)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try { connection.Open();
using (SqlCommand command = new SqlCommand(insertQuery, connection))
{
// Add parameters command.Parameters.AddWithValue("@Name", "Alice");
command.Parameters.AddWithValue("@Age", 25);
// Execute the query
int rowsInserted = command.ExecuteNonQuery();
Console.WriteLine($"{rowsInserted} row(s) inserted.");
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
}
}

Update Data

The UPDATE SQL statement modifies existing records in a table.

Example Code

using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
// Connection string
string connectionString = "your_connection_string_here";
// SQL UPDATE query
string updateQuery = "UPDATE YourTable SET Age = @Age WHERE Name = @Name";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try {
connection.Open();
using (SqlCommand command = new SqlCommand(updateQuery, connection))
{
// Add parameters command.Parameters.AddWithValue("@Name", "Alice");
command.Parameters.AddWithValue("@Age", 30);
// Execute the query int rowsUpdated = command.ExecuteNonQuery();
Console.WriteLine($"{rowsUpdated} row(s) updated."); } }
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
}
}

Delete Data

The DELETE SQL statement removes records from a table.

Example Code

using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
// Connection string
string connectionString = "your_connection_string_here";
// SQL DELETE query
string deleteQuery = "DELETE FROM YourTable WHERE Name = @Name";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try {
connection.Open();
using (SqlCommand command = new SqlCommand(deleteQuery, connection))
{
// Add parameters
command.Parameters.AddWithValue("@Name", "Alice");
// Execute the query
int rowsDeleted = command.ExecuteNonQuery();
Console.WriteLine($"{rowsDeleted} row(s) deleted.");
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
}
}

Explanation of Common Steps

  1. Connection String: Replace "your_connection_string_here" with your actual database connection string.

  2. SQL Commands:

    • INSERT INTO: Adds a new row.
    • UPDATE: Modifies data in an existing row.
    • DELETE FROM: Removes one or more rows.

  3. SqlConnection:

    • Establish a connection to the database.
    • Ensure it is closed or disposed after use by enclosing it in a using block.

  4. SqlCommand:

    • Define the SQL query to execute.
    • Use the Parameters collection to safely pass values to prevent SQL injection.

  5. ExecuteNonQuery: Executes the command and returns the number of rows affected.

Output

Insert Example:

1 row(s) inserted.

Update Example:

1 row(s) updated.

Delete Example:

1 row(s) deleted. 




Best Practices


    
    
  1. 
    
    
    Use Parameters: Always use parameterized queries to avoid SQL injection attacks.
    
    
    2. 
    
  2. 
    
    
    Transaction Management: For multiple operations, consider using transactions for data consistency.
    
    
    3. 
    
  3. 
    
    
    Error Handling: Implement robust error handling using try-catch blocks.
    
    
    4. 
    
  4. 
    
    
    Dispose Resources: Use using statements to ensure connections and commands are properly disposed of.
    
    
    5. 






With these examples, you can efficiently insert, update, and delete data in a database using ADO.NET.






                        

                               
                                                    


                    



                

            

        

    






        
        
Scroll to Top