Microsoft.Data.SqlClient

Microsoft.Data.SqlClient

Microsoft.Data.SqlClient provides data access for Microsoft SQL Server and Azure SQL Database.

BlogModel.cs

public class BlogModel
{
    public int BlogId { get; set; }
    public string BlogTitle { get; set; }
    public string BlogAuthor { get; set; }
    public string BlogContent { get; set; }
}

1. Constructor

public AdoDotNetExample(SqlConnectionStringBuilder sqlConnectionStringBuilder)
{
    _sqlConnectionStringBuilder = sqlConnectionStringBuilder;
}

Summary: The constructor initializes the class with a SqlConnectionStringBuilder object, which is used to manage the database connection string.

2. Read Method

public void Read()
{
    using (SqlConnection connection = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString))
    {
        connection.Open();
        Console.WriteLine("Connection open.");

        string query = "SELECT * FROM tbl_blog";
        using (SqlCommand cmd = new SqlCommand(query, connection))
        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sqlDataAdapter.Fill(dt);
            
            var blogs = ConvertToBlogModels(dt);
            foreach (var blog in blogs)
            {
                Console.WriteLine($"Blog Id => {blog.BlogId}");
                Console.WriteLine($"Blog Title => {blog.BlogTitle}");
                Console.WriteLine($"Blog Author => {blog.BlogAuthor}");
                Console.WriteLine($"Blog Content => {blog.BlogContent}");
                Console.WriteLine("--------------------------------");
            }
        }

        Console.WriteLine("Connection close.");
    }
}

Summary: This method connects to the database, executes a SELECT query to retrieve all rows from the tbl_blog table, converts the resulting DataTable to a list of BlogModel objects, and prints each blog's data to the console.

3. Edit Method

public void Edit(string id)
{
    using (SqlConnection connection = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString))
    {
        connection.Open();

        string query = "SELECT * FROM tbl_blog WHERE BlogId = @BlogId";
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {
            cmd.Parameters.AddWithValue("@BlogId", id);
            using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sqlDataAdapter.Fill(dt);

                var blogs = ConvertToBlogModels(dt);
                if (blogs.Count == 0)
                {
                    Console.WriteLine("No data found.");
                    return;
                }

                var blog = blogs[0];
                Console.WriteLine($"Blog Id => {blog.BlogId}");
                Console.WriteLine($"Blog Title => {blog.BlogTitle}");
                Console.WriteLine($"Blog Author => {blog.BlogAuthor}");
                Console.WriteLine($"Blog Content => {blog.BlogContent}");
            }
        }
    }
}

Summary: This method retrieves a specific blog entry based on the provided id, converts the resulting DataTable to a list of BlogModel objects, prints the details if found, or indicates that no data was found.

4. Create Method

public void Create(string title, string author, string content)
{
    using (SqlConnection connection = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString))
    {
        connection.Open();

        string query = @"INSERT INTO [dbo].[Tbl_Blog]
                         ([BlogTitle], [BlogAuthor], [BlogContent])
                         VALUES (@BlogTitle, @BlogAuthor, @BlogContent)";
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {
            cmd.Parameters.AddWithValue("@BlogTitle", title);
            cmd.Parameters.AddWithValue("@BlogAuthor", author);
            cmd.Parameters.AddWithValue("@BlogContent", content);
            int result = cmd.ExecuteNonQuery();

            Console.WriteLine(result > 0 ? "Saving Successful." : "Saving Failed.");
        }
    }
}

Summary: This method inserts a new blog entry into the Tbl_Blog table with the provided title, author, and content, and indicates whether the save operation was successful.

5. Update Method

public void Update(int id, string title, string author, string content)
{
    using (SqlConnection connection = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString))
    {
        connection.Open();

        string query = @"UPDATE [dbo].[Tbl_Blog]
                         SET [BlogTitle] = @BlogTitle,
                             [BlogAuthor] = @BlogAuthor,
                             [BlogContent] = @BlogContent
                         WHERE BlogId = @BlogId";
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {
            cmd.Parameters.AddWithValue("@BlogId", id);
            cmd.Parameters.AddWithValue("@BlogTitle", title);
            cmd.Parameters.AddWithValue("@BlogAuthor", author);
            cmd.Parameters.AddWithValue("@BlogContent", content);
            int result = cmd.ExecuteNonQuery();

            Console.WriteLine(result > 0 ? "Updating Successful." : "Updating Failed.");
        }
    }
}

Summary: This method updates an existing blog entry in the Tbl_Blog table based on the provided id, title, author, and content, and indicates whether the update operation was successful.

6. Delete Method

public void Delete(int id)
{
    using (SqlConnection connection = new SqlConnection(_sqlConnectionStringBuilder.ConnectionString))
    {
        connection.Open();

        string query = @"DELETE FROM Tbl_Blog WHERE BlogId = @BlogId";
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {
            cmd.Parameters.AddWithValue("@BlogId", id);
            int result = cmd.ExecuteNonQuery();

            Console.WriteLine(result > 0 ? "Deleting Successful." : "Deleting Failed.");
        }
    }
}

Summary: This method deletes a blog entry from the Tbl_Blog table based on the provided id, and indicates whether the delete operation was successful.

Helper Method: Convert DataTable to List of BlogModel

private List<BlogModel> ConvertToBlogModels(DataTable dt)
{
    var blogs = new List<BlogModel>();
    foreach (DataRow row in dt.Rows)
    {
        var blog = new BlogModel
        {
            BlogId = Convert.ToInt32(row["BlogId"]),
            BlogTitle = row["BlogTitle"].ToString(),
            BlogAuthor = row["BlogAuthor"].ToString(),
            BlogContent = row["BlogContent"].ToString()
        };
        blogs.Add(blog);
    }
    return blogs;
}

Summary: This helper method converts a DataTable to a list of BlogModel objects, which is used in the Read and Edit methods for easier data manipulation and readability.