da.Update(ds, “Emp”)
• Based on the state of each DataRow in the mentioned DataTable, the appropriate command of the DataAdapter referenced by “da” is executed.
• If the state of the Row is “Deleted” then DeleteCommand is executed. If the state of the Row is “Modified” then the
“UpdateCommand” is executed and if the state of the row is “New” then the “InsertCommand” is executed.
• Based on the row being executed and the SourceColumn and SourceVersion property values of Parameter, the value for the parameters of the command in execution are set.
SourceColumn property of SqlParameter:
It is the name of DataColumn in DataTable to which a Parameter is binded to i.e., while executing the command the value of the parameter is taken from the mentioned column of the DataRow for which the command is being executed.
SourceVersion property of SqlParameter:
It decides which version (Current or Original) value of the column for a given row should be assigned to the parameter.
Dealing with Concurrency:-
When the changes made to the dataset by one user are updated in the database, the data in the dataset of the other users becomes Dirty and such data should not be updated unless the dataset is refeshed and latest data is modified and updated because if change are made to the dirty data and updated in database, it may override the change made by other users with out looking it those changes.
Solution: The UpdateCommand and DeleteCommand CommantText must be modified to inlcude all the columns so that the Original Values of Column are compared with the values in the database fields.
Ex: Update Emp Set EmpName=@EName, EmpSalay=@ESal
where EmpId=@OrgEId and EmpName=@OrgEName and EmpSalary=@OrgESalary
When DataAdapter#Update is executed and if it executes ethier UpdateCommand or DeleteCommand and finds the number of records effected by execution of this command is zero then it throws “DBConcurrencyException”.
Deccansoft Software Services – MS.NET ADO.NET – Dataset
Dim con As New SqlConnection("server=localhost\sqlexpress;user id=sa;password=dss;database=MSNET") Dim dt As DataTable
Dim WithEvents daEmp As SqlDataAdapter Dim ds As DataSet
Dim dr As DataRow
Private Sub DataSetForm_Load(. . .) Handles MyBase.Load
daEmp = New SqlDataAdapter("Select EmpId, EmpName, EmpSalary from Emp", con) MsgBox(daEmp.SelectCommand.CommandText)
ds = New DataSet()
RefreshEmpIdDropDown() gvEmp.DataSource = ds.Tables(0) ''''''Insert Command'''''''
Dim cmdInsert As SqlCommand Dim p(2) As SqlParameter
cmdInsert = New SqlCommand("insert into Emp(EmpName,EmpSalary) values (@ename,@esalary)", con) p(0) = cmdInsert.Parameters.Add("@ename", SqlDbType.VarChar, 20)
p(0).SourceColumn = "EmpName"
p(0).SourceVersion = DataRowVersion.Current
p(1) = cmdInsert.Parameters.Add("@esalary", SqlDbType.Float) p(1).SourceColumn = "Empsalary"
p(1).SourceVersion = DataRowVersion.Current daEmp.InsertCommand = cmdInsert
''''''''Delete Command''''''''
Dim cmdDelete As SqlCommand
cmdDelete = New SqlCommand("Delete from emp where empid=@orgeid", con) Dim par As SqlParameter
par = cmdDelete.Parameters.Add("@orgeid", SqlDbType.Int) par.SourceColumn = "Empid"
par.SourceVersion = DataRowVersion.Original daEmp.DeleteCommand = cmdDelete
'''''''Update Command'''''''''
Dim cmdUpdate As SqlCommand
cmdUpdate = New SqlCommand("Update Emp set empname=@ename, empsalary=@esalary where empid=@orgeid", con) p(0) = cmdUpdate.Parameters.Add("@orgeid", SqlDbType.Int)
p(0).SourceColumn = "Empid"
p(0).SourceVersion = DataRowVersion.Original
p(1) = cmdUpdate.Parameters.Add("@ename", SqlDbType.VarChar, 20) p(1).SourceColumn = "EmpName"
p(1).SourceVersion = DataRowVersion.Current
p(2) = cmdUpdate.Parameters.Add("@esalary", SqlDbType.Float) p(2).SourceColumn = "EmpSalary"
p(2).SourceVersion = DataRowVersion.Current daEmp.UpdateCommand = cmdUpdate 'Dim sb As New SqlCommandBuilder(daEmp)
'sb.ConflictOption = ConflictOption.CompareAllSearchableValues ‘ orOverwriteChanges
‘The above statement automatically frames the InsertCommand, UpdateCommand and DeleteCommand properties of the
‘mentioned DataAdapter (daEmp) and it does so based on the columns mentioned in the SelectCommand Properties of the
‘DataAdapter. Also for this to work the database table mentioned in the Select statement of SelectCommand must have the
‘PrimaryKey defined in the backend (database).
Deccansoft Software Services – MS.NET ADO.NET – Dataset
'MsgBox(sb.GetInsertCommand().CommandText) 'MsgBox(sb.GetUpdateCommand().CommandText) 'MsgBox(sb.GetDeleteCommand().CommandText)
'For Handling events of DataAdapter in C# do the following here…..
'daEmp.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);
'daEmp.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
End Sub
Private Sub RefreshEmpIdDropDown() ds.Clear()
daEmp.Fill(ds, "Emp") dt = ds.Tables("Emp") cmbId.Items.Clear()
For Each dr As DataRow In dt.Rows cmbId.Items.Add(dr("EmpId")) Next
cmbId.SelectedIndex = 0 End Sub
Private Sub cmbId_SelectedIndexChanged(. . .) Handles cmbId.SelectedIndexChanged Dim drs() As DataRow
drs = dt.Select("empid=" & cmbId.Text) ‘This method return the array of datarow based on the condition given as the first parameter.
dr = drs(0) ‘Because we are filtering based on the EmpId which is PrimaryKey we will have only 1 record.
‘dt.PrimaryKey = New DataColumn(){dt.Columns(“EmpId”)}
'dr = dt.Rows.Find(cmbId.Text) ‘Can be used only if the EmpId is maked as PrimaryKey in DataTable txtName.Text = dr("EmpName").ToString()
If (dr.IsNull("EmpSalary")) Then txtSalary.Text = ""
Else
txtSalary.Text = dr("EmpSalary").ToString() End If
End Sub
Private Sub btnNew_Click(. . .) Handles btnAdd.Click Dim dr As DataRow
dr = dt.NewRow()
‘dr = New DataRow() 'Is invalid because the constructor of the class is Protected dr("Empname") = txtName.Text ‘EmpId is AutoIncrement thus it need not be set.
dr("EmpSalary") = CSng(txtSalary.Text) dt.Rows.Add(dr)
End Sub
Private Sub btnModify_Click(. . .) Handles btnEdit.Click Dim dr As DataRow
Dim drs() As DataRow
drs = dt.Select("empid=" & cmbId.Text) dr = drs(0)
dr("Empname") = txtName.Text
dr("EmpSalary") = CSng(txtSalary.Text) End Sub
Private Sub btnDelete_Click(. . .) Handles btnDelete.Click Dim drs() As DataRow
drs = dt.Select("empid=" & cmbId.Text) d
Dim res As DialogResult r = drs(0)
res = MessageBox.Show("Do you really want to delete the Row", "Delete", MessageBoxButtons.YesNo) If (res = Windows.Forms.DialogResult.Yes) Then
MessageBox.Show("Before Delete: " & dt.Rows.Count)
dr.Delete() 'This only marks the row as deleted but the row is still there in Rows collection of DataTable MessageBox.Show("After Delete: " & dt.Rows.Count)
cmbId.Items.RemoveAt(cmbId.SelectedIndex)
If (cmbId.Items.Count <> 0) Then cmbId.SelectedIndex = 0 End Sub
Deccansoft Software Services – MS.NET ADO.NET – Dataset
daEmp.Update(ds, "Emp") 'This method executes the Insert, Update or Delete Commands of the dataadapter using the data from a given DataRow and the command executed depends upon the RowState property of the datarow.
RefreshEmpIdDropDown()
Catch exp As DBConcurrencyException ‘Please refer to the notes at the beginning of this handout.
MsgBox(exp.Message) dt.Clear()
daEmp.Fill(ds, "Emp") RefreshEmpIdDropDown() End Try
End Sub
‘Event Handler of DataAdapter.
Private Sub daEmp_RowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
Handles daEmp.RowUpdated If (e.StatementType = StatementType.Insert) Then
Dim con As SqlConnection
con = e.Command.Connection ‘As the connection is already open it is not required to reopen the connection.
Dim cmd As New SqlCommand("Select @@Identity", con)
e.Row("EmpId") = cmd.ExecuteScalar() – EmpId of the record inserted is fetched and assigned to the current rows EmpId field.
Dim index As Integer
index = cmbId.Items.Add(e.Row("EmpId")) cmbId.SelectedIndex = index
End If End Sub
Private Sub daEmp_RowUpdating(ByVal sender As Object, ByVal e As SqlRowUpdatingEventArgs)
Handles daEmp.RowUpdating If e.StatementType == StatementType.Update Then
If e.Row("EmpSalary") > 50000 Then
MsgBox("Salary Cannot Be more than 50000") e.Status = UpdateStatus.SkipCurrentRow e.Row.RejectChanges()
End If End If End Sub
Table Mapping : Used for giving DataTable and Column Names which can be different from backend table and field names.
Note: The backend column names are Case Sensitive.
Private Sub btnTableMapping_Click(. . .) Handles btnTableMapping.Click
Dim dtmEmp As New Data.Common.DataTableMapping("Emp", "Employee") daEmp.TableMappings.Add(dtmEmp)
Dim dcm As New Data.Common.DataColumnMapping("EmpId", "ID") dtmEmp.ColumnMappings.Add(dcm)
dtmEmp.ColumnMappings.Add("EmpName", "Name") dtmEmp.ColumnMappings.Add("EmpSalary", "Salary") dt.Clear()
daEmp.Fill(ds, "Emp")
gvEmp.DataSource = ds.Tables("Employee") End Sub
DataRelation:
Dim con As SqlConnection Dim ds As New DataSet()
con = New SqlConnection("server=localhost\sqlexpress;user id=sa;password=dss;database=MSNET") Dim daDept As New SqlDataAdapter("select * from Dept", con)
Dim daEmp As New SqlDataAdapter("select * from Emp", con) daDept.Fill(ds, "Dept")
daEmp.Fill(ds, "Emp") Dim dr As DataRelation
dr = New DataRelation("Dept_Emp", --Relationship Name
ds.Tables("Dept").Columns("DeptId"), // -- Parent Table column reference ds.Tables("Emp").Columns("DeptId"),// - Child Table column refenrece True) //– if true it also creates a Foreign Key Constraint in Child Table.
ds.Relations.Add(dr)
Deccansoft Software Services – MS.NET ADO.NET – Dataset
Dim drDept As DataRow
drDept = ds.Tables("Dept").Select("DeptId=1")(0) ‘Gets the reference to the Dept with DeptId=1 Dim drsEmps As DataRow()
drsEmps = drDept.GetChildRows("Dept_Emp") ‘Dept_Emp is the DataRelation name Dim drEmp As DataRow
Dim str As String = ""
For Each drEmp In drsEmps
str &= drEmp(0) & vbTab & drEmp(1) & vbCrLf Next
MsgBox(str)
MsgBox(ds.Tables("Emp").Rows(0).GetParentRow("Dept_Emp")("DeptName")) – Gets the name of the Department of the first emp in the Emp DataTable Employee.
DataView: One DataTable can have multiple DataViews and each DataView can be Sorted and Filtered independently.
Dim dt As DataTable dt = ds.Tables("Emp") Dim dvEmp As DataView
dvEmp = dt.DefaultView ‘ or dv = New DataView(dt) dvEmp.RowFilter = "EmpSalary > 2000"
Dim str As String = ""
Dim drv As DataRowView For Each drv in dvEmp
str &= drv(0) & " " & drv("EmpSalary") & vbCrLf Next
MsgBox(str)
dvEmp.Sort = "EmpId"
MsgBox(dvEmp.Find(2)) 'Index of the row with EmpId=2
dvEmp.Sort = "EmpName,EmpSalary" ‘Sorted first using EmpName and if name is same sorts using EmpSalary MsgBox(dvEmp.Find(New Object() {"E1", 1000}))‘Gets index of row with EmpName=’E1” & EmpSalary=1000 dvEmp.Sort = "EmpName"
Dim drvs() As DataRowView = dvEmp.FindRows("E1") ‘Gets the DataRowView of the row having EmpName=’E1’
Note: One DataView cannot be using multiple DataTables. Between DataTable and DataView its One to Many relationship.
Creating DataSet Programmatically Dim ds As New DataSet
Dim dt As New DataTable("Table")
Dim dcNumber As New DataColumn("Number", GetType(Integer)) Dim dcStar As New DataColumn("*", GetType(String))
dcStar.DefaultValue = "*"
Dim dcCounter As New DataColumn("Counter", GetType(String)) dcCounter.AutoIncrement = True
Dim dcEquals As New DataColumn("=", GetType(String)) dcEquals.DefaultValue = "="
Dim dcResult As New DataColumn("Result", GetType(String)) dcResult.Expression = "Number * Counter"
ds.Tables.Add(dt)
dt.Columns.Add(dcNumber) dt.Columns.Add(dcStar) dt.Columns.Add(dcCounter) dt.Columns.Add(dcEquals) dt.Columns.Add(dcResult) For i As Integer = 1 To 10 Dim dr As DataRow dr = dt.NewRow()
dr("Number") = Integer.Parse(txtNumber.Text) dt.Rows.Add(dr)
Next
gvTable.DataSource = ds.Tables(0)
Set Primary Key for a DataTable Programmatically
Deccansoft Software Services – MS.NET ADO.NET – Dataset
Adding ForeignKey Programmatically Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint(ds.Tables("Dept").Columns("DeptID"), -- Primary Key ds.Tables("Emp").Columns("DeptID")) – Foreign Key fk.DeleteRule = Rule.SetNull – if Dept is deleted then all the emps in Emp table DeptId is set to Null.
fk.UpdateRule = Rule.Cascade –‘If DeptId is modified in the Dept table it automatically changes in Emp table.
ds.Tables("Emp").Constraints.Add(fk)
Note: Any kind schema changes make to the dataset or datatable connot be reflected in backend.
To Compute second last max salary Dim maxSalary As Decimal
maxSalary = ds.Tables("Emp").Compute("max(empsalary)", Nothing)
MsgBox(ds.Tables("Emp").Compute("max(empsalary)", "EmpSalary<" & maxSalary))
TypedDataSet: It’s a class inherited from DataSet and is generated for a specific table in database schema.
• Right Click on Project Æ Add New Item Æ Select DataSet from the List (EmpDataSet.xsd)
• Drag and Drop the Table from the Server Explorer.
• Right Click on the Table and Select Configure. Change the Method Names if required. You can also add more methods to the TableAdapter.
To Fill DataSet and Show all retrieve all data Dim taEmp As New
EmpDataSetTableAdapters.EmpTableAdapter Dim ds As New EmpDataSet
taEmp.Fill(ds.Emp) Dim s As String = ""
For Each dr As EmpDataSet.EmpRow In ds.Emp.Rows s &= dr.EmpId & vbTab
s &= dr.EmpName & vbTab If (Not dr.IsEmpSalaryNull) Then s &= dr.EmpSalary & vbCrLf End If
Next MsgBox(s) End Sub
To Add a New Row to the DataTable Dim taEmp As New
EmpDataSetTableAdapters.EmpTableAdapter Dim dt As EmpDataSet.EmpDataTable dt = taEmp.GetData()
Dim dr As EmpDataSet.EmpRow dr = dt.NewEmpRow
dr.EmpName = "EName1"
dr.EmpSalary = 10000 dt.Rows.Add(dr) taEmp.Update(dt) End Sub
Bulk Copy
Dim sb As New SqlBulkCopy("Connection String of Destination") sb.DestinationTableName = "DestTablename"
sb.ColumnMappings.Add("SourceColumnName","DestColName") ‘Do this for all the columns of the table ‘sb.WriteToServer(dr) ‘Read on DataReader and copy to new table
‘sb.WriteToServer(dt) ‘Read from DataTable and copy to new table
‘sb.WriteToServer(drs) ‘Read from Array of DataRow and copy to new table