数据库连接是否在此类“可重用"中? [英] Is the database connection in this class "reusable"?

问题描述

我是asp.net的新手，所以这可能是一个非常基本的问题，但我无法弄清楚.

I'm new to asp.net so this might be really basic question, but i cant figure it out.

我在互联网上找到了一些连接数据库的代码.我创建了一个名称空间和一些类，以便在不同的项目中使用相同的代码.

I found a bit of code on the internet, that connects to database. And i created a namespace and some classes to use the same code in different projects.

代码和我的课程如下:

namespace databaseFunctions
{
    public class databaseConnection
    {
private static string databaseConnectionString()
        {
            return "DRIVER={MySQL ODBC 5.1 Driver}; ........";
        }

        public static DataTable getFromDatabase(string SQL)
        {
            DataTable rt = new DataTable();
            DataSet ds = new DataSet();
            OdbcDataAdapter da = new OdbcDataAdapter();
            OdbcConnection con = new OdbcConnection(databaseConnectionString());
            OdbcCommand cmd = new OdbcCommand(SQL, con);
            da.SelectCommand = cmd;
            da.Fill(ds);
            try
            {
                rt = ds.Tables[0];
            }
            catch
            {   
                rt = null;
            }
            return rt;
        }

        public static Boolean insertIntoDatabase(string SQL)
        {

            OdbcDataAdapter da = new OdbcDataAdapter();
            OdbcConnection con = new OdbcConnection(databaseConnectionString());
            OdbcCommand cmd = new OdbcCommand(SQL, con);
            con.Open();
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }

        }

}

从数据库获取数据或将数据插入某些数据库没有问题. 但.当我尝试从mysql数据库中获取last_insert_id()时.我只有零.

There is no problem getting data from database, or insert data into some database. But. when i try to get the last_insert_id() from the mysql database. i only get a zero.

这就是为什么我认为我创建并从Internet复制的这段代码每次调用"getFromDatabase(SQL)"都会创建一个新连接的原因

This is why i think that this piece of code I've created and copied from internet, creates a new connection for every time i call the "getFromDatabase(SQL)"

在我告诉程序放弃连接之前，是否有人可以帮助我修复此类getFromDatabase()使数据库连接保持活动状态?

Is there anyone that could help me with fixing this class getFromDatabase() to keep the databaseconnection alive until i tell the program to abandon the connection?

我想应该更改新的OdbcConnection"?是否可以检查是否已经存在连接? 我已经用经典的asp完成了数百次，但是现在，有了类和东西.我完全迷路了.

I guess it is the "new OdbcConnection" that should be changed? Is it possible to check if there already is a connection alive? I've done this hundreds of times in classic asp, but now, with classes and stuff. I'm totally lost.

推荐答案

您面临的问题是，您已将自己编码为每个动作新的连接"角.您真正想要的目标是被视为最佳实践，它是每批动作的新连接".

The problem you face is that you've coded yourself into a "new connection per action" corner. What you really want to aim for,and is considered best practice, is "new connection per batch of actions".

在这种情况下，我建议在需要时打开连接，并在处置后关闭.我们要做的是将odbc适配器移至更大的作用域变量，以便可以在类中对其进行访问.

What I recommend in this case is to open connection when required, and close when disposed. What we'll do is move the odbc adapters to a larger scoped variable so that it can be accessed within the class.

namespace databaseFunctions
{
    public class databaseConnection:IDisposable
    {
        private OdbcConnection con;
        private string connectionString;

        public databaseConnection(string connectionString){
            this.connectionString = connectionString;
        }


        public void OpenConnection(){
            if (con == null || con.IsClosed ){ // we make sure we're only opening connection once.
                con = new OdbcConnection(this.connectionString);
            }
        }
        public void CloseConnection(){
            if (con != null && con.IsOpen){ // I'm making stuff up here
                con.Close();
            }
        }

        public DataTable getFromDatabase(string SQL)
        {
            OpenConnection();

            DataTable rt = new DataTable();
            DataSet ds = new DataSet();
            OdbcCommand cmd = new OdbcCommand(SQL, con);
            da.SelectCommand = cmd;
            da.Fill(ds);
            try
            {
                rt = ds.Tables[0];
            }
            catch
            {   
                rt = null;
            }
            return rt;
        }

        public Boolean insertIntoDatabase(string SQL)
        {
            OpenConnection();

            OdbcCommand cmd = new OdbcCommand(SQL, con);
            con.Open();
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }

        }


        // Implementing IDisposable method
        public void Dispose(){
            CloseConenction();
        }
    }
}

现在，下次您使用课程时，做类似的事情

Now the next time you use your class do something like

using (DatabaseConnection db = new DatabaseConnection()){
    db.InsertIntoDatabase(...);
    db.GetLastInsertID();
    db.GetFromDatabase(...);
}

在该代码块的末尾，由于它是IDisposeable的，它将在dispose方法中为您关闭该连接.

At the end of that code block, because it is IDisposeable, it will close that connection for you in the dispose method.

我更改的内容:

• 已实现 IDisposable 接口
• 将方法从静态方法更改为类方法.
• 添加了用于打开关闭连接的新方法
• 将连接变量移至类级别范围
• 向构造函数添加了一个参数，该参数使您可以传递连接字符串(您应将此连接字符串放入Web.Config

• implemented IDisposable interface
• changed methods from static to class methods.
• added new methods for opening closing connection
• moved connection variable to class level scope
• added an argument to the constructor that lets you pass in a connection string (you should put this connection string in you Web.Config

• 构造函数根据每个建议引入connectionString.

这篇关于数据库连接是否在此类“可重用"中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！