1 / 10

Connecting to MySQL from .NET using ADO.Net

Learn how to connect to MySQL database from .NET applications using ADO.Net. Understand ADO.Net architecture and key classes like MySqlConnection, MySqlCommand, MySqlDataReader, and MySqlDataAdapter.

lrichardson
Download Presentation

Connecting to MySQL from .NET using 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. MySQL Connection using ADO.Net Connecting to MySQL from .NET Languages

  2. ADO.Net Architecture • ADO.Net provides an architecture for communicating between an application and a data source. • The “data source” can be anything that has the required API, but usually it is a database server. Data Source Connection Object(ADO.Net) Data Adaptor Dataset (Local) Application

  3. MySQL ADO.Net Provider MySqlConnection is main connection to the MySQL database MySqlCommand enables the execution of any command against the database. MySqlDataReader provides fast, forward-only read access to the database. MySqlDataAdapter serves as an interface between the MySQL data classes and the Microsoft DataSet. MySqlParameter used to store dynamic parameters for a command. MySqlTransaction used to represent a MySQL transaction.

  4. Getting MySqlConnector.Net • You need a MySql "Connector" for .Net applications. • Download from http://dev.mysql.com/downloads/ • Run the installer. • The connector registers itself with the "Global Assembly Cache" so that the DLL can be found. • Difference from Java: Java uses a CLASSPATH to find code; Visual Studio uses Windows Registry to find resources. • (Optional for visual programming) Add the components to the Toolbox in Visual Studio:Tools -> Add/Remove Toolbox Items...or Tools -> Choose Toolbox Items...

  5. Undefined MySql Namespace in C# • After installing MySqlConnector.Net, in your project you would add its name space to your C# source code using MySql.Data.MySqlClient; • but, you may get a compiler error that the "MySql" name space is not found. • in this case, add a reference to the Connector's DLL file: • Project -> Add Reference -> Browse • Find the .Net2.0 MySqlData.dll file, ex: C:/MySql/MySqlConnector.Net/bin/.Net 2.0/MySqlData.dll This should fix the name space problem.

  6. Creating a Connection Object • Connection Object manages the connection to database server. • You must specify: server name, username, password • Can omit unnecessary attributes or leave blank. string connectString = "Data Source=localhost;Database=bank;User Id=bank;Password=FatChance"; MySqlConnection myconn = new MySqlConnection( connectString ); Better programming: public DBConnection(string host, string database, string user, string pass) { string connectString = String.Format( "Data Source={0};Database={1};User Id={2};Password={3}", host, database, user, pass); MySqlConnection myconn = new MySqlConnection( connectString ); }

  7. Opening the Connection • After creating connection, open it. • This may throw a MySqlException MySqlConnection myconn = null; try { myconn = new MySqlConnection( connectString ); myconn.Open(); } catch ( MySqlException e ) { Console.WriteLine("Error connecting to server: "+e.Message); }

  8. Creating a Command Object • Use a MySqlCommand object to issue database cmds • A Command object is like a Java Statement object. • You can reuse a Command object. • Requires a Connection object (myconn) as param. MySqlCommand cmd =new MySqlCommand("SHOW TABLES;", myconn); • Method of executing command depends on the SQL statement: • UPDATE, INSERT, DELETE: cmd.ExecuteNonQuery() returns int. • SHOW (QUERY): cmd.ExecuteReader() returns MySqlDataReader Semi-colon MySqlDataReader reader = cmd.ExecuteReader( )

  9. Processing Query Data • MySqlDataReader has many methods for getting data by column number, column name, or index. • Iterate over results using (boolean) reader.Read( ) MySqlDataReader reader = null; try { reader = cmd.ExecuteReader( ); if ( reader == null ) { Console.WriteLine("ExecuteReader failed"); return; } while( reader.Read() ) { Console.WriteLine( reader.GetString(0) ); } } catch ( MySqlException e) { Console.WriteLine("caught exception " + e.Message ); } finally { if (reader != null) reader.Close(); }

  10. Resources MySQL • http://dev.mysql.com/tech-resources/articles/dotnet/ Learning SQL • http://www.w3schools.com/sql/ nice tutorial and command reference Learning JDBC • JDBC Trail in Sun's Java Tutorial. • Dietel, Java How To Program, Chapter 25. • ... and zillions of resources on the web

More Related