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(); } }