How to Read/Write to SQL database from console application (C#)

Question

I am new to SQL server.

As you can see my first function above is simply used to call the second function and pass a list as a parameter.
Now I am parsing the info from the dividedTrans char array. Each value means something and this I am displaying to the console. However, I need to save it to an SQL database. The parsed values. I have some experience in SQLite, however this is a bit different. I googled things up and this is what I could come up with. However, I found no simple way to update columns. My column names are the same as what is mentioned in the console.writeline code, for example some of the columns are "data record" , "record_id", "article_code" etc. So I wish to save to column "data record" the value of "2.1 Movement record" for instance and then in "record_id" the value of dividedTrans[0]. Could someone please help me with an efficient way of doing this?

class save_parsed_data
{
    static string connectionstring;
    static SqlConnection connection; 

    public static void save_data(string transaction)
    {
        connectionstring = ConfigurationManager.
               ConnectionStrings["testEnvironmentSAMPLE.Properties.Settings.sampleConnectionString"].ConnectionString;

            List<char> dividedTrans = new List<char>(transaction.ToCharArray());

            if (transaction.StartsWith("21"))
                StringWith21(dividedTrans);

            Console.ReadKey();
    }

    public static void StringWith21(List<char> dividedTrans)
    {
        using (connection = new SqlConnection(connectionstring))
        using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Sample_values", connection))
        {   
            DataTable codaTable = new DataTable(); 
            adapter.Fill(codaTable);

            Console.WriteLine("\nData Record 2.1: Movement Record.");

            Console.WriteLine("Record Identification: {0}", dividedTrans[0]);

            Console.WriteLine("Article Code: {0}", dividedTrans[1]);

            Console.WriteLine("Sequence Number: {0}", new string(dividedTrans.GetRange(2, 4).ToArray()));

            Console.WriteLine("Detail Number: {0}", new string(dividedTrans.GetRange(6, 4).ToArray()));

            Console.WriteLine("Reference Number of bank: {0}", new string(dividedTrans.GetRange(10, 21).ToArray()));

            Console.WriteLine("Movement Sign: {0}", new string(dividedTrans.GetRange(31, 1).ToArray()));

            Console.WriteLine("Amount: {0}", Convert.ToString(Int32.Parse(string.Join("", dividedTrans.GetRange(32, 15)))));

            Console.WriteLine("Value Date: {0}", DateandTime(string.Join("", dividedTrans.GetRange(47, 6))).ToString("d"));

            Console.WriteLine("Transaction Code: {0}", new string(dividedTrans.GetRange(53, 8).ToArray()));

            Console.WriteLine("Communication Type: {0}", new string(dividedTrans.GetRange(61, 1).ToArray()));

            Console.WriteLine("Communication zone: {0}", new string(dividedTrans.GetRange(62, 53).ToArray()));

            Console.WriteLine("Entry Date: {0}", DateandTime(string.Join("", dividedTrans.GetRange(115, 6))).ToString("d"));

            Console.WriteLine("Sequence Number: {0}", new string(dividedTrans.GetRange(121, 3).ToArray()));

            Console.WriteLine("Globalization Code: {0}", new string(dividedTrans.GetRange(124, 1).ToArray()));

            Console.WriteLine("Next Code: {0}", new string(dividedTrans.GetRange(125, 1).ToArray()));

            Console.WriteLine("Blank: {0}", new string(dividedTrans.GetRange(126, 1).ToArray()));

            Console.WriteLine("Linker Code: {0}", new string(dividedTrans.GetRange(127, 1).ToArray()));
        }
    }

}

"Save to DB " means you need to insert into an sql table or update it. As you are first reading the sql table into the DataTable one may conclude you are going to update it. Or insert? — Serg
– Serg, Commented Aug 30, 2017 at 8:14
I need to insert it into the table. I shall be updating it later on, but not right now. Right now the table is empty. — Richeek Dey
– Richeek Dey, Commented Aug 30, 2017 at 8:26

tmaj · Accepted Answer · 2017-08-30 08:04:56Z

2

There are many ways to write data.

One way is raw SQL as shown in SQL update statement in C#

using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{ 
  command.CommandText = "UPDATE Student SET Name = @name where id = @id";

  command.Parameters.AddWithValue("@name", name);
  command.Parameters.AddWithValue("@id", myId);

  connection.Open();

  command.ExecuteNonQuery();

  connection.Close();
}

answered Aug 30, 2017 at 8:04

tmaj

36k12 gold badges108 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

james · Accepted Answer · 2017-08-30 08:02:24Z

1

My suggestion is to use some ORM (Object-relational mapping) tool. For C#, there are 2 major, EntityFramework and NHibernate. I prefere EntityFramework for it simplicity.

answered Aug 30, 2017 at 8:02

james

7433 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to Read/Write to SQL database from console application (C#)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related