Member 11589429 Ответов: 1

Преобразование в N-уровневую архитектуру

Я хочу преобразовать свою архитектуру в N-уровневую архитектуру.Пожалуйста, найдите ниже подробную информацию.

----------------------- Sql-------------------------------- 
USE [Practice] GO

/****** Object: Table [dbo].[Brand] Script Date: 03/02/2019 00:56:50 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Brand](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BrandName] [varchar](20) NULL,
    [ProductId] [int] NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
----------------------------------------------------------------------------------------------------

USE [Practice]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 03/02/2019 00:57:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Product](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](20) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


--------------------------------------------------------------------------------------------------------------------

USE [Practice]
GO

/****** Object:  Table [dbo].[BrandProductAssociation]    Script Date: 03/02/2019 00:58:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BrandProductAssociation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BrandId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [int] NOT NULL,
    [Ingrdient] [varchar](10) NULL,
    [UseFor] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


--------------------------------------------------------------------------------------------------------------------
USE [Practice]
GO

/****** Object:  StoredProcedure [dbo].[Proc_SelecBrand]    Script Date: 03/02/2019 00:58:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE Procedure [dbo].[Proc_SelecBrand]  
@ProductId int = null  
as  
begin  
select * from Brand where (productid =@ProductId or @ProductId is null)  
end
GO


----------------------------------------------------------------------------------------------------------------------
USE [Practice]
GO

/****** Object:  StoredProcedure [dbo].[Proc_SelecProduct]    Script Date: 03/02/2019 00:59:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create Procedure [dbo].[Proc_SelecProduct]
as
begin
select * from Product
end
GO


------------------------------------------------------------------------------------------------------------------------
USE [Practice]
GO

/****** Object:  StoredProcedure [dbo].[Proc_InsertBrandProductDetails]    Script Date: 03/02/2019 01:00:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create Procedure [dbo].[Proc_InsertBrandProductDetails]
@BrandId int,
@ProductId int,
@price int,
@Ingri varchar(10),
@user varchar(10)
as
begin
Insert into BrandProductAssociation ([BrandId],[ProductId],[Price],[Ingrdient],[UseFor]) values (@BrandId,@ProductId,@price,@Ingri,@user)
end
GO

------aspx----------------------------------------------------------------

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="TestApp._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>

            <table style="width: 300px" cellspacing="4" cellpadding="4">
                <tr>
                    <td colspan="2">
                        <asp:Label ID="LblMessage" runat="server" ForeColor="Red"></asp:Label>
                    </td>
                </tr>
                <tr style="height: 20px">
                    <td style="width: 100; background-color: #e5e5e5">
                        Product
                    </td>
                    <td style="width: 200; background-color: #f7f7f7">
                        <asp:DropDownList ID="ddlProduct" runat="server" Height="19px" Width="200px" OnSelectedIndexChanged="ddlProduct_SelectedIndexChanged"
                            AutoPostBack="true">
                        </asp:DropDownList>
                        <span style="color: Red">*</span>
                        <br />
                        <asp:RequiredFieldValidator ID="RFDProduct" runat="server" ErrorMessage="Please Select Product"
                            InitialValue="--Select Product--" ControlToValidate="ddlProduct" Display="Dynamic" ForeColor="Red" ></asp:RequiredFieldValidator>
                    </td>
                </tr>
                <tr style="height: 20px">
                    <td style="width: 100; background-color: #e5e5e5">
                        Brand
                    </td>
                    <td style="width: 200; background-color: #f7f7f7">
                        <asp:DropDownList ID="ddlBrand" runat="server" Height="19px" Width="200px">
                        </asp:DropDownList>
                        <span style="color: Red">*</span>
                        <br />
                        <asp:RequiredFieldValidator ID="RFDBrand" runat="server" ErrorMessage="Please Select Brand"
                            InitialValue="--Select Brand--" ControlToValidate="ddlBrand" Display="Dynamic" ForeColor="Red" ></asp:RequiredFieldValidator>
                    </td>
                </tr>

        <tr style="height: 10px">
            <td style="width: 100; background-color: #e5e5e5">
                Price
            </td>
            <td style="width: 200; background-color: #f7f7f7">
                <asp:TextBox ID="txtPrice" runat="server" Width="200px" Height="19px"></asp:TextBox><span style="color: Red">*</span>
                <br />
                <asp:RequiredFieldValidator ID="RFDtxtPrice" runat="server" ErrorMessage="Please Enter Price" Display="Dynamic"
                    ControlToValidate="txtPrice" ForeColor="Red" ></asp:RequiredFieldValidator>
            </td>
        </tr>

        <tr style="height: 10px">
            <td style="width: 100; background-color: #e5e5e5">
                Ingredient
            </td>
            <td style="width: 200; background-color: #f7f7f7">
                <asp:TextBox ID="txtIngredient" runat="server" Width="200px" Height="19px"></asp:TextBox><span
                    style="color: Red">*</span>
                <br />
                <asp:RequiredFieldValidator ID="RFDIngredient" runat="server" ErrorMessage="Please Enter Ingredient"
                    ControlToValidate="txtIngredient" Display="Dynamic" ForeColor="Red" ></asp:RequiredFieldValidator>
            </td>
        </tr>

        <tr style="height: 10px">
            <td style="width: 100; background-color: #e5e5e5">
                Use
            </td>
            <td style="width: 200; background-color: #f7f7f7">
                <asp:TextBox ID="txtUse" runat="server" Width="200px" Height="19px"></asp:TextBox><span style="color: Red">*</span>
                <br />
                <asp:RequiredFieldValidator ID="RFDUse" runat="server" ErrorMessage="Please Enter Use"
                    ControlToValidate="txtUse" Display="Dynamic" ForeColor="Red" ></asp:RequiredFieldValidator>
            </td>
        </tr>

        <tr style="height: 10px">
            <td colspan="2" align="center">
                <asp:Button ID="BtnSubmit" runat="server" Text="Submit" OnClick="BtnSubmit_Click" />
            </td>
        </tr>
    </table>
        </ContentTemplate>
    </asp:UpdatePanel>


</asp:Content>

------------------------------------------------------------------------------------ цезий ------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace TestApp
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["UserId"] = 2;

            if (Session["UserId"] == null)
            {
                Response.Redirect("Login.aspx");
            }
            else
            { 
                if(!IsPostBack)
                {
                    PopulateBrand();
                    PopulateProduct();
                }
            }
        }

        private void PopulateProduct() 
        {
            try
            {


                DataSet ds = SqlHelpper.GetDataSet(CommandType.StoredProcedure, "Proc_SelecProduct", "Product", "PracticeConnectionString", null);

                if (ds != null)
                {
                    DataTable dt = ds.Tables["Product"];

                    if (dt.Rows.Count > 0)
                    {
                        ddlProduct.Items.Insert(0, "--Select Product--");

                        foreach (DataRow dr in dt.Rows)
                        {
                            ListItem lt = new ListItem(dr["ProductName"].ToString(), dr["Id"].ToString());

                            ddlProduct.Items.Add(lt);

                        }
                    }


                }

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        private void PopulateBrand()
        {
            try
            {
                string Product = null;

                if (ddlProduct.SelectedIndex > 0)
                {
                    Product = ddlProduct.SelectedItem.Value;

                }
                DataSet ds = SqlHelpper.GetDataSet(CommandType.StoredProcedure, "Proc_SelecBrand", "Product", "PracticeConnectionString",
                             new SqlParameter("@ProductId", Product));          


                if (ds != null)
                {
                    ddlBrand.DataSource = ds;
                    ddlBrand.DataTextField = "BrandName";
                    ddlBrand.DataValueField = "Id";
                    ddlBrand.DataBind();
                    ddlBrand.Items.Insert(0, "--Select Brand--");
                }
            }
            catch (Exception ex)
            {
                throw;
            }


        }

        protected void ddlProduct_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                PopulateBrand();
            }
            catch (Exception ex)
            { 

            }
        }

        protected void BtnSubmit_Click(object sender, EventArgs e)
        {
            int count = SqlHelpper.ExecuteNonQuery(CommandType.StoredProcedure, "Proc_InsertBrandProductDetails", "PracticeConnectionString",
                new SqlParameter("@BrandId",ddlBrand.SelectedItem.Value),
                new SqlParameter("@price",txtPrice.Text),
                new SqlParameter("@ProductId",ddlProduct.SelectedItem.Value),
                new SqlParameter("@Ingri",txtIngredient.Text),
                new SqlParameter("@user",txtUse.Text));

            if (count > 0)
            {
              // Show message

                //clean control 

                txtIngredient.Text = "";
                txtPrice.Text = "";
                txtUse.Text = "";
                ddlBrand.SelectedIndex = 0;
                ddlProduct.SelectedIndex = 0;
                LblMessage.Text = "BrandProduct details inserted sucessfully.";


            }
        }
    }
}

-------------------- Вспомогательный класс, созданный под кодом приложения-------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace TestApp
{
    public class SqlHelpper
    {
        public static DataSet GetDataSet(CommandType cmdType, string cmdText, string tabName, string connString, params SqlParameter[] cmdParms)
        {
            string Constring = ConfigurationManager.ConnectionStrings[connString].ConnectionString;
            SqlConnection conn = new SqlConnection(Constring);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
               foreach(SqlParameter param in cmdParms)
               {

                   cmd.Parameters.Add(param);
               }
            }

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                da.Fill(ds, tabName);

                return ds;
            }
            catch
            {
                conn.Close();

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText,string connString,params SqlParameter[] cmdParms)
        {
            SqlConnection conn=null;
            int val=0;
            try
            {
                string Constring = ConfigurationManager.ConnectionStrings[connString].ConnectionString;

                conn = new SqlConnection(Constring);
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                val = cmd.ExecuteNonQuery();
                // cmd.Parameters.Clear();
                int cat = val;
               conn.Close();
                return val;

            }
            catch (Exception)
            {
                conn.Close();
                throw;
                //trans.Rollback();
            }
            finally
            {
              conn.Close();
            }
    }

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

     }
}

Что я уже пробовал:

-------------------- Helper class created under app_code-------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace TestApp
{
    public class SqlHelpper
    {
        public static DataSet GetDataSet(CommandType cmdType, string cmdText, string tabName, string connString, params SqlParameter[] cmdParms)
        {
            string Constring = ConfigurationManager.ConnectionStrings[connString].ConnectionString;
            SqlConnection conn = new SqlConnection(Constring);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
               foreach(SqlParameter param in cmdParms)
               {

                   cmd.Parameters.Add(param);
               }
            }

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                da.Fill(ds, tabName);

                return ds;
            }
            catch
            {
                conn.Close();

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText,string connString,params SqlParameter[] cmdParms)
        {
            SqlConnection conn=null;
            int val=0;
            try
            {
                string Constring = ConfigurationManager.ConnectionStrings[connString].ConnectionString;

                conn = new SqlConnection(Constring);
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                val = cmd.ExecuteNonQuery();
                // cmd.Parameters.Clear();
                int cat = val;
               conn.Close();
                return val;

            }
            catch (Exception)
            {
                conn.Close();
                throw;
                //trans.Rollback();
            }
            finally
            {
              conn.Close();
            }
    }

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

     }
}

ASP.NET Источник

Преобразование в N-уровневую архитектуру

1 Ответов

OriginalGriff

Категории

Недавние ответы

Изменение источника данных (базы данных) программно

Проблема при попытке вставить данные: ошибка при преобразовании типа данных nvarchar в числовой.

Может ли кто-нибудь, пожалуйста, сказать мне, как я практичен в жизненном цикле страницы .NET

C# get и set ? любая помощь ценится!

Как получить список ip-адресов пользователей, подключенных к моему Wi-Fi