Link to home
Start Free TrialLog in
Avatar of Sheritlw
SheritlwFlag for United States of America

asked on

Linq to SQL inserting/updating gridview using stored procedure

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

Avatar of BurnieP
BurnieP
Flag of Canada image

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.
}

Avatar of Sheritlw

ASKER

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

Thanks
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.
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
ASKER CERTIFIED SOLUTION
Avatar of BurnieP
BurnieP
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial