public void DirectTest()
{
using (DbManager db = new DbManager())
{
string paramName = (string) db.DataProvider.Convert("name", ConvertType.NameToQueryParameter);
string fieldName = (string) db.DataProvider.Convert("FirstName", ConvertType.NameToQueryField);
db.SetCommand(string.Format("SELECT {0} = {1} FROM Person WHERE PersonID = 1", paramName, fieldName)
, db.OutputParameter(paramName, DbType.String, 50))
.ExecuteNonQuery();
Assert.AreEqual("John", db.Parameter(paramName).Value);
}
}
public IDbDataParameter[] GetParameters(DbManager db, object obj)
{
var parameters = new IDbDataParameter[Parameters.Count];
for (var i = 0; i < Parameters.Count; i++)
{
var info = Parameters[i];
//parameters[i] = db.Parameter(info.ParameterName, info.MemberMapper.GetValue(obj));
var mmi = info.MemberMapper.MapMemberInfo;
var val = info.MemberMapper.GetValue(obj);
if (val == null && mmi.Nullable/* && mmi.NullValue == null*/)
{
//replace value with DbNull
val = DBNull.Value;
}
if (mmi.IsDbTypeSet)
{
parameters[i] = mmi.IsDbSizeSet
? db.Parameter(info.ParameterName, val, info.MemberMapper.DbType, mmi.DbSize)
: db.Parameter(info.ParameterName, val, info.MemberMapper.DbType);
}
else
{
parameters[i] = val != DBNull.Value
? db.Parameter(info.ParameterName, val)
: db.Parameter(info.ParameterName, val, info.MemberMapper.GetDbType());
}
if (mmi.KeyGenerator is SequenceKeyGenerator && ActionName == "InsertWithIdentity")
{
parameters[i] = db.OutputParameter(info.ParameterName, val);
}
}
return parameters;
}
public void OutputParameterTest()
{
using (DbManager db = new DbManager())
{
/*
* CREATE Procedure Scalar_OutputParameter
* @outputInt int = 0 output,
* @outputString varchar(50) = '' output
* AS
* BEGIN
* SET @outputInt = 12345
* SET @outputString = '54321'
* END
*/
db
.SetSpCommand("Scalar_OutputParameter",
db./*[a]*/OutputParameter/*[/a]*/("@outputInt", 1),
db./*[a]*/OutputParameter/*[/a]*/("@outputString", "1"))
.ExecuteNonQuery();
Assert.AreEqual(12345, (int) db./*[a]*/Parameter("@outputInt"). Value/*[/a]*/);
Assert.AreEqual("54321", (string)db./*[a]*/Parameter("@outputString").Value/*[/a]*/);
}
}
public void Test()
{
using (DbManager db = new DbManager())
{
object id;
#if ORACLE
id = db
.SetCommand("INSERT INTO BinaryData (Data) VALUES (:pData) RETURNING BinaryDataID INTO :pID",
db.Parameter("pData", new byte[] { 1, 2, 3, 4, 5}),
db.OutputParameter("pID", System.Data.DbType.Int32)
)
.ExecuteScalar(ScalarSourceType.OutputParameter, "ID");
#elif MSSQL
id = db
.SetCommand("INSERT INTO BinaryData (Data) VALUES (@Data)\nSELECT Cast(SCOPE_IDENTITY() as int)",
db.Parameter("@Data", new byte[] { 1, 2, 3, 4, 5}))
.ExecuteScalar();
#elif FIREBIRD
db
.SetCommand("INSERT INTO BinaryData (Data) VALUES (@Data)",
db.Parameter("@Data", new byte[] { 1, 2, 3, 4, 5}))
.ExecuteNonQuery();
id = db
.SetCommand("SELECT GEN_ID(PersonID, 0) FROM dual")
.ExecuteScalar();
#elif ACCESS || SQLCE
db
.SetCommand("INSERT INTO BinaryData (Data) VALUES (@Data)",
db.Parameter("@Data", new byte[] { 1, 2, 3, 4, 5}))
.ExecuteNonQuery();
id = db
.SetCommand("SELECT @@IDENTITY")
.ExecuteScalar();
#elif SQLITE
db
.SetCommand("INSERT INTO BinaryData (Stamp, Data) VALUES (datetime('now'), @Data)",
db.Parameter("@Data", new byte[] { 1, 2, 3, 4, 5}))
.ExecuteNonQuery();
id = db
.SetCommand("SELECT last_insert_rowid()")
.ExecuteScalar();
#else
Assert.Fail("Unknown DB type.");
#endif
BinaryData bd = (BinaryData)db
.SetCommand(
"SELECT * FROM BinaryData WHERE BinaryDataID = " + db.DataProvider.Convert("id", ConvertType.NameToQueryParameter),
db.Parameter("id", id))
.ExecuteObject(typeof(BinaryData));
Assert.IsNotNull(bd);
Assert.AreEqual(5, bd.Data. Length);
#if FIREBIRD
// Stamps are integers in Firebird.
//
Assert.AreEqual(4, bd.Stamp.Length);
#elif ACCESS || SQLCE
// Not supported in MS Access
//
Assert.IsNull(bd.Stamp);
#else
Assert.AreEqual(8, bd.Stamp.Length);
#endif
db
#if FIREBIRD || ACCESS || SQLCE || SQLITE
.SetCommand("DELETE FROM BinaryData")
#else
.SetCommand("TRUNCATE TABLE BinaryData")
#endif
.ExecuteNonQuery();
}
}
