C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库
来源:互联网 发布:淘宝买家后台操作流程 编辑:程序博客网 时间:2024/05/16 01:14
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.Common;
- namespace DbHelper
- {
- /// <summary>
- /// 通用数据库访问类,封装了对数据库的常见操作
- ///</summary>
- public sealed class DbUtility
- {
- public string ConnectionString { get; set; }
- private DbProviderFactory providerFactory;
- /// <summary>
- /// 构造函数
- /// </summary>
- /// <param name="connectionString">数据库连接字符串</param>
- /// <param name="providerType">数据库类型枚举,参见<paramref name="providerType"/></param>
- public DbUtility(string connectionString, DbProviderType providerType)
- {
- ConnectionString = connectionString;
- providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
- if (providerFactory == null)
- {
- throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
- }
- }
- /// <summary>
- /// 对数据库执行增删改操作,返回受影响的行数。
- /// </summary>
- /// <param name="sql">要执行的增删改的SQL语句</param>
- /// <param name="parameters">执行增删改语句所需要的参数</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string sql, IList<DbParameter> parameters)
- {
- return ExecuteNonQuery(sql, parameters, CommandType.Text);
- }
- /// <summary>
- /// 对数据库执行增删改操作,返回受影响的行数。
- /// </summary>
- /// <param name="sql">要执行的增删改的SQL语句</param>
- /// <param name="parameters">执行增删改语句所需要的参数</param>
- /// <param name="commandType">执行的SQL语句的类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string sql, IList<DbParameter> parameters, CommandType commandType)
- {
- using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
- {
- command.Connection.Open();
- int affectedRows = command.ExecuteNonQuery();
- command.Connection.Close();
- return affectedRows;
- }
- }
- /// <summary>
- /// 执行一个查询语句,返回一个关联的DataReader实例
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <returns></returns>
- public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters)
- {
- return ExecuteReader(sql, parameters, CommandType.Text);
- }
- /// <summary>
- /// 执行一个查询语句,返回一个关联的DataReader实例
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <param name="commandType">执行的SQL语句的类型</param>
- /// <returns></returns>
- public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters, CommandType commandType)
- {
- DbCommand command = CreateDbCommand(sql, parameters, commandType);
- command.Connection.Open();
- return command.ExecuteReader(CommandBehavior.CloseConnection);
- }
- /// <summary>
- /// 执行一个查询语句,返回一个包含查询结果的DataTable
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <returns></returns>
- public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters)
- {
- return ExecuteDataTable(sql, parameters, CommandType.Text);
- }
- /// <summary>
- /// 执行一个查询语句,返回一个包含查询结果的DataTable
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <param name="commandType">执行的SQL语句的类型</param>
- /// <returns></returns>
- public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters, CommandType commandType)
- {
- using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
- {
- using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
- {
- adapter.SelectCommand = command;
- DataTable data = new DataTable();
- adapter.Fill(data);
- return data;
- }
- }
- }
- /// <summary>
- /// 执行一个查询语句,返回查询结果的第一行第一列
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <returns></returns>
- public Object ExecuteScalar(string sql, IList<DbParameter> parameters)
- {
- return ExecuteScalar(sql, parameters, CommandType.Text);
- }
- /// <summary>
- /// 执行一个查询语句,返回查询结果的第一行第一列
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <param name="commandType">执行的SQL语句的类型</param>
- /// <returns></returns>
- public Object ExecuteScalar(string sql, IList<DbParameter> parameters, CommandType commandType)
- {
- using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
- {
- command.Connection.Open();
- object result = command.ExecuteScalar();
- command.Connection.Close();
- return result;
- }
- }
- public DbParameter CreateDbParameter(string name, object value)
- {
- return CreateDbParameter(name, ParameterDirection.Input, value);
- }
- public DbParameter CreateDbParameter(string name, ParameterDirection parameterDirection, object value)
- {
- DbParameter parameter = providerFactory.CreateParameter();
- parameter.ParameterName = name;
- parameter.Value = value;
- parameter.Direction = parameterDirection;
- return parameter;
- }
- /// <summary>
- /// 创建一个DbCommand对象
- /// </summary>
- /// <param name="sql">要执行的查询语句</param>
- /// <param name="parameters">执行SQL查询语句所需要的参数</param>
- /// <param name="commandType">执行的SQL语句的类型</param>
- /// <returns></returns>
- private DbCommand CreateDbCommand(string sql, IList<DbParameter> parameters, CommandType commandType)
- {
- DbConnection connection = providerFactory.CreateConnection();
- DbCommand command = providerFactory.CreateCommand();
- connection.ConnectionString = ConnectionString;
- command.CommandText = sql;
- command.CommandType = commandType;
- command.Connection = connection;
- if (!(parameters == null || parameters.Count == 0))
- {
- foreach (DbParameter parameter in parameters)
- {
- command.Parameters.Add(parameter);
- }
- }
- return command;
- }
- }
- /// <summary>
- /// 数据库类型枚举
- /// </summary>
- public enum DbProviderType : byte
- {
- SqlServer,
- MySql,
- SQLite,
- Oracle,
- ODBC,
- OleDb,
- Firebird,
- PostgreSql,
- DB2,
- Informix,
- SqlServerCe
- }
- /// <summary>
- /// DbProviderFactory工厂类
- /// </summary>
- public class ProviderFactory
- {
- private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
- private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
- static ProviderFactory()
- {
- //加载已知的数据库访问类的程序集
- providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
- providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb");
- providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC");
- providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client");
- providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
- providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
- providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
- providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql");
- providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
- providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix");
- providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
- }
- /// <summary>
- /// 获取指定数据库类型对应的程序集名称
- /// </summary>
- /// <param name="providerType">数据库类型枚举</param>
- /// <returns></returns>
- public static string GetProviderInvariantName(DbProviderType providerType)
- {
- return providerInvariantNames[providerType];
- }
- /// <summary>
- /// 获取指定类型的数据库对应的DbProviderFactory
- /// </summary>
- /// <param name="providerType">数据库类型枚举</param>
- /// <returns></returns>
- public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
- {
- //如果还没有加载,则加载该DbProviderFactory
- if (!providerFactoies.ContainsKey(providerType))
- {
- providerFactoies.Add(providerType, ImportDbProviderFactory(providerType));
- }
- return providerFactoies[providerType];
- }
- /// <summary>
- /// 加载指定数据库类型的DbProviderFactory
- /// </summary>
- /// <param name="providerType">数据库类型枚举</param>
- /// <returns></returns>
- private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
- {
- string providerName = providerInvariantNames[providerType];
- DbProviderFactory factory = null;
- try
- {
- //从全局程序集中查找
- factory = DbProviderFactories.GetFactory(providerName);
- }
- catch (ArgumentException e)
- {
- factory = null;
- }
- return factory;
- }
- }
- }
- //使用示例 SQLite
- string connectionString = @"Data Source=D:\VS2008\NetworkTime\CrawlApplication\CrawlApplication.db3";
- string sql = "SELECT * FROM Weibo_Media order by Id desc limit 0,20000";
- DbUtility db = new DbUtility(connectionString, DbProviderType.SQLite);
- DataTable data = db.ExecuteDataTable(sql, null);
- DbDataReader reader = db.ExecuteReader(sql, null);
- reader.Close();
- //使用示例 MySql
- string connectionString = @"Server=localhost;Database=crawldb;Uid=root;Pwd=root;Port=3306;";
- string sql = "SELECT * FROM Weibo_Media order by Id desc limit 0,20000";
- DbUtility db = new DbUtility(connectionString, DbProviderType.MySql);
- DataTable data = db.ExecuteDataTable(sql, null);
- DbDataReader reader = db.ExecuteReader(sql, null);
- reader.Close();
- //使用示例 Execl
- string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/XLS/车型.xls") + ";Extended Properties=Excel 8.0;";
- string sql = "SELECT * FROM [Sheet1$]";
- DbUtility db = new DbUtility(connectionString, DbProviderType.OleDb);
- DataTable data = db.ExecuteDataTable(sql, null);
0 0
- C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库
- C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库
- C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库
- C#操作MSSQL数据库通用类
- 【备用】非常不错的ASP操作数据库类,支持多数据库MSSQL,ACCESS,ORACLE,MYSQL等
- C# Access数据库通用类
- MSSQL数据库C#操作类
- 一个通用数据库操作组件DBUtil(c#)、支持SqlServer、Oracle、Mysql、Access、SQLITE
- 操作Access的通用数据库类
- C# 通用数据库类操作
- C#数据库操作通用类
- C# ACCESS数据库操作类
- C# ACCESS数据库操作类
- C# Access数据库操作类
- C#---数据库访问通用类、Access数据库操作类、mysql类
- C# 操作MSSQL数据库类(winform)
- C# 操作MSSQL数据库类(winform)
- MFC中使用ADO操作各类数据库的封装类,包括MySql、Access、Oracle、MSSql
- 线性素数筛法模板
- 升级xcode7之后,真机调试出现BaiduMapAPI.framework/BaiduMapAPI(BMAddrList.o)' does not contain bitcode的问题
- jquery 中的hover(),mouseleave()&mouseenter()和mouseover()&mouseout()的区别
- 归档以及反归档
- LeetCode---Delete Node in a Linked List
- C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库
- 解决idea工具下tomcat中文乱码问题
- SHOPING 购物(学了老男孩视频写的)
- Android中将布局文件/View添加至窗口过程分析 ---- 从setContentView()谈起
- zoj 2539 Energy Minimization 【构建最小割模型求解 函数最值】【基础题】
- Struts 2 Web开发学习实录--总结
- 每个程序员都应该知道的8个Linux命令
- Unity3D实现3D立体游戏原理及过程
- C++文件输入与输出