http://cafe.naver.com/ws29/446
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!--
add Key 설정
데이타베이스 연결 스트링
-->
*****
<appSettings>
<add key="BoardDSN" value="Network Library=DBMSSOCN;Data Source=localhost,1433;Initial Catalog=Testdb;user id=sa;Password=1234;" />
</appSettings>
*****
<system.web>
<!-- 동적 디버깅 컴파일
ASPX 디버깅을 사용하려면 compilation debug="true"로 설정합니다. 이렇게 하지 않고
해당 값을 false로 설정하면 이 응용 프로그램의 런타임 성능이 향상됩니다.
컴파일된 페이지에 디버깅 기호(.pdb 정보)를 삽입하려면 compilation debug="true"로 설정합니다.
이렇게 하면 파일 크기가 커져서 실행 속도가 느려지므로
디버깅하는 경우에만 이 값을 true로 설정하고
디버깅하는 경우 외에는 모두 false로 설정합니다. 자세한 내용은 ASP.NET 파일 디버깅에 관한
설명서를 참조하십시오.
-->
우선 web.config 화일 안에 **** 안에 구문을 입력한다.
그 다음 DB연결 Class를 추가 한후 아래에 소스를 복사한후 활용하면 됨...
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
namespace Common
{
/// <summary>
/// DB에 대한 요약 설명입니다.
/// </summary>
public class DB
{
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();
}
}
}