.NET SqlHelper类

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

namespace WebApplication

{

publicclassSqlHelper

{

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 =new SqlCommand())

{

cmd.CommandText =strSql;

cmd.Parameters.AddRange(pams);

return Convert.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,out intpageCount,out introwCount,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;

}

}

}

}

}