Ado.net Datareader example

Example use of a datereader:

A datareader means speed, use it if you need a high-speed collection of read-only data!

    public class Employee
    {
        public string Voornaam { get; set; }
        public string Achternaam { get; set; }
    }

    class Employees
    {
        private const string connectionString = @"Server=serverName;Database=databaseName;User Id=userName;Password=passWord;";

        public static List GetEmployees()
        {
            List employeelist = new List ();
            using(SqlConnection sqlConnection = new SqlConnection (connectionString))
	        {
		        SqlCommand sqlCommand = new SqlCommand ("Select * from Filialen", sqlConnection);
                sqlConnection.Open();
                using(SqlDataReader sqlReader = sqlCommand.ExecuteReader())
	            {
                    while (sqlReader.Read())
	                {
	                    Employee employee = new Employee (){ Voornaam = (String)sqlReader["OndernemerNaam"]};
                        employeelist.Add(employee);
	                }
	            }
	        }
            return employeelist;
        }
    }

Ado.net and Azure

Big difference between a Azure database and a on premise database is latency. There simply will be more time-outs. time out errors are transient meaning they will go away. therefor you have to check your sql-logic for transient errors and to a retry after a few seconds. Windows azure offers the iTransientErrorDetectionStrategy interface:

    class MyRetryStrategy : ITransientErrorDetectionStrategy
    {
        public bool IsTransient(Exception ex)
        {
            if (ex != null && ex is SqlException)
            {
                foreach (SqlError error in (ex as SqlException).Errors)
                {
                    switch (error.Number)
                    {
                        case 1205:
                            System.Diagnostics.Debug.WriteLine("SQL Error: Deadlock condition. Retrying...");
                            return true;
                        case -2:
                            System.Diagnostics.Debug.WriteLine("SQL Error: Timeout expired. Retrying...");
                            return true;
                    }
                }
            }
            // For all others, do not retry.
            return false;
        }
    }

Now use this class together with your Ado.net code to implement TransientErrorDetecton, example

    public class Employees
    {
        private const string connectionString = @"Server=serverName;Database=databaseName;User Id=userName;Password=passWord;";

        public static List GetEmployees()
        {
            List employeelist = new List();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                RetryPolicy retry = new RetryPolicy(5, new TimeSpan(0, 0, 5));
                SqlCommand sqlCommand = new SqlCommand("Select * from Filialen", sqlConnection);
                sqlConnection.OpenWithRetry(retry);
                using (SqlDataReader sqlReader = sqlCommand.ExecuteReaderWithRetry(retry))
                {
                    while (sqlReader.Read())
                    {
                        Employee employee = new Employee() { Voornaam = (String)sqlReader["OndernemerNaam"] };
                        employeelist.Add(employee);
                    }
                }
            }
            return employeelist;
        }
    }

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!