Lesson ADO.NET

Chia sẻ: Lavie Lavie | Ngày: | Loại File: PDF | Số trang:0

Thêm vào BST

Báo xấu

56
lượt xem 3
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Lesson ADO.NET aims at introduce ADO.NET and SQL Server interaction (connection, command, data reader, stored procedure, disconnected data set, database independent coding). This lesson includes Connection parameters, Database; Authentication; Command setup; SqlCommand ExecuteReader.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Lesson ADO.NET

ADO.NET
Objective • Introduce ADO.NET and SQL Server interaction – connection – command – data reader – stored procedure – disconnected data set – database independent coding 2
Overview • ADO.NET provides managed types for database access – generic types in System.Data namespace – SQL Server types in System.Data.SqlClient namespace – other data providers also supported 3
Connection parameters • Must specify several pieces of information to connect – server – database – authentication credentials • Exact connection details differ for different providers 4
Server • Use Server parameter to specify server for SQL Server – passed in connect string – use "." or "localhost" to connect to local database specify string connectString = "Server=localhost;..."; server 5
Database • Use Database parameter to specify database for SQL Server – passed in connect string specify string connectString = "Database=pubs;..."; database 6
Authentication • Two ways to authenticate a client connection for SQL Server – Windows authentication uses Windows user information • Integrated Security set to SSPI in connect string – SQL Server authentication uses SQL Server user information • User ID and Password passed in connect string use Windows string connectString = "Integrated Security=SSPI;..."; information use SQL string connectString = "User ID=Joe; Password=lobster;..."; information 7
Connecting • Use SqlConnection to connect to SQL Server – create object – specify connect string • can pass to constructor • can set after creation using ConnectionString property – call Open method Application SqlConnection Database parameters string cs = "server=.;Integrated Security=SSPI;database=pubs"; create SqlConnection connection = new SqlConnection(cs); open connection.Open(); ... 8
Disconnecting • Close SqlConnection when finished – can call either Close or Dispose method – typical to place call in finally block or using statement static void Main() { SqlConnection connection = null; try { ... open connection.Open(); ... } finally { close connection.Dispose(); } } 9
Command setup • Use SqlCommand to execute command – must specify command text • can pass to constructor • can set after creation using CommandText property – must specify connection to use • can pass to constructor • can set after creation using Connection property Application SqlCommand SqlConnection Database SqlConnection connection = new SqlConnection(...); ... string text = "select * from authors"; create SqlCommand command = new SqlCommand(text, connection); command ... 10
SqlCommand ExecuteReader • Use ExecuteReader when result set expected – returned data placed in SqlDataReader object – reader provides forward-only access to data – multiple results supported using NextResult – throws Exception if command fails ExecuteReader Application SqlCommand SqlConnection Database SqlDataReader string text = "select * from authors"; SqlConnection connection = new SqlConnection(...); SqlCommand command = new SqlCommand(text, connection); ... SqlDataReader reader = command.ExecuteReader(); capture returned data execute command 11
SqlDataReader data access • Two main ways to access rows of result set – use foreach to traverse rows of IDataRecord objects – use while loop with Read to manually advance through rows • Three main ways to access columns of a row – index by column ordinal or name – pass column index to getXXX methods – use for loop with FieldCount to access each column in turn static void Display(SqlDataReader reader) { loop through rows while (reader.Read()) { access data in row string last = (string)reader["au_lname"]; using indexers string first = (string)reader[2]; access data in row string zip = reader.GetString(7); using get method ... } } 12
SqlDataReader Close • Call Close when finished with SqlDataReader – releases connection (which can then be reused) – can not access contained data after closing string text = "select * from authors"; SqlConnection connection = new SqlConnection(...); SqlCommand command = new SqlCommand(text, connection); ... SqlDataReader reader = command.ExecuteReader(); ... reader.Close(); close reader when finished 13
SqlCommand ExecuteNonQuery • Use ExecuteNonQuery when no data will be returned – returns an int specifying number of rows affected ExecuteNonQuery Application SqlCommand SqlConnection Database int string text = "insert into authors " + "(au_id, au_lname, au_fname, contract) values " + "('111-11-1111', 'Adams', 'Mark', 1)"; SqlCommand command = new SqlCommand(text, connection); execute int rowsAffected = command.ExecuteNonQuery(); command ... 14
Stored procedure • Use SqlCommand to execute stored procedure – set CommandType property to StoredProcedure – set CommandText property to procedure name – pass parameters in Parameters collection – call ExecuteReader procedure name SqlCommand command = new SqlCommand("byroyalty", connection); command type command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@percentage", SqlDbType.Int); parameters command.Parameters["@percentage" ].Value = 50; execute reader = command.ExecuteReader(); ... 15
Disconnected data in ADO.NET • Can model in-memory cache of data – tables, relations, rows, columns etc. – disconnected and independent of data source DataSet Customer Name Customer Id Tables Ann 0 Bob 1 Customer Id Balance Rating 0 5000 2 1 750 5 16
DataSet • DataSet class models disconnected data set – has collection property of DataTable objects public class DataSet ... { tables in data set public DataTableCollection Tables { get; } ... } 17
DataTable • DataTable is in-memory model of a table – has rows, columns, etc. public class DataTable ... { rows and columns public DataRowCollection Rows { get; } currently in table public DataColumnCollection Columns { get; } ... } 18
DataRow • DataRow is in-memory model of row inside DataTable – several ways to access column data – rows are generated by tables, not created directly public class DataRow ... { access column data public object this[string] { get; set; } by name or number public object this[int] { get; set; } all columns in row public object[] ItemArray { get; set; } ... } 19
DataColumn • DataColumn models column of DataTable – specify name and data type when creating public class DataColumn ... { public DataColumn(string name, Type type); ... } name of Type object for column type of column data 20