Sunday 28 April 2013

C# Database handler Class

A class I have written to handle dabase processes of a datagrid.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using System.Drawing;

namespace StockCommander
{
    class databaseHandle
    {
        String connectionString = @"Data Source=(localdb)\Projects;Initial Catalog=test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;";
        SqlDataAdapter dataAdapter;
        DataTable table = new DataTable();

        public void GetData(string selectCommand, ref DataGridView dataGrid)
        {
            try
            {
                dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
                table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;

                dataAdapter.Fill(table);
                dataGrid.DataSource = table;

                dataGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            }
            catch (SqlException)
            {
                MessageBox.Show("SQL Exception occured...");
            }
        }
        public void GetData(string selectCommand, ref DataGridView dataGrid, int identityColumn)
        {
            try
            {
                dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
                table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;

                dataAdapter.Fill(table);
                dataGrid.DataSource = table;
                table.Columns[identityColumn].AutoIncrement = true;
                table.Columns[identityColumn].AutoIncrementSeed = returnLastId();

                dataGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            }
            catch (SqlException)
            {
                MessageBox.Show("SQL Exception occured...");
            }
        }
        public void updateData()
        {
            dataAdapter.Update(table);
        }

        private int returnLastId()
        {
            return table.Rows.Count + 1;
        }

        public void deleteRows(IEnumerator rowColl, ref DataGridView dataGrid)
        {
            while (rowColl.MoveNext())
            {
                DataGridViewRow currRow = (DataGridViewRow)rowColl.Current;
                dataGrid.Rows.Remove(currRow);

            }
        }
        public void setRowReadOnlyRows(ref DataGridView dataGrid,int i)
        {
           
            dataGrid.Rows[i].ReadOnly = true;
            dataGrid.Rows[i].DefaultCellStyle.BackColor = Color.LightGray;
        }

        public void setRowReadOnlyRows(ref DataGridView dataGrid)
        {
            for (int i = 0; i <= dataGrid.RowCount - 2; i++)
            {
                setRowReadOnlyRows(ref dataGrid, i);
            }
        }

    }
}

Friday 8 March 2013

Party Model

Currently implementing a "Agreement" level party model and just a basic party model.
Some of the implementation include a library of addresses/phone records.


Will add some Python code on how I handled the library concept.  As there is some complications to ensure that addresses is not being duplicated and that multiple addresses is linked to a single party and if updated the link is changed.

Monday 11 February 2013

Errors :(

I got a error: "SQL0668N Operation not allowed for reason code 5".

The web and DB2 help gave me the description that a load was busy hapening which in this case was not True.
In the end the real error was because I had was doing a load as below:

DECLARE CURSOR1 CURSOR FOR
SELECT column1,column2
from table1 where ID not in (select ID from TABLE2);



LOAD FROM CURSOR1OF CURSOR
    INSERT INTO table2 (col1,col2);

Because the Cursor contains the table in which the load is being done it gives the reason code 5.

Thursday 31 January 2013

IBM Data studio

A quick update on my experience.  The selects is working great, the only thing I am missing currently from my winsql is that I can click on a foreign key and it retrieves the row for me.

Optimization

Next week I am planning a in-depth analysis on my experience in optimization a data take on script. The scripts writes 20 million to a staging file then do data manipulation and writes to several different tables.

Then there is about 1 million records to be compared with the 20 million records and then written to a staging area and then manipulated before written to multiple tables.

Monday 14 January 2013

IBM Data Studio

Because of the JAVA it takes a bit of resources and is a bit slow. Otherwise I have no complaints. All the database and sql management operations can be done on this tool.

Some searching may be required to do some advanced operations.

Wednesday 9 January 2013

IBM Data studio

Update:
First impressions is not bad, the only current issue I have is the amount of data that has to be downloaded. The initial download is to installation package about 160mb then a further 800mb for the rest of the packages. 


New Development Tool

Downloading the IBM Data studio and will give feedback if it actually helps in a business environment. The reason for downloading is that I have a couple of functions and scripts that needs some debuging and statistical information. So hopes this helps.

Current development software is WINSQL...

http://www.ibm.com/developerworks/downloads/im/data/