Easy Code Stuff: December 2013

Dec 31, 2013

Import Excel File To A DataGridView in C#

In a windows form application sometimes you may want to import a excel file to a data grid view. In this blog post Ill demonstrate to you guys to do that. First of all open a new project and name it as you want. Design the form simple as you can with a Text Field, Button and a Data Grid View like shown in the below image.

I've named my project as "ImportExcelFileToDataGridViewinCsharp". I've added the Text Field, Button and a Data Grid View. Just make sure you have renamed the properties as below to follow some coding standard which I followed in the code.
     * TextField Name as 'txtFilePath'
     * Button Name as 'btnLoad' and Text as 'Load Excel'
     * DataGridView Name as 'dgvExcelData'

We need another item to do this task. That is OpenFileDialog, which uses to select the relevant file from windows explorer. You can find it in the toolbox.

Now lets do the coding part. Actually what you should happen is when we click the button we need to open the "Open File Dialog" window to select the file. To do that double click the button you will get the button click event. The final code will looks like below.

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

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

        private void btnLoad_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            System.Windows.Forms.DialogResult dr = ofd.ShowDialog();
            if (dr == DialogResult.OK)
            {
                txtFilePath.Text = ofd.FileName;
                loadExcelToDataGrid(ofd.FileName);
            }
        }

        private void loadExcelToDataGrid(string strFilePath)
        {
            string sheet = "Sheet1";
            String strConnectionString = @"Data Source=" + strFilePath + "; Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;";
            OleDbConnection con = new OleDbConnection(strConnectionString);
            con.Open();
            OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + sheet + "$]", con);
            OleDbDataAdapter daCSV = new OleDbDataAdapter();
            daCSV.SelectCommand = cmdSelect;
            DataSet ds = new DataSet();
            daCSV.Fill(ds);
            dgvExcelData.DataSource = ds.Tables[0];
            con.Close();
        }

    }
}

One main thing to be note in the above code. You can see in the private method "loadExcelToDataGridView" I have assigned the "Sheet1" to the string variable sheet. This is the name of the excel sheet which you are going to upload. Simply you can use this code and improve it to capture more sheets as well as giving a selection to the user to select the sheet.

Sometimes if you run the project in a new version of visual studio you may get the below exception. That means you need to install the 2007 Office System Driver: Data Connectivity Components.

Download Source Code

Hope you got what you are looking for.

Dec 25, 2013

Add Two Numbers And Print The Output in C#

This Simple Program Can Use To Add Two User Inputs and Print The Total.
This Program Also Validate Weather User Inputs Are Numeric To Add, If Not It Will gives A error Message.

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

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

private void btnAdd_Click(object sender, EventArgs e)
{
double dNum1,dNum2;
bool chk1 = double.TryParse(txtNum1.Text, out dNum1);
bool chk2 = double.TryParse(txtNum2.Text, out dNum2);
if (!chk1)
{
txtNum1.Clear();
txtNum1.Focus();
lblOutPut.Text = "Number Is Not In Correct Format";
}
else if (!chk2)
{
txtNum2.Clear();
txtNum2.Focus();
lblOutPut.Text = "Number Is Not In Correct Format";
}
else
{
lblOutPut.Text = "Addition Is :"+(dNum1+dNum2).ToString();
}
}
}
}

How To Check A Input String Is Numeric Or Not in C#

The Easiest Way To Check Weather The Input String Is Numeric or Not Is By Converting It To A Double Variable.For This We can Use double.TryParse method.This Method Returns A Boolean Value According To the Conversion.

You Can Use Following Code Unit Check the User Input

using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

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

private void btnCheck_Click(object sender, EventArgs e)
{
double dNum1;//To Save The Inputs
bool chk = double.TryParse(txtEnterText.Text, out dNum1);
if (chk)
{
lblOutPut.Text = "Its A Numeric String";
}
else
{
lblOutPut.Text = "Its Not A Numeric String";
}
}
}
}

Load Year's To A ComboBox in C# That Are In Between Given Two Dates

This Code Unit Helps To Loads Years To A ComboBix in C#, That Are Between Two Given Dates.You Can Modify This Code Unit and You Can Get The Dates From Either User Input or Database Values.

In Order To Do That I'm Using A Function Called CmbYearLoad()
This Method Has Two String Variables Called sStartDate and sEndDate.
This Variables Stores The Values Of Dates.

private void CmbYearLoad()
{
string sStartDate = "06-22-2003";//This Value Can be Either From Database or User Input
string sEndDate = "12-15-2013";
DateTime dtStartDate = Convert.ToDateTime(sStartDate);
DateTime dtEndDate = Convert.ToDateTime(sEndDate);
int iStartYear = dtStartDate.Year;//Getting The Relevant Year Of The Date
int iEndYear = dtStartDate.Year;
CmbYear.Items.Clear();
for (int iYearCount = iEndYear; iYearCount >= iStartYear; iYearCount--)
{
CmbYear.Items.Add(iYearCount);
}
CmbYear.SelectedIndex = 0;
}

Dec 24, 2013

html Make Skype Buttons To Call Or Chat With A Contact

Skype Buttons

Copy and paste this and change my name 'hudhaifa962' into the name which you want to call.

<script type="text/javascript" src="http://www.skypeassets.com/i/scom/js/skype-uri.js"></script>
<div id="SkypeButton_Call_hudhaifa962_1">
<script type="text/javascript">
Skype.ui({
"name": "call",
"element": "SkypeButton_Call_hudhaifa962_1",
"participants": ["hudhaifa962"],
"imageSize": 32
});
</script>
</div>

Copy and paste this and change my name 'hudhaifa962' into the name which you want to chat.

<script type="text/javascript" src="http://www.skypeassets.com/i/scom/js/skype-uri.js"></script>
<div id="SkypeButton_Chat_hudhaifa962_1">
<script type="text/javascript">
Skype.ui({
"name": "chat",
"element": "SkypeButton_Chat_hudhaifa962_1",
"participants": ["hudhaifa962"],
"imageSize": 32
});
</script>

</div>

Copy and paste this and change my name 'hudhaifa962' into the name which you want to call and chat.

<script type="text/javascript" src="http://www.skypeassets.com/i/scom/js/skype-uri.js"></script>
<div id="SkypeButton_Dropdown_hudhaifa962_1">
<script type="text/javascript">
Skype.ui({
"name": "dropdown",
"element": "SkypeButton_Dropdown_hudhaifa962_1",
"participants": ["hudhaifa962"],
"imageSize": 32
});
</script>

</div>

html Skype Call In A Link Click

How Skype URIs work

In its simplest form, you can embed a hyperlink referencing a Skype URI in a webpage to place a Skype call. For example, to initiate a call to the Skype Echo / Sound Test Service, the link would be:

<a href="skype:echo123?call">Call the Skype Echo / Sound Test Service</a>

Clicking the link:

Brings the device’s Skype client into focus, starting it as necessary.
Effects auto-login or prompts your users for their Skype Name and password.
Typically opens a confirmation dialog to authorize placing the call.
Places the call.

Load Values Directly From Database To A Combo Box In C# With Another Combo Box Index Change Event

In My Example I'm Trying To Load District And Cities To Two Combo Box
First Make Sure Your Connection Is As Below
(if you have any problem in SQL Connection refer to following post
http://easycodestuff.blogspot.com/2014/01/secure-best-sql-server-connection-for-c.html)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace MyProject
{
    class DBConnection
    {
        public MySqlConnection ConnectDB()
        {
            MySqlConnection con = new MySqlConnection("Server = localhost ; Uid = root ; Pwd = ; Database = example;"); //With PhpMyAdmin Database
            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return con;
        }
    }
}

following codes help to loads the Combo Box

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MyProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            cmbDistrictLoad();//To Load Items To The District ComboBox
        }
        private void cmbDistrictLoad()
        {
            DBConnection connection = new DBConnection();
            MySqlConnection con = connection.ConnectDB();
            MySqlCommand command = con.CreateCommand();
            command.CommandText = "SELECT DistrictName From District Order By (DistrictName)";
            MySqlDataReader reader = command.ExecuteReader();
            cmbDistrict.Items.Clear();
            if (reader.HasRows == true)
            {
                while (reader.Read())
                {
                    cmbDistrict.Items.Add(reader[0].ToString());
                }
            }
            cmbDistrict.SelectedIndex = 0;//Index Changed
        }
        private void cmbCityLoad(string SelectedDistrict)
        {
            DBConnection connection = new DBConnection();
            MySqlConnection con = connection.ConnectDB();
            MySqlCommand command = con.CreateCommand();
            command.CommandText = "SELECT CityName From City WHERE DistrictId = (SELECT DistrictId From District WHERE DistrictName = '"+ SelectedDistrict +"') Order By (CityName)";
            MySqlDataReader reader = command.ExecuteReader();
            cmbCity.Items.Clear();
            if (reader.HasRows == true)
            {
                while (reader.Read())
                {
                    cmbCity.Items.Add(reader[0].ToString());
                }
            }
            cmbCity.SelectedIndex = 0;//Index Changed
        }
        private void cmbDistrict_SelectedIndexChanged(object sender, EventArgs e)
        {
            string SelectedDistrict = cmbDistrict.Items[cmbDistrict.SelectedIndex].ToString();
            cmbCityLoad(SelectedDistrict);//Calling cmbCityLoad() With selected District
        }
        private void cmbCity_SelectedIndexChanged(object sender, EventArgs e)
        {
            //You Can Write Your Code As You Wish After Select A District
        }
    }
}

Dec 7, 2013

C# Remove A DataGridRow On Button Click

private void Tab1BtnRemove_Click(object sender, EventArgs e)
{
int noofrows = datagridname.RowCount; //Count The Number Of Rows In The DataGrid

if (noofrows != 0) //If Row Count Is Zero It Wont Make An Exception If You Use An If
{
int iSelectedRow; //Create a int variable

iSelectedRow = datagridname.CurrentRow.Index;
//Get the index of the datagarid row which is selected

datagridname.Rows.RemoveAt(iSelectedRow);
//Removes That Row From The DataGrid
}
}

C# Load To ListBox From SqlServer

using System;
using System.Data;
using System.Data.SqlClient;
//Use all These Libararies If Your Connecting SqlServer

//Write These Codes In A Button Click Event Or A Function Or What Ever Event You Want To
#region Codes
string ConnectionString = @"Server=localhost;Database=dbName;Trusted_Connection=True";

SqlConnection connection = new SqlConnection(ConnectionString);

string commandtext = "Select CusNumber,CusName,CusLocation From Customer_Master";

SqlCommand command = new SqlCommand(commandtext,connection);

connection.Open();

SqlDataReader reader = comand.ExecuteReader();

ListBoxName.Items.Clear();//Code To Clear The Item Box When You Need It

while(reader.Read())
{
ListBoxName.Items.Add(reader[0].ToString()+" - "+reader[1].ToString+" - "+reader[2].ToString);
}

reader.Close();
connection.Close();
#endregion

Dec 1, 2013

C# Load Values To ComboBox From A Database

//you will need the my previous post . which i have explained about the DC Connection..
// I will call tht function and and connect the database;
// http://easycodestuff.blogspot.com/2013/11/c-sql-server-2008-database-connection.html

public void setSPCombo() //ComboBox Load Functions
{
DBConnection connection = new DBConnection();//make a object from the class DB Connection
SqlConnection con = connection.ConnectDB();
SqlCommand command = con.CreateCommand(); //creates a command with connection
command.CommandText = "SELECT ColumnName From TableName ORDER BY (ColumnName)";
//use order by if u want to order it in a-z format or 1-9 formats.

SqlDataReader reader = command.ExecuteReader(); // u have to execute through a reader..

comboname.Items.Clear(); //Fist clear the combobox because we dont want old the values
comboname.Items.Add("Select"); //write a default vale and set it .
comboname.SelectedIndex = 0;

while (reader.Read())//read until the end of reader and add all the result rows to the combo
{
comboname.Items.Add(reader[0].ToString());
}

reader.Close(); // close the reader and con..we dont wont trouble later
con.Close();
}

C# Sql Server 2008 Database Connection

using System.Data; // Include This In Your Code
using System.Data.SqlClient; // Include This In Your Code

public class DBConnections
{
public SqlConnection ConnectDB() // i create a connection in this function and return the connection
{
string ConnectionString = @"Server=localhost;Database=dbname;Trusted_Connection=True";
//u can use localhost as the server name or user ur sql server instance name

// below commented code is to connect to a remote database. u have to configure database to do this //string ConnectionString = "Data Source=192.168.0.1,1433;Network Library=DBMSSOCN;Initial //Catalog=dbname;User ID=useridhere;Password=passwordhere";

SqlConnection con = new SqlConnection(ConnectionString);

try
{
con.Open();
}
catch (SqlException)
{

}
catch (Exception ex )
{
MessageBox.Show(ex.Message);
}

return con;
}
}