Interacting with a database using C # without Entity Framework

Question

I am assigned a task where I need to display a form whose data is in different tables on the Sql server. The requirement is strictly not to use Entity infrastructure or stored procedure. Then what are my options?

I am currently running some sql queries using the SqlCommand object, but then it gets very complicated when it comes to getting relational data, and then allows the user to update it from the form.

What is the best way / approach in Winforms to view and edit relational data?

Answer 1

• Question # 1 ends with "[besides the Entity structure], what are my options?" and you got the answers to this in the comments (direct ADO.NET or NHibernate). You also know that since you said you were using SqlCommand.

• Question # 2 is difficult, and I think this is what your instructor wants you to find out: "What is the best way / approach in Winforms to view and edit relational data?"

I use a ton of user interface for relational data using Microsoft Stack. But regardless of technology, you need a good template or method. The short answer (this is not a book) is to abstract data from the user interface. Thus, they can operate independently and also independently of each other.

First, implement the editing data and display the data should be two different parts of your program. One method that I like is to select the relationship data from SQL SERVER as XML, and then use XSLT to display it:

<Customer> <Name>... <Company>... <Phone>... <BillingAddresses> <Address>... <Address>... <PayementMethods> <CreditCard>... <CreditCard>... <RecentOrders> <Order>... <Order>... <Order>... 

You can use XSLT to easily do this in HTML, which looks just the way you want it. In this example, next to where you show each <Address> and <CreditCard> , you can place the EDIT / DELETE links along with the ADD links under each section.

By clicking EDIT / DELETE / ADD, you will be taken to the screen to modify this piece of relational data. When you return to the screen displaying XML as HTML, you will see updated data.

This is good because you can easily change your XML without recompiling the code. You can also use XSLT to display your data in any layout you want. Then your XML can include literals for what is stored as codes (AMX = AMERICAN EXPRESS, etc.).

Anywho, this is just one solution without real requirements (for example, support for "UNDO", the simultaneous application of several changes, etc.).

Answer 2

You can write your own simple classes for the objects you need to access / update. For the purposes of the following example, suppose you have 2 tables:

Person

• Person_Id INT PRIMARY KEY NOT NULL
• Name NVARCHAR (100) NULL

Letters

• Person_Id INT PRIMARY KEY NOT NULL
• Email_Id INT PRIMARY KEY NOT NULL

• Email NVARCHAR (100) NOT NULL

   public class MyProgram { public List<Person> ReadRecords() { // Set up your connection SqlConnection conn = new SqlConnection(); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM Person", conn); SqlDataReader reader = cmd.ExecuteReader(); List<Person> personRecords = new List<Person>(); while (reader.Read()) { Person p = new Person(reader, conn); personRecords.Add(p); } return personRecords; } public int UpdateRecords(IEnumerable<Person> records, SqlConnection conn) { int personsUpdated = 0; int recordsUpdated = 0; foreach (Person p in records) { if (p.Changed) { recordsUpdated += p.Update(conn); personsUpdated++; } } return recordsUpdated; } } public class Person { public const string SqlGetPersonEmailsCommand = "SELECT Email_Id, Email FROM Emails WHERE Person_Id = @Person_Id"; public const string SqlUpdatePersonCommand = "UPDATE Person SET Name = @Name WHERE Id = @OriginalId"; public const string SqlUpdatePersonEmailCommand = "UPDATE Emails SET Email = @Email WHERE Email_Id = @Email_Id"; public int OriginalId { get; private set; } private bool personChanged; private bool emailsChanged { get { return changedEmails.Count > 0; } } public bool Changed { get { return personChanged || emailsChanged; } } private int _id; public int Id { get { return _id; } set { throw new Exception("Changing Id is not allowed."); } } private string _name; public string Name { get { return _name; } set { _name = value; personChanged = true; } } private List<int> changedEmails; private Dictionary<int, string> _emailAddresses; public string[] EmailAddresses { get { string[] values = new string[_emailAddresses.Count]; _emailAddresses.Values.CopyTo(values, 0); return values; } } public void UpdateEmail(int emailId, string newEmail) { _emailAddresses[emailId] = newEmail; changedEmails.Add(emailId); } public Person(IDataReader reader, SqlConnection conn) { // Read ID (primary key from column 0) OriginalId = _id = reader.GetInt32(0); // Check if value in column 1 is Null; if so, set _name to Null, otherwise read the value _name = reader.IsDBNull(1) ? null : reader.GetString(1); // Now get all emails for this Person record SqlCommand readEmailsCmd = new SqlCommand(SqlGetPersonEmailsCommand, conn); readEmailsCmd.Parameters.Add("@Person_Id", SqlDbType.Int); readEmailsCmd.Parameters["@Person_Id"].Value = OriginalId; SqlDataReader emailReader = readEmailsCmd.ExecuteReader(); changedEmails = new List<int>(); _emailAddresses = new Dictionary<int, string>(); if (emailReader.HasRows) { while (emailReader.Read()) { int emailId = emailReader.GetInt32(0); string email = emailReader.GetString(1); _emailAddresses.Add(emailId, email); } } } public int Update(SqlConnection conn) { int rowsUpdated = 0; SqlCommand command = null; // Update Person record if (personChanged) { command = new SqlCommand(SqlUpdatePersonCommand, conn); command.Parameters.Add("@OriginalId", SqlDbType.Int); command.Parameters["@OriginalId"].Value = OriginalId; command.Parameters.Add("@Name", SqlDbType.NVarChar); command.Parameters["@Name"].Value = _name; rowsUpdated = command.ExecuteNonQuery(); } // Now update all related Email records foreach (int id in changedEmails) { command = new SqlCommand(SqlUpdatePersonEmailCommand, conn); command.Parameters.Add("@Email_Id", SqlDbType.Int); command.Parameters["@Email_Id"].Value = id; command.Parameters.Add("@Email", SqlDbType.NVarChar); command.Parameters["@Email"].Value = _emailAddresses[id]; rowsUpdated = +command.ExecuteNonQuery(); } return rowsUpdated; } } 

The above example supports changing the username and associated email addresses.