C#中实现各种数据库的连接 传入SQL语句进行增删改查

一、Access数据库连接

using System.Data.OleDb;
public DataSet Get(string sql)
        {
            OleDbConnection oleDb = new OleDbConnection("连接字符串");
            oleDb.Close();
            DataSet dataSet = new DataSet();
            DataTable datatable = new DataTable();
            oleDb.Open();
            OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter(sql, oleDb);
            dbDataAdapter.Fill(datatable);
            dataSet.Tables.Add(datatable);
            oleDb.Close();
            return dataSet;
        }

二、MySQL数据库连接

using MySql.Data.MySqlClient; 
public static DataSet sql(string sql)
        {
            string connection = "连接字符串";
            DataSet DataSet = new DataSet();
            DataTable DataTable = new DataTable();
            MySqlConnection con = new MySqlConnection(connection);
            con.Open();
            MySqlDataAdapter sqlData = new MySqlDataAdapter(sql, con);
            sqlData.Fill(DataTable);
            DataSet.Tables.Add(DataTable);
            con.Close();
            return DataSet;
        }

三、SQL Sever数据库连接

using System.Data.SqlClient;
 public class DBHelper
    {
        // 数据库连接字符串的定义
        private static String connStr = "连接字符串";
        // 数据库连接对象
        private static SqlConnection conn = null;
        //1 初始化数据库连接
        public static void getCon()
        {
            if (conn == null)
                conn = new SqlConnection(connStr);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            if (conn.State == ConnectionState.Broken)
            {

                conn.Close(); conn.Open();
            }
        }
        //2 执行增删改的方法
        public static bool exeDML(String sql)
        {
            getCon();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int k = cmd.ExecuteNonQuery();
            conn.Close();
            return k > 0;
        }

        //执行查询 获取DataReader
        public static SqlDataReader getDataReader(String sql)
        {
            getCon();
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        //执行聚合函数
        public static object exeFunction(String sql)
        {
            getCon();
            SqlCommand cmd = new SqlCommand(sql, conn);
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }

        //断开时查询
        public static DataTable getTable(String sql)
        {
            getCon();
            //创建适配器对象
            SqlDataAdapter dsp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            dsp.Fill(ds);
            return ds.Tables[0];
        }

        //断开式查询
        public static DataSet getDS(String sql)
        {
            getCon();
            //创建适配器对象
            SqlDataAdapter dsp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            dsp.Fill(ds);
            return ds;
        }

    }

四、Oracle数据库连接

 using System.Data.OracleClient;
    class Oracle
    {
        string connectionString="连接字符串";
        public void ExecuteQuery(string sqlQuery)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                command.CommandText = sqlQuery;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    action(reader);
                }
                reader.Close();
            }
        }
    }

五、Rides数据库连接

//安装StackExchange.Redis包
using StackExchange.Redis;
public class RedisHelper
{
    private static readonly ConfigurationOptions ConfigurationOptions = ConfigurationOptions.Parse("127.0.0.1:6379,password=123456");
    private static readonly object Locker = new object();
    private static ConnectionMultiplexer _redisConn;
    public static ConnectionMultiplexer RedisConn
    {
        get
        {
            if (_redisConn == null)
            {
                // 锁定某一代码块，让同一时间只有一个线程访问该代码块
                lock (Locker)
                {
                    if (_redisConn == null || !_redisConn.IsConnected)
                    {
                        _redisConn = ConnectionMultiplexer.Connect(ConfigurationOptions);
                    }
                }
            }
            return _redisConn;
        }
    }
}
  //创建 ConnectionMultiplexer 对象
  private ConnectionMultiplexer conn = RedisHelper.RedisConn;
//使用
 private void button1_Click(object sender, EventArgs e)
 {
     //默认为第一个表 如果想用第二个表 在括号里填2
     IDatabase db = conn.GetDatabase();
 #region 测试二 批量添加
 //批量添加数据
 KeyValuePair<RedisKey, RedisValue>[] pairs = {
     new KeyValuePair<RedisKey, RedisValue>("Name1","jhobbn"),
     new KeyValuePair<RedisKey, RedisValue>("Name2", "lilbtei"),
     new KeyValuePair<RedisKey, RedisValue>("Name3", "jimgbtr")
     };
 //批量添加key-value对
 db.StringSet(pairs);
 //批量添加key-value对
 RedisKey[] keys = { "Name1", "Name2", "Name3" };
 //批量获取key的值
 RedisValue[] values = db.StringGet(keys);
 //db.StringSet("mylastname", "Shuxian", TimeSpan.FromSeconds(10));//设置时间，20s后过期。
  #endregion
 }

六、SQLite数据库连接

using System.Data.SQLite;  
public static DataSet SQLite(string sql)
  {
      string connectionString = "Data Source=" + Application.StartupPath + @"\根目录下表名;" + "Version=3;";
      // 创建一个新的DataSet来保存查询结果  
      DataSet dataSet = new DataSet();
      // 使用SQLiteConnection打开数据库连接  
      using (SQLiteConnection connection = new SQLiteConnection(connectionString))
      {
          connection.Open();
          // 创建一个新的SQLiteCommand，并将连接和SQL语句传递给它  
          using (SQLiteCommand command = new SQLiteCommand(sql, connection))
          {
              // 使用SQLiteDataAdapter填充DataSet  
              using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
              {
                  // 填充DataSet  
                  adapter.Fill(dataSet);
              }
          }
      }
      return dataSet;
  }