Delete Data From SQL Server Tables

Deleting huge set of data from a table in SQL Server by DELETE command is costly, because the deletion is logging each row in the transaction log, and it consumes noticeable resources and locks. Use TRUNCATE table command instead if you want to quickly deleting the data without locking the table and writing the log file. It took almost 1 minute to delete 2-million records in a test database with DELETE command, and only 1 second with TRUNCATE command.

Note that it's possible to rollback the data after DELETE command is executed, but not for TRUNCATE command.

Oracle Database Acess in .NET

I wanted to import a Oracle database backup to a test environment and used .NET to talk with it.

First import the database backup:
C:> imp system/orcl@odbdev file= dbbackup.dmp fromuser=orms touser=orms

Configure .NET Data Provider for Oracle:
Data Source: Oracle Database (Oracle Client);
Data Provider: .NET Framework Data Provider for Oracle;
Server Name: DBServer (This is the service name configured in tnsname.ora which is set using oracle client tools)
User Name:[User]
Password: [Password]

Connection String:

<add key="OracleDBConnString" value="user ID=[USER];Password=[PASSWORD];data source=ODBDEV;" />

Both Oracle and Microsoft make their Oracle data providers available for free. Microsoft Oracle data provider is available in .NET 1.1 and .NET 2.0 framework, but it still requires Oracle client software installed; Oracle Data Provider for .NET (ODP.NET) is included with the Oracle database installation. The recommendation is use ODP.NET since it's optimized for Oracle by Oracle, and we did find ODP.NET running faster than Microsoft's in our test.

To use ODP.NET, you need to add reference of Oracle.DataAccess.dll from GAC, import the name space, and the rest is identical to the regular ADO.NET with SQL Server:

using System.Configuration;
using Oracle.DataAccess.Client;

public class DAL
{
    public static DataTable GetAllUsers()
    {
         DataTable dtUser = new DataTable();
         string connString= ConfigurationSettings.AppSettings("OracleDBConnString");
         string sqlText = "SELECT * FROM Users";
         try
         {      
             using (OracleConnection conn = New OracleConnection(connString))
             {
                  OracleAdapter adapter= new OracleDataAdapter(sqlText, conn);
                  adapter.Fill(dtUser);
             }
         }
         catch (Exception ex)
         {
             ErrorLog.Write(ex);
         }

         return dtUser;
    }
}

Concatenate Generic String List To A String

How to convert .NET 2.0 Generic List<string> to a string like "str1, str2,..."? Of course we can loop through each items inside the generic list, and add each to a string builder. But that doesn't look very elegant. The easier way is use string.Join static method and Generic ToArray method:

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        List<string> Names = new List<string>();
        Names.Add("Bob");
        Names.Add("Rob");
        string strNames = string.Join(", ", Names.ToArray());
        Console.WriteLine(strNames);
        Console.Read();
    }
}

The result is:

Bob, Rob