基本数据库操作类（sql&Access）

最新推荐文章于 2025-08-06 16:23:50 发布

MasDn

最新推荐文章于 2025-08-06 16:23:50 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库技术文章标签：数据库 access sql string exception database

本文链接：https://blog.youkuaiyun.com/MasDn/article/details/715074

数据库技术专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个数据库操作类，该类提供了多种方法来处理数据库操作，包括创建数据库、创建表、插入记录、删除记录等。支持SQL Server和Access数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

namespace DB
{
   #region 数据库操作类
/// <summary>
/// 有两个方法一个有返回值DataTable适合查询，一个无返回值适合插入修改等等操作
/// </summary>
/// <pama name="ComStr">操作语句</pama>
/// <pama name="ConStr">数据库路径</pama>
class DataBaseOperate
{
    public int OperateDBReturnInt(string ComStr, OleDbConnection dbconn)
    {
        int i = 0;
        dbconn.Open();
        if (dbconn.State == ConnectionState.Open)
        {
            OleDbCommand dbcomm = dbconn.CreateCommand();
            dbcomm.CommandType = CommandType.Text;
            dbcomm.CommandText = ComStr;
            try
            {
                i = dbcomm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                dbconn.Close();
            }
        }
        else
        {
            throw new Exception("Cannot Connect DataBase!");
        }
        return i;
    }

    public int OperateDBReturnInt(string ComStr, SqlConnection dbconn)
    {
        int i = 0;
        dbconn.Open();
        if (dbconn.State == ConnectionState.Open)
        {
            SqlCommand dbcomm = dbconn.CreateCommand();
            dbcomm.CommandType = CommandType.Text;
            dbcomm.CommandText = ComStr;
            try
            {
                i = dbcomm.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                dbconn.Close();
            }
        }
        else
        {
            throw new Exception("Cannot Connect DataBase!");
        }
        return i;
    }

    public DataTable OperateDBReturnDT(string ComStr, OleDbConnection dbconn)
    {
        DataTable db = new DataTable();
        dbconn.Open();
        if (dbconn.State == ConnectionState.Open)
        {
            OleDbDataAdapter dbadap = new OleDbDataAdapter();
            OleDbCommand dbcomm = dbconn.CreateCommand();
            dbcomm.CommandType = CommandType.Text;
            dbcomm.CommandText = ComStr;
            dbadap.SelectCommand = dbcomm;
            dbadap.InsertCommand = dbcomm;
            dbadap.DeleteCommand = dbcomm;
            dbadap.UpdateCommand = dbcomm;
            try
            {
                dbadap.Fill(db);
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                dbconn.Close();
            }
        }
        else
        {
            throw new Exception("Cannot Connect DataBase!");
        }
        return db;
    }

    public DataTable OperateDBReturnDT(string ComStr, SqlConnection dbconn)
    {
        DataTable db = new DataTable();
        dbconn.Open();
        if (dbconn.State == ConnectionState.Open)
        {
            SqlDataAdapter dbadap = new SqlDataAdapter();
            SqlCommand dbcomm = dbconn.CreateCommand();
            dbcomm.CommandType = CommandType.Text;
            dbcomm.CommandText = ComStr;
            dbadap.SelectCommand = dbcomm;
            dbadap.InsertCommand = dbcomm;
            dbadap.DeleteCommand = dbcomm;
            dbadap.UpdateCommand = dbcomm;
            try
            {
                dbadap.Fill(db);
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                dbconn.Close();
            }
        }
        else
        {
            throw new Exception("Cannot Connect DataBase!");
        }
        return db;
    }

    public void CreateDB(string db, string filepath, SqlConnection dbconn)
    {
        string ComStr = "create database " + db + " on (name='" + db + "_dat' ,filename='" + filepath + "//"
            + db + ".mdf',size=10,maxsize=50,filegrowth=5) log on(name='" + db + "_log' ,filename='" + filepath + "//"
            + db + ".ldf',size=10,maxsize=50,filegrowth=5) ";
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void CreateDB(string db, string filepath, int size, int Max, int filegrowth, SqlConnection dbconn)
    {
        string ComStr = "create database " + db + " on ( name='" + db + "_dat' ,filename='" + filepath + "//"
            + db + ".mdf' , size=" + size + ",maxsize=" + Max + ",filegrowth=" + filegrowth + ") log on (name='"
            + db + "_log ',filename='" + filepath + "//" + db + ".ldf' , size=" + size + ",maxsize=" + Max + ",filegrowth=" + filegrowth + ")";
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void CreateDB(string db, string filepath)
    {
        File.Create(filepath + db + ".mdb");
    }

    public void CreateTable(string TableName, string[] field, string[] fieldType, SqlConnection dbconn)
    {
        string ComStr = "create table " + TableName + " (";
        for (int i = 0; i < field.Length; i++)
        {
            ComStr += "'" + field[i] + "'";
            ComStr += fieldType[i];
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
        }
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void CreateTable(string TableName, string[] field, string[] fieldType, OleDbConnection dbconn)
    {
        string ComStr = "create table " + TableName + " (";
        for (int i = 0; i < field.Length; i++)
        {
            ComStr += "'" + field[i] + "'";
            ComStr += fieldType[i];
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
        }
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void Insert(string Table, string[] field, string[] values, SqlConnection dbconn)
    {
        string ComStr = "insert into " + Table + "( ";
        for (int i = 0; i < field.Length; i++)
        {
            ComStr += "'" + field[i] + "'";
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
            ComStr += "values (";
            ComStr += "'" + values[i] + "'";
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
        }
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void Insert(string Table, string[] field, string[] values, OleDbConnection dbconn)
    {
        string ComStr = "insert into " + Table + "( ";
        for (int i = 0; i < field.Length; i++)
        {
            ComStr += "'" + field[i] + "'";
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
            ComStr += "values (";
            ComStr += "'" + values[i] + "'";
            if (i != field.Length - 1) ComStr += ",";
            else ComStr += " )";
        }
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void Delete(string TableName, string judge, SqlConnection dbconn)
    {
        string ComStr = "delete from " + TableName + " where " + judge;
        OperateDBReturnInt(ComStr, dbconn);
    }

    public void Delete(string TableName, string judge, OleDbConnection dbconn)
    {
        string ComStr = "delete from " + TableName + " where " + judge;
        OperateDBReturnInt(ComStr, dbconn);
    }
}
#endregion
}