Sheritlw
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
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
ASKER
Wouldn't that cause a lot of hits to the database?
Thanks
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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].Te
// Save your line.
}