Oleh: ekoyw | Mei 12, 2010

myQuery

Class dalam bahasa C#.NET ini sebenarnya telah aku susun sejak beberapa tahun lalu. Tapi dasar, karena hanya dikerjakan secara sambilan maka belum pernah sempurna meskipun sampai saat ini.

Meskipun demikian, Class ini masih berguna membantuku menyusun beberapa software database seperti myBSS yang juga dikerjakan secara sambilan…. hehehe. Tetapi sampai sekarang software myBSS ini sangat berguna mendukung pekerjaan utamaku. Enaknya, karena myBSS ini dikembangkan sendiri sehingga ketika ditemukan bug maka tinggal dibenerin, di-compile ulang dan selesai deh…

myQuery ini adalah class untuk akses database. Dalam contoh di bawah menggunakan database MySQL dan koneksi ODBC. Beberapa tahun sebelumnya pernah aku buat dalam versi Microsoft SQL Server dan bisa jalan. Tetapi yang terakhir kali ini belum pernah aku kembangkan lagi.

Bagi yang berminat… silahkan di-copy dan dibetulkan  jika ada yang kurang serta dilengkapi sesuai kebutuhan.

MyQuery ini antara lain berisi : Create Connection, Create SQL Statement in MySQL (e.g : SELECT, UPDATE, DELETE Statement), mendapatkan foreignkey dan tabelnya, mendapatkan koneksi antar tabel, dll.

Di bagian terakhir terdapat satu procedure untuk convert ke XML dari sebuah DataGridView.

===========================================

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Collections;
using System.Data.Odbc;
using System.Windows.Forms;
using System.Reflection;

namespace DataBrowser
{
class Query
{
private SqlConnection mySQLconn;
private OdbcConnection myOdbc;
private string compName;
private string dbName;
private string connectionString;

#region Properties
/// <summary>
/// Get or set the name of computer where database is hosted
/// </summary>
public string ServerName
{
get
{
return this.compName;
}
set
{
this.compName = value;
}
}

/// <summary>
/// Get or set the database name
/// </summary>
public string DataBaseName
{
get
{
return this.dbName;
}
set
{
this.dbName = value;
}
}
#endregion

#region Data Operation

/// <summary>
/// Set Database tanpa perlu connect SQL
/// </summary>
public void SetDatabase()
{
this.compName = this.ServerName;
this.dbName = this.DataBaseName;
}

/// <summary>
/// Create Connection String
/// </summary>
/// <returns></returns>
private string CreateConnectionString()
{
//connectionString = “Server=” + this.ServerName + “;Database=” + this.DataBaseName + “;Integrated Security=SSPI;”;
//connectionString = “DSN = DBMySQL; UID = root; PWD = sa”;
//this.compName = “IP-79075971”;
//this.dbName = “BSS Lubuk Linggau”;
connectionString = “DRIVER={MySQL ODBC 3.51 Driver};” + “SERVER=” + this.ServerName + “;DATABASE=” + this.DataBaseName + “;UID=root;” + “PASSWORD=sa;” + “OPTION=3″;
return connectionString;
}

/// <summary>
/// Create Connection to database
/// </summary>
/// <returns></returns>
public OdbcConnection CreateSqlConnection()
{
return new OdbcConnection(this.CreateConnectionString());
}

/// <summary>
/// Close connection to database
/// </summary>
/// <returns></returns>
public OdbcConnection CloseSQLConnection()
{
this.mySQLconn.Close();
return this.myOdbc;
}

/// <summary>
/// Create Data Adapter
/// </summary>
/// <returns></returns>
private IDataAdapter CreateDataAdapter()
{
return new OdbcDataAdapter();
}

/// <summary>
/// Create SQL Command
/// </summary>
/// <param name=”cmdText”></param>
/// <returns></returns>
private IDbCommand CreateCommand(string cmdText)
{
return new OdbcCommand(cmdText);
}

/// <summary>
/// Create data adapter from SQL Command and connection
/// </summary>
/// <param name=”command”></param>
/// <param name=”connection”></param>
/// <returns></returns>
private IDataAdapter CreateDataAdapter(string command, IDbConnection connection)
{
OdbcDataAdapter adapter1 = (OdbcDataAdapter)this.CreateDataAdapter();
adapter1.SelectCommand = (OdbcCommand)this.CreateCommand(command);
adapter1.SelectCommand.Connection = (OdbcConnection)connection;
return adapter1;
}

/// <summary>
/// Untuk mendapatkan isi dari tabel sesuai dengan SQLCommand
/// </summary>
/// <param name=”TableName”></param>
/// <returns></returns>
public DataTable GetData(string SqlCommand)
{
if (this.myOdbc == null)
{
this.myOdbc = this.CreateSqlConnection();
}
OdbcDataAdapter adapter1 = (OdbcDataAdapter)this.CreateDataAdapter(SqlCommand, this.myOdbc);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

private void AddParameter(OdbcCommand cmd, params string[] cols)
{
foreach (string col in cols)
{
cmd.Parameters.Add(“@” + col, OdbcType.Char, 0, col);
}

}

private string InsertText(string tabel, string[] namefield)
{
int a, fieldcount = 0;
fieldcount = namefield.Length;
string fieldname = “”;
string fieldparam = “”;
string instxt = “”;
for (a = 0; a <= fieldcount – 2; a++)
{
fieldname = fieldname + namefield[a];
fieldname = fieldname + ” , “;
fieldparam = fieldparam + “@” + namefield[a];
fieldparam = fieldparam + ” , “;
}
fieldname = fieldname + namefield[fieldcount – 1];
fieldparam = fieldparam + “@” + namefield[fieldcount – 1];
instxt = “INSERT INTO ” + tabel + “(” + fieldname + “) VALUES ( ” + fieldparam + “)”;
return instxt;
}

private string UpdateText(string tabel, string[] namefield, string DataOI)
{
int a, fieldcount = 0;
fieldcount = namefield.Length;
string updatetxt = “”;

for (a = 1; a <= fieldcount – 2; a++)
{
updatetxt = updatetxt + namefield[a] + ” =@” + namefield[a];
updatetxt = updatetxt + “,”;
}
updatetxt = updatetxt + namefield[fieldcount – 1] + ” =@” + namefield[fieldcount – 1];
updatetxt = “UPDATE ” + tabel + ” SET ” + updatetxt + ” WHERE ” + DataOI + ” =@” + DataOI;
return updatetxt;
}

private string DeleteText(string tabel, string DataOI)
{
string deltext = “”;
deltext = ” DELETE FROM ” + tabel + ” WHERE ” + DataOI + ” =@” + DataOI;
return deltext;
}

/// <summary>
/// Get a list of fields from a table
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public DataTable GetListOfTableFields(string tableName)
{
string SQLCommand = “Show Columns From ” + tableName;

if (this.myOdbc == null)
{
this.myOdbc = this.CreateSqlConnection();
}
OdbcDataAdapter adapter1 = (OdbcDataAdapter)this.CreateDataAdapter(SQLCommand, this.myOdbc);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

/// <summary>
/// Mendapatkan Foreign Key Column dan Table Reference
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public DataTable GetForeignKey(string tableName)
{
string SQLCommand = “SELECT Table_schema, Table_Name, Column_Name, ” +
“Referenced_Table_Schema, Referenced_Table_Name, Referenced_Column_Name ” +
“FROM information_schema.KEY_COLUMN_USAGE ” +
“WHERE Table_Schema LIKE ‘” + this.DataBaseName + “‘ AND Table_Name LIKE ‘” + tableName + “‘ ” +
“AND Constraint_Name != ‘Primary'”;
if (this.myOdbc == null)
{
this.myOdbc = this.CreateSqlConnection();
}
OdbcDataAdapter adapter1 = (OdbcDataAdapter)this.CreateDataAdapter(SQLCommand, this.myOdbc);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

/// <summary>
/// Untuk mendapatkan tipe data setiap table
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public DataTable GetTableDataType(string tableName, string FieldName)
{
string SQLCommand = “SELECT * FROM information_schema.`COLUMNS` ” +
“WHERE Table_Schema LIKE ‘” + this.DataBaseName + “‘ AND Table_Name LIKE ‘” + tableName +
“‘ AND COLUMN_NAME LIKE ‘” + FieldName + “‘”;
if (this.myOdbc == null)
{
this.myOdbc = this.CreateSqlConnection();
}
OdbcDataAdapter adapter1 = (OdbcDataAdapter)this.CreateDataAdapter(SQLCommand, this.myOdbc);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

/// <summary>
/// Mendapatkan nilai data sebenarnya dari Foreign Key
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public DataTable GetForeignValue(string tableName)
{
DataTable ForeignData = this.GetForeignKey(tableName);
DataTable Hasil = new DataTable();
DataTable TableForeign = new DataTable();
DataRow FieldRow = null;
bool sama = false;
Hasil.Columns.Add(“TableName”, typeof(string));
Hasil.Columns.Add(“FieldName”, typeof(string));
Hasil.Columns.Add(“FieldID”, typeof(string));
Hasil.Columns.Add(“OpenTable”, typeof(string));
Hasil.Columns.Add(“FieldValueName”, typeof(string));
Hasil.Columns.Add(“ParentTable”, typeof(string));
Hasil.Columns.Add(“ParentField”, typeof(string));

if (ForeignData.Rows.Count > 0)
{
TableForeign = this.GetListOfTableFields(ForeignData.Rows[0][“Referenced_Table_Name”].ToString());
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[0];
FieldRow.BeginEdit();
//Nama Tabel Foreign Key. mis : BSC
FieldRow[“TableName”] = ForeignData.Rows[0][“Referenced_Table_Name”].ToString();
//Name Field Referensi Foreign Key. mis : bsc_id
FieldRow[“FieldID”] = ForeignData.Rows[0][“Referenced_Column_Name”].ToString();
//Nama Field yang berisi nilai dari yang ditunjuk FK. mis BSCName
FieldRow[“FieldName”] = TableForeign.Rows[1][“Field”].ToString();
FieldRow[“FieldValueName”] = TableForeign.Rows[1][“Field”].ToString();
//nama Tabel yang dipanggil/ditampilkan
FieldRow[“OpenTable”] = ForeignData.Rows[0][“Referenced_Table_Name”].ToString();
//Nama Tabel Parent
FieldRow[“ParentTable”] = ForeignData.Rows[0][“Table_Name”].ToString();
//Nama Column Parent
FieldRow[“ParentField”] = ForeignData.Rows[0][“Column_Name”].ToString();

FieldRow.EndEdit();

for (int a = 1; a <= ForeignData.Rows.Count – 1; a++)
{
TableForeign = this.GetListOfTableFields(ForeignData.Rows[a][“Referenced_Table_Name”].ToString());
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[a];
FieldRow.BeginEdit();
//FieldRow[“TableName”] = ForeignData.Rows[a][“Referenced_Table_Name”].ToString();
FieldRow[“FieldID”] = ForeignData.Rows[a][“Referenced_Column_Name”].ToString();
FieldRow[“FieldValueName”] = TableForeign.Rows[1][“Field”].ToString();
FieldRow[“ParentTable”] = ForeignData.Rows[a][“Table_Name”].ToString();
FieldRow[“ParentField”] = ForeignData.Rows[a][“Column_Name”].ToString();

for (int b = 0; b <= a – 1; b++)
{
if (ForeignData.Rows[a][“Referenced_Table_Name”].ToString() == ForeignData.Rows[b][“Referenced_Table_Name”].ToString())
{
sama = true;
break;
}
}
if (!sama)
{
FieldRow[“TableName”] = ForeignData.Rows[a][“Referenced_Table_Name”].ToString();
FieldRow[“FieldName”] = TableForeign.Rows[1][“Field”].ToString();
//FieldRow[“FieldValueName”] = TableForeign.Rows[1][“Field”].ToString();
FieldRow[“OpenTable”] = ForeignData.Rows[a][“Referenced_Table_Name”].ToString();

}
else
{
FieldRow[“TableName”] = ForeignData.Rows[a][“Table_Name”].ToString();
FieldRow[“FieldName”] = ForeignData.Rows[a][“Column_Name”].ToString();
//FieldRow[“FieldValueName”] = TableForeign.Rows[1][“Field”].ToString();
FieldRow[“OpenTable”] = ForeignData.Rows[a][“Referenced_Table_Name”].ToString();
}
sama = false;

FieldRow.EndEdit();
}
}
return Hasil;
}

/// <summary>
/// Mendapatkan nama field, termasuk foreign field, dari sebuah tabel
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public DataTable GetColumnNameofTable(string tableName)
{
DataTable TableUtama = this.GetListOfTableFields(tableName);
DataTable TableForeign = this.GetForeignKey(tableName);
bool Match = false;
int baris = 0;
string FieldTable, FieldForeign = “”;

DataTable Hasil = new DataTable();
DataRow FieldRow = null;
Hasil.Columns.Add(“TableName”, typeof(string));
Hasil.Columns.Add(“FieldName”, typeof(string));

FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[0];
FieldRow.BeginEdit();
FieldRow[“TableName”] = tableName;
FieldRow[“FieldName”] = TableUtama.Rows[0][“Field”].ToString();
FieldRow.EndEdit();

for (int a = 1; a <= TableUtama.Rows.Count – 1; a++)
{
Match = false;
for (int b = 0; b <= TableForeign.Rows.Count – 1; b++)
{
FieldTable = TableUtama.Rows[a][“Field”].ToString();
FieldForeign = TableForeign.Rows[b][“Column_Name”].ToString();
if (FieldTable == FieldForeign)
{
Match = true;
baris = b;
break;
}
}
if (!Match)
{
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[a];
FieldRow.BeginEdit();
FieldRow[“TableName”] = tableName;
FieldRow[“FieldName”] = TableUtama.Rows[a][“Field”].ToString();
FieldRow.EndEdit();
}
else
{
DataTable TableRef = this.GetData(“SHOW COLUMNS FROM ” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString());
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[a];
FieldRow.BeginEdit();
FieldRow[“TableName”] = TableForeign.Rows[baris][“Referenced_Table_Name”].ToString();
FieldRow[“FieldName”] = TableRef.Rows[1][0].ToString();
FieldRow.EndEdit();
}
}

return Hasil;
}

/// <summary>
/// Create SQL Command untuk menampilkan isi table beserta foreign key-nya
/// </summary>
/// <param name=”tableName”></param>
/// <returns></returns>
public string CreateSQLCommand(string tableName)
{
DataTable TableUtama = this.GetListOfTableFields(tableName);
DataTable TableForeign = this.GetForeignKey(tableName);
bool Match= false;
int baris = 0;
string FieldTable, FieldForeign, hasil = “”;
bool sama = false;
bool cocok = false;

string SelectCommand = “SELECT “;
string WHERECommand = ” WHERE “;
string Field1, FKField1, FROMCommand = string.Empty;
int barisku = 0;

for (int c = 0; c <= TableForeign.Rows.Count – 1; c++)
{
Field1 = TableUtama.Rows[0][“Field”].ToString();
FKField1 = TableForeign.Rows[c][“Column_Name”].ToString();
if (Field1 == FKField1)
{
cocok = true;
barisku = c;
break;
}
}
if (cocok)
FROMCommand = ” FROM ” + tableName + ” ” + tableName + “, ” +
TableForeign.Rows[barisku][“Referenced_Table_Name”].ToString() + ” ” + TableForeign.Rows[barisku][“Referenced_Table_Name”].ToString();
else
FROMCommand = ” FROM ” + tableName + ” ” + tableName;
cocok = false;

SelectCommand = SelectCommand + tableName + “.” + TableUtama.Rows[0][“Field”].ToString();
for (int a = 1; a <= TableUtama.Rows.Count – 1; a++)
{
Match = false;
if (TableForeign.Rows.Count > 0)
{
for (int b = 0; b <= TableForeign.Rows.Count – 1; b++)
{
FieldTable = TableUtama.Rows[a][“Field”].ToString();
FieldForeign = TableForeign.Rows[b][“Column_Name”].ToString();
if (FieldTable == FieldForeign)
{
Match = true;
baris = b;
break;
}
}
if (!Match)
{
SelectCommand = SelectCommand + “,” + tableName + “.” + TableUtama.Rows[a][“Field”].ToString();
}
else
{
DataTable TableRef = this.GetData(“SHOW COLUMNS FROM ” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString());
//SelectCommand = SelectCommand + “,” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString() + “.” + TableRef.Rows[1][0].ToString();
for (int i = 0; i <= baris-1; i++)
{
if (TableForeign.Rows[baris][“Referenced_Table_Name”].ToString() == TableForeign.Rows[i][“Referenced_Table_Name”].ToString())
{
sama = true;
break;
}
}
if (!sama)
{
FROMCommand = FROMCommand + ” , ” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString() + ” ” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString();
SelectCommand = SelectCommand + “,” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString() + “.” + TableRef.Rows[1][0].ToString();
}
else
{
FROMCommand = FROMCommand;
SelectCommand = SelectCommand + “,” + TableForeign.Rows[baris][“Referenced_Table_Name”].ToString() + “.” + TableRef.Rows[1][0].ToString() + ” As ” + TableUtama.Rows[a][“Field”].ToString();
}
sama = false;
}
}
else
{
SelectCommand = SelectCommand + “,” + tableName + “.” + TableUtama.Rows[a][“Field”].ToString();
}
SelectCommand = SelectCommand;
}

if (TableForeign.Rows.Count > 0)
{
WHERECommand = WHERECommand + ” ” + TableForeign.Rows[0][“Table_Name”].ToString() + “.” + TableForeign.Rows[0][“Column_Name”].ToString() +
” = ” + TableForeign.Rows[0][“Referenced_Table_Name”].ToString() + “.” + TableForeign.Rows[0][“Referenced_Column_Name”].ToString();
for (int b = 1; b <= TableForeign.Rows.Count – 1; b++)
{
for (int i = 0; i <= b – 1; i++)
{
if (TableForeign.Rows[i][“Referenced_Table_Name”].ToString() == TableForeign.Rows[b][“Referenced_Table_Name”].ToString())
{
sama = true;
break;
}
}
if(!sama)
WHERECommand = WHERECommand + ” AND ” + TableForeign.Rows[b][“Table_Name”].ToString() + “.” + TableForeign.Rows[b][“Column_Name”].ToString() +
” = ” + TableForeign.Rows[b][“Referenced_Table_Name”].ToString() + “.” + TableForeign.Rows[b][“Referenced_Column_Name”].ToString();
WHERECommand = WHERECommand;
sama = false;
}
hasil = SelectCommand + FROMCommand + WHERECommand;
}
else
hasil = SelectCommand + FROMCommand;

return hasil;
}

/// <summary>
/// Create INSERT Command untuk memasukkan data
/// </summary>
/// <param name=”tableName”>Nama Table</param>
/// <param name=”InsertData”>Datatabel yang terdiri dari 2 Kolom, TableField dan Isi Data</param>
/// <returns></returns>
public string CreateInsertSQLCommand(string tableName, DataTable InsertData)
{
string SQLInsert = “INSERT INTO ” + tableName;
string Value = “”;
string fieldname = ” (” + InsertData.Rows[0][0].ToString();

DataTable ForeignKey = this.GetForeignKey(tableName);

//Cek ada ForeignKey atau tidak
if (ForeignKey.Rows.Count > 0)
{
DataTable ForeignData = this.GetForeignValue(tableName);
DataTable myfirstData = this.GetTableDataType(tableName, InsertData.Rows[0][0].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
{
if (InsertData.Rows[0][1].ToString() == null)
Value = ” VALUE (0″;
else
Value = ” VALUE (” + InsertData.Rows[0][1].ToString();
}
else
Value = ” VALUE (‘” + InsertData.Rows[0][1].ToString() + “‘”;

//string Value = ” VALUE (” + InsertData.Rows[0][1].ToString();
//string fieldname = ” (” + InsertData.Rows[0][0].ToString();
bool Match = false;
int baris = 0;

for (int a = 1; a <= InsertData.Rows.Count – 1; a++)
{
Match = false;
for (int b = 0; b <= ForeignData.Rows.Count – 1; b++)
{
if (InsertData.Rows[a][0].ToString() == ForeignData.Rows[b][“FieldName”].ToString())
{
Match = true;
baris = b;
break;
}
}
if(!Match)
{
DataTable DataType = this.GetTableDataType(tableName, InsertData.Rows[a][0].ToString());
fieldname = fieldname + “,” + InsertData.Rows[a][0].ToString();
if (DataType.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == “”)
{
if (InsertData.Rows[a][1].ToString() == “”)
Value = Value + “,0”;
else
{
if (DataType.Rows[0][“Data_Type”].ToString() == “date”)
Value = Value + “,DATE(‘” + InsertData.Rows[a][1].ToString() + “‘)”;
else if (DataType.Rows[0][“Data_Type”].ToString() == “datetime”)
Value = Value + “,'” + InsertData.Rows[a][1].ToString() + “‘”;
else
Value = Value + “,” + InsertData.Rows[a][1].ToString();
}
}
else
Value = Value + “,'” + InsertData.Rows[a][1].ToString() + “‘”;
}
else
{
DataTable DataType = this.GetTableDataType(tableName, ForeignData.Rows[baris][“FieldID”].ToString());
DataTable Datahasil = this.GetData(“SELECT * FROM ” + ForeignData.Rows[baris][“TableName”].ToString() +
” WHERE ” + ForeignData.Rows[baris][“FieldName”].ToString() + “= ‘” + InsertData.Rows[a][“FieldData”].ToString() + “‘”);
fieldname = fieldname + “,” + ForeignData.Rows[baris][“ParentField”].ToString();
if (DataType.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == “”)
{
if (Datahasil.Rows[0][0].ToString() == “”)
Value = Value + “,0”;
else
Value = Value + “,” + Datahasil.Rows[0][0].ToString();
}
else
Value = Value + “,'” + Datahasil.Rows[0][0].ToString() + “‘”;
}
fieldname = fieldname;
Value = Value;
}
}
//Jika tidak ada Foreign key
else
{
DataTable myfirstData = this.GetTableDataType(tableName, InsertData.Rows[0][0].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
{
if (InsertData.Rows[0][1].ToString() == null)
Value = ” VALUE (0″;
else
Value = ” VALUE (” + InsertData.Rows[0][1].ToString();
}
else
Value = ” VALUE (‘” + InsertData.Rows[0][1].ToString() + “‘”;
//string fieldname = ” (” + InsertData.Rows[0][0].ToString();
for (int a = 1; a <= InsertData.Rows.Count – 1; a++)
{
DataTable DataType = this.GetTableDataType(tableName, InsertData.Rows[a][0].ToString());
fieldname = fieldname + “,” + InsertData.Rows[a][0].ToString();
if (DataType.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == “”)
{
if (InsertData.Rows[a][1].ToString() == “”)
Value = Value + “,0”;
else
{
if (DataType.Rows[0][“Data_Type”].ToString() == “date”)
Value = Value + “,DATE(‘” + InsertData.Rows[a][1].ToString() + “‘)”;
else if (DataType.Rows[0][“Data_Type”].ToString() == “datetime”)
Value = Value + “,'” + InsertData.Rows[a][1].ToString() + “‘”;
else
Value = Value + “,” + InsertData.Rows[a][1].ToString();
}
}
else
Value = Value + “,'” + InsertData.Rows[a][1].ToString() + “‘”;
fieldname = fieldname;
Value = Value;
}
}

SQLInsert = SQLInsert + fieldname + “) ” + Value + “)”;
return SQLInsert;
}

/// <summary>
/// Untuk update data yang telah diedit
/// </summary>
/// <param name=”tablename”></param>
/// <returns></returns>
public string UpdateSQLCommand(string tableName, DataTable UpdateData, string PKTable)
{
string SQLUpdate = “UPDATE ” + tableName;
string Set = ” SET “;
string Where = ” WHERE “;
string backslash = “”;
DataTable ForeignKey = this.GetForeignKey(tableName);

// Jika ada Foreign Key
if (ForeignKey.Rows.Count > 0)
{
DataTable ForeignData = this.GetForeignValue(tableName);

DataTable myfirstData = this.GetTableDataType(tableName, UpdateData.Rows[0][0].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
{
Set = Set + UpdateData.Rows[0][0].ToString() + “=” + UpdateData.Rows[0][1].ToString();
Where = Where + UpdateData.Rows[0][0].ToString() + “=” + PKTable;
}
else
{
Set = Set + UpdateData.Rows[0][0].ToString() + “='” + UpdateData.Rows[0][1].ToString() + “‘”;
Where = Where + UpdateData.Rows[0][0].ToString() + “='” + PKTable + “‘”;
}

bool Match = false;
int baris = 0;

for (int a = 1; a <= UpdateData.Rows.Count – 1; a++)
{
//backslash = SisipBackSlash(UpdateData.Rows[a][1].ToString());
Match = false;
for (int b = 0; b <= ForeignData.Rows.Count – 1; b++)
{
if (UpdateData.Rows[a][0].ToString() == ForeignData.Rows[b][“FieldName”].ToString())
{
Match = true;
baris = b;
break;
}
}
if (!Match)
{
DataTable DataType = this.GetTableDataType(tableName, UpdateData.Rows[a][0].ToString());
if (DataType.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == “”)
{
if (UpdateData.Rows[a][1].ToString() == “”)
Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “= 0”;
else
{
if (DataType.Rows[0][“Data_Type”].ToString() == “date”)
Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “=DATE(‘” + UpdateData.Rows[a][1].ToString() + “‘)”;
else if (DataType.Rows[0][“Data_Type”].ToString() == “datetime”)
Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “= ‘” + UpdateData.Rows[a][1].ToString() + “‘”;
else
//Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “=” + backslash;
Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “=” + UpdateData.Rows[a][1].ToString();
}

}
else
//Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “='” + backslash + “‘”;
Set = Set + “,” + UpdateData.Rows[a][0].ToString() + “='” + UpdateData.Rows[a][1].ToString() + “‘”;
}
else
{
DataTable DataType = this.GetTableDataType(tableName, ForeignData.Rows[baris][“FieldID”].ToString());
DataTable Datahasil = this.GetData(“SELECT * FROM ” + ForeignData.Rows[baris][“OpenTable”].ToString() +
” WHERE ” + ForeignData.Rows[baris][“FieldValueName”].ToString() + “= ‘” + UpdateData.Rows[a][“FieldData”].ToString() + “‘”);
if (DataType.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == “”)
{
if (Datahasil.Rows[0][0].ToString() == “”)
Set = Set + “,” + ForeignData.Rows[baris][“ParentField”].ToString() + “= 0”;
else
Set = Set + “,” + ForeignData.Rows[baris][“ParentField”].ToString() + “=” + Datahasil.Rows[0][0].ToString();
}
else
Set = Set + “,” + ForeignData.Rows[baris][“ParentField”].ToString() + “='” + Datahasil.Rows[0][0].ToString() + “‘”;
}
Set = Set;
}
}

// Jika tidak ada foreign key
else
{
DataTable myfirstData = this.GetTableDataType(tableName, UpdateData.Rows[0][0].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
{
Set = Set + UpdateData.Rows[0][0].ToString() + “=” + UpdateData.Rows[0][1].ToString();
Where = Where + UpdateData.Rows[0][0].ToString() + “=” + PKTable;
}
else
{
Set = Set + UpdateData.Rows[0][0].ToString() + “='” + UpdateData.Rows[0][1].ToString() + “‘”;
Where = Where + UpdateData.Rows[0][0].ToString() + “='” + PKTable + “‘”;
}

for (int a = 1; a <= UpdateData.Rows.Count – 1; a++)
{
myfirstData = this.GetTableDataType(tableName, UpdateData.Rows[a][0].ToString());
//backslash = SisipBackSlash(UpdateData.Rows[a][1].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
Set = Set + “, ” + UpdateData.Rows[a][0].ToString() + “=” + UpdateData.Rows[a][1].ToString();
else
Set = Set + “, ” + UpdateData.Rows[a][0].ToString() + “='” + UpdateData.Rows[a][1].ToString() + “‘”;
Set = Set;
}
//Where = Where + UpdateData.Rows[0][0].ToString() + “=” + UpdateData.Rows[0][1].ToString();
}
SQLUpdate = SQLUpdate + Set + Where;
return SQLUpdate;
}

/// <summary>
/// Untuk menghapus data
/// </summary>
/// <param name=”namaTable”></param>
/// <param name=”DeleteData”></param>
/// <returns></returns>
public string DELETESQLCommand(string tableName, DataTable DeleteData)
{
string SQLDelete = “DELETE FROM ” + tableName;
string Where = ” WHERE “;

DataTable myfirstData = this.GetTableDataType(tableName, DeleteData.Rows[0][0].ToString());
if (myfirstData.Rows[0][“CHARACTER_MAXIMUM_LENGTH”].ToString() == null)
Where = Where + DeleteData.Rows[0][0].ToString() + “=” + DeleteData.Rows[0][1].ToString();
else
Where = Where + DeleteData.Rows[0][0].ToString() + “='” + DeleteData.Rows[0][1].ToString() + “‘”;

SQLDelete = SQLDelete + Where;
return SQLDelete;
}

/// <summary>
/// Untuk merecord perubahan data yang dilakukan user
/// </summary>
/// <param name=”barislog”>Primary Key User Log</param>
/// <param name=”username”>Nama User</param>
/// <param name=”ModifiedDate”>Tanggal Perubahan</param>
/// <param name=”ModifiedText”>Syntax SQL yang dirubah user</param>
/// <returns></returns>
public DataTable User_Modify(string username, string ModifiedText)
{
string MySelect = this.CreateSQLCommand(“user_modify”);
DataTable TableModify = this.GetData(MySelect);
int barislog = TableModify.Rows.Count + 1;
ModifiedText = ‘”‘ + ModifiedText + ‘”‘;
string RecordTime = Convert.ToString(DateTime.Now.Year) + “-” + Convert.ToString(DateTime.Now.Month) + “-” + Convert.ToString(DateTime.Now.Day) + ” ” +
Convert.ToString(DateTime.Now.Hour) + “:” + Convert.ToString(DateTime.Now.Minute) + “:” + Convert.ToString(DateTime.Now.Second);
string SQLku = “INSERT INTO user_modify (user_modify_id, username, modify_date,modify_text) VALUES (” +
barislog + “,'” + username + “‘,'” + RecordTime + “‘,” + ModifiedText + “)”;
DataTable Hasil = this.GetData(SQLku);
return Hasil;
}

/// <summary>
/// Simpang data yang akan disimpan dan dieksekusi dengan syntax INSERT INTO dari DataGridView
/// </summary>
/// <param name=”namaField”></param>
/// <param name=”isiData”></param>
/// <returns></returns>
public DataTable SaveDataToInsert(DataGridView myDataGridView)
{
DataTable Hasil = new DataTable();
DataRow FieldRow = null;
Hasil.Columns.Add(“FieldName”, typeof(string));
Hasil.Columns.Add(“FieldData”, typeof(string));
for (int a = 0; a <= myDataGridView.Rows.Count-2 ; a++)
{
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[a];
FieldRow.BeginEdit();
FieldRow[“FieldName”] = myDataGridView.Rows[a].Cells[0].Value;
FieldRow[“FieldData”] = myDataGridView.Rows[a].Cells[1].Value;
FieldRow.EndEdit();
}
return Hasil;
}

/// <summary>
/// Simpang data yang akan disimpan dan dieksekusi dengan syntax INSERT INTO dari DataGridView
/// </summary>
/// <param name=”namaField”></param>
/// <param name=”isiData”></param>
/// <returns></returns>
public DataTable SaveDataPCM(DataGridView myDataGridView, int jumE1)
{
DataTable Hasil = new DataTable();
DataRow FieldRow = null;
Hasil.Columns.Add(“FieldName”, typeof(string));
Hasil.Columns.Add(“FieldData”, typeof(string));
for (int a = 0; a <= jumE1; a++)
{
FieldRow = Hasil.Rows.Add();
FieldRow = Hasil.Rows[a];
FieldRow.BeginEdit();
FieldRow[“FieldName”] = myDataGridView.Rows[a].Cells[0].Value;
FieldRow[“FieldData”] = myDataGridView.Rows[a].Cells[1].Value;
FieldRow.EndEdit();
}
return Hasil;
}

private string AddBackSlash(string data)
{
String hasil = string.Empty;
hasil.GetEnumerator();
for (int a = 0; a <= data.Length – 1; a++)
{
if (data[a] == ‘\\’)
{
//data[a] = ‘\\’;
//hasil[b + 1] = ‘\\’;
}

}
return hasil;
}

public string AddData(string tabel, ArrayList IsiData)
{
DataTable FieldName = this.GetListOfTableFields(tabel);
string fieldname = “”;
string fieldparam = “”;
string instxt = “”;
for (int a = 1; a <= FieldName.Rows.Count-2; a++)
{
fieldname = fieldname + FieldName.Rows[a][0].ToString();
fieldname = fieldname + ” , “;
fieldparam = fieldparam + IsiData[a].ToString();
fieldparam = fieldparam + ” , “;
}
fieldname = fieldname + FieldName.Rows[FieldName.Rows.Count-1][0].ToString(); ;
fieldparam = fieldparam + IsiData[FieldName.Rows.Count – 1].ToString(); ;
instxt = “INSERT INTO ” + tabel + “(” + fieldname + “) VALUES ( ” + fieldparam + “)”;
return instxt;
}

public void AddData(OdbcDataAdapter dataAdapter, OdbcConnection conn, string tabel, params string[] FieldName)
{

dataAdapter.InsertCommand = conn.CreateCommand();
dataAdapter.InsertCommand.CommandText = InsertText(tabel, FieldName);
AddParameter(dataAdapter.InsertCommand, FieldName);
}

public void EditData(OdbcDataAdapter dataAdapter, OdbcConnection conn, string tabel, string FieldOI, params string[] FieldName)
{
dataAdapter.UpdateCommand = conn.CreateCommand();
dataAdapter.UpdateCommand.CommandText = UpdateText(tabel, FieldName, FieldOI);
AddParameter(dataAdapter.UpdateCommand, FieldName);
}

public void DeleteData(OdbcDataAdapter dataAdapter, OdbcConnection conn, string tabel, string FieldOI, params string[] FieldName)
{
dataAdapter.DeleteCommand = conn.CreateCommand();
dataAdapter.DeleteCommand.CommandText = DeleteText(tabel, FieldOI);
AddParameter(dataAdapter.DeleteCommand, FieldName);
}

#endregion

#region Export to Excel
/// <summary>
/// Exports a passed datagridview to an Excel worksheet.
/// If captions is true, grid headers will appear in row 1.
/// Data will start in row 2.
/// </summary>
/// <param name=”datagridview”></param>
/// <param name=”captions”></param>
public void Export2Excel(DataGridView datagridview, bool captions)
{
object objApp_Late;
object objBook_Late;
object objBooks_Late;
object objSheets_Late;
object objSheet_Late;
object objRange_Late;
object[] Parameters;
string[] headers = new string[datagridview.ColumnCount];
string[] columns = new string[datagridview.ColumnCount];

int i = 0;
int c = 0;
for (c = 0; c <= datagridview.ColumnCount – 1; c++)
{
headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
i = c + 65;
columns[c] = Convert.ToString((char)i);
}

try
{
// Get the class type and instantiate Excel.
Type objClassType;
objClassType = Type.GetTypeFromProgID(“Excel.Application”);
objApp_Late = Activator.CreateInstance(objClassType);
//Get the workbooks collection.
objBooks_Late = objApp_Late.GetType().InvokeMember(“Workbooks”,
BindingFlags.GetProperty, null, objApp_Late, null);
//Add a new workbook.
objBook_Late = objBooks_Late.GetType().InvokeMember(“Add”,
BindingFlags.InvokeMethod, null, objBooks_Late, null);
//Get the worksheets collection.
objSheets_Late = objBook_Late.GetType().InvokeMember(“Worksheets”,
BindingFlags.GetProperty, null, objBook_Late, null);
//Get the first worksheet.
Parameters = new Object[1];
Parameters[0] = 1;
objSheet_Late = objSheets_Late.GetType().InvokeMember(“Item”,
BindingFlags.GetProperty, null, objSheets_Late, Parameters);

if (captions)
{
// Create the headers in the first row of the sheet
for (c = 0; c <= datagridview.ColumnCount – 1; c++)
{
//Get a range object that contains cell.
Parameters = new Object[2];
Parameters[0] = columns[c] + “1”;
Parameters[1] = Missing.Value;
objRange_Late = objSheet_Late.GetType().InvokeMember(“Range”,
BindingFlags.GetProperty, null, objSheet_Late, Parameters);
//Write Headers in cell.
Parameters = new Object[1];
Parameters[0] = headers[c];
objRange_Late.GetType().InvokeMember(“Value”, BindingFlags.SetProperty,
null, objRange_Late, Parameters);
}
}

// Now add the data from the grid to the sheet starting in row 2
for (i = 0; i < datagridview.RowCount – 1; i++)
{
for (c = 0; c <= datagridview.ColumnCount – 1; c++)
{
//Get a range object that contains cell.
Parameters = new Object[2];
Parameters[0] = columns[c] + Convert.ToString(i + 2);
Parameters[1] = Missing.Value;
objRange_Late = objSheet_Late.GetType().InvokeMember(“Range”,
BindingFlags.GetProperty, null, objSheet_Late, Parameters);
//Write Headers in cell.
Parameters = new Object[1];
Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
objRange_Late.GetType().InvokeMember(“Value”, BindingFlags.SetProperty,
null, objRange_Late, Parameters);
}
}

//Return control of Excel to the user.
Parameters = new Object[1];
Parameters[0] = true;
objApp_Late.GetType().InvokeMember(“Visible”, BindingFlags.SetProperty,
null, objApp_Late, Parameters);
objApp_Late.GetType().InvokeMember(“UserControl”, BindingFlags.SetProperty,
null, objApp_Late, Parameters);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = “Error: “;
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, ” Line: “);
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, “Error”);
}
}
#endregion
}
}


Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Kategori

%d blogger menyukai ini: