sqlhelp.cs

一个可以简单实现模块的拖曳、锁定、最大化、最小化控制的示例

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

/// <summary>
/// SqlHelp 的摘要说明
/// </summary>
public class SqlHelp
{
	//数据库连接字符串
	private string connString;

	//构造函数
	public SqlHelp() {
		string dbName = ConfigurationManager.AppSettings["sqlServer2005DbName"];
		connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + HttpContext.Current.Request.PhysicalApplicationPath+dbName + ";Integrated Security=True;User Instance=True";
	}
	public SqlHelp(string connString) {
		this.connString = connString;
	}
	//----------------------------------------------//
	//-----------------普通DML操作------------------//
	//----------------------------------------------//

	/// <summary>
	/// 判断是否有查询记录，一般用于用户是否存在等
	/// 例：select * from [user] where name='aaa' and pwd='111';
	/// </summary>
	/// <param name="strQuery"></param>
	/// <returns></returns>
	//5_1_a_s_p_x.c_o_m
	public bool Exists(string strQuery) {
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				SqlDataAdapter da = new SqlDataAdapter(strQuery, conn);
				da.Fill(ds, "ds");
				if (ds.Tables["ds"].Rows.Count > 0) {
					return true;
				} else {
					return false;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 通过select查询，获取DataSet对象
	/// </summary>
	/// <param name="strQuery">select查询语句</param>
	/// <returns>DataSet对象</returns>
	public DataSet GetList(string strQuery) {
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				SqlDataAdapter da = new SqlDataAdapter(strQuery, conn);
				da.Fill(ds, "ds");
				return ds;
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 查询“分页”内容
	/// </summary>
	/// <param name="tName">表名或视图名</param>
	/// <param name="pkField">主键字段（必须是int型）</param>
	/// <param name="queryFields">需要查询的字段列表</param>
	/// <param name="sqlWhere">where语句</param>
	/// <param name="sqlOrder">排序内容</param>
	/// <param name="pageSize">每页记录数</param>
	/// <param name="pageIndex">第几页</param>
	public DataSet GetList(string tName, string pkField, string queryFields, string sqlWhere, string sqlOrder, int pageSize, int pageIndex) {
		if (pageIndex < 1) {
			pageIndex = 1;
		}
		if (pageSize < 1) {
			pageSize = 1;
		}
		int selectFrom = (pageIndex - 1) * pageSize;

		StringBuilder buffer = new StringBuilder();
		buffer.Append("SELECT TOP " + pageSize + " " + queryFields);
		buffer.Append(" FROM " + tName);
		buffer.Append(" WHERE (" + pkField + " NOT IN (");
		if (sqlWhere == null || sqlWhere != null && sqlWhere == string.Empty) {
			buffer.Append("SELECT TOP " + selectFrom + " " + pkField + " FROM " + tName + ")) ");
		} else {
			buffer.Append("SELECT TOP " + selectFrom + " " + pkField + " FROM " + tName + " where " + sqlWhere + " ) and " + sqlWhere + ") ");
		}
		if (sqlOrder != null && sqlOrder != string.Empty) {
			buffer.Append(sqlOrder);
		}
		string strQuery = buffer.ToString();
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				SqlDataAdapter da = new SqlDataAdapter(strQuery, conn);
				da.Fill(ds, "ds");
				return ds;
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 执行 Transact-SQL 语句并返回受影响的行数
	/// </summary>
	/// <param name="strQuery">Transact-SQL 语句</param>
	/// <returns>影响的行数</returns>
	public int ExecuteNonQuery(string strQuery) {
		int rows = 0;	//影响行数
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand(strQuery, conn)) {
					conn.Open();
					rows = command.ExecuteNonQuery();
					conn.Close();
					return rows;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
	/// </summary>
	/// <param name="strQuery">Transact-SQL 语句，一般为select count(*) from table-name</param>
	/// <returns>结果集中第一行的第一列。忽略其他列或行。</returns>
	public object ExecuteScalar(string strQuery) {
		object obj = new object();	//结果集中第一行的第一列的内容
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand(strQuery, conn)) {
					conn.Open();
					obj = command.ExecuteScalar();
					conn.Close();
					return obj;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 获取SqlDataReader对象
	/// 注意：使用SqlDataReader时，不能断开连接，所以不能使用using方式创建SqlConnection
	/// </summary>
	/// <param name="strQuery">Transact-SQL 语句</param>
	/// <returns>SqlDataReader对象</returns>
	public SqlDataReader ExecuteReader(string strQuery) {
		SqlConnection conn = new SqlConnection(connString);
		try {
			using (SqlCommand command = new SqlCommand(strQuery, conn)) {
				conn.Open();
				SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
				return reader;
			}
		} catch {
			conn.Close();
			throw;
		}
	}

	/// <summary>
	/// 查询“分页”内容
	/// </summary>
	/// <param name="tName">表名或视图名</param>
	/// <param name="pkField">主键字段（必须是int型）</param>
	/// <param name="queryFields">需要查询的字段列表</param>
	/// <param name="sqlWhere">where语句</param>
	/// <param name="sqlOrder">排序内容</param>
	/// <param name="pageSize">每页记录数</param>
	/// <param name="pageIndex">第几页</param>
	public SqlDataReader ExecuteReader(string tName, string pkField, string queryFields, string sqlWhere, string sqlOrder, int pageSize, int pageIndex) {
		if (pageIndex < 1) {
			pageIndex = 1;
		}
		if (pageSize < 1) {
			pageSize = 1;
		}
		int selectFrom = (pageIndex - 1) * pageSize;

		StringBuilder buffer = new StringBuilder();
		buffer.Append("SELECT TOP " + pageSize + " " + queryFields);
		buffer.Append(" FROM " + tName);
		buffer.Append(" WHERE (" + pkField + " NOT IN (");
		if (sqlWhere == null || sqlWhere != null && sqlWhere == string.Empty) {
			buffer.Append("SELECT TOP " + selectFrom + " " + pkField + " FROM " + tName + ")) ");
		} else {
			buffer.Append("SELECT TOP " + selectFrom + " " + pkField + " FROM " + tName + " where " + sqlWhere + " ) and " + sqlWhere + ") ");
		}
		if (sqlOrder != null && sqlOrder != string.Empty) {
			buffer.Append(sqlOrder);
		}
		string strQuery = buffer.ToString();
		SqlConnection conn = new SqlConnection(connString);
		DataSet ds = new DataSet();
		try {
			using (SqlCommand command = new SqlCommand(strQuery, conn)) {
				conn.Open();
				SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
				return reader;
			}
		} catch {
			conn.Close();
			throw;
		}
	}

	//----------------------------------------------//
	//-------------带参数的DML操作------------------//
	//----------------------------------------------//

	/// <summary>
	/// 判断是否有查询记录，一般用于用户是否存在等
	/// 例：select * from [user] where name='aaa' and pwd='111';
	/// </summary>
	/// <param name="strQuery">select查询</param>
	/// /// <param name="paras">select参数列表</param>
	/// <returns>bool</returns>
	public bool Exists(string strQuery, SqlParameter[] paras) {
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand()) {
					PrepareCommand(conn, command, strQuery, paras);
					SqlDataAdapter da = new SqlDataAdapter(command);
					da.Fill(ds, "ds");
					if (ds.Tables["ds"].Rows.Count > 0) {
						return true;
					} else {
						return false;
					}
				}
			}
		} catch {
			throw;
		}
	}

	/// 通过select查询，获取DataSet对象
	/// </summary>
	/// <param name="strQuery">select查询语句</param>
	/// <param name="paras">select参数列表</param>
	/// <returns>DataSet对象</returns>
	public DataSet GetList(string strQuery, SqlParameter[] paras) {
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand()) {
					PrepareCommand(conn, command, strQuery, paras);
					SqlDataAdapter da = new SqlDataAdapter(command);
					da.Fill(ds, "ds");
					return ds;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 执行 Transact-SQL 语句并返回受影响的行数
	/// </summary>
	/// <param name="strQuery">Transact-SQL 语句</param>
	/// <param name="paras">select参数列表</param>
	/// <returns>影响的行数</returns>
	public int ExecuteNonQuery(string strQuery, SqlParameter[] paras) {
		int rows = 0;	//影响行数
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand()) {
					PrepareCommand(conn, command, strQuery, paras);
					rows = command.ExecuteNonQuery();
					return rows;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
	/// </summary>
	/// <param name="strQuery">Transact-SQL 语句，一般为select count(*) from table-name</param>
	/// <param name="paras">select参数列表</param>
	/// <returns>结果集中第一行的第一列。忽略其他列或行。</returns>
	public object ExecuteScalar(string strQuery, SqlParameter[] paras) {
		object obj = new object();	//结果集中第一行的第一列的内容
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand()) {
					PrepareCommand(conn, command, strQuery, paras);
					obj = command.ExecuteScalar();
					return obj;
				}
			}
		} catch {
			throw;
		}
	}

	/// <summary>
	/// 获取SqlDataReader对象
	/// 注意：使用SqlDataReader时，不能断开连接，所以不能使用using方式创建SqlConnection
	/// </summary>
	/// <param name="cmdType">CommandType</param>
	/// <param name="cmdText">string</param>
	/// <param name="commandParameters">params</param>
	/// <returns></returns>
	public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
		SqlConnection conn = new SqlConnection(connString);
		try {
			using (SqlCommand command = new SqlCommand()) {
				PrepareCommand(conn, command, cmdText, commandParameters);
				SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
				command.Parameters.Clear();
				return reader;
			}
		} catch {
			conn.Close();
			throw;
		}
	}

	//带参数的DML操作
	private void PrepareCommand(SqlConnection conn, SqlCommand command, string strQuery, SqlParameter[] paras) {
		if (conn.State != ConnectionState.Open) {
			conn.Open();
		}
		command.Connection = conn;
		command.CommandText = strQuery;
		command.CommandType = CommandType.Text;
		if (paras != null) {
			foreach (SqlParameter parm in paras)
				command.Parameters.Add(parm);
		}
	}



	//----------------------------------------------//
	//-------------运行存储过程---------------------//
	//----------------------------------------------//
	public DataSet GetListWithProcedure(string strProcedureName, SqlParameter[] paras) {
		DataSet ds = new DataSet();
		try {
			using (SqlConnection conn = new SqlConnection(connString)) {
				using (SqlCommand command = new SqlCommand(strProcedureName, conn)) {
					command.CommandType = CommandType.StoredProcedure;
					for (int i = 0; i < paras.Length; i++) {
						command.Parameters.Add(paras[i]);
					}
					SqlDataAdapter da = new SqlDataAdapter(command);
					da.Fill(ds, "ds");
					return ds;
				}
			}
		} catch {
			throw;
		}
	}
}