.Net Project & 재태크 재무 관리

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 FrmSqlParamter : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DisplayData();

    }

    private void DisplayData()
    {
        SqlConnection con = new SqlConnection
               (ConfigurationManager.ConnectionStrings
                 ["ConnectionString"].ConnectionString);
        con.Open();

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

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "Categories");

        this.ctlCategoryList.DataSource = ds.Tables[0];
        this.ctlCategoryList.DataBind();

        con.Close();
       
    }
    //추가
    protected void btnAddConnection_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings
                     ["ConnectionString"].ConnectionString;

        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = 
     "Insert Into Categories(CategoryName) Values(@CategoryName)";
        cmd.CommandType = CommandType.Text;
             

        //[1] 파라미터 추가하는 첫번째 방법
        //cmd.Parameters.AddWithValue
               ("@CategoryName", txtCategoryName.Text);

        //[2] 파라미터 추가하는 두번째 방법
        cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 50);
        cmd.Parameters["@CategoryName"].Value = txtCategoryName.Text;

        //[3] SqlParamter 클래스 사용 : 
        //Sp의 Output 매개변수 처리시 무조건 사용 하여야 한다.
        //SqlParameter ParamterCategoryName =
        //new SqlParameter("@CategoryName", SqlDbType.VarChar, 50);

        //ParamterCategoryName.Direction = ParameterDirection.Input; 
        //ParamterCategoryName.Value = txtCategoryName.Text;
        //cmd.Parameters.Add(ParamterCategoryName);
       
        cmd.ExecuteNonQuery(); //실행
       
        con.Close();
        DisplayData();
    }
}

namespace 주소록
{
    public partial class MainForm : Form
    {
    
        private String connectinonString; //커넥션 스트링 저장용 필드 선언

        public MainForm()
        {
            InitializeComponent();         

            //데이터 베이스 연결문자열 지정
            connectinonString = String.Format (
                "server=.;database={0};uid={0};pwd=1234;", "AddressBook");
        }
        private void MainForm_Load(object sender, EventArgs e)
        {
           DisplayData();
        }
        private void LoadData()
        {             
            DisplayData();
        }

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

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "AddressBook");

            this.dgvAddress.DataSource = ds.Tables[0].DefaultView;
           
            con.Close(); //닫기

            if (ds.Tables[0].Rows.Count > 0)
            {
                ShowRecord(0); //첫번째 자료로 초기화
                currentIndex = 0;
            }
            sslCount.Text = ds.Tables[0].Rows.Count.ToString();
        }

            SqlConnection con = new SqlConnection(connectinonString);
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert Into AddressBook Values
                                         (@Name, @Mobile, @Email)";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text);

            cmd.ExecuteNonQuery() ; //저장
            con.Close();
            ClearTextBox();
            MessageBox.Show("저장되었습니다.");
            DisplayData(); //호출 하여 재 바인딩
        }

                MessageBox.Show("백업 완료");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
----------------------------------------------------------------------

       // 종료  
       private void miExit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private int currentIndex = -1;

        //셀 클릭하였을 경우 선택한값 텍스트 박스에 표시
        private void dgvAddress_CellClick(object sender,
            DataGridViewCellEventArgs e)
        {
            currentIndex = e.RowIndex; // 현재 인덱스를 필드에 보관

            if (e.RowIndex < ds.Tables[0].Rows.Count)
            {
                ShowRecord(e.RowIndex);
            }
        }

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update AddressBook
                      Set Name = @Name, Mobile = @Mobile, Email = @Email
                                        Where Num = @Num";

            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
            cmd.Parameters.AddWithValue("@Num", txtNum.Text);

            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("수정되었습니다");
            ClearTextBox();
            DisplayData();

            ShowRecord(currentIndex);
        }

        //삭제
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (txtNum.Text != "" && currentIndex != -1)
            {
                DialogResult dr =
                    MessageBox.Show("정말로 삭제하시겠습니까?",
                           "삭제확인" , MessageBoxButtons.YesNo, 
                                          MessageBoxIcon.Question);
                if (dr != DialogResult.No)
                {
                    // 실제 DB에서 삭제
                    DeleteData(); //호출
                    DisplayData();
                }
            }
        }

        private void DeleteData()
        {
            SqlConnection con = new SqlConnection(connectinonString);
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText =
                   "Delete AddressBook Where Num = @Num";
            cmd.CommandType = CommandType.Text;
           
            //파라미터 추가
            cmd.Parameters.AddWithValue("@Num", txtNum.Text);
           
            cmd.ExecuteNonQuery(); //삭제 실행
            con.Close();
            MessageBox.Show("삭제되었습니다");
            ClearTextBox();               
        }

        //이동
        private void btnGo_Click(object sender, EventArgs e)
        {
            if (txtGo.Text != "" && Convert.ToInt32(txtGo.Text) > 0) 
            {
                int go = Convert.ToInt32(txtGo.Text);

                DataRow dr;
                bool flag = false ;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                 {
                    dr= ds.Tables[0].Rows[i];
   
                    if (go == Convert.ToInt32(dr["Num"]))
                    {
                        txtNum.Text = dr["Num"].ToString();
                        txtName.Text = dr["Name"].ToString();
                        txtMobile.Text = dr["Mobile"].ToString();
                        txtEmail.Text = dr["Email"].ToString();
                     
                        flag = true;
                     }   
              }
                if(flag == false)
                {
                    MessageBox.Show("찾지못했습니다");
                }
                txtGo.Select();
            }
        }
        // 검색
        private void btnSearch_Click(object sender, EventArgs e)
        {
            DataView ddv = ds.Tables[0].DefaultView;
            ddv.RowFilter =
                    "Name Like '%" + txtSearch.Text + "%' Or "
                    + "Mobile Like '%" + txtSearch.Text + "%' Or "
                    + "Email Like '%" + txtSearch.Text + "%'";

            this.dgvAddress.DataSource = ddv;          
        }

        //텍스트박스 클리어
        private void button1_Click(object sender, EventArgs e)
        {
            ClearTextBox(); //텍스트박스 클리어
        }
    }

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class Category_FrmRowfilter : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        //커넥션
        SqlConnection con = new SqlConnection
                          (ConfigurationManager.ConnectionStrings
                           ["ConnectionString"].ConnectionString);
        con.Open();

        //커멘드
        SqlCommand cmd = new SqlCommand
                    ("Select *From Categories", con);
       
        cmd.CommandType = CommandType.Text;

        //어댑터
        SqlDataAdapter da = new SqlDataAdapter(cmd); //초기화
        DataSet ds = new DataSet();
        da.Fill(ds, "Categories");

        //전체출력
        this.ctlSearchList.DataSource = ds;
        this.ctlSearchList.DataBind();
        
        con.Close();
      
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {

        SqlConnection con = new SqlConnection
                     (ConfigurationManager.ConnectionStrings
                      ["ConnectionString"].ConnectionString);
        con.Open();

        SqlCommand cmd = new SqlCommand
                   ("Select *From Categories", con);
        cmd.CommandType = CommandType.Text;

        SqlDataAdapter da = new SqlDataAdapter(cmd); //초기화
        DataSet ds = new DataSet();
        da.Fill(ds, "Categories");

        //전체출력 
        this.ctlSearchList.DataSource = ds;
        this.ctlSearchList.DataBind();
       
        //[1]DataTable변환
        DataTable dt = ds.Tables[0];

        //[2]DataView 변환
        DataView dv = dt.DefaultView;

        //[3]DataView.RowFilter변경
        dv.RowFilter = "CategoryName Like '%" + txtSerchquery.Text + "%'";
        dv.Sort = "CategoryName Asc"; //정렬

        this.ctlSearchList.DataSource = dv;
        this.ctlSearchList.DataBind();
       
        con.Close();
   }
}

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

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

    private void Display()
    {
        //커넥션
        SqlConnection con = new SqlConnection
                 (ConfigurationManager.ConnectionStrings
                  ["ConnectionString"].ConnectionString);

        con.Open();
      
        //커맨드
        SqlCommand cmd = new SqlCommand
                  ("Select *From Categories",con);
        cmd.CommandType = CommandType.Text;

       //어댑터 
        SqlDataAdapter da = new SqlDataAdapter(cmd); //초기화

        DataSet ds = new DataSet();

        da.Fill(ds, "Categories");

        this.lstCategoryList.DataSource = ds;
        this.lstCategoryList.DataTextField = "CategoryName";//보이는
        this.lstCategoryList.DataValueField = "CategoryID"; //안의 값
        this.lstCategoryList.DataBind();

        con.Close();
    }
}