Populate Datagrid Using Datareader in C#.Net

try
{
string SQLstr = "Select * from Employee";

SqlConnection con = new SqlConnection("Connection_String");

//Open Connection
con.Open();

SqlCommand com = new SqlCommand(SQLstr, con);
SqlDataReader read = com.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(read);

// finally bind the data to the grid
dataGridView1.DataSource = dt;

// Close Connection
con.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error");
}

Populate Datagrid using data adapter in C# .Net

SqlConnection OCon;

try
{
// Create Insert Query

string SQLstr = "Select * from Employee";

OCon = new SqlConnection("Connection_String");

SqlDataAdapter dataAdapter = new SqlDataAdapter(SQLstr, OCon);

// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);
BindingSource dbBindSource = new BindingSource();
dbBindSource.DataSource = table;

// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

// you can make it grid readonly.
dataGridView1.ReadOnly = true;

// finally bind the data to the grid
dataGridView1.DataSource = dbBindSource;

//ONTCon.CloseConn();

}
catch (Exception ex)
{
// Trough Error
}

Send Mail through outlook using C#

To Send Email Using Outlook you need to add refrence of Outlook to your project



using Outlook = Microsoft.Office.Interop.Outlook;

public class ODA_Genral
{
public void sendemail()
{
try
{
// Create the Outlook application.
Outlook.Application objApp = new Outlook.Application();

// Get the NameSpace and Logon information.
Outlook.NameSpace objNS = oApp.GetNamespace("mapi");

// Log on by using a dialog box to choose the profile.
objNS.Logon(System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, true);

// Create a new mail item.
Outlook.MailItem objMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);

// Set the subject.
objMsg.Subject = "Mail Send Using Outlook through C#.net";

// Set HTML Body for you mail.
String HtmlBoday;
HtmlBoday = "Send Mail Through Outlook using C#.Net";

objMsg.HTMLBody = HtmlBoday;

// Add a recipient.
Outlook.Recipients objRecips = (Outlook.Recipients)oMsg.Recipients;

// TODO: Change the recipient in the next line if necessary.
Outlook.Recipient objRecip = (Outlook.Recipient)oRecips.Add("Email ID");
objRecip.Resolve();

// Call Send Method to send the mail.
objMsg.Send();

// Log off from Outlook.
objNS.Logoff();

// Set All Objects to Null.
objRecip = null;
objRecips = null;
objMsg = null;
objNS = null;
objApp = null;
}

// Simple error handling.
catch (Exception ex)
{
Console.WriteLine("{0} Exception Occured. Mail Sending Failed", ex);
}
}
}

Filter DataTable

Most of them we come across the situation where we need to filter the data from Datatable. Below are the two ways we can achieve this functionality.

1. Filter the Datatable and save the result in Datarow

SqlConnection myConn = new SqlConnection(strConString);
string strSql = "select * from Employee";DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(strSql, myConn);

// Filters the data by "EmployeeID = 143" and returns an array of DataRow
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow[] dRow = dt.Select("EmployeeID=’143’");
for (int i = 0; i < dRow.Length; i++)
{
Response.Write("Row " + dRow[i]["Employee_Name"].ToString());
}

2. Create DataView populate it with Datatable and then use Filter Property of Dataview

DataView dtView = ds.Tables["Employee"].DefaultView;
dtView.RowFilter = "EmpployeeID = '143'";

Copy DataRows from one DataTables to another

using System;
using System.Data;
using System.Data.SqlClient;

namespace ImportRowFromDataTable
{
class Class1
{
static void Main(string[] args)
{
int tbl_1count;
int tbl_2count;
int i;

// Add code to start application here.
DataTable tbl_1 = new DataTable();
DataTable tbl_2 = new DataTable();

// Change the connection string to your server.
SqlConnection Con = new SqlConnection("Connectionstring");

// Create the DataAdapter.
SqlDataAdapter da = new SqlDataAdapter("Select * from tbl_1", Conn);

// Fill the DataSet with data.
DataSet ds = new DataSet();
da.Fill(ds, "tbl_1");
tbl_1 = ds.Tables["tbl_1"];
tbl_1count = tbl_1.Rows.Count;

// Write the number of rows in the Products table to the screen.
Console.WriteLine("Table tbl_1has " + tbl_1count.ToString() + " Rows.");

// Loop through the five rows, and disply the first column.
for (i = 0; i <= 4; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_1.Rows[i][1]);
}

// Use Clone method to copy the table tbl_1 structure.
tbl_2 = tbl_1.Clone();

// Use the ImportRow method to copy from tbl_1's data to its clone.
for (i = 0; i <= 4; ++i)
{
tbl_2.ImportRow(tbl_1.Rows[i]);
}
tbl_2count = tbl_2.Rows.Count;


// Write the number of rows in tbl_2table to the screen.
Console.WriteLine("Table tbl_2 has " +tbl_2count.ToString() + " Rows");

// Loop through the rows of tbl_2, and display.
for (i = 0; i <= tbl_2count - 1; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_2.Rows[i][1]);
}

Console.ReadLine();

}
}
}

Copy DataRows form DataTables to another

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace ImportRowFromDataTable
{
class Class1
{
static void Main(string[] args)
{
int tbl_1count;
int tbl_2count;
int i;

// Add code to start application here.
DataTable tbl_1 = new DataTable();
DataTable tbl_2 = new DataTable();

// Change the connection string to your server.
SqlConnection Con = new SqlConnection("Connectionstring");

// Create the DataAdapter.
SqlDataAdapter da = new SqlDataAdapter("Select * from tbl_1", Conn);

// Fill the DataSet with data.
DataSet ds = new DataSet();
da.Fill(ds, "tbl_1");
tbl_1 = ds.Tables["tbl_1"];
tbl_1count = tbl_1.Rows.Count;

// Write the number of rows in the Products table to the screen.
Console.WriteLine("Table tbl_1has " + tbl_1count.ToString() + " Rows.");

// Loop through the five rows, and disply the first column.
for (i = 0; i <= 4; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_1.Rows[i][1]);
}

// Use Clone method to copy the table tbl_1 structure.
tbl_2 = tbl_1.Clone();

// Use the ImportRow method to copy from tbl_1's data to its clone.
for (i = 0; i <= 4; ++i)
{
tbl_2.ImportRow(tbl_1.Rows[i]);
}
tbl_2count = tbl_2.Rows.Count;


// Write the number of rows in tbl_2table to the screen.
Console.WriteLine("Table tbl_2 has " +tbl_2count.ToString() + " Rows");

// Loop through the rows of tbl_2, and display.
for (i = 0; i <= tbl_2count - 1; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_2.Rows[i][1]);
}

Console.ReadLine();

}
}
}

Stored Procedure to Create Dynamic PIVOT in SQL

We can create dynamic PIVOT in SQL. Check the below example

CREATE PROCEDURE [dbo].[Trainings]

-- Add the parameters for the stored procedure here

AS
BEGIN

SET NOCOUNT ON;
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

--Create PIVOT table fields list in ('', '') format

SET @Columns = substring((select ',['+Training_Name+']' from View_Training group by Training_Name for xml path('')),2,8000)

--PIVOT table query

SET @SQL = 'SELECT * FROM
(Select * from View_Training) CrossTraning
PIVOT
(max(Status) for Training_Name in ('+@Columns+')) pivottable'

EXEC(@sql)
END