Andrew Bellamy

get all students from Serco Facility MSSQL Database

21st July 2010 No Comments Andy Categories: code Tags: C#, MSSQL

to start

//  Change connectionString as requried
static string connectionString = "Data Source=SERVER;Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD;";
static string sql = "SELECT * FROM STUDENTS WHERE SetID = @DataSet";

the method

you can use either pieces of code

code 1

public static object[] GetStudents(string dataset)
{
	ArrayList temp = new ArrayList();

	using (SqlConnection sqlConnection = new SqlConnection(connectionString))
	{
		using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
		{
			sqlCommand.Parameters.Add("@DataSet", SqlDbType.VarChar);
			sqlCommand.Parameters["@DataSet"].Value = dataset;

			sqlConnection.Open();

			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			int studentID = sqlDataReader.GetOrdinal("StudentID");
			int name = sqlDataReader.GetOrdinal("Name");
			int classGroupId = sqlDataReader.GetOrdinal("ClassGroupId");

			while (sqlDataReader.Read())
			{
				temp.Add(new object[] { sqlDataReader.GetValue(studentID), sqlDataReader.GetValue(name), sqlDataReader.GetValue(classGroupId) });
			}

			sqlConnection.Close();
		}
	}

	return temp.ToArray();
}

code 2

public static DataSet GetStudents(string dataset)
{
	DataSet dataSet;

	using (dataSet = new DataSet())
	{
		using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, connectionString))
		{
			sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@DataSet", dataset);
			sqlDataAdapter.Fill(dataSet);
		}
	}

	return dataSet;
}

usage

for code 1

static void Main(string[] args)
{
	object[] students = GetStudents("2010/2011");
	foreach (object[] student in students)
	{
		Console.WriteLine(student[0] + "~" + student[1] + "~" + student[2]);
	}
}

for code 2

static void Main(string[] args)
{
	using (DataSet dataSet = GetStudents("2010/2011"))
	{
		foreach (DataRow dataRow in dataSet.Tables[0].Rows)
		{
			Console.WriteLine(dataRow[0] + "~" + dataRow[1] + "~" + dataRow[2]);
		}
	}
}