Insert Null Values into a Sqlite Table

titojd

Well-known member
Joined
Oct 12, 2018
Messages
63
Programming Experience
1-3
Hello Colleagues, I have a cruel doubt tormenting me,
I need to include 15 numbers from a jSon list in a table with 25 fields.
Example of the jSon File where 15 Numbers from 0 to 25 are drawn
{
"tenslist": [
"01", "03", "04", "08", "10", "11", "13", "14", "17", "18", "19", "21", "22 ","23", "25"
],
}
I have a table with 25 fields, I need to get each number in its appropriate field, and then fill in a DataGrid
Example Image:
teste.png


I can insert normally into a table with 15 fields but not with 25,
Could someone give me a light?
I'll be very thankfull.
 
Solution
An alternative is to create a class which represents your data and using Dapper NuGet package.

Example where Id is auto-incrementing, the remaining columns are nullable.


Table structure:
CREATE TABLE InsertExample (
    Id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Column1 INTEGER,
    Column2 INTEGER,
    Column3 INTEGER,
    Column4 TEXT,
    Column5 TEXT
);

Class to represent the table above

Model for table:
public class InsertExample
{
    public int Id { get; set; }
    public int? Column1 { get; set; }
    public int? Column2 { get; set; }
    public int? Column3 { get; set; }
    public string? Column4 { get; set; }
    public string? Column5 { get; set; }
}

Mockup code to add records

Insert operations:
using System.Data.SQLite;
using Dapper;
internal...
I can insert normally into a table with 15 fields but not with 25,

Show us your code for successful insertion into SQLite with 15 fields. Then show us your code for unsuccessful insertion into SQLite with 25 fields.
 
Connection to the Database:
C#:
 public static string path = Directory.GetCurrentDirectory() + "\\ResultadoDB.sqlite";
        private static SQLiteConnection sqliteConnection;

        private static SQLiteConnection DbConnection()
        {
            sqliteConnection = new SQLiteConnection("Data Source=" + path);
            sqliteConnection.Open();
            return sqliteConnection;
        }

Insertion with 15 fields
C#:
 public static string Add(Resultado number)
        {
            string Saida = "";
            using (var connection = DbConnection())
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "insert into TBResultado(Concurso,_01,_02,_03,_04,_05,_06,_07,_08,_09,_10,_11,_12,_13,_14,_15)" +
         " Values(@Concurso,@_01,@_02,@_03,@_04,@_05,@_06,@_07,@_08,@_09,@_10,@_11,@_12,@_13,@_14,@_15)";

                    cmd.Parameters.AddWithValue("@Concurso", number.Concurso);
                    cmd.Parameters.AddWithValue("@_01", number._01.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_02", number._02.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_03", number._03.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_04", number._04.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_05", number._05.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_06", number._06.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_07", number._07.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_08", number._08.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_09", number._09.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_10", number._10.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_11", number._11.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_12", number._12.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_13", number._13.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_14", number._14.ToString("D2"));
                    cmd.Parameters.AddWithValue("@_15", number._15.ToString("D2"));

                    if (VerificarExistencia(number.Concurso) == false)
                    {
                        try
                        {
                            number.Concurso = Convert.ToInt32(cmd.ExecuteNonQuery());
                            Saida = "Resultado registrado com sucesso!";
                        }
                        catch (Exception exc)
                        {
                            Saida = "Ocorreu um erro inesperado: " + exc.Message;
                        }
                        return Saida;
                    }
                    else
                    {
                        //If the Result is already registered, this code will return and nothing will be saved.
                        Saida = "Resultado já cadastrado.";
                        return Saida;
                    }
                }
            }
        }

Insertion with 25 fields

C#:
public static string AddTeste(ResultadoTeste number) { string Saida = ""; using (var connection = DbConnection()) { using (var cmd = connection.CreateCommand()) { cmd.CommandText = "insert into TBTeste(Concurso,n01,n02,n03,n04,n05,n06,n07,n08,n09,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,n23,n24,n25)" + " Values(@Concurso,@n01,@n02,@n03,@n04,@n05,@n06,@n07,@n08,@n09,@n10,@n11,@n12,@n13,@n14,@n15,@n16,@n17,@n18,@n19,@n20,@n21,@n22,@n23,@n24,@n25)"; cmd.Parameters.AddWithValue("@Concurso", number.Concurso); cmd.Parameters.AddWithValue("@n01", number.n01.ToString("D2")); cmd.Parameters.AddWithValue("@n02", number.n02.ToString("D2")); cmd.Parameters.AddWithValue("@n03", number.n03.ToString("D2")); cmd.Parameters.AddWithValue("@n04", number.n04.ToString("D2")); cmd.Parameters.AddWithValue("@n05", number.n05.ToString("D2")); cmd.Parameters.AddWithValue("@n06", number.n06.ToString("D2")); cmd.Parameters.AddWithValue("@n07", number.n07.ToString("D2")); cmd.Parameters.AddWithValue("@n08", number.n08.ToString("D2")); cmd.Parameters.AddWithValue("@n09", number.n09.ToString("D2")); cmd.Parameters.AddWithValue("@n10", number.n10.ToString("D2")); cmd.Parameters.AddWithValue("@n11", number.n11.ToString("D2")); cmd.Parameters.AddWithValue("@n12", number.n12.ToString("D2")); cmd.Parameters.AddWithValue("@n13", number.n13.ToString("D2")); cmd.Parameters.AddWithValue("@n14", number.n14.ToString("D2")); cmd.Parameters.AddWithValue("@n15", number.n15.ToString("D2")); cmd.Parameters.AddWithValue("@n16", number.n16.ToString("D2")); cmd.Parameters.AddWithValue("@n17", number.n17.ToString("D2")); cmd.Parameters.AddWithValue("@n18", number.n18.ToString("D2")); cmd.Parameters.AddWithValue("@n19", number.n19.ToString("D2")); cmd.Parameters.AddWithValue("@n20", number.n20.ToString("D2")); cmd.Parameters.AddWithValue("@n21", number.n21.ToString("D2")); cmd.Parameters.AddWithValue("@n22", number.n22.ToString("D2")); cmd.Parameters.AddWithValue("@n23", number.n23.ToString("D2")); cmd.Parameters.AddWithValue("@n24", number.n24.ToString("D2")); cmd.Parameters.AddWithValue("@n25", number.n25.ToString("D2")); if (VerificarExistencia(number.Concurso) == false) { try { number.Concurso = Convert.ToInt32(cmd.ExecuteNonQuery()); Saida = "Resultado Teste registrado com sucesso!"; } catch (Exception exc) { Saida = "Ocorreu um erro inesperado: " + exc.Message; } return Saida; } else { //If the Result is already registered, this code will return and nothing will be saved. Saida = "Resultado Teste já cadastrado."; return Saida; } } } }"] public static string AddTeste(ResultadoTeste number)
        {
            string Saida = "";
            using (var connection = DbConnection())
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "insert into TBTeste(Concurso,n01,n02,n03,n04,n05,n06,n07,n08,n09,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,n23,n24,n25)" +
         " Values(@Concurso,@n01,@n02,@n03,@n04,@n05,@n06,@n07,@n08,@n09,@n10,@n11,@n12,@n13,@n14,@n15,@n16,@n17,@n18,@n19,@n20,@n21,@n22,@n23,@n24,@n25)";

                    cmd.Parameters.AddWithValue("@Concurso", number.Concurso);
                    cmd.Parameters.AddWithValue("@n01", number.n01.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n02", number.n02.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n03", number.n03.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n04", number.n04.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n05", number.n05.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n06", number.n06.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n07", number.n07.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n08", number.n08.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n09", number.n09.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n10", number.n10.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n11", number.n11.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n12", number.n12.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n13", number.n13.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n14", number.n14.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n15", number.n15.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n16", number.n16.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n17", number.n17.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n18", number.n18.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n19", number.n19.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n20", number.n20.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n21", number.n21.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n22", number.n22.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n23", number.n23.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n24", number.n24.ToString("D2"));
                    cmd.Parameters.AddWithValue("@n25", number.n25.ToString("D2"));

                    if (VerificarExistencia(number.Concurso) == false)
                    {
                        try
                        {
                            number.Concurso = Convert.ToInt32(cmd.ExecuteNonQuery());
                            Saida = "Resultado Teste registrado com sucesso!";
                        }
                        catch (Exception exc)
                        {
                            Saida = "Ocorreu um erro inesperado: " + exc.Message;
                        }
                        return Saida;
                    }
                    else
                    {
                        //If the Result is already registered, this code will return and nothing will be saved.
                        Saida = "Resultado Teste já cadastrado.";
                        return Saida;
                    }
                }
            }
        }

C#:
 private ResultadoTeste PreencherInformacoesTeste()
        {
            ResultadoTeste SorteioBase = new ResultadoTeste();

            SorteioBase.Concurso = Convert.ToInt32(txtConcurso.Text);
            SorteioBase.n01 = Convert.ToInt32(ResultadoAtual[0].Text);
            SorteioBase.n02 = Convert.ToInt32(ResultadoAtual[1].Text);
            SorteioBase.n03 = Convert.ToInt32(ResultadoAtual[2].Text);
            SorteioBase.n04 = Convert.ToInt32(ResultadoAtual[3].Text);
            SorteioBase.n05 = Convert.ToInt32(ResultadoAtual[4].Text);
            SorteioBase.n06 = Convert.ToInt32(ResultadoAtual[5].Text);
            SorteioBase.n07 = Convert.ToInt32(ResultadoAtual[6].Text);
            SorteioBase.n08 = Convert.ToInt32(ResultadoAtual[7].Text);
            SorteioBase.n09 = Convert.ToInt32(ResultadoAtual[8].Text);
            SorteioBase.n10 = Convert.ToInt32(ResultadoAtual[9].Text);
            SorteioBase.n11 = Convert.ToInt32(ResultadoAtual[10].Text);
            SorteioBase.n12 = Convert.ToInt32(ResultadoAtual[11].Text);
            SorteioBase.n13 = Convert.ToInt32(ResultadoAtual[12].Text);
            SorteioBase.n14 = Convert.ToInt32(ResultadoAtual[13].Text);
            SorteioBase.n15 = Convert.ToInt32(ResultadoAtual[14].Text);
            SorteioBase.n16 = Convert.ToInt32(ResultadoAtual[15].Text);
            SorteioBase.n17 = Convert.ToInt32(ResultadoAtual[17].Text);
            SorteioBase.n18 = Convert.ToInt32(ResultadoAtual[18].Text);
            SorteioBase.n19 = Convert.ToInt32(ResultadoAtual[19].Text);
            SorteioBase.n20 = Convert.ToInt32(ResultadoAtual[20].Text);
            SorteioBase.n21 = Convert.ToInt32(ResultadoAtual[21].Text);
            SorteioBase.n22 = Convert.ToInt32(ResultadoAtual[22].Text);
            SorteioBase.n23 = Convert.ToInt32(ResultadoAtual[23].Text);
            SorteioBase.n24 = Convert.ToInt32(ResultadoAtual[24].Text);
            SorteioBase.n25 = Convert.ToInt32(ResultadoAtual[25].Text);

            return SorteioBase;
        }

C#:
 private void salvar()
        {
        
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                string Saida = Logica.AddTeste(PreencherInformacoesTeste());
                MessageBox.Show(Saida, "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Um novo Resultado Foi salvo no Banco!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
C#:
private void btnSalvarConcurso_Click(object sender, EventArgs e)
        {
            salvar();
        }
Both are practically the same, I just increased the fields in the code and created a table with 25 fields (TBTest)
 
Last edited by a moderator:
@Skydiver Thank you very much for helping me many times.
but this time what I need to do is create a condition that,
that saves each number in its field.
as there are only 15 random numbers out of a total of 0 to 25 numbers,
10 numbers would be blank in the table.
Here comes my problem, how to put 15 numbers in a table that contains 25 fields?
How to leave 10 (random) fields in the table blank?
the DB table and the GridView would look like the image below...

(just to explain the question better) A hug.
 
When you are copying and pasting lines of code, that is typically a code smell that tells you you should look at using loops and/or helper functions.

You can use a loop to dynamically build your insert statement.

Or since you are using string instead of numbers for your columns, you could use a helper function to return the number as a string or as an empty string.
 
I've already warmed up my brains thinking about how to build this, but I haven't been successful, I'll do some more research.
Thank you very much for responding.
a hug.
 
Firstly, stop using AddWithValue. It infers the parameter data type from the value. Apart from the fact that it will sometimes infer the wrong data type, it can't infer any data type from a NULL value. Use Add instead and specify the data type explicitly.

Secondly, if you want use a database NULL with ADO.NET then you use DBNull.Value. That's been the way since the inception of .NET and a little bit of research would have told you that. How you determine that you need to use a NULL value is up to you and will depend on the situation. Here's an example that uses data from TextBoxes and uses NULL if the TextBox is empty:
C#:
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("INSERT INTO Table1 (Name, Number) VALUES (@Name, @Number)", connection))
{
    command.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = (nameTextBox.TextLength == 0 ? (object)DBNull.Value : nameTextBox.Text);
    command.Parameters.Add("@Number", SqlDbType.Int).Value = (numberTextBox.TextLength == 0 ? (object)DBNull.Value : Convert.ToInt32(numberTextBox.Text));
   
    connection.Open();
    command.ExecuteNonQuery();
}
That example uses the SqlClient provider for SQL Server but the same principles applies to any ADO.NET provider.
 
@titojd : Consider the following pseudo code:
C#:
var columnNames = new List<string>();
var parameterNames = new List<string>();
for(int columnNumber = 1; columnNumber <= 25; columnNumber++)
{
    columnName = $"n{columnNumber:D2}"
    parameterName = $"@{columName}";
    valueString = txtField[columnNumber].Text;

    columnNames.Add(columnName);
    parameterNames.Add(parameterName);

    if (int.TryParse(valueString, out int value)
        command.Parameters.Add(parameterName, value);
    else
        command.Parameters.Add(parameterName, DBNull.Value);
}

allColumnNames = string.Join(", ", columnNames);
allParameterNames = string.Join(", ", parameterNames);
command.CommandText = $"INSERT INTO Results ({allColumnNames}) VALUES ({allParameterNames})";
 
@Skydiver, I'm very grateful for taking your board to help me.

Unfortunately I couldn't change your code,
I'm trying to understand...

I thought about trying to get the 15 numbers from the Json file and load them into 25 Labels respecting the nulls, after that it would be easy...
 
It's not a matter of changing my code to be dropped in for your use. It's a matter of looking at what operations are being done and understanding.

At runtime for each field for the game, the names of the columns are computed. Given the of the column, a name for a parameter is computed. These are added to lists of columns and parameter names. Next an attempt is made to convert the text from the UI into an integer. If something is parsed, then that parameter is set to the parsed value, otherwise the parameter is set to a null database value.
After each of the fields are examined, the SQL insert command is created with the list of column names comma separated, and the list of parameter names also comma separated.

If you just want to copy and paste code, have ChatGPT just write the code for you.
 
@Skydiver, my Brother, I don't want a ready-made code, I just want to understand, I'm not going to use AI,
I want to learn, I'm just an amateur thinking out loud maybe, :)
I managed to do it with an if() condition, it was long and ugly, but it's working, until I find another better way I'll use it like this...
or how much I understand your code.
Thank you for your help, a hug.

C#:
 public void addNulls()
        {         
            if (ResultadoJson[0].Text == "01")
            {
                UltimoResultado[0].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "02")
            {
                UltimoResultado[1].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "03")
            {
                UltimoResultado[2].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "04")
            {
                UltimoResultado[3].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "05")
            {
                UltimoResultado[4].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "06")
            {
                UltimoResultado[5].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "07")
            {
                UltimoResultado[6].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "08")
            {
                UltimoResultado[7].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "09")
            {
                UltimoResultado[8].Text = ResultadoJson[0].Text;
            }
            else if (ResultadoJson[0].Text == "10")
            {
                UltimoResultado[9].Text = ResultadoJson[0].Text;
            }

                if (ResultadoJson[1].Text == "02")
                {
                    UltimoResultado[1].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "03")
                {
                    UltimoResultado[2].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "04")
                {
                    UltimoResultado[3].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "05")
                {
                    UltimoResultado[4].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "06")
                {
                    UltimoResultado[5].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "07")
                {
                    UltimoResultado[6].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "08")
                {
                    UltimoResultado[7].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "09")
                {
                    UltimoResultado[8].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "10")
                {
                    UltimoResultado[9].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "11")
                {
                    UltimoResultado[10].Text = ResultadoJson[1].Text;
                }
                else if (ResultadoJson[1].Text == "12")
                {
                    UltimoResultado[11].Text = ResultadoJson[1].Text;
                }
  
    // ......
      
        
 
                        if (ResultadoJson[13].Text == "14")
                        {
                            UltimoResultado[13].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "15")
                        {
                            UltimoResultado[14].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "16")
                        {
                            UltimoResultado[15].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "17")
                        {
                            UltimoResultado[16].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "18")
                        {
                            UltimoResultado[17].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "19")
                        {
                            UltimoResultado[18].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "20")
                        {
                            UltimoResultado[19].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "21")
                        {
                            UltimoResultado[20].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "22")
                        {
                            UltimoResultado[21].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "23")
                        {
                            UltimoResultado[22].Text = ResultadoJson[13].Text;
                        }
                        else if (ResultadoJson[13].Text == "24")
                        {
                            UltimoResultado[23].Text = ResultadoJson[13].Text;
                        }
                           
                            if (ResultadoJson[14].Text == "15")
                            {
                                UltimoResultado[14].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "16")
                            {
                                UltimoResultado[15].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "17")
                            {
                                UltimoResultado[16].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "18")
                            {
                                UltimoResultado[17].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "19")
                            {
                                UltimoResultado[18].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "20")
                            {
                                UltimoResultado[19].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "21")
                            {
                                UltimoResultado[20].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "22")
                            {
                                UltimoResultado[21].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "23")
                            {
                                UltimoResultado[22].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "24")
                            {
                                UltimoResultado[23].Text = ResultadoJson[14].Text;
                            }
                            else if (ResultadoJson[14].Text == "25")
                            {
                                UltimoResultado[24].Text = ResultadoJson[14].Text;
                            }
          }
     }
 
An alternative is to create a class which represents your data and using Dapper NuGet package.

Example where Id is auto-incrementing, the remaining columns are nullable.


Table structure:
CREATE TABLE InsertExample (
    Id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Column1 INTEGER,
    Column2 INTEGER,
    Column3 INTEGER,
    Column4 TEXT,
    Column5 TEXT
);

Class to represent the table above

Model for table:
public class InsertExample
{
    public int Id { get; set; }
    public int? Column1 { get; set; }
    public int? Column2 { get; set; }
    public int? Column3 { get; set; }
    public string? Column4 { get; set; }
    public string? Column5 { get; set; }
}

Mockup code to add records

Insert operations:
using System.Data.SQLite;
using Dapper;
internal class DapperOperations
{
    private static string ConnectionString()
        => "Data Source=sample1.db";
    public static int InsertData(List<InsertExample> list)
    {
        using var cn = new SQLiteConnection(ConnectionString());
        return cn.Execute(
           """
            INSERT INTO InsertExample (Column1,Column2,Column3,Column4,Column5)
            VALUES (@Column1,@Column2,@Column3,@Column4,@Column5);
            """, list);
    }
}

Mocked data with nulls

Mocked data:
public class MockedData
{
    public static List<InsertExample> GetMockedData() =>
    [
        new() { Column1 = null, Column2 = 2, Column3 = 3, Column4 = "A", Column5 = null },
        new() { Column1 = 4, Column2 = null, Column3 = 6, Column4 = null, Column5 = "D" },
        new() { Column1 = 7, Column2 = 8, Column3 = 9, Column4 = "E", Column5 = "F" },
        new() { Column1 = 28, Column2 = 29, Column3 = 30, Column4 = "S", Column5 = "T" }
    ];
}

Code to actually insert rows.

Run code:
var count = DapperOperations.InsertData(MockedData.GetMockedData());

figure1.png
 
Solution

Latest posts

Back
Top Bottom