Microsoft



ISDV 165

Lecture #5: - Part I

is a group of classes and interfaces provided by the .NET framework to interact with any data source. The data source can be RDBMS such as Oracle, Microsoft SQL Server, DB2 or MySQL. The data source can be File and directory systems, Excel files, or XML files. In fact and the .NET framework provide native support for XML.

works with a disconnected set of data. After the gets the data from the source data, it closes the connection. This improves the application efficiency and scalability.

In order to talk to a data source, we need a data provider. The following data provider namespaces are included in the :

• System.Data.SqlClient: A provider specific for connecting to Microsoft SQL Server.

• System.Data.OleDb: A provider for connecting to any data source with an available OLEDB provider.

• System.Data.Odbc: A provider for connecting to any data source that has an available ODBC driver.

• System.Data.OracleClient: A provider for connecting to Oracle database server.

The following diagram describes part of the architecture for :

Data providers consist of many objects. The important ones are:

• Connection: Used to connect to a data source. The connection string specifies the required information to connect to the data source. It contains information such as the data provider used to make the connection, the name of the data source, and security information.

Depending on the way SQL server is installed, we can connect to the server using one of two methods: Windows authentication or SQL authentication. Windows authentication method requires a Windows account on the machine containing the SQL server. The SQL authentication method requires the use of the SQL server admin id/password.

The following connection string can be used to connect to Microsoft SQL server using Windows authentication method:

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

And the following connection string can be used to connect to Microsoft SQL server using SQL authentication method:

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

• Command: Represents an SQL statement or stored procedure to execute against a database.

The CommandType property of the Command object determines the command kind. Possible values are:

o Text: Indicates that the CommandText property of the Command object will be a SQL statement. A Command can have multiple SQL statements separated by semicolon. If we have a command that contain multiple select statements, the DataReader will return multiple result sets. The first result set is returned by default and to access the next result set, we have to call the method NextResult. The following code excerpt shows how to loop through the multiple result sets:

do

{

while(rd.Read())

{}

}

while(rd.NextResult());

o StoredProcedure indicates that the CommandText property of the Command object contains the name of a database stored procedure.

o TableDirect: Indicates that the CommandText property will be a table name. The result if executing the command will be all the columns and rows from the specified table.

• DataAdapter: Represents a bridge between a Data set and a data provider object and is used to retrieve and save data. A Data Set is an in-memory cash of data. It exposes a hierarchical object model of tables, rows, and columns with the constraints and relationships defined for the dataset.

• DataReader: Provides a way of reading a forward-only stream of data rows from a data source. Data reader is limited to single table, view or stored procedure. The object can be constructed by calling ExecuteReader method of the command object. Data retrieved using the DataReader cannot be modified.

• CommandBuilder: Automatically generates single-table commands used to save changes made to a Data Set to the database.

The namespace System.Data contains classes that constitute the architecture. Some of these objects are listed below:

• The DataAdapter is generally used to bridge data communication between a single DataTable in a DataSet object and a single database table.

• A DataSet can contain multiple DataTable objects. Therefore we can create one DataAdapter for each database table we are interested in. To fill the DataSet, we call the Fill method of each DataAdapter created.

• DataTable: Represents one table within a DataSet.

• DataView: Represents a view of data in a DataTable object.

• DataRow: Represents a row of data in a DataTable .

• DataColumn: Represents the schema of a column in a DataTable.

• Constraint: Represents a constraint that can be enforced on one or more DataColumn objects.

• DataRelation object represents a relationship between two columns in two different tables. If we have n tables, then we will need n-1 DataRelation objects to link these tables.

• When a relationship is established between two tables, we can use the methods GetChildRows and GetParentRow of the DataRow object to retrieve child and parent rows.

• There are many sources of errors that can happen when we update data. For example trying to update data modified by other user. The RowUpdate event of the DataAdapter is fired after attempting to update a row and before an exception is thrown. We can use the RowUpdate event handler to handle update errors. One of the arguments of the UpdateRow event is a class derived from the RowUpdateEventArgs. This argument provides information about the error. The Status property of the argument returns the UpdateStatus of the command. The state property has 4 possible values:

o Continue: The state has this value if no error has occurred.

o ErrorsOccurred: Indicates that one or more errors have occurred while attempting to update the current row.

o SkipAllRemainingRows: Indicates that the update for the current row and all of the remaining rows should be skipped.

o SkipCurrentRow : Indicates that the update for the current row should be skipped, but the update for the remaining records should continue.

Other properties of the argument are:

o Errors : return errors generated by the data provider.

o RecordsAffected: Returns the number of records affected by the update.

o Row: return the row that was updated.

• The DataView exposes 3 properties that determine whether we can update the data contained in the DataView or not. These properties are: AllowDelete, AllowEdit and AllowNew. Be default a Data View object is editable. If we want to make a read only DataView, we should set the above properties to false.

• The property Sort of a DataView object is used to sort data either in ascending (default) or descending order.

• The property RowFilter of the DataView object is used to filter data contained in the view. The syntax of this property must follows the syntax of the SQL where clause. String literals must be enclosed within a single quote and dates are enclosed within #.

• The RowState property of the DataView object allows to filter DataRow objects based on their state. Possible values of this property are: Unchanged, Added,Deleted,OriginalRows, CurrentRows, ModifiedCurrent and ModifiedOriginal.

• We can perform data transaction as shown in the following example:

System.Data.SqlClient.SqlTransaction t=null;

try

{

sqlConnection1.Open();

t=sqlConnection1.BeginTransaction();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

mandType=CommandType.Text;

mandText="insert into region values (787,'test')";

System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();

mandType=CommandType.Text;

mandText="insert into region values (788,'test1')";

cmd.Transaction=t;

cmd1.Transaction=t;

cmd.ExecuteNonQuery();

cmd1.ExecuteNonQuery();

mit();

}

catch(Exception )

{

t.Rollback();

}

finally

{

sqlConnection1.Close();

}

Below we describe how to work with a data source using the DataAdapter along with the DataSet objects:

• To retrieve data from a data source, we can use the DataAdapter to populate a DataSet object. We can bind data in a DataSet to data web control such as: DataGrid, Repeater or DataList.

• To add a new record to the data source, add a new row to the DataTable object in the DataSet, then call Update method of the DataAdapter. For update method to work, we need to provide the InsertCommand property of the command object.

• To update data, assign new value to the item in the DataRow object we want to change, then call update method of the DataAdapter. For update method to work, we need to provide the UpdateCommand property of the command object.

• To delete a row from the data source, we can call delete method of the DataRow object, and then call update method of the DataAdapter object. For update method to work, we need to provide the DeleteCommand property of the command object.

We can use the CommandBuidler object to create the InsertCommand, UpdateCommand, and DeleteCommand properties of the Command object based on a SelectCommand. Notice that if you do not call update method, changes will be made to the DataSet only and not to the original data source.

As an alternative approach, we can use the Command object to issue direct SQL commands against the data source. To query a data source, we call the ExecuteReader method of the Command object. The result of this method is placed in a DataReader object. To execute insert, update, or delete commands, we use ExecuteNonQuery method of the Command object.

The ExecuteScalar method of the Command object Executes a query, and returns the first column of the first row in the result set returned by the query as a .NET Framework data type. Extra columns or rows are ignored.

Examples:

Ex1:

The following code excerpt tests the connection to SQL Server. We will use the ExecuteScalar method of the Command object to retrieve the total number of customers in the Customers table.

Following is the user interface:

Execute Scalar Example

And the code behind is shown below:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class executeScalar : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "Select count(*) from customers";

string res = cmd.ExecuteScalar().ToString();

Response.Write("Number of records in customers table is " + res);

sqlConnection1.Close();

}

}

Instead of hard coding the connection, we can also drag and drop the connection from the Server Explorer to the application

Ex2:

In this example we will query the table region under SQL Server using the DataReader object and generate an HTML table of the output:

The user interface is shown below:

Database Examples

And following is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnQuery_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "Select * from region";

System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();

string res = "";

bool first=false;

while (dr.Read())

{

// for the first row in the table, we want to find the field name and type

if (!first)

{

res+="";

for (int i = 0; i < dr.FieldCount; i++)

{

string types=null;

Type t = dr.GetFieldType(i);

types+= dr.GetName(i) +" type is "+t.ToString();

res+=""+types+"";

}

first = true;

res += "";

}

// for the rest of the table, just display the values of the fields

res+="";

for (int i = 0; i < dr.FieldCount; i++)

{

res +="" + dr.GetValue(i) + "";

}

res += "";

}

res += "";

Response.Write(res );

sqlConnection1.Close();

}

}

Ex3:

In this example, will be working with the DataTable, DataRow and DataColumn objects to navigate through records in the DataSet. The example queries the region table under SQL Server.

The user interface is defined below:

Data Table Example

The code behind is listed below:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class DataTableEx : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "Select * from region";

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds, "region");

// loop through the data source

String result = null;

foreach (DataTable dt in ds.Tables)

{

result += "table name is " + dt.TableName + " ";

foreach (DataRow dr in dt.Rows)

{

foreach (DataColumn dc in dt.Columns)

result += dr[dc] + " ";

}

result += "";

}

Response.Write(result);

sqlConnection1.Close();

}

}

Ex 4:

This example shows how to create XML document from a DataSet:

The user interface is shown below:

XML from DataSet Example

And below is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class XMLfromDataSet : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "Select * from Customers,Orders where Customers.CustomerID=Orders.CustomerID and Customers.CustomerID like 'WO%'";

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds);

txtOutput.Text=ds.GetXml();

ds.WriteXml("c:\\test.xml");

sqlConnection1.Close();

}

}

Ex 5:

In this example we will issue direct SQL statements against the SQL server:

Following is the user interface:

Direct SQL example

Following is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class directSQL : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

string strSQL = "insert into vote values ('Linux',0)";

mandType = CommandType.Text;

mandText = strSQL;

cmd.ExecuteNonQuery();

strSQL = "update vote set voteCount=1 where Candidatename='Unix'";

mandType = CommandType.Text;

mandText = strSQL;

cmd.ExecuteNonQuery();

strSQL = "delete from vote where candidateName='C'";

mandType = CommandType.Text;

mandText = strSQL;

cmd.ExecuteNonQuery();

strSQL = "select * from vote";

mandType = CommandType.Text;

mandText = strSQL;

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds, "vote");

// displaying the result in a grid view

GridView1.DataSource = ds;

GridView1.DataMember = "vote";

GridView1.DataBind();

}

}

Where the vote table is created using the following SQL statement:

CREATE TABLE vote(candidateName nvarchar(20), voteCount numeric(3))

Ex6:

In this example we will read data from an XML file and bind it to a data grid.

The user interface is shown below:

Bind a Grid View to and XML

And here is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class GridViewfromXML : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

DataSet ds = new DataSet();

ds.ReadXml("c://test.xml");

GridView1.DataSource = ds;

GridView1.DataBind();

}

}

Ex 7

This example shows how to filter data using the DataView object. The output is displayed in a GridView:

The user interface is shown below:

Filtering example

Here is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class filterData : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmdCust = new System.Data.SqlClient.SqlCommand();

cmdCust = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "select * from customers ";

System.Data.SqlClient.SqlDataAdapter daCust = new System.Data.SqlClient.SqlDataAdapter();

daCust.SelectCommand = cmdCust;

DataSet ds = new DataSet();

daCust.Fill(ds, "Customers");

System.Data.DataView dv = new System.Data.DataView(ds.Tables["Customers"]);

dv.RowFilter = "Country= 'France'";

dv.Sort = "CompanyName";

GridView1.DataSource = dv;

GridView1.DataBind();

}

}

Ex 8:

This example shows how to create a DataSet object programmatically and bind it to a Grid View:

Following is the user interface:

Create DataSet programatically

Following is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class createDataSetProgramatically : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

DataSet ds = new DataSet();

DataTable dt = new DataTable("NewTable");

dt.Columns.Add("Id");

dt.Columns.Add("name");

DataRow dr = dt.NewRow();

dr[0] = "1";

dr[1] = "John";

dt.Rows.Add(dr);

ds.Tables.Add(dt);

GridView1.DataSource = ds;

GridView1.DataMember = "NewTable";

GridView1.DataBind();

}

}

Ex9:

In this example we will bind data stored in a DataSet to a DataList control.

Following is the user interface:

Untitled Page

ID:

Company Name:

Contact Name:

Contact Title:

Order ID:

Here is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class DataListEx : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExecute_Click(object sender, EventArgs e)

{

System.Data.DataSet ds = new DataSet();

ds.ReadXml("c://test.xml");

DataList1.DataSource = ds;

DataList1.DataBind();

}

}

Ex 10:

In this example we will manipulate the customers table under SQL Server using the DataAdapter , CommandBuilder and the DataSet objects.

Following is the user interface:

using CommandBuilder ex

And following is the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class usingCommandBuilder : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();

// to use Windows authentication method, uncomment the following line and comment using the SQL authentication method

// sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;Integrated Security=True";

// using SQL authentication method

sqlConnection1.ConnectionString = "Data Source=HUMBERCOMP;Initial Catalog=Northwind;User ID=sa;Password=humber ";

sqlConnection1.Open();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

cmd = sqlConnection1.CreateCommand();

mandType = CommandType.Text;

mandText = "select * from customers";

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da);

da.InsertCommand = cb.GetInsertCommand();

da.DeleteCommand = cb.GetDeleteCommand();

da.UpdateCommand = cb.GetUpdateCommand();

da.Fill(ds, "customers");

// delete an existing record

foreach (DataRow dr1 in ds.Tables["Customers"].Rows)

{

if (dr1[0].ToString().Trim().Equals("1"))

{

dr1.Delete();

da.Update(ds, "Customers");

break;

}

}

// insert a new record

DataRow dr = ds.Tables["Customers"].NewRow();

dr[0] = "1";

dr[1] = "Humber";

dr[2] = "Muthana";

dr[3] = "Staff";

dr[4] = "Humber College Blvd.";

dr[5] = "Toronto";

dr[6] = "ON";

dr[7] = "M9W5L7";

dr[8] = "Canada";

dr[9] = "";

dr[10] = "";

ds.Tables[0].Rows.Add(dr);

da.Update(ds, "Customers");

//update

string[] ar ={ "New", "Rep", "current", "", "", "", "", "", "", "" };

DataRow[] dataRow = ds.Tables["Customers"].Select(" CustomerID= '1'");

if (dataRow.Length > 0)

{

dataRow[0].BeginEdit();

dataRow[0]["CompanyName"] = ar[0];

dataRow[0]["ContactName"] = ar[1];

dataRow[0]["ContactTitle"] = ar[2];

dataRow[0]["Address"] = ar[3];

dataRow[0]["City"] = ar[4];

dataRow[0]["Region"] = ar[5];

dataRow[0]["PostalCode"] = ar[6];

dataRow[0]["Country"] = ar[7];

dataRow[0]["Phone"] = ar[8];

dataRow[0]["Fax"] = ar[9];

dataRow[0].EndEdit();

DataSet dsc = ds.GetChanges(DataRowState.Modified);

string msg = null;

if (dsc.HasErrors)

{

foreach (DataTable tb in dsc.Tables)

{

if (tb.HasErrors)

{

DataRow[] errorR = tb.GetErrors();

foreach (DataRow drw in tb.Rows)

{

msg += "Error in " + drw["CustomerID"];

}

}

}

}

if (msg != null)

Response.Write(msg);

else

{

ds.AcceptChanges();

da.Update(dsc, "Customers");

Response.Write("update to record is accepted");

}

}

else

Response.Write("record been updated is not found");

}

//alternative approche 1 for update

/*

DataRow [] arEdit=ds.Tables["Customers"].Select(" CustomerID= '1'");

if (arEdit !=null)

{

arEdit[0]["CompanyName"]=ar[0];

arEdit[0]["ContactName"]=ar[1];

arEdit[0]["ContactTitle"]=ar[2];

arEdit[0]["Address"]=ar[3];

arEdit[0]["City"]=ar[4];

arEdit[0]["Region"]=ar[5];

arEdit[0]["PostalCode"]=ar[6];

arEdit[0]["Country"]=ar[7];

arEdit[0]["Phone"]=ar[8];

arEdit[0]["Fax"]=ar[9];

da.Update(ds,"Customers");

}

// alternative approach 2 for update

// loop through the records in the table

foreach( DataRow dr1 in ds.Tables["Customers"].Rows)

{

if (dr1[0].ToString().Trim().Equals("1"))

{

dr[1]="Humber College";

dr[2]="John";

dr[3]="Staff";

da.Update(ds,"Customers");

break;

}

}

*/

}

-----------------------

DataRow

DataColumnn

Constraint

DataView

Rows

Constaints

Columnss

DataRelation

DataTable

Relations

Tables

DataSet Objects

DataSet

DataAdapter

Parameter

Data Relation

Command

Provider Objects

Connection

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download