Kuke Kuke - 1 month ago 23
C# Question

how to select data with ado.net from mssql

My mission is to get result from database which should look like this:

[
{
LicenseNumber = "xxx", //string, that can contain numbers
EMailAddress = "people@gmail.com",
PhoneNumber = "+370 600 00001",
OrganisationName = "work place"
}
]


I got the following code:

public class DataController : ApiController
{
[Authorize]
[HttpGet]
[Route("api/participants")]
public JsonResult<Array> Get(string participantCode)
{
JsonResult<Array> result = null;
string connectionString = "Server=db1.lagoon.lt;Database=LagoonOcean_LT;Integrated Security=True";
string queryString =
"SELECT p.LicenseNumber, p.EMailAddress, p.PhoneNumber, w.OrganisationName" +
"FROM [LagoonOcean_LT].[dbo].[Person] p.inner join LagoonOcean_LT.dbo.PersonMainWorkplaceVW w on w.PersonUUID = p.PersonUUID" +
"WHERE LicenseNumber == participantCode;";
int paramValue = 5;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@LicenseNumber", paramValue);


var participants = new List<object>();
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
participants.Add(new[]{new
{
LicenseNumber = reader[0],
EMailAddress = reader[1],
PhoneNumber = reader[2],
OrganisationName = reader[3]
}
});
}
reader.Close();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
result = Json<Array>(participants.ToArray());
}
return result;
}
}


but i get an error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in DoctorInfoWebService.dll but was not handled in user code
Additional information: Incorrect syntax near '.'.


I think there is something wrong with selecting data or connection string

Answer
FROM [LagoonOcean_LT].[dbo].[Person] p.inner

What are you trying to do here ? Remove the . after p. Also the default schema is dbo it is not needed to be written.