lecture-10 making external data available locally

7/28/2019 Lecture-10 Making External Data Available Locally

1/20

Modern Programming Language

Lecture-10


2/20

Making External Data Available Locally

Agenda Load external data into a DataTable or DataSet

Return updated DataSet content to an externalsource

Use SQL statements and stored procedures tomanage DataSet content


3/20

Introduction

The disconnected data experience provided byADO.NET revolves around the DataSet classand its supporting objects.

This chapter introduces the DataAdapter classthe class that fulfills disconnected core datapromise.


4/20

Understanding Data Adapters

Data adapters link your external database tables and

your local DataSet-managed tables by issuing SQLstatements.

Anytime you need to get data from the database into aDataSet, the adapter must perform a Fill operation,

issuing a SELECT statement and moving the resultsinto local DataTable instances. You can then updatethe values in those DataTable instances.

When its time to return changes stored in the DataSet

to the database, the data adapters Update operationsends the relevant INSERT, UPDATE, and DELETEstatements to the database to bring the external datastore into line with local changes.


5/20

Diagram of DataAdapter

The following figure shows the components working

on a single database table, Customer.


6/20

Cont The DataAdapter manages a lot of complex activity between the

database and a DataSet or DataTable.

All the classes introduced so far in our all lessonsfrom DataSet toSqlParameter, from DataRow to DataReadercome into playwhen creating instances of a data adapter class.

The System.Data.SqlClient.SqlDataAdapterclass exposes the SQL

Server provider implementation of the adapter. You can also findOLE DB and ODBC variations of the data adapter in the classesSystem.Data.OleDb.OleDbDataAdapter andSystem.Data.Odbc.OdbcDataAdapter, respectively.

All these classes derive from

System.Data.Common.DbDataAdapter, which in turn derives fromSystem.Data.Common.DataAdapter.


7/20

SqlDataAdapter support features SqlDataAdapter provides three general support features in your

application: Record retrieval: Populating a DataTable with database

records represents the minimal functionality of the data adapter.Internally, the SqlDataAdapter uses a DataReader instance toretrieve records out of the database, so you must provide it with aSELECT statement and a connection string. Stored proceduresthat return data rows also work; the adapter will correctly processmultiple record sets returned by the query.

Record updating: Moving modifed data back to externalstorage is a little more in volved. The update operation requiresdistinct INSERT, UPDATE, and DELETE statements to completeits work.

Table and column name mapping: Each data adapter includesa mapping layer that automatically renames tables and columnsas needed while data is passed between local and remotestorage areas.


8/20

Moving Data from Source to Memory

The SqlDataAdapter.Fill method requests data from

SQL Server using a valid SELECT statement or adata-selection stored procedure. After it accesses thedata through an internal SqlDataReader, it moves therecords into the DataTable or DataSet of your choice.

Moving Data into a DataTable: To move data from a database table into a DataTable

instance, set up a new SqlDataAdapterobject and callits Fill method, passing it the instance of the

DataTable.Dim dt As New DataTable() Dim cs As String = "Data source=.;Integrated security=true;Initial catalog=Sale

Dim con As New SqlConnection(cs)

Dim da As New SqlDataAdapter("Select * from customer", con)

da.Fill(dt)

DataGridView1.DataSource = dt

As the data adapter reads the incoming data,

it examines the schema of that data and builds

the columns and properties of the DataTableinstance as needed.


9/20

Cont The data adapter uses the constructor arguments to create a

new SqlCommand instance. It then assigns this instance to its

SelectCommand property, a property that must be set before theSqlDataAdapter can do its data retrieval work.

Pass in a SQL string and SqlConnection pair to theSqlDataAdapter constructor, or just leave off the argumentsaltogether. The SqlDataAdapter class has no connection string or

connection properties, so if you dont provide them with theconstructor, you need to include them with a SqlCommandinstance that you assign to the SqlDataAdapter.SelectCommandproperty directly, as shown here:

Dim dt As New DataTable()

Dim cs As String = "Data source=.;Integrated security=true;Initial catalog=Sale"


Dim da As New SqlDataAdapter()

Dim cmd As New SqlCommand("Select * from customer", con)

da.SelectCommand = cmd

da.Fill(dt)


Neither of the preceding examples openedthe connection explicitly.

If the commands connection isnt open yet,

the Fill method opens it for youand closes

it when the operation

completes.


10/20

Cont.

The SqlDataAdapter.SelectCommand property is a

standard SqlCommand instance, you can use any ofthat command objects features to access the remotedata. This includes adding one or more SqlParameterobjects. You can also execute stored procedure.

Dim dt As New DataTable()

Dim con As New SqlConnection("Data source=.;Integrated security=true;Initial catalog=Sale")

Dim da As New SqlDataAdapter()

Dim cmd As New SqlCommand("dbo.GetOrderOfCustomer", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@custid", SqlDbType.BigInt)

cmd.Parameters("@custid").Value = Me.TextBox1.Text

da.SelectCommand = cmd

da.Fill(dt)



11/20

Moving Data into a DataSet

Moving external data into a waiting DataSet instance

is as easy as filling a DataTable. To import the datainto a DataSet, call the SqlDataAdapter.Fill method,passing it an instance of DataSet.

Dim ds As New DataSet()

Dim cs As String = "Data source=.;Integrated security=true;Initial catalog=Sale"


Dim da As New SqlDataAdapter("Select * from customer", con)

da.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

As with a DataTable load, the DataSet version of Fill will auto-build the schema for you. If you want to preconfgure the DataSetschema, you can build its table by hand or call theSqlDataAdapter.FillSchema method just before you call the Fillmethod.

da.FillSchema(ds, SchemaType.Source)


12/20

Cont

Fill names the first created table in the data set

Table, as is done when filling a DataTable directly.To alter this default name, specify the new name as asecond argument to the Fill method.

Da.Fill(targetSet, "Customer")

The Fill(DataSet) method will import multiple tables ifits SelectCommand includes a batch of SELECTstatements or a stored procedure that returns multipleresult sets. The first table created is still named

Table (by default). Subsequent tables are namednumerically, with the second table given the nameTable1, the third table Table2, and so on.


13/20

Moving Data from Memory to Source

After imported data has been modifed within a

DataTable (with or without a surrounding DataSet),the same SqlDataAdapter that brought the data in canmove the changes back out to the source.

But a little more configuring of the data adapter are

involved. For the return data trip requires setting upthe appropriate data manipulation statements andcalling the SqlDataAdapter.Update method.


14/20

Configuring the Update Commands

The SqlDataAdapter.SelectCommand property

manages the movement of data only from the externalsource to the local DataSet or DataTable.

To move data in the other direction or delete data, youneed to set up three distinct properties:

InsertCommand

UpdateCommand

DeleteCommand

Like SelectCommand, these three properties areSqlCommand instances, each containing a SQLstatement (or stored procedure), a SqlConnectionreference, and parameters.


15/20

The Insert command of DataAdapter ' for Insertion

cmd = New SqlCommand("Insert into customer (custid,custname,Address,Balance)values (@custid,@custname,@Address,@Balance)", con)

cmd.Parameters.Add("@custid", SqlDbType.BigInt, 0, "custID")

cmd.Parameters.Add("@custname", SqlDbType.VarChar, 30, "custname")

cmd.Parameters.Add("@Address", SqlDbType.VarChar, 40, "Address")

cmd.Parameters.Add("@Balance", SqlDbType.Decimal, 0.0, "Balance")

da.InsertCommand = cmd

da.Update(ds.Tables(0))


16/20

The UpdateCommand of DataAdapter ' FOR UPDATION

cmd = New SqlCommand("UPDATE Customer SETcustname=@custname,Address=@Address, Balance=@Balance WHEREcustid=@custid", con)

cmd.Parameters.Add("@custname", SqlDbType.VarChar, 20, "custname")

cmd.Parameters.Add("@Address", SqlDbType.VarChar, 30, "Address")

cmd.Parameters.Add("@Balance", SqlDbType.VarChar, 0.0, "Balance")

cmd.Parameters.Add("@Custid", SqlDbType.BigInt, 0, "custid")

da.UpdateCommand = cmd

da.Update(ds.Tables(0))


17/20

The DeleteCommand of DataAdapter

' for Deletion

cmd = New SqlCommand("DELETE FROM Customer WHERECustid=@Custid", con)

cmd.Parameters.Add("@Custid", SqlDbType.BigInt, 0, "CustId")

da.DeleteCommand = cmd


18/20

Performing the UpdateAfter setting all command properties of Data Adaptoer you simply

call the adapters Update method to move those changes into theexternal database.

You must identify which local source the Update method is to usefor the update, which can be either a DataSet (which updates alltables included in that set) or a DataTable.

Da.Update(localTable)

The Update method examines each row in the specifed DataSetor DataTable deciding which rows require an INSERT, UPDATE,or DELETE action; or no action at all.

For each row that needs updating, the adapter raises its ownOnRowUpdating event just before issuing the SQL command;then raises the related OnRowUpdated event after the row hasbeen changed in the database.


19/20

The SqlCommandBuilder class

It is a class defined by System.Data.SqlClient

namespace and is used to generatecommands(insert,delete,update) that can be usedto reflect DataSet changes back to a sql serverdatabase.

Update method of SqlDataAdapter uses theSqlCommandBuilder's commandsto reflect DataSet changes back to a sql serverdatabase.

Dim SCB New SqlCommandBuilder(da)

The SqlCommandBuilder generate the insert, deleteand update command for the relevant data adaptor.


20/20

Questions???

lecture-10 making external data available locally

Documents