How To Connect Database in ASP.NET MVC using C# with ADO.NET

Introduction

Data management and retrieval in modern web development depend heavily on the smooth integration of databases. This article explores building a powerful Data Access Layer (DAL) in C# that is intended to communicate with a MySQL database. An example of how to implement such a DAL is given in the provided code.

The ClsDal class is responsible for interacting with a MySQL database. It contains methods to retrieve data from the database and handle connection strings.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

namespace DemoAPI.DB_Logic
{
    public class ClsDal
    {
        private static string mstrPath = null;
        static ClsCommonCryptography mobjCryptography = new ClsCommonCryptography();

        public static DataSet Qry_WithDataSet(string ProcWithParameter)
        {
            IErrorRepository error = new ErrorRepository();
            DataSet ds = new DataSet();
            try
            {
                if (GetConnStr() != null)
                {
                    MySqlConnection sqlCon = new MySqlConnection(mstrPath);

                    sqlCon.Open();

                    MySqlCommand sqlCmd = new MySqlCommand(ProcWithParameter, sqlCon);

                    sqlCmd.CommandTimeout = 0;

                    MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter
                    {
                        SelectCommand = sqlCmd
                    };

                    DataSet dtReturn = new DataSet();

                    sqlDataAdapter.Fill(dtReturn);

                    sqlCmd.Dispose();
                    sqlDataAdapter.Dispose();
                    sqlCon.Dispose();

                    return dtReturn;
                }
                else
                {
                    return null;
                }
            }
            catch (MySqlException sqlEx)
            {
                error.DBlogError("Qry_WithDataSet", ProcWithParameter, "Output" + "\n" + sqlEx.ToString());
                return null;
            }
            catch (Exception ex)
            {
                error.DBlogError("DBError Method : Qry_WithDataSet", ProcWithParameter, "Output" + "\n" + ex.ToString());
                return null;
            }
        }

        private static string GetConnStr()
        {
            IErrorRepository error = new ErrorRepository();
            try
            {
                mstrPath = System.Configuration.ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString.ToString();
                mstrPath = mobjCryptography.StringDecrypt(mstrPath);
                return mstrPath;
            }
            catch (Exception ex)
            {
                error.DBlogError("DBError Method : GetConnStr", mstrPath, "Output" + "\n" + ex.ToString());
                return null;
            }
        }
    }
}

Data Access Layer (DAL) for MySQL database operations is defined by the supplied C# code as the ClsDal class in the DemoAPI.DB_Logic namespace. It offers techniques to safely manage connections and query the database. A decrypted connection string that is obtained from the configuration file is used by the Qry_WithDataSet method to run SQL queries or stored procedures against the database. Ensuring strong error management, it manages exceptions through organized error logging. While both methods use an `IErrorRepository` interface to log database-related issues, the GetConnStr method decrypts and obtains the database connection string. Generally speaking, this code promotes security and dependability by encapsulating best practices for database interaction in the context of web applications.

Conclusion  

For MySQL databases, implementing a Data Access Layer in C# requires connection management, query execution, and error handling done right. These best practices are demonstrated by the given code, which shows how to safely connect to a MySQL database, run queries, and log problems for maintenance and troubleshooting.

FAQs

Q 1. What is a Data Access Layer (DAL)?

Ans. A Data Access Layer (DAL) is a software design pattern or component that provides a simplified and consistent interface to interact with a database or other data sources.

Q 2. Why is a Data Access Layer important?

Ans. A DAL separates the business logic of an application from the database interactions, promoting a cleaner and more maintainable codebase. It also improves security by centralizing data access and query management.

Q 3. What are the key benefits of using a Data Access Layer?

Ans.

• Modularity: DAL promotes modularity by separating database access logic from business logic.
• Security: Centralized data access allows for easier implementation of security measures.
• Maintainability: Changes to database schema or technology can be managed within the DAL without affecting the rest of the application.
• Performance: DAL can optimize data access patterns and queries for better performance.

Q 4. What are common components of a Data Access Layer?

Ans.

• Data Access Objects (DAOs): These are responsible for encapsulating the logic required to access data sources.
• Data Transfer Objects (DTOs): DTOs are used to transfer data between different layers of an application.
• Connection Management: Handling database connections and transactions.
• Query Management: Managing database queries and ensuring efficient execution.

Q 5. Which design patterns are commonly used in Data Access Layers?

Ans

• Repository Pattern: Provides a higher-level interface to access data, abstracting away the details of how data is stored and retrieved.
• Data Mapper Pattern: Maps objects to records in a database and vice versa, handling the translation between objects and database entries.