存储过程把找到的记录的MenuOrder值与离他最近的一条进行交换附C#代码

最新推荐文章于 2017-01-05 10:49:14 发布

C979170768

最新推荐文章于 2017-01-05 10:49:14 发布

阅读量1.4k

点赞数

分类专栏： ASP.NET 文章标签：存储 c# table dataset textbox object

本文链接：https://blog.csdn.net/C979170768/article/details/7024640

版权

ASP.NET 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

这是效果图片

C# Code:

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;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
     
         Add_Table();
        if (!IsPostBack)
        {  
           
                 //添加数据
           // table.Visible = false;
            Table2.Visible = false;
            if (Request["id"]==null)
            {
            table1.Visible = false;
           // table2.Visible = false;
            DataTable dt = Add_recored();
            Add_Records(dt,null);
            }
            else if (Request["id"].ToString() == "1")
            {
                table1.Visible = true;
                Table2.Visible = false;
                // table2.Visible = false;
                DataTable dt = Add_recored();
                Add_Records(dt, null);
            }
            else if (Request["id"].ToString() == "2")
            {
                //table1.Visible = true;
                Table2.Visible = true;
                DataTable dt = Add_recored();
                Add_Records(dt, null);
            }


        }
        

    }

    private void Add_Records(DataTable dt,TreeNode node)
    {
        DataView dv = new DataView(dt);
        if (node == null)
        {
            dv.RowFilter = "parentid='0'";
        }
        else
        {
            dv.RowFilter = "parentid='"+node.Value+"'"; 
                   
        }
        foreach (DataRowView item in dv)
        {
            TreeNode tn = new TreeNode(item["menuname"].ToString(), item["menuid"].ToString());
            Add_Records(dt, tn);
            if (node == null)
            {
                TreeView1.Nodes.Add(tn);
            }
            else
            {
                node.ChildNodes.Add(tn);  
            }


        }



    }

    private DataTable Add_recored()
    {
        string str_con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        using (SqlConnection con = new SqlConnection(str_con))
        {
            using (SqlCommand cmd = new SqlCommand("select * from menutree order by ParentID,MenuOrder", con))
            {
                DataSet ds = new DataSet();
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds);

                }
                return ds.Tables[0];
            }
        }
    }    //添加数据的方法体

    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        Table2.Visible = true;
        table1.Visible = false;
     //   Add_Table();

    }

    private void Add_Table()
    {
        table1.Visible = false;
        Create_Table_Title();         //生成表头
        DataTable dt = create();
        TableRow row = null;
        TableCell cell = null;
        foreach (DataRow item in dt.Rows)
        {
            row = new TableRow();
            cell = new TableCell();
            cell.Text = item["menuid"].ToString();
            row.Cells.Add(cell);

            cell = new TableCell();
            cell.Text = item["menuname"].ToString();
            row.Cells.Add(cell);

            cell = new TableCell();
            LinkButton link = new LinkButton();
            link.Text = "进入栏目";
            link.PostBackUrl = "";
            cell.Controls.Add(link);
            //cell.Text = "栏目设置类";
            row.Cells.Add(cell);

            cell = new TableCell();
            cell.Text = item["menupath"].ToString();
            row.Cells.Add(cell);
            Table2.Rows.Add(row);


            cell = new TableCell();
            LinkButton lb = new LinkButton();
            lb.CommandArgument = item["menuid"].ToString();
            lb.CommandName = "1";
            lb.Command += new CommandEventHandler(lb_Command);
            lb.Text = "-";
            cell.Controls.Add(lb);
            row.Cells.Add(cell);


            cell = new TableCell();
            LinkButton llb = new LinkButton();
            llb.CommandArgument = item["menuid"].ToString();
            llb.CommandName = "-1";
            llb.Command += new CommandEventHandler(lb_Command);
            llb.Text = "+";
            cell.Controls.Add(llb);
            row.Cells.Add(cell);


            Table2.Rows.Add(row);


        }
    }

    void lb_Command(object sender, CommandEventArgs e)
    {

        //Response.Write(e.CommandArgument.ToString());
        string str_con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        using (SqlConnection con = new SqlConnection(str_con))
        {
            using (SqlCommand cmd = new SqlCommand("Sort", con))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add("@mid", e.CommandArgument.ToString());
                cmd.Parameters.Add("@sig", e.CommandName.ToString());
                con.Open();
                cmd.ExecuteNonQuery();
                Server.Transfer("default.aspx?id=2");
            }

        }

       // Add_Table();
        //throw new NotImplementedException();
    }

    private void Create_Table_Title()
    {
        TableRow row = new TableRow();

        TableCell cell = new TableCell();
        cell.Text = "栏目编号";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "栏目名称";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "栏目类型";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "栏目路径";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "上移";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "下移";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "状态";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "编辑";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = "删除";
        row.Cells.Add(cell);

        Table2.Rows.Add(row);
    }

    protected void LinkButton2_Click(object sender, EventArgs e)
    { 
        table1.Visible = true;
        Table2.Visible = false;

    }

    protected void LinkButton3_Click(object sender, EventArgs e)
    {
       Insert();
       Server.Transfer("default.aspx?id=1");
    }

    private void Insert()
    {
        string str_con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        using (SqlConnection con = new SqlConnection(str_con))
        {
            using (SqlCommand cmd = new SqlCommand("insert menutree values(@menuid,@Parentid,@MenuName,@MenuPath,@menuorder)", con))
            {
                con.Open();
                cmd.Parameters.Add("@menuid", TextBox1.Text);
                cmd.Parameters.Add("@menuorder", TextBox2.Text);
                cmd.Parameters.Add("@Parentid", TextBox3.Text);
                cmd.Parameters.Add("@MenuName", TextBox4.Text);
                cmd.Parameters.Add("@MenuPath", TextBox5.Text);
              
                try
                {
                cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {

                    throw;
                }


            }
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
       
    }

    protected void LinkButton4_Click(object sender, EventArgs e)
    {
        
    }

    protected DataTable create()
    {
        string str_con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        using (SqlConnection con = new SqlConnection(str_con))
        {
            using (SqlCommand cmd = new SqlCommand("select * from menutree where parentid='0' order by ParentID,MenuOrder ", con))
            {
                DataSet ds = new DataSet();
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds);
                }
                return ds.Tables[0];
            }
        }
    
    
    }

}

存储过程:

create proc Sort
@mid nvarchar(20), --移动的MenuID
@sig INT --这里是方向上,下1向下-1向上
AS
begin
SET NOCOUNT ON;

declare @name int --用来存储MenuOrder上一条的 --取值
set @name=(select MenuOrder from (select top 100 ROW_NUMBER() over(order by menuorder) as id,MenuID,MenuOrder
from MenuTree where ParentID='0' order by ParentID, MenuOrder) as ttt where
ttt.id=(select ttt.id+@sig from (select top 100 ROW_NUMBER() over(order by menuorder) as id,MenuID,MenuOrder
from MenuTree where ParentID='0' order by ParentID, MenuOrder ) as ttt where ttt.MenuID=@mid))
--print @name --把前一参数的MenuOrder存起来

declare @menuid varchar(20) --取id
set @menuid=(select MenuID from (select top 100 ROW_NUMBER() over(order by menuorder) as id,MenuID,MenuOrder
from MenuTree where ParentID='0' order by ParentID, MenuOrder) as mmm where
mmm.id=(select ttt.id+@sig from (select top 100 ROW_NUMBER() over(order by menuorder) as id,MenuID,MenuOrder
from MenuTree where ParentID='0' order by ParentID, MenuOrder ) as ttt where ttt.MenuID=@mid))
--print @menuid --把前一参数的MenuOrder存起来

--select * from MenuTree where ParentID='0' order by ParentID,MenuOrder
--print @menuid

declare @name1 int --存当前行的MenuOrder值
set @name1=(select MenuOrder from MenuTree where MenuID=@mid) --取当前行的MenuOrder值
--print @name1

update MenuTree set MenuOrder=@name1 whereMenuID=@menuid
update MenuTree set MenuOrder=@name where MenuID=@mid --修改当前的Menuorder
end

C979170768

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程把找到的记录的MenuOrder值与离他最近的一条进行交换附C#代码

这是效果图片C# Code: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.Sql
复制链接

扫一扫