none
Main Window RRS feed

  • Question

  • What code I must write new window with SQL Server tables in user app form(where user can simply write new data to table) appears after connection to SQL Server and how to make main window?

    using System;
    using System.Windows;
    using System.Data;
    using System.Data.SqlClient;
    using System.Security;
    
    namespace MinimalApproachExample
    {
        
        
            public class DatabaseUser
            {
           
            public string DoWork(string pUserNName, string pPassword)
            {
    
                string connectionString = $"Data Source={serverName};" +
                                          $"Initial Catalog={catalogName};";
    
    
                var securePassword = new SecureString();
    
                foreach (var character in pPassword)
                {
                    securePassword.AppendChar(character);
                }
                return "";
            }
                private string serverName;
                private string catalogName;
                public DatabaseUser(string pServerName, string pCatalogName)
                {
                    serverName = pServerName;
                    catalogName = pCatalogName;
                }
                public bool SqlCredentialLogin(string pUserName, string pPassword)
                {
    
                    string connectionString = $"Data source={serverName};" +
                        $"Initial catalog={catalogName};";
                    var securePassword = new SecureString();
                    foreach (var character in pPassword)
                    {
                        securePassword.AppendChar(character);
                    }
                    securePassword.MakeReadOnly();
                    var credentials = new SqlCredential(pUserName, securePassword);
                    using (var cn = new SqlConnection { ConnectionString = connectionString })
                    {
                        try
                        {
                            cn.Credential = credentials;
                            cn.Open();
                            return true;
                        }
                        catch (Exception e)
                        {
                            return false;
                        }
                    }
                }
    
           
    
        }        
    }
    

    using System;
    using System.Windows;
    using System.Windows.Forms;
    
    namespace MinimalApproachExample
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void UserNameTextBox_TextChanged(object sender, EventArgs e)
            {
    
            }
    
            private void checkBox1_CheckedChanged(object sender, EventArgs e)
            {
                if (checkBox1.Checked)
                {
                    PasswordTextBox.UseSystemPasswordChar = true;
                }
                else
                {
                    PasswordTextBox.UseSystemPasswordChar = false;
                }
            }
            public int failureTimes = 0;
            private void ConnectButton_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
                {
                    var ops = new DatabaseUser("N10468000115\\SQLHUNTER", "master");
                    var loginResults = ops.SqlCredentialLogin(UserNameTextBox.Text, PasswordTextBox.Text);
                    if (loginResults)
                    {
    
                        var successValue = ops.DoWork(UserNameTextBox.Text, PasswordTextBox.Text);
    
                        var workResult = string.IsNullOrWhiteSpace(successValue);
                        if (workResult)
                        {
                            MessageBox.Show("Operation is successful");
                            
                           
    
                           
    
                        }
                        else
                        {
                            MessageBox.Show(successValue);
                        }
                    }
                    else
                    {
                        failureTimes++;
                        if (failureTimes>=3)
                        
                        {
                            MessageBox.Show("You have failed 3 times, the account has been locked!");
                            Close();
                        }
                        MessageBox.Show($"You have failed {failureTimes} times, there are {3 - failureTimes} chances left");
                        
                    }
                    }
                else
                {
                    MessageBox.Show("Incomplete information to continue");
                }
            }
            
    
            private void CancelButton_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
    

    • Moved by CoolDadTx Wednesday, November 13, 2019 2:40 PM Winforms related
    Wednesday, November 13, 2019 10:54 AM

Answers

  • Hi pavlob,

    In order to pass the data of datagridview to the database in time, you can use the method SqlDataAdapter.update.

    Here is code you can refer to.

    SqlDataAdapter adapter = null;
    DataSet ds = null;
    String connStr ="connectstring";
    
    private void Form1_Load(object sender, EventArgs e)
    {
            adapter = new SqlDataAdapter("select * from table", connStr);
            ds = new DataSet();
            adapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
    }
    
    private void Button1_Click(object sender, EventArgs e)
    {
            SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
            adapter.Update(ds);
    }

    Best Regards,

    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Decompressor Friday, November 22, 2019 12:50 PM
    Friday, November 15, 2019 10:04 AM

All replies

  • You will need something along these lines which is a rough outline. This outline is dependent on if the database is on a server which will work while if the database is local to each user this will not work. Also, for the stubbed methods shown below a table is needed that only an admin can work with which means the admin credentials must be present to login and do the required actions.

    /// <summary>
    /// Responsible for 
    /// * Flagging user with failed login
    /// * Unflagging a user (this really belong outside the app in a
    ///   admin tool.
    /// * Checking if a user can login.
    ///
    /// For this to work, another table is needed that only a specific
    /// user other than regular users can perform read and write operations.
    ///
    /// Also consideration is needed to act upon a database on a server vs
    /// a database which is on a user's computer.
    /// </summary>
    public class AdministratorOperations
    {
        public void ReinstateUser(string userName)
        {
            /*
             * TODO clear unauthorized flag from flagged table
             * Should return a bool for success or not
             */
        }
    
        public void RevokeUser(string userName)
        {
            // TODO user failed with three attempts
        }
    
        public bool CanLogin(string userName)
        {
            /*
             * TODO query table with flagged users with failed logins
             *
             * Return true if they are not flagged, return false if flagged
             */
    
            return true; // appease compiler for this mockup
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, November 13, 2019 11:19 AM
    Moderator
  • Hi pavlob,

    >> where user can simply write new data to table

    Add a new date into the table, you can refer to the following code.

    SqlDesigner SqlDesigner = new SqlDesigner(); private void Button1_Click(object sender, EventArgs e) { try { SqlDesigner.ExecuteNoQuery("insert into table(Id,Name,Quantity,Date) values ('" + textBox1.Text + "','" +

    textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')"); } catch (Exception) { MessageBox.Show("failed"); } } class SqlDesigner { private static string connStr ="connectString"; public static int ExecuteNoQuery(string sql) { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; return cmd.ExecuteNonQuery(); } } } }

    >> how to make main window

    Could you please explain what main window is?

    Best Regards,

    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 7:35 AM
  •  
    
    CREATE TABLE [Sales].[Customer] (  
        [CustomerID]   INT           IDENTITY (1, 1) NOT NULL,  
        [CustomerName] NVARCHAR (40) NOT NULL,  
        [YTDOrders]    INT           NOT NULL,  
        [YTDSales]     INT           NOT NULL  
    );  
    
    CREATE TABLE [Sales].[Orders] (  
        [CustomerID] INT      NOT NULL,  
        [OrderID]    INT      IDENTITY (1, 1) NOT NULL,  
        [OrderDate]  DATETIME NOT NULL,  
        [FilledDate] DATETIME NULL,  
        [Status]     CHAR (1) NOT NULL,  
        [Amount]     INT      NOT NULL  
    It's a tables, which must be in main window. Main window is windows form, where authorised user can write all information to these tables
    Thursday, November 14, 2019 9:47 AM
  • Hi pavlob,

    In order to pass the data of datagridview to the database in time, you can use the method SqlDataAdapter.update.

    Here is code you can refer to.

    SqlDataAdapter adapter = null;
    DataSet ds = null;
    String connStr ="connectstring";
    
    private void Form1_Load(object sender, EventArgs e)
    {
            adapter = new SqlDataAdapter("select * from table", connStr);
            ds = new DataSet();
            adapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
    }
    
    private void Button1_Click(object sender, EventArgs e)
    {
            SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
            adapter.Update(ds);
    }

    Best Regards,

    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Decompressor Friday, November 22, 2019 12:50 PM
    Friday, November 15, 2019 10:04 AM