Пример #1
0
        public Student GetStudent(int studentId)
        {
            // Lets create a variable to hold the student we're returning
            Student student = null;

            // Declaring our SqlConnection inside of a "using" statement ensures the connection gets closed/disposed correctly
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                // The SqlCommand object holds our T-SQL code with placeholders for parameters (@ParameterName)
                SqlCommand cmd = new SqlCommand("SELECT Id, Name FROM Student WHERE Id = @StudentId", connection);
                // Create a SqlParameter with the correct parameter name and type (varchar = SqlDbType.Text)
                SqlParameter parameter = new SqlParameter("StudentID", SqlDbType.Int);
                // Set the SqlParameter value to our method parameter
                parameter.Value = studentId;
                // Add the SqlParameter we created to the SqlCommand.Parameters collection so it can be used
                cmd.Parameters.Add(parameter);

                // Open the database connection
                connection.Open();

                // ExecuteReader returns a DataReader object that knows how to read each record in sequence
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    // This while statement sill step through each individual record until the DataReader has reached the end of the results
                    while (dr.Read())
                    {
                        // Now that we have returned a student, lets initialize the "student" variable
                        student = new Student();
                        // The DataReader column can be specified by ordinal (ie. 0) or column name (ie. "Name")
                        student.Id = System.Int32.Parse(dr["Id"].ToString());
                        student.Name = dr["Name"].ToString();
                    }
                }
            }
            return student;
        }
Пример #2
0
        public List<Student> GetStudentList()
        {
            // Create a List of Students to hold what is returned
            List<Student> studentList = new List<Student>();

            // Declaring our SqlConnection inside of a "using" statement ensures the connection gets closed/disposed correctly
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                // The SqlDataAdapter knows how to use a Command to fill a DataSet
                SqlDataAdapter adapter = new SqlDataAdapter();

                // Our command will query all student records
                adapter.SelectCommand = new SqlCommand("SELECT * FROM Student", conn);
                // Open the database connection
                conn.Open();

                // Create a DataSet variable to hold the results
                DataSet dataSet = new DataSet();

                // The DataAdapter.Fill method puts the results in a DataSet variable
                adapter.Fill(dataSet);

                // DataSets can contain multiple tables, but we're just going to look at the rows returned in table index 0
                foreach(DataRow row in dataSet.Tables[0].Rows)
                {
                    // We need a Student object to hold the values
                    Student student = new Student();
                    student.Id = System.Int32.Parse(row["Id"].ToString());
                    student.Name = row["Name"].ToString();
                    // Lets add our student to the list
                    studentList.Add(student);
                }
            }
            return studentList;
        }