lyqConnectionString 要换

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Data;usingSystem.Data.SqlClient;namespaceLyqDAL{///<summary>///自定义访问通用类///</summary>publicclassDBHelper{privatestaticreadonlystringconnectionString=ConfigurationManager.ConnectionStrings["lyqConnectionString"].ConnectionString.ToString();privatestaticreadonlystringproviderName="System.Data.SqlClient";privatestaticSqlConnectionconnection=newSqlConnection(connectionString);///<summary>///GetConnection用于获取连接数据库的connection对象///</summary>///<returns></returns>privatestaticDbConnectionGetConnection(){DbProviderFactory_factory=DbProviderFactories.GetFactory(providerName);DbConnectionconnection=_factory.CreateConnection();connection.ConnectionString=connectionString;returnconnection;}///<summary>///GetCommand获取命令参数command对象///</summary>///<paramname="commandText"></param>///<paramname="commandType"></param>///<paramname="connection"></param>///<returns></returns>privatestaticDbCommandGetCommand(stringcommandText,CommandTypecommandType,DbConnectionconnection){DbCommandcommand=connection.CreateCommand();command.CommandText=commandText;command.CommandType=commandType;returncommand;}///<summary>///读取数据///</summary>///<paramname="sql">sql语句</param>///<paramname="param">参数列表</param>///<returns>DataTable</returns>publicstaticDataTableGetDataTable(stringsql,paramsSqlParameter[]param){DataTabledt=newDataTable();using(SqlConnectionconn=newSqlConnection(connectionString)){SqlDataAdapterda=newSqlDataAdapter(sql,conn);if(param!=null)da.SelectCommand.Parameters.AddRange(param);da.Fill(dt);}returndt;}///<summary>///GetCommand方法重载///</summary>///<paramname="commandText">sql语句</param>///<paramname="connection"></param>///<returns></returns>privatestaticDbCommandGetCommand(stringcommandText,DbConnectionconnection){DbCommandcommand=connection.CreateCommand();command.CommandText=commandText;command.CommandType=CommandType.Text;returncommand;}publicDbCommandGetSqlStringCommond(stringsqlQuery){DbCommanddbCommand=connection.CreateCommand();dbCommand.CommandText=sqlQuery;dbCommand.CommandType=CommandType.Text;returndbCommand;}///<summary>///GetParameter用于为命令设置参数///</summary>///<paramname="paramName"></param>///<paramname="paramValue"></param>///<paramname="command"></param>///<returns></returns>privatestaticDbParameterGetParameter(stringparamName,objectparamValue,DbCommandcommand){DbParameterparameter=command.CreateParameter();parameter.ParameterName=paramName;parameter.Value=paramValue;returnparameter;}///<summary>///执行无参的存储过程///</summary>///<paramname="sqlCommand">存储过程名</param>///<returns></returns>publicstaticintExecuteNonQueryProc(stringsqlCommand){intresult=0;using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(sqlCommand,CommandType.StoredProcedure,connection);connection.Open();result=command.ExecuteNonQuery();command.Parameters.Clear();}returnresult;}///<summary>///执行无返回值有参数的存储过程///</summary>///<paramname="sqlCommand">存储过程名</param>///<paramname="parameters">参数</param>///<returns></returns>publicstaticintExecuteNonQueryProc(stringsqlCommand,Dictionary<string,object>parameters){intresult=0;using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(sqlCommand,CommandType.StoredProcedure,connection);foreach(KeyValuePair<string,object>pinparameters){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();result=command.ExecuteNonQuery();command.Parameters.Clear();}returnresult;}///<summary>///执行无返回值的sql语句///</summary>///<paramname="sqlCommand"></param>///<paramname="parameters"></param>publicstaticintExecuteNonQuery(stringsqlCommand){intresult=0;using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(sqlCommand,CommandType.Text,connection);connection.Open();result=command.ExecuteNonQuery();command.Parameters.Clear();returnresult;}}///<summary>///执行有参数的sql语句///</summary>///<paramname="sqlCommand"></param>///<paramname="para"></param>///<returns></returns>publicstaticintExecuteNonQuery(stringsqlCommand,Dictionary<string,object>para){intresult=0;using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(sqlCommand,CommandType.Text,connection);foreach(KeyValuePair<string,object>pinpara){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();result=command.ExecuteNonQuery();command.Parameters.Clear();returnresult;}}///<summary>///执行有返回值无参数的存储过程///</summary>///<paramname="cmdText"></param>///<returns></returns>publicstaticobjectExecuteScalarProc(stringcmdText){using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(cmdText,CommandType.StoredProcedure,connection);connection.Open();objectval=command.ExecuteScalar();command.Parameters.Clear();returnval;}}///<summary>///执行有返回值的有参数的存储过程///</summary>///<paramname="cmdText">存储过程名</param>///<paramname="para">参数</param>///<returns></returns>publicstaticobjectExecuteScalarProc(stringcmdText,Dictionary<string,object>para){using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(cmdText,CommandType.StoredProcedure,connection);foreach(KeyValuePair<string,object>pinpara){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();objectval=command.ExecuteScalar();command.Parameters.Clear();returnval;}}///<summary>///执行有返回值的sql语句///</summary>///<paramname="cmdText"></param>///<returns></returns>publicstaticobjectExecuteScalar(stringcmdText){using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(cmdText,CommandType.Text,connection);connection.Open();objectval=command.ExecuteScalar();command.Parameters.Clear();returnval;}}///<summary>///执行有返回值有参数的sql语句///</summary>///<paramname="cmdText"></param>///<paramname="para"></param>///<returns></returns>publicstaticobjectExecuteScalar(stringcmdText,Dictionary<string,object>para){using(DbConnectionconnection=GetConnection()){DbCommandcommand=GetCommand(cmdText,CommandType.Text,connection);foreach(KeyValuePair<string,object>pinpara){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();objectval=command.ExecuteScalar();command.Parameters.Clear();returnval;}}///<summary>///执行无参数的存储过程,返回DbDataReader对象///</summary>///<paramname="sqlCommand">存储过程名</param>///<returns></returns>publicstaticDbDataReaderGetReaderProc(stringsqlCommand){try{DbConnectionconnection=GetConnection();DbCommandcommand=GetCommand(sqlCommand,CommandType.StoredProcedure,connection);connection.Open();DbDataReaderreader=command.ExecuteReader(CommandBehavior.CloseConnection);returnreader;}catch(Exceptionex){Console.Write(""+ex.Message);returnnull;}}///<summary>///执行有参数的存储过程,返回DbDataReader对象///</summary>///<paramname="sqlCommand"></param>///<paramname="parameters"></param>///<returns></returns>publicstaticDbDataReaderGetReaderProc(stringsqlCommand,Dictionary<string,object>parameters){try{DbConnectionconnection=GetConnection();DbCommandcommand=GetCommand(sqlCommand,CommandType.StoredProcedure,connection);foreach(KeyValuePair<string,object>pinparameters){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();DbDataReaderreader=command.ExecuteReader(CommandBehavior.CloseConnection);command.Parameters.Clear();returnreader;}catch{returnnull;}}#region///<summary>///执行无参数的sql语句,返回DbDataReader对象///</summary>///<paramname="sqlCommand"></param>///<returns></returns>publicstaticDbDataReaderGetReader(stringsqlCommand){try{DbConnectionconnection=GetConnection();DbCommandcommand=GetCommand(sqlCommand,CommandType.Text,connection);connection.Open();DbDataReaderreader=command.ExecuteReader(CommandBehavior.CloseConnection);command.Parameters.Clear();returnreader;}catch(Exceptionex){Console.Write(""+ex.Message);returnnull;}}#endregion///<summary>///执行有参数的sql语句,返回DbDataReader对象///</summary>///<paramname="sqlCommand"></param>///<paramname="parameters"></param>///<returns></returns>publicstaticDbDataReaderGetReader(stringsqlCommand,Dictionary<string,object>parameters){try{DbConnectionconnection=GetConnection();DbCommandcommand=GetCommand(sqlCommand,CommandType.Text,connection);foreach(KeyValuePair<string,object>pinparameters){command.Parameters.Add(GetParameter(p.Key,p.Value,command));}connection.Open();DbDataReaderreader=command.ExecuteReader(CommandBehavior.CloseConnection);command.Parameters.Clear();returnreader;}catch(Exceptionex){Console.Write(""+ex.Message);returnnull;}}///<summary>///返回DataTable对象///</summary>///<paramname="safeSql"></param>///<returns></returns>publicstaticDataTableGetDataSet(stringsafeSql){/*DbProviderFactory_factory=DbProviderFactories.GetFactory(providerName);DbConnectionconnection=GetConnection();connection.Open();DbDataAdapterda=_factory.CreateDataAdapter();da.SelectCommand=connection.CreateCommand();da.SelectCommand.CommandText=safeSql;DataTabledt=newDataTable();da.Fill(dt);returndt;*/using(DbConnectionconnection=GetConnection()){DbProviderFactory_factory=DbProviderFactories.GetFactory(providerName);DbCommandcommand=GetCommand(safeSql,CommandType.Text,connection);connection.Open();DbDataAdapterda=_factory.CreateDataAdapter();da.SelectCommand=command;DataTabledatatable=newDataTable();da.Fill(datatable);returndatatable;}}}}