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

Monday, May 30, 2011

Automatically save an Email attachment to a Database

This article explains how the UltraESB is used to connect an email inbox and  a database.
The sample which is described below supports the ESB to get  an email attachment and it can be saved to a table separately in the database.
The UltraESB supports polling both pop3 and IMAP email boxes . In this example we will poll the GMail account, "sunethcha@gmail.com" every 10 seconds and get attachment separately and send it's include data to database by UltraESB.  
We can get idea about this scenario by using image below,

Email attachment to database

To solve this scenario, first we need to create a database

Creating a database is easy
In this example we use the derby database which ships with the JDK, to host our table. Following link will provide you a guide to set up a sample database with Derby
in this example I have created database, named "ATTACHDB". Before creating it you need to run the network server and ./ij  (as mentioned in the above link). Afterwards the database is created and connected, by executing the following commands. 
connect 'jdbc:derby://localhost:1527/database;create=true;user=admin;password=admin';

Create Database
Create table
Tables are created by executing following commands

CREATE TABLE ADMIN.ATTACHMENT ("CODENAME" varchar(20),"CODE" varchar(500));

Create table
sample - 504
This sample have three special beans ,


        
            
                
                
                
                
                
                    
                
            
        
    


This first bean is used to listen to mails, second and third beans  are used to connect with the database.


Properties of the transport declaration is as follows.


                    
                
            
            
            
                     
        

The "mainPartIdentificationPriority" is an important property of the above declaration.


The below Java code fragment  is use to separate and save data to the database



Run sample - 504


Add the sample xml file to the "ultraesb-1.4.0/samples/conf" directory. Finally run the ESB with the sample code, by running the following command.

Start sample with UltraESB
The file.txt is sent as a mail attachment and the content of the file is written to the database.

soap.txt