안녕하세요
C# 컨텐츠로 블로그를 운영중인 코딩 연습생입니다
이번 포스팅에서는 C#으로 MSSQL를 접속하고 쿼리문을 전송시켜 연동시키기까지 한번 해보도록 하겠습니다
일반적인 방법으로는
1) 접속정보 생성
2) SqlConnection 생성
3) SqlDataAdapter 생성
4) SqlCommand 실행
5) DataSet 실행
6) DataTable 데이터 받기
이런 순서로 직접 날코딩하여 사용을 했었습니다
그런데 프로젝트에서 DB 통신을 자주해야 하는경우 매우 귀찮아지게 됩니다
그래서 쉽게 MSSQL과 통신하고 내가 원하는 방식으로 FeedBack 받을수 있는 클래스 생성도 같이 설명해보
도록 하겠습니다
첫번째로 일반 방식입니다
string source = string.Empty;
string SYS_TIME = string.Empty;
//콜렉션 생성
DataRowCollection Rs2 = null;
//접속정보 생성
source = @"User Id=계정ID;Password=패스워드;Server=tjqjIP;Initial Catalog=DB명";
//컨넥션 생성
SqlConnection Con = new SqlConnection(source);
Con.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
string query = "쿼리";
adapter.SelectCommand = new SqlCommand(query, Con);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
Rs2 = table.Rows;
if (Rs2 != null && Rs2.Count > 0)
{
for (int i = 0; i < Rs2.Count; i++)
{
//쿼리에서 FeedBack 받은 값 사용
SYS_TIME = Rs2[0]["시간"].ToString();
}
}
Con.Close();
단순한 SELECT문 하나 사용하더라도 참 많은 구문을 작성해야 합니다
두번째 방법으로는 클래스로 DB 명령를 미리 생성해놓고 호출하여 사용하는 방식으로 구현해 볼께요
1) DB에서 사용될 명령를 미리 생성합니다
- Database.cs라는 구성 요소 클래스 파일 생성
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
namespace db_conn
{
public partial class Database : Component
{
public Database()
{
InitializeComponent();
}
public Database(IContainer container)
{
container.Add(this);
InitializeComponent();
}
private db_conn.BaseDB.DBTypeEnum m_DbType = BaseDB.DBTypeEnum.JDE;
public db_conn.BaseDB.DBTypeEnum DbType
{
get { return m_DbType; }
set { m_DbType = value; }
}
/// <summary>
/// SELECT처럼 결과가 있는 쿼리를 실행한다
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>데이터테이블</returns>
public DataTable ExcuteQuery(string query)
{
return ExcuteQuery(m_DbType.ToString(), query);
}
/// <summary>
/// SELECT처럼 결과가 있는 쿼리를 실행한다
/// </summary>
/// <param name="dbName">DB명</param>
/// <param name="query">쿼리</param>
/// <returns>데이터테이블</returns>
public DataTable ExcuteQuery(string dbName, string query)
{
db_conn.BaseDB db = BaseDB.GetDB(dbName.ToUpper());
return db.ExcuteQuery(query);
}
/// <summary>
/// insert, update처럼 조작하는 쿼리를 실행한다.
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>결과</returns>
public int ExcuteNonQuery(string query)
{
return ExcuteNonQuery(m_DbType.ToString(), query);
}
// <summary>
/// insert, update처럼 조작하는 쿼리를 실행한다.
/// </summary>
/// <param name="dbName">DB명</param>
/// <param name="query">쿼리</param>
/// <returns>결과</returns>
public int ExcuteNonQuery(string dbName, string query)
{
db_conn.BaseDB db = BaseDB.GetDB(dbName.ToUpper());
return db.ExcuteNonQuery(query);
}
/// <summary>
/// 대량의 데이터를 인서트한다.
/// </summary>
/// <param name="toTbName">인서트할 테이블</param>
/// <param name="dt">데이터테이블</param>
/// <returns>오류메세지</returns>
public string BulkInsert(string toTbName, System.Data.DataTable dt)
{
return BulkInsert(m_DbType.ToString(), toTbName, dt);
}
/// <summary>
/// 대량의 데이터를 인서트한다.
/// </summary>
/// <param name="dbName">DB명</param>
/// <param name="toTbName">인서트할 테이블</param>
/// <param name="dt">데이터테이블</param>
/// <returns>오류메세지</returns>
public string BulkInsert(string dbName, string toTbName, System.Data.DataTable dt)
{
db_conn.BaseDB db = BaseDB.GetDB(dbName.ToUpper());
return db.BulkInsert(toTbName, dt);
}
/// <summary>
/// 1:1매칭되는 테이블을 넘겨 Dictionary로 받는다.
/// </summary>
/// <param name="dbName">DB명</param>
/// <param name="query">쿼리(KEY_COL,VAL_COL이 반드시 있어야한다)</param>
/// <returns>결과</returns>
public Dictionary<string, string> GetDic1To1(string dbName, string query)
{
Dictionary<string, string> ret = new Dictionary<string, string>();
DataTable dt = ExcuteQuery(dbName, query);
string key = "";
string val = "";
for (int row = 0; row < dt.Rows.Count; row++)
{
key = dt.Rows[row]["KEY_COL"].ToString();
val = dt.Rows[row]["VAL_COL"].ToString();
if (!ret.ContainsKey(key))
{
ret.Add(key, val);
}
}
return ret;
}
/// <summary>
/// 테이블을 넘겨 Dictionary(row)
/// </summary>
/// <param name="dbName">DB명</param>
/// <param name="query">쿼리(KEY_COL은 반드시 있어야한다)</param>
/// <returns>결과</returns>
public Dictionary<string, DataRow> GetDicRow(string dbName, string query)
{
Dictionary<string, DataRow> ret = new Dictionary<string, DataRow>();
DataTable dt = ExcuteQuery(dbName, query);
string key = "";
for (int row = 0; row < dt.Rows.Count; row++)
{
key = dt.Rows[row]["KEY_COL"].ToString();
if (!ret.ContainsKey(key))
{
ret.Add(key, dt.Rows[row]);
}
}
return ret;
}
/// <summary>
/// 데이터테이블을 딕셔너리에 넣는다.
/// </summary>
/// <param name="keyCols">키값을 가지는 컬럼들(:로 연결한다)</param>
/// <param name="dt">넘길 테이블</param>
/// <returns>결과</returns>
public Dictionary<string, DataRow> GetDicRow(string keyCols, DataTable dt)
{
Dictionary<string, DataRow> ret = new Dictionary<string, DataRow>();
string key = "";
string[] spKeys = keyCols.Split(':');
for (int row = 0; row < dt.Rows.Count; row++)
{
key = "";
for (int i = 0; i < spKeys.Length; i++)
{
key += dt.Rows[row][spKeys[i]].ToString();
}
if (!string.IsNullOrEmpty(key) && !ret.ContainsKey(key))
{
ret.Add(key, dt.Rows[row]);
}
}
return ret;
}
/// <summary>
/// 데이터테이블을 딕셔너리에 넣는다.
/// </summary>
/// <param name="keyCols">키값을 가지는 컬럼들(:로 연결한다)</param>
/// <param name="sumCols">SUM할 컬럼들(:로 연결한다)</param>
/// <param name="dt">넘길 테이블</param>
/// <returns>결과</returns>
public Dictionary<string, DataRow> GetDicRow(string keyCols, string sumCols, DataTable dt)
{
Dictionary<string, DataRow> ret = new Dictionary<string, DataRow>();
string key = "";
string[] spKeys = keyCols.Split(':');
string[] spSums = sumCols.Split(':');
for (int row = 0; row < dt.Rows.Count; row++)
{
key = "";
for (int i = 0; i < spKeys.Length; i++)
{
key += dt.Rows[row][spKeys[i]].ToString();
}
if (!ret.ContainsKey(key))
{ //INSERT
ret.Add(key, dt.Rows[row]);
}
else
{ //UPDATE
for (int i = 0; i < spSums.Length; i++)
{
ret[key][spSums[i]] = Convert.ToDouble(ret[key][spSums[i]]) + Convert.ToDouble(dt.Rows[row][spSums[i]]);
}
}
}
return ret;
}
/// <summary>
/// 딕셔너리를 DataTable로 변환
/// </summary>
/// <param name="dic">딕셔너리</param>
/// <returns>테이블</returns>
public DataTable ConvertDicToDT(Dictionary<string, DataRow> dic)
{
DataTable dt = new DataTable();
int cnt = 0;
foreach (KeyValuePair<string, DataRow> pair in dic)
{
if (cnt == 0)
{
dt = pair.Value.Table.Clone();
}
DataRow dr = dt.NewRow();
for (int col = 0; col < pair.Value.ItemArray.Length; col++)
{
dr[col] = pair.Value[col];
}
dt.Rows.Add(dr);
cnt++;
}
return dt;
}
}
}
2) 접속 DB에 대한 속성 클래스 생성
- BaseDB 클래스 파일 생성
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using System.Data.Common;
namespace db_conn
{
public abstract class BaseDB
{
#region 열거형
/// <summary>
/// 매출타입(OES/OEM)
/// </summary>
public enum DBTypeEnum
{
TEST
}
#endregion
private DBTypeEnum m_DBType = DBTypeEnum.TEST;
public BaseDB(DBTypeEnum dbType)
{
m_DBType = dbType;
}
/// <summary>
/// 쿼리를 실행한다(결과 있을때)
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>실행결과</returns>
public abstract DataTable ExcuteQuery(string query);
/// <summary>
/// 쿼리를 실행한다(결과 없을때)
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>적용행수</returns>
public abstract int ExcuteNonQuery(string query);
/// <summary>
/// 대량의 데이터를 Insert한다
/// </summary>
/// <param name="toTbName">저장할테이블</param>
/// <param name="dt">데이터</param>
/// <returns>에러메세지</returns>
public abstract string BulkInsert(string toTbName, DataTable dt);
/// <summary>
/// DB연결자생성
/// </summary>
/// <returns>DB연결자</returns>
protected abstract DbConnection DBConnection();
/// <summary>
/// SQL파일을 실행한다.
/// </summary>
/// <param name="path">SQL파일</param>
/// <param name="args">인자(:)으로 구분</param>
/// <returns>결과</returns>
public DataTable ExcuteFileQuery(string path, string args)
{
string query = System.IO.File.ReadAllText(path);
query = query.Replace("\r\n", " "); //강제개행
query = query.Replace("\t", " "); //탭키
query = query.Replace(";", " "); //탭키
string[] spArgs = args.Split(':');
for (int i = 0; i < spArgs.Length; i++)
{
query = query.Replace("{" + i.ToString() + "}", spArgs[i]);
}
return this.ExcuteQuery(query);
}
/// <summary>
/// 줄리안데이트
/// </summary>
/// <param name="yyyymmdd">년월일</param>
/// <returns>줄리안</returns>
public int GetJulianDate(string yyyymmdd)
{
int yyyy = Convert.ToInt32(yyyymmdd.Substring(0, 4));
int mm = Convert.ToInt32(yyyymmdd.Substring(4, 2));
int dd = Convert.ToInt32(yyyymmdd.Substring(6, 2));
DateTime lDate = new DateTime(yyyy, mm, dd);
DateTime rDate = new DateTime(yyyy, 1, 1);
string date1;
string date2;
date1 = "1" + yyyymmdd.Substring(2, 2);
TimeSpan span = lDate.Subtract(rDate);
date2 = Convert.ToString(Convert.ToUInt32(span.Days) + 1).PadLeft(3, '0');
return Convert.ToInt32(date1 + date2);
}
public static BaseDB GetDB(string dbType)
{
BaseDB db = null;
switch (dbType)
{
case "TEST":
db = new TEST();
break;
}
return db;
}
}
}
3) DB타입에 따른 세부 명령어 지정
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace db_conn
{
public class MssqlDB:BaseDB
{
public MssqlDB(string ip, string uid, string pwd, string db)
: base(DBTypeEnum.MSSQL)
{
m_DB_IP = ip;
m_DB_ID = uid;
m_DB_PWD = pwd;
m_DB_Name = db;
}
#region 멤버필드
private string m_DB_IP = "";
private string m_DB_ID = "";
private string m_DB_PWD = "";
private string m_DB_Name = "";
#endregion
#region 상수
private const string CN_DB_TIME = "600"; //300초까지 타임아웃한다.
#endregion
#region 공개메소드
/// <summary>
/// 대량의 DataTable을 인서트한다
/// </summary>
/// <param name="toTbName">삽입할 테이블명</param>
/// <param name="dt">데이터테이블</param>
public override string BulkInsert(string toTbName, DataTable dt)
{
string ret = "";
try
{
SqlBulkCopy bulk = new SqlBulkCopy(DBConnection().ConnectionString);
bulk.DestinationTableName = toTbName;
bulk.WriteToServer(dt);
bulk.Close();
}
catch (Exception exLog)
{
ret = exLog.Message;
throw new Exception(exLog.Message);
}
return ret;
}
/// <summary>
/// 결과가 있는 쿼리를 실행
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>결과</returns>
public override DataTable ExcuteQuery(string query)
{
DataTable dt = new DataTable();
try
{
SqlDataAdapter adpt = new SqlDataAdapter(query, (SqlConnection)DBConnection());
adpt.SelectCommand.CommandTimeout = Convert.ToInt32(CN_DB_TIME);
adpt.Fill(dt);
}
catch (Exception eLog)
{
throw eLog;
}
return dt;
}
/// <summary>
/// 결과가 없는 쿼리를 실행
/// </summary>
/// <param name="query">쿼리</param>
/// <returns>영향받은 Row수</returns>
public override int ExcuteNonQuery(string query)
{
int ret = 0;
try
{
SqlConnection connect = (SqlConnection)DBConnection();
connect.Open();
SqlTransaction tran = connect.BeginTransaction();
try
{
SqlCommand com = new SqlCommand(query, connect, tran);
ret = com.ExecuteNonQuery();
tran.Commit();
connect.Close();
}
catch (Exception ineLog)
{
tran.Rollback();
throw ineLog;
}
}
catch (Exception eLog)
{
throw eLog;
}
return ret;
}
protected override DbConnection DBConnection()
{
string connectStr = "Server=" + m_DB_IP;
connectStr = connectStr + ";" + "User ID = " + m_DB_ID;
connectStr = connectStr + ";" + "Password = " + m_DB_PWD;
connectStr = connectStr + ";" + "Initial Catalog = " + m_DB_Name;
connectStr = connectStr + ";" + "Connection Timeout= " + CN_DB_TIME;
SqlConnection ret = new SqlConnection(connectStr);
return ret;
}
public int InsertUpdateImage(string _SQL, System.Drawing.Image _Image, string _ImageFieldName, System.Drawing.Imaging.ImageFormat _ImageFormat)
{
int _SqlRetVal = 0;
try
{
SqlConnection con = (SqlConnection)this.DBConnection();
con.Open();
System.Data.SqlClient.SqlCommand _SqlCommand
= new System.Data.SqlClient.SqlCommand(_SQL, con);
System.IO.MemoryStream _MemoryStream = new System.IO.MemoryStream();
_Image.Save(_MemoryStream, _ImageFormat);
System.Data.SqlClient.SqlParameter _SqlParameter
= new System.Data.SqlClient.SqlParameter("@" + _ImageFieldName, SqlDbType.Image);
_SqlParameter.Value = _MemoryStream.ToArray();
_SqlCommand.Parameters.Add(_SqlParameter);
_SqlRetVal = _SqlCommand.ExecuteNonQuery();
_SqlCommand.Dispose();
_SqlCommand = null;
}
catch (Exception eLog)
{
throw eLog;
}
return _SqlRetVal;
}
#endregion
}
}
이렇게 준비가 다 되시면 본문에서 DB 연결과 명령어 사용이 훨씬 간편해 집니다
[사용방법]
1. 사용하는 프로젝트에 빌드를 통해 생성된 구성요소클래스 파일을 참조에 넣어준다
2. 본문에서 db_conn을 사용하기 위한 선언문
3. 다음과 같은 형태로 DB 연동 사용
string SYS_TIME = string.Empty;
string Sql = " SELECT 시간";
Sql += " FROM A ";
TEST db = new TEST();
DataTable dt = db.ExcuteQuery(Sql);
if (dt.Rows.Count > 0)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
SYS_TIME = dt.Rows[i]["시간"].ToString());
}
}
이렇게 하면 첫번째 방식과 동일한 결과 값을 얻을수 있습니다
가장 큰 차이점은 첫번째 방식은 쿼리를 종류 SELECT, INSERT, UPDATE, DELETE의 구문에 따라 방식이 조금 바뀝니다
그렇기 때문에 사용할때마다 코딩이 구문에 맞게 맞춰야하지요
하지만 두번째 방식의 경우 db의 변환 타입만 맞춰 사용하면 구문에 따른 코딩이 단순화가 된다는 점이 있습니다
두개 모두 구현하셔서 테스트 해보시기 바랍니다
감사합니다~
