Appling Linq on ADO.net

// Marking sql-like queries on datatable with linq

EnumerableRowCollection query =
from employee in employees.AsEnumerable()
where employee.Field("salary") > 20
orderby employee.Field("salary")
select employee;
foreach (DataRow emp in query)
{
Response.Write(emp.Field("LastName") + ": ");
Response.Write(emp.Field("salary") + "
");
}

Editable DatagridView linked with database

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

namespace Trans829115.PL
{
public partial class frmUpdateDBViaDataGridView : Form
{
# region Private variables

private OracleConnection objcon;
private OracleCommand objcmd;
private OracleDataAdapter objadapter;
private OracleCommandBuilder objbuilder;
private DataSet objdataset;
private DataTable userTable;
private bool mAllowInsert;
private bool mAllowDelete;
private bool IsModifications = false;
ToolTip errorToolTip = new ToolTip();


#endregion

public frmUpdateDBViaDataGridView()
{
InitializeComponent();

// In Form control definition



objcon = new OracleConnection(BLL.QuerySet.ConString);
objcmd= new OracleCommand(BLL.QuerySet.TestQuery, objcon);
objadapter = new OracleDataAdapter(objcmd);
objbuilder = new OracleCommandBuilder(objadapter);
objdataset = new DataSet();
objadapter.Fill(objdataset);
userTable = objdataset.Tables[0];

userDataGridView.AllowUserToAddRows = true;
userDataGridView.AllowUserToDeleteRows = true;
btnDelete.Enabled = true;

}

private void btnDelete_Click(object sender, EventArgs e)
{
DeleteSelectedData();
}

private void frmUpdateDBViaDataGridView_Load(object sender, EventArgs e)
{
# region datagridveiw events/delegates

userDataGridView.CellBeginEdit+=new DataGridViewCellCancelEventHandler(userDataGridView_CellBeginEdit);
userDataGridView.CellEndEdit+=new DataGridViewCellEventHandler(userDataGridView_CellEndEdit);

userDataGridView.CellValidating+=new DataGridViewCellValidatingEventHandler(userDataGridView_CellValidating);
userDataGridView.CellValidated+=new DataGridViewCellEventHandler(userDataGridView_CellValidated);


userDataGridView.SelectionChanged+=new EventHandler(userDataGridView_SelectionChanged);
userDataGridView.CellEnter+=new DataGridViewCellEventHandler(userDataGridView_CellEnter);
userDataGridView.CellLeave+=new DataGridViewCellEventHandler(userDataGridView_CellLeave);




#endregion

# region custom datagridview settings

userDataGridView.MultiSelect = false;
userDataGridView.EditMode = DataGridViewEditMode.EditOnEnter;


#endregion
userDataGridView.DataSource = userTable.DefaultView;
lblRowCount.Text = "Number of records: " + userTable.Rows.Count.ToString();
userDataGridView.AllowUserToResizeColumns = true;
if (userTable.Rows.Count == 0)
{
btnDelete.Enabled = false;
btnUpdate.Enabled = false;
}

}

private void DeleteSelectedData()
{
if (MessageBox.Show("Do you really want to delete the selected record(s)?",
"Delete records", MessageBoxButtons.YesNo,
MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false)
== DialogResult.Yes)
{
try
{

int cnt = userDataGridView.SelectedRows.Count;
for (int i = 0; i < cnt; i++)
{
if (this.userDataGridView.SelectedRows.Count > 0 &&
this.userDataGridView.SelectedRows[0].Index !=
this.userDataGridView.Rows.Count - 1)
{
this.userDataGridView.Rows.RemoveAt(
this.userDataGridView.SelectedRows[0].Index);
}
}

if(objdataset.HasChanges())
{
DataTable changes = objdataset.Tables[0].GetChanges();

if (changes != null)


objadapter.Update(changes);
objdataset.Tables[0].AcceptChanges();


}

}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{

}
}
if (userTable.Rows.Count == 0)
{
btnUpdate.Enabled = false;
if (mAllowDelete) btnDelete.Enabled = false;
}
IsModifications = true;
}

private void UpdateModifiedData()
{
try
{
objcon.Open();
objadapter.UpdateCommand = objbuilder.GetUpdateCommand();
objadapter.Update(userTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
objcon.Close();
}
}


private void btnUpdate_Click(object sender, EventArgs e)
{
UpdateModifiedData();
}

private void frmUpdateDBViaDataGridView_FormClosing(object sender, FormClosingEventArgs e)
{
if (IsModifications)
if (MessageBox.Show("Do you want to save changes?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false) == DialogResult.Yes)
UpdateModifiedData();

}

private void userDataGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
//if (IsModifications)
// if (MessageBox.Show("Do you want to save changes?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false) == DialogResult.Yes)
// UpdateModifiedData();

}

private void userDataGridView_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Delete) DeleteSelectedData();
e.Handled = true; // Cancel
}

#region events

public void userDataGridView_SelectionChanged(object sender, EventArgs e)
{
//if(userDataGridView.SelectedRows.Count > 0)
//{
// DataGridViewCellStyle style = new DataGridViewCellStyle();

// style.Font = new Font(userDataGridView.Font, FontStyle.Bold);

// style.ForeColor = System.Drawing.Color.DarkBlue;


// userDataGridView.SelectedRows[0].DefaultCellStyle = style;

//}
//else
//{
// // Deformat cells
//}
}

public void userDataGridView_CellEnter(object sender, DataGridViewCellEventArgs e)
{
//Font underLineFont = new Font(userDataGridView.Font, FontStyle.Underline);
//userDataGridView[e.ColumnIndex, e.RowIndex].Style.ForeColor = Color.OrangeRed;
//userDataGridView[e.ColumnIndex, e.RowIndex].Style.Font = underLineFont;
}

public void userDataGridView_CellLeave(object sender, DataGridViewCellEventArgs e)
{
//Font BoldFont = new Font(userDataGridView.Font, FontStyle.Bold);
//userDataGridView[e.ColumnIndex, e.RowIndex].Style.ForeColor = Color.Black;
//userDataGridView[e.ColumnIndex, e.RowIndex].Style.Font = BoldFont;
}
public void userDataGridView_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{

if(((DataGridView)sender).CurrentCell.ColumnIndex==1)
{

// CurrentCell.EditedFormattedValue is key otherwise currentCell.Value will not work
if(((DataGridView)sender).CurrentCell.EditedFormattedValue.ToString().Length < 5)
{
((DataGridView)sender).CurrentRow.Cells[0].ErrorText = "Text Length could not be less than five";
e.Cancel = true;

}
else
{
((DataGridView)sender).CurrentRow.Cells[0].ErrorText = string.Empty;
}
}

}

public void userDataGridView_CellValidated(object sender, DataGridViewCellEventArgs e)
{

}


public void userDataGridView_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
// restrict column i.e. primary key to get edit
if(((DataGridView)sender).CurrentCell.ColumnIndex==0)
{
e.Cancel = true;
}

// Changing font for the Editing cells
if(((DataGridView)sender).CurrentCell.ColumnIndex!=0)
{
((DataGridView)sender).CurrentCell.Style.ForeColor = Color.OrangeRed;
((DataGridView)sender).CurrentCell.Style.Font= new Font("Arial", 10F,FontStyle.Bold);
((DataGridView)sender).CurrentCell.Style.BackColor = Color.LightGray;
((DataGridView)sender).CurrentCell.Style.SelectionForeColor = Color.Yellow;

}
}

public void userDataGridView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
// De-formattting the font for the Editing cells
if(((DataGridView)sender).CurrentCell.ColumnIndex!=0)
{
((DataGridView)sender).CurrentCell.Style.ForeColor = Color.Black;
((DataGridView)sender).CurrentCell.Style.Font= new Font("Arial", 8F, FontStyle.Bold);
((DataGridView)sender).CurrentCell.Style.BackColor = Color.White;
((DataGridView)sender).CurrentCell.Style.SelectionForeColor = Color.DarkBlue;



}
}

#endregion

}
}

Convert Text into Title Case/Sentance Case

public static string ConvertTextIntoSentanceCase(string strValue)
{
if(string.IsNullOrEmpty(strValue)) return string.Empty;


return new System.Globalization.CultureInfo("en").TextInfo.ToTitleCase(strValue.ToLower());


}