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();
}
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();
}