1 / 21

Database Handling

Database Handling. Create ODBC. In C# program. Include. Before you can start using the ODBC class definitions, you will need to include the right module. using System.Data.Odbc; // ODBC definitions. Making Connection.

carrie
Download Presentation

Database Handling

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. 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.

More Related