Retrieving data from SQL into a class

Question

Let's say my SQL database contains a table called 'Customer'. This Customer has an ID, a first name, a last name and an image (byte[]).

What I would like is a type (class) on the client that has an int, two strings and a byte[]. Each record would then be read into each one of these.

I know a little of C#, but I'm a bit of a newbie when it comes to accessing with C#, although I have been able to pull down records from a remote server. I'd just like to sort them once they arrive on the client side.

Edit: Okay, it seems I was a bit vague for people's liking, so let me clarify. I know how to make a class, and I have looked up the details about bringing data across from an SQL database into a C# project using ADO.NET commands.

What I want to know is how to mirror the information in the SQL table as an object in my C# project. So, for example, I download one record from my table, it goes into an object.

Previously I've used Silverlight and that has an EDMX model and a domain service class. If anyone is familiar with this behaviour, it's basically what I'm trying to emulate. I'd use that, but one of the limitations I face is that I'm restricted to using the application side alone, not the web site.

Thanks.

this question is too broad. What you're trying to do is DataAccess and binding that data to an object. I'm sure there's tons of information about doing so already. — hunter, Nov 23 '11 at 15:23
Are you asking how to fetch data from database or how to create a class? — Otiel, Nov 23 '11 at 15:24

Olivier Jacot-Descombes · Answer 1 · 2017-11-19T21:48:12.310

Here a short example on how you can retrieve your data using a data reader:

var customers = new List<Customer>();
string sql = "SELECT * FROM customers";
using (var cnn = new SqlConnection(
    "Data Source=Your_Server;Initial Catalog=Your_Database;Integrated Security=SSPI;")) {
    cnn.Open();
    using (var cmd = new SqlCommand(sql, cnn))
    using (SqlDataReader reader = cmd.ExecuteReader()) {
        // Get ordinals from the customers table
        int custIdOrdinal = reader.GetOrdinal("CustomerID");
        int nameOrdinal = reader.GetOrdinal("Name");
        int imageOrdinal = reader.GetOrdinal("Image");
        while (reader.Read()) {
            var customer = new Customer();
            customer.CustomerID = reader.GetInt32(custIdOrdinal);
            customer.Name = reader.IsDBNull(nameOrdinal)
                            ? null
                            : reader.GetString(nameOrdinal);
            if (!reader.IsDBNull(imageOrdinal)) {
                var bytes = reader.GetSqlBytes(imageOrdinal);
                customer.Image = bytes.Buffer;
            }
            customers.Add(customer);
        }
    }
}

If a table column is nullable then check reader.IsDBNull before retrieving the data.

score 4 · Accepted Answer · edited May 23 '17 at 12:07

4

You're talking about creating an ORM (Object-Relational Mapper), which is a bit too broad to be answered fully in a single answer here.

There's a detailed tutorial on how to write one here.

However, it should be noted that there are a large number of ORM tools for .NET already out there, and many of them are already listed in other questions on this site, such as this one.

edited May 23 '17 at 12:07

Community

1
1

answered Nov 23 '11 at 15:27

David

72,686
18
132
173

score 2 · Answer 3 · answered Nov 23 '11 at 15:27

2

Take a look at this article on mapping data types for the CLR. Here's a class definition for a customer:

public class Customer {
public int ID {get;private set;}
public string fName {get; set;}
public string lName {get; set;}
public byte[] image {get; set;}
}

You may want to look at linq to SQL to understand the sorting bit, there is an .OrderBy(f=>f.Name); you can apply to do an order by of your dataset.

Need a bit more information as to what you are after?

answered Nov 23 '11 at 15:27

JonH

32,732
12
87
145

+1 for a good start. I'm guessing he just has no idea where to start. – David Nov 23 '11 at 15:35
Break it down into smaller pieces, I could only answer based on the content of the question. Hopefully it will get him started. – JonH Nov 23 '11 at 15:43

score 0 · Answer 4 · edited Nov 23 '11 at 18:33

0

You should look into using SqlConnection and SqlCommand and filling a DataTable with the information you need.

edited Nov 23 '11 at 18:33

poupou

43,413
6
77
174

answered Nov 23 '11 at 18:16

Alex Seabrooks

61
1
7

Retrieving data from SQL into a class

4 Answers4