예제 #1
0
        public int Save(StudentDTO dto)
        {
            //Step - 1 Build Connection using Connection String
            String connStr = @"Data Source=.\SQLEXPRESS2012;Initial Catalog=TestDB2;Integrated Security= True;Persist Security Info=True;";

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Step 2 Open conneciton
                conn.Open();

                //Step 3 Build your query
                String sqlQuery = "";
                if (dto.StudentID > 0)
                {
                    sqlQuery = String.Format(@"Update dbo.Students SET Name='{0}', DOB='{1}' WHERE StudentID={2}", dto.Name, dto.DOB, dto.StudentID);
                }
                else
                {
                    sqlQuery = String.Format(@"INSERT INTO dbo.Students(Name, DOB) VALUES('{0}','{1}')", dto.Name, dto.DOB);
                    sqlQuery = sqlQuery + "; Select Scope_Identity()";
                }

                //Step 4 Build Command Object
                SqlCommand command = new SqlCommand(sqlQuery, conn);

                int studentid = 0;

                if (dto.StudentID > 0)
                {
                    command.ExecuteNonQuery();
                    studentid = dto.StudentID;
                }
                else
                {
                    // Step 5 Execute Scalar
                    studentid = Convert.ToInt32(command.ExecuteScalar());
                }


                return(studentid);
            }
        }
예제 #2
0
        public void Update(StudentDTO dto)
        {
            //Step - 1 Build Connection using Connection String
            String connStr = @"Data Source=.\SQLEXPRESS2012;Initial Catalog=TestDB2;Integrated Security= True;Persist Security Info=True;";

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Step 2 Open conneciton
                conn.Open();

                //Step 3 Build your query
                String sqlQuery = String.Format(@"Update dbo.Students SET Name='{0}', DOB='{1}' WHERE StudentID={2}", dto.Name, dto.DOB, dto.StudentID);

                //Step 4 Build Command Object
                SqlCommand command = new SqlCommand(sqlQuery, conn);

                // Step 5 Execute Non Query

                int recAff = command.ExecuteNonQuery();

                Console.WriteLine("Records Effected {0}", recAff);
            }
        }
예제 #3
0
        public List <StudentDTO> GetAllStudents()
        {
            //Step - 1 Build Connection using Connection String
            String connStr = @"Data Source=.\SQLEXPRESS2012;Initial Catalog=TestDB2;Integrated Security= True;Persist Security Info=True;";

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Step 2 Open conneciton
                conn.Open();

                //Step 3 Build your query
                String sqlQuery = "Select * from dbo.Students";

                //Step 4 Build Command Object
                SqlCommand command = new SqlCommand(sqlQuery, conn);

                //Step 5 Execute Command (ExecuteReader) in this case
                SqlDataReader reader = command.ExecuteReader();

                //Step 6 Iterate reader and fill LIST
                List <StudentDTO> stdList = new List <StudentDTO>();

                while (reader.Read() == true)
                {
                    StudentDTO dto = new StudentDTO();

                    //Find index first and then get value
                    dto.StudentID = reader.GetInt32(reader.GetOrdinal("StudentID"));
                    dto.Name      = reader.GetString(reader.GetOrdinal("Name"));
                    dto.DOB       = reader.GetDateTime(reader.GetOrdinal("DOB"));

                    stdList.Add(dto);
                }

                return(stdList);
            }
        }