Abdul Wahab
Tuesday, December 5, 2017
Use of MERGE in SQL
Wednesday, September 2, 2015
Convert List<Object> to DataTable using FastMember in C#.Net
Recently, I got the requirement to convert the List<Object> to DataTable. The ad-hoc way, which we have been following to loop through the List and create the DataTable – which I don’t want to do. Therefore, I found a very good way by using FastMember. All you have to do is to download the FastMember from NuGet. Following are the steps below to download the Fastmember
Search for fastmember, and then install it.
After completing the installation, just include the FastMember namespace – and with the help of ObjectReader class we will be able to convert List to DataTabe.
IEnumerable<Customer> customerData = GetAllCustomers();
if (customerData.Count() > 0)
{
using (DataTable table = new DataTable())
{
using (var reader = ObjectReader.Create(customerData, "CustomerId","CustomerName", "CustomerAddress"))
{
table.Load(reader);
}
}
}
IEnumerable<Customer> GetAllCustomers()
{
Customer[] customers = new Customer[]
{
new Customer { CustomerId = 1, CustomerName = "Ali", CustomerAddress = "Pakistan" },
new Customer { CustomerId = 2, CustomerName = "Wakeel", CustomerAddress ="Pakistan" },
new Customer { CustomerId = 3, CustomerName = "Anthony", CustomerAddress ="England" }
};
return customers;
}
GetAllCustomers() will return the Sample data. I believe this is one of the efficient and quickest way to process the Data. I Hope you all must have enjoyed it after implementing this code :)
Friday, May 22, 2015
Bulk copy using Enterprise Library in C#
Below is the sample code, which depicts the usage of SqlBulkCopy with Enterprise Library.
int InsertRecord(ref SqlDatabase db, DbTransaction transaction) {
//here you can enter your insertion code
// db.ExecuteNonQuery("command", transaction);
return 1;
}
public void InsertDataInBulk(DataTable dtData) {
SqlDatabase db = null;
DbTransaction transaction = null;
try {
int recordId = 0;
DataTable dt = dtData;
//remove any empty rows
using(dt = dt.AsEnumerable().Where(row = > !row.ItemArray.All(field = > object.ReferenceEquals(field, DBNull.Value) | field.Equals(string.Empty))).CopyToDataTable()) {
db = (SqlDatabase) DatabaseFactory.CreateDatabase();
// insert all the records in bulk into the database.
using(DbConnection connection = db.CreateConnection()) {
connection.Open();
// open transaction only in the case where you have to do multiple database operation
using(transaction = connection.BeginTransaction()) {
//insert the file record into filelog table
recordId = InsertRecord(ref db, transaction);
if (recordId > 0) {
using(SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection) connection, SqlBulkCopyOptions.Default, (SqlTransaction) transaction)) {
//if you're unsure of the column sequence in source and destination table then it's better to map the columns
SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMumber = new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);
bulkCopy.DestinationTableName = "YourTableName";
bulkCopy.WriteToServer(dt);
}
transaction.Commit();
} else transaction.Rollback();
}
}
}
} catch (Exception ex) {
if (transaction != null) transaction.Rollback();
//Log your error
} finally {
db = null;
}
}
Bulkcopy can also be used by using SqlConnection. But the above example is other way around.
Saturday, May 9, 2015
Efficient DataTransfer via DataSet (especially over Network) through Webservice in .Net
Normally, you don't feel the difference if Dataset have one to two tables. But surely, if will boost up the performance. Considering the below example; let's suppose we have one webserivce with a webmethod of return type dataset.
get {
return SchemaSerializationMode.IncludeSchema;
}
set {
if (value != SchemaSerializationMode.IncludeSchema) {
throw ExceptionBuilder.CannotChangeSchemaSerializationMode();
}
}
}
using (DataSet ds = web.GetDataset())
Saturday, January 17, 2015
How to search into a DataTable without a loop in C#?
Basically, there are two ways to filter the records; by considering the below example.
Let's suppose, you have a DataTable with the following Definition & Data.
Customer Table (dtCustomer)
Id FirstName LastName EmailAddress Address
1 Abdul Wahab abdulwahab@xyz.com Al Nahda Street
2 Abdul Qayyuum abdulqayuum@xyz.com Al Sufoof Street
3 George Adam georgea@xyz.com Al Nahda Street
4 Hemant Singh hSign@xyz.com Jumerah Street
5 Muhammad Ali mali@xyz.com City Street
We are requiring two types of data,
1. Get all customers, who are residing near 'Al Nahda Street'.
2. Get all customers, who are residing near 'Al Nahda Street' & their first name starts with 'Abdul'.
Now, there are two ways to get the records: one is with the DataView & the other one with Linq.
Dataview Method
var dview = new DataView(dtCustomer);
Requirement 1
dview.RowFilter= "Address like '%Al Nahda%'";
var dtResult1 = dview.ToTable();
Requirement 2
dview.RowFilter= "Address like '%Al Nahda%' and FirstName like '%Abdul%' ";
var dtResult2 = dview.ToTable();
*dtResult1 & dtResult2 has the desired results.*
Linq Method (Lamda expression & Predicate)
Requirement 1
var lst1= dt.AsEnumerable().Where(delegate(DataRow s){ return s["Address"].ToString().Contains("Nahda") ; }).ToList();
OR
var lst1= dt.AsEnumerable().Where(s=> { return s["Address"].ToString().Contains("Nahda") ; }).ToList();
OR
var lst1= dt.AsEnumerable().Where(s=> s["Address"].ToString().Contains("Nahda") ; }).ToList();
Requirement 2
var lst2= dt.AsEnumerable().Where(delegate(DataRow s){ return s["Address"].ToString().Contains("Nahda") && s["Name"].ToString().Contains("Abdul") ; }).ToList();
OR
var lst2= dt.AsEnumerable().Where(s=> { return s["Address"].ToString().Contains("Nahda") && s["Name"].ToString().Contains("Abdul") ; }).ToList();
*lst1 & lst1 have the desired results, with DataRow as a type*.
Consequently, from the above two methods everyone can filter their records without using a loop.
Happy Coding....! :)
Thursday, July 8, 2010
Cursors in Sql Server
Let suppose there are two tables one with name Employee and other with name Employee_Details, during the stimulus of the project apparently you have created only one table and i.e. Employee and you have store all the information of Employee accordingly. After some time you have got a requirement that we need to separate the details of Employees into a different table. So definitely there is always a question intended that how we will be taking care of our old data and we will be migrating the data in to a new table. So a simple answer to this question is Sql Server Cursors.
Yes, by using cursors we can migrate the data from one table to another by reading records rows by rows. How we can use cursors ??
Conclusion:
So, It's good to use cursors when dealing with row by row data, but there will be performance issues if cursor is used for cumbersome data.