C＃中的数据访问层代码 [英] Data Access Layer code in C#

问题描述

我正在制作一个Windows应用程序，而且我不知道如何分割像Business Layer和Data Access Layer这样的代码。现在，我已将UI创建为Windows项目，将业务层创建为类库，将数据访问层创建为另一个类库。

我想确保我为数据访问层创建的以下示例代码适用于该体系结构。我的目标是将值作为Dictionary对象传递给DAL，DAL将调用存储过程来执行BL请求的任务。

以下代码技术上是否正确？

Hi,

I am making a windows application, and I don't have any idea about splitting code like Business Layer, and Data Access Layer. Now, I have created UI as Windows Project, Business Layer as Class Library , and Data Access Layer as another Class Library.
I want to make sure that the below sample code I have created for Data Access Layer is right for the architecture. I am aiming to pass the values as Dictionary object to DAL, and DAL will call the stored procedure to perform the task requested by BL.
Is the below code technically correct ??

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace HRDAL
{
    public class procExec
    {
        SqlConnection con =null;
        public procExec(string conStr)
        {
            con = new SqlConnection(conStr);
            con.Open();

        }

        public KeyValuePair<int, string> ExecuteSP(string SPName, Dictionary<string, string> SPParameters)
        {
            if (con.State == ConnectionState.Open)
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = SPName;
                foreach(KeyValuePair<string, string> Pm in SPParameters)
                    com.Parameters.Add(new SqlParameter(Pm.Key, Pm.Value));

                return new KeyValuePair<int, string>(1000, "Success");
            }
            else
                return new KeyValuePair<int, string>(1001, "No Connection to the Server");
        }

        public ~procExec()
        {
            con.Close();
        }
    }
}

推荐答案

嗯，好吧，好吧。



我的主要抱怨是你不应该一直持有Connection Open;你应该打开它并在ExecuteSP中关闭它。



此外，使用Dispose而不是析构函数，让它调用Connection的Dispose。

可能使用语句将命令放在中。

我个人使用 IDbCommand com = con.CreateCommand（ ），但那只是我。



最重要的是，你没有执行这个程序。



编辑：我刚才意识到如果你不小心你可能会遇到线程问题 - 考虑锁定连接。

Ummm, well, OK.

My primary complaint is that you shouldn't hold the Connection Open the whole time; you should Open it and Close it in ExecuteSP.

Also, rather than a destructor, use a Dispose and have it call the Connection's Dispose.
Possibly put the Command in a using statement.
Personally I'd use IDbCommand com = con.CreateCommand() , but that's just me.

Most importantly, you don't execute the procedure.

I just realized that you may have threading issues if you're not careful -- consider locking the Connection.

这篇关于C＃中的数据访问层代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！