SqlConnection经理boilerplate..thing？

Question

这里只有一个从爪哇到C#的学生。在我们学习Java的时候，我们得到了这种SQL连接管理器类的东西，这基本上只是一堆代码，以便在不同的类中更容易地访问它(它是由我的丹麦老师编写的，有一些错误的拼写/内部笑话，不确定)：

 public class DbConnection
 {   //Constants used to get access to the database
//SQL Server
private static final String  driver = "nope";
//  private static final String  driver = "nope";
private static final String  databaseName = ";databaseName=nope";
//SQL Server
//  private static String  userName = ";user=sa";
private static String  userName = "; user=nope";
private static String password = ";password=nope";


private DatabaseMetaData dma;
private static Connection con;
// an instance of the class is generetated
private static DbConnection  instance = null;

// the constructor is private to ensure that only one object of this class is created
DbConnection()
{
    String url = driver + databaseName + userName + password;

    try{
        //load af driver
        //SQL Server
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        System.out.println("Load af class ok");

    }
    catch(Exception e){
        System.out.println("Can not find the driver");
        System.out.println(e.getMessage());
    }//end catch
    try{
        //connection to the database
        con = DriverManager.getConnection(url);
        //set autocommit
        con.setAutoCommit(true);
        dma = con.getMetaData(); // get meta data
        System.out.println("Connection to " + dma.getURL());
        System.out.println("Driver " + dma.getDriverName());
        System.out.println("Database product name " + dma.getDatabaseProductName());
    }//end try
    catch(Exception e){

        System.out.println("Problems with the connection to the database");
        System.out.println(e.getMessage());
        System.out.println(url);
    }//end catch
}//end  constructor

//closeDb: closes the connection to the database
public static void closeConnection()
{
    try{
        con.close();
        System.out.println("The connection is closed");
    }
     catch (Exception e){
        System.out.println("Error trying to close the database " +  e.getMessage());
     }
}//end closeDB

//getDBcon: Get-method, returns the connection to the database
public  Connection getDBcon()
{
   return con;
}
//this method is used to get the instance of the connection
public static DbConnection getInstance()
{
    if (instance == null)
    {
      instance = new DbConnection();
    }
    return instance;
}
public static void startTransaction()
{ try{
    con.setAutoCommit(false);
    }
  catch(Exception e){
    System.out.println("fejl start transaction");
    System.out.println(e.getMessage());
  }
}
public static void commitTransaction()
{ try{
    con.setAutoCommit(true);
    }
  catch(Exception e){
    System.out.println("fejl commit transaction");
    System.out.println(e.getMessage());
  }
}
public static void rollbackTransaction()
{ try{
    con.rollback();
    con.setAutoCommit(true);
    }
  catch(Exception e){
    System.out.println("fejl rollback transaction");
    System.out.println(e.getMessage());
  }
}
}//end DbConnection

因此，为了适应C#第二年，我想首先在C#中重新创建它，首先:在C#中使用它是个好主意吗？我看到很多人都在用

 using(SqlConnection....){}

我不知道如何实现autoCommits/Transaction回滚，因为例如，C#中的事务是不同的类，到目前为止，我创建了这个小类：

class DbConnection
{
    private const string DB_USER_ID = "user id=sa;";
    private const string DB_USER_PASSWORD = "password=nope;";
    private const string DB_SERVER_URL = @"server=localhost\SQLExpress1;";
    private const string DB_NAME = "database=test; ";
    private const string DB_TIME_OUT = "connection timeout=30";
    private const string DB_TRUSTED_CONN = "Trusted_Connection=yes;";

    private static SqlConnection myConnection = null;
    private static DbConnection instance = null;

    // private constructor to ensure that only object of this class is created
    private DbConnection()
    {
        createConnection();
    }
    // Instantiates SqlConnection object
    private void createConnection()
    {
        Console.WriteLine("Attempting to create connectiong...");
        try
        {
            myConnection = new SqlConnection(DB_USER_ID +
                DB_USER_PASSWORD +
                DB_SERVER_URL +
                DB_TRUSTED_CONN +
                DB_NAME +
                DB_TIME_OUT);
        }
        catch (Exception e)
        {
            Console.WriteLine("Problems with the connection to the database");
            Console.WriteLine(e.Message);
        }
    }

    private void openConnection()
    {
        try{
            myConnection.Open();
            Console.WriteLine("Connection succesfful!");
        } catch(Exception e) {
            Console.WriteLine(e.StackTrace);
        }
    }

    public static void closeConnection()
    {
        try
        {
            myConnection.Close();
            Console.WriteLine("Connection closed");
        }
        catch (Exception e)
        {
            Console.WriteLine("Problem closing connection");
            Console.WriteLine(e.Message);
        }
    }

    public SqlConnection getDBcon()
    {
        return myConnection;
    }

    public static DbConnection getInstance()
    {
        if (instance == null)
        {
            instance = new DbConnection();
        }
        return instance;
    }
}

Answer 1

伙计，那个DbConnection课程太糟糕了。把它扔掉。在VB中，作者将使用错误恢复下一步。

主要的问题是错误被丢弃了。节目继续处于糟糕的状态。

下一个问题是静态(全局共享)连接对象。这不是线程安全的，如果连接中断(网络问题)，它就会永久中断。

.NET有连接池。这门课你不需要。也许你可以给自己写个小助手来打开连接：

static SqlConnection CreateConnection() {
 ...
}

using (var conn = CreateConnection()) {
}

还能有多简单？