ㄷ

쿼리를 수행하기 위해 SqlCommand를 선언해 사용해줍니다!


ExecuteQuery는 select 쿼리일때 많이 쓰이고

ExecuteNonQuery는 insert, update, delete 쿼리때 많이 쓰입니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//SqlCommand를 사용하기 위해서 적어주자
using System.Data.SqlClient;
 
 
//방법 1
string sql ="select * from table";
SqlConnection conn = new SqlConnection(conString);
 
//선언
SqlCommand cmd = new SqlCommand(sql, conn);
 
//수행
cmd.ExecuteQuery();
cmd.ExecuteNonQuery();
 
 
//방법2
SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from table";
            cmd.Connection = conn;
 
//수행
cmd.ExecuteQuery();
cmd.ExecuteNonQuery();
 
cs




ㅇㅇㅇㅇㅇㅇㅇ




'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15
winform 엑셀 export  (0) 2019.01.15

SqlConnection은 winform에서 DB와 연결하기 위한 클래스입니다


3가지 사용법을 알아보겠습니다


세가지 모두 알고보면 같은 내용인데


유지보수를 위해서 2번째나 3번째 방법을 저는 선호합니다



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
// SqlConnection을 쓰기 위해선 반드시 적어주도록 합니다
using System.Data.SqlClient;
 
 
//방법1
//선언
SqlConnection conn = new SqlConnection(@"Server=localhost; database=test; uid=sa; pwd=123");
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
 
 
 
//방법2
//선언
SqlConnection conn = new SqlConnection();
string connString = @"Server=localhost; database=test; uid=sa; pwd=123";
conn.ConnectionString = ConnString;
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
 
 
//방법3
//선언
string connString = @"Server=localhost; database=test; uid=sa; pwd=123";
SqlConnection conn = new SqlConnection(connString);
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
cs
ㄷ


'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform 엑셀 export  (0) 2019.01.15


winform 제작을 하다보면 데이터그리드뷰의 내용을 엑셀로 변환할 필요가 있습니다.


아래의 소스는 엑셀변환함수를 만들고 그것을 버튼에서 호출하는 식으로 쓰였습니다.


한번만 훑어보시고 잘 쓰셨으면 좋겠어요


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
        // 파일저장창
        public SaveFileDialog saveFileDialog = new SaveFileDialog();
 
 
         // 엑셀 다운 버튼
        private void button9_Click(object sender, EventArgs e)
        {
            //엑셀 함수 불러오기
            ExportExcel(true);
        }
 
        // 엑셀 export 함수
        private void ExportExcel(bool captions)
        {
            this.saveFileDialog.FileName = "TempName";
            this.saveFileDialog.DefaultExt = "xls";
            this.saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            this.saveFileDialog.InitialDirectory = "c:\\";
 
            DialogResult result = saveFileDialog.ShowDialog();
 
            if (result == DialogResult.OK)
            {
                int num = 0;
                object missingType = Type.Missing;
 
                Excel.Application objApp;
                Excel._Workbook objBook;
                Excel.Workbooks objBooks;
                Excel.Sheets objSheets;
                Excel._Worksheet objSheet;
                Excel.Range range;
 
                string[] headers = new string[dataGridView1.ColumnCount];
                string[] columns = new string[dataGridView1.ColumnCount];
 
                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    headers[c] = dataGridView1.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                    num = c + 65;
                    columns[c] = Convert.ToString((char)num);
                }
 
                try
                {
                    objApp = new Excel.Application();
                    objBooks = objApp.Workbooks;
                    objBook = objBooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
                    objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 
                    if (captions)
                    {
                        for (int c = 0; c < dataGridView1.ColumnCount; c++)
                        {
                            range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                            range.set_Value(Missing.Value, headers[c]);
                        }
                    }
 
                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.ColumnCount; j++)
                        {
                            range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),
                                                                   Missing.Value);
                            range.set_Value(Missing.Value,
                                                  dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }
                    }
 
                    objApp.Visible = false;
                    objApp.UserControl = false;
 
                    objBook.SaveAs(@saveFileDialog.FileName,
                              Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                              missingType, missingType, missingType, missingType,
                              Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                              missingType, missingType, missingType, missingType, missingType);
                    objBook.Close(false, missingType, missingType);
 
                    Cursor.Current = Cursors.Default;
 
                    MessageBox.Show("Save Success!!!");
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);
 
                    MessageBox.Show(errorMessage, "Error");
                }
            }
        }
cs


'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15

+ Recent posts

티스토리툴바