none
Program doesn't work RRS feed

  • Question

  • When I press key "Start", I get error:System.Data.SqlClient.SqlException
      HResult=0x80131904
      Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
      Source=.Net SqlClient Data Provider
      StackTrace:
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at Decompression.MainWindow.btnOK2_Click(Object sender, EventArgs e) in C:\Users\User\source\repos\Decompression\Decompression\MainWindow.cs:line 76
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at Decompression.Form1.btnOK_Click(Object sender, EventArgs e) in C:\Users\User\source\repos\Decompression\Decompression\DatabaseUser.cs:line 40
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Decompression.Program.Main() in C:\Users\User\source\repos\Decompression\Decompression\Program.cs:line 19

    Inner Exception 1:
    Win32Exception
    Monday, December 2, 2019 2:39 PM

Answers

  • The .NET app cannot get to it so either the connection string is wrong or you have a security policy issue preventing the app from connecting. Look closely at the callstack.

    at Decompression.MainWindow.btnOK2_Click(Object sender, EventArgs e) in

    Your btnOK2_Click method is failing. Put a breakpoint on the call to open the connection. Notice your connection string? It is wrong it would appear. Your early code is using some sort of DatabaseUser class. But this code is creating the connection string directly. It is using private fields to set up the connection string but it appears these values are never being set. Your earlier code is using DatabaseUser which does have them set. Hence you have a bad connection string.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Decompressor Tuesday, December 3, 2019 12:32 PM
    Monday, December 2, 2019 3:47 PM
    Moderator
  • I notice that you have a separate thread asking about creating a setup program. If this is the same program then the setup program would be responsible for this and not your app.

    If you want to really build a login for your winforms app then note that you should really be using Win auth. In that case the connection string can be configured to just use the currently logged in users credentials. This is the preferred approach for Windows apps.

    If you really need to collect a SQL-specific username/pwd each time the user runs your app then there are plenty of examples online with code on how to do this. Pick one that meets your needs. Here's just one. The gist of the remaining code is along these lines.

    //You'll need some place to store the UN/PWD such that your database layer can access it. We'll use NeworkCredentials for this just to make it fit more easily into a Windows app. You need to store that information somewhere the DB layer has access to it. Maybe just a field in your main form, depends upon your architecture.
    
    public class MainForm : Form
    {
       …
    
       //Collecting credentials at form load, could be anywhere
       protected override void OnLoad ( EventArgs e )
       {
          base.OnLoad(e);
    
          _credentials = CredentialManager.PromptForLogin();  
       }
    
       //Some method that does DB stuff...
       private void DoDatabaseWork ()
       {
           using (var conn = new DatabaseConnection(_credentials))
           {
               ...
           };
       }
      
       private ICredentials _credentials;
    }
    
    //Helper class to separate credential management from UI
    public static class CredentialManager
    {
       public ICredentials PromptForLogin ( )
       {
           //Show login form, get credentials from user
           //Create instance of NetworkCredentials to store information
           return credentials;
       }
    }
    
    //Helper class to wrap a connection and add the credentials
    public class DatabaseConnection : SqlConnection
    {
       public DatabaseConnection ( ICredentials credentials )
       {
            //Build your connection string as normal
            var builder = new SqlConnectionStringBuilder();
          
            //Append the credential information (note could be expanded to support Windows or regular auth but at that point you can just store stuff in the config file)
            var creds = credentials.GetCredentials();
            builder.UserID = creds.UserName;
            builder.Password = creds.Password;     
    
            ConnectionString = builder.ConnectionString
       }
    }


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Decompressor Thursday, December 5, 2019 7:16 AM
    Wednesday, December 4, 2019 3:07 PM
    Moderator
  • I've added: sqm.ExecuteNonQuery(); and it works
    • Marked as answer by Decompressor Monday, December 9, 2019 3:50 PM
    Monday, December 9, 2019 3:50 PM

All replies

  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Decompression
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
    
            }
           
             
    
                private void btnOK_Click(object sender, EventArgs e)
            {
                    if (!string.IsNullOrWhiteSpace(loginTextBox.Text) && !string.IsNullOrWhiteSpace(passwordTextBox.Text))
                        {
                        var ops = new DatabaseUser("N10468000115\\SQLHUNTER", "demo");
                        var loginResults = ops.SqlCredentialLogin(loginTextBox.Text, passwordTextBox.Text);
                        if (loginResults)
                          {
    
                            var successValue = ops.DoWork(passwordTextBox.Text, loginTextBox.Text);
                            var workResult = string.IsNullOrWhiteSpace(successValue);
                            
                                if (workResult)
                                {
                                    Hide();
                                    Form MW = new MainWindow();
                                    MW.ShowDialog();
                                }
                                else
                                {
                                    MessageBox.Show(successValue);
                                }                                      
                          }
                               else
                                 {
                                      MessageBox.Show("Login is failed");
                                 }
                        }
    
            }
    
            private void checkBox1_CheckedChanged(object sender, EventArgs e)
            {
                if (checkBox1.Checked)
                {
                    passwordTextBox.UseSystemPasswordChar = true;
                }
                else
                {
                    passwordTextBox.UseSystemPasswordChar = false;
                }
            }
        }
    }
    

    Monday, December 2, 2019 2:40 PM
  • using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Security;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace Decompression
    {
        public class DatabaseUser
        {
            public string DoWork(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();
                return "";
            }
            private string serverName;
            private string catalogName;
            public DatabaseUser(string serverName, string catalogName )
            {
                this.serverName = serverName;
                this.catalogName = catalogName;
            }
    
    
    
            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 (SqlConnection cn = new SqlConnection { ConnectionString = connectionString })
                {
                    try
                    {
                        cn.Credential = credentials;
                        cn.Open();
                        return true;
                    }
                    catch (Exception e)
                    {
                        return false;
                    }
    
                }
    
    
    
            }
        }
    }
    

    Monday, December 2, 2019 2:41 PM
  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Decompression
    {
        public partial class MainWindow : Form
        {
            
            public MainWindow()
            {
                InitializeComponent();
            }
            private string serverName;
            private string catalogName;
            
            private bool IsCarNumberValid()
            {
                if(carNumberTextBox.Text=="")
                {
                    MessageBox.Show("Enter a car number");
                        return false;
                }
                else
                {
                    return true;
                }
            }
            private bool IsPeopleValid()
            {
                if (numericUpDown1.Value < 1)
                {
                    MessageBox.Show("Enter people amount");
                    return false;
                }
                else
                {
                    return true;
                }
            }
            private void ClearForm()
            {
                dateTimePicker1.Value = DateTime.Now;
            }
            private void btnOK2_Click(object sender, EventArgs e)
            {
                if(IsCarNumberValid())
                {
                    string connectionString = $"Data Source={serverName};" + $"Initial catalog={catalogName};";
                    using (SqlConnection cn = new SqlConnection { ConnectionString = connectionString })
                    {
                        using(SqlCommand sqm=new SqlCommand("dbo.uspTrans",cn))
                        {
                            sqm.CommandType = CommandType.StoredProcedure;
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value = carNumberTextBox.Text;
    
                            sqm.Parameters.Add(new SqlParameter("@t_date", SqlDbType.DateTime));
                            sqm.Parameters["@t_date"].Value = dateTimePicker1.Value;
    
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = richTextBox1.Text;
    
                            sqm.Parameters.Add(new SqlParameter("@people", SqlDbType.Int));
                            sqm.Parameters["@people"].Value = numericUpDown1.Value;
    
                            try
                            {
                                cn.Open();
                                sqm.ExecuteNonQuery();
                               
                            }
                            finally
                            {
                                cn.Close();
                            }
                        }
                    }
                }
            }
        }
    }
    

    Monday, December 2, 2019 2:42 PM
  • The Message property indicates the error:

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

    You are trying to connect to a SQL Server database and the connection string you are using is referring to a server that does not exist. Your code indicates the server is 'N10468000115\\SQLHUNTER' and the database name is `demo`. Are these correct? Can you connect to this server using SQL Server Management Studio from the machine running the program? If not then this is a SQL Server configuration issue.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, December 2, 2019 2:50 PM
    Moderator
  • I connect normally to the server and to the database  under defined login and password
    Monday, December 2, 2019 3:38 PM
  • The .NET app cannot get to it so either the connection string is wrong or you have a security policy issue preventing the app from connecting. Look closely at the callstack.

    at Decompression.MainWindow.btnOK2_Click(Object sender, EventArgs e) in

    Your btnOK2_Click method is failing. Put a breakpoint on the call to open the connection. Notice your connection string? It is wrong it would appear. Your early code is using some sort of DatabaseUser class. But this code is creating the connection string directly. It is using private fields to set up the connection string but it appears these values are never being set. Your earlier code is using DatabaseUser which does have them set. Hence you have a bad connection string.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Decompressor Tuesday, December 3, 2019 12:32 PM
    Monday, December 2, 2019 3:47 PM
    Moderator
  • Inside MainWindow, try replacing these lines:

       private string serverName;

       private string catalogName;

    with:

       private string serverName = @"N10468000115\SQLHUNTER";

       private string catalogName = "demo";

    Tuesday, December 3, 2019 11:15 AM
  •  if(IsCarNumberValid())
                {
                    var userName = "art";
                    var userPassword = "demo125";
                    var serverName= "N10468000115\\SQLHUNTER";
                    var catalogName = "hawk";
                    string connectionString = $"Data Source={serverName};" + $"Initial catalog={catalogName};"+
                        $"User Id={userName};" + $"Password={userPassword};";
    Yes! It's work.Thank you!
    Tuesday, December 3, 2019 12:31 PM
  • But how to make  any valid login and password will open connection?
    Tuesday, December 3, 2019 2:45 PM
  • If you want to be able to change the login info then you need to either put it in the config file (so you can change it without recompiling) or you need to prompt the user (think SSMS). The config file (or some other secure location) is the most common and is documented here. Put the entire string (not just UN/PWD) into the `connectionStrings` section and use `ConfigurationManager` to read it. Pass the entire connection string to the connection object and you're done.

    //Config
    <connectionStrings>
       <add name="MyConnection" connectionString="Server=…;Database=…;User Id=…;Password=…" providerName="System.Data.SqlClient" />
    </connectionStrings>
    
    //To get it
    var connString = ConfigurationManager.ConnectionStrings["MyConnection"];
    new SqlConnection(connString.ConnectionString);

    If you want each user to specify their UN/PWD when they use your app then display a form to collect the info from the user and then store that information into a connection management class that your database layer has access to.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, December 3, 2019 2:53 PM
    Moderator
  • Inside MainWindow, try replacing these lines:

       private string serverName;

       private string catalogName;

    with:

       private string serverName = @"N10468000115\SQLHUNTER";

       private string catalogName = "demo";

    But what to do with User Id  and Password?
     string connectionString = $"Data Source={serverName};" + $"Initial catalog={catalogName};"+
                        $"User Id={userName};" + $"Password={userPassword};";


    Tuesday, December 3, 2019 3:11 PM

  • If you want each user to specify their UN/PWD when they use your app then display a form to collect the info from the user and then store that information into a connection management class that your database layer has access to.


    Michael Taylor http://www.michaeltaylorp3.net

    May you describe this process in detail? For instance, I have five users, each has  authentical login and password, they have appropriate permissions, how to write the code each user can enter and use application under his login and password?
    • Edited by Decompressor Wednesday, December 4, 2019 10:42 AM
    Wednesday, December 4, 2019 10:25 AM
  • I notice that you have a separate thread asking about creating a setup program. If this is the same program then the setup program would be responsible for this and not your app.

    If you want to really build a login for your winforms app then note that you should really be using Win auth. In that case the connection string can be configured to just use the currently logged in users credentials. This is the preferred approach for Windows apps.

    If you really need to collect a SQL-specific username/pwd each time the user runs your app then there are plenty of examples online with code on how to do this. Pick one that meets your needs. Here's just one. The gist of the remaining code is along these lines.

    //You'll need some place to store the UN/PWD such that your database layer can access it. We'll use NeworkCredentials for this just to make it fit more easily into a Windows app. You need to store that information somewhere the DB layer has access to it. Maybe just a field in your main form, depends upon your architecture.
    
    public class MainForm : Form
    {
       …
    
       //Collecting credentials at form load, could be anywhere
       protected override void OnLoad ( EventArgs e )
       {
          base.OnLoad(e);
    
          _credentials = CredentialManager.PromptForLogin();  
       }
    
       //Some method that does DB stuff...
       private void DoDatabaseWork ()
       {
           using (var conn = new DatabaseConnection(_credentials))
           {
               ...
           };
       }
      
       private ICredentials _credentials;
    }
    
    //Helper class to separate credential management from UI
    public static class CredentialManager
    {
       public ICredentials PromptForLogin ( )
       {
           //Show login form, get credentials from user
           //Create instance of NetworkCredentials to store information
           return credentials;
       }
    }
    
    //Helper class to wrap a connection and add the credentials
    public class DatabaseConnection : SqlConnection
    {
       public DatabaseConnection ( ICredentials credentials )
       {
            //Build your connection string as normal
            var builder = new SqlConnectionStringBuilder();
          
            //Append the credential information (note could be expanded to support Windows or regular auth but at that point you can just store stuff in the config file)
            var creds = credentials.GetCredentials();
            builder.UserID = creds.UserName;
            builder.Password = creds.Password;     
    
            ConnectionString = builder.ConnectionString
       }
    }


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Decompressor Thursday, December 5, 2019 7:16 AM
    Wednesday, December 4, 2019 3:07 PM
    Moderator
  • Thank you very much.  In the code on link you've done they create table for user's credentials (user Id, password, user name etc.), but in SQL database already must be such table, where saved all valid users id and passwords(but I don't know how to find it). Is it neсessary to create such table or it is possible to refer on existing system table? Please, give me name of system table, if you know. I saw sys.credentials, but it's empty.
    Thursday, December 5, 2019 7:46 AM
  • You don't need to store/validate anything. When you pass a UN/PWD as part of a SQL connection string then that login must have already been set up by a DBA. You won't ever do anything yourself nor will your app.

    As part of your request you simply asked how to be able to collect (SQL) login info from the user and pass it to the connection string. We have accomplished that already. At no point will your app actually ever validate the credentials so where this is stored in the DB isn't relevant. If the user provides bad credentials then when you attempt to connect to the DB the first time SQL will validate the credentials (and rights) and fail the open request if the user doesn't have permission. All your app needs to do is handle the login failure and display an error to the user. You won't be doing anything with the underlying SQL views. Even if you wanted to you couldn't because you'll be connected as whatever user credentials were given and sensitive views like this are only relevant to DBAs. If you really want to know where this info is stored you'll need to post in the SQL forums though as that is really a DBA question.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, December 5, 2019 3:00 PM
    Moderator
  • "If you want to really build a login for your winforms app then note that you should really be using Win auth."

    Server=N10468000115\SQLHUNTER;Initial Catalog=hawk;Integrated Security=True;

    Have that "Win auth" you meant? If not, please, write in detail

    Friday, December 6, 2019 10:56 AM
  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace CSharpcorner
    {
        class Class1
        { 
    
    
            public Class1 Login(LoginModel model)
        {
            Class1 response = new Class1();
            try
            {
                using (IDbConnection connection = DbConnection.DbConnect())
                {
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("@Username", model.Username);
                    parameters.Add("@Password", model.Password);
                    response = SqlMapper.Query<Class1>(connection, "[dbo].[usp_CheckValidUser]", parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Message = ex.Message.ToString();
            }
            return response;
        }
    }
    }
    

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace CSharpcorner
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            public class LoginController
            {
                private LoginService _loginService;
                public LoginController()
                {
                    _loginService = new LoginService();
                }
            }
    
            private void btnOK_Click(object sender, EventArgs e)
            {
                progressBar.Minimum = 0;
                progressBar.Maximum = 100;
                progressBar.Value = 10;
                progressBar.Step = 10;
                lblRequiredUname.Visible = false;
                lblRequiredPwd.Visible = false;
                if (string.IsNullOrEmpty(loginTextBox.Text) && string.IsNullOrEmpty(passwordTextBox.Text))
                {
                    lblRequiredUname.Visible = true;
                    lblRequiredPwd.Visible = true;
                }
                else if (string.IsNullOrEmpty(loginTextBox.Text.Trim()))
                {
                    lblRequiredUname.Visible = true;
                }
                else if (string.IsNullOrEmpty(passwordTextBox.Text.Trim()))
                {
                    lblRequiredPwd.Visible = true;
                }
                else
                {
                    progressBar.Visible = true;
                    if (!bgWorker.IsBusy)
                    {
                        btnL.Enabled = false;
                        progressBar.Style = ProgressBarStyle.Marquee;
                        bgWorker.RunWorkerAsync();
                    }
                }
            }
            private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
            {
                Class1 response = new Class1();
                LoginModel model = new LoginModel();
                model.Username = loginTextBox.Text.Trim();
                // model.Password = txtPassword.Text.Trim();  
                string passwordSalt = string.Empty;
                RegistrationModel registrationModel = loginController.GetUserDetailsByUsername(model.Username);
                if (registrationModel != null)
                {
                    passwordSalt = registrationModel.PasswordSalt;
                    model.Password = StaticMethods.EncryptString(passwordSalt, txtPassword.Text.Trim());
                    response = loginController.Login(model);
                    if (response.Success)
                    {
                        this.Invoke(new MethodInvoker(delegate () {
                            this.Hide();
                            using (Dashboard dashboard = new Dashboard())
                            {
                                dashboard.ShowDialog();
                            }
                        }));
                    }
                    else
                    {
                        this.Invoke(new MethodInvoker(delegate () {
                            loginTextBox.ReadOnly = false;
                            passwordTextBox.ReadOnly = false;
                        }));
                        btnOK.Enabled = false;
                        MessageBox.Show(response.Message, "Fail!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
                else
                {
                    MessageBox.Show("Please enter valid username..", "Invalid Username!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            private void bgWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
            {
                progressBar.Style = ProgressBarStyle.Blocks;
                btnOK.Enabled = true;
            }
    
    
            protected override void OnLoad(EventArgs e)
            {
                base.OnLoad(e);
    
                _credentials = CredentialManager.PromptForLogin();
            }
    
            //Some method that does DB stuff...
            private void DoDatabaseWork()
            {
                using (var conn = new DatabaseConnection(_credentials))
                {
                    ...
           };
            }
    
            private ICredentials _credentials;
        }
    
        //Helper class to separate credential management from UI
        public static class CredentialManager
        {
            public ICredentials PromptForLogin()
            {
                //Show login form, get credentials from user
                //Create instance of NetworkCredentials to store information
                return credentials;
            }
        }
    
        //Helper class to wrap a connection and add the credentials
        public class DatabaseConnection : SqlConnection
        {
            public DatabaseConnection(ICredentials credentials)
            {
                //Build your connection string as normal
                var builder = new SqlConnectionStringBuilder();
    
                //Append the credential information (note could be expanded to support Windows or regular auth but at that point you can just store stuff in the config file)
                var creds = credentials.GetCredentials();
                builder.UserID = creds.UserName;
                builder.Password = creds.Password;
    
                ConnectionString = builder.ConnectionString
            }
        }
    }
    
    
    

    Friday, December 6, 2019 1:49 PM
  • Set Integrated Security to SSPI and that uses Windows auth. You can refer to the difference between true and SSPI here.

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, December 6, 2019 2:37 PM
    Moderator
  • Most of your errors seem related to your code and not the focus of this topic so you'll need to either look at them yourself or post a new thread about the compiler errors at this point.

    The only one that I see related to this discussion is the fact that DatabaseConnection derives from SqlConnection which is sealed. So you cannot use inheritance as originally suggestion. Just create a wrapper instead.

    //Helper class to wrap a connection and add the credentials
    public static class DatabaseConnection
    {
       public static SqlConnection WithCredentials ( this SqlConnection source, ICredentials credentials)
       {
         //Build your connection string as normal
         var builder = new SqlConnectionStringBuilder(source.ConnectionString);
    
         //Append the credential information (note could be expanded to support Windows or regular auth but at that point you can just store stuff in the config file)
         var creds = credentials.GetCredentials();
         builder.UserID = creds.UserName;
         builder.Password = creds.Password;
    
         source.ConnectionString = builder.ConnectionString
         return source;
       }
    }
    
    //Usage
    using (var conn = new SqlConnection("Server=blah;Database=blah").WithCredentials(credentials))
    {
    };


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, December 6, 2019 2:43 PM
    Moderator
  • I don't really need to collect information about user name and password, so I wrote simple application and it works when I enter login and password in Form1, but when I  put information to SuperWindow form  nothing occurs. Please, look at this, what I make wrong?

    
                                            
    

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;

    namespace _06._12._2019_loginform
    {
        public partial class Form1 : Form
        {



            SqlConnection con = new SqlConnection();
            public Form1()
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);


                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);
                con.Open();

                {
                }
            }





            private void btnOK_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);

                con.Open();
                string userid = loginTextBox.Text;
                string password = passwordTextBox.Text;
                SqlCommand cmd = new SqlCommand("select userid,password from hawk.dbo.UserLogin where UserName='" + loginTextBox.Text + "'and Password='" + passwordTextBox.Text + "'", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {

                    Hide();
                    Form SW = new SuperWindow();
                    SW.ShowDialog();

                }
                else
                {
                    MessageBox.Show("Invalid Login please check username and password");
                }
                con.Close();
            }
        }

    }

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace _06._12._2019_loginform
    {
        public partial class SuperWindow : Form
        {
            public SuperWindow()
            {
                InitializeComponent();
            }
    
            private void btnOKMain_Click(object sender, EventArgs e)
            {
    
                using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                {
    
                    const string cmm = "insert hawk.dbo.transport (tr_numb, t_date, people) values (@tr_numb," +
                          "@t_date, @people)";
                    using (SqlCommand sqm = new SqlCommand(cmm, cn))
                    {
                        sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                        sqm.Parameters["@tr_numb"].Value = txtCarNumber.Text;
    
                        sqm.Parameters.Add(new SqlParameter("@t_date", SqlDbType.DateTime));
                        sqm.Parameters["@t_date"].Value = dateTimePicker1.Value;
    
                        sqm.Parameters.Add(new SqlParameter("@people", SqlDbType.Int));
                        sqm.Parameters["@people"].Value = numericUpDown1.Value;
                        try
                        {
                            cn.Open();
                        }
                        catch
                        {
                            MessageBox.Show("operation failed");
                        }
                        finally
                        {
                            cn.Close();
                        }
    
                    }
    
                }
    
            }
        }
    }

    
    

    In Settings.settings. I entered: Server=N10468000115\SQLHUNTER; Initial Catalog=hawk; Integrated Security=SSPI;


    Monday, December 9, 2019 11:03 AM
  • I've added: sqm.ExecuteNonQuery(); and it works
    • Marked as answer by Decompressor Monday, December 9, 2019 3:50 PM
    Monday, December 9, 2019 3:50 PM
  • But what to do, if different users have different permissions? For instance one user has permission only

    "select", other has permission "select" and "insert". How to make each user  acts only under his permissions?

    Tuesday, December 10, 2019 7:43 AM
  • SQL enforces permissions so you don't need to do anything. It'll just fail with a permissions violation that you can catch and report to the user.

    If you really need to know whether a user has permissions or not then you're in for a lot of code. It is easy to query SQL for the list of roles a user has but permissions can be complex in SQL. For example select rights on table A doesn't mean that user can select from table B and therefore any query that joins the 2 will fail. Sprocs have their own permissions. I believe your best course of action is to use roles to identify what users can do. Then work with your DBA to set these up such that your app cares about roles and the DB is responsible for enforcing the permissions for each role. Please post this question in the SQL forums.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, December 10, 2019 2:50 PM
    Moderator
  • SQL enforces permissions so you don't need to do anything. It'll just fail with a permissions violation that you can catch and report to the user.

    If you really need to know whether a user has permissions or not then you're in for a lot of code. It is easy to query SQL for the list of roles a user has but permissions can be complex in SQL. For example select rights on table A doesn't mean that user can select from table B and therefore any query that joins the 2 will fail. Sprocs have their own permissions. I believe your best course of action is to use roles to identify what users can do. Then work with your DBA to set these up such that your app cares about roles and the DB is responsible for enforcing the permissions for each role. Please post this question in the SQL forums.


    Michael Taylor http://www.michaeltaylorp3.net

    Thank you
    Tuesday, December 10, 2019 3:08 PM