how do I write data that is not deleted with database when the form is closed and opened? | SQL

Oğulcan Akca 196 Reputation points
2021-02-27T20:38:37.557+00:00
private void btnPublish_Click(object sender, EventArgs e)
        {

            Sqlbaglan.NesneVer().thisConn = new SqlConnection(@"Data Source=(localdb)\mssqllocaldb;initial catalog = ManagerPanel;integrated security = true");
            Sqlbaglan.NesneVer().thisConn.Open();
            Sqlbaglan.NesneVer().thisQuery = new SqlCommand("SELECT Kurallar FROM [Rules] WHERE TC=@TC", Sqlbaglan.NesneVer().thisConn);
            Sqlbaglan.NesneVer().thisQuery.Parameters.AddWithValue("@TC", tbxTC.Text);
            label1.Text = Sqlbaglan.NesneVer().thisQuery.ExecuteScalar().ToString();
            Sqlbaglan.NesneVer().thisConn.Close();

        }

Friends, I have 2 problems, I do not have much knowledge of sql, but I wanted to do my job with the database. I created sql conn and query object with singleton dp. I called the rule that has that TC with the TC entered in tbxTC and printed it on label1.Text in the SAME FORM. So far, everything happens as I want. My 1st problem is that when I open and close the form, label1.Text gets reset. Consider my second problem independent of my first problem. My second problem is that I have written in capital letters already - I am nervous- I have to show it in another form. This form's name is frm2. So consider label1.text is in another form.

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,540 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,111 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,517 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Karen Payne MVP 35,286 Reputation points
    2021-02-28T16:09:03.78+00:00

    If you want what was retrieved next time the form is opened create a setting under project properties. In the following simple example there is a setting LastCustomerIdentifer with a default value of -1 which indicates it has not been used.

    Notes

    • All code presented requires C#8 which will work with C# 9 too
    • Only reason for a singleton is you mentioned using one, there really is no reason to use a singleton as static methods in a class will work just fine.

      Singleton class

      using System;
      using System.Data.SqlClient; namespace SimpleConnectSqlServer.Classes
      {
      public sealed class DataOperations
      {
      private static string _connectionString =
      "Data Source=.\sqlexpress;Initial " +
      "Catalog=NorthWind2020;Integrated Security=True";
          private static readonly Lazy<DataOperations> Lazy = new(() => new DataOperations());  
          public static DataOperations Instance => Lazy.Value;  
      
          public string CompanyName(int identifier)  
          {  
              using var cn = new SqlConnection { ConnectionString = _connectionString };  
              using var cmd = new SqlCommand  
              {  
                  Connection = cn,   
                  CommandText = "SELECT CompanyName FROM Customers WHERE CustomerIdentifier = @Id"  
              };  
      
              cmd.Parameters.AddWithValue("@Id", identifier);  
              cn.Open();  
      
              return (string) cmd.ExecuteScalar();  
          }  
      }  
      
      }

    Form code

    namespace SimpleConnectSqlServer  
    {  
        public partial class Form1 : Form  
        {  
            private int _iterations = 6;  
            public Form1()  
            {  
                InitializeComponent();  
                Closing += OnClosing;  
                Shown += OnShown;  
            }  
    
            private void OnShown(object? sender, EventArgs e)  
            {  
                if (Properties.Settings.Default.LastCustomerIdentifier <= -1) return;  
    
                CompanyNameLabel.Text = DataOperations.Instance.CompanyName(Properties.Settings.Default.LastCustomerIdentifier);  
                IdentifierTextBox.Text = Properties.Settings.Default.LastCustomerIdentifier.ToString();  
            }  
    
            private void OnClosing(object sender, CancelEventArgs e)  
            {  
                if (Properties.Settings.Default.LastCustomerIdentifier > -1)  
                {  
                    Properties.Settings.Default.Save();  
                }  
            }  
    
            private void GetCustomerNameButton_Click(object sender, EventArgs e)  
            {  
                if (!int.TryParse(IdentifierTextBox.Text, out var id)) return;  
    
                CompanyNameLabel.Text = DataOperations.Instance.CompanyName(id);  
                Properties.Settings.Default.LastCustomerIdentifier = id;  
            }  
        }  
    }  
    

    Verify in SSMS

    (SQL-Server Management Studio)
    72751-f2.png

    Settings tab under project properties

    72702-f1.png

    1 person found this answer helpful.
    0 comments No comments

  2. Duane Arnold 3,216 Reputation points
    2021-02-28T12:02:22.99+00:00

    Well, if you were on the right track, then you would be using SoC in your program.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    None of the database code should be in the button_click event, the event should have called a method that had the database code and let the database method do the work on the behalf of the button_click event.

    Now with the database code being in its own area, the database code can be called from anywhere within the program by any form in the program and even from a Form_Closing event.

    This may help you, and I say go back to the starting blocks and get a little basic understanding. You seem to be sharp minded and you should be able to easily pick it up.

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET-2

    HTH