C#---数据库访问通用类、Access数据库操作类、mysql类 .
//C# 数据库访问通用类 (ADO.NET)
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace XXX
{
/// <summary>
/// 针对SQL Server数据库操作的通用类
/// </summary>
public class SqlDbHelper
{
private string connectionString;
/// <summary>
/// 设置数据库连接字符串
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
/// <summary>
/// 构造函数
/// </summary>
public SqlDbHelper()
: this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString)
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
public SqlDbHelper(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// 执行一个查询，并返回结果集
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns>返回查询结果集</returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <returns>返回查询结果集</returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType, null);
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();//实例化DataTable，用于装载查询结果集
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);//填充DataTable
}
}
return data;
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
{
return ExecuteReader(sql, commandType, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
//CommandBehavior.CloseConnection参数指示关闭Reader对象时关闭与其关联的Connection对象
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType)
{
return ExecuteScalar(sql, commandType, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
{
object result = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打开数据库连接
result = command.ExecuteScalar();
}
}
return result;//返回查询结果的第一行第一列，忽略其它行和列
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType)
{
return ExecuteNonQuery(sql, commandType, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打开数据库连接
count = command.ExecuteNonQuery();
}
}
return count;//返回执行增删改操作之后，数据库中受影响的行数
}
/// <summary>
/// 返回当前连接的数据库中所有由用户创建的数据库
/// </summary>
/// <returns></returns>
public DataTable GetTables()
{
DataTable data = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();//打开数据库连接
data = connection.GetSchema("Tables");
}
return data;
}
}
}
//Access数据库-C# 操作类 代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace XXX
{
class AccessHelper
{
private string conn_str = null;
private OleDbConnection ole_connection = null;
private OleDbCommand ole_command = null;
private OleDbDataReader ole_reader = null;
private DataTable dt = null;
/// <summary>
/// 构造函数
/// </summary>
public AccessHelper()
{
conn_str =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\我的文档\Visual Studio 2008\Projects\AccessOperator\AccessOperator\bin\Debug\myDb.mdb'";
InitDB();
}
private void InitDB()
{
ole_connection =new OleDbConnection(conn_str);//创建实例
ole_command =new OleDbCommand();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="db_path">数据库路径</param>
public AccessHelper(string db_path)
{
conn_str ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ db_path + "'";
InitDB();
}
/// <summary>
/// 转换数据格式
/// </summary>
/// <param name="reader">数据源</param>
/// <returns>数据列表</returns>
privateDataTable ConvertOleDbReaderToDataTable(refOleDbDataReader reader)
{
DataTable dt_tmp =null;
DataRow dr =null;
int data_column_count = 0;
int i = 0;
data_column_count = reader.FieldCount;
dt_tmp = BuildAndInitDataTable(data_column_count);
if(dt_tmp == null)
{
return null;
}
while(reader.Read())
{
dr = dt_tmp.NewRow();
for(i = 0; i < data_column_count; ++i)
{
dr[i] = reader[i];
}
dt_tmp.Rows.Add(dr);
}
return dt_tmp;
}
/// <summary>
/// 创建并初始化数据列表
/// </summary>
/// <param name="Field_Count">列的个数</param>
/// <returns>数据列表</returns>
private DataTable BuildAndInitDataTable(int Field_Count)
{
DataTable dt_tmp =null;
DataColumn dc =null;
int i = 0;
if(Field_Count <= 0)
{
return null;
}
dt_tmp =new DataTable();
for(i = 0; i < Field_Count; ++i)
{
dc =new DataColumn(i.ToString());
dt_tmp.Columns.Add(dc);
}
return dt_tmp;
}
/// <summary>
/// 从数据库里面获取数据
/// </summary>
/// <param name="strSql">查询语句</param>
/// <returns>数据列表</returns>
publicDataTable GetDataTableFromDB(string strSql)
{
if(conn_str == null)
{
return null;
}
try
{
ole_connection.Open();//打开连接
if(ole_connection.State == ConnectionState.Closed)
{
return null;
}
ole_command.CommandText = strSql;
ole_command.Connection = ole_connection;
ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);
dt = ConvertOleDbReaderToDataTable(ref ole_reader);
ole_reader.Close();
ole_reader.Dispose();
}
catch(System.Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if(ole_connection.State != ConnectionState.Closed)
{
ole_connection.Close();
}
}
return dt;
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回结果</returns>
public int ExcuteSql(stringstrSql)
{
int nResult = 0;
try
{
ole_connection.Open();//打开数据库连接
if(ole_connection.State == ConnectionState.Closed)
{
return nResult;
}
ole_command.Connection = ole_connection;
ole_command.CommandText = strSql;
nResult = ole_command.ExecuteNonQuery();
}
catch(System.Exception e)
{
Console.WriteLine(e.ToString());
return nResult;
}
finally
{
if(ole_connection.State != ConnectionState.Closed)
{
ole_connection.Close();
}
}
return nResult;
}
staticvoid Main(string[] args)
{
AccessHelper Helper =new AccessHelper();
DataTable dt = Helper.GetDataTableFromDB("select * from test");
foreach(DataRow dr in dt.Rows)
{
Console.WriteLine(dr[0].ToString()+" "+dr[1].ToString());
}
Console.WriteLine(Helper.ExcuteSql("insert into test(test) values ('hello')"));
}
}
}
//C# mysql 类
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Text.RegularExpressions;///
using MySql.Data.MySqlClient;
namespace XXX
{
class MysqlConnection
{
MySqlConnection mysqlConnection;
DataSet dataSet;
string IP = null;
string UserName = "root";
string Password = "root";
string Database = null;
public MysqlConnection()
{
try
{
mysqlConnection = new MySqlConnection("datasource=20.0.0.20;username=root;password=root;database=sysinfo;charset=gb2312");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public MysqlConnection(string IP,string UserName,string Password,string Database)
{
try
{
string connectionString = "datasource=" + IP + ";username="+UserName+";password="+Password+";database=" + Database+ ";charset=gb2312" ;
mysqlConnection = new MySqlConnection(connectionString);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public string MysqlInfo()
{
string mysqlInfo = null;
try
{
mysqlConnection.Open();
mysqlInfo += "Connection Opened." + Environment.NewLine;
mysqlInfo += "Connection String:" + mysqlConnection.ConnectionString.ToString() + Environment.NewLine;
mysqlInfo += "Database:" + mysqlConnection.Database.ToString() + Environment.NewLine;
mysqlInfo += "Connection ServerVersion:" + mysqlConnection.ServerVersion.ToString() + Environment.NewLine;
mysqlInfo += "Connection State:" + mysqlConnection.State.ToString() + Environment.NewLine;
}
catch (MySqlException ex)
{
Console.WriteLine("MySqlException Error:" + ex.ToString());
}
finally
{
mysqlConnection.Close();
}
return mysqlInfo;
}
public int MysqlCommand(string MysqlCommand)
{
try
{
mysqlConnection.Open();
Console.WriteLine("MysqlConnection Opened.");
MySqlCommand mysqlCommand = new MySqlCommand(MysqlCommand, mysqlConnection);
return mysqlCommand.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Console.WriteLine("MySqlException Error:" + ex.ToString());
if (Regex.IsMatch(ex.ToString(), ""))
{
MessageBox.Show("数据库已经存在唯一键值");
}
}
finally
{
mysqlConnection.Close();
}
return -1;
}
public DataView MysqlDataAdapter(string table)
{
DataView dataView = new DataView();
try
{
mysqlConnection.Open();
MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter("Select * from " + table, mysqlConnection);
dataSet = new DataSet();
mysqlDataAdapter.Fill(dataSet, table);
dataView = dataSet.Tables[table].DefaultView;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysqlConnection.Close();
}
return dataView;
}
}//end class
}
//mysql 类2
class MySqlConnect
{
static void Main()
{
string connString = @"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false";
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
Console.WriteLine("Connection opened.");
Console.WriteLine("Connection Properties:");
Console.WriteLine("\tConnection String: {0}", conn.ConnectionString);
Console.WriteLine("\tDatabase: {0}", conn.Database);
Console.WriteLine("\tServerVersion: {0}",
conn.ServerVersion);
Console.WriteLine(
"\tState: {0}",
conn.State);
}
catch (MySqlException e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}
