Move and delete multiple rows in database table using EntityFramework in ASP.Net

Last Reply 2 months ago By pandeyism

Posted 2 months ago

i have a table members with column age 

name       age        dob         location
 rick     19 3/3/2001 kl
 fred      19 12/2/2002 Gl
 grace   18 8/1/2001 fh
 amos      17 9/3/1999 gh

using these code am able to increase the ages by 1 with a click Dim db As New DataContext

 

Dim db As New DataContext
        Dim MembersRecords = From p In db.members
                             Select p
        Dim age As Integer = 0

        For j = 1 To MembersRecords.Count()
            If MembersRecords.ToList(j - 1).age <> "" Then
                age = Val(MembersRecords.ToList(j - 1).age)
            Else
                age = 0
            End If
            age = age + 1
            MembersRecords.ToList(j - 1).age = age

        Next
        db.SubmitChanges()

    End Sub
name       age        dob         location
 rick     20 3/3/2001 kl
 fred      20 12/2/2002 Gl
 grace   19 8/1/2001 fh
 amos      18 9/3/1999 gh

with the same button click i want to select and move memebers at age 20 to a diffent table called 'old members' and deleting it from the members table

any help please

Posted 2 months ago

Hi rickabbam,

Please refer below sample.

HTML

<div>
    <asp:GridView ID="gvMembsers" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="id" HeaderText="id" />
            <asp:BoundField DataField="name" HeaderText="name" />
            <asp:BoundField DataField="age" HeaderText="age" />
            <asp:BoundField DataField="dob" HeaderText="dob" />
            <asp:BoundField DataField="location" HeaderText="location" />
        </Columns>
    </asp:GridView>
    <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
    <br />
    <br />
    <asp:GridView ID="gvOldmembers" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="id" HeaderText="id" />
            <asp:BoundField DataField="name" HeaderText="name" />
            <asp:BoundField DataField="age" HeaderText="age" />
            <asp:BoundField DataField="dob" HeaderText="dob" />
            <asp:BoundField DataField="location" HeaderText="location" />
        </Columns>
    </asp:GridView>
</div>

Namespaces

C#

using TestModel;

Vb.Net

Imports TestModel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    TestEntities test = new TestEntities();
    gvMembsers.DataSource = from m in test.members
                            select m;
    gvMembsers.DataBind();
}

protected void btnInsert_Click(object sender, EventArgs e)
{
    TestEntities test = new TestEntities();
    var membersRecords = (from m in test.members
                            select m).ToList();
    int age = 0;
    for (int i = 1; i < membersRecords.Count() + 1; i++)
    {
        if (membersRecords[i - 1].age.ToString() != "")
        {
            age = int.Parse(membersRecords[i - 1].age.ToString());
        }
        else
        {
            age = 0;
        }
        age = (age + 1);
        membersRecords[i - 1].age = age;
        test.SaveChanges();
    }
    this.BindGrid();       

    var memberOldRecord = (from o in test.members
                            where o.age >= 20
                            select o).ToList();
    for (int j = 0; j < memberOldRecord.Count(); j++)
    {
        using (TestEntities test1 = new TestEntities())
        {
            Oldmember ol = new Oldmember
            {
                name = memberOldRecord[j].name,
                age = memberOldRecord[j].age,
                dob = memberOldRecord[j].dob,
                location = memberOldRecord[j].location
            };
            test1.AddToOldmembers(ol);
            test1.SaveChanges();
        }
    }
    this.BindGrid();        

    using (TestEntities test2 = new TestEntities())
    {
        var mem = (from c in test2.members
                    where c.age >= 20
                    select c).ToList();
        for (int i = 0; i < mem.Count(); i++)
        {
            member member = mem[i];
            test2.DeleteObject(member);
            test2.SaveChanges();
        }
        gvOldmembers.DataSource = mem;
        gvOldmembers.DataBind();
        this.BindGrid();
    }       
}

Vb.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim test As TestEntities = New TestEntities()
    gvMembsers.DataSource = From m In test.members Select m
    gvMembsers.DataBind()
End Sub

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim test As TestEntities = New TestEntities()
    Dim membersRecords = (From m In test.members Select m).ToList()
    Dim age As Integer = 0
    For i As Integer = 1 To membersRecords.Count() + 1 - 1
        If membersRecords(i - 1).age.ToString() <> "" Then
            age = Integer.Parse(membersRecords(i - 1).age.ToString())
        Else
            age = 0
        End If
        age = (age + 1)
        membersRecords(i - 1).age = age
        test.SaveChanges()
    Next
    Me.BindGrid()
    Dim memberOldRecord = (From o In test.members Where o.age >= 20 Select o).ToList()
    For j As Integer = 0 To memberOldRecord.Count() - 1
        Using test1 As TestEntities = New TestEntities()
            Dim ol As Oldmember = New Oldmember With {
                .name = memberOldRecord(j).name,
                .age = memberOldRecord(j).age,
                .dob = memberOldRecord(j).dob,
                .location = memberOldRecord(j).location
            }
            test1.AddToOldmembers(ol)
            test1.SaveChanges()
        End Using
    Next
    Me.BindGrid()

    Using test2 As TestEntities = New TestEntities()
        Dim mem = (From c In test2.members Where c.age >= 20 Select c).ToList()
        For i As Integer = 0 To mem.Count() - 1
            Dim member As member = mem(i)
            test2.DeleteObject(member)
            test2.SaveChanges()
        Next
        gvOldmembers.DataSource = mem
        gvOldmembers.DataBind()
        Me.BindGrid()
    End Using
End Sub

Screenshot