Linq to SQL inserting/updating gridview using stored procedure

Sheritlw
Sheritlw used Ask the Experts™
on
I have a form that has 3 textboxes, 1 checkbox and a gridview.
There is a 1 to many relationship between the 3 textboxes/checkbox and the gridview.
Since I need to have a PK from one of the textboxes for the grid, I am binding the grid to a table in code behind.

For the insert and update, I have a single stored procedure.
How do I  insert and update all the rows in the gridview?
Please provide an example,
Thanks

Protected Sub grdDefaults_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles grdDefaults.Load
        Dim i As Int32 = CheckDefaultID()

        If i > 0 Then
            Dim dc As New SalonDataClassesDataContext
            Dim id = From p In dc.DefaultWorkDays Where p.DefaultID = i

            Me.grdDefaults.DataSource = id
            Me.grdDefaults.DataBind()
        Else
            CheckGrid(Me.grdDefaults)
        End If
    End Sub

    Protected Sub buttDefaultTaxSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttDefaultTaxSave.Click
        Dim RetVal As Int32 = 0
        Dim u As New SalonUtilities
        If u.IsSessionActive Then
            Dim dc As New SalonDataClassesDataContext
            Dim sUserID As String = Session("UserID").ToString
            Dim g As New Guid(sUserID)
            Dim i As Int32 = CheckDefaultID()
            Dim dprodTax As Decimal = Decimal.Parse(Me.txtProductTax.Text)
            Dim dServTax As Decimal = Decimal.Parse(Me.txtServiceTax.Text)



            Try
                Dim s = dc.stp_Defaults_Insert_Update(i, g, dServTax, dprodTax, Me.chkEmailReminder.Checked, RetVal)
                i = RetVal
                Me.txtDefaultID.Text = i
                Try

                    'Dim w =dc.stp_DefaultWorkDays_Insert_Update(
                Catch ex As Exception

                End Try
            Catch ex As Exception
                MsgBox("There was a problem saving your data.  Please report the following message to the web administrator. " & ex.Message)

            End Try

        End If
    End Sub

    Private Function CheckDefaultID() As Int32
        Dim iDefaultiD As Int32 = 0
        If IsNumeric(Me.txtDefaultID.Text) Then
            iDefaultiD = Int32.Parse(Me.txtDefaultID.Text)
        Else
            iDefaultiD = 0
        End If

        Return iDefaultiD
    End Function


ALTER PROCEDURE [dbo].[stp_DefaultWorkDays_Insert_Update]
	@DefaultWorkDaysID int,
	@DefaultID int,
	@WorkDay nchar(10),
	@StartTime int,
	@EndTime int,
	@RecID int Output
	
AS
BEGIN

If @DefaultWorkDaysID > 0
begin

	Update DefaultWorkDays set WorkDay = @WorkDay, StartTime = @StartTime,
	EndTime = @EndTime where DefaultWorkDaysID = @DefaultWorkDaysID
	set @RecID = @DefaultWorkDaysID

end
Else
Begin
	INSERT INTO [dbo].[DefaultWorkDays] (
		
		DefaultID,
		WorkDay,
		StartTime,
		EndTime
		)
	VALUES (
		
		@DefaultID,
		@WorkDay,
		@StartTime,
		@EndTime
	)

	SELECT @RecID = Scope_Identity()
END
End

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You can loop through all the gridview rows.  I am a C# programmer but it would look like this.


foreach(GridViewRow oRow in GridView.Rows){
   // get your values for the row..
   string s = oRow.Cells[0].Text;
   int i = int.Parse(oRow.Cells[1].Text);
   
   // Save your line.
}

Author

Commented:
Wouldn't that cause a lot of hits to the database?

Thanks

Commented:
Indeed.

You are using a stored procedure to insert/update so you will have to execute it for every row.

Another option would be to create a SQL CommandText statement.  While looping, you create your sql insert/update statement and separate every insert/update with ";".

You can then execute one statement containing every insert/update.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
I don't have to use the stored procedure to insert/update.
When I bound the datacontext to the grid in the code behind, will that allow me to use the insert/update from the grid?
I am new to both asp.net and linq to sql and I haven't been able to find any complete examples.  

Thanks
Commented:
Here is a code snippet (unfortunately it is in C# since I am developing in C#).  I think it could give you a good idea how to loop through gridview rows, build a collection of object from them, then save the collection using LINQ.

                  List<OrderSmallList> oList = new List<OrderSmallList>();
                  foreach (GridViewRow oItem in grdTransferReady.Items) {
                        oList.Add(new OrderSmallList {
                              IsProcessed = ((CheckBox)oItem.FindControl("chkSelect")).Checked,
                              ProductID = new Guid(((Label)oItem.FindControl("lblProductIDValue")).Text),
                              ID = int.Parse(((Label)oItem.FindControl("lblIDValue")).Text),
                              ProductCode = ((Label)oItem.FindControl("lblProductCodeValue")).Text,
                              OrderID = int.Parse(((Label)oItem.FindControl("lblOrderIDValue")).Text),
                              QtyMissing = int.Parse(((Label)oItem.FindControl("lblMissingValue")).Text),
                              InStock = int.Parse(((Label)oItem.FindControl("lblInStockValue")).Text),
                        });
                  }
                  using (dbContext db = new dbContext("Insert Connection String")) {
                        foreach (OrderSmallList oOrder in oList) {
                              db.Orders.InsertOnSubmit(oOrder);
                        }
                        db.SubmitChanges();
                  }
hi,
Store the data in a HashTable first. And than insert it into database [via.. SP] using bulk insert technique.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial