Skip to content

Commit cf17ffc

Browse files
SmartwareSmartware
committed
[Prolfik] Writing ADO.NET with SQLDataReader
1 parent 9493872 commit cf17ffc

7 files changed

Lines changed: 162 additions & 3 deletions

File tree

‎LearningCSharp.Cmd/LearningCSharp.Cmd.csproj‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,9 +32,12 @@
3232
<WarningLevel>4</WarningLevel>
3333
</PropertyGroup>
3434
<PropertyGroup>
35-
<StartupObject>LearningCSharp.Cmd.Series4.LINQPlus.Tester1</StartupObject>
35+
<StartupObject>LearningCSharp.Cmd.Series5.Tester</StartupObject>
3636
</PropertyGroup>
3737
<ItemGroup>
38+
<Reference Include="Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed, processorArchitecture=MSIL">
39+
<HintPath>..\packages\Newtonsoft.Json.11.0.1\lib\net45\Newtonsoft.Json.dll</HintPath>
40+
</Reference>
3841
<Reference Include="System" />
3942
<Reference Include="System.Core" />
4043
<Reference Include="System.Drawing" />
@@ -97,10 +100,12 @@
97100
<Compile Include="Series4\LINQPlus\Tester1.cs" />
98101
<Compile Include="Series4\LINQ\Program.cs" />
99102
<Compile Include="Series4\LINQ\Tester.cs" />
103+
<Compile Include="Series5\Tester.cs" />
100104
<Compile Include="Types\Tester.cs" />
101105
</ItemGroup>
102106
<ItemGroup>
103107
<None Include="App.config" />
108+
<None Include="packages.config" />
104109
</ItemGroup>
105110
<ItemGroup>
106111
<ProjectReference Include="..\LearningCSharp.Topics\LearningCSharp.Topics.csproj">

‎LearningCSharp.Cmd/Series4/Excercise/InventoryManager.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,7 @@ public static IEnumerable<Inventory> FreshInventories()
3333
}
3434
public static IEnumerable<Inventory> GetInventories()
3535
{
36-
List<Inventory> inventories = new List<Inventory>()
36+
Inventory[] inventories = new Inventory[3]
3737
{
3838

3939
new Inventory()
@@ -58,7 +58,7 @@ public static IEnumerable<Inventory> GetInventories()
5858
}
5959
};
6060

61-
return inventories;
61+
return inventories.AsEnumerable();
6262

6363
}
6464

‎LearningCSharp.Cmd/Series5/Tester.cs‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
using LearningCSharp.Topics.EF.Sample;
2+
using Newtonsoft.Json;
3+
using System;
4+
using System.Collections.Generic;
5+
using System.Linq;
6+
using System.Text;
7+
using System.Threading.Tasks;
8+
9+
namespace LearningCSharp.Cmd.Series5
10+
{
11+
public class Tester
12+
{
13+
public static void Main()
14+
{
15+
16+
var itemList = ProductManager.GetProductList("water");
17+
18+
foreach (var item in itemList)
19+
{
20+
Console.WriteLine($"Id: {item.Id}, Name: {item.Name}, Price: {item.Price}");
21+
}
22+
23+
Console.Read();
24+
var personObj = new Person()
25+
{
26+
LastName = "Aremu",
27+
FirstName = "Lollipop",
28+
Addresses = new List<Address>()
29+
{
30+
new Address()
31+
{
32+
City = "VI",
33+
Street = null
34+
},
35+
new Address()
36+
{
37+
City = "Awoyaya",
38+
Street = "Convenant Drive"
39+
}
40+
41+
}
42+
};
43+
44+
var serializedJson = JsonConvert.SerializeObject(personObj);
45+
46+
Console.WriteLine(serializedJson);
47+
48+
Console.Read();
49+
50+
var person = @"{
51+
'LastName': 'Prolifik',
52+
'FirstName': 'Lexzy',
53+
'Addresses': [
54+
{'City': 'VI', 'Street': ''},
55+
{'City': 'Awoyaya', 'Street': 'Convenant Drive'}]
56+
}";
57+
58+
var objPerson = JsonConvert.DeserializeObject<Person>(person);
59+
60+
Console.WriteLine($"LastName: {objPerson?.LastName} FirstName: {objPerson?.FirstName}");
61+
62+
foreach (var item in objPerson.Addresses)
63+
{
64+
Console.WriteLine($"City: {item.City} Street: {item.Street ?? "NA"}");
65+
}
66+
67+
Console.Read();
68+
}
69+
}
70+
71+
public class Person
72+
{
73+
public string LastName { get; set; }
74+
public string FirstName { get; set; }
75+
public List<Address> Addresses { get; set; }
76+
}
77+
78+
public class Address
79+
{
80+
public string City { get; set; }
81+
public string Street { get; set; }
82+
}
83+
}

‎LearningCSharp.Cmd/packages.config‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
<?xml version="1.0" encoding="utf-8"?>
2+
<packages>
3+
<package id="Newtonsoft.Json" version="11.0.1" targetFramework="net461" />
4+
</packages>

‎LearningCSharp.Topics/EF/Sample/ProductManager.cs‎

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Data.SqlClient;
4+
using System.Linq;
5+
using System.Text;
6+
using System.Threading.Tasks;
7+
8+
namespace LearningCSharp.Topics.EF.Sample
9+
{
10+
public class ProductManager
11+
{
12+
public static List<Item> GetProductList(string filter = null)
13+
{
14+
var sqlConString = "Server=.\\SQLExpress;Integrated Security=false; user id=sa; password=micr0s0ft_;Initial Catalog=Iposv3";
15+
var sqlCon = new SqlConnection(sqlConString);
16+
var cmdText = $"SELECT TOP 10 * FROM Product p where p.Name like '%{filter}%'";
17+
var sqlCmd = new SqlCommand(cmdText, sqlCon);
18+
19+
List<Item> itemList = new List<Item>();
20+
21+
try
22+
{
23+
sqlCon.Open();
24+
SqlDataReader reader = sqlCmd.ExecuteReader();
25+
26+
while (reader.Read())
27+
{
28+
var prod = new Item()
29+
{
30+
Name = reader.GetString(2),
31+
Id = reader.GetGuid(1),
32+
Price = reader.GetDecimal(4),
33+
};
34+
35+
itemList.Add(prod);
36+
}
37+
}
38+
catch (Exception ex)
39+
{
40+
}
41+
finally
42+
{
43+
sqlCon.Close();
44+
}
45+
46+
return itemList;
47+
}
48+
}
49+
50+
public class Item
51+
{
52+
public Guid Id { get; set; }
53+
public string Name { get; set; }
54+
public decimal Price { get; set; }
55+
}
56+
}

‎LearningCSharp.Topics/LearningCSharp.Topics.csproj‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,9 @@
3030
<WarningLevel>4</WarningLevel>
3131
</PropertyGroup>
3232
<ItemGroup>
33+
<Reference Include="Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed, processorArchitecture=MSIL">
34+
<HintPath>..\packages\Newtonsoft.Json.11.0.1\lib\net45\Newtonsoft.Json.dll</HintPath>
35+
</Reference>
3336
<Reference Include="System" />
3437
<Reference Include="System.Core" />
3538
<Reference Include="System.Xml.Linq" />
@@ -43,11 +46,15 @@
4346
<Compile Include="Delegates\Shape.cs" />
4447
<Compile Include="Domains\Category.cs" />
4548
<Compile Include="Domains\Product.cs" />
49+
<Compile Include="EF\Sample\ProductManager.cs" />
4650
<Compile Include="Enumeration\People.cs" />
4751
<Compile Include="LINQ\CollectionManager.cs" />
4852
<Compile Include="Properties\AssemblyInfo.cs" />
4953
<Compile Include="TryException\MyCustomEx.cs" />
5054
</ItemGroup>
55+
<ItemGroup>
56+
<None Include="packages.config" />
57+
</ItemGroup>
5158
<ItemGroup />
5259
<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
5360
</Project>

‎LearningCSharp.Topics/packages.config‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
<?xml version="1.0" encoding="utf-8"?>
2+
<packages>
3+
<package id="Newtonsoft.Json" version="11.0.1" targetFramework="net461" />
4+
</packages>

0 commit comments

Comments
 (0)