ASP.NET DAO ( Data Access Object ) Pattern 方法

 
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();
 
         }
      }
   }