เขียน DAO ง่าย ๆสำหรับ .net + Mysql
by admin on Mar.02, 2009, under Uncategorized
ผมได้ลองเขียน class ตัวหนึ่งมาง่าย ๆ เพื่อ map กับ database ของ mysql พยายามทำให้เหมือนในลักษณะ ของ Active Record ที่มีใช้ใน ruby on rails แต่ต้องประกอบกับ concept dataset ที่ .net 2005 ยังยึดเป็นหลัก ลองมาดูว่าหน้าตาดีกว่า ว่าเป็นอย่างไร
ดูจากวิธีการใช้งานกันก่อน ในที่นี้จะต่อกับ table ที่ชื่อว่า product
Product product = new Product();
int product_id = 3;
//query
DataTable dtProductFound = product.findAll(String.Format("product_id = {0}", product_id));
//new
DataTable dtNew = product.New();
DataRow dr = dtNew.NewRow();
dr["product_name"] = "test";
dr["product_id"] = "3";
product.save(dtNew);
//edit
//DataTable dtProductFound = product.findAll(String.Format("product_id = {0}", product_id));
if (dtProductFound != null && dtProductFound.Rows.Count > 0)
{
dtProductFound.Rows[0]["product_name"] = "tester";
product.Save(dtProductFound);
}
//remove
DataTable dtProductFound = product.findAll(String.Format("product_id = {0}", product_id));
product.Del(dtProductFound);
มาดูต่อว่า class Product ที่ map กับ table นั้นหน้าตาเป็นยังไง
using System;
using System.Collections.Generic;
using System.Text;
namespace DataAccess
{
public class Product : ModelBase
{
public Product ()
{
this.tableName = "prd_product";
this.primaryKey = "id";
}
}
}
ตัว class Product จะถูกสืบทอด มาจาก class แม่ที่ชื่อว่า Model Base เพียงแต่ตัวที่สืบทอดมานั้นจะต้องทำการ ตั้งค่า primary key กับ ชื่อ table เท่านั้นเอง
สำหรับ ModelBase Class หน้าตาก็จะเป็นดังนี้
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections;
using Utility;
namespace DataAccess
{
public class ModelBase
{
protected String tableName;
private String strSqlCommand;
protected String primaryKey;
private MySqlConnection conn;
private MySqlCommand command;
private MySqlDataAdapter adapter;
private Hashtable hJoinTable;
public bool setJoinTable = false;
public ModelBase()
{
hJoinTable = new Hashtable();
if (!DBConfig.readConfigFile())
{
Log.writeLogError("Can not read config file(dbconfig.conf)");
}
}
//standard sql query
//-------------------------
public DataTable New()
{
strSqlCommand = "SELECT * FROM " + this.tableName +" WHERE false";
return executeQuery();
}
public bool Save(DataTable dtSave)
{
if (dtSave == null || dtSave.Rows.Count == 0)
{
return false;
}
foreach (DataRow dr in dtSave.Rows)
{
//update
if (dr.RowState == System.Data.DataRowState.Modified)
{
string updateValue = "";
string whereId = "`" + primaryKey + "` = '" + dr[primaryKey].ToString() + "'";
foreach (DataColumn dc in dtSave.Columns)
{
//update
bool correctColumnFlg = false;
foreach (string strColumnName in getColumnName())
{
if (dc.ColumnName == strColumnName)
{
correctColumnFlg = true;
break;
}
}
if (!correctColumnFlg)
{
continue;
}
if (dc.ColumnName == primaryKey)
{
continue;
}
string strUpdate;
Type columnType = dc.DataType;
if (columnType.FullName == "MySql.Data.Types.MySqlDateTime")
{
if (dr[dc.ColumnName] == DBNull.Value)
{
strUpdate = "null";
}
else
{
MySql.Data.Types.MySqlDateTime date;
date = (MySql.Data.Types.MySqlDateTime)dr[dc.ColumnName];
strUpdate = String.Format("{0}-{1}-{2} {3}:{4}:{5}", date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second);
}
}
else if (columnType.FullName == "System.UInt32" || columnType.FullName == "System.Decimal" || columnType.FullName == "System.Byte")
{
if (dr[dc.ColumnName].ToString().Trim() == "")
{
strUpdate = "0";
}
else
{
strUpdate = dr[dc.ColumnName].ToString();
}
}
else
{
strUpdate = dr[dc.ColumnName].ToString();
}
if (strUpdate.Equals("null"))
{
if (updateValue.Equals(""))
{
updateValue += "`" + dc.ColumnName + "` = " + strUpdate;
}
else
{
updateValue += ", `" + dc.ColumnName + "` = " + strUpdate;
}
}
else
{
if (updateValue.Equals(""))
{
updateValue += "`" + dc.ColumnName + "` = '" + strUpdate + "'";
}
else
{
updateValue += ", `" + dc.ColumnName + "` = '" + strUpdate + "'";
}
}
}
strSqlCommand = String.Format("UPDATE {0} SET {1} WHERE {2}", this.tableName, updateValue, whereId);
}
else if (dr.RowState == DataRowState.Added)
{
//insert
string column = "";
string value = "";
foreach (DataColumn dc in dtSave.Columns)
{
bool correctColumnFlg = false;
foreach (string strColumnName in getColumnName())
{
if (dc.ColumnName == strColumnName)
{
correctColumnFlg = true;
break;
}
}
if (!correctColumnFlg)
{
continue;
}
//column
if (column != "")
column += ", `" + dc.ColumnName + "`";
else
column += "`" + dc.ColumnName + "`";
//value
string valueInsert;
//check type
Type columnType = dc.DataType;
if (columnType.FullName == "MySql.Data.Types.MySqlDateTime")
{
MySql.Data.Types.MySqlDateTime date;
if (dr[dc.ColumnName] == null || dr[dc.ColumnName].ToString().Trim().Equals(""))
{
//date = UtilConvert.toMySqlDateTime(DateTime.Now);
valueInsert = "null";
}
else
{
date = (MySql.Data.Types.MySqlDateTime)dr[dc.ColumnName];
valueInsert = String.Format("{0}-{1}-{2} {3}:{4}:{5}", date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second);
}
}
else if (columnType.FullName == "System.UInt32" || columnType.FullName == "System.Decimal" || columnType.FullName == "System.Byte")
{
if (dr[dc.ColumnName].ToString().Trim() == "")
{
valueInsert = "0";
}
else
{
valueInsert = dr[dc.ColumnName].ToString();
}
}
else
{
valueInsert = dr[dc.ColumnName].ToString();
}
if (valueInsert.Equals("null"))
{
if (value != "")
{
value += ", " + valueInsert;
}
else
{
value += "" + valueInsert;
}
}
else
{
if (value != "")
{
value += ", '" + valueInsert + "'";
}
else
{
value += "'" + valueInsert + "'";
}
}
}
strSqlCommand = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", this.tableName, column, value);
}
//same
else
{
continue;
}
//save by sql command
if (!executeNonQuery())
return false;
}
return true;
}
//
public DataTable findAll()
{
string strJoin = "";
if (hJoinTable.Count != 0 && setJoinTable)
{
foreach (object key in hJoinTable.Keys)
{
strJoin += hJoinTable[key.ToString()].ToString()+" ";
}
}
strSqlCommand = "SELECT * FROM " + this.tableName+" "+strJoin;
return executeQuery();
}
public DataTable findAll(String condition)
{
string strJoin = "";
if (hJoinTable.Count != 0)
{
foreach (object key in hJoinTable.Keys)
{
strJoin += hJoinTable[key.ToString()].ToString() + " ";
}
}
strSqlCommand = "SELECT * FROM " + this.tableName+" " + strJoin+ " WHERE "+condition;
return executeQuery();
}
public DataTable findFirst()
{
strSqlCommand = "SELECT * FROM " + this.tableName +" LIMIT 0,1";
return executeQuery();
}
public DataTable findLast()
{
strSqlCommand = "SELECT * FROM " + this.tableName + " ORDER BY " + primaryKey + " DESC LIMIT 0,1";
return executeQuery();
}
public DataTable findBySql(String sqlQuery)
{
strSqlCommand = sqlQuery;
return executeQuery();
}
public bool Del(DataTable dataDel)
{
foreach (DataRow dr in dataDel.Rows)
{
//string strCondition = " `id` = '" + dr["id"] + "'";
string strCondition = " `"+primaryKey+"` = '" + dr[primaryKey] + "'";
strSqlCommand = String.Format("DELETE FROM {0} WHERE {1}", this.tableName, strCondition);
//save by sql command
if (!executeNonQuery())
return false;
}
return true;
}
//join table
//-------------------------
public void addJoinTable(String tableName, String primaryFieldJoin, String joinTableField)
{
string strJoin = String.Format("INNER JOIN {0} ON {1} = {2}", tableName, primaryFieldJoin, joinTableField);
hJoinTable.Add(tableName, strJoin);
}
public void clearJoinTable()
{
hJoinTable.Clear();
}
// process
//-------------------------
private DataTable executeQuery()
{
if(conn ==null)
conn = new MySqlConnection();
conn.ConnectionString =
"server=" + DBConfig.serverDB +
";user id=" + DBConfig.username +
";password="+DBConfig.password+
";database=" + DBConfig.dabaseName +
";Allow Zero Datetime=True;";
if (command == null)
command = new MySqlCommand();
command.Connection = conn;
try
{
conn.Open();
}
catch(Exception e)
{
//connect error
Log.writeLogError("|Connect Database| "+e.Message);
}
//Execute query
command.CommandText = strSqlCommand;
if (adapter == null)
{
adapter = new MySqlDataAdapter();
}
adapter.SelectCommand = command;
DataTable dtQuery = new DataTable(this.tableName);
try
{
adapter.Fill(dtQuery);
}
catch(Exception e)
{
Log.writeLogError("|Query|" + e.Message);
conn.Close();
return null;
}
conn.Close();
return dtQuery;
}
private bool executeNonQuery()
{
if (conn == null)
conn = new MySqlConnection();
conn.ConnectionString =
"server=" + DBConfig.serverDB +
";user id=" + DBConfig.username +
";password=" + DBConfig.password +
";database=" + DBConfig.dabaseName +
";Allow Zero Datetime=True;";
if (command == null)
command = new MySqlCommand();
command.Connection = conn;
try
{
conn.Open();
}
catch(Exception e)
{
Log.writeLogError("|Connect Database| " + e.Message);
}
command.CommandText = strSqlCommand;
try
{
if (command.ExecuteNonQuery() == 1)
{
conn.Close();
return true;
}
else
{
conn.Close();
return false;
}
}
catch(Exception e)
{
Log.writeLogError("|Non Query| " + e.Message);
conn.Close();
return false;
}
}
//utility
//-------------------------
public string[] getColumnName()
{
strSqlCommand = "show columns from " + this.tableName;
DataTable dtColumnNames = executeQuery();
string[] strColumnNames = new string[dtColumnNames.Rows.Count];
int i=0;
foreach (DataRow dr in dtColumnNames.Rows)
{
strColumnNames[i] = dr["Field"].ToString();
i++;
}
return strColumnNames;
}
public static bool checkConnection()
{
DBConfig.readConfigFile();
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString =
"server=" + DBConfig.serverDB +
";user id=" + DBConfig.username +
";password=" + DBConfig.password +
";database=" + DBConfig.dabaseName +
";Allow Zero Datetime=True;";
try
{
conn.Open();
conn.Close();
}
catch (Exception e)
{
Log.writeLogError("|Connect Database| " + e.Message);
return false;
}
return true;
}
}
}
Model base จะทำการ gen sql command มาให้นั้นเอง สุดท้ายมาดู config class
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
using Utility;
namespace DataAccess
{
public class DBConfig
{
public static string serverDB = "";
public static string dabaseName = "";
public static string username = "";
public static string password = "";
public static bool readConfigFile()
{
serverDB = "172.171.3.3";
dabaseName = "prd_maz";
username = "root";
password = "meroot";
return true;
}
}
}
พยายามทำให้ใกล้เคียง active record แต่ลองทำเล่น ๆ เบื้องต้นก็ ok ยังเหลือเรื่องที่ต้องทำเพิ่มเติมคือเรื่องการ join และ relation ถ้ามีเวลาจะมาทำเพิ่ม 
