.Net Project & 재태크 재무 관리

 //[2] 커넥션 : SqlConnection, OleDbConnection 등을 DbConnection과 통일
        DbConnection con = factor.CreateConnection();
        con.ConnectionString =
            ConfigurationManager.ConnectionStrings
            ["ConnectionString"].ConnectionString;

        con.Open();

//[3]커맨드 : sqlcommand, orcalcommand 등을 dbcommand로 공통 처리
        DbCommand cmd = factor.CreateCommand();
        cmd.Connection = con;
        cmd.CommandText = "Select *From Categories";
        cmd.CommandType = System.Data.CommandType.Text;

//[4] 데이터 리더 : sqlDataReader, oledbDataReader = > DbDataReader
        DbDataReader dr = cmd.ExecuteReader();

//[5]바인딩
        this.ctlCategoryList.DataSource = dr;
        this.ctlCategoryList.DataBind();

//[6]마무리
        con.Close();

        DbParameter second = cmd.CreateParameter();
        second.ParameterName = "@Second";
        second.DbType = System.Data.DbType.Int32;
        second.Value = 5;

        //선언부
        cmd.Parameters.Add(first);
        cmd.Parameters.Add(second);

 

                                         ↕

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class FrmConfigurationManager : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //Web.Config 파일에 있는 <connectionStrings> 섹션에 있는 값 가져오기
        string str = ConfigurationManager.ConnectionStrings
                        ["ConnectionString"].ConnectionString;

        Response.Write(str + "<br />");
    }
}

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class FrmExecuteScalar : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DisplayCategoryCount();
        DisplayProductCount();
    }

    //등록된 상품 캣수
    private void DisplayProductCount()
    {
        SqlConnection con = new SqlConnection
                         (ConfigurationManager.ConnectionStrings
                           ["ConnectionString"].ConnectionString);
        con.Open();

        SqlCommand cmd = new SqlCommand
                                  ("Select Count(*) From Products", con);

        //스칼라값 실행
        this.lblProductCount.Text = cmd.ExecuteScalar().ToString(); 
        con.Close();        
    }

    //카테고리 상품 갯수
    private void DisplayCategoryCount()
    {
        //DisplayProductCount();
        SqlConnection conn = new SqlConnection
                                        (ConfigurationManager.ConnectionStrings
                                        ["ConnectionString"].ConnectionString);
        conn.Open();

        SqlCommand cmdd = new SqlCommand
                                    ("Select Count(*) From Categories", conn);

        //스칼라값 실행
        this.lblCategoryCount.Text = cmdd.ExecuteScalar().ToString(); 
        conn.Close();       
    }
--다른 방식----------------------------------------------------------
  using (SqlConnection con = new SqlConnection
            (ConfigurationManager.ConnectionStrings
             ["ConnectionString"].ConnectionString))
        {

            con.Open();
            SqlCommand cmd = new SqlCommand(
                "Select Count(*) As Cnt From Categories",con);

            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                lblCategoryCount.Text = dr["Cnt"].ToString(); //카운터 출력
            }
        }

                                                ↕

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class FrmParameters : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnSelect_Click(object sender, EventArgs e)
    {
        //[1]변수 선언부
        string first = txtFirst.Text;
        string second = txtSecond.Text;
        //[2]커넥션
        using (SqlConnection con = new SqlConnection(
            ConfigurationManager.ConnectionStrings
            ["ConnectionString"].ConnectionString))
        {
            con.Open();

            //[3] 커맨드
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText =
      //1. "Select *From Categories Where CategoryID Between 1 And 20";

     //웹 텍스트박스에 15;Drop Table Message;-- 입력하면 테이블 삭제(위험)
     //이와 같은 상황 보완하기 위해 3번 사용 
    
     //2. String.Format(@" Select *From Categories Where CategoryID 
     // Between {0} And {1}", first, second);
    
     // 2번 같은 문을 사용하면, 웹사이트 상에서 어느 누구나 테이블 삭제 가능
     // 그러므로 아래 3번과 같은 방식을 사용하여, 삭제 할 수 없도록 작성

            //3. 마지막 제일 좋은 방식
            "Select *From Categories Where CategoryID BetWeen
                                @First And @Second";
            //파라미터 추가
            cmd.Parameters.AddWithValue("@First", first); //Net 2.0
            cmd.Parameters.AddWithValue("@Second", second);

            //--------------------------------------------//
            //cmd.Parameters.Add("@First",
                      System.Data.SqlDbType.Int); //Net 1.X]
            //cmd.Parameters["@First"].Value = second;
            //--------------------------------------------//

            // 생략 가능 cmd.CommandType = CommandType.Text;

            //[4] 데이터 리더
            SqlDataReader dr = cmd.ExecuteReader();
           
            //[5] 바인딩
            ctlCategoryList.DataSource = dr;
            ctlCategoryList.DataBind();
         
            //[6] 마무리
            dr.Close();
        }
    }
}

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class FrmADONET : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DisplayDatees();
        }
    }

    //데이터 불러온다
   private void DisplayDatees()
    {
        SqlConnection conn = new SqlConnection("Data Source = .;Initial 
                              Catalog=Market;User ID=Market;Password=1234;");
        conn.Open();

        SqlCommand cmdd = new SqlCommand
                                     ("Select *From Categories", conn);
        cmdd.CommandType = CommandType.Text;

        //ExecuteReader()메서드의 결과값을 DataReader개체에 담기
        SqlDataReader dr = cmdd.ExecuteReader();

        //GridView바인딩
        this.ctrlGridView.DataSource = dr; //datareader, dataSet, List<T>
        this.ctrlGridView.DataBind(); //그렇지 않으면 알아서 모양 만들어 출력

        dr.Close(); //데이터리더도 반드시 Close()해야 함...

        conn.Close();
    }

    protected void btnCatory_Click(object sender, EventArgs e)
    {
        //[0]변수선언부
        string strCategoryName = txtInput.Text.Trim();
        //[1]커넥션
        SqlConnection con = new SqlConnection();
        con.ConnectionString = 
       "server=.;database=Market;uid=Market;pwd=1234;";
        //lblDisplay.Text = "연결되었습니다";
      
        con.Open();

        //[2]커맨드
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = String.Format
               ("Insert Categories(CategoryName)
                 Values('{0}')", strCategoryName);
        cmd.CommandType = CommandType.Text;

        //[3]실행 
        // 모든 명령어 실행 <-> Selecteh 가능 -> Reader사용 
        int rows = cmd.ExecuteNonQuery();    
        this.lblDisplay.Text = rows.ToString() + "개 입력되었습니다";

        con.Close();

    }

---

    //새로고침
    protected void BtnRefresh_Click(object sender, EventArgs e)
    {
        DisplayDatees();
        //ctrlGridView.DataBind();
    }

}

        //Connection
        SqlConnection con = new SqlConnection();
        con.ConnectionString =
                "server=.;database=Market;uid=Market;pwd=1234;";
             
        //Command
        SqlCommand cmd = new SqlCommand
                                    ("Select *From categories", con);
        cmd.CommandType = CommandType.Text;

        //Data Reader
        try
        {
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            this.lblError.Text = "연결완료";
           
            con.Close();
        }
        catch (SqlException se)
        {

            SqlErrorCollection sec = se.Errors;

            //에러를 묶어서 출력 (예외 처리 의해 에러 발생)
            string msg = "";
            foreach (var item in sec) //[3] 에러 하나를 담는 클래스
            {
                msg += item.ToString() + "<br />"; //.Message : 에러 메시지
            }
            this.lblError.Text = msg;
        }
    }