mysql 数据库操作通用类_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

{

///

/// 针对SQL Server数据库操作的通用类

///

public class SqlDbHelper

{

private string connectionString;

///

/// 设置数据库连接字符串

///

public string ConnectionString

{

set { connectionString = value; }

}

///

/// 构造函数

///

public SqlDbHelper()

: this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString)

{

}

///

/// 构造函数

///

/// 数据库连接字符串

public SqlDbHelper(string connectionString)

{

this.connectionString = connectionString;

}

///

/// 执行一个查询，并返回结果集

///

/// 要执行的查询SQL文本命令

/// 返回查询结果集

public DataTable ExecuteDataTable(string sql)

{

return ExecuteDataTable(sql, CommandType.Text, null);

}

///

/// 执行一个查询,并返回查询结果

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

/// 返回查询结果集

public DataTable ExecuteDataTable(string sql, CommandType commandType)

{

return ExecuteDataTable(sql, commandType, null);

}

///

/// 执行一个查询,并返回查询结果

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

/// Transact-SQL 语句或存储过程的参数数组

///

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;

}

///

///

///

/// 要执行的查询SQL文本命令

///

public SqlDataReader ExecuteReader(string sql)

{

return ExecuteReader(sql, CommandType.Text, null);

}

///

///

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

///

public SqlDataReader ExecuteReader(string sql, CommandType commandType)

{

return ExecuteReader(sql, commandType, null);

}

///

///

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

/// Transact-SQL 语句或存储过程的参数数组

///

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);

}

///

///

///

/// 要执行的查询SQL文本命令

///

public Object ExecuteScalar(string sql)

{

return ExecuteScalar(sql, CommandType.Text, null);

}

///

///

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

///

public Object ExecuteScalar(string sql, CommandType commandType)

{

return ExecuteScalar(sql, commandType, null);

}

///

///

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

/// Transact-SQL 语句或存储过程的参数数组

///

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;//返回查询结果的第一行第一列，忽略其它行和列

}

///

/// 对数据库执行增删改操作

///

/// 要执行的查询SQL文本命令

///

public int ExecuteNonQuery(string sql)

{

return ExecuteNonQuery(sql, CommandType.Text, null);

}

///

/// 对数据库执行增删改操作

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

///

public int ExecuteNonQuery(string sql, CommandType commandType)

{

return ExecuteNonQuery(sql, commandType, null);

}

///

/// 对数据库执行增删改操作

///

/// 要执行的SQL语句

/// 要执行的查询语句的类型，如存储过程或者SQL文本命令

/// Transact-SQL 语句或存储过程的参数数组

///

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;//返回执行增删改操作之后，数据库中受影响的行数

}

///

/// 返回当前连接的数据库中所有由用户创建的数据库

///

///

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;

///

/// 构造函数

///

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();

}

///

/// 构造函数

///

/// 数据库路径

public AccessHelper(string db_path)

{

conn_str ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ db_path + "'";

InitDB();

}

///

/// 转换数据格式

///

/// 数据源

/// 数据列表

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;

}

///

/// 创建并初始化数据列表

///

/// 列的个数

/// 数据列表

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;

}

///

/// 从数据库里面获取数据

///

/// 查询语句

/// 数据列表

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;

}

///

/// 执行sql语句

///

/// sql语句

/// 返回结果

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.");

}

}

}