static CDBVariantArray TestStoredProcedure(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(1);
vPData.Add(2.35m); //input/output
vPData.Add(null); //input/output
//second set
vPData.Add(2);
vPData.Add(0.99m); //input/output
vPData.Add(null); //input/output
//Parameter info array can be ignored for some ODBC drivers like MySQL, MS SQL Server, etc but performance will be degraded for code simplicity
bool ok = odbc.Prepare("{call sp_TestProc(?,?,?)}", dr);
COdbc.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
COdbc.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
ok = odbc.Execute(vPData, er, r, rh);
return(vPData);
}
static void TestStoredProcedure_2(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData)
{
CParameterInfo[] vInfo = { new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo() };
vInfo[0].DataType = tagVariantDataType.sdVT_I4;
//return direction can be ignorable
vInfo[1].DataType = tagVariantDataType.sdVT_I4;
vInfo[2].DataType = tagVariantDataType.sdVT_XML;
vInfo[2].Direction = tagParameterDirection.pdInputOutput;
vInfo[3].DataType = tagVariantDataType.sdVT_CLSID;
vInfo[3].Direction = tagParameterDirection.pdInputOutput;
vInfo[4].DataType = tagVariantDataType.sdVT_VARIANT;
vInfo[4].Direction = tagParameterDirection.pdOutput;
bool ok = odbc.Prepare("{?=call sp_TestRare1(?, ?, ?, ?)}", dr, vInfo);
COdbc.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
COdbc.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
ok = odbc.Execute(vPData, er, r, rh);
}
static CDBVariantArray TestStoredProcedure_2(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
//vInfo is ignorable for MS SQL server ODBC drivers for code simplicity
CParameterInfo[] vInfo = { new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo() };
vInfo[0].DataType = tagVariantDataType.sdVT_I4;
//return direction can be ignorable
vInfo[1].DataType = tagVariantDataType.sdVT_I4;
vInfo[2].DataType = tagVariantDataType.sdVT_XML;
vInfo[2].Direction = tagParameterDirection.pdInputOutput;
vInfo[3].DataType = tagVariantDataType.sdVT_CLSID;
vInfo[3].Direction = tagParameterDirection.pdInputOutput;
vInfo[4].DataType = tagVariantDataType.sdVT_VARIANT;
vInfo[4].Direction = tagParameterDirection.pdOutput;
bool ok = odbc.Prepare("{?=call sp_TestRare1(?,?,?,?)}", dr, vInfo);
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(-1); //return int. output parameter value not important.
vPData.Add(1); //@testid
vPData.Add("<test_sqlserver />"); //@myxml
vPData.Add(Guid.NewGuid()); //@tuuid
vPData.Add(-1); //@myvar. output parameter value not important.
//second set
vPData.Add(2); //return int. output parameter data type not important.
vPData.Add(4); //@testid
vPData.Add("<test_sqlserver_again />"); //@myxml
vPData.Add(Guid.NewGuid()); //@tuuid
vPData.Add(2); //@myvar. output parameter value not important.
COdbc.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
COdbc.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
ok = odbc.Execute(vPData, er, r, rh);
return(vPData);
}
static CDBVariantArray TestBatch(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(-1); //return int. output parameter value not important.
vPData.Add(1); //@testid
vPData.Add("<test_sqlserver />"); //@myxml
vPData.Add(Guid.NewGuid()); //@tuuid
vPData.Add(-1); //@myvar.
vPData.Add(1);
vPData.Add(2.35m); //input/output
vPData.Add(null);
//second set
vPData.Add(2); //return int. output parameter data type not important.
vPData.Add(4); //@testid
vPData.Add("<test_sqlserver_again />"); //@myxml
vPData.Add(Guid.NewGuid()); //@tuuid
vPData.Add(2); //@myvar.
vPData.Add(2);
vPData.Add(0.99m); //input/output
vPData.Add(null);
//Parameter info array can be ignored for some ODBC drivers like MySQL, MS SQL Server, etc but performance will be degraded for code simplicity
COdbc.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
COdbc.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
bool ok = odbc.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();{?=call sp_TestRare1(?,?,?,?)};{call sqltestdb.dbo.sp_TestProc(?,?,?)}", vPData,
er, r, rh);
return(vPData);
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123"); //20901 for plugindev
using (CSocketPool <COdbc> spOdbc = new CSocketPool <COdbc>(true, 600000))
{
if (!spOdbc.StartSocketPool(cc, 1))
{
Console.WriteLine("Failed in connecting to remote async odbc server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
COdbc odbc = spOdbc.Seek();
bool ok = odbc.Open("", dr); //use default database
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
COdbc.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
COdbc.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
TestCreateTables(odbc);
ok = odbc.Execute("delete from employee;delete from company;delete from test_rare1;delete from SpatialTable;INSERT INTO SpatialTable(mygeometry, mygeography)VALUES(geometry::STGeomFromText('LINESTRING(100 100,20 180,180 180)',0),geography::Point(47.6475,-122.1393,4326))", er);
ok = odbc.Execute("INSERT INTO test_rare1(mybool,mymoney,myxml,myvariant,mydateimeoffset)values(1,23.45,'<sometest />', N'美国总统川普下个星期四','2017-05-02 00:00:00.0000000 -04:00');INSERT INTO test_rare1(mybool,mymoney,myvariant)values(0,1223.45,'This is a test for ASCII string inside sql_variant');INSERT INTO test_rare1(myvariant)values(283.45)", er);
TestPreparedStatements(odbc);
TestPreparedStatements_2(odbc);
InsertBLOBByPreparedStatement(odbc);
ok = odbc.Execute("SELECT * from company;select * from employee;select CONVERT(datetime,SYSDATETIME());select * from test_rare1;select * from SpatialTable", er, r, rh);
ok = odbc.Tables("sqltestdb", "%", "%", "TABLE", er, r, rh);
CDBVariantArray vPData = TestStoredProcedure(odbc, ra);
ok = odbc.WaitAll();
Console.WriteLine();
Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2);
CDBVariantArray vPData2 = TestStoredProcedure_2(odbc, ra);
ok = odbc.WaitAll();
Console.WriteLine();
Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2);
CDBVariantArray vPData3 = TestBatch(odbc, ra);
ok = odbc.WaitAll();
Console.WriteLine();
Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2);
ok = odbc.Tables("AdventureWorks", "%", "%", "TABLE", er, r, rh);
ok = odbc.WaitAll();
ok = odbc.Execute("use AdventureWorks", er);
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> tables = ra[ra.Count - 1];
int columns = tables.Key.Count;
int num_tables = tables.Value.Count / columns;
for (int n = 0; n < num_tables; ++n)
{
string sql = "select * from " + tables.Value[columns * n + 1].ToString() + "." + tables.Value[columns * n + 2].ToString();
ok = odbc.Execute(sql, er, r, rh);
}
ok = odbc.WaitAll();
ok = odbc.Tables("AdventureWorksDW", "%", "%", "TABLE", er, r, rh);
ok = odbc.WaitAll();
ok = odbc.Execute("use AdventureWorksDW", er);
tables = ra[ra.Count - 1];
columns = tables.Key.Count;
num_tables = tables.Value.Count / columns;
for (int n = 0; n < num_tables; ++n)
{
string sql = "select * from " + tables.Value[columns * n + 1].ToString() + "." + tables.Value[columns * n + 2].ToString();
ok = odbc.Execute(sql, er, r, rh);
}
ok = odbc.WaitAll();
int index = 0;
Console.WriteLine();
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValuePair <CDBColumnInfoArray, CDBVariantArray> it in ra)
{
Console.Write("Statement index = {0}", index);
if (it.Key.Count > 0)
{
Console.WriteLine(", rowset with columns = {0}, records = {1}.", it.Key.Count, it.Value.Count / it.Key.Count);
}
else
{
Console.WriteLine(", no rowset received.");
}
++index;
}
Console.WriteLine("+++++ End rowsets +++");
Console.WriteLine();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
}
}
