Skip to content

Instantly share code, notes, and snippets.

@puryfury

puryfury/DBConn.cs

Created May 17, 2013 11:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save puryfury/5598618 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save puryfury/5598618 to your computer and use it in GitHub Desktop.
Download ZIP
Nancy Firebird Embedded DB Access Base Example
Raw
DBConn.cs
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.IO;
using System.Reflection;
using System.Web;
using FirebirdSql.Data.FirebirdClient;
namespace CustomerWanso.Defined
{
/// <summary>
/// DB 커넥션 (Firebird Embedded 2.5 DB 사용)
/// </summary>
public class DBConn : IDisposable
{
public const string CSEDB = "CSEDB";
/// <summary>
/// 연결 문자열 불러오기
/// </summary>
private static readonly ConnectionStringSettings connstr = ConfigurationManager.ConnectionStrings[CSEDB];
/// <summary>
/// DB 연결 정의
/// </summary>
private FbConnection conn;
/// <summary>
/// DB 관리 개체 초기화
/// </summary>
public DBConn()
{
if (connstr != null)
{
conn = new FbConnection(connstr.ConnectionString);
}
else
{
//ServerType=0;User=SYSDBA;Password=masterkey;Dialect=3;Database=c:\data\mydb.fdb
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ServerType = FbServerType.Embedded;
csb.Database = @"|DataDirectory|\" + (ConfigurationManager.AppSettings["csedb:dbfile"] ?? "CSWANSO.FDB");
csb.Charset = "UTF8";
csb.Dialect = 3;
csb.ClientLibrary = Path.Combine(new Uri(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase)).LocalPath, "fbembed.dll");
csb.UserID = ConfigurationManager.AppSettings["csedb:username"] ?? "wanso";
csb.Password = ConfigurationManager.AppSettings["csedb:password"] ?? "C$w4ns0";
csb.MinPoolSize = 1;
csb.MaxPoolSize = 30;
csb.ConnectionLifeTime = 60;
conn = new FbConnection(csb.ToString());
}
conn.Open();
}
/// <summary>
/// DB 관리 개체 닫기
/// </summary>
public void Close()
{
try {
conn.Close();
}
catch { }
}
/// <summary>
/// DB 관리 개체 닫기
/// </summary>
public void Dispose()
{
Close();
}
/// <summary>
/// SELECT 쿼리를 통한 여러 개의 행 받아오기
/// </summary>
/// <param name="sql">SELECT 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public IList<FluidRow> GetList(string sql, params object[] param)
{
List<FluidRow> list = new List<FluidRow>();
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
using (FbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
Dictionary<ColunmFluid, dynamic> result = new Dictionary<ColunmFluid, dynamic>();
for(int i=0,len=reader.FieldCount;i<len;i++)
result.Add(new ColunmFluid(reader.GetName(i),i),reader.GetValue(i));
list.Add(new FluidRow(result));
}
}
}
return list;
}
/// <summary>
/// SELECT 쿼리를 통한 여러 개의 행을 DTO로 캐스팅하여 받아오기
/// </summary>
/// <typeparam name="T">DTO 캐스팅</typeparam>
/// <param name="sql">SELECT 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public IList<T> GetList<T>(string sql, params object[] param) where T : class, new()
{
List<T> list = new List<T>();
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
using (FbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
list.Add(AOPExtends.CastReader<T>(reader));
}
}
}
return list;
}
/// <summary>
/// INSERT,UPDATE,DELETE 등의 영향성 쿼리 실행
/// </summary>
/// <param name="sql">INSERT,UPDATE,DELETE 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public int Execute(string sql, params object[] param)
{
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
return comm.ExecuteNonQuery();
}
}
/// <summary>
/// INSERT,UPDATE,DELETE 등의 영향성 쿼리를 DTO 객체를 파라미터로 정의하여 실행
/// </summary>
/// <typeparam name="T">DTO 타입</typeparam>
/// <param name="sql">INSERT,UPDATE,DELETE 쿼리</param>
/// <param name="po">DTO 객체</param>
/// <param name="useattr">RequestParamAttribute 사용 여부</param>
/// <returns></returns>
public int Execute<T>(string sql, object po, bool useattr) where T : class,new()
{
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
Type t = typeof(T);
if (po != null)
{
foreach (PropertyInfo prop in t.GetProperties())
{
var attrs = prop.GetCustomAttributes(typeof(RequestParamAttribute), false);
RequestParamAttribute attr = attrs.Cast<RequestParamAttribute>().SingleOrDefault(a => a.Type == RequestParamType.All || a.Type == RequestParamType.Record);
comm.Parameters.AddWithValue(attr != null && useattr ? attr.Name : prop.Name, prop.GetValue(po, null) ?? DBNull.Value);
}
}
return comm.ExecuteNonQuery();
}
}
/// <summary>
/// INSERT,UPDATE,DELETE 등의 영향성 쿼리를 DTO 객체를 파라미터로 정의하여 실행
/// </summary>
/// <typeparam name="T">DTO 타입</typeparam>
/// <param name="sql">INSERT,UPDATE,DELETE 쿼리</param>
/// <param name="po">DTO 객체</param>
/// <returns></returns>
public int Execute<T>(string sql, object po) where T : class,new()
{
return Execute<T>(sql, po, true);
}
/// <summary>
/// SELECT 쿼리를 통한 상위 행의 첫번째 열 값 받아오기
/// </summary>
/// <param name="sql">SELECT 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public dynamic GetScalar(string sql, params object[] param)
{
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
return comm.ExecuteScalar();
}
}
/// <summary>
/// SELECT 쿼리를 통한 여러 개의 행의 첫번째 열 값 받아오기
/// </summary>
/// <param name="sql">SELECT 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public IList<dynamic> GetScalars(string sql, params object[] param)
{
List<dynamic> list = new List<dynamic>();
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
using (FbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
list.Add(reader.GetValue(0));
}
}
}
return list;
}
/// <summary>
/// SELECT 쿼리를 통한 상위 한 개의 행 받아오기
/// </summary>
/// <param name="sql">SELECT 쿼리</param>
/// <param name="param">입력 파라미터 (0부터 순차적 숫자)</param>
/// <returns></returns>
public FluidRow GetRow(string sql, params object[] param)
{
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
using (FbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
Dictionary<ColunmFluid, dynamic> result = new Dictionary<ColunmFluid, dynamic>();
for (int i = 0, len = reader.FieldCount; i < len; i++)
result.Add(new ColunmFluid(reader.GetName(i), i), reader.GetValue(i));
return new FluidRow(result);
}
}
}
return null;
}
/// <summary>
/// SELECT 쿼리를 통한 상위 한 개의 행을 DTO로 캐스팅하여 받아오기
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T GetRow<T>(string sql, params object[] param) where T : class, new()
{
using (FbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
for (int i = 0, len = param.Length; i < len; i++)
comm.Parameters.AddWithValue(i.ToString(), param[i] ?? DBNull.Value);
using (FbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
return AOPExtends.CastReader<T>(reader);
}
}
}
return null;
}
/// <summary>
/// 트랜잭션을 실시하여 트랜잭션 개체를 받음
/// </summary>
/// <returns></returns>
public DbTransaction BeginTransection()
{
return conn.BeginTransaction();
}
}
/// <summary>
/// 열 순서번호 또는 칼럼 정의명을 수용하게 해주는 클래스
/// </summary>
public struct ColunmFluid
{
/// <summary>
/// 열 정의명
/// </summary>
private string name;
/// <summary>
/// 열 순서번호 (0부터 시작)
/// </summary>
private int idx;
/// <summary>
/// 열 순서번호와 열 이름을 담아 초기화
/// </summary>
/// <param name="name"></param>
/// <param name="idx"></param>
public ColunmFluid(string name, int idx)
{
this.name = name;
this.idx = idx;
}
/// <summary>
/// 열 이름
/// </summary>
public string Name { get { return name; } }
/// <summary>
/// 순서번호
/// </summary>
public int Index { get { return idx; } }
/// <summary>
/// 칼럼 개체와 임의의 개체 비교
/// </summary>
/// <param name="cf"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static bool Equals(ColunmFluid cf, object obj)
{
if (obj == null) return Object.Equals(cf, obj);
else if (obj is int)//대상이 숫자면 숫자만 비교
return cf.Index == (int)obj;
else if (obj is string)//대상이 문자열이면 대소문자 무시 열 이름 비교
return string.Equals(cf.Name, (string)obj, StringComparison.OrdinalIgnoreCase);
else return Object.Equals(cf, obj);//그 외는 기본 비교
}
/// <summary>
/// ColunmFluid 와 열 번호 또는 열 명 비교
/// </summary>
/// <param name="obj">비교할 개체</param>
/// <returns></returns>
public override bool Equals(object obj)
{
return obj is ColunmFluid ? base.Equals(obj) : Equals(this, obj);
}
/// <summary>
/// 열 명과 열 이름으로 해시코드 생성
/// </summary>
/// <returns></returns>
public override int GetHashCode()
{
if (name == null) return DBNull.Value.GetHashCode() ^ idx.GetHashCode();
else return name.GetHashCode() ^ idx.GetHashCode();
}
/// <summary>
/// 열 명을 불러오기
/// </summary>
/// <returns></returns>
public override string ToString()
{
return name;
}
}
/// <summary>
/// 숫자나 이름으로 접근할 수 있는 열과 값 쌍으로 이루어진 읽기 전용 컬렉션
/// </summary>
public class FluidRow
{
private IDictionary<ColunmFluid, dynamic> dic;
public FluidRow()
{
dic = new Dictionary<ColunmFluid, dynamic>(0);
}
public FluidRow(IDictionary<ColunmFluid, dynamic> dic)
{
this.dic = dic;
}
public dynamic this[string key]
{
get
{
foreach (ColunmFluid col in dic.Keys)
if (ColunmFluid.Equals(col, key)) return dic[col];
return null;
}
}
public dynamic this[int key]
{
get
{
foreach (ColunmFluid col in dic.Keys)
if (ColunmFluid.Equals(col, key)) return dic[col];
return null;
}
}
public int ColumnCount { get { return dic.Count; } }
public IEnumerable<string> ColumnNames
{
get
{
return from col in dic.Keys select col.Name;
}
}
public bool ContainsName(string key)
{
foreach (ColunmFluid col in dic.Keys)
if (ColunmFluid.Equals(col, key)) return true;
return false;
}
public int ColumnIndexOf(string key)
{
int result = -1;
foreach (ColunmFluid col in dic.Keys)
if (ColunmFluid.Equals(col, key)) return result++;
else result++;
return result;
}
public IDictionary<ColunmFluid, dynamic> ToDictionary()
{
return new Dictionary<ColunmFluid, dynamic>(dic);
}
}
}
Raw
UserDao.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CustomerWanso.Defined;
using CustomerWanso.DataModel;
using CustomerWanso.Secutiry;
namespace CustomerWanso.DataAccess
{
public interface IUserDao
{
int UpdateLogin(string userid, string password);
IList<UserDto> SelectUsers(int rows, int page, UserDto user);
int GetUserCount(UserDto user);
UserDto ViewUser(string userid);
int InsertUser(UserDto user);
int UpdateUser(UserDto user);
int DeleteUser(string userid);
}
public class UserDao : DBConn, IUserDao
{
public int UpdateLogin(string userid, string password)
{
string sql = @"
UPDATE CS_USERS SET LOGDATE = CURRENT_TIMESTAMP
WHERE USERID = @0 AND USERPW = @1
";
return Execute(sql, userid, password);
}
public IList<UserDto> SelectUsers(int rows, int page, UserDto user)
{
int begin = rows * (page - 1);
string sql = @"
SELECT FIRST @0 SKIP @1 * FROM CS_USERS
WHERE USERNM LIKE '%' || @2 || '%'
";
return GetList<UserDto>(sql, rows, begin, user.UserName);
}
public int GetUserCount(UserDto user)
{
string sql = @"
SELECT COUNT(*) FROM CS_USERS
WHERE USERNM LIKE '%' || @0 || '%'
";
return GetScalar(sql, user.UserName);
}
public UserDto ViewUser(string userid)
{
string sql = @"
SELECT * FROM CS_USERS WHERE USERID=@0
";
return GetRow<UserDto>(sql, userid);
}
public int InsertUser(UserDto user)
{
string sql = @"
INSERT INTO CS_USERS (USERID,USERPW,USERNM,USERTEL,USERMAIL,USERURL,STATUS,WRITER)
VALUES (@userid,@userpw,@usernm,@usertel,@usermail,@userurl,@status,'TEST')
";
user.UserPassword = CryptHelper.SHA512String(user.UserPassword);
return Execute<UserDto>(sql, user);
}
public int UpdateUser(UserDto user)
{
string sql = @"
UPDATE CS_USERS SET
USERNM=@usernm
"+(!string.IsNullOrEmpty(user.UserPassword)?",USERPW=@userpw":"")+@"
,USERTEL=@usertel
,USERMAIL=@usermail
,USERURL=@userurl
,STATUS=@status
WHERE USERID=@userid
";
user.UserPassword = CryptHelper.SHA512String(user.UserPassword);
return Execute<UserDto>(sql, user);
}
public int DeleteUser(string userid)
{
string sql = @"
DELETE FROM CS_USERS WHERE USERID=@0
";
return Execute(sql, userid);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment