데이타베이스 작업을 할시에 필요한 라이브러리를 소개합니다.
select, update, delete, insert 모두에 사용되며, ad-hoc 쿼리나 스토어프로시저 모두에 사용 가능합니다.
사용하는 방법
아래 예제는 모두 Contacts class 가 있어서, UI 버튼 클릭시 Contacts 클래스의 property 에 개별적인 값을 대입한 후, 필요한 작업을 수행합니다.
Contacts.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using DataGridViewExample.lib;
namespace DataGridViewExample.data
{
public class Contacts
{
#region Properties
public int Idx { get; set; }
public string Name { get; set; }
public string BirthInfo { get; set; }
public string ZipCode { get; set; }
#endregion
#region Functions
///
// Add the contacts info
///
///
public bool AddContacts()
{
bool retval = false;
try
{
SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
string query = @"insert into [dbo].[tbl_contacts](Name, BirthInfo, ZipCode)
values(@Name, @BirthInfo, @ZipCode);";
sdo.ExecuteNoneQuery(query,
new SqlParameter[]
{
new SqlParameter("@Name", Name),
new SqlParameter("@BirthInfo", BirthInfo),
new SqlParameter("@ZipCode", ZipCode)
},
CommandType.Text);
sdo.Dispose();
retval = true;
}
catch (SqlException se)
{
Trace.WriteLine(se.Message);
retval = false;
}
catch(Exception ex)
{
Trace.WriteLine(ex.Message);
retval = false;
}
return retval;
}
///
/// Modify the contacts info
///
///
public bool ModifyContacts()
{
bool retval = false;
try
{
SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
string query = @"update [dbo].[tbl_contacts]
set Name = @Name, BirthInfo = @BirthInfo, ZipCode = @ZipCode
where Id = @Idx;";
sdo.ExecuteNoneQuery(query,
new SqlParameter[]
{
new SqlParameter("@Name", Name),
new SqlParameter("@BirthInfo", BirthInfo),
new SqlParameter("@ZipCode", ZipCode),
new SqlParameter("@Idx", Idx)
},
CommandType.Text);
sdo.Dispose();
retval = true;
}
catch (SqlException se)
{
Trace.WriteLine(se.Message);
retval = false;
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
retval = false;
}
return retval;
}
public bool RemoveContacts()
{
bool retval = false;
try
{
SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
string query = @"delete from [dbo].[tbl_contacts]
where Id = @Idx;";
sdo.ExecuteNoneQuery(query,
new SqlParameter[]
{
new SqlParameter("@Idx", Idx)
},
CommandType.Text);
sdo.Dispose();
retval = true;
}
catch (SqlException se)
{
Trace.WriteLine(se.Message);
retval = false;
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
retval = false;
}
return retval;
}
#endregion
}
}
1. select 시
DataTable set 을 가져와서 DataGridView 의 DataSource 에 바인드 합니다.
SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
DataTable dt = sdo.ExecuteQuery("select Id, Name, BirthInfo, ZipCode from [dbo].[tbl_contacts]");
dataGridView1.DataSource = dt;
dt.Dispose();
sdo.Dispose();
DataTable set 을 가져와서 List에 넣는 후 DataGridView 의 DataSource 에 바인드 합니다.
SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
DataTable dt = sdo.ExecuteQuery("select Id, Name, BirthInfo, ZipCode from [dbo].[tbl_contacts]");
List<Contacts> pContact = new List<Contacts>();
if(dt.Rows.Count > 0)
{
foreach(DataRow row in dt.Rows)
{
pContact.Add(new Contacts()
{
Idx = Convert.ToInt32(row["Id"]),
Name = row["Name"].ToString(),
BirthInfo = row["BirthInfo"].ToString(),
ZipCode = row["ZipCode"].ToString()
});
}
}
dataGridView1.DataSource = pContact;
dt.Dispose();
sdo.Dispose();
2. update 시
Contacts contact = new Contacts();
contact.Idx = Convert.ToInt32(lblIdx.Text);
contact.Name = txtName.Text.Trim();
contact.BirthInfo = txtBirthInfo.Text.Trim();
contact.ZipCode = txtZipCode.Text.Trim();
if (contact.ModifyContacts())
{
toolStripStatusLabel1.Text = Properties.Resources.AddConfirmMsg;
}
else
{
toolStripStatusLabel1.Text = Properties.Resources.AddErrorMsg;
}
3. delete 시
Contacts contact = new Contacts();
contact.Idx = Convert.ToInt32(lblIdx.Text);
string message = string.Format("[{0}] 정보를 삭제하시겠습니까", txtName.Text.Trim());
if (MessageBox.Show(message, "확인", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.OK)
{
if (contact.RemoveContacts())
{
toolStripStatusLabel1.Text = Properties.Resources.RemoveConfirmMsg;
}
else
{
toolStripStatusLabel1.Text = Properties.Resources.RemoveErrorMsg;
}
}
4. insert 시
Contacts contact = new Contacts();
contact.Name = txtName.Text.Trim();
contact.BirthInfo = txtBirthInfo.Text.Trim();
contact.ZipCode = txtZipCode.Text.Trim();
if (contact.AddContacts())
{
toolStripStatusLabel1.Text = Properties.Resources.AddConfirmMsg;
}
else
{
toolStripStatusLabel1.Text = Properties.Resources.AddErrorMsg;
}
데이타베이스 라이브러리는 아래와 같습니다.
SQLOperator.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataGridViewExample.lib
{
///
/// Summary description for SqlDataOperator
///
public class SQLOperator : IDisposable
{
private SqlConnection conn;
private SqlCommand comm;
private SqlDataAdapter adap;
private SqlTransaction tran;
public SqlConnection Connection
{
get
{
return conn;
}
set
{
conn = value;
}
}
public SqlCommand Command
{
get
{
return comm;
}
set
{
comm = value;
}
}
public SqlTransaction Transaction
{
get
{
return tran;
}
}
public SQLOperator()
: this("default")
{
}
public SQLOperator(string connectionStringName)
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
//conn = new SqlConnection(Properties.Settings.Default.SampleDBConnectionString);
comm = new SqlCommand();
comm.Connection = conn;
adap = new SqlDataAdapter();
}
public SqlTransaction BeginTransaction()
{
if (conn.State == ConnectionState.Closed)
conn.Open();
tran = conn.BeginTransaction();
return tran;
}
public void RollBackTran()
{
if (tran != null)
tran.Rollback();
}
public void CommitTran()
{
if (tran != null)
tran.Commit();
}
public SqlDataReader ExecuteReader(string query, SqlParameter[] parameters, CommandType commType)
{
comm.CommandType = commType;
comm.CommandText = query;
AddParameters(parameters);
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public SqlDataReader ExecuteReader(string spName, SqlParameter[] parameters)
{
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = spName;
AddParameters(parameters);
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public SqlDataReader ExecuteReader(string query)
{
comm.CommandType = CommandType.Text;
comm.CommandText = query;
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public object ExecuteScalar(string sql)
{
comm.CommandText = sql;
comm.CommandType = CommandType.Text;
if (conn.State == ConnectionState.Closed)
conn.Open();
object result = comm.ExecuteScalar();
comm.Parameters.Clear();
return result;
}
public object ExecuteScalar(string spName, SqlParameter[] parameters)
{
comm.CommandText = spName;
comm.CommandType = CommandType.StoredProcedure;
AddParameters(parameters);
if (conn.State == ConnectionState.Closed)
conn.Open();
object result = comm.ExecuteScalar();
comm.Parameters.Clear();
return result;
}
public object ExecuteScalar(string spName, SqlParameter[] parameters, CommandType commType)
{
comm.CommandText = spName;
comm.CommandType = commType;
AddParameters(parameters);
if (conn.State == ConnectionState.Closed)
conn.Open();
object result = comm.ExecuteScalar();
comm.Parameters.Clear();
return result;
}
public object ExecuteScalar(string spName, SqlParameter[] parameters, CommandType commType, SqlTransaction tran)
{
comm.Transaction = tran;
return ExecuteScalar(spName, parameters, commType);
}
public object ExecuteScalar(string spName, SqlParameter[] parameters, SqlTransaction tran)
{
comm.Transaction = tran;
return ExecuteScalar(spName, parameters);
}
public void ExecuteNoneQuery(string spName, SqlParameter[] parameters)
{
comm.CommandText = spName;
comm.CommandType = CommandType.StoredProcedure;
AddParameters(parameters);
SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
para.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(para);
if (conn.State == ConnectionState.Closed)
conn.Open();
comm.ExecuteNonQuery();
comm.Parameters.Clear();
}
public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, CommandType commType)
{
comm.CommandText = spName;
comm.CommandType = commType;
AddParameters(parameters);
SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
para.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(para);
if (conn.State == ConnectionState.Closed)
conn.Open();
comm.ExecuteNonQuery();
comm.Parameters.Clear();
}
public int ExecuteNoneQuery_Retrun_Param(string spName, SqlParameter[] parameters, CommandType commType)
{
comm.CommandText = spName;
comm.CommandType = commType;
AddParameters(parameters);
SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
para.Direction = ParameterDirection.ReturnValue;
SqlParameter return_param = comm.Parameters.Add(para);
if (conn.State == ConnectionState.Closed)
conn.Open();
comm.ExecuteNonQuery();
var return_param_1 = return_param.Value;
comm.Parameters.Clear();
return (int)return_param_1;
}
public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, CommandType commType, SqlTransaction tran)
{
comm.Transaction = tran;
ExecuteNoneQuery(spName, parameters, commType);
}
public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, SqlTransaction tran)
{
comm.Transaction = tran;
ExecuteNoneQuery(spName, parameters);
}
public void ExecuteNoneQuery(string sql, SqlTransaction tran)
{
comm.Transaction = tran;
ExecuteNoneQuery(sql);
}
public void ExecuteNoneQuery(string sql)
{
comm.CommandText = sql;
comm.CommandType = CommandType.Text;
if (conn.State == ConnectionState.Closed)
conn.Open();
comm.ExecuteNonQuery();
}
public DataTable ExecuteQuery(string tableName, string spName, SqlParameter[] parameters)
{
DataTable dtResult = new DataTable(tableName);
comm.CommandText = spName;
comm.CommandType = CommandType.StoredProcedure;
AddParameters(parameters);
adap.SelectCommand = comm;
if (conn.State == ConnectionState.Closed)
conn.Open();
adap.Fill(dtResult);
comm.Parameters.Clear();
return dtResult;
}
public DataTable ExecuteQuery(string tableName, string spName, SqlParameter[] parameters, CommandType commType)
{
DataTable dtResult = new DataTable(tableName);
comm.CommandText = spName;
comm.CommandType = commType;
AddParameters(parameters);
adap.SelectCommand = comm;
if (conn.State == ConnectionState.Closed)
conn.Open();
adap.Fill(dtResult);
comm.Parameters.Clear();
return dtResult;
}
public DataSet ExecuteQueryToDS(string spName, SqlParameter[] parameters)
{
DataSet dsResult = new DataSet();
comm.CommandText = spName;
comm.CommandType = CommandType.StoredProcedure;
AddParameters(parameters);
adap.SelectCommand = comm;
if (conn.State == ConnectionState.Closed)
conn.Open();
adap.Fill(dsResult);
comm.Parameters.Clear();
return dsResult;
}
public DataTable ExecuteQuery(string tableName, string sql)
{
DataTable dtResult = ExecuteQuery(sql);
dtResult.TableName = tableName;
return dtResult;
}
public DataTable ExecuteQuery(string sql)
{
DataTable dtResult = new DataTable();
comm.CommandText = sql;
comm.CommandType = CommandType.Text;
adap.SelectCommand = comm;
if (conn.State == ConnectionState.Closed)
conn.Open();
adap.Fill(dtResult);
return dtResult;
}
private void AddParameters(SqlParameter[] parameters)
{
comm.Parameters.Clear();
foreach (SqlParameter parameter in parameters)
{
comm.Parameters.Add(parameter);
}
}
public string AntiInjection(string val)
{
val = RemoveScriptTag(val).Replace("'", "''");
return val;
}
public static string RemoveScriptTag(string s)
{
System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex("(?<capture><[/]?script[a-zA-Z_0-9=\"\'\\s]*>)");
System.Text.RegularExpressions.MatchEvaluator evaluator = new System.Text.RegularExpressions.MatchEvaluator(MatchReplace);
return reg.Replace(s, evaluator);
}
public static string MatchReplace(System.Text.RegularExpressions.Match m)
{
string s = m.Groups["capture"].Value;
s = s.Replace("<", "<").Replace(">", ">");
return s;
}
#region IDisposable 멤버
public void Dispose()
{
comm.Parameters.Clear();
if (conn.State == ConnectionState.Open)
conn.Close();
}
#endregion
}
}
MyEventHandler2.zip
