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
{
/// <p><br></p><summary>
/// Summary description for SqlDataOperator
/// </summary>
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);
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
