Learning Points
- Hard code connection string or,
- Add connection string in App.config file and read it using ConnectionManager of System.Configuration namespace. If System.Configuration is missing in your application, download it from NuGet package.
- If App.config is missing, create it and name it App.config.
loginForm.cs
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WinFormsAppx1
{
public partial class frmLogin : Form
{
public frmLogin()
{
InitializeComponent();
}
private void frmLogin_Load(object sender, EventArgs e)
{
txtUsername.TabIndex = 0;
txtPassword.TabIndex = 1;
}
private void btnLogin_Click(object sender, EventArgs e)
{
validateTextBoxes();
}
private void validateTextBoxes()
{
if (String.IsNullOrEmpty(txtUsername.Text))
{
MessageBox.Show("Username is required.");
}
else if (String.IsNullOrEmpty(txtPassword.Text))
{
MessageBox.Show("Password is required.");
}
else
{
}
//check username and password
//store logging time
int success = StoreDataIntoTable(txtUsername.Text, txtPassword.Text);
if (success==1)
{
MessageBox.Show("Data Inserted.");
Application.Exit();
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
Application.Exit();
}
App.config file
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="cs" connectionString="Data Source=IN-AJEET-LT\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True" providerName="system.Data.SqlClient"/>
</connectionStrings>
</configuration>
private int StoreDataIntoTable(string user, string passwd)
{
// 1st Approach, ConnectionString: Hard coded
//string connstring = @"Data Source=IN-AJEET-LT\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True";
// 2nd Approach, ConnectionString: inside App.config file
string connstring = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(connstring))
{
sqlConnection.Open();
string commandText = "INSERT INTO logger values('" + user + "', '" + passwd + "', '" + DateTime.Now + "')";
SqlCommand command = new SqlCommand(commandText, sqlConnection);
command.ExecuteNonQuery();
return 1;
}
}
3rd Approach, Stored Procedure: Replace the above method by following method. Instead of using the CommandText, we use the stored procedure created inside the SQL Server database. Stored procedures provides better security as the database code is stored separately in the SQL Server instead of the C# code.
Create the following procedure as SQL query and execute it to create stored procedure object.
CREATE procedure [dbo].[Logg] (
@username nvarchar(30),
@password nvarchar(30),
@logDate nvarchar(30)
)
As
INSERT INTO logger values(@username, @password, @logDate)
NOTE: The stored procedure variables are used inside the C# code. They are called Parameters of Command.
private int StoreDataIntoTable(string user, string passwd)
{
// 1st Approach, ConnectionString: Hard coded
//string connstring = @"Data Source=IN-AJEET-LT\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True";
// 2nd Approach, ConnectionString: inside App.config file
string connstring = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(connstring))
{
//sqlConnection.Open();
//string commandText = "INSERT INTO logger values('" + user + "', '" + passwd + "', '" + DateTime.Now + "')";
//Stored procedure instead of hard coded command inside C# code
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Logg";
command.Parameters.AddWithValue("@username", user);
command.Parameters.AddWithValue("@password", passwd);
command.Parameters.AddWithValue("@logDate", DateTime.Now);
sqlConnection.Open();
command.Connection = sqlConnection;
command.ExecuteNonQuery();
return 1;
}
}
loginForm.Designer.cs
namespace WinFormsAppx1
{
partial class frmLogin
{
///
/// Required designer variable.
///
private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
///
/// true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmLogin));
this.pictureBoxLogin = new System.Windows.Forms.PictureBox();
this.txtUsername = new System.Windows.Forms.TextBox();
this.txtPassword = new System.Windows.Forms.TextBox();
this.btnLogin = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.btnCancel = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.pictureBoxLogin)).BeginInit();
this.SuspendLayout();
//
// pictureBoxLogin
//
this.pictureBoxLogin.Image = ((System.Drawing.Image)(resources.GetObject("pictureBoxLogin.Image")));
this.pictureBoxLogin.Location = new System.Drawing.Point(12, 40);
this.pictureBoxLogin.Name = "pictureBoxLogin";
this.pictureBoxLogin.Size = new System.Drawing.Size(100, 109);
this.pictureBoxLogin.SizeMode = System.Windows.Forms.PictureBoxSizeMode.Zoom;
this.pictureBoxLogin.TabIndex = 0;
this.pictureBoxLogin.TabStop = false;
//
// txtUsername
//
this.txtUsername.Location = new System.Drawing.Point(237, 40);
this.txtUsername.Name = "txtUsername";
this.txtUsername.Size = new System.Drawing.Size(171, 25);
this.txtUsername.TabIndex = 1;
//
// txtPassword
//
this.txtPassword.Location = new System.Drawing.Point(237, 71);
this.txtPassword.Name = "txtPassword";
this.txtPassword.Size = new System.Drawing.Size(171, 25);
this.txtPassword.TabIndex = 1;
//
// btnLogin
//
this.btnLogin.Location = new System.Drawing.Point(237, 112);
this.btnLogin.Name = "btnLogin";
this.btnLogin.Size = new System.Drawing.Size(75, 26);
this.btnLogin.TabIndex = 5;
this.btnLogin.Text = "Login";
this.btnLogin.Click += new System.EventHandler(this.btnLogin_Click);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(149, 43);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(67, 17);
this.label1.TabIndex = 3;
this.label1.Text = "Username";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(149, 75);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(64, 17);
this.label2.TabIndex = 3;
this.label2.Text = "Password";
//
// btnCancel
//
this.btnCancel.Location = new System.Drawing.Point(333, 112);
this.btnCancel.Name = "btnCancel";
this.btnCancel.Size = new System.Drawing.Size(75, 26);
this.btnCancel.TabIndex = 4;
this.btnCancel.Text = "Cancel";
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// frmLogin
//
this.AutoScaleDimensions = new System.Drawing.SizeF(7F, 17F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(432, 205);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.btnLogin);
this.Controls.Add(this.txtPassword);
this.Controls.Add(this.txtUsername);
this.Controls.Add(this.pictureBoxLogin);
this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
this.Name = "frmLogin";
this.Text = "Login Form";
this.Load += new System.EventHandler(this.frmLogin_Load);
((System.ComponentModel.ISupportInitialize)(this.pictureBoxLogin)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.PictureBox pictureBoxLogin;
private System.Windows.Forms.TextBox txtUsername;
private System.Windows.Forms.TextBox txtPassword;
private System.Windows.Forms.Button btnLogin;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button btnCancel;
}
}
What to do if the connection fails to Database server?
if you try to connect with the SQL Server using SSMS, and connection fails as shown below.
step1: Try to connect
step2: Connection fail message
step3: Connection Restart: Open the "Services" windows. Search Services by pressing Winkey plus S in Windows10 and type Services. Note that Search bar appears in the bottom above the windows task bar, when Winkey+S is pressed.
step4: When the connection is restarted, run the C# code.
No comments:
Post a Comment