Ad Rotator Control

Ad Rotator is used to display advertisements on the page and change the advertisement with every request. Ad rotator support displaying the advertisement from an XML file and also from the database.

Properties:

• Advertisement File: When you want to display advertisements in the ad rotator from a XML file then this property is used to specify URL of the XML file from which to take the details of advertisements.

• Alternate Text Field: Used to specify column name in table that contains alternate tex for the advertisements.

• ImageUrlField: Used to specify column name in the table that contain Url of the image to display for the advertisements.

• Navigate Url Field: Used to specify the column name in table that contains Url of the page to display when user clicks on the advertisements.

• DataSource: Used to specify ID of a data source control to use as a data source for the ad rotator.

• KeywordFilter:Used to specify a keyword based on which advertisements displayed in the ad rotator will be filtered.

Calender Control

Calender is used to display calender to the user on the page and allow the user to select a date or a range of  dates.

Properties:

• DateNameFormat : Used to specify the format in which the week name is displayed within the calender.
• FirstDayofWeek:Used to specify week name in which you want to start the calender.
• SelectedDate: Used to specify the date selected within the calender.
• Selection Mode: Used to specify whether user can select a single date or day and week or day, week and month.

Events:

• SelectionChanged: This is the default event for the calender and it eill be raised whenever a new date is selected in the calender.
• DayRender: This event will be raised once for every day rendered in the calender.

DataGridVeiw Control

This control is used to display the data in the form of a table i.e. rows and columns. To display data in the control first we need to bind the DataTable of DataSet to the GridVeiw control by using a DataSource property as following
                  dataGridVeiw1.DataSource=<datatable>

DataGridVeiw control has a specialty ie changes performed to data in it gets reflected directly to the data of DataTable to which it was bound, so that we can update dataset back to database directly.

To test this add a new form in the project and place a DataGridVeiw control on it setting the dock property as top. Now place 2 buttons on the form setting the text as save and close

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;
using System.Configuration;

namespace WindowsFormsApp
{
    public partial class Form9 : Form
    {
        public Form9()
        {
            InitializeComponent();
        }
        SqlConnection con;
        SqlDataAdapter da;
        SqlCommandBuilder cb;
        DataSet ds;
        private void Form9_Load(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["SConstr"].ConnectionString;
            con = new SqlConnection(constr);
            ds = new DataSet();
            da = new SqlDataAdapter("Select Eno,Ename,Job,Salary From Employee Order By Eno", con);
            da.Fill(ds, "Employee");
            dataGridView1.DataSource = ds.Tables[0];

        }

        private void btnsave_Click(object sender, EventArgs e)
        {
            cb = new SqlCommandBuilder(da);
            da.Update(ds, "Employee");
            MessageBox.Show("Data saved to db sarver");
        }

        private void btnclose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Add a new form in the project and design it as following

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;
using Microsoft.VisualBasic;
namespace WindowsFormsApp
{
    public partial class Form7 : Form
    {
        SqlConnection con;
        SqlCommandBuilder cb;
        DataSet ds;
        int rno = 0;
        SqlDataAdapter da;

        public Form7()
        {
            InitializeComponent();
        }

        private void Form7_Load(object sender, EventArgs e)
        {
            con = new SqlConnection("server=localhost; database=db1;uid=cheryl;trusted_connection=true");
            da = new SqlDataAdapter("Select Eno,Ename,Job,Salary from Employee Order By Eno", con);
            ds = new DataSet();
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds, "Employee");
            ShowData();
        }
        public void ShowData()
        {
            txteno.Text = ds.Tables[0].Rows[rno][0].ToString();
            txtname.Text = ds.Tables[0].Rows[rno][1].ToString();
            txtjob.Text = ds.Tables[0].Rows[rno][2].ToString();
            txtsalary.Text = ds.Tables[0].Rows[rno][3].ToString();
        }

        private void btnfirst_Click(object sender, EventArgs e)
        {
            rno = 0;
            ShowData();
        }

        private void btnprev_Click(object sender, EventArgs e)
        {
            if (rno > 0)
            {
                rno -= 1; ;
                if (ds.Tables[0].Rows[rno].RowState == DataRowState.Deleted)
                {
                    MessageBox.Show("Deleted row cannot be accessed.");
                    return;
                }
                ShowData();
            }
            else MessageBox.Show("First record of table");
        }

        private void btnnext_Click(object sender, EventArgs e)
        {
            if (rno 0)
            {
                int Eno = int.Parse(value);
                DataRow dr = ds.Tables[0].Rows.Find(Eno);
                if (dr != null)
                {

                    txteno.Text = dr[0].ToString();
                    txtname.Text = dr[1].ToString();
                    txtjob.Text = dr[2].ToString();
                    txtsalary.Text = dr[3].ToString();
                }
                else
                {
                    MessageBox.Show("Employee doesnot exist for a given employee no.");
                }
            }

        }

        private void btnclose_Click(object sender, EventArgs e)
        {
            this.Close();

        }
    }
}

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.