c 操作mysql通用类_C#数据库操作通用类

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data.Common;usingSystem.Data;usingSystem.Reflection;namespaceDBHelpers

{publicclassDBHelper

{privatestaticDBParaFactory dbParaFactory;publicstaticDBParaFactory DbParaFactory

{get{if(DBHelper.dbParaFactory==null)

{returnnewSQLiteDBParaFactory();

}returnDBHelper.dbParaFactory;

}set{ DBHelper.dbParaFactory=value; }

}privatestaticDbConnection _Connection;/publicstaticDbConnection Connection

{get{if(_Connection==null)

{

_Connection=DbParaFactory.GetDbConnection();

_Connection.Open();

}elseif(_Connection.State==ConnectionState.Closed)

{

_Connection.Open();

}elseif(_Connection.State==ConnectionState.Broken)

{

_Connection.Close();

_Connection.Open();

}return_Connection;

}

}publicstaticboolExists(stringsafeSql)

{return(ExecuteCommand(safeSql)>0);

}publicstaticboolExists(stringSql,paramsDbParameter[] values)

{return(ExecuteCommand(Sql, values)>0);

}//执行并返回影响行数/publicstaticintExecuteCommand(stringsafeSql)

{

DbCommand cmd=cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=safeSql;intresult=cmd.ExecuteNonQuery();returnresult;

}//执行并返回影响行数publicstaticintExecuteCommand(stringsql,paramsDbParameter[] values)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sql;

cmd.Parameters.AddRange(values);returncmd.ExecuteNonQuery();

}//执行并返回执行结果中的第一列/publicstaticobjectGetScalar(stringsafeSql)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=safeSql;objectresult=cmd.ExecuteScalar();returnresult;

}//执行并返回执行结果中的第一列publicstaticobjectGetScalar(stringsql,paramsDbParameter[] values)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.Parameters.AddRange(values);

cmd.CommandText=sql;objectresult=cmd.ExecuteScalar();returnresult;

}//根据sql语句获得datareader/publicstaticDbDataReader GetReader(stringsafeSql)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=safeSql;

DbDataReader reader=cmd.ExecuteReader();returnreader;

}//根据sql语句获得datareaderpublicstaticDbDataReader GetReader(stringsql,paramsDbParameter[] values)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sql;

cmd.Parameters.AddRange(values);

DbDataReader reader=cmd.ExecuteReader();returnreader;

}//根据sql语句获得DataTable/publicstaticDataSet GetDataSet(stringsafeSql)

{

DataSet ds=newDataSet();

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=safeSql;

DbDataAdapter da=DbParaFactory.GetDbDataAdapter();

da.SelectCommand=cmd;

da.Fill(ds);returnds;

}//根据sql语句获得DataTablepublicstaticDataSet GetDataSet(stringsql,paramsDbParameter[] values)

{

DataSet ds=newDataSet();

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sql;

cmd.Parameters.AddRange(values);

DbDataAdapter da=DbParaFactory.GetDbDataAdapter();

da.SelectCommand=cmd;

da.Fill(ds);returnds;

}publicstaticDataAdapter GetDataAdapter(stringsafeSql)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=safeSql;

DbDataAdapter da=DbParaFactory.GetDbDataAdapter();

da.SelectCommand=cmd;returnda;

}//根据sql语句获得DataTablepublicstaticDataAdapter GetDataAdapter(stringsql,paramsDbParameter[] values)

{

DbCommand cmd=DbParaFactory.GetDbCommand();

cmd.Connection=Connection;

cmd.CommandType=CommandType.Text;

cmd.CommandText=sql;

cmd.Parameters.AddRange(values);

DbDataAdapter da=DbParaFactory.GetDbDataAdapter();

da.SelectCommand=cmd;returnda;

}//DataSet装换为泛型集合/DataSet///待转换数据表索引///publicstaticIListDataSetToIList(DataSet ds,inttableIndex)

{if(ds==null||ds.Tables.Count<0)returnnull;if(tableIndex>ds.Tables.Count-1)returnnull;if(tableIndex<0)

tableIndex=0;

DataTable dt=ds.Tables[tableIndex];//返回值初始化IListresult=newList();for(intj=0; j

{

T _t=(T)Activator.CreateInstance(typeof(T));

PropertyInfo[] propertys=_t.GetType().GetProperties();foreach(PropertyInfo piinpropertys)

{for(inti=0; i

{//属性与字段名称一致的进行赋值if(pi.Name.Equals(dt.Columns[i].ColumnName))

{//数据库NULL值单独处理if(dt.Rows[j][i]!=DBNull.Value)

pi.SetValue(_t, dt.Rows[j][i],null);elsepi.SetValue(_t,null,null);break;

}

}

}

result.Add(_t);

}returnresult;

}//DataSet装换为泛型集合/DataSet///待转换数据表名称//2008-08-01 22:47 HPDV2806publicstaticIListDataSetToIList(DataSet ds,stringtableName)

{int_TableIndex=0;if(ds==null||ds.Tables.Count<0)returnnull;if(string.IsNullOrEmpty(tableName))returnnull;for(inti=0; i

{//获取Table名称在Tables集合中的索引值if(ds.Tables[i].TableName.Equals(tableName))

{

_TableIndex=i;break;

}

}returnDataSetToIList(ds, _TableIndex);

}

}

}