.NET SqlHelper类

来源：互联网发布：科比职业生涯总数据编辑：程序博客网时间：2024/05/17 06:30

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

namespace WebApplication

{

public class SqlHelper

{

publicstaticstringstrConn ="server=.\\SqlExpress;database=zz;uid=sa;pwd=;";

publicstaticDataTable GetDataTable(string strSql,paramsSqlParameter[] pams) {

using(SqlConnection conn =newSqlConnection(strConn))

{

conn.Open();

using (SqlCommandcmd = conn.CreateCommand())

{

cmd.CommandText = strSql;

cmd.Parameters.AddRange(pams);

SqlDataAdapter adapter =newSqlDataAdapter(cmd);

DataTabledt =new DataTable();

adapter.Fill(dt);

return dt;

}

publicstaticintExcuteNoQuery(string strSql,paramsSqlParameter[] pams) {

using(SqlConnection conn =newSqlConnection(strConn))

{

conn.Open();

using (SqlCommandcmd =new SqlCommand(strSql,conn))

{

cmd.Parameters.AddRange(pams);

returncmd.ExecuteNonQuery();

}

publicstaticintExecuteScalar(string strSql,paramsSqlParameter[] pams){

using(SqlConnection conn =newSqlConnection(strConn))

{

conn.Open();

using(SqlCommandcmd =newSqlCommand())

{

cmd.CommandText =strSql;

cmd.Parameters.AddRange(pams);

returnConvert.ToInt32(cmd.ExecuteScalar());

}

publicstaticintExcuteNoQuerys(string[] strSqls,paramsSqlParameter[][] pams2) {

intres = 0;

using(SqlConnection conn =newSqlConnection(strConn))

{

conn.Open();

using (SqlCommandcmd =new SqlCommand())

{

SqlTransaction tran =conn.BeginTransaction();

cmd.Transaction = tran;

cmd.Connection = conn;

try

{

for(int i = 0; i < strSqls.Length; i++)

{

stringstrSql = strSqls[i];

cmd.CommandText = strSql;

if(pams2.Length>i)

{

cmd.Parameters.AddRange(pams2[i]);

}

res += cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

}

tran.Commit();

}

catch (Exceptionex)

{

tran.Rollback();

throwex;

}

returnres;

}

publicstaticDataTable GetPageListByPROC(stringproName,int pageIndex,intpageSize,outintpageCount,outintrowCount,bool isDEL) {

DataTable dt =new DataTable();

using(SqlConnection conn =newSqlConnection(strConn))

{

using (SqlCommandcmd =new SqlCommand(proName,conn))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@pageIndex",pageIndex);

cmd.Parameters.AddWithValue("@pageSize",pageSize);

cmd.Parameters.AddWithValue("isDel",isDEL);

cmd.Parameters.Add("@pageCount",SqlDbType.Int);

cmd.Parameters.Add("@rowCount",SqlDbType.Int);

cmd.Parameters[4].Direction = ParameterDirection.Output;

cmd.Parameters[3].Direction = ParameterDirection.Output;

SqlDataAdapter adapter =newSqlDataAdapter(cmd);

adapter.Fill(dt);

pageCount = (int)cmd.Parameters[3].Value;

rowCount = (int)cmd.Parameters[4].Value;

return dt;

}

0 0