在这一类＆QUOT数据库连接;可重复使用的＆QUOT;？ [英] 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.

我搜了一下在互联网上code，连接到数据库。我创建了一个命名空间和一些类使用相同的code在不同的项目。

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.

在code和我的课是：

The code and my class is the following:

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.

这就是为什么我认为这件作品code我创建，并从互联网复制的，会为每一次我打电话时一个新的连接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（）保持的DatabaseConnection活着，直到我告诉程序放弃连接？

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.

推荐答案

你所面临的问题是，你自己已经coded进入一个角落里每个行动新的连接。你真正想要的瞄准，被认为是最好的做法，就是每批次的行动新的连接。

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(...);
}

那个code块的结尾，因为它是IDisposeable，它将关闭在Dispose方法为您联系。

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

东西我改变：

• 接口


• 改变方法，从静态到类的方法。


• 增加了新的方法来开闭连接


• 移动连接变量类级别范围


• 添加一个参数，可以让你连接字符串中传递的构造函数（你应该把这个连接字符串中您的Web.Config

编辑：

• 构造函数每建议的connectionString。

这篇关于在这一类＆QUOT数据库连接;可重复使用的＆QUOT;？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！