Archive for category C#

SqlDbType.Structured [another gem in ADO.NET] and Bulk insert using Table Valued Parameters

Use case
You want to insert multiple rows (say from 2 to 999 rows) from your .NET application to a database table.
You are using .NET Framework 3.5 or above with Sql Server 2008 or above.

Approaches
Insert one row at a time
As you can imagine, this would have a performance hit because of too many connection getting opened/closed – more chatty
Use a CSV of rows
This approach is more chunkier than the above approach. Overall, below is the approach :-
– Create a comma separated string of rows from the application
– Send CSV to a stored procedure from application
– Stored procedure would make use of a UDF to parse the CSV to a table variable
– Stored procedure would insert data from the table variable to the actual table
Use SqlDbType.Structured
The above approach solves the problem of the application being too chatty to the database. However, It’t not elegant and involve too much of “manual” parsing of string.

In .NET Framework 3.5 and onwards, SqlCommand can make use of another parameter type named SqlDbType.Structured which enables a .NET application to send a DataTable (yes, an “System.Data.DataTable” object) directly to the stored procedure which can be directly used as a table inside the stored procedure as If It was a table in database.

In the below example we will send a DataTable of email addresses from .NET application to a database stored procedure which would insert data directly from this table to the actual table.

Steps
Database changes :-
/*Create a user defined table type which will hold the contents of the DataTable passed from the application.
This will be used as a Table Valued Parameter.
*/

CREATE TYPE [dbo].[EmailAddressList] AS TABLE
(
[EmailAddress] [NVARCHAR](100) NULL
);

/*Create the actual table to which we will insert data from the DataTable*/
CREATE TABLE EmailAddressDetails
(
EmailAddress NVARCHAR(100),
CreatedOn DateTime DEFAULT GetDate()
)

/*Create the stored procedure which will be called from the application*/
CREATE PROCEDURE EmailAddresses_InsertBatch
@EmailAddressBatch [EmailAddressList] READONLY
AS
BEGIN
INSERT INTO EmailAddressDetails (EmailAddress)
SELECT E.EmailAddress FROM @EmailAddressBatch E
END

Application changes
//Function to create a DataTable with dummy email addresses
//The DataTable created here should match the schema of the User defined table type created above.

private DataTable CreateEmailAddressDataTable()
{
DataTable emailAddressDT = new DataTable();
emailAddressDT.Columns.Add(“EmailAddress”, typeof(string));
int emailAddrressCount = 100;
for (int i = 0; i < emailAddrressCount; i++)
{
DataRow row = emailAddressDT.NewRow();
row[“EmailAddress”] = i.ToString() + “.something@lpl.com”;
emailAddressDT.Rows.Add(row);
}
return emailAddressDT;
}

//Function to call the stored procedure with DataTable
private void AddEmailAddressToDb()
{
DataTable dataTable = CreateEmailAddressDataTable();
string connectionString = “Server=YourServerName;Database=YourDatabaseName;UserId=ashish;Password=ashish;”;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = “EmailAddresses_InsertBatch”;
command.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter(“@EmailAddressBatch”, SqlDbType.Structured);
                   param.TypeName = “dbo.EmailAddressList”;
param.Value = dataTable;
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
}
}

The above line highlighted in green is the gem in ADO.NET. 🙂 When we send DataTable the stored procedure, It populates the user defined table type which we can directly use in the stored procedure – inserting from it to the actual table in this case. No parsing of CSV in UDF – takes away big pain when you have a complex structure.
image
Note :- Microsoft recommends this to be used when you are inserting less than 1000 rows. For more rows, consider using SqlBulkCopy.

,

1 Comment

A simple way to generate random password

I took que from Ambuj’s post to generate a simple password which is more random. It simply takes random specified number of characters from a guid value. Following is the code.

static string GenerateRandomPassword(int numberOfCharactersInPassword)
{
           if (numberOfCharactersInPassword <= 0 || numberOfCharactersInPassword > 32)
           {
               throw new ArgumentException(“A password of only length 1 to 32 can be generated.”);
           }
           string guidWithoutDashes = Guid.NewGuid().ToString(“n”);
           //Console.WriteLine(“Guid without dashes :- ” + guidWithoutDashes);
           var chars = new char[numberOfCharactersInPassword];
           var random = new Random();
           for (int i = 0; i < chars.Length; i++)
           {
               chars[i] = guidWithoutDashes[random.Next(guidWithoutDashes.Length)];
           }
           //Console.WriteLine(“Random password :- ” + new string(chars));
           return new string(chars);
}

The complete project to test quickly can be downloaded from here.

Leave a comment

New APIs in System.IO

Have a look at some of the new APIs in introduced in System.IO which makes some of the tasks efficient – both performance as well as memory wise.

For example, there are instances when we need the total number of files in a given directory (recursively). In those instances, we think of following two methods :-

System.IO.DirectoryInfo.GetFiles() which returns FileInfo[]

System.IO.Directory.GetFiles() which returns string[] (which contains names)

.NET 4.0 introduced some new methods for IO and two of which are :-

System.IO.DirectoryInfo.EnumerateFiles() which returns IEnumerable<FileInfo>

System.IO.Directory. EnumerateFiles() which returns IEnumerable<string> (which contains names)

Why these two new methods? Because they are efficient because when we use them we don’t have to wait for thw whole FileInfo[] or String[] to return before we could access the collection. From MSDN:-

TheEnumerateFilesandGetFilesmethods differ as follows: When you useEnumerateFiles, you can start enumerating the collection ofFileInfoobjects before the whole collection is returned; when you useGetFiles, you must wait for the whole array ofFileInfoobjects to be returned before you can access the array. Therefore, when you are working with many files and directories,EnumerateFilescan be more efficient.”

I did some performance comparison ( a small project attached ) and following are the results :-

For 56505 number of files :-

Methods Time taken (in milliseconds) Memory used (in Kilobytes)
DirectoryInfo.GetFiles() 3393 31004
DirectoryInfo.EnumerateFiles() 3365 6223
Directory.GetFiles() 3001 24888
Directory.EnumerateFiles() 2961 6992

As you can see DirectoryInfo.EnumerateFiles() is faster than DirectoryInfo.GetFiles() and more importantly occupies almost 1/5 of memory in comparison.

Also, regarding Directory.GetFiles(), It is used to fetch the names of the files. But as we can see one can use Directory.EnumerateFiles() which is faster and occupies almost 1/4th of the memory in comparison.

clip_image002

[Task manager showing memory occupied by DirectoryInfo.GetFiles()]

clip_image004

[Task manager showing memory occupied by DirectoryInfo.EnumerateFiles()]

Therefore, when we need to enumerate large number of files, we can look at EnumerateFiles() method.

2 Comments

Using Unity Application Block 2.0 – The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

I was trying to use Unity Apllication Block this morning and following is the code I wrote:-

Interfaces (In the assembly named “Interfaces”. In project :- Interfaces)
namespace Interfaces
{
public interface IDoSomeWork1
{
string DoSomeWork1();
}
}

namespace Interfaces
{
public interface IDoSomeWork2
{
string DoSomeWork2();
}
}

Dependencies (In the assembly named “Entities”. In project :- Entities)
namespace Entities
{
public class ClassB : IDoSomeWork1
{
public string DoSomeWork1()
{
return this.ToString();
}
}
}

namespace Entities
{
public class ClassC : IDoSomeWork2
{
public string DoSomeWork2()
{
return this.ToString();
}
}
}

Class (In project :- UsingUnity)
public class ClassA
{
[Dependency]
public IDoSomeWork1 DoSomeWork1 { get; set; }
[Dependency]
public IDoSomeWork2 DoSomeWork2 { get; set; }

public void SomeMethodInClassA()
{
Console.WriteLine(DoSomeWork1.DoSomeWork1());
Console.WriteLine(DoSomeWork2.DoSomeWork2());
}
}

App.Config (In a console application project :- ConsoleUsingUnity)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="unity"
type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection,
Microsoft.Practices.Unity.Configuration" />
</configSections>
<unity>
<containers>
<container>
<types>
<type type="Interfaces.IDoSomeWork1, Interfaces"
mapTo="Entities.ClassB, Entities" />
<type type="Interfaces.IDoSomeWork2, Interfaces"
mapTo="Entities.ClassC, Entities" />
</types>
</container>
</containers>
</unity>
</configuration>

The client (In a console application project :- ConsoleUsingUnity)
public class Class1
{
static void Main(string[] args)
{
IUnityContainer container = new UnityContainer();

// Load from config file
UnityConfigurationSection section = (UnityConfigurationSection)ConfigurationManager.GetSection(“unity”);
section.Configure(container);

ClassA classA = container.Resolve<ClassA>();
classA.SomeMethodInClassA();
}
}

And when I run the client, I get the following error at section.Configure(container);:-

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

Solution
I must state that the code above didn’t give me any problem (build error etc.). It just gave me the error I stated in my question. The problem with Unity at this point of time is that It does not provide which assembly or a which types in the assembly could not be loaded. This is a requested feature.

In my case It was a missing assembly problem. I didn’t reference Entities assembly in to the client application project. It seems that that “Entities” assembly could be resolved only at the run-time (since it didn’t give me any compile time error). However, the run-time error was also not useful at all.

I had a look a Fusion Log viewer (It should be in the .NET SDK folder). What a gem of an utility It is. It can log all kind of assembly bindings (all or only failures) and It give a very neat description of which assembly could not load. Very helpful! FailedToLoadAssemblyDetected

Log

I added the the reference of the “Entities” assembly to the client application and It was able to call methods from the dependencies (ClassB and ClassC).

So, next time, you get this “The given assembly name or codebase was invalid” error, try Fusion Log Viewer. It wont help you in finding which types couldn’t be loaded. However,at least you will be sure all your assemblies are getting loaded correctly.

1 Comment

[DebuggerDisplay] Attribute

 "[DebuggerDisplay]" [http://chrisfulstow.com/customising-visual-studio-debugger-datatips-with-debuggerdisplay/]. Something you would save you few seconds (and more) while you are debugging.

Leave a comment

Use sections in the config file and access them…

(NameValueCollection)ConfigurationManager.GetSection("applicationSettings/ConnectionStrings");

for the following XML fragment in the config file:-
 
<applicationSettings>
<ConnectionStrings>
<add key="DevConnectionstring" value="server=myserver1;database=mydatabase;uid=ashish;pwd=password"/>
<add key="QAConnectionstring" value="server=myserver2;database=mydatabase;uid=ashish;pwd=password"/>
<add key="ProdConnectionstring" value="server=myserver3;database=mydatabase;uid=ashish;pwd=password"/>
</ConnectionStrings>
</applicationSettings>

Leave a comment

Visual studio 2005 Build error :- The volume for a file has been externally altered so that the opened file is no longer valid

Just clean the solution and rebuild.
 
Just dont have any idea why the above error happened this morning when I tried to add some code to a windows service and tried to build the solution.But it has a simple resolution,just clean the solution. 🙂  [Solution Explorer > Right click on the Solution> Select "Clean Solution"].
 
 
 

Leave a comment

Convert string to byte array and viceversa

                   byte[] elementByteContent= contentObject.Content; //  this  byte array content
                    string elementStringContent = System.Text.Encoding.UTF8.GetString(elementByteContent);
                    elementStringContent = elementStringContent.Replace(" ", "");
                    if (elementStringContent == string.Empty)
                    {
                        elementStringContent = "<P>&nbsp;</P>";
                        contentObject.Content = System.Text.Encoding.UTF8.GetBytes(elementStringContent);
                    }

Leave a comment

Process.Start() – Open the file from the application after writing on the file

There are instances where you write to a file (say XML/Text file/word) fo debugging/testing etc.In those scenarios,your program finishes writing to the file and then you open the file manually in the application(IE/Notepad/Microsoft word) to see what the program has written in that file.You can actually launch that application after you finish writing to that file from with in the program making the whole process quick.The file opens in its default application right after you finish debugging without you needing to manually open it.
 

The steps:- (2 lines):-
 
a) Include the namespace:-
using System.Diagnostics;

b) Start the process for the saved file:-
//
//Code to save the file
//
 
// Following line would open the saved file in its default application
Process.Start("SavedFullFilePath");
 
 
Example:-
// Code to save the file
XmlDocument Xdoc = new XmlDocument();
string folderPath = @"C:\Ashish\";
string upload = folderPath + "2007.docx";
string savename = folderPath + "WordPackage.xml";
ZipPackage zipPackage = null;
byte[] packageData = null;
Stream stream;
packageData = GetBytesFromFile(upload);
stream =
new MemoryStream(packageData);
if (stream != null)
zipPackage = GetZipPackageFromStream(stream);
Xdoc = RRD.DSA.SCP.OfficeAssembler.
WordToXyXmlAssembler.ConvertToXyXml(zipPackage);
Xdoc.Save(savename);

// Following line would open the saved file in its default application (Internet explorer)
Process
.Start(strSave);

 
 

Leave a comment

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands