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 =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;
}
}
}
}
}