ADO.NET Accelerator

Comparison against ADO.NET

The following code snippets will show you how many lines of ADO.NET code you can reduce using ADO.NET Accelerator. For the following code snippets you get a 60% ADO.NET code reduction.

Create a dataset

Using ADO.NET Accelerator to create a dataset reduces your ADO.NET code from 8 lines of code to just 1. 87% less code.

ADO.NET Accelerator (1 line)ADO.NET (8 lines)

    1  DataSet customerDataset = DbManager.Instance.CreateDataSet(0, "DemoDb");


Execute a SQL query

Using ADO.NET Accelerator to execute a SQL query, which contains 2 parameters, reduces your ADO.NET code from 11 lines of code to just 4. 63% less code.

ADO.NET Accelerator (4 line)ADO.NET (11 lines)

    1     CiOrderedDictionary paramValues = new CiOrderedDictionary();

    2 

    3     paramValues.Add("FirstName", "Matt");

    4     paramValues.Add("CustomerId", 1);

    5 

    6     int affectedRecords = DbManager.Instance.ExecuteNonQuery(3, "DemoDb", paramValues);


Create a data reader using a select SQL query

Using ADO.NET Accelerator to create a data reader, using a select SQL query which contains 1 parameter, reduces your ADO.NET code from 7 lines of code to just 3. 57% less code.

ADO.NET Accelerator (3 lines)ADO.NET (7 lines)

    1     CiOrderedDictionary paramValues = new CiOrderedDictionary();

    2 

    3     paramValues.Add("CustomerId", 1);

    4 

    5     IDataReader ordersByCustomerReader = DbManager.Instance.ExecuteReader(1, "DemoDb", paramValues);


Get a scalar value from a select SQL query

Using ADO.NET Accelerator to get a scalar value, using a select SQL query, reduces your ADO.NET code from 5 lines of code to just 1. 80% less code.

ADO.NET Accelerator (1 line)ADO.NET (5 lines)

    1     int customerCount = (int)DbManager.Instance.ExecuteScalar(12, "DemoDb");       


Execute multiple SQL queries using a transaction

Using ADO.NET Accelerator to execute multiple SQL queries using a transaction reduces your ADO.NET code from 26 lines of code to 23. 11% less code.

ADO.NET Accelerator (23 lines)ADO.NET (26 lines)

    1     IDbConnection connection = null;

    2     IDbTransaction transaction = null;

    3     DbManager.Instance.CreateTransaction("DemoDb", out connection, out transaction);

    4 

    5     try

    6     {

    7         CiOrderedDictionary paramValues = new CiOrderedDictionary();

    8         paramValues.Add("FirstName", "Tim");

    9         paramValues.Add("LastName", "Smith");

   10         DbManager.Instance.ExecuteNonQuery(4, "DemoDb", paramValues, connection, transaction);

   11 

   12         paramValues.Clear();

   13         paramValues.Add("FirstName", "Scott");

   14         paramValues.Add("LastName", "Dawson");

   15         DbManager.Instance.ExecuteNonQuery(4, "DemoDb", paramValues, connection, transaction);

   16 

   17         transaction.Commit();

   18     }

   19     catch

   20     {

   21         transaction.Rollback();

   22     }

   23     finally

   24     {

   25         connection.Close();

   26     }


Get a value using an output parameter

Using ADO.NET Accelerator to get a value from an output parameter reduces your ADO.NET code from 11 lines of code to 4. 64% less code.

ADO.NET Accelerator (4 lines)ADO.NET (11 lines)

    1     CiOrderedDictionary paramValues = new CiOrderedDictionary();

    2 

    3     paramValues.Add("Guid", DBNull.Value);

    4     DbManager.Instance.ExecuteNonQuery(13, "DemoDb", paramValues);       

    5     string guidValue = paramValues["Guid"].ToString();


SQL code used by ADO.NET Accelerator

ADO.NET Accelerator uses for the code snippets a XML file to store the SQL code.

    1 <XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">

    2   <SqlStatement>

    3     <SqlStatementId>0</SqlStatementId>

    4     <SqlStatementName>Customers</SqlStatementName>

    5     <Description />

    6     <CommandText><![CDATA[SELECT * FROM CUSTOMER ORDER BY CUSTOMERID]]></CommandText>

    7     <SqlStatementType>Text</SqlStatementType>

    8     <LastModification>2007-01-22T00:00:00-06:00</LastModification>

    9   </SqlStatement>

   10   <SqlStatement>

   11     <SqlStatementId>1</SqlStatementId>

   12     <SqlStatementName>Customer orders</SqlStatementName>

   13     <Description />

   14     <CommandText><![CDATA[SELECT * FROM [ORDER] WHERE CustomerId = ?]]></CommandText>

   15     <SqlStatementType>Text</SqlStatementType>

   16     <LastModification>2007-01-22T00:00:00-06:00</LastModification>

   17   </SqlStatement>

   18   <SqlStatement>

   19     <SqlStatementId>3</SqlStatementId>

   20     <SqlStatementName>Update customer</SqlStatementName>

   21     <Description />

   22     <CommandText><![CDATA[UPDATE CUSTOMER SET

   23   FirstName = ?

   24 WHERE CustomerId = ?]]></CommandText>

   25     <SqlStatementType>Text</SqlStatementType>

   26     <LastModification>2007-01-22T00:00:00-06:00</LastModification>

   27   </SqlStatement>

   28   <SqlStatement>

   29     <SqlStatementId>4</SqlStatementId>

   30     <SqlStatementName>New customer</SqlStatementName>

   31     <Description />

   32     <CommandText><![CDATA[INSERT INTO Customer (FirstName, LastName) VALUES (?, ?)]]></CommandText>

   33     <SqlStatementType>Text</SqlStatementType>

   34     <LastModification>2007-01-22T00:00:00-06:00</LastModification>

   35   </SqlStatement>

   36   <SqlStatement>

   37     <SqlStatementId>12</SqlStatementId>

   38     <SqlStatementName>All Customer Count</SqlStatementName>

   39     <CommandText><![CDATA[SELECT COUNT(*) FROM CUSTOMER]]></CommandText>

   40     <SqlStatementType>Text</SqlStatementType>

   41     <LastModification>2008-10-01T00:00:00-05:00</LastModification>

   42   </SqlStatement>

   43   <SqlStatement>

   44     <SqlStatementId>13</SqlStatementId>

   45     <SqlStatementName>Create GUID</SqlStatementName>

   46     <Description />

   47     <CommandText><![CDATA[CreateGuid]]></CommandText>

   48     <SqlStatementType>StoredProcedure</SqlStatementType>

   49     <LastModification>2008-10-01T00:00:00-05:00</LastModification>

   50   </SqlStatement>

   51   <SqlParameter>

   52     <SqlParameterId>9</SqlParameterId>

   53     <SqlStatementId>4</SqlStatementId>

   54     <SqlParameterName>FirstName</SqlParameterName>

   55   </SqlParameter>

   56   <SqlParameter>

   57     <SqlParameterId>10</SqlParameterId>

   58     <SqlStatementId>4</SqlStatementId>

   59     <SqlParameterName>LastName</SqlParameterName>

   60   </SqlParameter>

   61   <SqlParameter>

   62     <SqlParameterId>12</SqlParameterId>

   63     <SqlStatementId>1</SqlStatementId>

   64     <SqlParameterName>CustomerId</SqlParameterName>

   65   </SqlParameter>

   66   <SqlParameter>

   67     <SqlParameterId>14</SqlParameterId>

   68     <SqlStatementId>3</SqlStatementId>

   69     <SqlParameterName>FirstName</SqlParameterName>

   70   </SqlParameter>

   71   <SqlParameter>

   72     <SqlParameterId>15</SqlParameterId>

   73     <SqlStatementId>3</SqlStatementId>

   74     <SqlParameterName>CustomerId</SqlParameterName>

   75   </SqlParameter>

   76   <SqlParameter>

   77     <SqlParameterId>16</SqlParameterId>

   78     <SqlStatementId>13</SqlStatementId>

   79     <SqlParameterName>Guid</SqlParameterName>

   80   </SqlParameter>

   81   <SqlParameterProperty>

   82     <PropertyName>OleDbType</PropertyName>

   83     <PropertyValue>VarChar</PropertyValue>

   84     <SqlParameterID>9</SqlParameterID>

   85   </SqlParameterProperty>

   86   <SqlParameterProperty>

   87     <PropertyName>Size</PropertyName>

   88     <PropertyValue>0</PropertyValue>

   89     <SqlParameterID>9</SqlParameterID>

   90   </SqlParameterProperty>

   91   <SqlParameterProperty>

   92     <PropertyName>OleDbType</PropertyName>

   93     <PropertyValue>VarChar</PropertyValue>

   94     <SqlParameterID>10</SqlParameterID>

   95   </SqlParameterProperty>

   96   <SqlParameterProperty>

   97     <PropertyName>Size</PropertyName>

   98     <PropertyValue>0</PropertyValue>

   99     <SqlParameterID>10</SqlParameterID>

  100   </SqlParameterProperty>

  101   <SqlParameterProperty>

  102     <PropertyName>OleDbType</PropertyName>

  103     <PropertyValue>Integer</PropertyValue>

  104     <SqlParameterID>12</SqlParameterID>

  105   </SqlParameterProperty>

  106   <SqlParameterProperty>

  107     <PropertyName>Size</PropertyName>

  108     <PropertyValue>0</PropertyValue>

  109     <SqlParameterID>12</SqlParameterID>

  110   </SqlParameterProperty>

  111   <SqlParameterProperty>

  112     <PropertyName>OleDbType</PropertyName>

  113     <PropertyValue>VarChar</PropertyValue>

  114     <SqlParameterID>14</SqlParameterID>

  115   </SqlParameterProperty>

  116   <SqlParameterProperty>

  117     <PropertyName>OleDbType</PropertyName>

  118     <PropertyValue>Integer</PropertyValue>

  119     <SqlParameterID>15</SqlParameterID>

  120   </SqlParameterProperty>

  121   <SqlParameterProperty>

  122     <PropertyName>OleDbType</PropertyName>

  123     <PropertyValue>VarChar</PropertyValue>

  124     <SqlParameterID>16</SqlParameterID>

  125   </SqlParameterProperty>

  126 </XmlSqlDataStore>