[펌] webservice

 

CommandHelper.cs

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
//using System.EnterpriseServices;
using System.Configuration;

namespace WebService1
{
 //commandText유형을 정의
 enum CmdType
 {
  SqlQuery,
  StoredProc
 };

 // [ClassInterface(ClassInterfaceType.AutoDual)]//기존 COM객체 지원
 // [ComVisible(true)]

 //[JustInTimeActivation(true)]
 //개체를 비활성인 컨텍스트전용객체로 만듬 이는 객체가 사용되고나서 메모리에서없어져도
 // 해당 컨텍스트는 메모리에 그 개체를 남겨두어 다음 호출시 빠르게 반응하게함
 //[ObjectPooling(MinPoolSize=1,MaxPoolSize=5)]//오브젝트풀링
 //[Transaction(TransactionOption.Required)]
 //트랜잭션 의 옵션들 상세내용은 127번 게시물 참조
 //IDisposable인터페이스는 관리되지않는 리소스(dbconnect등)를 메모리해제키위해 사용
 // Dispose메서드구현으로 적용함
 class CommandHelper //: System.EnterpriseServices.ServicedComponent,IDisposable
 {
  protected SqlConnection conn;
  private string conStr;

  public CommandHelper()
  {
   //conStr=System.Configuration.ConfigurationSettings.AppSettings["constr"];
   conStr="server=dotnet11;uid=sa;pwd=;database=test";
   conn=new SqlConnection(conStr);
  }

  //public csfirstCOM(string conStr)
  //{
  // this.conStr=conStr;
  // conn=new SqlConnection(conStr);
  //}

 

  //sqltext가 스토어드 프로시져일때 update,delete,insert에 사용할 함수
  //commandType(sql,proc)
  private SqlCommand BuildIntCommand(string procName,IDataParameter[] parameters,CmdType commandType)
  {
   SqlCommand command=null;
   if(parameters==null)
   {
    command=BuildQueryCommand(procName,null,commandType);
    return command;
   }
   command=BuildQueryCommand(procName,parameters,commandType);
   command.Parameters.Add(new SqlParameter("@RtnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
   return command;
  }

  //sqltext가 스토어드 프로시져일때select문에 사용할 함수
  private SqlCommand BuildQueryCommand(string procName,IDataParameter[] parameters,CmdType commandType)
  {
   SqlCommand command=new SqlCommand(procName,conn);

   if(commandType==CmdType.StoredProc)
    command.CommandType=CommandType.StoredProcedure;
   else
    command.CommandType=CommandType.Text;

   if(parameters==null)
    return command;

   foreach(SqlParameter param in parameters)
   {
    command.Parameters.Add(param);
   }

   return command;
  }

  //1.update,delete,insert를 실행하는 함수
 // [AutoComplete] //트랜잭션적용의 경우
  public int RunProc(string procName,IDataParameter[] parameters,out int rowsAffected)
  {
   int result;
   conn.Open();
   SqlCommand command=BuildIntCommand(procName,parameters,CmdType.StoredProc);
   rowsAffected=command.ExecuteNonQuery();
   result=(int)command.Parameters["@RtnValue"].Value;
   conn.Close();
   return result;
  }

  //2.select를 실행하고 SqlDataReader를 리턴하는 함수
  public SqlDataReader RunProc(string procName,IDataParameter[] parameters)
  {
   SqlDataReader returnReader;
   conn.Open();
   SqlCommand command=BuildQueryCommand(procName,parameters,CmdType.StoredProc);
   //command.CommandType=CommandType.StoredProcedure;
   returnReader=command.ExecuteReader(CommandBehavior.CloseConnection);
   return returnReader;
  }

  //3.where절없는 select실행 SqlDataReader리턴
  public SqlDataReader RunProc(string procName)
  {
   SqlDataReader returnReader;
   conn.Open();
   SqlCommand command=BuildQueryCommand(procName,null,CmdType.StoredProc);
   //command.CommandType=CommandType.StoredProcedure;
   returnReader=command.ExecuteReader(CommandBehavior.CloseConnection);
   return returnReader;
  }

  //4.select를 실행하고 DataSet을 리턴하는 함수
  public DataSet RunProc(string procName,IDataParameter[] parameters,string tblName)
  {
   DataSet dataset=new DataSet();
   
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildQueryCommand(procName,parameters,CmdType.StoredProc);
   conn.Open();
   sqlDA.Fill(dataset,tblName);
   conn.Close();
   return dataset;
  }

  //5.조건없는 select실행후 DataSet리턴
  public DataSet RunProc(string procName,string tblName)
  {
   DataSet dataset=new DataSet();
   conn.Open();
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildQueryCommand(procName,null,CmdType.StoredProc);
   sqlDA.Fill(dataset,tblName);
   conn.Close();
   return dataset;
  }

  //6.기존 DataSet의 Table에 결과셋을 추가
  //Relation설정,새로운 Table 추가등에 사용하는 함수
  public void RunProc(string procName,IDataParameter[] parameters,DataSet dataSet,string tblName)
  {
   conn.Open();
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildIntCommand(procName,parameters,CmdType.StoredProc);
   sqlDA.Fill(dataSet,tblName);
   conn.Close();
  }

  //7.조건없는 Relation설정,새로운 Table 추가등에 사용하는 함수
  public void RunProc(string procName,DataSet dataSet,string tblName)
  {
   conn.Open();
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildIntCommand(procName,null,CmdType.StoredProc);
   sqlDA.Fill(dataSet,tblName);
   conn.Close();
  }

  //8.sql문일때 update,insert,delete
  public int RunIntCmd(
   string cmdText,
   IDataParameter[] parameters
   )
  {
   int result;
   conn.Open();
   SqlCommand command=BuildIntCommand(cmdText,parameters,CmdType.SqlQuery);
   result=command.ExecuteNonQuery();
   conn.Close();
   return result;
  }

  //9.sql문일때 select ->sqldatareader select * from authors where au_id=@au_id and au-fname=@au_fname
  public SqlDataReader RunCmd(
   string cmdText,
   IDataParameter[] parameters
   )
  {
   SqlDataReader reader;
   SqlCommand command=this.BuildQueryCommand(cmdText,parameters,CmdType.SqlQuery);
   conn.Open();
   reader=command.ExecuteReader(CommandBehavior.CloseConnection);
   return reader;

  }

  //10.where절없는 select->sqldatareader
  public SqlDataReader RunCmd(
   string cmdText
   )
  {
   SqlDataReader reader;
   SqlCommand command=this.BuildQueryCommand(cmdText,null,CmdType.SqlQuery);
   conn.Open();
   reader=command.ExecuteReader(CommandBehavior.CloseConnection);
   return reader;
  }

  //11.sql문일때 select->DataSet
  public DataSet RunCmd(
   string cmdText,
   IDataParameter[] parameters,
   string tblName
   )
  {
   DataSet dataset=new DataSet();
   
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildQueryCommand(cmdText,parameters,CmdType.SqlQuery);
   conn.Open();
   sqlDA.Fill(dataset,tblName);
   conn.Close();
   return dataset;
  }

  //12.where절없는 select ->DataSet
  public DataSet RunCmd(
   string cmdText,
   string tblName
   )
  {
   DataSet dataset=new DataSet();
   
   SqlDataAdapter sqlDA=new SqlDataAdapter();
   sqlDA.SelectCommand=BuildQueryCommand(cmdText,null,CmdType.SqlQuery);
   
   conn.Open();
   sqlDA.Fill(dataset,tblName);
   conn.Close();
   return dataset;
  }

  //13.sql문 select->sqlxmlreader  for xml auto,Elements
  public XmlReader RunXmlCmd(
   string cmdForxml,
   IDataParameter[] parameters
   )
  {
   XmlReader reader;
   SqlCommand selCommand=this.BuildQueryCommand(cmdForxml,parameters,CmdType.SqlQuery);
   conn.Open();
   reader=selCommand.ExecuteXmlReader();
   return reader;
  }
  //14.where절 없는 sql문 select->sqlxmlreader
  public XmlReader RunXmlCmd(
   string cmdForxml
   )
  {
   XmlReader reader;
   SqlCommand selCommand=this.BuildQueryCommand(cmdForxml,null,CmdType.SqlQuery);
   conn.Open();
   reader=selCommand.ExecuteXmlReader();
   return reader;
  }

 }

}