DataSetTest.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseProject
{
  public class DataSetTest
  {
    public void Test() {


      string connectionString = "server=localhost;DataBase=CourseDatabase;uid=sa;pwd=godoro";
      SqlConnection connection = new SqlConnection(connectionString);

      string sql = "select * from Student";
      SqlCommand command = new SqlCommand(sql, connection);
      SqlDataAdapter adapter = new SqlDataAdapter();
      adapter.SelectCommand = command;
      DataSet dataSet = new DataSet();
      adapter.Fill(dataSet, "Student");
      
      connection.Close();

      Console.WriteLine("Row Count  : " + dataSet.Tables["Student"].Rows.Count);
      foreach (DataRow row in dataSet.Tables["Student"].Rows) {
        int studentId = (int)row["StudentId"];
        string firstName = (string)row["FirstName"];
        string lastName = (string)row["LastName"];
        DateTime birthDate = (DateTime)row["BirthDate"];
        bool isGraduated = (bool)row["IsGraduated"];
        double bodyWeight = (double)row["BodyWeight"];
        int gradeNumber = (int)row["GradeNumber"];
        Console.WriteLine(studentId + " , " + firstName + "," + lastName + " ,"
          + birthDate + " , " + isGraduated + "," + bodyWeight, gradeNumber);
      }

    }
    public void TestAdvanced() {

      DataSet studentDataSet = new DataSet();
      
      DataTable studentTable = new DataTable("Student");
      
      studentTable.Columns.Add("StudentId",typeof(int));
      studentTable.Columns.Add("FirstName",typeof(string));
      studentTable.Columns.Add("BirthDate", typeof(DateTime));
      studentTable.Columns.Add("IsGraduated", typeof(bool));
      studentTable.Columns.Add("BodyWeight", typeof(double));
      studentTable.Columns.Add("GradeNumber", typeof(int));
      studentTable.Columns.Add("SchoolId", typeof(int));

      studentDataSet.Tables.Add(studentTable);

      string connectionString = "server=localhost;DataBase=CourseDatabase;uid=sa;pwd=godoro";
      SqlConnection connection = new SqlConnection(connectionString);

      SqlDataAdapter adapter = new SqlDataAdapter();
      
      string sqlStudentSelect="select * from Student";
      adapter.SelectCommand = new SqlCommand(sqlStudentSelect, connection);
      
      string sqlStudentInsert = "insert into Student(FirstName,LastName,BirthDate,IsGraduated,BodyWeight,GradeNumber,SchoolId)"
        +"values(@FirstName,@LastName,@BirthDate,@IsGraduated,@BodyWeight,@GradeNumber,@SchoolId)";
      adapter.InsertCommand = new SqlCommand(sqlStudentInsert, connection);
      adapter.InsertCommand.Parameters.Add("FirstName", SqlDbType.VarChar).SourceColumn = "FirstName";
      adapter.InsertCommand.Parameters.Add("LastName", SqlDbType.VarChar).SourceColumn = "LastName";
      adapter.InsertCommand.Parameters.Add("BirthDate", SqlDbType.DateTime).SourceColumn = "BirthDate";
      adapter.InsertCommand.Parameters.Add("IsGraduated", SqlDbType.Bit).SourceColumn = "IsGraduated";
      adapter.InsertCommand.Parameters.Add("BodyWeight", SqlDbType.Float).SourceColumn = "BodyWeight";
      adapter.InsertCommand.Parameters.Add("GradeNumber", SqlDbType.Int).SourceColumn = "GradeNumber";
      adapter.InsertCommand.Parameters.Add("SchoolId", SqlDbType.Int).SourceColumn = "SchoolId";


      string sqlStudentDelete = "delete from Student where StudentId=@StudentId";
      adapter.DeleteCommand = new SqlCommand(sqlStudentDelete, connection);
      adapter.DeleteCommand.Parameters.Add("StudentId", SqlDbType.Int).SourceColumn = "StudentId";

      string sqlStudenUpdate = "update Student set FirstName=@FirstName,LastName=@LastName,BirthDate=@BirthDate,"
        +"IsGraduated=@IsGraduated,BodyWeight=@BodyWeight,GradeNumber=@GradeNumber,SchoolId=@SchoolId  "
        +"where StudentId=@StudentId";
      adapter.UpdateCommand = new SqlCommand(sqlStudenUpdate, connection);
      adapter.UpdateCommand.Parameters.Add("StudentId", SqlDbType.Int).SourceColumn = "StudentId";
      adapter.UpdateCommand.Parameters.Add("FirstName", SqlDbType.VarChar).SourceColumn = "FirstName";
      adapter.UpdateCommand.Parameters.Add("LastName", SqlDbType.VarChar).SourceColumn = "LastName";
      adapter.UpdateCommand.Parameters.Add("BirthDate", SqlDbType.DateTime).SourceColumn = "BirthDate";
      adapter.UpdateCommand.Parameters.Add("IsGraduated", SqlDbType.Bit).SourceColumn = "IsGraduated";
      adapter.UpdateCommand.Parameters.Add("BodyWeight", SqlDbType.Float).SourceColumn = "BodyWeight";
      adapter.UpdateCommand.Parameters.Add("GradeNumber", SqlDbType.Int).SourceColumn = "GradeNumber";
      adapter.UpdateCommand.Parameters.Add("SchoolId", SqlDbType.Int).SourceColumn = "SchoolId";
     
      connection.Open();

      adapter.Fill(studentDataSet,"Student");

      Console.WriteLine("Row Count  : " + studentDataSet.Tables["Student"].Rows.Count);
      foreach (DataRow studentRow in studentDataSet.Tables["Student"].Rows) {
        int studentId = (int)studentRow["StudentId"];
        string firstName = (string)studentRow["FirstName"];
        string lastName = (string)studentRow["LastName"];
        DateTime birthDate = (DateTime)studentRow["BirthDate"];
        bool isGraduated = (bool)studentRow["IsGraduated"];
        double bodyWeight = (double)studentRow["BodyWeight"];
        int gradeNumber = (int)studentRow["GradeNumber"];
        int schoolId = (int)studentRow["SchoolId"];
        Console.WriteLine(studentId + " , " + firstName + "," + lastName + " ," + birthDate + " , "
           + isGraduated + "," + bodyWeight + "," + gradeNumber + "," + schoolId);
      }

      studentDataSet.Tables["Student"].Rows[0].Delete();

      studentDataSet.Tables["Student"].Rows[1]["FirstName"] = "Remzi";

      DataRow newStudentRow = studentDataSet.Tables["Student"].NewRow();
      newStudentRow["FirstName"] = "Muhlis";
      newStudentRow["LastName"] = "Akarsu";
      newStudentRow["BirthDate"] = new DateTime(1930, 11, 5);
      newStudentRow["IsGraduated"] = false;
      newStudentRow["BodyWeight"] = 69.8;
      newStudentRow["GradeNumber"] = 3;
      newStudentRow["SchoolId"] = 1;
      studentTable.Rows.Add(newStudentRow);

      adapter.Update(studentDataSet, "Student");

    }
  }
}