Extensible Markup language (XML).

         XML Stands for Extensible Markup Language. Its a language much like HTML which was designed to carry data, not to display data and also self-descriptive. XML does not do anything. XML was created to structure,store and transport information. XML tags are not predefined, you must define your own tags. XML is not a replacement for HTML, HTML and XML were designed with different goals:

• XML was designed to transport and store data, with focus on what data is.
• HTML was designed to display data, with focus on how data looks.
• HTML is about displaying information, while XML is about carrying information.

To create an XML document we need to satisfy a set of rules that are prescribed by W3C, as following

• An XML doc has to be saved with .xml extention.
• Data under XML doc should be present only under tags, where every tag should have a start and end element.       eg:   <Tag><Tag> Or <Tag id="1"/>
• Tags can also be defined with attribute which are user-defined where value to attribute should be enclosed under double quotes.
• While defining tags start and end tag should match in case.
• An Xml doc can only have one root element.

Accessing MS Excel data from .Net Application

Ms Excel is a file system which stores data in the form of rows and columns same as a database table. An Excel document is referred as Work Book that contains Work Sheets in it, work books are considered as databases and work sheets are considered as tables.First row of work sheet can store column names.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace WinForms7
{
    public partial class Form6 : Form
    {
        public Form6()
        {
            InitializeComponent();
        }
        OdbcConnection con; OdbcCommand cmd; OdbcDataReader dr; string Sqlstr;
        private void Form6_Load(object sender, EventArgs e)
        {
            con = new OdbcConnection("Dsn=ExcelDsn; ReadOnly=0");
            cmd = new OdbcCommand();
            cmd.Connection = con;
            con.Open();
            LoadData();
            label1.Text = dr.GetName(0); label2.Text = dr.GetName(1);
            label3.Text = dr.GetName(2); label4.Text = dr.GetName(3);
               
        }
        private void LoadData()
        {
            cmd.CommandText = "Select * from[Student$]";
            cmd.ExecuteReader();
            ShowData();


        }
        private void ShowData()
        {
            if (dr.Read())
            {
                textBox1.Text = dr[0].ToString();
                textBox2.Text = dr[1].ToString();
                textBox3.Text = dr[2].ToString();
                textBox4.Text = dr[3].ToString();
            }
            else
                MessageBox.Show("No data exists");

               
        }

        private void button3_Click(object sender, EventArgs e)
        {
            ShowData();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = " ";
            textBox1.Focus();
        }
        private void ExecuteDML()
        {
            dr.Close();
            cmd.CommandText = Sqlstr;
            if (cmd.ExecuteNonQuery() > 0)
                MessageBox.Show("Insert or Update operation was successful");
            else
                MessageBox.Show("Insert or Update operation failed");

                LoadData();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Sqlstr = String.Format("Insert into [Stident$] values({0},'{1}' {2},{3})", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text);
            dr.Close();
            ExecuteDML();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Sqlstr = String.Format("Update [Stident$] Set Sname='{0},Class={1}, Fees= {2} where Sno={3}", textBox2.Text, textBox2.Text, textBox3.Text, textBox4.Text);
            dr.Close();
            ExecuteDML();
        }
    }
}

Configuration files

While developing applications if there are any values in application which requires changes in future, should not be hard coded i.e. should not be maintained as static values within the application, because if any changes are required to those values in future client will not be able to make those changes because they will not have the source code for modification. To overcome this problem we need to identify those values and put them under a special file known as configuration file, its an XML file which stores values in it in the form of key/value pairs. The values that are present under configuration files can be read from applications in runtime. When an application is installed on a client machines along with it its configuration file will also be installed there and because the configuration file is a text file clients can edit those files and make modifications to the values under them at any time and those values will be taken into the application for execution.

Connecting .net application with Sql Server

We can connect with Sql Server from .net applications either by using Oledb or SqlClient class.

Add a new form in the project and design it as follows :-

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApp
{
    public partial class Form6 : Form
    {
        SqlCommand cmd;
        SqlConnection con;
        SqlDataReader dr;
        string SqlStr;
        public Form6()
        {
            InitializeComponent();
        }

        private void Form6_Load(object sender, EventArgs e)
        {
            con = new SqlConnection("server=localhost; database=db1;uid=cheryl;trusted_connection=true");
            cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            LoadData();
        }
       public void LoadData()
       {
           cmd.CommandText="Select sid,sname,course from stud Order By sid ";
           dr = cmd.ExecuteReader();
           ShowData();
       }
        public void ShowData()
        {
            if (!dr.IsClosed && dr.Read())
            {
                txtid.Text = dr[0].ToString();
                txtname.Text =dr[1].ToString();
                txtcourse.Text = dr[2].ToString();
            }
            else
                MessageBox.Show("No data exists");
        }

        private void btnnext_Click(object sender, EventArgs e)
        {
            ShowData();
        }

        private void btnnew_Click(object sender, EventArgs e)
        {
            txtid.Text = txtname.Text = txtcourse.Text = "";
            dr.Close(); 
            cmd.CommandText = "Select IsNull(Max(sid),1000)+1 from stud";
            txtid.Text = cmd.ExecuteScalar().ToString();
            btninsert.Enabled = true;
            txtname.Focus();
        }
        private void ExecuteDML()
        {
            DialogResult d = MessageBox.Show("Are you sure of executing the above SQL statement?\n\n" + SqlStr, "Comfirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (d == DialogResult.Yes)
            {
                cmd.CommandText = SqlStr;
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                    MessageBox.Show("Statement executed successfully");
                else
                    MessageBox.Show("Statement failed execution");
            }
        }

        private void btninsert_Click(object sender, EventArgs e)
        {
            SqlStr = "Insert into Stud(sname,course) values ('" + txtname.Text + "','" + txtcourse.Text + "')";
            ExecuteDML();
            btninsert.Enabled = false;
        }

        private void btnupdate_Click(object sender, EventArgs e)
        {
            SqlStr = "Update Stud set sname ='"+ txtname.Text + "', course = '" + txtcourse.Text + "' Where sid="+txtid.Text;
            dr.Close();
            ExecuteDML();

        }

        private void btndelete_Click(object sender, EventArgs e)
        {
            SqlStr = "Delete From Stud where sid=" + txtid.Text;
            dr.Close();
            ExecuteDML();

        }

        private void btnclose_Click(object sender, EventArgs e)
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();

            }
            this.Close();

        }
    }
}

Moving towards connectivity

     Pretty much every application deals with data in some manner, whether the data comes from memory, databases, XML files, or something else. The location where we store the data can be called as Data Source or Data Store where a Data Source can be a file, database or indexing servers etc. Programming languages cannot communicate with Data Sources directly because each Data Source adopts a different protocol (set of rules) for communication, so to overcome the problem long back Microsoft has introduced intermediate technologies like JET, Odbc and Oledb which works like a bridge between the applications and Data Sources to communicate with each other.

     The Microsoft jet database engine is a database engine on which several microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way. The first version of jet was developed in 1992, consisting of three modules which could be used to manipulate a database. Jet stands for Joint Engine Technology, sometimes been referred to as Microsoft jet engine or simply jet. Microsoft Access and Excel use JET as their underlying database engine.

    ODBC(Open Database Connectivity) is a standard C programming language middleware API for accessing database management systems (DBMS). ODBC accomplishes DBMS independence by using an OBBC driver as a translation layer between the application and the DBMS. An application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS.

   OLE DB (Object Linking and embedded database), an API designed by Microsoft, allows accessing data from a variety of sources in a uniform manner. The API provides a set of interfaces implemented using the Compnent Object Model (COM). Microsoft originally intended OLE DB as a higher level replacement for, and successor to ODBC, extending its feature set to support a wider variety of non-relational databases such as object databases and spreadsheets that do not necessarily implement SQL.

Introduction to ADO.NET

ADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .Net Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational database systems, though it can also access data in non-relational sources. ADO.NET is a set of classes that are organized in a number of namespaces. These namespaces provide developers access to databases to retrieve and update data. The classes of ADO.NET provide us with the means to access databases and execute T-SQL code that retrieves, inserts, updates and deletes data.

ADO.NET Data Architecture

• Dataset : The dataset is a disconnected, in- memory representation of data. It can be considered as a local copy of the relevant portions f the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database.

• Data Provider: The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work  together to provide data in an efficient and performance driven manner. The .NET Framework currently comes wit two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle.

Started learning ADO.net

     As I have just completed with Console applications and Windows applications I have started practicing ADO.net from today. 

     ADO.net is a set of classes that expose data access services to the .net programmer. ADO.net provides consistent access to data sources such as Microsoft SQL server, as well as data sources exposed through OLE DB and XML.

     ADO.net provides libraries for Data Source communication under the following namespaces

• System.Data
• System.Data.Oledb
• System.Data.SqlClient
• System.Data.OracleClient
• System.Data.Odbc