Skip to content

Microsoft.Data.SqlClient

Sann Lynn Htun edited this page Nov 21, 2024 · 6 revisions

Microsoft.Data.SqlClient

Microsoft.Data.SqlClient provides data access for Microsoft SQL Server and Azure SQL Database. Sure, let's walk through each method in the AdoDotNetExample class to summarize its functionality:

Explanation

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);

            foreach (DataRow dr in dt.Rows)
            {
                Console.WriteLine($"Blog Id => {dr["BlogId"]}");
                Console.WriteLine($"Blog Title => {dr["BlogTitle"]}");
                Console.WriteLine($"Blog Author => {dr["BlogAuthor"]}");
                Console.WriteLine($"Blog Content => {dr["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, and prints each row'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);

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

                DataRow dr = dt.Rows[0];
                Console.WriteLine($"Blog Id => {dr["BlogId"]}");
                Console.WriteLine($"Blog Title => {dr["BlogTitle"]}");
                Console.WriteLine($"Blog Author => {dr["BlogAuthor"]}");
                Console.WriteLine($"Blog Content => {dr["BlogContent"]}");
                Console.WriteLine("--------------------------------");
            }
        }
    }
}

Summary: This method retrieves a specific blog entry based on the provided id, 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.

C# Basics Wiki

Core Concepts

Object-Oriented Programming (OOP)

Advanced Topics

Miscellaneous

Tools and Resources

Clone this wiki locally