public List <OneToTest> Select()
{
#region normalSelect
List <OneToTest> oneToTests = new List <OneToTest>();
var lookup = new Dictionary <int, OneToTest>();
using (SqlConnection connection = new SqlConnection(options.connectionString))
{
using (SqlCommand command = new SqlCommand("dbo.selectManyProcedure", connection))
{
try
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int oneTestId = (int)reader["Id"];
OneToTest oneTest;
if (!lookup.TryGetValue(oneTestId, out oneTest))
{
oneTest = new OneToTest();
oneTest.Id = oneTestId;
oneTest.Name = reader["Name"] as string;
oneTest.Age = (int)reader["Age"];
lookup.Add(oneTestId, oneTest);
oneToTests.Add(oneTest);
}
UserTest userTest = new UserTest();
userTest.Id = (int)reader["UserTestId"];
userTest.FirstName = reader["FirstName"] as string;
userTest.LastName = reader["LastName"] as string;
userTest.Address = reader["Address"] as string;
userTest.FkOneToTestId = (int)reader["FkOneToTestId"];
oneTest.UserTests.Add(userTest);
}
return(oneToTests);
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
#endregion
}
public async Task <List <OneToTest> > SelectAsync()
{
string query = @"SELECT
OneToTest.Id,
OneToTest.Name,
OneToTest.Age,
UserTest.Id AS UserId,
UserTest.FirstName,
UserTest.LastName,
UserTest.Address,
UserTest.FkOneToTestId
FROM OneToTest
INNER JOIN UserTest
ON OneToTest.Id = UserTest.FkOneToTestId";
List <OneToTest> oneToTests = new List <OneToTest>();
var lookup = new Dictionary <int, OneToTest>();
using (SqlConnection connection = new SqlConnection(options.connectionString))
{
SqlCommand command = new SqlCommand(query, connection);
try
{
await connection.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
int oneTestId = (int)reader["Id"];
OneToTest oneTest;
if (!lookup.TryGetValue(oneTestId, out oneTest))
{
oneTest = new OneToTest();
oneTest.Id = oneTestId;
oneTest.Name = reader["Name"] as string;
oneTest.Age = (int)reader["Age"];
lookup.Add(oneTestId, oneTest);
oneToTests.Add(oneTest);
}
UserTest userTest = new UserTest();
userTest.Id = (int)reader["UserId"];
userTest.FirstName = reader["FirstName"] as string;
userTest.LastName = reader["LastName"] as string;
userTest.Address = reader["Address"] as string;
userTest.FkOneToTestId = (int)reader["FkOneToTestId"];
oneTest.UserTests.Add(userTest);
}
return(oneToTests);
}
}
catch (Exception ex)
{
throw new Exception(nameof(ex));
}
}
}
public List <OneToTest> Select()
{
string query = @"SELECT
OneToTest.Id,
OneToTest.Name,
OneToTest.Age,
UserTest.Id AS UserId,
UserTest.FirstName,
UserTest.LastName,
UserTest.Address,
UserTest.FkOneToTestId
FROM OneToTest
INNER JOIN UserTest
ON OneToTest.Id = UserTest.FkOneToTestId";
List <OneToTest> oneToTests = new List <OneToTest>();
var lookup = new Dictionary <int, OneToTest>();
using (SqlConnection connection = new SqlConnection(options.connectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int oneTestId = (int)reader["Id"];
OneToTest oneTest;
if (!lookup.TryGetValue(oneTestId, out oneTest))
{
oneTest = new OneToTest();
oneTest.Id = oneTestId;
oneTest.Name = reader["Name"] as string;
oneTest.Age = (int)reader["Age"];
lookup.Add(oneTestId, oneTest);
oneToTests.Add(oneTest);
}
UserTest userTest = new UserTest();
userTest.Id = (int)reader["UserId"];
userTest.FirstName = reader["FirstName"] as string;
userTest.LastName = reader["LastName"] as string;
userTest.Address = reader["Address"] as string;
userTest.FkOneToTestId = (int)reader["FkOneToTestId"];
oneTest.UserTests.Add(userTest);
}
return(oneToTests);
}
}
catch (Exception ex)
{
throw new Exception(nameof(ex));
}
}
}
#region normalSelect
//string query = "SELECT * FROM UserTest";
//List<UserTest> userTests = new List<UserTest>();
//using (SqlConnection connection = new SqlConnection(options.connectionString))
//{
// SqlCommand command = new SqlCommand(query, connection);
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// UserTest userTest = new UserTest();
// userTest.Id = (int)reader["Id"];
// userTest.FirstName = reader["FirstName"] as string;
// userTest.LastName = reader["LastName"] as string;
// userTest.Address = reader["Address"] as string;
// userTest.FkOneToTestId = (int)reader["FkOneToTestId"];
// userTests.Add(userTest);
// }
// }
// catch (Exception ex)
// {
// throw new Exception(ex.ToString());
// }
//}
#endregion
#region oneToMany
//string queryOneToMany = "SELECT * FROM UserTest INNER JOIN OneToTest ON OneToTest.Id = UserTest.FkOneToTestId";
//List<UserTest> userTests = new List<UserTest>();
//using (SqlConnection connection = new SqlConnection(options.connectionString))
//{
// using (SqlCommand command = new SqlCommand(queryOneToMany, connection))
// {
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// UserTest userTest = new UserTest();
// userTest.Id = (int)reader["Id"];
// userTest.FirstName = reader["FirstName"] as string;
// userTest.LastName = reader["LastName"] as string;
// userTest.Address = reader["Address"] as string;
// userTest.FkOneToTestId = (int)reader["FkOneToTestId"];
// userTest.OneToTest = new OneToTest();
// userTest.OneToTest.Id = userTest.FkOneToTestId;
// userTest.OneToTest.Name = reader["Name"] as string;
// userTest.OneToTest.Age = (int)reader["Age"];
// userTests.Add(userTest);
// }
// }
// catch (Exception ex)
// {
// throw new Exception(ex.ToString());
// }
// }
//}
#endregion
}
