Oleh: ekoyw | April 1, 2011

Bermain Dengan Struktur Tabel

Adakalanya atau bahkan sering sekali kita memerlukan berbagai informasi yang berhubungan dengan tabel database.  Misalnya kita ingin mengetahui tabel A ini berisi foreign key apa saja dan foreign key tersebut berasal dari tabel apa saja, atau yang paling simple tabel A ini terdiri dari field – field apa saja.

Berikut beberapa contoh procedure dalam C#.NET yang mungkin berguna.  Procedure ini telah digunakan menggunakan database MySQL. Untuk database yang lain perubahannya tidak terlalu banyak.

Untuk langkah awal, kita mendefinisikan connection string sebagai berikut :
/// <summary>
/// Create Connection String
/// </summary>
/// <returns></returns>

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

Isian databasename dan servername sesuai dengan database dan server yang akan kita access.  Setelah connection string siap maka bisa dilanjutkan untuk beberapa proses berikut :

1. Mendapatkan nama – nama field dari sebuah tabel
/// <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;
}

2. Mendapatkan foreign key berikut foreign key tersebut berasal dari tabel mana

/// <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;
}

3. Mendapatkan nilai sebenarnya dari seluruh Foreign Key di sebuah tabel. Misalnya di foreign key di tabel child hanya menunjukkan huruf LMG maka kita akan me-lookup nilai LMG di referenced table itu apa.

/// <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;
}

4. Mendapatkan tipe data dari sebuah field di tabel.

/// <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;
}

5. Mendapatkan nama field, termasuk foreign field dari sebuah tabel

/// <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;
}

Semoga bermanfaat ….


Responses

  1. Weee canggih rek, mantabsss

    • Memang e ngerti tur? hehehehe

  2. abot men, tambah mumet aku😦

  3. permisi numpang tanya :
    update INFORMATION_SCHEMA.COLUMNS set COLUMN_NAME=(‘export’) = ‘1’
    apa bisa isi field column name nya diupdate??
    klo gk bisa ada cara lain?? thx

    • Mau ganti nama kolom atau field? Kenapa ga langsung aja lewat DBMS, klik kanan rename…


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: