ASP.NET DAO ( Data Access Object ) Pattern 方法
web.config |
<?xml version="1.0"?> <configuration> <configSections> … … </configSections> <connectionStrings> <add name="ApplicationServices" connectionString="data source=VMSER001;initial catalog=CMS;persist security info=True;user id=sa;password=P@ssw0rd;" providerName="System.Data.SqlClient" /> </connectionStrings> … … </configuration> |
APP_CODE\DAO.cs |
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace CMS.App_Code { public class DAO { private SqlConnection getSqlConnecion() { try { String connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString().Trim(); SqlConnection conn = new SqlConnection(connectionString); return conn; }catch (Exception ex) { return null; } } public DataTable getSqlRecord(String SqlStatement) { SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader dr = null; try { conn = getSqlConnecion(); if (conn != null){ conn.Open(); if (conn.State == ConnectionState.Open) { cmd = new SqlCommand(SqlStatement, conn); dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); dr.Close(); dr.Dispose(); cmd.Dispose(); conn.Close(); conn.Dispose(); return dt; } else { return null; } } else { return null; } }catch(Exception ex){ if (dr != null) { if(!dr.IsClosed) dr.Close(); dr.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (conn != null) { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } //return null; DataTable errorMessage = new DataTable(); errorMessage.Columns.Add("Error Message"); errorMessage.Rows.Add(ex.Message); return errorMessage; } } public void setSqlRecord(String SqlStatement) { SqlConnection conn = null; SqlCommand cmd = null; try{ conn = getSqlConnecion(); if (conn != null){ conn.Open(); cmd = new SqlCommand(SqlStatement, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } }catch(Exception ex){ if (cmd != null) { cmd.Dispose(); } if (conn != null) { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } } } } } |
APP_CODE\Product.cs |
using System; using System.Data; using System.Text; namespace CMS.App_Code { public static class Product { public static DataTable getTop30Product() { DAO dao = new DAO(); return dao.getSqlRecord("SELECT TOP 30 XXXXXXXXXXX FROM [dbo].[Product]"); } public static DataTable getTop60Product() { DAO dao = new DAO(); return dao.getSqlRecord("SELECT TOP 60 XXXXXXXXXXX FROM [dbo].[Product]"); } public static DataTable getAllProduct() { DAO dao = new DAO(); return dao.getSqlRecord("SELECT XXXXXXXXXXX FROM [dbo].[Product]"); } public static void setProduct(String ProductName, int ProductCatagory, double ProductPrice) { StringBuilder SqlStatement = new StringBuilder(); SqlStatement.Append("INSERT INTO [dbo].[Product] (XXXXXXXXXXX) VALUES (N'XXXXXX"); SqlStatement.Append("', 'XXXXXXXXXX"); SqlStatement.Append("', GETDATE());"); DAO dao = new DAO(); dao.setSqlRecord(SqlStatement.ToString()); } } } |
Product.aspx.cs |
using System; using WebApplication3.App_Code; namespace CMS { public partial class Product : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Product.setProduct("XXXXX", XXXXXX, XXXXXXX); this.Top30Product_GridView.DataSource = Product.getTop30Product(); this.DataBind(); this.getTop60Product_GridView2.DataSource = Product.getTop60Product(); this.DataBind(); } } } |