.net连接sqlserver类库

王朝学院·作者佚名 2016-08-28

1usingSystem;2usingSystem.Collections.Generic;3usingSystem.Linq;4usingSystem.Web;5usingSystem.Data.SqlClient;6usingSystem.Data;7usingSystem.Configuration;89publicclassSqlHelper10{11publicstaticreadonlystringconString = ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;12//增删改13publicstaticboolExeNonQuery(stringsql, CommandType type,paramsSqlParameter[] lists)14{15boolbFlag =false;16using(SqlConnection con =newSqlConnection(conString))17{18SqlCommand cmd =newSqlCommand();19cmd.Connection =con;20cmd.CommandText =sql;21cmd.CommandType =type;22if(lists !=null)23{24foreach(SqlParameter pinlists)25{26cmd.Parameters.Add(p);27}28}29try30{31if(con.State ==ConnectionState.Closed)32{33con.Open();34}35intresult =cmd.ExecuteNonQuery();36if(result >0)37{38bFlag =true;39}4041}42catch{ ;}43}44returnbFlag;45}4647//查．读48publicstaticSqlDataReader ExeDataReader(stringsql, CommandType type,paramsSqlParameter[] lists)49{50SqlConnection con =newSqlConnection(conString);51SqlCommand cmd =newSqlCommand();52cmd.Connection =con;53cmd.CommandText =sql;54cmd.CommandType =type;5556if(con.State ==ConnectionState.Closed)57{58con.Open();59}6061if(lists !=null)62{63foreach(SqlParameter pinlists)64{65cmd.Parameters.Add(p);66}67}6869SqlDataReader reader =cmd.ExecuteReader();7071returnreader;72}7374//返回单个值75publicstaticobjectGetScalar(stringsql, CommandType type,paramsSqlParameter[] lists)76{77objectreturnValue =null;78using(SqlConnection con =newSqlConnection(conString))79{80SqlCommand cmd =newSqlCommand();81cmd.Connection =con;82cmd.CommandText =sql;83cmd.CommandType =type;84if(lists !=null)85{86foreach(SqlParameter pinlists)87{88cmd.Parameters.Add(p);89}90}91try92{93if(con.State ==ConnectionState.Closed)94{95con.Open();96}97returnValue =cmd.ExecuteScalar();9899}100catch{ ; }101}102returnreturnValue;103}104105//事务106publicstaticboolExeNonQueryTran(List<SqlCommand>list)107{108boolflag =true;109SqlTransaction tran =null;110using(SqlConnection con =newSqlConnection(conString))111{112try113{114if(con.State ==ConnectionState.Closed)115{116con.Open();117tran =con.BeginTransaction();118foreach(SqlCommand cominlist)119{120com.Connection =con;121com.Transaction =tran;122com.ExecuteNonQuery();123}124tran.Commit();125}126}127catch(Exception ex)128{129Console.Write(ex.Message);130tran.Rollback();131flag =false;132}133}134returnflag;135}136//返回DataTable137publicstaticDataTable GetTable(stringsql)138{139SqlConnection conn =newSqlConnection(conString);140SqlDataAdapter da =newSqlDataAdapter(sql, conn);141DataTable table =newDataTable();142da.Fill(table);143returntable;144}145///<summary>146///调用带参数的存储过程，返回dataTable147///</summary>148///<param name="PRoc">存储过程的名称</param>149///<param name="rows">一页几行</param>150///<param name="page">当前页</param>151///<param name="tabName">表名</param>152///<returns>dataTable</returns>153publicstaticDataTable Proc_Table(stringproc,introws,intpage,stringtabName)154{155SqlConnection conn =newSqlConnection(conString);156SqlCommand cmd =newSqlCommand(proc, conn);157//指定调用存储过程158cmd.CommandType =CommandType.StoredProcedure;159cmd.Parameters.Add("@rows", rows);160cmd.Parameters.Add("@page", page);161cmd.Parameters.Add("@tabName", tabName);162SqlDataAdapter apt =newSqlDataAdapter(cmd);163DataTable dt =newDataTable();164apt.Fill(dt);165returndt;166}167168//调用带参数的存储过程返回datatable169publicstaticDataTable GetTablebyproc(stringproc,intpageRow,intpagSize,stringtabName)170{171SqlConnection conn =newSqlConnection(conString);172SqlCommand cmd =newSqlCommand(proc,conn);173cmd.CommandType =CommandType.StoredProcedure;174cmd.Parameters.Add("@rows", pageRow);175cmd.Parameters.Add("@pagesize", pagSize);176cmd.Parameters.Add("@tablename", tabName);177SqlDataAdapter apt =newSqlDataAdapter(cmd);178DataTable table =newDataTable();179apt.Fill(table);180returntable;181182}183publicstaticDataTable GetDataByPager(stringtbname,stringfieldkey,intpagecurrent,intpagesize,stringfieldshow,stringfieldorder,stringwherestring,refintpagecount)184{185SqlParameter[] parameters ={186newSqlParameter("@tbname", SqlDbType.VarChar,100),187newSqlParameter("@FieldKey", SqlDbType.VarChar,100),188newSqlParameter("@PageCurrent", SqlDbType.Int),189newSqlParameter("@PageSize", SqlDbType.Int),190newSqlParameter("@FieldShow", SqlDbType.VarChar,200),191newSqlParameter("@FieldOrder", SqlDbType.VarChar,200),192newSqlParameter("@WhereString", SqlDbType.VarChar,500),193newSqlParameter("@RecordCount", SqlDbType.Int),194};195parameters[0].Value =tbname;196parameters[1].Value =fieldkey;197parameters[2].Value =pagecurrent;198parameters[3].Value =pagesize;199parameters[4].Value =fieldshow;200parameters[5].Value =fieldorder;201parameters[6].Value =wherestring;202parameters[7].Direction =ParameterDirection.Output;203DataTable dt = ExecuteQuery("sp_get_data", parameters).Tables[0];204pagecount = Convert.ToInt32(parameters[7].Value);205returndt;206}207///<summary>208///执行有参数的查询类存储过程209///</summary>210///<param name="pstrStoreProcedure">存储过程名</param>211///<param name="pParms">存储过程的参数数组</param>212///<returns>查询得到的结果集</returns>213publicstaticDataSet ExecuteQuery(stringpstrStoreProcedure, SqlParameter[] pParms)214{215216217DataSet dsResult =newDataSet();218SqlDataAdapter sda =newSqlDataAdapter();219SqlConnection con =newSqlConnection(conString);220SqlCommand cmd;221intintCounter;222try223{224if(con.State !=ConnectionState.Open)225con.Open();226cmd =newSqlCommand();227cmd.Connection =con;228cmd.CommandType =CommandType.StoredProcedure;229cmd.CommandText =pstrStoreProcedure;230if(pParms !=null)231{232for(intCounter =0; intCounter < pParms.GetLength(0); intCounter++)233{234cmd.Parameters.Add(pParms[intCounter]);235}236}237sda.SelectCommand =cmd;238sda.Fill(dsResult);239240241}242catch(SqlException ex)243{244thrownewException(ex.Message);245}246finally247{248//清空关闭操作249sda.Dispose();250con.Close();251con.Dispose();252253}254returndsResult;255}256///<summary>257///此分页存储过程直没修改大家可以用自己的258///</summary>259///<param name="tableName">表名</param>260///<param name="getFields">需要返回的列</param>261///<param name="orderName">排序的字段名</param>262///<param name="pageSize">页尺寸</param>263///<param name="pageIndex">页码</param>264///<param name="isGetCount">返回记录总数,非 0 值则返回</param>265///<param name="orderType">设置排序类型,0表示升序非0降序</param>266///<param name="strWhere"></param>267///<returns></returns>268//public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)269//{270//SqlParameter[] parameters = {271//new SqlParameter("@tblName", SqlDbType.VarChar, 255),272//new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),273//new SqlParameter("@fldName", SqlDbType.VarChar, 255),274//new SqlParameter("@PageSize", SqlDbType.Int),275//new SqlParameter("@PageIndex", SqlDbType.Int),276//new SqlParameter("@doCount", SqlDbType.Bit),277//new SqlParameter("@OrderType", SqlDbType.Bit),278//new SqlParameter("@strWhere", SqlDbType.VarChar, 1500)279//};280//parameters[0].Value = tableName;281//parameters[1].Value = getFields;282//parameters[2].Value = orderName;283//parameters[3].Value = pageSize;284//parameters[4].Value = pageIndex;285//parameters[5].Value = isGetCount ? 1 : 0;286//parameters[6].Value = orderType ? 1 : 0;287//parameters[7].Value = strWhere;288//return SqlHelper.RunProcedure("pro_pageList", parameters, "ds");289//}290//public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)291//{292//using (SqlConnection connection = new SqlConnection(conString))293//{294//DataSet dataSet = new DataSet();295//connection.Open();296//new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);297//connection.Close();298//return dataSet;299//}300//}301///<summary>302///构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)303///</summary>304///<param name="connection">数据库连接</param>305///<param name="storedProcName">存储过程名</param>306///<param name="parameters">存储过程参数</param>307///<returns>SqlCommand</returns>308privatestaticSqlCommand BuildQueryCommand(SqlConnection connection,stringstoredProcName, IDataParameter[] parameters)309{310SqlCommand command =newSqlCommand(storedProcName, connection)311{312CommandType =CommandType.StoredProcedure313};314foreach(SqlParameter parameterinparameters)315{316if(parameter !=null)317{318if(((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value ==null))319{320parameter.Value =DBNull.Value;321}322command.Parameters.Add(parameter);323}324}325returncommand;326}327//根据表名和主键id来进行删除328publicstaticintDelData(stringtabName,stringID)329{330if(ID !=string.Empty && ID !="0")331{332stringsql =string.Format("delete from {0} WHERE (ID IN ({1}))", tabName, ID);333intdelNum =ExecuteSql(sql);334returndelNum;335}336return0;337}338//增删改返回执行条数339publicstaticintExecuteSql(stringSQLString)340{341intnum2;342using(SqlConnection connection =newSqlConnection(conString))343{344SqlCommand command =newSqlCommand(SQLString, connection);345try346{347connection.Open();348num2 =command.ExecuteNonQuery();349}350catch(SqlException exception)351{352connection.Close();353throwexception;354}355finally356{357if(command !=null)358{359command.Dispose();360}361}362}363returnnum2;364}365}