내가 쓰려고 만든 C# 윈폼 템플릿 (SELECT)

1. 데이터그리드뷰 SELECT ( DataSource Binding)

using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
            {
                DataSet ds = new DataSet();
                conn.Open();

                // 2개의 파라미터 정의 (항상 @로 시작)
                string sql = " SELECT COMP.COMP_NM[회사명], PLANT.PLANT_NM[공장명], BIZ.CUSTOMER_NAME[거래처명]" +
                             "  , MAT.PART_NO[품번] , MAT.PART_NM[품명], REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,MAT.PRICE),1), '.00', '')[가격], MAT.UNIT[단위], (CONVERT(VARCHAR(10), ORDER_DATE, 120))[발주일자]," +
                             "  MAT.SAFTSR[안전재고], BUY_QTY[구매수량], LTIME[리드타임], ORDER_QTY_MIN[최소발주수량]," +
                             "  USERR.USER_NM[구매담당자], USERR2.USER_NM[재고담당자], MAT.RMK[비고]" +
                             "   FROM CIS_MATERIAL AS MAT" +
                             "   LEFT OUTER JOIN CIS_COMP AS COMP" +
                             "   ON MAT.COMP_CD = COMP.COMP_CD" +
                             "   LEFT OUTER JOIN CIS_PLANT AS PLANT" +
                             "   ON MAT.PLANT_CD = PLANT.PLANT_CD" +
                             "   LEFT OUTER JOIN CIS_BIZ_COMP AS BIZ" +
                             "   ON MAT.CUSTOMER_CODE = BIZ.CUSTOMER_CODE" +
                             "   LEFT OUTER JOIN CIS_USER AS USERR" +
                             "   ON MAT.BUY_MANAGER = USERR.USER_ID" +
                             "   LEFT OUTER JOIN CIS_USER AS USERR2" +
                             "   ON MAT.STOCK_MANAGER = USERR2.USER_ID" +
                             "   WHERE MAT.COMP_CD LIKE @COMP_CD AND MAT.PART_NO LIKE @PART_NO AND MAT.PART_NM LIKE @PART_NM ";
                SqlCommand cmd = new SqlCommand(sql, conn);

                if(A_cboCOMP_NM.SelectedIndex==0)
                {
                    cmd.Parameters.AddWithValue("@COMP_CD", "%%");
                } //콤보박스 검색조건1
                else
                {
                    cmd.Parameters.AddWithValue("@COMP_CD", "%" + A_cboCOMP_NM.SelectedValue.ToString() + "%");
                } //
                
				//텍스트 검색조건2
                cmd.Parameters.AddWithValue("@PART_NO", "%" + A_txtPART_NO.Text + "%");
                //텍스트 검색조건3
                cmd.Parameters.AddWithValue("@PART_NM", "%" + A_txtPART_NM.Text + "%");

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "data");

                conn.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "data";

				//삭제체크박스 false로 초기화
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    dataGridView1.Rows[i].Cells[0].Value = true;
                    dataGridView1.Rows[i].Cells[0].Value = false;
                }
            }

2. 데이터그리드뷰 SELECT (한 줄씩 출력)

using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
{
    DataSet ds = new DataSet();
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = " SELECT BAD_TYPE[불량구분], BAD_CD[불량코드], BAD_NAME[불량항목], REMARK[비고], USE_FLAG [사용유무]  FROM CIS_BAD_MASTER  " +
    " WHERE BAD_NAME LIKE @BAD_NAME ORDER BY BAD_TYPE";

    cmd.Parameters.AddWithValue("@BAD_NAME", "%" + txt_Bad_Name.Text + "%");

    SqlDataReader reader = cmd.ExecuteReader();
    List<string> alist = new List<string>();

    int i = 0;
    while (reader.Read())
    {
      dataGridView1.Rows.Add();
      dataGridView1.Rows[i].Cells["Type"].Value = reader[0].ToString();
      dataGridView1.Rows[i].Cells["Code"].Value = reader[1].ToString();
      dataGridView1.Rows[i].Cells["Name"].Value = reader[2].ToString();
      dataGridView1.Rows[i].Cells["RMK"].Value = reader[3].ToString();
      dataGridView1.Rows[i].Cells["USE"].Value = reader[4].ToString();
      i++;
    }
    dataGridView1.Columns[2].Width = dataGridView1.Columns[2].GetPreferredWidth(DataGridViewAutoSizeColumnMode.AllCells, true);

    dataGridView1.Columns[3].Width = dataGridView1.Columns[3].GetPreferredWidth(DataGridViewAutoSizeColumnMode.AllCells, true);
    dataGridView1.Columns[4].Width = dataGridView1.Columns[4].Width = 300; ;

    dataGridView1.Columns[5].Width = dataGridView1.Columns[5].GetPreferredWidth(DataGridViewAutoSizeColumnMode.AllCells, true);

}

3. 데이터그리드뷰 일반적인 WHERE 파라미터 (DataSource Binding)

 using (SqlConnection conn = new SqlConnection(Commons.CONNSTRING))
                {
                    DataSet ds = new DataSet();
                    conn.Open();

                    // 2개의 파라미터 정의 (항상 @로 시작)
                    string sql = " SELECT C.COMP_NM AS [회사명] , P.PLANT_CD AS [공장코드], P.PLANT_NM AS [공장명], P.RMK AS [비고] , P.USE_YN AS [사용유무] " +
                                      " FROM CIS_COMP AS C " +
                                      " INNER JOIN CIS_PLANT AS P " +
                                      " ON C.COMP_CD = P.COMP_CD " +
                                      " WHERE P.COMP_CD =  @COMP_CD  AND C.USE_YN = 'Y' ";
                    SqlCommand cmd = new SqlCommand(sql, conn);

                    SqlParameter paramCity = new SqlParameter("@COMP_CD", SqlDbType.VarChar, 30);
                    paramCity.Value = cbo_Company_Name.SelectedValue.ToString();
                    cmd.Parameters.Add(paramCity);

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds, "data");

                    conn.Close();
                    dataGridView1.DataSource = ds;
                    dataGridView1.DataMember = "data";

                }