Save User Setting in Win Forms

* Form Properties > appln bindings > Location

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace SaveSettings
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Size = Properties.Settings.Default.FormSize;
            this.BackColor = Properties.Settings.Default.color;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            Properties.Settings.Default.FormSize = this.Size;
            Properties.Settings.Default.Location = this.Location;
            Properties.Settings.Default.color = this.BackColor;
            Properties.Settings.Default.Save();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if(colorDialog1.ShowDialog() == DialogResult.OK)
               {
                 this.BackColor = colorDialog1.Color;
                }
       
        }
    }
}

How to join more than two tables in in sql server

Here we will be joining 3 tables , the concept behind this is the relation between any tables joining should have a column related to it from another table.

SQL Script to create the required tables

Create Table Departments

(

     DepartmentID int primary key,

     DepartmentName nvarchar(50)

)

GO

Create Table Genders

(

     GenderID int primary key,

     Gender nvarchar(50)

)

GO

Create Table Employees

(

     EmployeeID int primary key,

     EmployeeName nvarchar(50),

     DepartmentID int foreign key references Departments(DepartmentID),

     GenderID int foreign key references Genders(GenderID)

)

GO

Insert into Departments values (1, 'IT')

Insert into Departments values (2, 'HR')

Insert into Departments values (3, 'Payroll')

GO

Insert into Genders values (1, 'Male')

Insert into Genders values (2, 'Female')

GO

Insert into Employees values (1, 'Mark', 1, 1)

Insert into Employees values (2, 'John', 1, 1)

Insert into Employees values (3, 'Mike', 2, 1)

Insert into Employees values (4, 'Mary', 2, 2)

Insert into Employees values (5, 'Stacy', 3, 2)

Insert into Employees values (6, 'Valarie', 3, 2)

GO

Write a query to join 3 the tables and retrieve EmployeeName, DepartmentName and Gender. 

SELECT DepartmentName, Gender, COUNT(*) as TotalEmployees

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

JOIN Genders ON Employees.GenderID = Genders.GenderID

GROUP BY DepartmentName, Gender

ORDER BY DepartmentName, Gender

How to Delete duplicate rows in sql

SQL Script to create Employees table

Create table Employees

(

     ID int,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

GO

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

The delete query should delete all duplicate rows except one. 

Here is the SQL query that does the job. PARTITION BY divides the query result set into partitions.

WITH EmployeesCTE AS

(

   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber

   FROM Employees

)

DELETE FROM EmployeesCTE WHERE RowNumber > 1

What to choose for performance - SubQueries or Joins in sql

In General Joins work faster than subqueries .

According to MSDN, in sql server, in most cases, there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. For example, on my machine I have
400,000 records in tblProducts table
600,000 records in tblProductSales tables 

The following query, returns, the list of products that we have sold atleast once.This query is formed using sub-queries. When I execute this query I get 306,199 rows in 6 seconds
Select Id, Name, Description
from tblProducts
where ID IN
(
Select ProductId from tblProductSales
) 

At this stage please clean the query and execution plan cache using the following T-SQL command. 
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; -- Clears query cache
Go
DBCC FREEPROCCACHE; -- Clears execution plan cache
GO

Now, run the query that is formed using joins. Notice that I get the exact same 306,199 rows in 6 seconds. 

Select distinct tblProducts.Id, Name, Description
from tblProducts
inner join tblProductSales
on tblProducts.Id = tblProductSales.ProductId

Please Note: I have used automated sql script to insert huge amounts of this random data. Please watch Part 61 of SQL Server tutorial, in which we have discussed about this automated script.

According to MSDN, in some cases where existence must be checked, a join produces better performance. Otherwise, the nested query must be processed for each result of the outer query. In such cases, a join approach would yield better results.

The following query returns the products that we have not sold at least once. This query is formed using sub-queries. When I execute this query I get 93,801 rows in 3 seconds

Select Id, Name, [Description]
from tblProducts
where Not Exists(Select * from tblProductSales where ProductId = tblProducts.Id)

When I execute the below equivalent query, that uses joins, I get the exact same 93,801 rows in 3 seconds.

Select tblProducts.Id, Name, [Description]
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL 

In general joins work faster than sub-queries, but in reality it all depends on the execution plan that is generated by SQL Server. It does not matter how we have written the query, SQL Server will always transform it on an execution plan. If sql server generates the same plan from both queries, we will get the same result.

I would say, rather than going by theory, turn on client statistics and execution plan to see the performance of each option, and then make a decision. 

Creating a large table with random data in sql

create table demotable (productid int,productname varchar(50))    

declare @id int
select @id =1
while (@id<=300)
begin
insert into demotable values (@id,'product'+cast(@id as nvarchar(50)))
select @id=@id+1
end

select * from demotable

drop table demotable

Different ways to replace NULL in sql server

Consider the Employees table below. 


In Part 14, we have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output. 



In the output, MANAGER column, for Todd's rows is NULL. I want to replace the NULLvalue, with 'No Manager'

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNULL() function. If M.Name returns NULL, then 'No Manager' string is used as the replacement value.
SELECT E.Name as Employee, ISNULL(M.Name,'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using CASE Statement:
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager' 
ELSE M.Name END as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value.
SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

validate a textbox to enter mobile numbers in xxx-xxx-xxxx format using c#

private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
           string sVal = textBox1.Text;
         
                if (textBox1.TextLength <= 8)
                {
                    if (!string.IsNullOrEmpty(sVal) && e.KeyCode != Keys.Back)
                    {
                        sVal = sVal.Replace("-", "");
                        string newst = Regex.Replace(sVal, ".{3}", "$0-");
                        textBox1.Text = newst;
                        textBox1.SelectionStart = textBox1.Text.Length;
                    }
                }
                 if(textBox1.TextLength >= 12)
                {
                    e.SuppressKeyPress = true;
                }
         
        }

Date Reminder

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

namespace dt_to_excel
{
    public partial class Form2 : Form
    {
        private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
        private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
        private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
        private static Microsoft.Office.Interop.Excel.Application oXL;

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", true);
            key.SetValue("BReminder", @"C:\Users\swteam\Desktop\BReminder.exe");

            string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            if (File.Exists(@"C:\Users\swteam\Desktop\Book1.xls"))
            {
                checbday();
            }
            else
            {
                createNewFile();
            }
        }

        private void checbday()
        {
           string Month;
            string Date;
            string name;
            string message;
            try
            {
                string path = @"C:\Users\swteam\Desktop\Book1.xls";
                System.Data.DataTable dt = new System.Data.DataTable();
                dt = exceldata(path);
             

                string[] columnNames = dt.Columns.Cast<DataColumn>()
                                      .Select(x => x.ColumnName)
                                      .ToArray();

                int dtRows = dt.Rows.Count;
                int totalRows = ++dtRows;
                for (int i = 2; i <= totalRows; i++)
                {
                    DataRow[] dr = dt.Select("Id = '"+i+"'");
                    DateTime Todaydate = DateTime.Today.Date;
                    if (dr.Length > 0)
                    {
                        name = dr[0]["Name"].ToString();
                        Month = dr[0]["Month"].ToString();
                        Date = dr[0]["Date"].ToString();
                        message = dr[0]["MessageDesc"].ToString();
                        if ((Convert.ToString(Todaydate.Month) == Month) && (Date == Convert.ToString(Todaydate.Day)))
                            MessageBox.Show("Reminder>>" + name + ">>" + message + " today");
                    }
                }
            }
            catch (Exception)
            { }
        }

        private System.Data.DataTable exceldata(string filePath)
        {
            System.Data.DataTable dtexcel = new System.Data.DataTable();
            bool hasHeaders = false;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //Looping Total Sheet of Xl File
            /*foreach (DataRow schemaRow in schemaTable.Rows)
            {
            }*/
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [Sheet1$]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);

            }

            conn.Close();
            return dtexcel;

        }

        public static void ReadExistingExcel()
        {
         
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                  string path = @"C:\Users\swteam\Desktop\Book1.xls";
                  if (File.Exists(@"C:\Users\swteam\Desktop\Book1.xls"))
                {
                    oXL = new Microsoft.Office.Interop.Excel.Application();
                    oXL.Visible = true;
                    oXL.DisplayAlerts = false;
                    mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    //Workbooks workbooks = oXL.Workbooks;
                    //mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    //Get all the sheets in the workbook
                    mWorkSheets = mWorkBook.Worksheets;
                    //Get the allready exists sheet
                    mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
                    Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
                    int colCount = range.Columns.Count;
                    int rowCount = range.Rows.Count;
                    for (int index = 1; index <= 1; index++)
                    {
                        int rowNo = rowCount+1;
                        mWSheet1.Cells[rowCount + index, 1] = rowNo;
                        mWSheet1.Cells[rowCount + index, 2] = textBox1.Text;
                        mWSheet1.Cells[rowCount + index, 3] = textBox2.Text;
                        mWSheet1.Cells[rowCount + index, 4] = textBox3.Text;
                        mWSheet1.Cells[rowCount + index, 5] = textBox4.Text;
                        mWSheet1.Cells[rowCount + index, 6] = textBox5.Text;
                    }
                    mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
                    mWSheet1 = null;
                    mWorkBook = null;
                    oXL.Quit();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                }
                else
                {
                    createNewFile();
                }
                  label6.Text = "Added Successfully";
               
            }
            catch (Exception w)
            {
                MessageBox.Show(w.ToString());
            }
        }

        private void clear()
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            label6.Text = "";
        }

        private void createNewFile()
        {
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }


            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = "Id";
            xlWorkSheet.Cells[1, 2] = "Name";
            xlWorkSheet.Cells[1, 3] = "Date";
            xlWorkSheet.Cells[1, 4] = "Month";
            xlWorkSheet.Cells[1, 5] = "Year";
            xlWorkSheet.Cells[1, 6] = "MessageDesc";
            xlWorkBook.SaveAs(@"C:\Users\swteam\Desktop\Book1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created..");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            clear();
        }

    }
}

dt

 string path = @"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls";
                System.Data.DataTable dt = new System.Data.DataTable();
                dt = exceldata(path);
                dataGridView1.DataSource = dt;

                string[] columnNames = dt.Columns.Cast<DataColumn>()
                                      .Select(x => x.ColumnName)
                                      .ToArray();
                DataRow[] dr = dt.Select("Id = '2'");
                DateTime Todaydate = DateTime.Today.Date;
                string dat = Todaydate.Date.ToString("dd/MM/yyyy");
                if (dr.Length > 0)
                {
                    string avalue = dr[0]["Dob"].ToString();
                }

c# to excel

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace dt_to_excel
{
    public partial class Form2 : Form
    {
        private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
        private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
        private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
        private static Microsoft.Office.Interop.Excel.Application oXL;

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            if (File.Exists(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls"))
            {
           
            }
            else
            {
                createNewFile();
            }
        }

        public static void ReadExistingExcel()
        {
           
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                  string path = @"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls";
                  if (File.Exists(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls"))
                {
                    oXL = new Microsoft.Office.Interop.Excel.Application();
                    oXL.Visible = true;
                    oXL.DisplayAlerts = false;
                    mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    //Workbooks workbooks = oXL.Workbooks;
                    //mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    //Get all the sheets in the workbook
                    mWorkSheets = mWorkBook.Worksheets;
                    //Get the allready exists sheet
                    mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
                    Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
                    int colCount = range.Columns.Count;
                    int rowCount = range.Rows.Count;
                    for (int index = 1; index <= 1; index++)
                    {
                        int rowNo = rowCount+1;
                        mWSheet1.Cells[rowCount + index, 1] = rowNo;
                        mWSheet1.Cells[rowCount + index, 2] = textBox1.Text;
                        mWSheet1.Cells[rowCount + index, 3] = textBox2.Text;
                    }
                    mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
                    mWSheet1 = null;
                    mWorkBook = null;
                    oXL.Quit();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                }
                else
                {
                    createNewFile();
                }
            }
            catch (Exception w)
            {
                MessageBox.Show(w.ToString());
            }
        }

        private void createNewFile()
        {
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }


            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = "Sheet 1 content";

            xlWorkBook.SaveAs(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created..");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

    }
}