private static List <ExceptionEntity> ReadExceptions() { string connectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = TwentyOneGame; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False"; string queryString = @"Select Id, ExceptionType, ExceptionMessage, TimeStamp From Exceptions"; List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ExceptionEntity exception = new ExceptionEntity(); exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); } connection.Close(); } return(Exceptions); }
} // Below returns a list of exceptions and displays them. private static List <ExceptionEntity> ReadException() { string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TwentyOneGame;Integrated Security=True;Connect Timeout=30; Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; string queryString = @"Select Id, ExceptionType, ExceptionMessage,TImeStamp From Exceptions"; List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); using (SqlConnection connection = new SqlConnection(connectionString)) { // What is inside the curly brackets is what happens while there is a sql connection. SqlCommand command = new SqlCommand(queryString, connection); // Passing in parameters. connection.Open(); // Opens sql connection SqlDataReader reader = command.ExecuteReader(); // Extracts information from database while (reader.Read()) // Reads objects in database. { ExceptionEntity exception = new ExceptionEntity(); exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); } connection.Close(); } return(Exceptions); // Returns a list of exceptions using ADO.NET Framework }
private static List <ExceptionEntity> ReadExceptions() //added 3/30 This SQL function literally READS the data and assigns the data to a LIST object. { string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TwentyOneGame;Integrated Security=True; Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite; MultiSubnetFailover=False"; string queryString = @"Select Id, ExceptionType, ExceptionMessage, TimeStamp From Exceptions"; // choose what and where to grab the data that we need. Again it is just reading it. List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); // this will read the data written in the DB while (reader.Read()) // while the reader goes through each line. { ExceptionEntity exception = new ExceptionEntity(); // create the object to place all the data we need into. exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); } connection.Close(); } return(Exceptions); }
//ADO.Net Example private static List <ExceptionEntity> ReadExceptions() { string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TwentyOneGame;Integrated Security=True;Connect Timeout=30;Encrypt=False; TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; string queryString = @"Select Id, ExceptionType, ExceptionMessage, Timestamp From Exceptions"; List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) //<--reads through each entry in SQL table, creates a new object and maps the data to the object { ExceptionEntity exception = new ExceptionEntity(); exception.ID = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["Timestamp"]); Exceptions.Add(exception); //adds the new object to the List Exceptions } connection.Close(); //closes the connection } return(Exceptions); //returns the output expected by the method }
private static List <ExceptionEntity> ReadExceptions() // this method will query the database and return all the exceptions { string connectionString = @" Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = TwentyOneGame; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False"; string queryString = @"Select Id, ExceptionType, ExceptionMessage, TImeStamp From Exceptions";//asking for all exceptions List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) //loops through each record and creates new exception entity object { ExceptionEntity exception = new ExceptionEntity(); exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); // adds to Exceptions list } connection.Close(); } return(Exceptions); }
private static List <ExceptionEntity> ReadExceptions() { string connectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = TwentyOneGame; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False"; //Basically asking for everything w/o a "where" clause string queryString = @"Select Id, ExceptionType, ExceptionMessage, TimeStamp From Exceptions"; List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); //Empty list that will return. //Open up the connection using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //Pass in our connection string connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) //while reader is open for each object... { ExceptionEntity exception = new ExceptionEntity(); //map what we are getting back to our object exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); } connection.Close(); } return(Exceptions); //Returns a list of Exception entities. }
private static List <ExceptionEntity> ReadExceptions() { string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TwentyOneGame; Integrated Security=True;Connect Timeout=30;Encrypt=False; TrustServerCertificate=False;ApplicationIntent=ReadWrite; MultiSubnetFailover=False"; //NOTE: Please be aware that in bigger applicaions you won't have a connection string like this, and // You'll be using a method to call a piece of information from your configuration file that will // appoint you to that connection string. The reason for this is that if for whatever reason that string // changes, you would have to change this string throught your entire program. (Just something to be aware of). //NOTE: This is our query string selects four entities in the 'Exceptions' table in the 'TwentyOneGame' Database. string queryString = @"Select Id, ExceptionType, ExceptionMessage, TimeStamp From Exceptions"; List <ExceptionEntity> Exceptions = new List <ExceptionEntity>(); //NOTE: Here we are instantiating an SqlConnection. using (SqlConnection connection = new SqlConnection(connectionString)) { //NOTE: Here we are creating a 'command' object which takes the query and the connection strnigs. SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); //NOTE: After openning the connection to the database, creating an 'SqlDataReader' object by // calling the 'ExecuteReader' method (which sends the SqlCommand.CommandText to the SqlCommand.Connection // and returns a SqlDataReader object) from the 'command' object we instantiated. SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { //NOTE: What 'reader.Read()' will do is, it loops through each record. It returns 'true' if there are more rows, otherwise // it will return false. //NOTE: So for each record, we will instantiate an 'ExceptionEntity' object (from the 'ExceptionEntity' that we created), // then we will user the 'reader' object to read and assign each entity (Id, ExceptionType, ExceptionMessage, TimeStamp) to // its corresponding 'ExceptionEntity' class property. //NOTE: Basically we are mapping each database record to our object. ExceptionEntity exception = new ExceptionEntity(); //NOTE: Since we are getting Sql back, we need to make sure to typecast each column-entity value to its the corresponding type // that our object properties are expecting. exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); //NOTE: The only problem about using ADO.NET where we are typing the column names, there is no intellisense that will let you know // if it is typed in incorrectly, and the developer can only find out something is wrong until after running the program. //NOTE: After assigning values to the current object, we begin to append each exception entity to our 'Exceptions' list. Exceptions.Add(exception); } connection.Close(); } //NOTE: Here we are returning the 'exceptions' object which is a list of 'ExceptionEntity' objects that will contain our entities // from our databse as properties. return(Exceptions); }
/* ----------------------------------- * READING FROM DB TO CONSOLE (admin) * --------------------------------- */ private static List <ExceptionEntity> ReadExceptions() { //establish connection credentials string connectionString = @" Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = TwentyOneGame; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False "; //select * from exception inside connection string queryString = @" SELECT Id, ExceptionType, ExceptionMessage, TimeStamp FROM Exceptions "; //init empty list of object type (to be filled w/ "rows"/"objects" var Exceptions = new List <ExceptionEntity>(); using (var connection = new SqlConnection(connectionString)) { //build the command and open the connection var command = new SqlCommand(queryString, connection); connection.Open(); //init data reader obj, to loop through each record SqlDataReader reader = command.ExecuteReader(); //enters loop to exctract, record by record (access columns w/ reader[columnX] while (reader.Read()) { var exception = new ExceptionEntity(); exception.Id = Convert.ToInt32(reader["Id"]); exception.ExceptionType = reader["ExceptionType"].ToString(); exception.ExceptionMessage = reader["ExceptionMessage"].ToString(); exception.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]); Exceptions.Add(exception); } connection.Close(); } return(Exceptions); }