#region License
/*
* Copyright 2002-2007 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
#endregion
#if NET_2_0 && !MONO
#region Imports
using System;
//using System.Collections;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OracleClient;
using NUnit.Framework;
using Spring.Data.Common;
using Spring.Data.Generic;
using Spring.Data.Objects;
//using Sybase.Data.AseClient;
#endregion
namespace Spring.Data{
/// <summary>
/// This class contains misc tests for specific providers
/// </summary>
/// <author>Mark Pollack</author>
[TestFixture]
public class SQLiteTests
{
[SetUp]
public void Setup()
{
}
[Test]
public void SqlServerTest()
{
string errorCode = "544";
string[] errorCodes = new string[4] {"544", "2627", "8114", "8115"};
//Array.IndexOf()
//Array.Sort(errorCodes);
foreach (string code in errorCodes)
{
Console.WriteLine(code);
}
//if (Array.BinarySearch(errorCodes, errorCode) >= 0)
if (Array.IndexOf(errorCodes, errorCode) >= 0)
{
Console.WriteLine("yes");
}
else
{
Assert.Fail("did not find error code");
}
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
dbProvider.ConnectionString =
@"Data Source=MARKT60\SQL2005;Initial Catalog=Spring;Persist Security Info=True;User ID=springqa;Password=springqa";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
try
{
adoTemplate.ExecuteNonQuery(CommandType.Text, "insert into Vacation (id) values (1)");
} catch (Exception e)
{
Console.Write(e);
throw;
}
}
[Test]
public void OracleTest()
{
//Data Source=XE;User ID=hr;Unicode=True
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.OracleClient");
dbProvider.ConnectionString = "Data Source=XE;User ID=hr;Password=hr;Unicode=True";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
decimal count = (decimal) adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from emp");
Assert.AreEqual(14, count);
EmpProc empProc = new EmpProc(dbProvider);
IDictionary dict = empProc.GetEmployees();
foreach (DictionaryEntry entry in dict)
{
Console.WriteLine("Key = " + entry.Key + ", Value = " + entry.Value);
}
IList employeeList = dict["employees"] as IList;
foreach (Employee employee in employeeList)
{
Console.WriteLine(employee);
}
}
[Test]
public void Test()
{
//IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse1.15");
// dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Odbc-2.0");
dbProvider.ConnectionString =
"Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;";
Assert.IsNotNull(dbProvider);
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
IList<string> authorList =
adoTemplate.QueryWithRowMapperDelegate<string>(CommandType.Text, "select au_lname from authors",
delegate(IDataReader dataReader, int rowNum) { return dataReader.GetString(0); });
foreach (string s in authorList)
{
Console.WriteLine(s);
}
Assert.IsTrue(authorList.Count > 0);
}
/*
[Test]
public void StoredProc()
{
//IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse1.15");
//dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
//IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Odbc-2.0");
//dbProvider.ConnectionString =
// "Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;";
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse-15");
dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
HelloProc proc = new HelloProc(dbProvider);
IDictionary dict = proc.GetResults();
Assert.AreEqual("Go Sybase", dict["@inoutParam"]);
Assert.AreEqual("Hello mango", dict["@outParam"]);
Assert.AreEqual(101, (int) dict["RETURN_VALUE"]);
foreach (DictionaryEntry entry in dict)
{
Console.WriteLine("Key = " + entry.Key + ", Value = " + entry.Value);
}
}
[Test]
public void StordProcAdoTemplate()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse-15");
dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
IDbParameters parameters = new DbParameters(dbProvider);
parameters.Add("inParam", AseDbType.VarChar, 32).Value = "mango";
parameters.AddInOut("inoutParam", AseDbType.VarChar, 64).Value = "Sybase";
parameters.AddOut("outParam", AseDbType.VarChar, 64);
parameters.AddReturn("retValue", AseDbType.Integer);
adoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "sp_hello", parameters);
Assert.AreEqual("Go Sybase", parameters["@inoutParam"].Value);
Assert.AreEqual("Hello mango", parameters[2].Value);
Assert.AreEqual(101, (int) parameters[3].Value);
}
*/
[Test]
public void DeriveParams()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse-15");
dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
IDataParameter[] derivedParameters = adoTemplate.DeriveParameters("@sp_hello", true);
if (derivedParameters.Length == 0)
{
Console.WriteLine("Derived Parameters = 0!!!!");
}
for (int i = 0; i < derivedParameters.Length; i++)
{
Console.WriteLine("Parameter " + i + ", Name = " + derivedParameters[i].ParameterName + ", Value = " +
derivedParameters[i].Value);
}
}
/*
[Test]
public void RawDeriveParams()
{
using (
AseConnection conn =
new AseConnection("Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';"))
{
using (AseCommand cmd = new AseCommand("@sp_hello", conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
AseCommandBuilder.DeriveParameters(cmd);
Console.WriteLine("Number of parameters = " + cmd.Parameters.Count);
}
}
}
*/
[Test]
public void QueryWithMapper()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse-15");
dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
PubDao pubDao = new PubDao(dbProvider);
IList<Sale> sales = pubDao.GetSales("5023");
Assert.AreEqual(50, sales.Count);
}
[Test]
public void QueryWithMapperODBC()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Odbc-2.0");
dbProvider.ConnectionString =
"Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;";
PubDao pubDao = new PubDao(dbProvider);
IList<Sale> sales = pubDao.GetSales("5023");
Assert.AreEqual(50, sales.Count);
}
[Test]
public void QueryRawODBC()
{
using (
OdbcConnection conn =
new OdbcConnection(
"Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;"))
{
using (OdbcCommand cmd = new OdbcCommand("history_proc @stor_id", conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("stor_id", OdbcType.NVarChar).Value = "5023";
int i = 0;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
i++;
}
}
Assert.AreEqual(50, i);
}
}
}
}
public class PubDao : AdoDaoSupport
{
public PubDao(IDbProvider provider)
{
DbProvider = provider;
}
public IList<Sale> GetSales(string storeId)
{
return AdoTemplate.QueryWithRowMapperDelegate<Sale>(CommandType.StoredProcedure, "history_proc",
delegate(IDataReader dataReader, int rowNum)
{
Sale sale = new Sale();
sale.Date = dataReader.GetDateTime(0);
sale.OrderNumber = dataReader.GetString(1);
sale.Quantity = dataReader.GetInt32(2);
sale.Title = dataReader.GetString(3);
sale.Discount = dataReader.GetFloat(4);
sale.Price = dataReader.GetFloat(5);
sale.Total = dataReader.GetFloat(6);
return sale;
}, "stor_id",
DbType.String, 0, storeId);
}
}
public class Sale
{
private DateTime date;
private string orderNumber;
private int quantity;
private string title;
private float discount;
private float price;
private float total;
public DateTime Date
{
get { return date; }
set { date = value; }
}
public float Discount
{
get { return discount; }
set { discount = value; }
}
public string OrderNumber
{
get { return orderNumber; }
set { orderNumber = value; }
}
public int Quantity
{
get { return quantity; }
set { quantity = value; }
}
public string Title
{
get { return title; }
set { title = value; }
}
public float Price
{
get { return price; }
set { price = value; }
}
public float Total
{
get { return total; }
set { total = value; }
}
}
/*
public class HelloProc : StoredProcedure
{
public HelloProc(IDbProvider provider) : base(provider, "sp_hello")
{
DeclaredParameters.Add("inParam", AseDbType.VarChar, 32).Value = "mango";
DeclaredParameters.AddInOut("inoutParam", AseDbType.VarChar, 64).Value = "Sybase";
DeclaredParameters.AddOut("outParam", AseDbType.VarChar, 64);
DeclaredParameters.AddReturn("retValue", AseDbType.Integer);
Compile();
}
public IDictionary GetResults()
{
return Query("mango", "Sybase");
}
}
*/
public class EmpProc : StoredProcedure
{
public EmpProc(IDbProvider provider) : base(provider, "TEST.Get1CurOut")
{
//DeriveParameters();
DeclaredParameters.AddOut("P_CURSOR1", OracleType.Cursor);
AddRowMapper("employees", new EmployeeRowMapper());
Compile();
}
public IDictionary GetEmployees()
{
for (int i=0; i< DeclaredParameters.Count; i++)
{
Console.WriteLine("decarled parameter name = " + DeclaredParameters[i].ParameterName + ", type = " + DeclaredParameters[i].DbType);
}
return Query();
}
}
internal class EmployeeRowMapper : IRowMapper
{
public object MapRow(IDataReader reader, int rowNum)
{
Employee emp = new Employee();
emp.Number = reader.GetDecimal(0);
emp.Name = reader.GetString(1);
return emp;
}
}
internal class Employee
{
private decimal number;
private string name;
public decimal Number
{
get { return number; }
set { number = value; }
}
public string Name
{
get { return name; }
set { name = value; }
}
public override string ToString()
{
return "Number = " + number + ", Name = " + name;
}
}
}
#endif
|