Database Handling

1. Database Handling

2. Create ODBC

3. In C# program

4. Include Before you can start using the ODBC class definitions, you will need to include the right module. using System.Data.Odbc; // ODBC definitions

5. Making Connection A Connection is made using the OdbcConnection class and passing a connection string to the object being created. string strConnect = "DSN=MySQL;UID=root;PWD=admin;DATABASE=store"; OdbcConnection dbMySQL = new OdbcConnection(strConnect);

6. using System.Data.Odbc; // ODBC definitions class Program { static void Main(string[] args) { string strConnect = "DSN=MySQL;UID=root;PWD=admin;DATABASE=test"; OdbcConnectiondbMySQL = new OdbcConnection(strConnect); try { dbMySQL.Open(); // do some database stuff dbMySQL.Close(); } catch(OdbcException e) { Console.WriteLine("Database Error\n\n{0}", e.ToString()); } finally { if(dbMySQL != null) dbMySQL.Close(); }

7. Command object After we have attained a open database connection, we need to get the command object in order to execute our SQL. OdbcCommand sqlCommand = dbMySQL.CreateCommand(); A SqlCommand object allows you to specify what type of interaction you want to perform with a database

8. Prepare SQL prepare the SQL we want to execute sqlCommand.CommandText = "select * from emp order by name";

9. Execute SQL Since this SQL statement will be returning a result table we need to call the ExecuteReader( ) method on the OdbcCommand object "sqlCommand". OdbcDataReader sqlReader = sql.ExecuteReader();

10. Handling Result Set Once we have a data reader object, we can begin to read in the row value one at a time using the Read( ) method. This method will return 'true' if there are more rows to be fetched, and 'false' when there are no more rows existing in the result table.

11. while(sqlReader.Read()) { Console.WriteLine(“{0} {1}”, sqlReader.GetString(0), sqlReader.GetString(1)); } // GetName(0)

12. Insert

13. // prepare command string string insertString = @"     insert into Categories     (CategoryName, Description)     values ('Miscellaneous', 'Whatever doesn’t fit elsewhere')"; // 1. Instantiate a new command with a query and connectionSqlCommandcmd = new SqlCommand(insertString, conn); // 2. Call ExecuteNonQuery to send commandcmd.ExecuteNonQuery()

14. Delete

15. // prepare command string string deleteString = @"     delete from Categories     where CategoryName = 'Other'"; // 1. Instantiate a new commandSqlCommandcmd = new SqlCommand(); // 2. Set the CommandText propertycmd.CommandText = deleteString; // 3. Set the Connection propertycmd.Connection = conn; // 4. Call ExecuteNonQuery to send commandcmd.ExecuteNonQuery();

16. Getting Single values // 1. Instantiate a new command SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn); // 2. Call ExecuteNonQuery to send command int count = (int)cmd.ExecuteScalar();

17. Write code to insert and display employ records from Table1 in alphabetical order. Table1 : name string no int

18. Summary • A SqlCommand object allows you to query and send commands to a database.  • It has methods that are specialized for different commands.  • The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query.  • For insert, update, and delete SQL commands, you use the ExecuteNonQuerymethod.  • If you only need a single aggregate value from a query, the ExecuteScalar is the best choice.