![clock](../themes/indigo/img/timeicon.gif)
June 19, 2024 09:14 by
Peter
The seamless integration of databases is essential for current web development's data management and retrieval processes. This article explains how to create a robust C# Data Access Layer (DAL) that can interface with a MySQL database. This code provides an example of how to implement such a DAL. Using a MySQL database is the responsibility of the ClsDal class. It has functions to manage connection strings and get data out of the database.
![](https://aspnetmvceuropeanhosting.hostforlife.eu/image.axd?picture=2015%2f10%2fhfl-new-banner.png)
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.