|
Introduction
The DataAdapter provides a set of methods and properties to retrieve and save data between a DataSet and its source data store. Frequently people have asked me I am getting the following error : Dynamic SQL generation is not supported against multiple base table. How we can use data adaptor to update database when queries are based on joins. So I spent some time to write this article.
Some of them say its not possible, well they are partially right , meaning there is no ready made way to do so, but one who knows how DataAdapter works then he can figure out ways of doing so. If you look at object model of ADO.NET you will find DataAdapter contains 4 properties UpdateCommand, DeleteCommand, InsertCommand and SelectCommand. The Fill method of the DataAdapter calls the SELECT command while Update method calls INSERT, UPDATE or DELETE command for each changed row. One of the great features about the DataAdapter object is that these co mmands can be set explicitly at runtime using CommandBuilder object, alternatively you can explicitly providing the INSERT, UPDATE, and DELETE commands at design time.
Coming back to original question, here I will show you 4 different way to achieve the results and will understand pros and corns of each
- Before we start Create Table in SQL database and insert few records
CREATE TABLE [dbo].[Dept] (
[DeptNo] [numeric](18, 0) NOT NULL ,
[Dname] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO |
CREATE TABLE [dbo].[Emp] (
[Empno] [numeric](18, 0) NOT NULL ,
[Ename] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Salary] [numeric](18, 0) NULL ,
[Deptno] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
|
ALTER TABLE [dbo].[Dept] WITH NOCHECK ADD
CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED
(
[DeptNo]
) ON [PRIMARY]
GO |
ALTER TABLE [dbo].[Emp] WITH NOCHECK ADD
CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED
(
[Empno]
) ON [PRIMARY]
GO |
|
|
- Create a new windows application and Insert DataGrid with two buttons "Fill" and "Update"
Method#1 Setting Relations: Drag and drop SQLDataAdaptor1 object and select EMP during wizard. Drag and drop SQLDataAdaptor2 and select Dept table during wizard. From property window of both SQLDataAdaptor1 and 2, Click on "Generate DataSet" to create typed dataset. Bring both table's data from database into dataset by calling both DadaAdaptor's Fill and then set a relation between them using DataSet's Relation.Add Method, bind the datatable DEPT to datagrid using datagrid's datasource property. (MyDataSet1.Dept ). When relation is set in dataset between two tables and if the grid is bind with parent table , datagrid is smart enough to display in a parent-child relation.
Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(Me.MyDataSet1)
Me.SqlDataAdapter2.Fill(Me.MyDataSet1)
Me.MyDataSet1.Relations.Add(Me.MyDataSet1.Dept.DeptNoColumn, Me.MyDataSet1.Emp.DeptnoColumn)
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Me.SqlDataAdapter1.Update(Me.MyDataSet1)
Me.SqlDataAdapter2.Update(Me.MyDataSet1)
End Sub
This technique allows you to update both tables , but if you wish to display data from both tables in one single view, you can't, you guess right in all situations this is not what you desire.
So lets write a join query
Method #2. Specifying own UpdateCommand. Create a new Windows Form Drag and drop DataAdaptor , in wizard write following query
- SELECT Emp.Empno, Emp.Ename, Emp.Salary, Dept.DeptNo, Dept.Dname, Dept.Location FROM Dept INNER JOIN Emp ON Dept.DeptNo = Emp.Deptno
At the end wizard you will get error messages that it could not create appropriate statements for update, delete and insert. this is obvious as you are using joins in the query. Select "Finish". Select DataAdaptor and in TableMapping Properties change dataset table name to "Dept_Emp". From property window select "Generate DataSet"
From DataAdaptor's property window click UpdateCommand and select "New" to create new UpdateCommand object. select commandText property and write your own statement as below
-
(If SQLDataAdaptor then ) UPDATE Emp SET Ename = @p1, Salary = @p2, Deptno = @p3 WHERE (Empno = @p4)
(If OLEDBDataAdaptor then ) UPDATE Emp SET Ename = ?, Salary = ?, Deptno = ? WHERE (Empno = ?)
Before selecting "OK" check "Regenerate parameters collection for this command" and you are almost through. don't forget to set connection object.(SQLConnection1 in our case) in Connection property of UpdateCommand. Drag and drop datagrid object and set DataSource to "Dept_Emp" table.
This technique will allow you to be able to update any one underlying table. on similar lines you can write your own Delete and Insert commands. this technique is efficient and in most situations you will prefer.
lets get on with something more, in some situations you would like to update more then one table. like during my .NET training one had asked me that my application requires to record changes made to tables into "Audit Tables". Well at very first I would suggest to use triggers for such situations, still if you want more control over UpdateCommand then ..
Method #3 Playing with DataAdaptor events.
RowUpdating : This event is raised before a DML (Insert,Update,Delete) operation is to take place on a particular row. The RowUpdating event can be used for to gain additional control on the update operation like retaining old values before the changes are being made,cancel a particular update based on certain business logic
RowUpdated : This event is raised after a DML operation on a particular row is complete. The RowUpdated event can be used to respond to errors and exceptions that occur, have a retry logic and so on.
FillError : This event is raised when an error occurs during a FILL operation. In case of a FillError , the row that caused it is not added to the DataTable. Hence this event can be used to resolve that error and add it to the DataTable or exclude it and continue the Fill operation
For more details you should go through this URL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaddingremovingadonetproviderevents.asp)
Continuing in the same code of Method#2, create a handler for event RowUpdating and insert following. I am not doing any thing sensible here but this is just to give you an idea how you can make your things work. I am checking if the user has done any changes to Field2 (i.e Dname), by finding if there is difference between original and current value, if found I am creating a new command object to fire update statement on Dept table using same connection object
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Update(Me.MyDataSet1)
Me.SqlConnection1.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("Update Success")
End Sub
Private Sub SqlDataAdapter1_RowUpdating(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles SqlDataAdapter1.RowUpdating
If e.Row.Item(1, DataRowVersion.Current) <> e.Row.Item(1, DataRowVersion.Original) Then
Dim x As New SqlClient.SqlCommand("Update Dept set dname=@p1 where Deptno=@p2")
x.Parameters.Clear()
x.Parameters.Add("@p1", e.Row.Item(1))
x.Parameters.Add("@p2", e.Row.Item(0))
x.Connection = e.Command.Connection
x.ExecuteNonQuery()
' e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
Based on your business logic if you wish not to update any particular record but continue updating the rest then uncomment the above line in green. (*Remember: this is not going to do UNDO the updates made to DEPT table).
I did changes in ename of Record1 and Dname and Ename of record 2 when you click update ename of record2 will not change where as rest changes will be applied.
This technique is less efficient then Method#4(The Ultimate Way-Stored Procedure), as you are increasing lots of trip to the server, but really useful when the underlying database does not support stored procedure
Method #4. The Ultimate Way-Using Stored Procedure: This is what I will prefer, to take best advantage of available facility. also sometimes we want to avoid using SQL DML statements directly in our code for security reasons. Create a new WindowsForm with DataGrid and 2 Buttons (Fill and update). Drag DataAdaptor, in wizard select "Create New Stored Procedure" , Select one table and click next you will see the screen like below.
 based on the select statement wizard will create four stored procedures. but again here if your query is based on joins wizard will not be able to create stored procedures
so what we will do is go to back step and choose select from existing stored procedure. but before that lets create a simple stored procedure as shown below.
CREATE PROCEDURE [EMPUpdateCommand]
(
@Original_Empno numeric(18),
@Ename char(10),
@Salary numeric(18),
@Original_Deptno numeric(18),
@Dname char(10),
@Location char(10)
)
AS
SET NOCOUNT OFF;
UPDATE dbo.Emp SET Ename = @Ename, Salary = @Salary, Deptno = @Original_Deptno WHERE (Empno = @Original_Empno);
Update dbo.Dept Set Dname=@Dname , location=@location where deptno=@original_deptNo;
GO
- Drag and drop SQL DataAdaptor
- Select Connection
- Select "Use Existing Stored Procedure"
- Select Right procedures as seen in screen shot.
Complete all step to "Finish" and Wow! we are through, fill in code for events of two buttons (Fill and Update as below)
Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(Me.MyDataSet1)
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Me.SqlDataAdapter1.Update(Me.MyDataSet1)
End Sub
Run the code make some changes and click on update.
Hurray!.
Conclusion
Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET's DataAdaptor allows us to use stored procedures seamlessly. The combination of these two allows us to create very powerful applications rapidly.
That's it folks, I believe this article will clear your doubts and gives sufficient hits to accomplish your tasks. and with that I too get back with my task (2.00am Time to Sleep)
if you have any queries regarding article use my forum to post your questions or if you find some typographical error mail me at blogs@jigneshdesai.com
|