My SQL Connection using ADO Net Connecting to

My. SQL Connection using ADO. Net Connecting to My. SQL from. NET Languages

ADO. Net Architecture n n 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

My. SQL ADO. Net Provider My. Sql. Connection is main connection to the My. SQL database My. Sql. Command enables the execution of any command against the database. My. Sql. Data. Reader provides fast, forwardonly read access to the database. My. Sql. Data. Adapter serves as an interface between the My. SQL data classes and the Microsoft Data. Set. My. Sql. Parameter used to store dynamic parameters for a command. My. Sql. Transaction used to represent a My. SQL transaction.

Getting My. Sql. Connector. Net n n You need a My. Sql "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. n n 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: or Tools -> Add/Remove Toolbox Items. . . Tools -> Choose Toolbox Items. . .

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

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

Opening the Connection n n After creating connection, open it. This may throw a My. Sql. Exception My. Sql. Connection myconn = null; try { myconn = new My. Sql. Connection( connect. String ); myconn. Open(); } catch ( My. Sql. Exception e ) { Console. Write. Line("Error connecting to server: "+e. Message); }

Creating a Command Object n Use a My. Sql. Command object to issue database cmds n n n A Command object is like a Java Statement object. You can reuse a Command object. Requires a Connection object (myconn) as param. My. Sql. Command cmd = new My. Sql. Command("SHOW TABLES; ", myconn); Method of executing command depends on the SQL statement: § UPDATE, INSERT, DELETE: cmd. Execute. Non. Query() returns int. § SHOW (QUERY): cmd. Execute. Reader() returns My. Sql. Data. Reader reader = cmd. Execute. Reader( ) Semi-colon

Processing Query Data n n My. Sql. Data. Reader has many methods for getting data by column number, column name, or index. Iterate over results using (boolean) reader. Read( ) My. Sql. Data. Reader reader = null; try { reader = cmd. Execute. Reader( ); if ( reader == null ) { Console. Write. Line("Execute. Reader failed"); return; } while( reader. Read() ) { Console. Write. Line( reader. Get. String(0) ); } } catch ( My. Sql. Exception e) { Console. Write. Line("caught exception " + e. Message ); } finally { if (reader != null) reader. Close(); }

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