Tuesday, July 21, 2009

Use Data View

protected void BindEmployee()
{
ConnectDB objConnectDB = null;
SqlConnection objSqlConnection = null;
SqlCommand objSqlCommand = null;
SqlDataAdapter objSqlDataAdapter = null;
DataSet objDataSet = null;
try
{
objConnectDB = new ConnectDB();
objSqlConnection = objConnectDB.getConnection();
objDataSet = new DataSet();
//string strSQL = "SELECT GUID,FirstName+' '+coalesce(LastName,'') as FullName from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";
string strSQL = "SELECT GUID, FirstName+' '+coalesce(LastName,'') as FullName, REPLACE(FirstName+' '+coalesce(LastName,''),'''','\\''') as FullNameRead from TblEmployeeInfo where active=1 order by FirstName+' '+coalesce(LastName,'')";

objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);

objSqlDataAdapter.Fill(objDataSet);

if (txtFilter.Text == "")
{
grdvwEmployee.DataSource = objDataSet.Tables[0];
}
else
{
//DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim() + "%'", "", DataViewRowState.CurrentRows);
DataView dv = new DataView(objDataSet.Tables[0], "FullName like '%" + txtFilter.Text.Trim().Replace("'", "") + "%'", "", DataViewRowState.CurrentRows);
grdvwEmployee.DataSource = dv;
}


grdvwEmployee.DataBind();


}
catch (Exception ex)
{
lblMsg.Text = "Error: " + ex.Message;
}
finally
{
objSqlCommand.Dispose();
objSqlDataAdapter.Dispose();
objDataSet.Dispose();
}
}
Loading...