.NET GENES

Good day! Normally, in a situation where you want to inject thousands of rows to the database using C# and SQL Server, the optimal solution would be using built-in SQLBulkCopy() function. However, you may encounter the error message "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. String or binary data would be truncated." The code below works but in some instance may throw that kind of exception. private bool SQLBulkCopy<T>( string SqlConn, List<T> inList, string tableName, ref string errMsg, int optBatchSize = 5000, bool optTableLock = true ) { SqlBulkCopyOptions lockType; SqlTransaction transaction; if (optTableLock) lockType = SqlBulkCopyOptions.TableLock; else lockType = SqlBulkCopyOptions.Default; try { using ( var connection = new SqlConnection(SqlConn)) { connection.Open(); transaction = connection.BeginTransaction(); using ( var bulkCopy ...

Good afternoon friends! In this tutorial, I will demonstrate how to read an XML file using DataTable's ReadXml() method. While reading a file using DataSet's ReadXml() method is straightforward, using the DataTable's method might cause run-time or logical bugs. One example is that when using the DataTable's ReadXml() function, the DataTable object does not contain any records or nothing at all given that fact that the method has successfully executed. To proceed, we will read an XML file below called books.xml taken from MSDN page using DataTable's ReadXml() method. <?xml version="1.0" encoding="utf-8" ?> <catalog> <book id= "bk101" > <author> Gambardella, Matthew </author> <title> XML Developer's Guide </title> <genre> Computer </genre> <price> 44.95 </price> <publish_date> 2000-10-01 </publish_date> <description...

Good Evening! There was a question raised by a developer if it's possible to compute the running total of a specific DataTable column per grouping or category. The solution is to mark the category as a flag variable that indicates the group to be calculated. The sample code below creates a DataTable object with fictitious information of hardware materials that belong to a particular group specifically PartyCode column. If a column belongs to that group, it will perform calculations. If not, reset the flag variable and total so as to perform new calculations. When computing totals for large number of records, make sure to sort the DataTable using it's category. DataTable dt = new DataTable(); dt.Columns.AddRange( new DataColumn[ 5 ] { new DataColumn( "SrNo" , typeof ( int )), new DataColumn ( "PartyName" , typeof ( string )), new DataColumn ( "ItemName" , typeof ( string )), new DataColumn ( "ChqAmt" , typeof ( int ...

Hello, A question was brought up in the forums on how to Pivot a DataTable object here. The OP has already a solution with reference to this link Cross Tab / Pivot from Data Table . An alternative solution is to utilize the features of LINQ using group by statement to achieve the desired output. This solution consists of few lines of code compared with the solution from the forum post. C# Code var query = ( from students in dt.AsEnumerable() group students by students.Field< string >( "StudID" ) into g select new { StudID = g.Key, Eng = g.Where(c => c.Field< string >( "SubSht" ) == "Eng" ).Sum(c => c.Field< double >( "Score" )), Fre = g.Where(c => c.Field< string >( "SubSht" ) == "Fre" ).Sum(c => c.Field< double >( "Score" )), Mat = g.Where(c => c.Field< string >( "SubSht" ) == "Mat" ).Sum(c => c.Field< double ...

Hello, Here's how to update DataTable value(s) using Method or Query syntax in LINQ. C# Code private void Form1_Load( object sender, EventArgs e) { DataTable dt = new DataTable( "tblEntTable" ); dt.Columns.Add( "ID" , typeof ( string )); dt.Columns.Add( "Amount" , typeof ( decimal )); dt.Rows.Add( new object [] { "1" , 100.51}); dt.Rows.Add( new object [] { "2" , 200.52}); dt.Rows.Add( new object [] { "3" , 500.24}); dt.Rows.Add( new object [] { "4" , 400.31}); dt.Rows.Add( new object [] { "5" , 600.88}); dt.Rows.Add( new object [] { "6" , 700.11}); //QuerySyntax(dt); MethodSyntax(dt); } private void QuerySyntax(DataTable dt) { var result = from row in dt.AsEnumerable() select new { ID = row.Field< string >( "ID" ), Amount = 900.23 }; DataGridView1.DataSource = result.ToList(); } private void MethodSyntax(DataTa...

Hello, The code below converts a DataTable object to generic List object using LINQ and C#. It assumes that the DataTable columns match with class properties. C# Code 1 2 3 4 5 6 7 8 var personEnumerable = table.AsEnumerable(); List<Person> ListPosition = new List<Person>(); ListPosition = ( from item in personEnumerable select new Person { ID = item.Field< int >( "ID" ).ToString(), Salary = item.Field< double >( "Salary" ).ToString() }).ToList(); Class 1 2 3 4 5 public class Person { public string ID { get ; set ; } public string Salary { get ; set ; } }

Good day! Here's the C# version of this post. Apply LINQ Grouping and Sum aggregate to a Datatable Object (VB.NET) Code: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 DataTable dt = new DataTable( "tblEntTable" ); dt.Columns.Add( "ID" , typeof ( string )); dt.Columns.Add( "amount" , typeof ( decimal )); dt.Rows.Add( new object [] { "1" , 100.51 }); dt.Rows.Add( new object [] { "1" , 200.52 }); dt.Rows.Add( new object [] { "2" , 500.24 }); dt.Rows.Add( new object [] { "2" , 400.31 }); dt.Rows.Add( new object [] { "3" , 600.88 }); dt.Rows.Add( new object [] { "3" , 700.11 }); var results = ( from orders in dt.AsEnumerable() group orders by orders.Field< string >( "ID" ) into g select new { ID = g.Key, ...

Good day! There's a question on vb forums on how to apply LINQ grouping and aggregate function sum to a datatable object. This can be achieved by familiarizing on IGrouping which is a key/value pair interface. Here's how i did it. VB.NET 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Dim dt As New DataTable( "tblEntTable" ) dt.Columns.Add( "ID" , GetType ( String )) dt.Columns.Add( "amount" , GetType ( Decimal )) dt.Rows.Add( New Object () { "1" , 100.51}) dt.Rows.Add( New Object () { "1" , 200.52}) dt.Rows.Add( New Object () { "2" , 500.24}) dt.Rows.Add( New Object () { "2" , 400.31}) dt.Rows.Add( New Object () { "3" , 600.88}) dt.Rows.Add( New Object () { "3" , 700.11}) Dim result = (From orders In dt.AsEnumerable Group orders By ID = orders.Field( Of String )( "ID" ) Into g = Group Select New With { Key ID, .Amount = g.Sum(Function(r) r...

Assuming you have a datatable that has merged contents from different datatables and you want to eliminate the datatable with distinct rows, you can use the statement below. dt.DefaultView.ToTable( true , new string [] { "id" , "name" }); Cheers!

In a scenario where I added columns using AddRange(), I encountered an error as specified by the title. Here's the C# code: table1.Columns.AddRange( new DataColumn[] { col1, col2, col3 }); table2.Columns.AddRange( new DataColumn[] { col1, col2, col3 }); Where col1, col2, col3 are DataColumn objects. What I did was to initialize columns in the addrange method. But there are other solutions in the forums. table1.Columns.AddRange( new DataColumn[] { new DataColumn( "ID" ), new DataColumn( "WEB ID" ), new DataColumn( "URL" ) }); table2.Columns.AddRange( new DataColumn[] { new DataColumn( "ID" ), new DataColumn( "WEB ID" ), new DataColumn( "URL" ) });

Sorting a data table before binding to a data control like gridview or datalist is provided by microsoft through it's predefined methods which is defaultview.sort. Example: //C# dt1.DefaultView.Sort = "Name Desc" ; //VB.NET dt1.DefaultView.Sort = "Name Desc" Where dt1 is your datatable. Or you could create a dataview object as provided by MSDN. Source: MSDN

Here's how to re-order DataColumns in a DataTable. This solves a particular problem in a project I'm currently working with. Code: 1 datatable.Columns[ "Col1" ].SetOrdinal( 1 );