MAUI's SQL Server Error

Jassim Al Rahma 1,541 Reputation points
2023-05-05T16:00:12.47+00:00

Hi,

I have the following code to insert the contacts from Sqlite on the device to a remote SQL Server.

async void SyncDatabase()
{
    await Task.Run(async () =>
    {
        conn = new SQLiteAsyncConnection(App.SqlDB);
        var persons = await conn.Table<Persons>().ToListAsync();

        var connectionString = "Server=xxxxxx.database.windows.net;Database=xxxxx;User Id=xxxxxxx;Password=xxxxxx;";
        var query = "EXEC insert_contact @person_id, @device_id, @prefix, @display_name, @first_name, @middle_name, @last_name, @nickname, @phonetic_first_name, @phonetic_middle_name, @phonetic_last_name, @suffix, @company, @job_title, @department, @birthday, @anniversary, @notes";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var command = new SqlCommand(query, connection))
            {
                foreach (var person in persons)
                {
                    command.Parameters.Clear();

                    command.Parameters.AddWithValue("@person_id", person.Id);
                    command.Parameters.AddWithValue("@device_id", SecureStorage.GetAsync("DeviceId").Result);
                    command.Parameters.Add("@prefix", SqlDbType.NVarChar).Value = person.Prefix != null ? person.Prefix : DBNull.Value;
                    command.Parameters.Add("@display_name", SqlDbType.NVarChar).Value = person.DisplayName != null ? person.DisplayName : DBNull.Value;
                    command.Parameters.Add("@first_name", SqlDbType.NVarChar).Value = person.FirstName != null ? person.FirstName : DBNull.Value;
                    command.Parameters.Add("@middle_name", SqlDbType.NVarChar).Value = person.MiddleName != null ? person.MiddleName : DBNull.Value;
                    command.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = person.LastName != null ? person.LastName : DBNull.Value;
                    command.Parameters.Add("@nickname", SqlDbType.NVarChar).Value = person.Nickname != null ? person.Nickname : DBNull.Value;
                    command.Parameters.Add("@phonetic_first_name", SqlDbType.NVarChar).Value = person.PhoneticFirstName != null ? person.PhoneticFirstName : DBNull.Value;
                    command.Parameters.Add("@phonetic_middle_name", SqlDbType.NVarChar).Value = person.PhoneticMiddleName != null ? person.PhoneticMiddleName : DBNull.Value;
                    command.Parameters.Add("@phonetic_last_name", SqlDbType.NVarChar).Value = person.PhoneticLastName != null ? person.PhoneticLastName : DBNull.Value;
                    command.Parameters.Add("@suffix", SqlDbType.NVarChar).Value = person.Suffix != null ? person.Suffix : DBNull.Value;
                    command.Parameters.Add("@company", SqlDbType.NVarChar).Value = person.Company != null ? person.Company : DBNull.Value;
                    command.Parameters.Add("@job_title", SqlDbType.NVarChar).Value = person.JobTitle != null ? person.JobTitle : DBNull.Value;
                    command.Parameters.Add("@department", SqlDbType.NVarChar).Value = person.Department != null ? person.Department : DBNull.Value;
                    command.Parameters.Add("@birthday", SqlDbType.NVarChar).Value = person.Birthday != null ? person.Birthday : DBNull.Value;
                    command.Parameters.Add("@anniversary", SqlDbType.NVarChar).Value = person.Anniversary != null ? person.Anniversary : DBNull.Value;
                    command.Parameters.Add("@notes", SqlDbType.NVarChar).Value = person.Notes != null ? person.Notes : DBNull.Value;

                    command.ExecuteNonQuery();
                }
            }
        }
    });
}

The code is working perfectly but for few contacts and then I will get this error:

Microsoft.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed) ---> System.IO.IOException: Unable to read data from the transport connection: Operation timed out. ---> System.Net.Sockets.SocketException: Operation timed out

My SQL Server is on Microsoft Azure.

Kindly help.

Thanks.

Jassim

.NET MAUI
.NET MAUI
A Microsoft open-source framework for building native device applications spanning mobile, tablet, and desktop.
3,061 questions
{count} votes