DB 바인딩 클래스 - 펌 - 미테스트본.
Asp.NET 2009. 11. 10. 17:47 |[web.config 쪽에 해당 내용 추가]
<appSettings>
<add key="DSN" value="Data Source=dev.iamgsi.com,1433;Initial Catalog=testdb;user id=test;Password=test;"/>
</appSettings>
[DB 처리 클래스]
using System;
using System.Collections.Generic;
using System.Web;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication1
{
public class GDBCon
{
private int mCount = 0;
private SqlConnection mDbConn = null;
private SqlCommand mCmd = null;
private SqlDataReader mReader = null;
StringBuilder sb = new StringBuilder();
/* DataBase Connection Open */
public void DbConn()
{
try
{
mDbConn = new SqlConnection(GetDSN);
mDbConn.Open();
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "DataBase Open 실패");
}
}
// 연결문자열을 위한 Property
protected string GetDSN
{
get
{
// 밑에 구문이 web.config 에 있는 AppSettings
return System.Configuration.ConfigurationSettings.AppSettings["BoardDSN"];
}
}
/* DataBase Connection Close */
public void DbClose()
{
if (mDbConn == null)
{
return;
}
try
{
if (mDbConn.State.ToString() == "Open")
{
mDbConn.Close();
}
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "DataBase Close 실패");
}
}
/* DataBase Transaction Init */
public void InitTransaction(string TransName)
{
try
{
mCmd = new SqlCommand();
mCmd.Connection = mDbConn;
mCmd.Transaction = mDbConn.BeginTransaction(IsolationLevel.ReadCommitted, TransName);
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Trancsaction Open 실패");
}
}
/* Transaction Execute Query */
public void ExecuteTransaction(string[] QueryArr)
{
try
{
foreach (string Query in QueryArr)
{
mCmd.CommandText = Query;
mCmd.ExecuteNonQuery();
}
mCmd.Transaction.Commit();
}
catch (Exception e)
{
mCmd.Transaction.Rollback();
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Trancsaction Commit 실패");
}
}
/* Query Execute */
public void ExecuteQuery(string Query)
{
try
{
mCmd = new SqlCommand(Query, mDbConn);
mCmd.ExecuteNonQuery();
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, Query);
}
}
/* SQL DataReader Fatech Query */
public SqlDataReader FatechQuery(string Query)
{
try
{
mCmd = new SqlCommand(Query, mDbConn);
mReader = mCmd.ExecuteReader();
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, Query);
}
return mReader;
}
/* SQL DataReader Close */
public void ReaderClose()
{
try
{
if (!mReader.IsClosed)
{
mReader.Close();
}
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "SQLReader Close 실패");
}
}
/* Procedure Execute */
public int ExecuteProc(string ProcName, IDataParameter[] parameters)
{
int Result = 0;
try
{
SqlCommand Cmd = BuildIntCommand(ProcName, parameters);
Cmd.ExecuteNonQuery();
Result = (int)Cmd.Parameters["ReturnValue"].Value;
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Procedure ExecuteProc Error");
}
return Result;
}
/* SQL DataReader Fatech Procedure */
public SqlDataReader FatechProc(string ProcName, IDataParameter[] parameters)
{
SqlCommand Cmd = BuildProcCommand(ProcName, parameters);
try
{
Cmd.CommandType = CommandType.StoredProcedure;
mReader = Cmd.ExecuteReader();
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Procedure FatechProc Error");
}
return mReader;
}
/* Execute Query DateSet */
public DataSet ExecuteDataSet(string Query, string TableName, int StartRecord, int PageSize)
{
DataSet mDataSet = new DataSet();
try
{
SqlDataAdapter mDataAdapter = new SqlDataAdapter(Query, mDbConn);
mDataAdapter.Fill(mDataSet, StartRecord, PageSize, TableName);
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, Query);
}
return mDataSet;
}
/* Execute Procedure DateSet */
public DataSet ExecuteProcDataSet(string ProcName, IDataParameter[] parameters, string TableName, int StartRecord, int PageSize)
{
DataSet mDataSet = new DataSet();
SqlDataAdapter mDataAdapter = new SqlDataAdapter();
mDataAdapter.SelectCommand = BuildProcCommand(ProcName, parameters);
try
{
mDataAdapter.Fill(mDataSet, StartRecord, PageSize, TableName);
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Procedure ExecuteProcDataSet Error");
}
return mDataSet;
}
/* Total Count Function */
public int TotalQuery(string Query)
{
try
{
mCmd = new SqlCommand(Query, mDbConn);
mCount = (int)mCmd.ExecuteScalar();
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, Query);
}
return mCount;
}
/* Procedure BuildIntCommand */
protected SqlCommand BuildIntCommand(string ProcName, IDataParameter[] parameters)
{
SqlCommand Cmd = BuildProcCommand(ProcName, parameters);
try
{
Cmd.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null));
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Procedure BuildIntCommand Error");
}
return Cmd;
}
/* Procedure Parameter Build */
protected SqlCommand BuildProcCommand(string ProcName, IDataParameter[] parameters)
{
try
{
mCmd = new SqlCommand(ProcName, mDbConn);
mCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
mCmd.Parameters.Add(parameter);
}
}
catch (Exception e)
{
DbErrorMsg(e.Source, e.Message, e.StackTrace, "Procedure BuildProcCommand Error");
}
return mCmd;
}
/* Error Message Print */
public void DbErrorMsg(string ErrSource, string ErrMsg, string stack, string Query)
{
DbClose();
string ErrorMsg = "Error Souce =" + ErrSource + "<br>"
+ "Error Message = <font color='red'><b>" + ErrMsg + "</b></font><br>"
+ "Stack = " + stack + "<br><br>"
+ "Query = <font color='blue'><b>" + Query + "</b></font>";
System.Web.HttpContext.Current.Response.Write(ErrorMsg);
System.Web.HttpContext.Current.Response.End();
}
}
}