mikebeaton

var results = db.ExecuteWithParams("begin open :p_rc for select * from emp where deptno = 10; end;",
  outParams: new { p_rc = new Cursor() },
  // shared connection (Oracle) or transaction (PostgreSQL) required to share cursors
  connection: conn);
db.ExecuteAsProcedure("cursor_in_out.process_cursor",
  inParams: new { p_cursor = results.p_rc },
  connection: conn);

mikebeaton

var db = new MightyOrm(connectionString);
using (var connection = db.OpenConnection())
{
  using (var trans = conn.BeginTransaction())
  {
    var customer = db.Insert(CustomerInfo, connection);
    OrderInfo.CustomerID = customer.CustomerID;
    db.Insert(OrderInfo, connection);
    trans.Commit();
  }

mikebeaton

var films = new MightyOrm(connectionString, "Film");
var page = films.Paged(orderBy: "Title", currentPage: 2, pageSize: 30);

mikebeaton

SELECT c.column_id, c.name
FROM sys.objects o
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
AND s.name = @schema_name
INNER JOIN sys.columns c
ON o.object_id = c.object_id
WHERE o.name = @table_name

mikebeaton

SELECT c.column_id, c.name
FROM sys.objects o
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
AND s.name = @schema_name
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
AND i.is_primary_key = 1
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id

mikebeaton

DECLARE @SQL NVARCHAR(MAX)
DECLARE @i INT

SET @SQL = 'SELECT '

SET @SQL = @SQL + '''OURS <<<'' AS [ ],' + @CRLF
SELECT @SQL = @SQL + @TAB + '   [ours].[' + #columns.name + '],' + @CRLF
FROM #columns

SET @SQL = @SQL + @TAB + '   ''THEIRS >>>'' AS [ ],' + @CRLF

mikebeaton

SELECT 'OURS <<<' AS [ ],
	[ours].[AddressTypeID],
	[ours].[AddressType],
	'THEIRS >>>' AS [ ],
	[theirs].[AddressTypeID],
	[theirs].[AddressType]
FROM dbo.AddressTypes [ours]
FULL OUTER JOIN RemoteServer.CustomerDatabase.dbo.AddressTypes [theirs]
ON [ours].[AddressTypeID] = [theirs].[AddressTypeID]
WHERE [ours].[AddressTypeID] IS NULL AND [theirs].[AddressTypeID] IS NOT NULL

mikebeaton

SET @SQL = 'INSERT INTO ' + @local_table_name + ' (' + @CRLF

SELECT @SQL = @SQL + @TAB + '[' + #columns.name + '],' + @CRLF
FROM #columns

SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - LEN(@CRLF) - 1) + @CRLF

SELECT @SQL = @SQL + ')' + @CRLF

SELECT @SQL = @SQL + 'SELECT' + @CRLF

mikebeaton

INSERT INTO dbo.AddressTypes (
	[AddressTypeID],
	[AddressType]
)
SELECT
	[theirs].[AddressTypeID],
	[theirs].[AddressType]
FROM dbo.AddressTypes [ours]
FULL OUTER JOIN RemoteServer.CustomerDatabase.dbo.AddressTypes [theirs]
ON [ours].[AddressTypeID] = [theirs].[AddressTypeID]

mikebeaton

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DataCompare]
	@table_name sysname,
	@remote_db_name sysname,
	@import_theirs_to_ours bit = null,
	@import_ours_to_theirs bit = null,
	@schema_name sysname = 'dbo'