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;
}
}
public void AddSheepFold(string id, string name, string administrator, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Sheepfold\" (\"Id\",\"Name\",\"Administrator\",\"OperatorId\",\"CreateTime\",\"SysFlag\",\"Remark\")VALUES(:id,:name,:administrator,:operatorId,:createTime,false,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("administrator", administrator);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
private static void loadCargoData(AdoTemplate jdbcTemplate)
{
String cargoSql =
"insert into Cargo (id, tracking_id, spec_origin_id, spec_destination_id, spec_arrival_deadline, last_update) " +
"values ({0}, '{1}', {2}, {3}, '{4}', '{5}')";
Object[][] cargoArgs =
{
new object[] { 1, "XYZ", 1, 2, ts(10), ts(100) },
new object[] { 2, "ABC", 1, 5, ts(20), ts(100) },new object[] { 3, "ZYX", 2, 1, ts(30), ts(100) },
new object[] { 4, "CBA", 5, 1, ts(40), ts(100) },new object[] { 5, "FGH", 3, 5, ts(50), ts(100) },
new object[] { 6, "JKL", 6, 4, ts(60), ts(100) }
};
executeUpdate(jdbcTemplate, cargoSql, cargoArgs, "Cargo");
}
public void AddBreed(string id, string name, string description, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Breed\" (\"Id\",\"Name\",\"Description\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:name,:description,:operatorId,:createTime,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("description", description);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void Handle(CreateDutyEvent evt)
{
string sql = "insert into T_duty (createrid,createtime,departmentid,`desc`,id,isenabled,name) values(@createrid,@createtime,@departmentid,@desc,@id,@isenabled,@name)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("createrid", evt.CreaterId);
pms.AddWithValue("createtime", evt.CreateTime);
pms.AddWithValue("departmentid", evt.DepartmentId);
pms.AddWithValue("desc", evt.Desc);
pms.AddWithValue("id", evt.Id);
pms.AddWithValue("isenabled", evt.IsEnabled);
pms.AddWithValue("name", evt.Name);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public LectureCategoryRequirementItem FindByAccountAndCategoryId(int accountId, int category)
{
string query = "select a.*,b.name lectureCategoryName from lectureCategoryRequirement a inner join lectureCategory b on a.lectureCategoryId=b.id where accountId=@AccountId and lectureCategoryId = @Category";
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId);
param.AddWithValue("Category", category);
IList <LectureCategoryRequirementItem> list = AdoTemplate.QueryWithRowMapper <LectureCategoryRequirementItem>(CommandType.Text, query, new LectureCategoryRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public TestObject FindByName(string name)
{
IList toList = AdoTemplate.QueryWithRowMapper(CommandType.Text,
"select TestObjectNo, Age, Name from TestObjects where Name='" + name + "'",
new TestObjectRowMapper());
if (toList.Count > 0)
{
return((TestObject)toList[0]);
}
else
{
return(null);
}
}
public void Handle(CreateAccountEvent evt)
{
string sql = "insert into t_account VALUES(@Id,@Name,@Account,@Password,@CreaterId,@CreateTime)";
IDbParameters parameters = AdoTemplate.CreateDbParameters();
parameters.AddWithValue("Id", evt.Id);
parameters.AddWithValue("Name", evt.Name);
parameters.AddWithValue("Account", evt.Account);
parameters.AddWithValue("Password", evt.Password);
parameters.AddWithValue("CreaterId", evt.CreaterId);
parameters.AddWithValue("CreateTime", evt.CreateTime);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, parameters);
}
public void Handle(CorrectBookkeepingEvent evt)
{
//更新错账
string sqlUpdate = "update t_bookkeeping set Status=@Status,AbandonReason=@Reason WHERE Id=@OldId";
IDbParameters pmUpdate = AdoTemplate.CreateDbParameters();
pmUpdate.AddWithValue("Status", evt.OldStatus);
pmUpdate.AddWithValue("Reason", evt.AbandonReason);
pmUpdate.AddWithValue("OldId", evt.OldId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sqlUpdate, pmUpdate);
//回滚错账
string rbkId = Guid.NewGuid().ToString();
string sqlRbk = "insert into t_bookkeeping ( Id, Time, VoucherType, VoucherNum, Abstract, Amount,Direction,Balance,Status, CreaterId, CreateTime, Remark ) SELECT '" + rbkId + "', Time, VoucherType, VoucherNum, Abstract, Amount,Direction,Balance,Status, CreaterId, now(), Remark from t_bookkeeping WHERE id =@Id";
IDbParameters pmRbk = AdoTemplate.CreateDbParameters();
pmRbk.AddWithValue("Id", evt.OldId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sqlRbk, pmRbk);
string sqlUpdateRbk = "update t_bookkeeping set Direction=@Direction,Balance=@Balance,ReferenceId=@ReferenceId,Status=@Status where Id=@Id";
IDbParameters pmUpdateRbk = AdoTemplate.CreateDbParameters();
pmUpdateRbk.AddWithValue("Direction", evt.RbkDirection);
pmUpdateRbk.AddWithValue("Balance", evt.RbkBalance);
pmUpdateRbk.AddWithValue("ReferenceId", evt.OldId);
pmUpdateRbk.AddWithValue("Status", evt.RbkStatus);
pmUpdateRbk.AddWithValue("Id", rbkId);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sqlUpdateRbk, pmUpdateRbk);
//记录新账
string sqlNew = "insert into t_bookkeeping (Id,Time,VoucherType,VoucherNum,Abstract,Amount,Direction,Balance,CreaterId,CreateTime,ReferenceId,Status,Remark) VALUES(@Id,@Time,@VoucherType,@VoucherNum,@Abstract,@Amount,@Direction,@Balance,@CreaterId,@CreateTime,@ReferenceId,@Status,@Remark)";
IDbParameters pmUpdateNew = AdoTemplate.CreateDbParameters();
pmUpdateNew.AddWithValue("Id", evt.NewId);
pmUpdateNew.AddWithValue("Time", evt.Time);
pmUpdateNew.AddWithValue("VoucherType", evt.NewVoucherType);
pmUpdateNew.AddWithValue("VoucherNum", evt.NewVoucherNum);
pmUpdateNew.AddWithValue("Abstract", evt.NewAbstract);
pmUpdateNew.AddWithValue("Amount", evt.NewAmount);
pmUpdateNew.AddWithValue("Direction", evt.NewDirection);
pmUpdateNew.AddWithValue("Balance", evt.NewBalance);
pmUpdateNew.AddWithValue("CreaterId", evt.NewCreaterId);
pmUpdateNew.AddWithValue("CreateTime", evt.CreateTime);
pmUpdateNew.AddWithValue("ReferenceId", evt.OldId);
pmUpdateNew.AddWithValue("Status", evt.NewStatus);
pmUpdateNew.AddWithValue("Remark", evt.NewRemark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sqlNew, pmUpdateNew);
}
public Airport GetAirport(string code)
{
return((Airport)AdoTemplate.QueryForObject(CommandType.Text,
"SELECT * FROM airport WHERE code = @code",
airportMapper, "code", DbType.String, 0, code));
/*
* IDbCommand command = GetCommand("SELECT * FROM airport WHERE code = @code");
* IDbDataParameter parameter = command.CreateParameter();
* parameter.ParameterName = "@code";
* parameter.DbType = DbType.String;
* parameter.Value = code;
* command.Parameters.Add(parameter);
* return (Airport) QueryForEntity(command, this.airportMapper);
*/
}
public static int BasitTabloIdentityEkle
(
string @Adi
, string @Soyadi
)
{
AdoTemplate template = new AdoTemplate();
template.Connection = ConnectionSingleton.Instance.getConnection("KARKAS_ORNEK");
return(BasitTabloIdentityEkle(
@Adi
, @Soyadi
, template
));
}
public static int YaziDegeriniBul
(
int? @SAYI
, out string @SAYI_YAZI
)
{
AdoTemplate template = new AdoTemplate();
template.Connection = ConnectionSingleton.Instance.getConnection("KARKAS_ORNEK");
return(YaziDegeriniBul(
@SAYI
, out @SAYI_YAZI
, template
));
}
public virtual IList <string> GetCustomerNameByCountryAndCity(string country, string city)
{
// note no need to use parameter prefix.
// This allows the SQL to be changed via external configuration but the parameter setting code
// can remain the same if no provider specific DbType enumerations are used.
IDbParameters parameters = CreateDbParameters();
parameters.AddWithValue("Country", country).DbType = DbType.String;
parameters.Add("City", DbType.String).Value = city;
return(AdoTemplate.QueryWithResultSetExtractor(CommandType.Text,
customerByCountryAndCityCommandText,
new CustomerNameResultSetExtractor <List <string> >(),
parameters));
}
public LectureTypeRequirementItem FindById(int id)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, b.name lectureTypeName ,c.name lectureCategoryName, c.id lectureCategoryId from lectureTypeRequirement a inner join lectureType b on a.lectureTypeId=b.id inner join lectureCategory c on b.lectureCategoryId = c.id where a.id = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
IList <LectureTypeRequirementItem> list = AdoTemplate.QueryWithRowMapper <LectureTypeRequirementItem>(CommandType.Text, builder.ToString(), new LectureTypeRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public void AddPermission(string id, string name, string description, string URL, string operatorId, DateTime createTime, string remark)
{
string sql = "insert into \"T_Permission\" (\"Id\",\"Name\",\"Descreption\",\"URL\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:Id,:Name,:Description,:URL,:OperatorId,:CreateTime,:Remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("description", description);
pms.AddWithValue("URL", URL);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
/// <summary>
/// sql语句中包含聚合语句，where不能放在最后
/// 仅支持一个参数的情况下
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <typeparam name="TFilter"></typeparam>
/// <param name="rowsCount"></param>
/// <param name="querySql"></param>
/// <param name="filter"></param>
/// <returns></returns>
List <TResult> GetRuledRowsDataWithFormate <TResult, TFilter>(int rowsCount, string querySql, IQueryFilter filter)
where TResult : class, new()
where TFilter : IQueryFilter, new()
{
List <TResult> list = new List <TResult>();
IDbParameters pms;
querySql = string.Format(querySql, (filter ?? new TFilter()).ToSqlWhere(out pms));
string sql = string.Format("select * from ({0}) as sr where sr.\"rownum\"<={1}", querySql, rowsCount);
list = AdoTemplate.QueryWithRowMapper <TResult>(CommandType.Text, sql, new BaseRowMapper <TResult>(), pms).ToList();
return(list);
}
/// <summary>
/// 删除记录（逻辑删除，表里必须有IsDelete字段）
/// </summary>
public bool DeleteLogical <T>(string columnName, object value)
{
try
{
TableMapAttribute attribute = TableMapAttribute.GetInstance(typeof(T));
string tableName = attribute.TableName;
IDbParameters param = AdoTemplate.CreateDbParameters();
string sql = string.Format("update {0} set IsDelete=1 where {1}=@{1}", tableName, columnName);
param.AddWithValue(columnName, value);
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, param) > 0);
}
catch (Exception ex)
{
return(false);
}
}
public static void CleanupA()
{
var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
var gDbProvider = new MockDbProvider(connection, connectionString);
using (var adoTemplate = new AdoTemplate(gDbProvider))
{
try
{
adoTemplate.ExecuteNonQuery(dropDummyTable);
}
catch (Exception x)
{ }
}
}
/// <summary>
/// 修改记录
/// </summary>
public int Update(DataTable dt, string where)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("update {0} set ", dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i != 0)
{
sbSql.Append(",");
}
sbSql.AppendFormat("[{0}]='{1}'", dt.Columns[i].ColumnName, dt.Rows[0][i].ToString());
}
sbSql.AppendFormat(" where {0}", where);
return(AdoTemplate.ExecuteNonQuery(CommandType.Text, sbSql.ToString()));
}
public void UpdateMating(string femaleId, string maleId, DateTime matingDate, bool isRemindful, string principalId, string remark, string id)
{
string sql = "UPDATE \"T_Mating\" SET \"FemaleId\"=:femaleId,\"MaleId\"=:maleId,\"MatingDate\"=:matingDate,\"IsRemindful\"=:isRemindful,\"PrincipalId\"=:principalId,\"Remark\"=:remark where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("femaleId", femaleId);
pms.AddWithValue("maleId", maleId);
pms.AddWithValue("matingDate", matingDate);
pms.AddWithValue("isRemindful", isRemindful);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("remark", remark);
pms.AddWithValue("id", id);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public SectionRequirementItem FindById(int id)
{
StringBuilder builder = new StringBuilder();
builder.Append("select *from SectionRequirement where id = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
IList <SectionRequirementItem> list = AdoTemplate.QueryWithRowMapper <SectionRequirementItem>(CommandType.Text, builder.ToString(), new SectionRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public void UpdateAntiepidemicPlan(string name, string vaccine, DateTime planExecuteDate, string sheepFlock, string principalId, string remark, string id)
{
string sql = "UPDATE \"T_AntiepidemicPlan\" SET \"Name\"=:name,\"Vaccine\"=:vaccine,\"PlanExecuteDate\"=:planExecuteDate,\"SheepFlock\"=:sheepFlock,\"PrincipalId\"=:principalId,\"Remark\"=:remark where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", name);
pms.AddWithValue("vaccine", vaccine);
pms.AddWithValue("planExecuteDate", planExecuteDate);
pms.AddWithValue("sheepFlock", sheepFlock);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("remark", remark);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public void GrantPermission2User(List <string> permissionIds, string userId)
{
StringBuilder sb = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
for (int i = 0; i < permissionIds.Count(); i++)
{
sb.AppendFormat("insert into \"T_UserPermission\" (\"Id\",\"UserId\",\"PermissionId\")VALUES(:Id{0},:UserId{0},:PermissionId{0});", i);
pms.AddWithValue("id" + i, Guid.NewGuid().ToString());
pms.AddWithValue("permissionId" + i, permissionIds[i]);
pms.AddWithValue("userId" + i, userId);
}
AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString(), pms);
}
public DataTable GetEmptyTable(string tablename)
{
string sql = "select name,xtype,typestat from syscolumns where id=object_id('" + tablename + "')";
DataTable dt = AdoTemplate.DataTableCreate(CommandType.Text, sql);
DataTable dtRes = new DataTable(tablename);
foreach (DataRow dr in dt.Rows)
{
/*
* 34 image； 48 tinyint； 56 int； 61 datetime； 104 bit； 106 decimal
* 165 varbinary；167 varchar； 231 nvarchar；239 nchar
*/
Type type = typeof(string);
byte t = (byte)dr["xtype"];
if (t == 48)
{
type = typeof(byte);
}
else if (t == 56)
{
type = typeof(int);
}
else if (t == 61)
{
type = typeof(DateTime);
}
else if (t == 104)
{
type = typeof(bool);
}
else if (t == 106)
{
type = typeof(decimal);
}
else if (t == 167 || t == 231 || t == 239)
{
type = typeof(string);
}
else if (t == 165 || t == 34)
{
type = typeof(byte[]);
}
dtRes.Columns.Add(dr["name"].ToString(), type);
}
return(dtRes);
}
/// <summary>
/// Retorna la fecha del último envío recibido por CNBV
/// </summary>
/// <returns>Fecha del último envío.</returns>
public DateTime ObtenFechaUltimoEnvioDocumentoInstancia()
{
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate <DateTime>(CommandType.Text,
"SELECT MAX(FechaCreacion) maxFecha FROM DocumentoInstancia WHERE EspacioNombresPrincipal IS NOT NULL",
delegate(IDataReader reader, int rowNum) {
var fecha = DateTime.MinValue;
if (DbUtil.FieldExists(reader, "maxFecha"))
{
fecha = reader.GetDateTime(reader.GetOrdinal("maxFecha"));
}
return(fecha);
},
builder.GetParameters());
return(documentosInstancia.First());
}
public static DataTable MusteriSorgulaHepsiniGetir
(
AdoTemplate template
)
{
ParameterBuilder builder = template.getParameterBuilder();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "ORNEKLER.MUSTERI_SORGULA_HEPSINI_GETIR";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(builder.GetParameterArray());
DataTable _tmpDataTable = template.DataTableOlustur(cmd);
return(_tmpDataTable);
}
public ThirdAssess GetThirdAssessById(string id)
{
string sql = "SELECT A .\"Id\", s.\"SerialNumber\", ta.\"MatingAbility\", A .\"Weight\", A .\"HabitusScore\", A .\"AssessDate\", s.\"Birthday\", s.\"Gender\", A .\"PrincipalId\", A .\"OperatorId\", A .\"CreateTime\", A .\"Remark\" FROM \"T_ThirdAssess\" ta JOIN \"T_Assess\" AS A ON A .\"Id\" = ta.\"AssessId\" JOIN \"T_Sheep\" s ON s.\"Id\" = A .\"SheepId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" where A.\"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
List <ThirdAssess> list = GetData <ThirdAssess>(sql, pms);
list.ForEach(a =>
{
//TODO:TotalScore
});
return(list.FirstOrDefault());
}
private static void loadItineraryData(AdoTemplate jdbcTemplate)
{
String legSql =
"insert into Leg (id, cargo_id, voyage_id, load_location_id, unload_location_id, load_time, unload_time, leg_index) " +
"values ({0},{1},{2},{3},{4},'{5}','{6}',{7})";
Object[][] legArgs =
{
// Cargo 5: Hongkong - Melbourne - Stockholm - Helsinki
new object[] { 1, 5, 1, 3, 2, ts(1), ts(2), 0 }, new object[] { 2, 5, 1, 2, 1, ts(3), ts(4), 1 },
new object[] { 3, 5, 1, 1, 5, ts(4), ts(5), 2 }, // Cargo 6: Hamburg - Stockholm - Chicago - Tokyo
new object[] { 4, 6, 2, 6, 1, ts(1), ts(2), 0 }, new object[] { 5, 6, 2, 1, 7, ts(3), ts(4), 1 },
new object[] { 6, 6, 2, 7, 4, ts(5), ts(6), 2 }
};
executeUpdate(jdbcTemplate, legSql, legArgs, "Leg");
}
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 virtual IList <string> GetCustomerNameByCountryAndCityWithParamsBuilder(string country, string city)
{
// note no need to use parameter prefix.
// This allows the SQL to be changed via external configuration but the parameter setting code
// can remain the same if no provider specific DbType enumerations are used.
IDbParametersBuilder builder = CreateDbParametersBuilder();
builder.Create().Name("Country").Type(DbType.String).Size(15).Value(country);
builder.Create().Name("City").Type(DbType.String).Size(15).Value(city);
return(AdoTemplate.QueryWithResultSetExtractor(CommandType.Text,
customerByCountryAndCityCommandText,
new CustomerNameResultSetExtractor <List <string> >(),
builder.GetParameters()));
}
public void AddAblactation(string id, string sheepId, float ablactationWeight, DateTime ablactationDate, string principalId, string operatorId, DateTime createTime, string remark)
{
string sql = "INSERT into \"T_Ablactation\" (\"Id\",\"SheepId\",\"PrincipalId\",\"OperatorId\",\"CreateTime\",\"Remark\")VALUES(:id,:sheepId,:principalId,:operatorId,:createTime,:remark);update \"T_Sheep\" set \"AblactationWeight\"=:ablactationWeight,\"AblactationDate\"=:ablactationDate where \"Id\"=:sheepId";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("sheepId", sheepId);
pms.AddWithValue("principalId", principalId);
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", createTime);
pms.AddWithValue("remark", remark);
pms.AddWithValue("ablactationWeight", ablactationWeight);
pms.AddWithValue("ablactationDate", ablactationDate);
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
public AP2CargoRepository()
{
_AdoTemplate = ContextRegistry.GetContext().GetObject("AdoTemplate") as AdoTemplate;
}
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);
}
}
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);
}
}
protected override void OnSetUpInTransaction()
{
// TODO store Sample* and object instances here instead of handwritten SQL
SampleDataGenerator.loadSampleData(adoTemplate, new TransactionTemplate(transactionManager));
_genericTemplate = new AdoTemplate(adoTemplate);
}
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);
}
