[SQLHelper.cs]
using System;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace PetShop.DBUtility {
public class SqlHelper {
// 数据库连接字符串
public static readonly string PubConnectionString =
ConfigurationManager.
ConnectionStrings["PubConnectionString"].
ConnectionString;
// 缓存参数数组
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 根据提供的参数，执行一个SqlCommand以返回连接串所指定数据库的结果
/// </summary>
/// <remarks>
/// 例如:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">CommandType (存储过程, 文本, 等等...)</param>
/// <param name="commandText">存储过程名或T-SQL </param>
/// <param name="commandParameters">qlParamters数组，用于执行命令</param>
/// <returns>包含结果的SqlDataReader对象</returns>
public static SqlDataReader ExecuteReader(
string connectionString,
CommandType cmdType,
string cmdText,
params SqlParameter[] commandParameters) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
} catch {
conn.Close();
throw;
}
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(
SqlCommand cmd,
SqlConnection conn,
SqlTransaction trans,
CommandType cmdType,
string cmdText,
SqlParameter[] cmdParms) {
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null) {
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
[Default.aspx]
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default" %>
<%@ import Namespace="PetShop.IDAL" %>
<%@ import Namespace="PetShop.Model" %>
<%@ import Namespace="PetShop.SQLServerDAL" %>
<!DOCTYPE
html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Default</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<%
IAuthors authors;
System.Collections.Generic.IList<AuthorsInfo> authorsInfos;
authorsInfos = new System.Collections.Generic.List<AuthorsInfo>();
authors = new Authors();
authorsInfos = authors.GetAllAuthors();
foreach(AuthorsInfo item in authorsInfos){
Response.Write("# " + item.Au_id + " | " + item.Au_lname + "<br/>");
}
%>
</div>
</form>
</body>
</html>
No comments:
Post a Comment