1 / 25

ADO. NET

ADO. NET. What is “ ADO.Net ” ?. ADO.Net is a new object model for dealing with databases in .Net. Although some of the concepts are similar to the classical ADO, there are some new concepts such as the Dataset. Introduction to ADO.NET. Process of using DB (1). Import Namespace

chase-knox
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO. NET

  2. What is “ADO.Net”? • ADO.Net is a new object model for dealing with databases in .Net. Although some of the concepts are similar to the classical ADO, there are some new concepts such as the Dataset.

  3. Introduction to ADO.NET

  4. Process of using DB (1) • Import Namespace • Make Connection • Open Connection • Send Query • Execute Query • Store result • Populate data in UI • Close connection

  5. Process of using DB (2) SqlConnection conn = new SqlConnection(connstr); //using(SqlConnection conn = new SqlConnection(connstr)) conn.Open(); SqlCommand cmd = new SqlCommand(SQLstr,conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { listBox1.Items.Add(reader[1].ToString()); } conn.Close();

  6. Connection(1) • Connection String • MS-SQL string connstr = "server=SERVERNAME;uid=USERID;pwd=PASSWORDr;database=DBNAME;"; string connstr = "server=rainnysea;uid=testuser;pwd=testuser;database=Teststd;"; • MS-Acess • string connstr = "Provider=ProviderName&Version;Data Source=FileNameOnServer"; • string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\cs440\\Test\\data\\xyz.mdb;";

  7. Connection(2) database connections are an important resource and you should explicitly close the connection . conn = SqlConnection(ConnString); try{ // open the connection to the database conn.Open(); // do something with the database } catch (Exception e) { // report error } finally { conn.Close( ); }

  8. Execute Method on the Command

  9. Command(1) –ExecuteScalar() • ExecuteScalar() SqlCommand cmd = new SqlCommand(qry, this.con ); Object obj = cmd.ExecuteScalar(); // returns one row

  10. Command(2) –ExecuteReader() • ExecuteReader() SqlCommand cmd = new SqlCommand(sql,conn); SqlDataReader reader = cmd.ExecuteReader();

  11. Command(3) –ExecuteNonQuery() • ExecuteNonQuery() SqlCommand cmd = new SqlCommand(sql,conn); int cntrows = cmd.ExecuteNonQuery();

  12. Data Stored Object(1)

  13. Application Application 1 Application 2 Data Reader Data Set Data Source Data Source Data Adapter Data Stored Object(2) DataReader DataSet

  14. What is “Data Set”? • A Dataset is a disconnected object that can potentially contain all or part of the database including tables, constraints and their relationships. Thus for using databases in web applications, Dataset can provide a much higher performance for mostly read-only type of data. • The DataSet class can further use DataTable, DataRow, DataColumn, DataRelation and Constraint classes to define its offline data.

  15. What is ths “Data Adapter”? • DataAdapter class acts as the communication point between the DataSet And tha database. • This object is created much the same way as the Command object. • Fill() method fills DataSet with data obtained from SQL query.

  16. DataSet(1) DataSet DS = new DataSet(); SqlDataAdapter Adapter = new SqlDataAdapter(qry, conn); Adapter.Fill(DS, tblName);

  17. DataGrid Control(1) • displays data in a series of rows and columns. • displaying either a single table or the hierarchical relationships between a set of tables. • update the data in the bound DataSet, the DataGrid control reflects the changes.

  18. DataGrid Control(2) • dataGrid.DataSource = dataset.DefaultViewManager; • dataGrid.DataSource = dataset.Tables[“tablename"].DefaultView ;

  19. DataGrid Control(2) dataset.Relations.Add ("CategoryProducts", ds.Tables["category"].Columns["catID"], ds.Tables["product"].Columns["catID"]); //(relationship name, Parent column name, Child column name)

  20. Parameterized query(1) • Construct the SqlCommand command string with parameters. • Declare a SqlParameter object, assigning values as appropriate. • Assign the SqlParameter object to the SqlCommand object's Parameters property.

  21. Parameterized query(2) str = "update tblCategory set catdesc = @newname where catdesc = @oldname"; SqlCommand cmd = new SqlCommand(str, Conn()); cmd.Parameters.Add(new SqlParameter("@newname",newname));

  22. Stored Procedure(1) • A pre-defined, reusable routine that is stored in a database. • Accept input parameters and return multiple values. • Reduced client/server traffic.

  23. Stored Procedure(2) CREATE PROCEDURE procedure_name(@parameter_name as datatype) AS [ Insert into tblcategory(catdesc) values(@catDesc)] // SQL Query GO

  24. Stored Procedure(3) • create a command object identifying the stored procedure. • set the command object so it knows to execute a stored procedure. • add parameter to command, which will be passed to the stored procedure.

  25. Stored Procedure(4) SqlCommand cmd = new SqlCommand("cateIns", Conn() ); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add (new sqlParameter("@catDesc", SqlDbType.VarChar,50,"catDesc")); cmd.Parameters[0].Value = newname;

More Related