repeater插入，多选删除，条件查询实例（存储过程实现）

来源：互联网发布：淘宝搞笑好评大全编辑：程序博客网时间：2024/05/18 21:48

前台：基本实现页面，全选js等

<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        function Check() {
            var oElements = document.getElementsByTagName("input");
            //var chk = document.getElementsByName("chkA");
            var chk = document.getElementById("Repeater1_chkAll");
            alert(chk);
            var bIsCheckde = chk.checked;
            for (var i = 0; i < oElements.length; i++) {
                if (oElements[i].type == "checkbox") {
                    oElements[i].checked = bIsCheckde;
                }
            }
        }

        function IsCheckBox(chk) {
            if (chk.type == 'checkbox') return true;
            else return false;
        }

        function IsMatch(id, ChildId) {
            var sPattern = '^Repeater1.*' + ChildId + '$';
            var oRegExp = new RegExp(sPattern);
            if (oRegExp.exec(id))
                return true;
            else
                return false;
        }
        function test() {
            alert("test sucessed !!");
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <input type="button" value="test" onclick="test()" />
        <p>
            所需要的参数：</p>
        <asp:Label ID="Label1" runat="server" Text="siteID" AssociatedControlID="TextBox1"></asp:Label><asp:TextBox
            ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Label ID="Label2" runat="server" Text="tmp_amt" AssociatedControlID="TextBox2"></asp:Label><asp:TextBox
            ID="TextBox2" runat="server"></asp:TextBox>
        <asp:Label ID="Label3" runat="server" Text="inv_num" AssociatedControlID="TextBox3"></asp:Label><asp:TextBox
            ID="TextBox3" runat="server"></asp:TextBox>
    </div>
    <asp:Button ID="Button1" runat="server" Text="插入" OnClick="Button1_Click" />
    <asp:Button ID="Button3" runat="server" Text="删除" OnClientClick="return confirm('确定要删除吗？')"
        OnClick="Button3_Click" />
    <asp:Button ID="Button4" runat="server" Text="查询" OnClick="Button4_Click" />
    <p>
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="显示" />
    </p>
    <asp:Repeater ID="Repeater1" runat="server">
        <HeaderTemplate>
            <table>
                <tr>
                    <td>
                        <input name="chkA" id="chkAll" runat="server" type="checkbox" onclick="Check()" title="全选" />全
                    </td>
                    <td>
                        ID
                    </td>
                    <td>
                        siteID
                    </td>
                    <td>
                        tmp_amt
                    </td>
                    <td>
                        inv_num
                    </td>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:CheckBox ID="chkItem" runat="server" />
                </td>
                <td>
                    <asp:Label ID="lblID" runat="server" Text='<%# Eval("id") %>'></asp:Label>
                </td>
                <td>
                    <%# Eval("siteID") %>
                </td>
                <td>
                    <%# Eval("tmp_amt")%>
                </td>
                <td>
                    <asp:Label ID="lblinv" runat="server" Text='<%# Eval("inv_num") %>'></asp:Label>
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    </form>
</body>

后台：插入，显示，查询，删除（调用存储过程）

protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string siteid = TextBox1.Text.ToString();
            string tmpAmt = TextBox2.Text;
            string invNum = TextBox3.Text;
            string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("procAdd", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@siteid", siteid);
            cmd.Parameters.AddWithValue("@tmp_amt", tmpAmt);
            cmd.Parameters.AddWithValue("@inv_num", invNum);
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con.Close();

}

        protected void Button2_Click(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
            SqlConnection con = new SqlConnection(constr);
            string sqlstr = "select * from T_inv";
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlstr,con);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con.Close();

}

        protected void Button3_Click(object sender, EventArgs e)
        {
            string inv = "";
            for (int i = 0; i < this.Repeater1.Items.Count; i++)
            {
                CheckBox cbox=(CheckBox)this.Repeater1.Items[i].FindControl("chkItem");
                if (cbox.Checked == true)
                {
                    if (inv == "")
                    {
                        inv = ((Label)this.Repeater1.Items[i].FindControl("lblinv")).Text ;

                    }
                    else
                    {
                        inv += "," + ((Label)this.Repeater1.Items[i].FindControl("lblinv")).Text ;

                    }
                }
            }
            string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("procDel", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@inv", inv);
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con.Close();

            //string sqlstr = "delete from Testproc where inv_num in (" + inv + ")" + " select * from testproc ";
            //string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
            //SqlConnection con = new SqlConnection(constr);
            //con.Open();
            //SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, con);
            //DataSet ds = new DataSet();
            //adapter.Fill(ds);
            //DataTable dt = ds.Tables[0];
            //Repeater1.DataSource = ds;
            //Repeater1.DataBind();
            //con.Close();

        //    string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
        //    SqlConnection con = new SqlConnection(constr);
        //    SqlCommand cmd = new SqlCommand("delProc", con);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.AddWithValue("@inv", inv);
        //    con.Open();
        //    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //    DataSet ds = new DataSet();
        //    adapter.Fill(ds);
        //    DataTable dt = ds.Tables[0];
        //    Repeater1.DataSource = ds;
        //    Repeater1.DataBind();
        //    con.Close();
       }

        protected void Button4_Click(object sender, EventArgs e)
        {
            string siteid = TextBox1.Text.ToString();
            string tmpAmt = TextBox2.Text;
            string invNum = TextBox3.Text;
            string constr = ConfigurationManager.ConnectionStrings["proc1Connectstring"].ToString();
            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("procQuery", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@invNum", invNum);
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con.Close();
        }

存储过程

插入：

ALTER procedure [dbo].[procAdd]
  @siteid varchar(10),
  @tmp_amt decimal(18,4),
  @inv_num varchar(20)
as
insert into T_inv values(@siteid,@tmp_amt,@inv_num);
select * from T_inv

模糊查询：

ALTER procedure [dbo].[procQuery]
@invNum varchar(20)
as
select * from T_inv where inv_num like '%'+@invNum+'%'

删除：调用自定义函数化字符串为数组

ALTER procedure [dbo].[procDel]
@inv varchar(500)
as
begin
declare @next int
declare @length int
set @next=1
set @length=dbo.Get_StrArrayLength(@inv,',')
while @next<=@length
begin
declare @item varchar(20)
set @item=dbo.Get_StrArrayStrOfInded(@inv,',',@next)
delete from t_inv where inv_num=@item;
set @next=@next+1
end
select * from t_inv
end

函数：

取字符串中的项数：

ALTER function [dbo].[Get_StrArrayLength]
(
@str varchar(1024),
@split varchar(10)
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
  begin
  set @start=@location+1
  set @location=charindex(@split,@str,@start)
  set @length=@length+1
  end
return @length
end

取得字符串中的项：

ALTER function [dbo].[Get_StrArrayStrOfInded]
(
@str varchar(1024),
@split varchar(10),
@index int
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=LTRIM(@str)
set @start=1
set @next=1
set @seed=LEN(@split)
set @location=CHARINDEX(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location=0
select @location=len(@str)+1

return substring(@str,@start,@location-@start)
end