using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Web;
using iPortalActive.DB;
using MySql.Data.MySqlClient;
/// <summary>
/// @author:cst@20140612 modified
/// </summary>
namespace CsUtility
{
public enum OperationType
{
Create,
Remove,
Update,
Delete
}
public class OperationFactory
{
public static AbstractDbFactory CreateFactory(OperationType optType)
{
switch (optType)
{
case OperationType.Create:
return new ExecuteFactory();
case OperationType.Remove:
return new QueryFactory();
case OperationType.Update:
return new UpdateFactory();
case OperationType.Delete:
return new DeleteFactory();
default: break;
}
}
}
public abstract class AbstractDbFactory
{
}
public static class MDbMapper<T> where T : class, new()
{
static MDbMapper()
{
}
public static bool Add(T model)
{
string sql = GenerateInsert(model);
MySqlParameter[] paras = GenerateMysqlParamArray(model);
int rowsAffected = DbHelperMySQL.ExecuteSql(sql, paras);
if (rowsAffected > 0)
{
return true;
}
return false;
}
public static bool Update(T model)
{
string sql = GenerateUpdate(model);
MySqlParameter[] paras = GenerateMysqlParamArray(model);
int rowsAffected = DbHelperMySQL.ExecuteSql(sql, paras);
if (rowsAffected > 0)
{
return true;
}
return false;
}
public static bool Delete(T model)
{
string sql = GenerateDelete(model);
MySqlParameter[] paras = GenerateMysqlParamArray(model);
int rowsAffected = DbHelperMySQL.ExecuteSql(sql, paras);
if (rowsAffected > 0)
{
return true;
}
return false;
}
public static IList<T> Query(T model)
{
string sql = GenerateSelect(model);
MySqlParameter[] paras = GenerateMysqlParamArray(model);
DataSet ds = DbHelperMySQL.Query(sql, paras);
return ReflactorHelper<T>.DsToIList(ds);
}
public static int GetCount(T model)
{
throw new NotImplementedException();
}
#region Update Field
private static string GenerateUpdate(T model)
{
if (typeof(T).GetProperty("Id").Equals(null) || typeof(T).GetProperty("id").Equals(null))
{
throw new ArgumentNullException("Id");
}
string sql = "Update " + typeof(T).Name.ToLower() + " set ";
sql += GetWhereString(model, true);
sql += " where id=?Id";
return sql;
}
#endregion
#region Insert Field
private static string GenerateInsert(T model)
{
string sql = "Insert into " + typeof(T).Name.ToLower();
string cols = GetColsString(model, null);
string vals = GetColsString(model, "?");
sql += cols;
sql += "values";
sql += vals;
return sql;
}
private static MySqlParameter[] GenerateMysqlParamArray(T model)
{
PropertyInfo[] ps = model.GetType().GetProperties();
IList<MySqlParameter> list = new List<MySqlParameter>();
for (int i = 0; i < ps.Length; i++)
{
if (ps[i].GetValue(model, null) == null) continue;
list.Add(new MySqlParameter("?" + ps[i].Name,
Convert.ChangeType(ps[i].GetValue(model, null), ps[i].GetValue(model, null).GetType())));
}
var paras = new MySqlParameter[list.Count];
for (int i = 0; i < paras.Length; i++)
{
paras[i] = list[i];
}
return paras;
}
private static string GetColsString(T model, string prefix)
{
PropertyInfo[] ps = typeof(T).GetProperties();
string cols = "(";
for (int i = 0; i < ps.Length; i++)
{
if (ps[i].GetValue(model, null) == null) continue;
cols += (prefix ?? "") + ps[i].Name + ",";
}
cols = cols.TrimEnd(',') + ")";
return cols;
}
#endregion
#region Delete Field
public static string GenerateDelete(T model)
{
string sql = "Delete from " + typeof(T).Name.ToLower() + " where ";
sql += GetWhereString(model, false);
return sql;
}
private static string GetWhereString(T model, bool isUpdate)
{
PropertyInfo[] ps = typeof(T).GetProperties();
string w = isUpdate ? " " : " 1=1 and ";
for (int i = 0; i < ps.Length; i++)
{
if ((isUpdate && ps[i].Name.ToLower().Equals("id")) || ps[i].GetValue(model, null) == null ||
(ps[i].Name.ToLower().Equals("id") && ps[i].GetValue(model, null).Equals(0))) continue;
w += ps[i].Name + "=?" + ps[i].Name + (isUpdate ? "," : " and ");
}
w = isUpdate ? w.TrimEnd(',') : w.Substring(0, w.Length - 4);
return w;
}
#endregion
#region Select Field
public static string GenerateSelect(T model)
{
string sql = "Select * from " + typeof(T).Name.ToLower() + " where ";
sql += GetWhereString(model, false);
return sql;
}
#endregion
#region SelectCount Field
#endregion
}
public static class MReqMapper
{
public static T GetMdl<T>() where T : class, new()
{
HttpRequest request = HttpContext.Current.Request;
var model = new T();
var keys = new string[request.Form.AllKeys.Length + request.QueryString.AllKeys.Length];
request.QueryString.AllKeys.CopyTo(keys, 0);
request.Form.AllKeys.CopyTo(keys, request.QueryString.AllKeys.Length);
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo p in properties)
{
try
{
string val = request.Form[p.Name] ?? request.QueryString[p.Name];
p.SetValue(model, Convert.ChangeType(val, p.PropertyType), null);
}
catch (Exception)
{
}
}
return model;
}
}
public class KeyAttribute : Attribute
{
}
public class Singleton<T> where T : new()
{
public static T Instance = new T();
}
public class ReflactorHelper<T> where T : new()
{
public static IList<T> DsToIList(DataSet ds, int tbIndex)
{
DataTable dt = ds.Tables[tbIndex];
IList<T> list = new List<T>();
DataRowCollection rows = dt.Rows;
PropertyInfo[] pis = typeof(T).GetProperties();
foreach (DataRow dr in rows)
{
T m = DrToModel(dr);
list.Add(m);
}
return list;
}
public static IList<T> DsToIList(DataSet ds)
{
return DsToIList(ds, 0);
}
public static T DrToModel(DataRow row)
{
var m = new T();
foreach (DataColumn col in row.Table.Columns)
{
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
try
{
if (row[pi.Name.ToLower()] != DBNull.Value)
{
pi.SetValue(m, Convert.ChangeType(row[pi.Name.ToLower()], pi.PropertyType), null);
}
}
catch (Exception)
{
}
}
}
return m;
}
}
public enum DbType
{
MsSql,
MySql,
Sqlite
}
public interface IDbHelper
{
int ExecuteSql(string sql, params DbParameter[] paras);
DataSet Query(string sql, params DbParameter[] paras);
object GetSingle(string sql, params DbParameter[] paras);
int GetMaxId(string sql, params DbParameter[] paras);
}
public class Connection
{
private readonly string _db;
public Connection(string db)
{
_db = ConfigurationManager.AppSettings[db];
}
public string Db
{
get { return _db; }
}
}
public class MySqlHelper : IDbHelper
{
private Connection _con;
private MySqlConnection _myCon;
public MySqlHelper(Connection con)
{
_con = con;
_myCon = new MySqlConnection(con.Db);
}
#region IDbHelper 成员
public int ExecuteSql(string sql, params DbParameter[] paras)
{
throw new NotImplementedException();
}
public DataSet Query(string sql, params DbParameter[] paras)
{
throw new NotImplementedException();
}
public object GetSingle(string sql, params DbParameter[] paras)
{
throw new NotImplementedException();
}
public int GetMaxId(string sql, params DbParameter[] paras)
{
throw new NotImplementedException();
}
#endregion
}
}