Archive for category C#
SqlDbType.Structured [another gem in ADO.NET] and Bulk insert using Table Valued Parameters
Posted by Ashish Gupta in ADO.NET, C#, Sql Server on September 17, 2013
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 connections getting opened/closed – more chatty
Use a CSV of rows
This approach is 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
– The stored procedure would make use of a UDF to parse the CSV into a table variable
– The 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’s not elegant and involves 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, a “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 the 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@xyz.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.
Note:– Microsoft recommends this to be used when you are inserting less than 1000 rows. For more rows, consider using SqlBulkCopy.
A simple way to generate random password
Posted by Ashish Gupta in C# on May 4, 2012
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.
New APIs in System.IO
Posted by Ashish Gupta in C# on November 26, 2010
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.
[Task manager showing memory occupied by DirectoryInfo.GetFiles()]
[Task manager showing memory occupied by DirectoryInfo.EnumerateFiles()]
Therefore, when we need to enumerate large number of files, we can look at EnumerateFiles() method.
Using Unity Application Block 2.0 – The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)
Posted by Ashish Gupta in C# on October 17, 2010
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!
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.
[DebuggerDisplay] Attribute
Posted by Ashish Gupta in C# on September 20, 2010
Use sections in the config file and access them…
Posted by Ashish Gupta in C# on July 23, 2008
(
NameValueCollection)ConfigurationManager.GetSection("applicationSettings/ConnectionStrings");Visual studio 2005 Build error :- The volume for a file has been externally altered so that the opened file is no longer valid
Posted by Ashish Gupta in C# on June 2, 2008
Convert string to byte array and viceversa
Posted by Ashish Gupta in C# on May 26, 2008
string elementStringContent = System.Text.Encoding.UTF8.GetString(elementByteContent);
elementStringContent = elementStringContent.Replace(" ", "");
if (elementStringContent == string.Empty)
{
elementStringContent = "<P> </P>";
contentObject.Content = System.Text.Encoding.UTF8.GetBytes(elementStringContent);
}
Process.Start() – Open the file from the application after writing on the file
Posted by Ashish Gupta in C# on May 26, 2008
using System.Diagnostics;
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);