郑德才博客

在开发过程中，如果没有MSSql数据库，那么使用Access数据库时，需要对Access数据库数据进行操作，对Access数据操作和MSSql差不多，所以可以建立工具类，方便使用，需注意：在添加和修改数据时，有时候回出现无法操作，可能是数据表中的"关键词"，需要在字段的两边加上[]：

public class SQLAccess
{
    /// <summary>
    /// 返回多行多列查询值，无效则返回 
    /// </summary>
    /// <param name="connectingStr">数据库连接串</param>
    /// <param name="sqlCommand">数据库查询串</param>
    public static DataTable GetDBToDataTable(string connectingStr, string sqlCommand)
    {
        OleDbConnection conn = new OleDbConnection(connectingStr);
        try
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        OleDbDataAdapter myDa = new OleDbDataAdapter(sqlCommand, conn);
        try
        {
            DataTable dt = new DataTable();
            myDa.Fill(dt);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            myDa.Dispose();
            return dt;
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        finally
        {
            try
            {
                myDa.Dispose();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            catch { }
        }
    }

    /// <summary>
    /// 返回单行单列查询值，无效则返回
    /// </summary>
    /// <param name="connectingStr">数据库连接串</param>
    /// <param name="sqlCommand">数据库查询串</param>
    public static object GetDBValue_3(string connectingStr, string sql)
    {
        OleDbConnection conn = new OleDbConnection(connectingStr);
        try
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        try
        {
            object rs = cmd.ExecuteScalar();
            if (conn.State == ConnectionState.Open)
                conn.Close();
            cmd.Dispose();
            return rs;
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        finally
        {
            try
            {
                cmd.Dispose();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            catch { }
        }
    }

    /// <summary>
    /// 执行SQL，返回受影响的行数，错误则返回 -1
    /// </summary>
    /// <param name="connectingStr">数据库连接串</param>
    /// <param name="sqlCommand">数据库查询串</param>
    public static int ExeSqlCommand(string connectingStr, string sql)
    {
        OleDbConnection conn = new OleDbConnection(connectingStr);
        try
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        try
        { 
            int rs = cmd.ExecuteNonQuery();
            if (conn.State == ConnectionState.Open)
                conn.Close();
            cmd.Dispose();
            return rs;
        }
        catch (System.Exception ex)
        {
            throw (ex);
        }
        finally
        {
            try
            {
                cmd.Dispose();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            catch { }
        }
    }
}