Winfrom中连接数据库
谁能提供一个完整的Winfrom中连接数据库的类,先在这里谢谢了。。。
[解决办法]
百度 搜索 SqlHeler.cs
[解决办法]
namespace ESWS
{
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using ESWS;
internal class MySqlHelper : IDisposable
{
private MySqlCommand mySqlCommand;
private MySqlConnection mySqlConnection;
private MySqlDataAdapter mySqlDataAdapter;
private MySqlTransaction myTransaction;
protected virtual void AddParameters(MySqlCommand command, MySqlParameter[] parameters)
{
foreach (MySqlParameter parameter in parameters)
{
command.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
}
}
internal void BegionTransaction()
{
if (this.DBConnection.State != ConnectionState.Open)
{
this.DBConnection.Open();
}
this.myTransaction = this.DBConnection.BeginTransaction();
}
internal void Commit()
{
this.DBTransaction.Commit();
}
protected MySqlCommand CreateCommand(string commandText)
{
return this.CreateCommand(commandText, CommandType.Text);
}
protected MySqlCommand CreateCommand(string commandText, CommandType commandType)
{
return this.CreateCommand(commandText, commandType, null);
}
protected MySqlCommand CreateCommand(string commandText, MySqlParameter[] parameters)
{
return this.CreateCommand(commandText, CommandType.Text, parameters);
}
protected MySqlCommand CreateCommand(string commandText, CommandType commandType, MySqlParameter[] parameters)
{
return this.CreateCommand(this.DBConnection, commandType, commandText, 30, parameters);
}
protected MySqlCommand CreateCommand(MySqlConnection connection, CommandType commandType, string commandText, int commandTimeout, MySqlParameter[] parameters)
{
MySqlCommand mySqlCommand;
try
{
using (this.mySqlCommand = new MySqlCommand())
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
this.mySqlCommand.Connection = connection;
this.mySqlCommand.CommandType = commandType;
this.mySqlCommand.CommandText = commandText;
if ((parameters != null) && (parameters.Length > 0))
{
this.AddParameters(this.mySqlCommand, parameters);
}
this.mySqlCommand.CommandTimeout = commandTimeout;
mySqlCommand = this.mySqlCommand;
}
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return mySqlCommand;
}
protected MySqlCommand CreateCommand(MySqlTransaction transaction, CommandType commandType, string commandText, int commandTimeout, MySqlParameter[] parameters)
{
MySqlCommand mySqlCommand;
try
{
using (this.mySqlCommand = new MySqlCommand())
{
if (transaction.Connection.State != ConnectionState.Open)
{
transaction.Connection.Open();
}
this.mySqlCommand.Connection = transaction.Connection;
this.mySqlCommand.CommandType = commandType;
this.mySqlCommand.CommandText = commandText;
if ((parameters != null) && (parameters.Length > 0))
{
this.AddParameters(this.mySqlCommand, parameters);
}
this.mySqlCommand.CommandTimeout = commandTimeout;
mySqlCommand = this.mySqlCommand;
}
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return mySqlCommand;
}
protected MySqlCommand CreateCommandTransaction(string commandText)
{
return this.CreateCommandTransaction(commandText, CommandType.Text);
}
protected MySqlCommand CreateCommandTransaction(string commandText, MySqlParameter[] parameters)
{
return this.CreateCommandTransaction(commandText, CommandType.Text, parameters);
}
protected MySqlCommand CreateCommandTransaction(string commandText, CommandType commandType)
{
return this.CreateCommandTransaction(commandText, commandType, null);
}
protected MySqlCommand CreateCommandTransaction(string commandText, CommandType commandType, MySqlParameter[] parameters)
{
return this.CreateCommand(this.DBTransaction, commandType, commandText, 30, parameters);
}
public void Dispose()
{
if (this.DBConnection.State != ConnectionState.Closed)
{
this.DBConnection.Dispose();
}
GC.Collect();
}
internal int ExecuteNonQuery(string commandText)
{
int num;
try
{
num = this.CreateCommand(commandText).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQuery(string commandText, MySqlParameter[] parameters)
{
int num;
try
{
num = this.CreateCommand(commandText, parameters).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryProcedure(string commandText)
{
int num;
try
{
num = this.CreateCommand(commandText, CommandType.StoredProcedure).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryProcedure(string commandText, MySqlParameter[] parameters)
{
int num;
try
{
num = this.CreateCommand(commandText, CommandType.StoredProcedure, parameters).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryProcedureTransaction(string commandText)
{
int num;
try
{
num = this.CreateCommandTransaction(commandText, CommandType.StoredProcedure).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryProcedureTransaction(string commandText, MySqlParameter[] parameters)
{
int num;
try
{
num = this.CreateCommandTransaction(commandText, CommandType.StoredProcedure, parameters).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryTransaction(string commandText)
{
int num;
try
{
num = this.CreateCommandTransaction(commandText).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal int ExecuteNonQueryTransaction(string commandText, MySqlParameter[] parameters)
{
int num;
try
{
num = this.CreateCommandTransaction(commandText, parameters).ExecuteNonQuery();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return num;
}
internal MySqlDataReader ExecuteReader(string commandText)
{
return this.CreateCommand(commandText).ExecuteReader();
}
internal MySqlDataReader ExecuteReader(string commandText, MySqlParameter[] parameters)
{
return this.CreateCommand(commandText, parameters).ExecuteReader();
}
internal MySqlDataReader ExecuteReaderProcedure(string commandText)
{
return this.CreateCommand(commandText, CommandType.StoredProcedure).ExecuteReader();
}
internal MySqlDataReader ExecuteReaderProcedure(string commandText, MySqlParameter[] parameters)
{
return this.CreateCommand(commandText, CommandType.StoredProcedure, parameters).ExecuteReader();
}
internal object ExecuteScalar(string commandText)
{
object obj2;
try
{
obj2 = this.CreateCommand(commandText).ExecuteScalar();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return obj2;
}
internal object ExecuteScalar(string commandText, MySqlParameter[] parameters)
{
object obj2;
try
{
obj2 = this.CreateCommand(commandText, parameters).ExecuteScalar();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return obj2;
}
internal object ExecuteScalarProcedure(string commandText)
{
object obj2;
try
{
obj2 = this.CreateCommand(commandText, CommandType.StoredProcedure).ExecuteScalar();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return obj2;
}
internal object ExecuteScalarProcedure(string commandText, MySqlParameter[] parameters)
{
object obj2;
try
{
obj2 = this.CreateCommand(commandText, CommandType.StoredProcedure, parameters).ExecuteScalar();
}
catch (MySqlException exception)
{
throw new CustomException(exception.Message, exception);
}
return obj2;
}
internal void FillDataSet(DataSet ds, string commandText)
{
MySqlCommand selectCommand = this.CreateCommand(commandText);
using (this.mySqlDataAdapter = new MySqlDataAdapter(selectCommand))
{
this.mySqlDataAdapter.Fill(ds);
}
}
internal void FillDataSet(DataSet ds, string commandText, MySqlParameter[] parameters)
{
MySqlCommand selectCommand = this.CreateCommand(commandText, parameters);
using (this.mySqlDataAdapter = new MySqlDataAdapter(selectCommand))
{
this.mySqlDataAdapter.Fill(ds);
}
}
internal void FillDataSetProcedure(DataSet ds, string commandText)
{
MySqlCommand selectCommand = this.CreateCommand(commandText, CommandType.StoredProcedure);
using (this.mySqlDataAdapter = new MySqlDataAdapter(selectCommand))
{
this.mySqlDataAdapter.Fill(ds);
}
}
internal void FillDataSetProcedure(DataSet ds, string commandText, MySqlParameter[] parameters)
{
MySqlCommand selectCommand = this.CreateCommand(commandText, CommandType.StoredProcedure, parameters);
using (this.mySqlDataAdapter = new MySqlDataAdapter(selectCommand))
{
this.mySqlDataAdapter.Fill(ds);
}
}
internal T GetEntityByDataReader<T>(MySqlDataReader reader) where T: new()
{
try
{
if ((reader == null)
[解决办法]
!reader.HasRows)
{
return default(T);
}
T t = new T();
if (reader.Read())
{
foreach (PropertyInfo info in typeof(T).GetProperties())
{
object[] customAttributes = info.GetCustomAttributes(typeof(ColumnAttribute), true);
if (customAttributes.Length == 0)
continue;
string columnName = (customAttributes[0] as ColumnAttribute).ColumnName;
if (info.CanWrite)
{
info.SetValue(t, info.PropertyType.IsGenericType ? null : this.TypeParse(reader[columnName]), null);
}
}
}
return t;
}
catch (Exception exception)
{
throw new CustomException(exception.Message, exception);
}
finally
{
if (!reader.IsClosed && (reader != null))
{
reader.Close();
}
}
}
internal List<T> GetListByDataReader<T>(MySqlDataReader reader) where T: new()
{
try
{
List<T> list = new List<T>();
if ((reader == null)
[解决办法]
!reader.HasRows)
return list;
PropertyInfo[] properties = typeof(T).GetProperties();
while (reader.Read())
{
T t = new T();
foreach (PropertyInfo info in properties)
{
object[] customAttributes = info.GetCustomAttributes(typeof(ColumnAttribute), true);
if (customAttributes.Length == 0)
continue;
string columnName = (customAttributes[0] as ColumnAttribute).ColumnName;
if (info.CanWrite)
{
info.SetValue(t, info.PropertyType.IsGenericType ? null : this.TypeParse(reader[columnName]), null);
}
}
list.Add(t);
}
return list;
}
catch (Exception exception)
{
throw new CustomException(exception.Message, exception);
}
finally
{
if (!reader.IsClosed && (reader != null))
{
reader.Close();
}
}
}
internal void RollBack()
{
this.DBTransaction.Rollback();
}
private object TypeParse(object data)
{
if (!(data is DBNull))
{
return data;
}
switch (Type.GetTypeCode(data.GetType()))
{
case TypeCode.Boolean:
return this.TypeParseDBNull<bool>();
case TypeCode.Char:
return this.TypeParseDBNull<char>();
case TypeCode.SByte:
return this.TypeParseDBNull<sbyte>();
case TypeCode.Byte:
return this.TypeParseDBNull<byte>();
case TypeCode.Int16:
return this.TypeParseDBNull<short>();
case TypeCode.UInt16:
return this.TypeParseDBNull<ushort>();
case TypeCode.Int32:
return this.TypeParseDBNull<int>();
case TypeCode.UInt32:
return this.TypeParseDBNull<uint>();
case TypeCode.Int64:
return this.TypeParseDBNull<long>();
case TypeCode.UInt64:
return this.TypeParseDBNull<ulong>();
case TypeCode.Single:
return this.TypeParseDBNull<float>();
case TypeCode.Double:
return this.TypeParseDBNull<double>();
case TypeCode.Decimal:
return this.TypeParseDBNull<decimal>();
case TypeCode.DateTime:
return this.TypeParseDBNull<DateTime>();
case TypeCode.String:
return this.TypeParseDBNull<string>();
}
return this.TypeParseDBNull<object>();
}
private T TypeParseDBNull<T>()
{
return default(T);
}
internal MySqlConnection DBConnection
{
get
{
if (this.mySqlConnection == null)
{
this.mySqlConnection = new MySqlConnection(Global.MySqlConnectionStrings);
}
return this.mySqlConnection;
}
set
{
this.mySqlConnection = value;
}
}
internal MySqlTransaction DBTransaction
{
get
{
return this.myTransaction;
}
set
{
this.myTransaction = value;
}
}
}
}