Here's a comprehensive guide on how to insert data into SQL using C#

Here's a comprehensive guide on how to insert data into SQL using C#:

1. Basic Setup with ADO.NET

First, add the necessary using directives:

csharp

using System.Data;
using System.Data.SqlClient; // For .NET Framework
// or
using Microsoft.Data.SqlClient; // For .NET Core/.NET 5+

2. Connection String

csharp

string connectionString = "Server=your_server;Database=your_database;User Id=your_username;Password=your_password;";
// or for Windows Authentication:
// string connectionString = "Server=your_server;Database=your_database;Integrated Security=true;";

3. Basic Insert Example

csharp

public void InsertUser(string name, string email)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Email", email);
            
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

4. Insert with Return Value (Get Inserted ID)

csharp

public int InsertUserReturnId(string name, string email)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = @"INSERT INTO Users (Name, Email) 
                         VALUES (@Name, @Email);
                         SELECT SCOPE_IDENTITY();";
        
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Email", email);
            
            connection.Open();
            int newId = Convert.ToInt32(command.ExecuteScalar());
            return newId;
        }
    }
}

5. Using Entity Framework Core

First, install the EF Core package:

bash

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Define your model and context:

csharp

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString);
    }
}

Insert using EF Core:

csharp

public void InsertUserWithEF(string name, string email)
{
    using (var context = new AppDbContext())
    {
        var user = new User { Name = name, Email = email };
        context.Users.Add(user);
        context.SaveChanges();
        
        Console.WriteLine($"User inserted with ID: {user.Id}");
    }
}

6. Bulk Insert

csharp

public void BulkInsertUsers(List<User> users)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            try
            {
                foreach (var user in users)
                {
                    using (SqlCommand command = new SqlCommand(
                        "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", 
                        connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Name", user.Name);
                        command.Parameters.AddWithValue("@Email", user.Email);
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

7. Using Dapper (Lightweight ORM)

Install Dapper:

bash

dotnet add package Dapper

csharp

using Dapper;

public void InsertUserWithDapper(string name, string email)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        var query = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        connection.Execute(query, new { Name = name, Email = email });
    }
}

8. Error Handling

csharp

public bool TryInsertUser(string name, string email)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
            
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@Email", email);
                
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                return rowsAffected > 0;
            }
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"SQL Error: {ex.Message}");
        return false;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"General Error: {ex.Message}");
        return false;
    }
}

9. Async Insert

csharp

public async Task<int> InsertUserAsync(string name, string email)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = @"INSERT INTO Users (Name, Email) 
                         VALUES (@Name, @Email);
                         SELECT SCOPE_IDENTITY();";
        
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Email", email);
            
            await connection.OpenAsync();
            var result = await command.ExecuteScalarAsync();
            return Convert.ToInt32(result);
        }
    }
}

Key Points to Remember:

1. Always use parameterized queries to prevent SQL injection

2. Use using statements for proper resource disposal

3. Handle exceptions appropriately

4. Validate input data before inserting

5. Consider using ORMs like Entity Framework for complex applications

6. Use async methods for better performance in web applications

Choose the approach that best fits your project's requirements and complexity level!