Chapter: 7 Working with database

7.1    Introduce the database, its features and necessity in programming environment

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.

Features of Database

• Minimum Redundancy and Duplication
• Reduced amount of space and money spent on storage
• Data Organization
• Data Retrieval
• Usage Of Query Languages
• Multi User Access
• Data Integrity is Maintained
• Provides a High Level of Data Security

Necessity of Database in Programming Environment

• Data Integrity: This means that the structure of the database can change, but the application that uses the data does not have to change.
• Data Consistency: The data is identical regardless of who is inspecting it.
• Data Backups: Backing up data from a single location is simple.
• Data Security: Data can only be accessed by the authorized users.
• Data Redundancy: Duplication of the data is minimized.
• Data Durability: Database will persist even after a power loss or a disaster.

7.2    Demonstrate the Database environment setup and configure the requirements

                To be written….

7.3    Illustrate the connection of C# program with database

C# and .Net can work with a majority of databases, the most common being Oracle and Microsoft SQL Server. But with every database, the logic behind working with all of them is mostly the same.

Connecting C# with Database: To work with a database, the first of all you required a connection. The connection to a database normally consists of the below-mentioned parameters.

• Database name or Data Source: The database name to which the connection needs to be set up and connection can be made or you can say only work with one database at a time.
• Credentials: The username and password which needs to be used to establish a connection to the database.
• Optional Parameters: For each database type, you can specify optional parameters to provide more information on how .NET should connect to the database to handle the data.

Code:

// C# code to connect the database

using System;

using System.Data.SqlClient;

class DBConn

{

    // Main Method

    static void Main()

    {

        Connect();

        Console.ReadKey();

    }

    static void Connect()

    {

        string constr;

        // for the connection to sql server database

        SqlConnection conn;

        // Data Source is the name of the

        // server on which the database is stored.

        // The Initial Catalog is used to specify

        // the name of the database

        // The UserID and Password are the credentials

        // required to connect to the database.

        constr = @”Data Source=demo;Initial Catalog=demo;User ID=demo;Password=demo”;

        conn = new SqlConnection(constr);

        // to open the connection

        conn.Open();

        Console.WriteLine(“Connection Open!”);

        // to close the connection

        conn.Close();

    }

}

Output:

                Connection Open !

7.4    Demonstrate the Read and write operations from the database

                Read form database:

                Code:

                                // C# code to demonstrate how

// to use select statement

using System;

using System.Data.SqlClient;

namespace Database_Operation

{

    class SelectStatement

    {

        // Main Method

        static void Main()

        {

            Read();

            Console.ReadKey();

        }

        static void Read()

        {

            string constr;

            // for the connection to

            // sql server database

            SqlConnection conn;

            // Data Source is the name of the

            // server on which the database is stored.

            // The Initial Catalog is used to specify

            // the name of the database

            // The UserID and Password are the credentials

            // required to connect to the database.

            constr = @”Data Source=demo;Initial Catalog=demo;User ID=demo;Password=demo”;

            conn = new SqlConnection(constr);

            // to open the connection

            conn.Open();

            // use to perform read and write

            // operations in the database

            SqlCommand cmd;

            // use to read a row in

            // table one by one

            SqlDataReader dreader;

            // to sore SQL command and

            // the output of SQL command

            string sql, output = “”;

            // use to fetch rows from demo table

            sql = “Select articleID, articleName from demoTable”;

            // to execute the sql statement

            cmd = new SqlCommand(sql, conn);

            // fetch all the rows

            // from the demo table

            dreader = cmd.ExecuteReader();

            // for one by one reading row

            while (dreader.Read())

            {

                output = output + dreader.GetValue(0) + ” – ” + dreader.GetValue(1) + “\n”;

            }

            // to display the output

            Console.Write(output);

            // to close all the objects

            dreader.Close();

            cmd.Dispose();

            conn.Close();

        }

    }

}

                Output:

                                1 – C#

2 – C++

            Write to database

                Code:

                                // C# code for how to use Insert Statement

using System;

using System.Data.SqlClient;

namespace Database_Operation {

class InsertStatement {

    // Main Method

    static void Main()

    {

        Insert();

        Console.ReadKey();

    }

    static void Insert()

    {

         string constr;

        // for the connection to

        // sql server database

        SqlConnection conn;

        // Data Source is the name of the

        // server on which the database is stored.

        // The Initial Catalog is used to specify

        // the name of the database

        // The UserID and Password are the credentials

        // required to connect to the database.

        constr = @”Data Source=DESKTOP-GP8F496;Initial Catalog=Demodb;User ID=sa;Password=24518300″;

        conn = new SqlConnection(constr);

        // to open the connection

        conn.Open();

        // use to perform read and write

        // operations in the database

        SqlCommand cmd;

        // data adapter object is use to

        // insert, update or delete commands

        SqlDataAdapter adap = new SqlDataAdapter();

        string sql = “”;

        // use the defined sql statement

        // against our database

        sql = “insert into demo values(3, ‘Python’)”;

        // use to execute the sql command so we

        // are passing query and connection object

        cmd = new SqlCommand(sql, conn);

        // associate the insert SQL

        // command to adapter object

        adap.InsertCommand = new SqlCommand(sql, conn);

        // use to execute the DML statement against

        // our database

        adap.InsertCommand.ExecuteNonQuery();

        // closing all the objects

        cmd.Dispose();

        conn.Close();

    }

}

}