ado net
Download
Skip this Video
Download Presentation
ADO.Net

Loading in 2 Seconds...

play fullscreen
1 / 28

ADO.Net - PowerPoint PPT Presentation


  • 275 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ADO.Net' - albert


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

ADO.Net

CS795

what is ado net
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
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
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
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:

open and closing connections
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
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
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
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
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 (“@logo”, blob);

cmd.ExecuteNonQuery ();

}

catch …

finally …

executescalar cont11
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
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.Close();

transactions
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
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 (“@amount”, SqlDbType.Money);

cmd.Parameters.Add (“@id”, SqlDbType.Char);

cmd.Parameters[“@amount”].Value = -1500;

cmd.Parameters[“@id”].Value = “1234”;

cmd.ExecuteNonQuery ();

cmd.Parameters[“@amount”].Value = 1500;

cmd.Parameters[“@id”].Value = “9867”;

cmd.ExecuteNonQuery ();

}

stored procdures
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
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 (“@amount”, 1500);

cmd.Parameters.Add (“@from”, ‘1234’);

cmd.Parameters.Add (“@to”, ‘9876’);

cmd.ExecuteNonQuery ();

}

Catch (SqlException ex) { ….}

finally {conn.Close(); }

example where it returns a value
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 (“@id”, ‘1234’);

SqlParameter bal = cmd.Parameters.Add (“@balance”, SqlDbType.Money);

bal.Direction = ParameterDirection.Output;

SqlParameter ret = cmd.Parameters.Add (“@return”, SqlDbType.Int);

ret.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery ();

int retval = (int) ret.value;

decimal balance = (decimal) bal.Value;

}

catch (SqlException ex) { ….}

finally {conn.Close(); }

slide19
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
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
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
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
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
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
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
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
Links
  • The C# Station ADO.NET Tutorial
  • Using ADO.NET for beginners
  • In Depth ASP.NET using ADO.NET
links28
Links
  • The C# Station ADO.NET Tutorial
  • Using ADO.NET for beginners
  • In Depth ASP.NET using ADO.NET
ad