Ado net l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 28

ADO.Net PowerPoint PPT Presentation


  • 202 Views
  • Uploaded on
  • Presentation posted in: General

ADO.Net. CS795. What is ADO.Net?. Database language spoken by managed applications ADO.net database accesses go through modules: data providers SQL Server .Net provider---to interface with MS SQL databases without any help from unmanaged providers

Download Presentation

ADO.Net

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ado net l.jpg

ADO.Net

CS795


What is ado net l.jpg

What is ADO.Net?

  • Database language spoken by managed applications

  • ADO.net database accesses go through modules: data providers

    • SQL Server .Net provider---to interface with MS SQL databases without any help from unmanaged providers

    • OLE DB .Net provider---to interface with other databases through unmanaged OLE DB providers

  • OLE DB providers provide a uniform API over a variety of databases


System data sqlclient l.jpg

System.Data.SqlClient

using System.Data.SqlClient

SqlConnection conn = new SqlConnection

(“server=localhost; database=pubs; uid=mukka; pwd=“);

try {

conn.Open();

SqlCommand cmd = new SqlCommand(“select * from titles”, conn);

SqlDataReader reader = cmd.ExecuteReader ();

while (reader.Read ())

Console.Writeln (reader[“title”]);

}

Catch (SqlException ex) {Console.WriteLine (ex.message);

}

finally {conn.Close();}

Note: For OLEDB, simply replace Sql in the above code by OleDb


Connections commands datareaders l.jpg

Connections, Commands, DataReaders

The canonical usage pattern for executing database commands in ADO.Net:

  • Create a connection object encapsulating a connection string

  • Open the connection by calling Open on the connection object

  • Create a command object encapsulating both an SQL command and the connection that the command will use

  • Call a method on the command object to execute the command

  • Close the connection by calling Close on the connection object


Sqlconnection class l.jpg

SqlConnection Class

SqlConnection = conn SqlConnection();

Conn.ConnectionString = “server=localhost; database=pubs; uid=mukka; pwd=“;

Or

SqlConnection conn = “server=localhost; database=pubs; uid=mukka; pwd=“;

Other parameters for ConnectionString:

http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlConnectionClassConnectionString.aspx

It cal also be defined on web.config:

  • The constructor of the SqlConnection object expects one parameter: the connection string. The connection string identifies the SQL server name, the SQL database name, and satisfies the authorization requirement by providing a user name and a password, or by specifying Trusted_Connection=true. You can specify the connection string in line within the constructor line, or you can specify it in Web.config:

    <add key="ConnectionString value="server=HAW2L1800\NetSDK;Trusted_Connection=true;database=StoreDOC“/>


Open and closing connections l.jpg

Open and Closing Connections

  • You must first open a connection and close it at the end.

    SqlConnection conn = new SqlConnection

    (“server=localhost; database=pubs; uid=mukka; pwd=“);

    try{

    conn.Open();

    ….}

    catch (SqlConnection ex){ …..}

    finally { conn.Close();}


Command classes l.jpg

Command Classes

ExecuteReader (for read)

ExecuteNonQuery (for updates)

SqlConnection conn = new SqlConnection (“server=localhost; datbase=pubs; uid=mukka; pwd=“);

try {

conn.Open();

SqlCommand cmd = new SqlCommand ();

cmd.CommandText= “delete from title where title_id = “xxxx”;

cmd.Connection = conn;

cmd.ExecuteNonQuery ();

}

catch (SqlException ex) {

….}

finally { conn.Close();}


Executenonquery l.jpg

ExecuteNonQuery

  • To execute operations where database is changed

  • Example: insert, update, delete, create database, create table, etc.

  • Insert, update, delete: Returns number of rows affected by the operation

  • Returns -1 for others

    SqlCommand cmd = new SqlCommand

    (“insert in to titles (title_id, title, type, pubdate)”+

    “values (‘CS150’,’C++ Programming’, )+

    “ ‘computer science’, ‘May 2006’, conn);

    cmd.ExecuteNonQuery();


Executescalar l.jpg

ExecuteScalar

  • Returns the 1st row of the 1st column in the result

  • Used for commands such as: count, avg, min, max, sum

    try{

    conn.Open();

    SqlCommand cmd = new SqlCommand

    (“select max (advance) from title”, conn);

    decimal amount = (decimal) cmd.ExecuteScalar ();

    Console.WriteLine (“ExecuteScalar returned (0:c)”, amount);

    }

    Catch (SqlException ex} {Console.Writeln (ex.Message);}

    finally {conn.Close();}


Executescalar cont l.jpg

ExecuteScalar (cont.)

  • To retrieve BLOBs (Binary large objects) from databases

  • http://builder.com.com/5100-6371-5766889.html

  • http://support.microsoft.com/default.aspx?scid=kb;en-us;309158

  • http://www.codeproject.com/cs/database/images2db.asp

  • http://www.codeproject.com/useritems/Blobfield.asp

    FileStream stream new FileStream (“Logo.jpg”, FileMode.Open);

    byte[] blob new byte [stream.Length];

    stream.Read (blob, 0, (int) stream.Length);

    stream.Close();

    SqlConnection con = new …

    try{ conn.Open();

    SqlCommand cmd = new SqlCommand

    (“insert into pub_info (pub_id, logo) values (‘9937’, @logo)”, conn);

    cmd.Parameters.Add ([email protected], blob);

    cmd.ExecuteNonQuery ();

    }

    catch …

    finally …


Executescalar cont11 l.jpg

ExecuteScalar (Cont.)

  • To validate a user name and password (page 506-507, Jeff Prosie book)

    Try { conn.Open();

    StringBuilder builder = new StringBuilder ();

    builder.Append (“select count (*) from users where username =

    ….

    int count = (int) command.ExecuteScalar ();

    return (count > 0);

    }


Executereader method l.jpg

ExecuteReader Method

  • To perform database queries

  • Returns a DataReader object: SqlDataReader or OleDataReader

    try{ conn.Open();

    SqlCommand cmd = new SqlCommand(“select * from titles”, conn);

    SqlDataReader reader = cmd.ExecuteReader();

    While (reader.Read())

    Console.WriteLine (reader[“title”]); }

    ******************

    try {conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn);

    SqlDataReader reader = cmd.ExecuteReader();

    for (int i=0; I <reader.FieldCount; i++) Console.WriteLine (reader.GetName[i]));

    }

    reader.Close();


Transactions l.jpg

Transactions

  • Ex: transfer funds from one account (say 1234) to another account (say 9876).

    SqlTransaction trans = null;

    SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);

    try{conn.Open();

    trans = conn.BeginTransaction (IsolationLevel.Serializable);

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = conn;

    cmd.Transaction=trans;

    cmd.CommandText = “update accounts set balance = balance-1500 where account_id = ‘1234’ “;

    cmd.ExecuteNonQuery();

    cmd.CommandText = “update accounts set balance = balance+1500 where account_id = ‘9876’ “;

    cmd.ExecuteNonQuery();

    Trans.Commit();

    }

    Alternate to Commit is Rollback.


Parameterized commands l.jpg

Parameterized Commands

  • When commands are the same but the parameters are different

    Try{ conn.Open();

    SqlCommand cmd = new SqlCommand(“update accounts set balance = balance”+

    [email protected] where account_id = @id”, conn);

    cmd.Parameters.Add ([email protected], SqlDbType.Money);

    cmd.Parameters.Add ([email protected], SqlDbType.Char);

    cmd.Parameters[[email protected]].Value = -1500;

    cmd.Parameters[[email protected]].Value = “1234”;

    cmd.ExecuteNonQuery ();

    cmd.Parameters[[email protected]].Value = 1500;

    cmd.Parameters[[email protected]].Value = “9867”;

    cmd.ExecuteNonQuery ();

    }


Stored procdures l.jpg

Stored Procdures

  • User defined command added to a database

  • Execute faster because they are already in compiled form.

    CREATE PROCEDURE proc_TransferFunds

    @Amount money, @From char (10), @To char (10);

    AS

    BEGIN TRANSACTION

    UPDATE Accounts SET Balance = Balance - @Amount WHERE Account_ID = @From

    IF @@ROWCOUNT = 0

    BEGIN ROLLBACK TRANSACTION RETURN END

    UPDATE Accounts SET Balance = Balance + @Amount WHERE Account_ID [email protected]

    IF @@ROWCOUNT = 0

    BEGIN ROLLBACK TRANSACTION RETURN END

    COMMIT TRANSACTION

    GO


How does an application call the stored procedure l.jpg

How does an application call the stored procedure?

SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);

try{conn.Open();

SqlCommand cmd = new SqlCommand (“proc_TransferFunds”, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add ([email protected], 1500);

cmd.Parameters.Add ([email protected], ‘1234’);

cmd.Parameters.Add ([email protected], ‘9876’);

cmd.ExecuteNonQuery ();

}

Catch (SqlException ex) { ….}

finally {conn.Close(); }


Example where it returns a value l.jpg

Example where it returns a value

CREATE PROCEDURE proc_GetBalance

@ID char(10), @Balance money OUTPUT

AS

SELECT @Balance =Balance From Accounts WHERE Account_ID = @ID

IF @@ROWCOUNT = 1 RETURN 0

ELSE BEGIN SET @Balance = 0 RETURN -1 END

GO

*****************

SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);

try{conn.Open();

SqlCommand cmd = new SqlCommand (“proc_GetBalance”, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add ([email protected], ‘1234’);

SqlParameter bal = cmd.Parameters.Add ([email protected], SqlDbType.Money);

bal.Direction = ParameterDirection.Output;

SqlParameter ret = cmd.Parameters.Add ([email protected], SqlDbType.Int);

ret.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery ();

int retval = (int) ret.value;

decimal balance = (decimal) bal.Value;

}

catch (SqlException ex) { ….}

finally {conn.Close(); }


Dataset class l.jpg

DataSet Class


Slide19 l.jpg

  • Datasets are ideal for retrieving results from database queries and storing them in memory.

  • In addition, this data may be modified and propagated back to the database.

  • It can also support, random access to the data (unlike DataReader)

  • Great for caching, especially in web applications.


Datasets vs datareaders l.jpg

DataSets vs. DataReaders

  • If the application simply queries a database and read through the records one at a time until it finds the record it is looking for, DataReader is the right tool

  • If the application requires all results from a query, say to display in a table, and have ability to iterate back and forth through the result set, DataSet is a good alternate.


Slide21 l.jpg

DataSet ds = new DataSet ();

MyDataGrid.DataSource = ds; //DataGrid controls are complex web controls

MyDataGrid.DataBind ();

SqlDataReader reader = cmd.ExecuteReader ();

MyDataGrid.DataSource = reader;

MyDataGrid.DataBind ();


Dataadapter l.jpg

DataAdapter

  • DataSets don’t interact with databases directly;

  • Insteda, they interact through DataAdapters

  • Purpose: To perform database queries and create DataTables containing the query results; also, to write the modified DataTables into databases

  • Fill and Update


Dataadapter fill l.jpg

DataAdapter.Fill

SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,

“server=localhost; database=pubs; uid=mukka; pwd=“);

DataSet ds = new dataSet ();

adapter.Fill (ds. “Titles”);

What does Fill do?

  • Open a connection to the pubs database using adapter.

  • Performs a query on the pubs database using the query string passed to adapter.

  • Creates a DataTable named “Titles” in ds.

  • Initializes DataTable with a schema that matches that of the “Titles” table in the database.

  • Retrieves records produced by the query and writes them to the DataTable

  • Closes the connection to the database


Datatable l.jpg

DataTable

foreach (DataTable table in ds.Tables) Console.WriteLine (table.TableName);

DataTable table = ds.Tables[0];

foreach (DataRow row in table.Rows) Console.WriteLine(row[0]);

DataTable table = ds.Tables[0];

foreach (DataRow row in table.Rows) Console.WriteLine(row[“account_id”]);

DataTable table = ds.Tables[0];

foreach (DataColumn col in table.Columns)

Console.WriteLine(“Name={0}, Type ={1}”, col.ColumnName, col.DataType);


Insert a record into datatable l.jpg

Insert a record into DataTable

SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,

“server=localhost; database=pubs; uid=mukka; pwd=“);

DataSet ds = new dataSet ();

adapter.Fill (ds. “Titles”);

DataTable table ds.Tables[“Titles”];

DataRow row = table.NewRow ();

row[“title_id”] = “CS795”;

row[“title”] = “.Net Security”;

row[“price”]=“70.99”;

Table.Rows.Add (row);


Propagating changes back to database l.jpg

Propagating Changes back to Database

SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,

“server=localhost; database=pubs; uid=mukka; pwd=“);

SqlCommandBuilder builder = new SqlCommandBuilder (adapter);

DataSet ds = new dataSet ();

adapter.Fill (ds. “Titles”);

DataTable table ds.Tables[“Titles”];

DataRow row = table.NewRow ();

row[“title_id”] = “CS795”;

row[“title”] = “.Net Security”;

row[“price”]=“70.99”;

table.Rows.Add (row);

adapter.Update (table);

(only writes the ones that were changed)


Links l.jpg

Links

  • The C# Station ADO.NET Tutorial

  • Using ADO.NET for beginners

  • In Depth ASP.NET using ADO.NET


Links28 l.jpg

Links

  • The C# Station ADO.NET Tutorial

  • Using ADO.NET for beginners

  • In Depth ASP.NET using ADO.NET


  • Login