Connection, Command, INSERT, UPDATE, ExcuteNonQuery, 프로시져 연결, Parameters

1. 코넥션, 커맨드 짝지어주기, 쿼리문 스트링형태로 전달

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

//ADO.NET 관련 클래스

using System.Data;//DB의 종류에 상관없이 일반적인 DB작업 관련 클래스

using System.Data.SqlClient;//MS-SQL(여러버전) 2000이상, MS-SQL Data Provider

using System.Data.OleDb;//OLEDB Data Provider Oracle 접근 가능

using System.Data.Odbc;//ODBC Data Provider Oracle 접근 가능

//--oracle.com -> 오라클 전용 Provider를 제공한다. 설치하면 된다. 오라클 버젼별로 만들어 주어야 한다.

namespace AdoConsole

{

        class Connection

        {

               static void Main(string[] args)

               {

                       //사람 -> SSMS -> SQL -> 질의실행 -> DB -> 결과

                       //응용프로그램 -> C# -> SQL -> 질의실행 -> DB -> 결과

                       //데이타베이스 서버는 SQL만 받는다.

 

                       //SQL 사용하는 절차(사람)

                       //1. SSMS 실행

                       //2. 연결(서버, 계정명, 암호)

                       //3. 데이터베이스 선택

                       //4. SQL 작성

                       //5. SQL 실행

                       //6. 결과

                       //7. 연결종료

 

                       //SQL 사용하는 절차(C#)

                       //1. SSMS -> C#프로그램을 통해서(ADO.NET 클래스)

                       //2. 연결

                       SqlConnection con = new SqlConnection();

                       //OdbcConnection con1;

                       //OleDbConnection con2;

                       //OracleConnection con3;

 

                       // - 연결 문자열(DB에 접속하기 위해서 알아야 하는 정보)

                       //   - 서버주소, 계정, 암호, 데이터베이스

                       // DB는 항상 try catch를 염두해 둔다.

                       //server 주소가 틀리거나 서버가 꺼져있을경우

                       con.ConnectionString = "server=localhost; database=ADONET; uid=sa2; pwd=net401$!";//연결 문자열

 

                       Console.WriteLine(con.State);//State 열거형 속성 연결상태

 

                       // - 연결

                       con.Open();//예외 발생 가능성이 있다.

 

                       Console.WriteLine(con.State);//State 열거형 속성 연결상태

 

                       //4.5.6 진행...

 

                       //7. 연결 종료

                       con.Close();

 

                       Console.WriteLine(con.State);//State 열거형 속성 연결상태

               }

        }

}

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

        class insert

        {

               static void Main(string[] args)

               {

                       //tblAddress에 레코드 추가

                      

                       //1. DB연결

                       SqlConnection con = new SqlConnection();

                       con.ConnectionString = "server=localhost;database=ADONET;uid=sa;pwd=net401$!";

                       con.Open();

 

                       if (con.State == ConnectionState.Open)

                       {

                              //2. Insert

                              //2.1 명령객체 생성

                              // - 모든 SQL 구문을 실행

                              // - 명령은 1회 이상 가능

                              // - 동일한 SQL을 여러번 반복 O

                              // - SQL을 바꿔서 다시 실행O

                              SqlCommand cmd = new SqlCommand();

 

                              //2.2 명령객체에게 연결객체 소개(필수!!)

                              //커맨드객체는 코넥션을 가지고 있어야한다.

                              cmd.Connection = con;

 

                              //2.3 실행할 SQL 작성

                             cmd.CommandText = @"INSERT INTO tblAddress (name, age, email, [address], tel)

                                                                    VALUES ('홍길동', 20, 'hong@test.com', '서울시', '010-5555-6666');";

 

                              //2.4 SQL 실행

                              //2.4.1 반환값이 없는 질의(나머지)

                              //2.4.2 반환값이 있는 질의(select)

                              Console.WriteLine("현재 인서트된 갯수는 {0}건 입니다.",cmd.ExecuteNonQuery());

                       }

 

                       con.Close();

               }

        }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

        class input

        {

               static void Main(string[] args)

               {

                       //tblAddress에 레코드 추가

 

                       //1. DB연결

                       SqlConnection con = new SqlConnection();

                       con.ConnectionString = "server=localhost;database=ADONET;uid=sa;pwd=net401$!";

                       con.Open();

 

                       if (con.State == ConnectionState.Open)

                       {

                              SqlCommand cmd = new SqlCommand();

 

                              //2.2 명령객체에게 연결객체 소개(필수!!)

                              //커맨드객체는 코넥션을 가지고 있어야한다.

                              cmd.Connection = con;

 

                              //2.3 실행할 SQL 작성

                      

                              //사전 작업 - 사용자로부터 데이터 입력

                              //SQL의 자료형과 C#의 자료형은 아무 상관이 없다.

                              Console.Write("이름 : ");//nvarchar(10)

                              string name = Console.ReadLine();

 

                              Console.Write("나이 : ");//int

                              string age = Console.ReadLine();

 

                              Console.Write("이메일 : ");//nvarchar(50)

                              string email = Console.ReadLine();

 

                              Console.Write("주소 : ");//nvarchar(200)

                              string address = Console.ReadLine();

 

                              Console.Write("전화번호 : ");//nvarchar(15);

                              string tel = Console.ReadLine();

 

                              //예외처리 (호따옴표 하나를 호따옴표 두개로 바꾸려면 (' -> '')

                              name = name.Replace("'", "''");

                              age = age.Replace("'", "''");

                              email = email.Replace("'", "''");

                              address = address.Replace("'", "''");

                              tel = tel.Replace("'", "''");

 

 

                              //cmd.CommandText = @"INSERT INTO tblAddress (name, age, email, [address], tel)  VALUES ('"+name+"', '"+age+"', '"+email+"', '"+address+"', '"+tel+"');";

                              cmd.CommandText = string.Format(@"insert into tblAddress (name, age, email, [address], tel) values ('{0}', {1}, '{2}', '{3}', '{4}');", name, age, email, address, tel);

                              //위에 구문중 하나가 빠지게 되면 쿼리 에러가 난다.

                              //디버깅용 확인용

                              Console.WriteLine(cmd.CommandText);

                              Console.ReadLine();

                              Console.WriteLine("현재 인서트된 갯수는 {0}건 입니다.", cmd.ExecuteNonQuery());

                       }

 

                       con.Close();

               }

        }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data;

using System.Data.SqlClient;

 

 

namespace AdoConsole

{

        class Update

        {

               static void Main(string[] args)

               {

                       //반환값이 없는 쿼리 : ExecuteNonQuery()

 

                       //회원 번호 입력 -> 주소를 변경

                       //UPDATE tblAddress SET

                       //address = '주소'

                       //WHERE  seq = 5;

 

                       //1. 연결작업

                       SqlConnection con = new SqlConnection();

                       con.ConnectionString = Settings1.Default.ADONETConStr;

                       con.Open();

 

                       //2. 어느 회원?

                       Console.Write("수정할 회원 번호 : ");

                       string seq = Console.ReadLine();

 

                       Console.Write("수정할 주소 : ");

                       string address = Console.ReadLine();

 

                       //3. 명령 객체

                       SqlCommand cmd = new SqlCommand();

                       cmd.Connection = con;

                      

                       string query = string.Format(@"UPDATE tblAddress SET [address] = '{0}' WHERE seq = {1}", address, seq);

                       cmd.CommandText = query;//실제 쿼리문 날리기~

 

                       //4. 실행

                       int result = cmd.ExecuteNonQuery();

                       if (result == 1)

                              Console.WriteLine("수정되었습니다.");

                       else

                              Console.WriteLine("수정되지 않았습니다.");

 

                       //5. dusruf whdfy

                       con.Close();

               }

        }

}

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

        class Delete

        {

               static void Main(string[] args)

               {

                       //회원번호를 입력 -> 회원 삭제

 

                       //1. 번호 입력

                       Console.Write("삭제할 회원 번호 : ");

                       string seq = Console.ReadLine();

 

                       //2. 연결 객체

                       SqlConnection con = new SqlConnection(Settings1.Default.ADONETConStr);

                       con.Open();

 

                       //3. 질의문

                       string query = "delete from tblAddress where seq = " + seq;

 

                       //4. 명령 객체

                       SqlCommand cmd = new SqlCommand(query, con);

 

                       //5. 실행

                       int result = cmd.ExecuteNonQuery();

 

                       if (result == 1)

                              Console.WriteLine("삭제했습니다.");

                       else

                              Console.WriteLine("존재하지 않는 회원번호");

 

 

                       con.Close();

               }

        }

}

 

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

        class ExcuteNonQuerycs

        {

               static void Main(string[] args)

               {

 

                       //반환값이 없는 SQL의 실행 방법은 모두 동일

                       Console.Write("실행할 SQL 입력 : ");

                       string query = Console.ReadLine();

 

                       //1.

                       SqlConnection con = new SqlConnection(Settings1.Default.ADONETConStr);

                       con.Open();

 

                       //2.

                       SqlCommand cmd = new SqlCommand(query, con);//쿼리문 넣고 연결객체와 연관짓는 것을 한방에

 

                       //3. 실행!!

                       cmd.ExecuteNonQuery();

 

                       //4.

                       con.Close();

               }

        }

}

 

 

CREATE PROC upTblAddressInsert

AS

INSERT INTO tblAddress VALUES ('홍길동', 20, 'hong@test.com', '서울시', '010-5555-6666', default);

            

DROP PROC uptblAddressInsert    

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

        class Procedure

        {

               static void Main(string[] args)

               {

                       //C#에서 프로시저를 호출하는 방법1

                       SqlConnection con = new SqlConnection(Settings1.Default.ADONETConStr);

                       con.Open();

 

                       SqlCommand cmd = new SqlCommand();

                       cmd.Connection = con;

 

                       cmd.CommandText = "upTblAddressInsert";//***SSMS에서 블럭을 잡아서 F5와 같은 효과

                       cmd.CommandType = CommandType.StoredProcedure;//CommandType.Text가 기본 값이다.

 

                       cmd.ExecuteNonQuery();//proc가 반환값이 없는 프로시져라 ExecuteNonQuery 메소드로 실행

                       con.Close();

 

               }

        }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

           class Procedure02

           {

                     static void Main(string[] args)

                     {

                                //C#에서 프로시저를 호출하는 방법2

                                SqlConnection con = new SqlConnection(Settings1.Default.ADONETConStr);

                                con.Open();

 

                                SqlCommand cmd = new SqlCommand();

                                cmd.Connection = con;

                                cmd.CommandText = "exec upTblAddressInsert2 '헤헤헷', 29, 'kkkk@test.com', 'KH임', '010-000-0000'";

                                Console.WriteLine(cmd.CommandText);

 

                                cmd.ExecuteNonQuery();

 

                                con.Close();

                     }

           }

}

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace AdoConsole

{

           class Parameters

           {

                     static void Main(string[] args)

                     {

                                //** SQL의 인자값이 있을떄 C#에서 호출하는 방법 ** 이방법을 제일 많이 사용한다.

                                SqlConnection con = new SqlConnection(Settings1.Default.ADONETConStr);

                                con.Open();

 

                                SqlCommand cmd = new SqlCommand();

                                cmd.Connection = con;

 

                                //**반드시 프로시저가 아니라도 텍스트쿼리에서도 매개 변수를 사용할 수 있다!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

                                cmd.CommandText = "INSERT INTO tblAddress(name, age, email, address, tel) VALUES (@name, @age, @email, @address, @tel)";

 

                                //SQL에서는 매개변수가 없다고 에러 발생!!!

                                // -> C#측에서 미리 준비를 해줘야 함!!

 

                                //C#측의 매개변수는 SqlCommand가 관리

                                SqlParameter param1  = new SqlParameter("@name", SqlDbType.NVarChar, 10);

                                cmd.Parameters.Add(param1); //Parameters라는 컬렉션

                                cmd.Parameters["@name"].Value = "테스트";

 

                                cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int));

                                cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50));

                                cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 200));

                                cmd.Parameters.Add(new SqlParameter("@tel", SqlDbType.VarChar, 15));

 

                                cmd.Parameters["@age"].Value = 25;

                                cmd.Parameters["@email"].Value = "h@test.com";

                                cmd.Parameters["@address"].Value = "서울시";

                                cmd.Parameters["@tel"].Value = "010-111-1111";

 

                                cmd.ExecuteNonQuery();

 

                                con.Close();

                     }

           }

}