How to convert SqlDataReader to LINQ way?

Question

I wonder how to convert SqlDataReader to LINQ way?

Here is my SqlDataReader code.

string strConn = "server=xxx.com;database=mydb;User ID=test;Password=test;Trusted_Connection=true;";
SqlConnection myConn = new SqlConnection(strConn);
myConn.Open();

string strSQL = "select period from timetable_view where identity_num = '" + Identity_NUM + "' week = '3'";
SqlCommand myCommand = new SqlCommand(strSQL, myConn);
SqlDataReader myDataReader = myCommand.ExecuteReader();

int Count = Add_CourseConfirmedQuery.Count();

Random rnum = new Random();
string[] arr = new string[Count];
for (int i = 0; i < Count; i++)
{
    myDataReader.Read();
    arr[i] = myDataReader["period"].ToString();
}
ViewBag.test = arr[0];
ViewBag.test2 = arr[1];

myCommand.Cancel();
myDataReader.Close();
myConn.Close();
myConn.Dispose();

string[] input2 = new string[Count];

for (int i = 0; i < Count; i++)
{
    input2[i] = arr[i];
}

This code works fine.

I've tried to code in LINQ but it doesn't work.

Here's the LINQ code that I've tried, but I have no idea how to extract all records of a.identity_num, and put them into an array like the code above(SqlDataReader).

var test = from a in timetable_view
           where a.identity_num = Identity_NUM && a.week = "3"
           select a new{a.identity_num};

ViewBag.test = test.FirstOrDefault();

[UPDATE Question]

This is what I am trying now.

I can't extract the right records from my table.

But the page display: System.String[]

var Query = from m in db.members
            join d in db.departments on m.department equals d.department_id
            join s in db.select_list on m.member_id equals s.member_id
            join c in db.courses on s.kkk_id equals c.kkk_id
            join t in db.teachers on c.teacher equals t.teacher_id
            where m.identity_num == Identity_NUM && c.week == "3"
            select c.period;
            var PA = Query.ToArray();
            int Count = Query.Count();


string[] arr = new string[Count];
            for (int i = 0; i < Count; i++)
            {
                arr[i] = PA.ToString();
            }
            ViewBag.test = arr[0];
            ViewBag.test2 = arr[1];
.............
............
...........

string[] input2 = new string[Count];
         for (int i = 0; i < Count; i++)
         {
            input2[i] = arr[i];
         }
.............
............
...........

What do you need that annonymus type for? If you don't need it, change the new { c.period } to c.period — CSharpie
– CSharpie, Commented May 20, 2013 at 10:53
There is no but, System.String[] is not an annonymus type, or ist it? — CSharpie
– CSharpie, Commented May 20, 2013 at 11:16

Bob Vale · Accepted Answer · 2013-05-20 11:51:36Z

3

Updated to match changed question:

Don't select a new anonymous object, just select the value:

var arr= (from a in timetable_view
           where a.identity_num = Identity_NUM && a.week = "3"
           select a.period).ToArray();

Fully updated change should be

var Query = from m in db.members
            join d in db.departments on m.department equals d.department_id
            join s in db.select_list on m.member_id equals s.member_id
            join c in db.courses on s.kkk_id equals c.kkk_id
            join t in db.teachers on c.teacher equals t.teacher_id
            where m.identity_num == Identity_NUM && c.week == "3"
            select c.period;


var arr = Query.ToArray();    

ViewBag.test = arr[0];
ViewBag.test2 = arr[1];

From the comments:

The error message generated by having ToString() in the code indicates to us that period is a string anyhow and so the call can be removed.
To get count, int Count = arr.Length
The for-loop has been removed because it is redundant, using the ToArray() function achieves the same result.

edited May 20, 2013 at 11:51

answered May 20, 2013 at 9:45

Bob Vale

18.5k1 gold badge44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2401077 Over a year ago

I've updated my question. But the page display: <>f__AnonymousType15`1[System.String][]

Bob Vale Over a year ago

@user2401077 Check the updated answer. Also if you are only going to use the first two values you might want to use var arr = Query.Take(2).ToArray()

user2401077 Over a year ago

Why do you delete the for loop?

user2401077 Over a year ago

And also I still need int Count = Query.Count();

CSharpie Over a year ago

Then do int Count = arr.Length; Your for-loop became obsolete. All you need is the variable 'arr'. Its an array of strings. More precise arr is System.String[].

|

Jignesh.Raj · Accepted Answer · 2013-05-20 11:05:57Z

0

you should need to convert linkq to toArray:

var TEST = (from a in timetable_view
           where a.identity_num = Identity_NUM && a.week = "3"
           select a new{a.identity_num}).ToArray();

var Query = from m in db.members
            join d in db.departments on m.department equals d.department_id
            join s in db.select_list on m.member_id equals s.member_id
            join c in db.courses on s.kkk_id equals c.kkk_id
            join t in db.teachers on c.teacher equals t.teacher_id
            where m.identity_num == Identity_NUM && c.week == "3"
            select c.period.ToString();


var arr = Query.ToArray();    

ViewBag.test = arr[0];
ViewBag.test2 = arr[1];

edited May 20, 2013 at 11:05

answered May 20, 2013 at 9:37

Jignesh.Raj

6,0074 gold badges30 silver badges57 bronze badges

2 Comments

user2401077 Over a year ago

I've updated my question. But the page display: <>f__AnonymousType15`1[System.String][]

user2401077 Over a year ago

Why do you delete the for loop? And also I still need int Count = Query.Count();

Piotr Stapp · Accepted Answer · 2013-05-20 11:06:29Z

0

You need to convert linq query to array. Bellow code should do this:

var test = (from a in school_timetable_view
           where a.identity_num == Identity_NUM && a.week == "3"
           select a new{a.identity_num}).ToArray();

edited May 20, 2013 at 11:06

answered May 20, 2013 at 9:31

Piotr Stapp

19.9k11 gold badges72 silver badges118 bronze badges

3 Comments

Bob Vale Over a year ago

That creates an anonymous type rather that selecting the value like the reader code does. Also the reader code is grabbing period not identity_num

user2401077 Over a year ago

I've updated my question. But the page display: <>f__AnonymousType15`1[System.String][]

Moo-Juice Over a year ago

Those = need to be ==, also.

Collectives™ on Stack Overflow

How to convert SqlDataReader to LINQ way?

3 Answers 3

8 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related