Accept square brackets ([ ]) as delimiters for quoted identifies

To make life easier when embedding SQL statements as strings in C# code, EffiProz 1.1 will accept both double quotes and square buckets as delimiters for quoted identifies.

CREATE TABLE [select] ([identity] INT IDENTITY, [order] INT NOT NULL);

CREATE TABLE "select" ("identity" INT IDENTITY, "order" INT NOT NULL);

Save the Planet With EffiProz Embedded Silverlight Database

Our embedded Silverlight database will help reduce energy consumption in data centers and network devices by reducing the amount of data that need to traversed between the server and the client ... eventually will help to SAVE THE PLANET!

Execute SQL Queries in Table-Valued Functions (TVFs)

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

You can create a context connection and execute SQL queries in TVF function but not in the FillRowMethod.

[SqlFunction(FillRowMethodName = "FillRow", DataAccess= DataAccessKind.Read)]

public static IEnumerable InitMethod2()
{
List list = new List();
using (EfzConnection conn = new EfzConnection("context connection=true"))
{
EfzCommand command = conn.CreateCommand();
command.CommandText = String.Format("SELECT * FROM names");
conn.Open(); 
EfzDataReader reader = command.ExecuteReader();

while (reader.Read())
{
list.Add(new IdNameEntry(reader.GetInt32(0), reader.GetString(1)));
}
} 
return list;

} 
  
public static void FillRow(Object obj, out int? id, out string name) 

{
IdNameEntry idNameEntry = (IdNameEntry)obj;

id = idNameEntry.Id;

name = idNameEntry.Name;

} 

CREATE TABLE names(id int, name varchar(100));
INSERT INTO names VALUES (0,'car'), (1,'van'), (2,'bus');

CREATE FUNCTION testTVF2()
 RETURNS TABLE (id int, name varchar(100))
 EXTERNAL NAME ' EffiProz.Test.SelfTest.TVFFunctions.InitMethod2';

/*result
 0,car 
 1,van
 2,bus
*/
SELECT id,name from testTVF2();

CLR Table-Valued Functions (TVFs) - Usage Samples

You can use TVFs in SQL queries where a regular table name or a sub query is valid; like in join list, exists clause, in clause, etc.


Few usage samples are given below (we are using the testTVF TVF created in last post http://blog.effiproz.com/2010/06/clr-table-valued-functions-tvfs.html ),

create table prices(id int, price decimal);

insert into prices values (0, 100.45),(2, 50.6), (4,200.45);

/*result
 car,100.45
 bus,50.6
*/
select t.name, p.price from testTVF() t inner join prices p on t.id = p.id;

/*result
 car,100.45
 bus,50.6
*/
select t.name, p.price from prices p inner join testTVF() t on t.id = p.id;

/*result
 0,100.45
 2,50.6
*/
select * from prices p where exists(select * from testTVF() t where t.id=p.id);


/*result
 True
 True
 False
*/
select p.id in (select t.id from testTVF() t) from prices p;

CLR Table-Valued Functions (TVFs)

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

EffiProz 1.1 supports CLR Table-Valued Functions  in a way that is very similar to how TVFs are supported by SQL Server. Following example shows how to create a TVF in effiproz database.

First write the CLR function,

public class IdNameEntry

{
public int Id { get; set; }

public string Name { get; set; }

public IdNameEntry(int id, string name)
{

Id = id;

Name = name;
}
}
  
public class TVFFunctions 
{

[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod()
{

List list = new List();
list.Add(new IdNameEntry(0, "car"));
list.Add(new IdNameEntry(1, "van"));
list.Add(new IdNameEntry(2, "bus"));

return list;
}

public static void FillRow(Object obj, out int? id, out string name)
{

IdNameEntry idNameEntry = (IdNameEntry)obj;

id = idNameEntry.Id;

name = idNameEntry.Name;

}

}

Now we create the TVF  and execute as below,

CREATE FUNCTION testTVF()
RETURNS TABLE (id INT, name VARCHAR(100))
EXTERNAL NAME ' EffiProz.Test.SelfTest.TVFFunctions.InitMethod';

SELECT id,name FROM testTVF();

How to set permissions for CLR User-Defined Aggregate Functions

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

Users/Roles can be granted/revoked EXECUTE and ALL privileges to CLR user defined aggregates.

CREATE USER PUSER IDENTIFIED BY ''
GRANT CHANGE_AUTHORIZATION TO PUSER
GRANT EXECUTE ON AGGREGATE WeightedAvg TO puser
GRANT SELECT ON TABLE MyTable TO puser
SET SESSION AUTHORIZATION 'PUSER'

SELECT public.WeightedAvg(ItemValue, ItemWeight) FROM MyTable;

SET SESSION AUTHORIZATION 'SA'

REVOKE EXECUTE ON AGGREGATE WeightedAvg FROM puser CASCADE
SET SESSION AUTHORIZATION 'PUSER'
--following select will fail
--SELECT public.WeightedAvg(ItemValue, ItemWeight) FROM MyTable;

SET SESSION AUTHORIZATION 'SA'
GRANT  ALL PRIVILEGES ON AGGREGATE WeightedAvg TO puser
SET SESSION AUTHORIZATION 'PUSER'
SELECT public.WeightedAvg(ItemValue, ItemWeight) FROM MyTable;

SET SESSION AUTHORIZATION 'SA'

Multi-Parameter CLR User-Defined Aggregate Functions

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

Following example shows how to create a CLR User-Defined Aggregate Function in EffiProz-SL database.
This is very similar to 2nd example given for SQL server on http://msdn.microsoft.com/en-us/library/ms131056.aspx

CLR aggregate function definition,

[SqlUserDefinedAggregate]
    public struct WeightedAvg
    {     
        private long sum;        
        private int count;

     
        public void Init()
        {
            sum = 0;
            count = 0;
        }

    
        public void Accumulate(int? Value, int? Weight)
        {
            if (Value.HasValue && Weight.HasValue)
            {
                sum += (long)Value.Value * (long)Weight.Value;
                count += (int)Weight.Value;
            }
        }    

      
        public int? Terminate()
        {
            if (count > 0)
            {
                int value = (int)(sum / count);
                return value;
            }
            else
            {
                return null;
            }
        }
    }

Then we can create a new aggregate function with CREATE AGGREGATE statement,

CREATE AGGREGATE WeightedAvg (val int, weight int) RETURNS int
 EXTERNAL NAME 'EffiProz.Test.SelfTest.WeightedAvg';

Now we can create a sample table and call the new aggregate function,

CREATE TABLE MyTable (ItemValue int, ItemWeight int);

INSERT INTO MyTable VALUES(1, 4);
INSERT INTO MyTable VALUES(6, 1);

SELECT public.WeightedAvg(ItemValue, ItemWeight) FROM MyTable;

Finally we can drop the aggregate with "DROP AGGREGATE" statement,

DROP AGGREGATE WeightedAvg;

CLR User-Defined Aggregate Functions

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

Following example shows how to create a CLR User-Defined Aggregate Function in EffiProz-SL database.

CLR aggregate function must have "System.Data.EffiProz.Clr.SqlUserDefinedAggregate" custom attribute and three standard methods "Init", "Accumulate" and "Terminate" as shown below (Which very similar to what is given for SQL server on http://msdn.microsoft.com/en-us/library/ms131056.aspx ),

[SqlUserDefinedAggregate]
public class Concatenate 
{ 
private StringBuilder intermediateResult;
public void Init()

{
this.intermediateResult = new StringBuilder();
}

public void Accumulate(String value)
{
if (value == null)

{

return;

}

this.intermediateResult.Append(value).Append('
');

}

public String Terminate()

{
string output = string.Empty;

if (this.intermediateResult != null

&& this.intermediateResult.Length > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

}

return output; 

}

}

Then we can create a new aggregate function with CREATE AGGREGATE statement,

[
CREATE AGGREGATE MyAgg (input varchar(200)) RETURNS varchar(100000)
 EXTERNAL NAME 'EffiProz.Test.SelfTest.Concatenate';

Now we can create a sample table and call the new aggregate function,

CREATE TABLE BookAuthors
 (
 BookID   int       NOT NULL,
 AuthorName    varchar(200) NOT NULL
 );

INSERT INTO BookAuthors VALUES(1, 'Johnson');
INSERT INTO BookAuthors VALUES(2, 'Taylor');
INSERT INTO BookAuthors VALUES(3, 'Steven');
INSERT INTO BookAuthors VALUES(2, 'Mayler');
INSERT INTO BookAuthors VALUES(3, 'Roberts');
INSERT INTO BookAuthors VALUES(3, 'Michaels');

SELECT BookID, public.MyAgg(AuthorName) FROM BookAuthors GROUP BY BookID;

Finally we can drop the aggregate with "DROP AGGREGATE" statement,

DROP AGGREGATE MyAgg;

Instance methods as CLR Stored Procedures

EffiProz-SL now supports defining instance methods with "System.Data.EffiProz.Clr.SqlProcedure" attribute as CLR stored procedures.

[SqlProcedure]
public  void Insert_Students(int id, string name)
{
using (EfzConnection conn = new EfzConnection("context connection=true"))
            {
                EfzCommand command = conn.CreateCommand();
                command.CommandText = String.Format("Insert into student3 values({0},'{1}'); commit;", id, name);
                conn.Open();
                command.ExecuteNonQuery();

                command.CommandText = "Select * from student3;";
                EfzDataReader reader = command.ExecuteReader();
                EfzContext.Pipe.Send(reader);
            }
        }

Context connection inside a CLR Stored Procedure

Following example shows how to create a new EfzConnection inside a stored procedure with "Context Connection" option. This also shows how to return a data reader from a CLR stored procedure.

string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB5; User=sa; Password=;";

            using (EfzConnection cnn = new EfzConnection(connString))
            {
                cnn.Open();

                using (EfzCommand cmd = cnn.CreateCommand())
                {
                    string sql = "CREATE TABLE test(id INT, name VARCHAR(100));\n";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    sql = "CREATE PROCEDURE FetchRecord3(IN id INT,  IN name VARCHAR(100))\n" +
                            "MODIFIES SQL DATA\n" +
                   "LANGUAGE DOTNET\n EXTERNAL NAME 'CLRStoredProcedure.ClrRoutines.ContextConnectionTest'";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    try
                    {
                        cmd.CommandText = "call FetchRecord3(10,'mark');";
                        EfzDataReader reader = cmd.ExecuteReader();
                        StringBuilder result = new StringBuilder();
                        while (reader.Read())
                        {
                            result.Append(String.Format("ID: {0} Name: {1} \n", reader.GetInt32(0), reader.GetString(1)));
                        }

                        result_lbl.Content = result;
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }

CLR Stored Procedure definition,

public static void ContextConnectionTest(int id, string name)
        {
            using (EfzConnection conn = new EfzConnection("context connection=true"))
            {
                EfzCommand command = conn.CreateCommand();
                command.CommandText = String.Format("Insert into test values({0},'{1}'); commit;", id, name);
                conn.Open();
                command.ExecuteNonQuery();

                command.CommandText = "Select * from test;";
                EfzDataReader reader = command.ExecuteReader();
                EfzContext.Pipe.Send(reader);
            }
        }

Return multiple manual data records from a CLR stored procedure

Following example shows how to return multiple manually created data record from a CLR stored procedure.

string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB2; User=sa; Password=;";


using (EfzConnection cnn = new EfzConnection(connString))

{

cnn.Open();

using (EfzCommand cmd = cnn.CreateCommand())

{

string sql = "CREATE PROCEDURE FetchRecord2()\n" +

"MODIFIES SQL DATA\n" +

"LANGUAGE DOTNET\n EXTERNAL NAME 'CLRStoredProcedure.ClrRoutines.SendTransientResultSet2'";

cmd.CommandText = sql;

cmd.ExecuteNonQuery();

cmd.CommandText = "call FetchRecord2();";

EfzDataReader reader = cmd.ExecuteReader();

StringBuilder result = new StringBuilder();

while (reader.Read())

{

result.Append(String.Format("ID: {0} Name: {1} \n", reader.GetInt32(0), reader.GetString(1)));

}
result_lbl.Content = result;

}

} 

CLR Stored Procedure declaration,

public static void SendTransientResultSet2()


{

EfzDataRecord record = new EfzDataRecord(new EfzMetaData("id", EfzSqlType.Int),

new EfzMetaData("name", EfzSqlType.VarChar, 128));

record.SetInt32(0, 0);

record.SetString(1, "Van");

EfzContext.Pipe.SendResultsStart(record);
record = new EfzDataRecord(new EfzMetaData("id", EfzSqlType.Int),

new EfzMetaData("name", EfzSqlType.VarChar, 128));

record.SetInt32(0, 1);

record.SetString(1, "Car");

EfzContext.Pipe.SendResultsRow(record);
EfzContext.Pipe.SendResultsEnd();

}

Download latest development release from http://www.effiproz.com/downloads/effiproz_sl4_1_1_dev.zip

Return manually created data record from a CLR stored procedure

Upcoming EffiProz-SL 1.1 supports a API, similar to what is provided by SQL Server, for CLR Stored Procedures to interface with EffiProz database engine.

Following example shows how to return a single data record from a CLR stored procedure.

string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB1; User=sa; Password=;";

            using (EfzConnection cnn = new EfzConnection(connString))
            {
                cnn.Open();

                using (EfzCommand cmd = cnn.CreateCommand())
                {                   
                    string sql = "CREATE PROCEDURE FetchRecord()\n" +
                            "MODIFIES SQL DATA\n" +
                   "LANGUAGE DOTNET\n EXTERNAL NAME 'CLRStoredProcedure.ClrRoutines.SendTransientResultSet'";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();


                    cmd.CommandText = "call FetchRecord();";                   
                    EfzDataReader reader = cmd.ExecuteReader();
                    StringBuilder result = new StringBuilder();
                    while (reader.Read())
                    {
                        result.Append(String.Format("Message: {0} \n", reader.GetString(0)));
                    }

                    result_lbl.Content = result;
                }
            }

CLR Stored Procedure declaration,

public static void SendTransientResultSet()
        {

            EfzDataRecord record = new EfzDataRecord(new EfzMetaData("stringcol", EfzSqlType.VarChar, 128));

            record.SetString(0, "Hello World!");

            EfzContext.Pipe.Send(record);
        }

Download latest development release from http://www.effiproz.com/downloads/effiproz_sl4_1_1_dev.zip