This project is read-only.
HOW TO USE DBIDIOM FOR ADO.NET CORE
------------------------------------------------

using System;
using System.Collections.Generic;

using DbIdiom;
using DbIdiom.SqlClient;
using DbIdiom.SqlClient.Extensions;

namespace DbIdiomSamples
{
//SqlClient samples using DbIdiom

/*
Tables in test
--------------
Table1:
Column1 nchar(10) Allow Nulls
Id int Primary Key Identity

Stored Procedures in test
-------------------------
CREATE PROCEDURE dbo.GetById
(
@id int
)
AS
SELECT * FROM Table1 WHERE Id = @id
RETURN
GO

CREATE PROCEDURE dbo.GetCount
AS
SELECT COUNT(*) FROM Table1
RETURN
GO

CREATE PROCEDURE dbo.GetData
AS
SELECT * FROM Table1
RETURN
GO
*/
class Program
{
const string CONNECTION_STRING = @"Data Source=FABIOG-NOTE64\SQLExpress;Initial Catalog=TestDb;Integrated Security=True;Pooling=True;Asynchronous Processing=true";

static void Main(string[] args)
{
var db = SqlClientUtil.Create(CONNECTION_STRING); //create an sql util with associated connection string

//insert 10 items in db using database transaction
//note: connection started connected
using (var con_scope = db.GetConnectionScope(DbConnectionScopeBehavior.StartConnected))
using (var tx_scope = db.CreateDbTransactionScope())
{
for (int i = 0; i < 10; ++i)
db.Execute //db is using connection scope and transaction scope internally
(
"INSERT INTO Table1 VALUES (@data)".AsSqlText
(
"@data".AsSqlParameter("Value" + (i + 1).ToString())
)
);

tx_scope.Complete(); //commit transaction
}

//select all items asynchronously via stored procedure . Skip the first 5 items, and take all rest performing a map to a anonymous type
//note: connection started disconnected, but at the first use it'll be opened until the end of scope or a call to Close method
using (var con_scope = db.GetConnectionScope(DbConnectionScopeBehavior.DeferredOpenConnectionAndKeepAlive))
{
var async_getdata = db.ExecuteAsync
(
"GetData".AsSqlStoredProcedure( /* no args */ ),

SqlClientDataReaderMethods.SkipAndTakeAll
(
5,
r => new { Id = r.GetInt32(1), Column1 = r.GetString(0) }
)
);

var result = async_getdata.Result; //wait to acquire the async result
if (null != result)
{
con_scope.Close(); //close connection

foreach (var r in result)
Console.WriteLine("{0} {1}", r.Id, r.Column1);
}
}

//select all items asynchronously in XML format
//note: how to using async callback is commented
string xml_value = null;
using (var con_scope = db.GetConnectionScope(DbConnectionScopeBehavior.DeferredOpenConnectionAndKeepAlive))
{
var xml_async_result = db.ExecuteAsync
(
"SELECT * FROM Table1 FOR XML AUTO, XMLDATA".AsSqlText(),

SqlClientXmlReaderMethods.ReadAllOuterXml()

//async callback
//, result =>
// {
// con_scope.Close();
// Console.WriteLine( String.Format("Thread Id = {0}\n\n{1}", System.Threading.Thread.CurrentThread.ManagedThreadId, result) );
// }
);

xmlvalue = xmlasync_result.Result; //Result will block if not finished
}

Console.WriteLine(String.Format("Thread Id = {0}\n\n{1}", System.Threading.Thread.CurrentThread.ManagedThreadId, xml_value));

//select all items synchronously in XML format
//note: connection will be opened and then closed after command execution (pool behavior)
using (var con_scope = db.GetConnectionScope(DbConnectionScopeBehavior.OpenClosePerCommandExecution))
{
var xml_result = db.Execute
(
"SELECT * FROM Table1 FOR XML AUTO, XMLDATA".AsSqlText(),

//equivalent to SqlClientXmlReaderMethods.ReadAllOuterXml()
(System.Xml.XmlReader xmlr) =>
{
var builder = new System.Text.StringBuilder();
xmlr.Read();
while (xmlr.ReadState != System.Xml.ReadState.EndOfFile)
{
builder.AppendLine(xmlr.ReadOuterXml());
}
return builder.ToString();
}
); //connection closed internally here

Console.WriteLine(xml_result);
}

//select all items synchronously via stored procedure . Take all items performing a map to a anonymous type
//note: connection will be opened and then closed after command execution (pool behavior)
using (var con_scope = db.GetConnectionScope(DbConnectionScopeBehavior.OpenClosePerCommandExecution))
{
var result = db.Execute
(
"GetData".AsSqlStoredProcedure(),

SqlClientDataReaderMethods.TakeAll
(
r => new { Id = r.GetInt32(1), Column1 = r.GetString(0) }
)
);

if (null != result)
foreach (var r in result)
Console.WriteLine("{0} {1}", r.Id, r.Column1);
}

//more samples
using (var con_scope = db.GetConnectionScope()) // same as db.GetConnectionScope(DbConnectionScopeBehavior.Default) or db.GetConnectionScope(DbConnectionScopeBehavior.StartDisconnected)
{
con_scope.Open();

db.Execute //internally calls ExecuteNonQuery
(
"INSERT INTO Table1 VALUES (@data)".AsSqlText
(
"@data".AsSqlParameter("HELLO")
)
);

var result = db.Execute //internally calls ExecuteReader
(
"GetData".AsSqlStoredProcedure(),

SqlClientDataReaderMethods.TakeAll
(
r => new { Id = r.GetInt32(1), Column1 = r.GetString(0) }
)
);

con_scope.Close(); //close connection in this scope

if (null != result)
foreach (var r in result)
Console.WriteLine("{0} {1}", r.Id, r.Column1);

con_scope.Open(); //re-open connection in this scope

var count = db.Execute<int> //internally calls ExecuteScalar
(
"GetCount".AsSqlStoredProcedure()
);

Console.WriteLine("Number of items in Database Table1 = {0}", count);
}

Console.WriteLine("Press ENTER to Finish");
Console.ReadLine();
}
}
}

Last edited Jul 6, 2010 at 6:52 PM by fgaluppo, version 2

Comments

No comments yet.