CREATE TABLE [dbo].[BULK] (
[ItemCode] NVARCHAR (50) NOT NULL,
[PieceCode] NVARCHAR (50) NOT NULL,
[InstCode] NVARCHAR (3) NOT NULL,
[PieceName] NVARCHAR (70) NULL,
[SectionCode] NVARCHAR (20) NOT NULL,
[AreaCode] NVARCHAR (20) NULL,
[DiscCode] NVARCHAR (4) NOT NULL,
[Date] NVARCHAR (20) NULL,
[MadeBy] NVARCHAR (10) NULL,
[PBSCode] NVARCHAR (5) NULL,
[SystemCode1] NVARCHAR (20) NULL,
[SystemCode2] NVARCHAR (20) NULL,
[SystemCode3] NVARCHAR (20) NULL,
[SystemCode4] NVARCHAR (20) NULL,
[SystemCode5] NVARCHAR (20) NULL,
[SystemCode6] NVARCHAR (20) NULL,
[SystemCode7] NVARCHAR (20) NULL,
[SystemCode8] NVARCHAR (20) NULL,
[SystemCode9] NVARCHAR (20) NULL,
[ProcCode] NVARCHAR (12) NULL,
[MatrCode] NVARCHAR (20) NULL,
[LiftCode] NVARCHAR (6) NULL,
[CORCode] NVARCHAR (7) NULL,
[NoOff] INT NULL,
[Quantity] DECIMAL (18, 6) NULL,
[UnitWeight] DECIMAL (18, 6) NOT NULL,
[UnitContent] DECIMAL (18, 6) NOT NULL,
[WeightStatusCode] NVARCHAR (6) NOT NULL,
[ConstrCode] NVARCHAR (2) NULL,
[Remark] NVARCHAR (100) NULL,
[XCoGlo] DECIMAL (18, 6) NOT NULL,
[YCoGlo] DECIMAL (18, 6) NOT NULL,
[ZCoGlo] DECIMAL (18, 6) NOT NULL,
[CategoryCode] NVARCHAR (1) NULL,
[PriceCode] NVARCHAR (50) NULL,
[Code1] NVARCHAR (50) NULL,
[Code2] NVARCHAR (50) NULL,
[MHC] NVARCHAR (3) NULL,
CONSTRAINT [PK_BULK] PRIMARY KEY CLUSTERED ([ItemCode] ASC, [PieceCode] ASC, [InstCode] ASC)
);

private void SaveDataButton_Click(object sender, EventArgs e)
{
int row = 0;
int numberOfItems = rowNumber;
string MS_SQL_Project_ConnectionString = Startmenu.MS_SQL_Project_ConnectionString;
string test;

using (SqlConnection connection = new SqlConnection(MS_SQL_Project_ConnectionString))
{
string sql = "UPDATE [BULK] SET PieceName = @PieceName, " +
"SectionCode = @SectionCode, AreaCode = @AreaCode, DiscCode = @DiscCode, Date = @Date, MadeBy = @MadeBy, " +
"PBSCode = @PBSCode, SystemCode1 = @SystemCode1, SystemCode2 = @SystemCode2, SystemCode3 = @SystemCode3, " +
"SystemCode4 = @SystemCode4, SystemCode5 = @SystemCode5, SystemCode6 = @SystemCode6, SystemCode7 = @SystemCode7, " +
"SystemCode8 = @SystemCode8, SystemCode9 = @SystemCode9, ProcCode = @ProcCode, MatrCode = @MatrCode, " +
"LiftCode = @LiftCode, CORCode = @CORCode, NoOff = @NoOff, Quantity = @Quantity, UnitWeight = @UnitWeight, " +
"UnitContent = @UnitContent, WeightStatusCode = @WeightStatusCode, ConstrCode = @ConstrCode, Remark = @Remark, " +
"XCoGlo = @XCoGlo, YCoGlo = @YCoGlo, ZCoGlo = @ZCoGlo, CategoryCode = @CategoryCode, PriceCode = @PriceCode, " +
"Code1 = @Code1, Code2 = @Code2, MHC = @MHC " +
"WHERE ItemCode = @ItemCode AND PieceCode = @PieceCode AND InstCode = @InstCode;";
connection.Open();

Application.DoEvents();

using (SqlCommand command = new SqlCommand(sql, connection))
{
while (row < numberOfItems)
{
//command.Parameters.AddWithValue("@ItemCode", list[i].ItemCode);
command.Parameters.AddWithValue("@ItemCode", fGrid.Cells[row, 0].Value);
//command.Parameters.AddWithValue("@PieceCode", list[i].PieceCode);
command.Parameters.AddWithValue("@PieceCode", fGrid.Cells[row, 1].Value);
//command.Parameters.AddWithValue("@InstCode", list[i].InstCode);
command.Parameters.AddWithValue("@InstCode", fGrid.Cells[row, 2].Value);
//command.Parameters.AddWithValue("@PieceName", list[i].PieceName);
command.Parameters.AddWithValue("@PieceName", fGrid.Cells[row, 3].Value);
command.Parameters.AddWithValue("@SectionCode", fGrid.Cells[row, 4].Value);
command.Parameters.AddWithValue("@AreaCode", fGrid.Cells[row, 5].Value);
command.Parameters.AddWithValue("@DiscCode", fGrid.Cells[row, 6].Value);
command.Parameters.AddWithValue("@Date", fGrid.Cells[row, 7].Value);
command.Parameters.AddWithValue("@MadeBy", fGrid.Cells[row, 8].Value);
command.Parameters.AddWithValue("@PBSCode", fGrid.Cells[row, 9].Value);
//command.Parameters.AddWithValue("@AreaCode", fGrid.Cells[row, 9].Value);
command.Parameters.AddWithValue("@SystemCode1", fGrid.Cells[row, 10].Value);
command.Parameters.AddWithValue("@SystemCode2", fGrid.Cells[row, 11].Value);
command.Parameters.AddWithValue("@SystemCode3", fGrid.Cells[row, 12].Value);
command.Parameters.AddWithValue("@SystemCode4", fGrid.Cells[row, 13].Value);
command.Parameters.AddWithValue("@SystemCode5", fGrid.Cells[row, 14].Value);
command.Parameters.AddWithValue("@SystemCode6", fGrid.Cells[row, 15].Value);
command.Parameters.AddWithValue("@SystemCode7", fGrid.Cells[row, 16].Value);
command.Parameters.AddWithValue("@SystemCode8", fGrid.Cells[row, 17].Value);
command.Parameters.AddWithValue("@SystemCode9", fGrid.Cells[row, 18].Value);
command.Parameters.AddWithValue("@ProcCode", fGrid.Cells[row, 19].Value);
command.Parameters.AddWithValue("@MatrCode", fGrid.Cells[row, 20].Value);
command.Parameters.AddWithValue("@LiftCode", fGrid.Cells[row, 21].Value);
command.Parameters.AddWithValue("@CORCode", fGrid.Cells[row, 22].Value);
test = fGrid.Cols[23].CellStyle.ValueType.Name;
command.Parameters.AddWithValue("@NoOff", fGrid.Cells[row, 23].Value);
test = fGrid.Cols[24].CellStyle.ValueType.Name;

command.Parameters.AddWithValue("@Quantity", fGrid.Cells[row, 24].Value);
test = fGrid.Cols[25].CellStyle.ValueType.Name;
command.Parameters.AddWithValue("@UnitWeight", fGrid.Cells[row, 25].Value);
command.Parameters.AddWithValue("@UnitContent", fGrid.Cells[row, 26].Value);
command.Parameters.AddWithValue("@WeightStatusCode", fGrid.Cells[row, 27].Value);
command.Parameters.AddWithValue("@ConstrCode", fGrid.Cells[row, 28].Value);
command.Parameters.AddWithValue("@Remark", fGrid.Cells[row, 29].Value);
command.Parameters.AddWithValue("@XCoGlo", fGrid.Cells[row, 30].Value);
command.Parameters.AddWithValue("@YCoGlo", fGrid.Cells[row, 31].Value);
command.Parameters.AddWithValue("@ZCoGlo", fGrid.Cells[row, 32].Value);
command.Parameters.AddWithValue("@CategoryCode", fGrid.Cells[row, 33].Value);
command.Parameters.AddWithValue("@PriceCode", fGrid.Cells[row, 34].Value);
command.Parameters.AddWithValue("@Code1", fGrid.Cells[row, 35].Value);
command.Parameters.AddWithValue("@Code2", fGrid.Cells[row, 36].Value);
command.Parameters.AddWithValue("@MHC", fGrid.Cells[row, 37].Value);
command.ExecuteNonQuery();

command.Parameters.Clear();

//command.ExecuteNonQueryAsync(); // Faster? Lots at the same time? Not tested.
row++;
ItemCountField.Text = Convert.ToString(row);
ItemCountField.Refresh();


}
}
connection.Close();
}
}

private void BULK_igrid_Excel_Load(object sender, EventArgs e)
{
string MS_SQL_Project_ConnectionString = Startmenu.MS_SQL_Project_ConnectionString;
string headerName = "Error: Not a header";
Type columnDataType;
string headerType;
rowNumber = 0;

using (SqlConnection projectConnection = new SqlConnection(MS_SQL_Project_ConnectionString))
{
projectConnection.Open();
using (SqlCommand command = new SqlCommand("SELECT * FROM [BULK];", projectConnection))
{
SqlDataReader reader = command.ExecuteReader();
if (reader != null) // Set Column Headers
{
for (int i = 0; i < reader.FieldCount; i++)
{
headerName = reader.GetName(i);
columnDataType = reader.GetFieldType(i);
headerType = columnDataType.Name;

fGrid.Cols.Add(headerName);

fGrid.Cols[i].CellStyle.ValueType = columnDataType;
if (headerType == "Decimal")
{
i = i + 1 - 1;
}

if (fGrid.Cols[i].CellStyle.ValueType == null)
{
i = i +1 -1; // ERROR!!!
}
if (headerType == "nvarchar") // Default value = ""
{
i = i + 1 - 1; // What is situation here?
//fGrid.Cols[i].CellStyle.
}

}
}
fGrid.DefaultRow.Height = fGrid.GetPreferredRowHeight(true, true); // Dynamic vis a vis DPI
fGrid.Rows.Count = 1000;

fGrid.BeginUpdate();
while (reader.Read()) // Set Row Values
{
for (int i = 0; i < reader.FieldCount; i++)
{


fGrid.Cells[rowNumber, i].Value = reader[i].ToString(); // One item in row read into grid
}

rowNumber++; // Row complete, next row. Also total number of Rows in grid.
if ((rowNumber % 1000) == 0) // Modulo
{
fGrid.Rows.Count = rowNumber + 1000;
}
}
reader.Close();
fGrid.Rows.Count = rowNumber;
//PopulateGrid(rowNumber);
fGrid.EndUpdate();

RowNumberField.Text = Convert.ToString(rowNumber);
RowNumberField.Refresh();
}
projectConnection.Close();
}
}

private void BULK_iGrid_FillWithData_Load(object sender, EventArgs e)
{
Application.UseWaitCursor = true;
Cursor.Position = Cursor.Position;
Application.DoEvents();

try
{
string MS_SQL_Project_ConnectionString = Startmenu.MS_SQL_Project_ConnectionString;
using (SqlConnection projectConnection = new SqlConnection(MS_SQL_Project_ConnectionString))
{
projectConnection.Open();
using (SqlCommand command = new SqlCommand("SELECT * FROM [BULK];", projectConnection))
{
fGrid.FillWithData(command);
}
}
}
catch (Exception ex)
{
Application.UseWaitCursor = false;
Cursor.Position = Cursor.Position;

Debug.WriteLine("Exception Message: " + ex.Message);
}

Application.UseWaitCursor = false;
Cursor.Position = Cursor.Position;
}