Friday, February 10, 2012

C# Programming Database connection

http://vincshap.blogspot.com/2011/10/update-drop-down.html
http://vincshap.blogspot.com/2011/10/sql-server2005-with-data-add-delete.html


//handler Database
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Schedule_Generator
{
    class HandlerDatabase
    {
        SqlConnection connection = new SqlConnection("Data Source=priya-PC\\SQLEXPRESS;Initial Catalog=BANKDB;Integrated Security=True ");
        SqlCommand command = new SqlCommand();
        SqlDataAdapter adapter = null;

        public void ExicuteQuary(string quary)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            command.CommandText = quary;
            command.Connection = connection;
            command.ExecuteNonQuery();
            connection.Close();
        }
        public DataSet GetDataSet(string quary)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                adapter = new SqlDataAdapter(quary, connection);
            }
            DataSet dataset1 = new DataSet();

            adapter.Fill(dataset1);
            connection.Close();
            return dataset1;
        }
    }
}

//Data Tranceport

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Schedule_Generator
{
    class InputOutputFormDatahandler : HandlerDatabase
    {
        public void AddInputData(string LoNumber, double AnIRate, double LAmount, string ReSDate, int PeLoan)
        {
            string quary = "INSERT INTO InputData VALUES ('" + LoNumber + "','" + AnIRate + "','" + LAmount + "','" + ReSDate + "','" + PeLoan + "')";
            ExicuteQuary(quary);
        }
        public DataSet ViewLNumber()
        {
            DataSet GetGridDataSet = new DataSet();
            string quary1 = "SELECT LNumber FROM InputData ORDER BY LNumber ASC";
            GetGridDataSet = GetDataSet(quary1);
            return GetGridDataSet;
        }
        public DataSet ViewAll(string LoNumber)
        {
            DataSet GetGridDataSetAll = new DataSet();
            string quary1 = "SELECT * FROM InputData WHERE LNumber='" + LoNumber + "'";
            GetGridDataSetAll = GetDataSet(quary1);
            return GetGridDataSetAll;
        }
    }
}

//Data View

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 Schedule_Generator
{
    public partial class ViewData : Form
    {
        InputOutputFormDatahandler callInputFormDatahandler = new InputOutputFormDatahandler();
        int monthAll;
        decimal IntRepayment;
        decimal CaptRepayment;
        decimal TotRepayment;
        decimal RemCapital;
        decimal AIRate;
        decimal LAmount;
        decimal PLoan;
        DateTime STDate;

        public ViewData()
        {
            InitializeComponent();
        }

        private void ViewData_Load(object sender, EventArgs e)
        {
            DataSet datasetLNumber = new DataSet();
            datasetLNumber = callInputFormDatahandler.ViewLNumber();
            LNumber.DataSource = datasetLNumber.Tables[0];
            LNumber.DisplayMember = datasetLNumber.Tables[0].Columns["LNumber"].ToString();
        }

        private void deatails_Click(object sender, EventArgs e)
        {
            string LoNumber = LNumber.Text;

            // data grid deatails
            DateTime dt = DateTime.Now;
            string year = dt.Year.ToString();
            string month = dt.Month.ToString();
            string day = dt.Day.ToString();
            string date = year + "-" + month + "-" + day;
            dataGridView1.Rows[0].Cells[0].Value = date;

            DateTime DateToday = Convert.ToDateTime(date);

            DataSet datasetAll = new DataSet();
            datasetAll = callInputFormDatahandler.ViewAll(LoNumber);

            if (datasetAll.Tables[0].Rows.Count > 0)
            {
                string StartDate = datasetAll.Tables[0].Rows[0][3].ToString();
                STDate = Convert.ToDateTime(StartDate);

                int years = DateToday.Year - STDate.Year;
                int monthsDif = DateToday.Month - STDate.Month;

                monthAll = years * 12 + monthsDif;

            }
            try
            {
                AIRate = Convert.ToDecimal(datasetAll.Tables[0].Rows[0][1].ToString());
                LAmount = Convert.ToDecimal(datasetAll.Tables[0].Rows[0][2].ToString());
                PLoan = Convert.ToDecimal(datasetAll.Tables[0].Rows[0][4].ToString());
               
            }
            catch
            {
                MessageBox.Show("Not stored all values for this.");
            }
            decimal MRateTO = AIRate / 12;

            decimal MInterest = LAmount * MRateTO / 100;
            IntRepayment = MInterest * monthAll;

            CaptRepayment = (LAmount / PLoan) * monthAll;
            TotRepayment = IntRepayment + CaptRepayment;

            RemCapital = LAmount - CaptRepayment;

            dataGridView1.Rows[0].Cells[2].Value = Convert.ToDouble(IntRepayment);
            dataGridView1.Rows[0].Cells[1].Value = Convert.ToDouble(CaptRepayment);
            dataGridView1.Rows[0].Cells[3].Value = Convert.ToDouble(TotRepayment);
            dataGridView1.Rows[0].Cells[4].Value = Convert.ToDouble(RemCapital);

            //other details
            try
            {
                amount.Text = datasetAll.Tables[0].Rows[0][2].ToString();
                startdate.Text = STDate.Year + "-" + STDate.Month + "-" + STDate.Day;
                rate.Text = datasetAll.Tables[0].Rows[0][1].ToString();
                period.Text = datasetAll.Tables[0].Rows[0][4].ToString();
            }
            catch {
                MessageBox.Show("Not stored all values for this.");
            }
        }
    }
}
//ASP Drop down box
         private void ViewData_Load(object sender, EventArgs e)
        {
            DataSet datasetLNumber = new DataSet();
            datasetLNumber = callInputFormDatahandler.ViewLNumber();
            LNumber.DataSource = datasetLNumber.Tables[0];
            LNumber.DisplayMember = datasetLNumber.Tables[0].Columns["LNumber"].ToString();
        }

No comments:

Post a Comment