internal DBUserClaims(
string dbConnectionString,
ILoggerFactory loggerFactory)
{
logFactory = loggerFactory;
connectionString = dbConnectionString;
// possibly will change this later to have SqliteFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(SqliteFactory.Instance);
}
internal DBGeoZone(
string dbReadConnectionString,
string dbWriteConnectionString,
ILoggerFactory loggerFactory)
{
logFactory = loggerFactory;
readConnectionString = dbReadConnectionString;
writeConnectionString = dbWriteConnectionString;
// possibly will change this later to have MySqlClientFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(MySqlClientFactory.Instance);
}
internal DBSiteUser(
string dbReadConnectionString,
string dbWriteConnectionString,
ILoggerFactory loggerFactory)
{
logFactory = loggerFactory;
readConnectionString = dbReadConnectionString;
writeConnectionString = dbWriteConnectionString;
// possibly will change this later to have NpgSqlFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(Npgsql.NpgsqlFactory.Instance);
}
public DbSetup(
cloudscribe.DbHelpers.SQLite.SqliteConnectionstringResolver connectionStringResolver,
ILogger<DbSetup> logger,
IVersionProviderFactory versionProviderFactory)
{
if (connectionStringResolver == null) { throw new ArgumentNullException(nameof(connectionStringResolver)); }
if (logger == null) { throw new ArgumentNullException(nameof(logger)); }
if (versionProviderFactory == null) { throw new ArgumentNullException(nameof(versionProviderFactory)); }
versionProviders = versionProviderFactory;
log = logger;
connectionString = connectionStringResolver.Resolve();
sqliteFilePath = connectionStringResolver.SqliteFilePath;
// possibly will change this later to have SqliteFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(SqliteFactory.Instance);
}
public DbSetup(
ILoggerFactory loggerFactory,
IOptions<ConnectionStringOptions> configuration,
IVersionProviderFactory versionProviderFactory)
{
if (loggerFactory == null) { throw new ArgumentNullException(nameof(loggerFactory)); }
if (configuration == null) { throw new ArgumentNullException(nameof(configuration)); }
if (versionProviderFactory == null) { throw new ArgumentNullException(nameof(versionProviderFactory)); }
versionProviders = versionProviderFactory;
logFactory = loggerFactory;
log = loggerFactory.CreateLogger(typeof(DbSetup).FullName);
writeConnectionString = configuration.Value.WriteConnectionString;
readConnectionString = configuration.Value.ReadConnectionString;
// possibly will change this later to have MySqlClientFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(MySqlClientFactory.Instance);
}
public DbSetup(
ILoggerFactory loggerFactory,
IOptions<MSSQLConnectionOptions> connectionOptions,
IVersionProviderFactory versionProviderFactory)
{
if (loggerFactory == null) { throw new ArgumentNullException(nameof(loggerFactory)); }
if (connectionOptions == null) { throw new ArgumentNullException(nameof(connectionOptions)); }
if (versionProviderFactory == null) { throw new ArgumentNullException(nameof(versionProviderFactory)); }
versionProviders = versionProviderFactory;
logFactory = loggerFactory;
log = loggerFactory.CreateLogger(typeof(DbSetup).FullName);
readConnectionString = connectionOptions.Value.ReadConnectionString;
writeConnectionString = connectionOptions.Value.WriteConnectionString;
ownerPrefix = connectionOptions.Value.OwnerPrefix;
// possibly will change this later to have SqlClientFactory/DbProviderFactory injected
AdoHelper = new AdoHelper(SqlClientFactory.Instance);
}
/// <summary>
/// Inserts a row in the mp_UserLocation table. Returns rows affected count.
/// </summary>
/// <param name="rowID"> rowID </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="iPAddress"> iPAddress </param>
/// <param name="iPAddressLong"> iPAddressLong </param>
/// <param name="hostname"> hostname </param>
/// <param name="longitude"> longitude </param>
/// <param name="latitude"> latitude </param>
/// <param name="iSP"> iSP </param>
/// <param name="continent"> continent </param>
/// <param name="country"> country </param>
/// <param name="region"> region </param>
/// <param name="city"> city </param>
/// <param name="timeZone"> timeZone </param>
/// <param name="captureCount"> captureCount </param>
/// <param name="firstCaptureUTC"> firstCaptureUTC </param>
/// <param name="lastCaptureUTC"> lastCaptureUTC </param>
/// <returns>int</returns>
public int Create(
Guid rowID,
Guid userGuid,
Guid siteGuid,
string iPAddress,
long iPAddressLong,
string hostname,
double longitude,
double latitude,
string iSP,
string continent,
string country,
string region,
string city,
string timeZone,
int captureCount,
DateTime firstCaptureUTC,
DateTime lastCaptureUTC)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_UserLocation ");
sqlCommand.Append("(");
sqlCommand.Append("RowID, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("IPAddress, ");
sqlCommand.Append("IPAddressLong, ");
sqlCommand.Append("Hostname, ");
sqlCommand.Append("Longitude, ");
sqlCommand.Append("Latitude, ");
sqlCommand.Append("ISP, ");
sqlCommand.Append("Continent, ");
sqlCommand.Append("Country, ");
sqlCommand.Append("Region, ");
sqlCommand.Append("City, ");
sqlCommand.Append("TimeZone, ");
sqlCommand.Append("CaptureCount, ");
sqlCommand.Append("FirstCaptureUTC, ");
sqlCommand.Append("LastCaptureUTC ");
sqlCommand.Append(")");
sqlCommand.Append(" VALUES ");
sqlCommand.Append("(");
sqlCommand.Append("@RowID, ");
sqlCommand.Append("@UserGuid, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@IPAddress, ");
sqlCommand.Append("@IPAddressLong, ");
sqlCommand.Append("@Hostname, ");
sqlCommand.Append("@Longitude, ");
sqlCommand.Append("@Latitude, ");
sqlCommand.Append("@ISP, ");
sqlCommand.Append("@Continent, ");
sqlCommand.Append("@Country, ");
sqlCommand.Append("@Region, ");
sqlCommand.Append("@City, ");
sqlCommand.Append("@TimeZone, ");
sqlCommand.Append("@CaptureCount, ");
sqlCommand.Append("@FirstCaptureUTC, ");
sqlCommand.Append("@LastCaptureUTC ");
sqlCommand.Append(")");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[17];
arParams[0] = new SqlCeParameter("@RowID", SqlDbType.UniqueIdentifier);
arParams[0].Value = rowID;
arParams[1] = new SqlCeParameter("@UserGuid", SqlDbType.UniqueIdentifier);
arParams[1].Value = userGuid;
arParams[2] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[2].Value = siteGuid;
arParams[3] = new SqlCeParameter("@IPAddress", SqlDbType.NVarChar, 50);
arParams[3].Value = iPAddress;
arParams[4] = new SqlCeParameter("@IPAddressLong", SqlDbType.BigInt);
arParams[4].Value = iPAddressLong;
arParams[5] = new SqlCeParameter("@Hostname", SqlDbType.NVarChar, 255);
arParams[5].Value = hostname;
arParams[6] = new SqlCeParameter("@Longitude", SqlDbType.Float);
arParams[6].Value = longitude;
arParams[7] = new SqlCeParameter("@Latitude", SqlDbType.Float);
arParams[7].Value = latitude;
arParams[8] = new SqlCeParameter("@ISP", SqlDbType.NVarChar, 255);
arParams[8].Value = iSP;
arParams[9] = new SqlCeParameter("@Continent", SqlDbType.NVarChar, 255);
arParams[9].Value = continent;
arParams[10] = new SqlCeParameter("@Country", SqlDbType.NVarChar, 255);
arParams[10].Value = country;
arParams[11] = new SqlCeParameter("@Region", SqlDbType.NVarChar, 255);
arParams[11].Value = region;
arParams[12] = new SqlCeParameter("@City", SqlDbType.NVarChar, 255);
arParams[12].Value = city;
arParams[13] = new SqlCeParameter("@TimeZone", SqlDbType.NVarChar, 255);
arParams[13].Value = timeZone;
arParams[14] = new SqlCeParameter("@CaptureCount", SqlDbType.Int);
arParams[14].Value = captureCount;
arParams[15] = new SqlCeParameter("@FirstCaptureUTC", SqlDbType.DateTime);
arParams[15].Value = firstCaptureUTC;
arParams[16] = new SqlCeParameter("@LastCaptureUTC", SqlDbType.DateTime);
arParams[16].Value = lastCaptureUTC;
int rowsAffected = AdoHelper.ExecuteNonQuery(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
private DataSearch <ImportDataViewModel> GetResults(DataGridoption option)
{
_logger.Debug("GetResults");
string message1 = "Records cannot be Entered Manully:";
string message = message1.Trim();
AdoHelper ado = new AdoHelper();
//SqlDataReader reader = null;
List <ImportDataViewModel> lstResults = new List <ImportDataViewModel>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
lstSqlParameter.Add(new SqlParameter("@CurrentPage", option.pageIndex));
lstSqlParameter.Add(new SqlParameter("@NoOfRecords", option.pageSize));
try
{
#region Manual Entry
SqlParameter param0 = new SqlParameter("@SelectedTests", option.searchBy);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDCheckTestType_READ", "RND", new object[] { param0 }))
{
if (reader.HasRows)
{
ImportDataViewModel ID = null;
while (reader.Read())
{
ID = new ImportDataViewModel();
ID.TestingNo = Convert.ToInt32(reader["TestingNo"]);
ID.TestType = Convert.ToString(reader["TestType"]);
ID.Active = Convert.ToChar(reader["Active"]);
if (ID.Active != '3')
{
if (message.Trim() == message1.Trim())
{
message += ID.TestType;
}
else
{
message += ", " + ID.TestType;
}
}
else
{
lstResults.Add(ID);
}
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
#endregion
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
DataSearch <ImportDataViewModel> ds = new DataSearch <ImportDataViewModel>
{
items = lstResults,
// total = (lstResults != null && lstResults.Count > 0) ? lstResults[0].total : 0,
total = (lstResults != null && lstResults.Count > 0) ? lstResults.Count : 0,
Message = message
};
return(ds);
}
/// <summary>
/// Retrieve the Processing Material data and Assign to Grid
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
private DataSearch <RNDProcessing> GetProcessingMaterial(DataGridoption option)
{
_logger.Debug("GetProcessingMaterial");
//SqlDataReader reader = null;
AdoHelper ado = new AdoHelper();
List <RNDProcessing> lstProcessingMaterial = new List <RNDProcessing>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
lstSqlParameter.Add(new SqlParameter("@CurrentPage", option.pageIndex));
lstSqlParameter.Add(new SqlParameter("@NoOfRecords", option.pageSize));
AddSearchFilter(option, lstSqlParameter);
try
{
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDProcessingMaterial_Read", "RND", lstSqlParameter.Cast <object>().ToArray()))
{
if (reader.HasRows)
{
RNDProcessing PM = null;
while (reader.Read())
{
PM = new RNDProcessing();
PM.RecID = Convert.ToInt32(reader["RecID"]);
PM.WorkStudyID = Convert.ToString(reader["WorkStudyID"]);
PM.MillLotNo = Convert.ToInt32(reader["MillLotNo"]);
PM.Hole = Convert.ToString(reader["Hole"]);
PM.PieceNo = Convert.ToString(reader["PieceNo"]);
PM.Sonum = Convert.ToString(reader["Sonum"]);
PM.ProcessNo = Convert.ToByte(reader["ProcessNo"]);
PM.ProcessID = Convert.ToString(reader["ProcessID"]);
PM.HTLogNo = Convert.ToInt32(reader["HTLogNo"]);
PM.HTLogID = Convert.ToString(reader["HTLogID"]);
PM.AgeLotNo = Convert.ToInt32(reader["AgeLotNo"]);
PM.AgeLotID = Convert.ToString(reader["AgeLotID"]);
PM.SHTTemp = Convert.ToString(reader["SHTTemp"]);
PM.SHSoakHrs = Convert.ToString(reader["SHSoakHrs"]);
PM.SHSoakMns = Convert.ToString(reader["SHSoakMns"]);
PM.SHTStartHrs = Convert.ToString(reader["SHTStartHrs"]);
PM.SHTStartMns = Convert.ToString(reader["SHTStartMns"]);
//PM.SHTDate = (!string.IsNullOrEmpty(reader["SHTDate"].ToString())) ? Convert.ToDateTime(reader["SHTDate"]) : (DateTime?)null;
PM.SHTDate = Convert.ToString(reader["SHTDate"]);
PM.StretchPct = Convert.ToString(reader["StretchPct"]);
PM.AfterSHTHrs = Convert.ToString(reader["AfterSHTHrs"]);
PM.AfterSHTMns = Convert.ToString(reader["AfterSHTMns"]);
PM.NatAgingHrs = Convert.ToString(reader["NatAgingHrs"]);
PM.NatAgingMns = Convert.ToString(reader["NatAgingMns"]);
PM.ArtStartHrs = Convert.ToString(reader["ArtStartHrs"]);
PM.ArtStartMns = Convert.ToString(reader["ArtStartMns"]);
//PM.ArtAgeDate = (!string.IsNullOrEmpty(reader["ArtAgeDate"].ToString())) ? Convert.ToDateTime(reader["ArtAgeDate"]) : (DateTime?)null;
PM.ArtAgeDate = Convert.ToString(reader["ArtAgeDate"]);
PM.ArtAgeTemp1 = Convert.ToString(reader["ArtAgeTemp1"]);
PM.ArtAgeHrs1 = Convert.ToString(reader["ArtAgeHrs1"]);
PM.ArtAgeMns1 = Convert.ToString(reader["ArtAgeMns1"]);
PM.ArtAgeTemp2 = Convert.ToString(reader["ArtAgeTemp2"]);
PM.ArtAgeHrs2 = Convert.ToString(reader["ArtAgeHrs2"]);
PM.ArtAgeMns2 = Convert.ToString(reader["ArtAgeMns2"]);
PM.ArtAgeTemp3 = Convert.ToString(reader["ArtAgeTemp3"]);
PM.ArtAgeHrs3 = Convert.ToString(reader["ArtAgeHrs3"]);
PM.ArtAgeMns3 = Convert.ToString(reader["ArtAgeMns3"]);
PM.FinalTemper = Convert.ToString(reader["FinalTemper"]);
PM.TargetCount = Convert.ToString(reader["TargetCount"]);
PM.ActualCount = Convert.ToString(reader["ActualCount"]);
// PM.RCS = Convert.ToString(reader["RCS"]);
PM.RNDLotID = Convert.ToString(reader["RNDLotID"]);
PM.total = Convert.ToInt32(reader["total"]);
lstProcessingMaterial.Add(PM);
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
DataSearch <RNDProcessing> ds = new DataSearch <RNDProcessing>
{
items = lstProcessingMaterial,
total = (lstProcessingMaterial != null && lstProcessingMaterial.Count > 0) ? lstProcessingMaterial[0].total : 0
};
return(ds);
}
private static void TestTransaction()
{
AdoHelper db = MyDB.GetDBHelper();
EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db);
AuctionOperationLog optLog = new AuctionOperationLog();
optLog.OperaterID = 1000;
optLog.Module = "Login";
optLog.Operation = "登录成功1";
optLog.LogSource = "PC";
db.BeginTransaction();
try
{
query.Insert(optLog);
//必须设置为全部属性已经修改，否则仅会更新 Operation 字段
optLog.ResetChanges(true);
optLog.Operation = "退出登录";
query.Insert(optLog);
//optLog.Module = "Login";
//OQL q = OQL.From(optLog).Select().Where(optLog.Module).END;
OQL q = new OQL(optLog);
//q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%"));
q.Select().Count(optLog.OperaterID, "");//使用空字符串参数，这样统计的值将放到 OperaterID 属性中
//必须指定db参数，否则不再一个事务中，无法进行统计查询
optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db);
int allCount = optLog.OperaterID;
//optLog 已经使用过，在生成OQL的查询前，必须使用新的实体对象，
// 否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错
optLog = new AuctionOperationLog();
q = new OQL(optLog);
q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc");
q.Limit(10, 2);
q.PageEnable = true;
q.PageWithAllRecordCount = allCount;
//查询列表并更新到数据库
List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db);
foreach (AuctionOperationLog logItem in list)
{
logItem.AtDateTime = DateTime.Now;
}
query.Update(list);
db.Commit();
Console.WriteLine("事务操作成功。");
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message);
db.Rollback();
}
}
public ResultDto InsertUpdateGroupMeeting(GroupMeetingDto Groupmeeting, string GroupMeetingMembers)
{
AdoHelper obj = new AdoHelper();
ResultDto resultDto = new ResultDto();
string obectName = "Group Meeting";
try
{
SqlParameter[] parms = new SqlParameter[12];
parms[0] = new SqlParameter("@GroupId", Groupmeeting.GroupID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
parms[1] = new SqlParameter("@MeetingObjective", Groupmeeting.MeetingObjective);
parms[1].SqlDbType = System.Data.SqlDbType.VarChar;
parms[2] = new SqlParameter("@MeetingComments", Groupmeeting.MeetingComments);
parms[2].SqlDbType = System.Data.SqlDbType.VarChar;
parms[3] = new SqlParameter("@MeetingDate", Groupmeeting.MeetingDate);
parms[3].SqlDbType = System.Data.SqlDbType.Date;
parms[4] = new SqlParameter("@TransactionDate", Groupmeeting.TransactionDate);
parms[4].SqlDbType = System.Data.SqlDbType.Date;
parms[5] = new SqlParameter("@IsConducted", Groupmeeting.IsConducted);
parms[5].SqlDbType = System.Data.SqlDbType.Bit;
parms[6] = new SqlParameter("@IsSplMeeting", Groupmeeting.IsSplMeeting);
parms[6].SqlDbType = System.Data.SqlDbType.Bit;
parms[7] = new SqlParameter("@Reason", Groupmeeting.Reason);
parms[7].SqlDbType = System.Data.SqlDbType.VarChar;
parms[8] = new SqlParameter("@OtherReason", Groupmeeting.OtherReason);
parms[8].SqlDbType = System.Data.SqlDbType.VarChar;
parms[9] = new SqlParameter("@UserId", Groupmeeting.UserId);
parms[9].SqlDbType = System.Data.SqlDbType.Int;
parms[10] = new SqlParameter("@GroupMeetingmember", GroupMeetingMembers);
parms[10].SqlDbType = System.Data.SqlDbType.NVarChar;
parms[11] = new SqlParameter("@GroupMeetingID", Groupmeeting.GroupMeetingID);
parms[11].SqlDbType = System.Data.SqlDbType.Int;
parms[11].Direction = ParameterDirection.InputOutput;
obj.ExecNonQueryProc("UspGroupMeetingInsertUpdate", parms);
resultDto.ObjectId = (int)parms[11].Value;
if (resultDto.ObjectId > 0)
{
resultDto.Message = string.Format("{0} details saved successfully", obectName);
}
else if (resultDto.ObjectId == -99)
{
resultDto.Message = string.Format("Error occured while saving {0} details", obectName);
}
else if (resultDto.ObjectId == -98)
{
resultDto.Message = string.Format("MEETING ALREADY CONDUCTED/NOT CONDUCTED BOTH TYPE OF ENTRIES NOT POSSIBLE", obectName);
}
else if (resultDto.ObjectId == -97)
{
resultDto.Message = string.Format("MULTIPLE NOT CONDUCTED SHOULD NOT ALLOWED", obectName);
}
else if (resultDto.ObjectId == -96)
{
resultDto.Message = string.Format("ALREADY MEETING EXISTIS WITH GIVEN DATE", obectName);
}
}
catch (Exception)
{
resultDto.Message = string.Format("Service layer error occured while saving the {0} details", obectName);
resultDto.ObjectId = -98;
}
return(resultDto);
}
private List <Language> GetAllItems(bool force = false)
{
return(AdoHelper.ExecCachedListProc <Language>("sp_cms_languages_select", force));
}
void TestOqlPage()
{
UserEntity ue = new UserEntity();
OQL q = OQL.From(ue)
.Select(ue.ID, ue.Name, ue.Age)
.Where(cmp => cmp.Comparer(ue.Age, ">", 20))
.OrderBy(ue.Age)
.END;
q.Limit(2, 3, true);
Console.WriteLine("q:Page SQL is \r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
//当前测试总记录数5，查询后，OQL会得到总记录数
AdoHelper db = MyDB.GetDBHelperByConnectionName("conn2");
var list = EntityQuery <UserEntity> .QueryList(q, db);
q = OQL.From(ue)
.Select(ue.Age).Sum(ue.Age, "sum_age")
.GroupBy(ue.Age)
.OrderBy(ue.Age)
.END;
q.Limit(2);
var list2 = EntityQuery <UserEntity> .QueryList(q, db);
Users user = new Users()
{
NickName = "pdf.net", RoleID = RoleNames.Admin, Age = 20
};
UserRoles roles = new UserRoles()
{
RoleName = "role1"
};
//测试字段直接比较
OQL q00 = OQL.From(user)
.Select(user.ID, user.NickName, user.LastLoginIP)
.Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime))
.OrderBy(o => o.Desc(user.LastLoginTime))
.END;
Console.WriteLine("q00:one table and select all fields \r\n{0}", q00);
Console.WriteLine(q00.PrintParameterInfo());
string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999);
Console.WriteLine("Page SQL");
Console.WriteLine(pageSql);
OQL q2 = OQL.From(user)
.InnerJoin(roles).On(user.RoleID, roles.ID)
.Select(user.RoleID, roles.RoleName)
.Where(user.NickName, roles.RoleName)
.GroupBy(user.RoleID, roles.RoleName)
.OrderBy(user.ID)
.END;
Console.WriteLine("q2:two table query use join\r\n{0}", q2);
Console.WriteLine(q2.PrintParameterInfo());
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999);
Console.WriteLine("Page SQL");
Console.WriteLine(pageSql);
}
private List <Tenant> GetAllItems(bool force = false)
{
return(AdoHelper.ExecCachedListProc <Tenant>("sp_admin_tenants_select", force));
}
///// <summary>
///// Gets a count of rows in the mp_UserLocation table.
///// </summary>
///// <param name="siteGuid"> siteGuid </param>
//public int GetCountBySite(Guid siteGuid)
//{
// NpgsqlParameter[] arParams = new NpgsqlParameter[1];
// arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
// arParams[0].Value = siteGuid.ToString();
// StringBuilder sqlCommand = new StringBuilder();
// sqlCommand.Append("SELECT Count(*) ");
// sqlCommand.Append("FROM mp_userlocation ");
// sqlCommand.Append("WHERE ");
// sqlCommand.Append("siteguid = :siteguid ");
// sqlCommand.Append(";");
// object obj = AdoHelper.ExecuteScalar(
// readConnectionString,
// CommandType.Text,
// sqlCommand.ToString(),
// arParams);
// return Convert.ToInt32(obj);
//}
/// <summary>
/// Gets a page of data from the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public DbDataReader GetPageByUser(
Guid userGuid,
int pageNumber,
int pageSize)
{
//totalPages = 1;
//int totalRows
// = GetCountByUser(userGuid);
//if (pageSize > 0) totalPages = totalRows / pageSize;
//if (totalRows <= pageSize)
//{
// totalPages = 1;
//}
//else
//{
// int remainder;
// Math.DivRem(totalRows, pageSize, out remainder);
// if (remainder > 0)
// {
// totalPages += 1;
// }
//}
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = userGuid.ToString();
arParams[1] = new NpgsqlParameter("pagenumber", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = pageNumber;
arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Value = pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_userlocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("userguid = :userguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ipaddresslong ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(AdoHelper.ExecuteReader(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
//return AdoHelper.ExecuteReader(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_userlocation_selectpagebyuser(:userguid,:pagenumber,:pagesize)",
// arParams);
}
/// <summary>
/// Inserts a row in the mp_UserLocation table. Returns rows affected count.
/// </summary>
/// <param name="rowID"> rowID </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="iPAddress"> iPAddress </param>
/// <param name="iPAddressLong"> iPAddressLong </param>
/// <param name="hostname"> hostname </param>
/// <param name="longitude"> longitude </param>
/// <param name="latitude"> latitude </param>
/// <param name="iSP"> iSP </param>
/// <param name="continent"> continent </param>
/// <param name="country"> country </param>
/// <param name="region"> region </param>
/// <param name="city"> city </param>
/// <param name="timeZone"> timeZone </param>
/// <param name="captureCount"> captureCount </param>
/// <param name="firstCaptureUTC"> firstCaptureUTC </param>
/// <param name="lastCaptureUTC"> lastCaptureUTC </param>
/// <returns>int</returns>
public int Create(
Guid rowID,
Guid userGuid,
Guid siteGuid,
string iPAddress,
long iPAddressLong,
string hostname,
double longitude,
double latitude,
string iSP,
string continent,
string country,
string region,
string city,
string timeZone,
int captureCount,
DateTime firstCaptureUTC,
DateTime lastCaptureUTC)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[17];
arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = rowID.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Value = userGuid.ToString();
arParams[2] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Value = siteGuid.ToString();
arParams[3] = new NpgsqlParameter("ipaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[3].Value = iPAddress;
arParams[4] = new NpgsqlParameter("ipaddresslong", NpgsqlTypes.NpgsqlDbType.Bigint);
arParams[4].Value = iPAddressLong;
arParams[5] = new NpgsqlParameter("hostname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Value = hostname;
arParams[6] = new NpgsqlParameter("longitude", NpgsqlTypes.NpgsqlDbType.Double);
arParams[6].Value = longitude;
arParams[7] = new NpgsqlParameter("latitude", NpgsqlTypes.NpgsqlDbType.Double);
arParams[7].Value = latitude;
arParams[8] = new NpgsqlParameter("isp", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[8].Value = iSP;
arParams[9] = new NpgsqlParameter("continent", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[9].Value = continent;
arParams[10] = new NpgsqlParameter("country", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[10].Value = country;
arParams[11] = new NpgsqlParameter("region", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Value = region;
arParams[12] = new NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[12].Value = city;
arParams[13] = new NpgsqlParameter("timezone", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[13].Value = timeZone;
arParams[14] = new NpgsqlParameter("capturecount", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[14].Value = captureCount;
arParams[15] = new NpgsqlParameter("firstcaptureutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[15].Value = firstCaptureUTC;
arParams[16] = new NpgsqlParameter("lastcaptureutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[16].Value = lastCaptureUTC;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_userlocation (");
sqlCommand.Append("rowid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("ipaddress, ");
sqlCommand.Append("ipaddresslong, ");
sqlCommand.Append("hostname, ");
sqlCommand.Append("longitude, ");
sqlCommand.Append("latitude, ");
sqlCommand.Append("isp, ");
sqlCommand.Append("continent, ");
sqlCommand.Append("country, ");
sqlCommand.Append("region, ");
sqlCommand.Append("city, ");
sqlCommand.Append("timezone, ");
sqlCommand.Append("capturecount, ");
sqlCommand.Append("firstcaptureutc, ");
sqlCommand.Append("lastcaptureutc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":rowid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":ipaddress, ");
sqlCommand.Append(":ipaddresslong, ");
sqlCommand.Append(":hostname, ");
sqlCommand.Append(":longitude, ");
sqlCommand.Append(":latitude, ");
sqlCommand.Append(":isp, ");
sqlCommand.Append(":continent, ");
sqlCommand.Append(":country, ");
sqlCommand.Append(":region, ");
sqlCommand.Append(":city, ");
sqlCommand.Append(":timezone, ");
sqlCommand.Append(":capturecount, ");
sqlCommand.Append(":firstcaptureutc, ");
sqlCommand.Append(":lastcaptureutc ");
sqlCommand.Append(")");
sqlCommand.Append(";");
//int rowsAffected = AdoHelper.ExecuteNonQuery(
// writeConnectionString,
// CommandType.StoredProcedure,
// "mp_userlocation_insert(:rowid,:userguid,:siteguid,:ipaddress,:ipaddresslong,:hostname,:longitude,:latitude,:isp,:continent,:country,:region,:city,:timezone,:capturecount,:firstcaptureutc,:lastcaptureutc)",
// arParams);
int rowsAffected = AdoHelper.ExecuteNonQuery(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
private List <PageTemplate> GetAllItems(bool force = false)
{
return(AdoHelper.ExecCachedListProc <PageTemplate>("sp_cms_page_templates_select", force));
}
private List <SysVar> GetAllItems()
{
return(AdoHelper.ExecCachedListProc <SysVar>("sp_cms_SysVar_select", true));
}
public int InsertUpdateAccountHead(AccountHeadDto accountHead, string Origin, int OriginObjectId, out string ahCode)
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[15];
parms[0] = new SqlParameter("@AHID", accountHead.AHID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
parms[0].Size = 4;
parms[0].Direction = ParameterDirection.InputOutput;
parms[1] = new SqlParameter("@AHCode", accountHead.AHCode);
parms[1].SqlDbType = System.Data.SqlDbType.VarChar;
parms[1].Size = 256;
parms[1].Direction = ParameterDirection.InputOutput;
parms[2] = new SqlParameter("@AHName", accountHead.AHName);
parms[2].SqlDbType = System.Data.SqlDbType.VarChar;
parms[3] = new SqlParameter("@TE_AHName", accountHead.TE_AHName);
parms[3].SqlDbType = System.Data.SqlDbType.NVarChar;
parms[4] = new SqlParameter("@AHType", accountHead.AHType);
parms[4].SqlDbType = System.Data.SqlDbType.Int;
parms[5] = new SqlParameter("@ParentAHID", accountHead.ParentAHID);
parms[5].SqlDbType = System.Data.SqlDbType.Int;
parms[6] = new SqlParameter("@IsMemberTransaction", accountHead.IsMemberTransaction);
parms[6].SqlDbType = System.Data.SqlDbType.Bit;
parms[7] = new SqlParameter("@IsSLAccount", accountHead.IsSLAccount);
parms[7].SqlDbType = System.Data.SqlDbType.Bit;
parms[8] = new SqlParameter("@OpeningBalance", accountHead.OpeningBalance);
parms[8].SqlDbType = System.Data.SqlDbType.Money;
parms[9] = new SqlParameter("@OpeningBalanceType", accountHead.OpeningBalanceType);
parms[9].SqlDbType = System.Data.SqlDbType.VarChar;
parms[10] = new SqlParameter("@AHLevel", accountHead.AHLevel);
parms[10].SqlDbType = System.Data.SqlDbType.Int;
parms[11] = new SqlParameter("@IsFederation", accountHead.IsFederation);
parms[11].SqlDbType = System.Data.SqlDbType.Bit;
parms[12] = new SqlParameter("@UserID", accountHead.UserID);
parms[12].SqlDbType = System.Data.SqlDbType.Int;
parms[13] = new SqlParameter("@Origin", Origin);
parms[13].SqlDbType = System.Data.SqlDbType.VarChar;
parms[14] = new SqlParameter("@OriginObjectId", OriginObjectId);
parms[14].SqlDbType = System.Data.SqlDbType.Int;
int rowCount = obj.ExecNonQueryProc("uspAccountHeadInsertUpdate", parms);
int ahId = (int)parms[0].Value;
ahCode = (string)parms[1].Value;
return(ahId);
}
/// <summary>
/// 用连接字符串名字初始化本类
/// </summary>
/// <param name="connName"></param>
public PostgreSQLDbContext(AdoHelper db)
{
this.CurrentDataBase = db;
}
/// <summary>
/// Inserts a row in the mp_TaskQueue table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="queuedBy"> queuedBy </param>
/// <param name="taskName"> taskName </param>
/// <param name="notifyOnCompletion"> notifyOnCompletion </param>
/// <param name="notificationToEmail"> notificationToEmail </param>
/// <param name="notificationFromEmail"> notificationFromEmail </param>
/// <param name="notificationSubject"> notificationSubject </param>
/// <param name="taskCompleteMessage"> taskCompleteMessage </param>
/// <param name="canStop"> canStop </param>
/// <param name="canResume"> canResume </param>
/// <param name="updateFrequency"> updateFrequency </param>
/// <param name="queuedUTC"> queuedUTC </param>
/// <param name="completeRatio"> completeRatio </param>
/// <param name="status"> status </param>
/// <param name="serializedTaskObject"> serializedTaskObject </param>
/// <param name="serializedTaskType"> serializedTaskType </param>
/// <returns>int</returns>
public int Create(
Guid guid,
Guid siteGuid,
Guid queuedBy,
string taskName,
bool notifyOnCompletion,
string notificationToEmail,
string notificationFromEmail,
string notificationSubject,
string taskCompleteMessage,
bool canStop,
bool canResume,
int updateFrequency,
DateTime queuedUTC,
double completeRatio,
string status,
string serializedTaskObject,
string serializedTaskType)
{
#region Bit Conversion
int intNotifyOnCompletion;
if (notifyOnCompletion)
{
intNotifyOnCompletion = 1;
}
else
{
intNotifyOnCompletion = 0;
}
int intCanStop;
if (canStop)
{
intCanStop = 1;
}
else
{
intCanStop = 0;
}
int intCanResume;
if (canResume)
{
intCanResume = 1;
}
else
{
intCanResume = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_TaskQueue (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("QueuedBy, ");
sqlCommand.Append("TaskName, ");
sqlCommand.Append("NotifyOnCompletion, ");
sqlCommand.Append("NotificationToEmail, ");
sqlCommand.Append("NotificationFromEmail, ");
sqlCommand.Append("NotificationSubject, ");
sqlCommand.Append("TaskCompleteMessage, ");
sqlCommand.Append("CanStop, ");
sqlCommand.Append("CanResume, ");
sqlCommand.Append("UpdateFrequency, ");
sqlCommand.Append("QueuedUTC, ");
sqlCommand.Append("CompleteRatio, ");
sqlCommand.Append("Status, ");
sqlCommand.Append("SerializedTaskObject, ");
sqlCommand.Append("SerializedTaskType )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?QueuedBy, ");
sqlCommand.Append("?TaskName, ");
sqlCommand.Append("?NotifyOnCompletion, ");
sqlCommand.Append("?NotificationToEmail, ");
sqlCommand.Append("?NotificationFromEmail, ");
sqlCommand.Append("?NotificationSubject, ");
sqlCommand.Append("?TaskCompleteMessage, ");
sqlCommand.Append("?CanStop, ");
sqlCommand.Append("?CanResume, ");
sqlCommand.Append("?UpdateFrequency, ");
sqlCommand.Append("?QueuedUTC, ");
sqlCommand.Append("?CompleteRatio, ");
sqlCommand.Append("?Status, ");
sqlCommand.Append("?SerializedTaskObject, ");
sqlCommand.Append("?SerializedTaskType )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[17];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?QueuedBy", MySqlDbType.VarChar, 36);
arParams[2].Value = queuedBy.ToString();
arParams[3] = new MySqlParameter("?TaskName", MySqlDbType.VarChar, 255);
arParams[3].Value = taskName;
arParams[4] = new MySqlParameter("?NotifyOnCompletion", MySqlDbType.Int32);
arParams[4].Value = intNotifyOnCompletion;
arParams[5] = new MySqlParameter("?NotificationToEmail", MySqlDbType.VarChar, 255);
arParams[5].Value = notificationToEmail;
arParams[6] = new MySqlParameter("?NotificationFromEmail", MySqlDbType.VarChar, 255);
arParams[6].Value = notificationFromEmail;
arParams[7] = new MySqlParameter("?NotificationSubject", MySqlDbType.VarChar, 255);
arParams[7].Value = notificationSubject;
arParams[8] = new MySqlParameter("?TaskCompleteMessage", MySqlDbType.Blob);
arParams[8].Value = taskCompleteMessage;
arParams[9] = new MySqlParameter("?CanStop", MySqlDbType.Int32);
arParams[9].Value = intCanStop;
arParams[10] = new MySqlParameter("?CanResume", MySqlDbType.Int32);
arParams[10].Value = intCanResume;
arParams[11] = new MySqlParameter("?UpdateFrequency", MySqlDbType.Int32);
arParams[11].Value = updateFrequency;
arParams[12] = new MySqlParameter("?QueuedUTC", MySqlDbType.DateTime);
arParams[12].Value = queuedUTC;
arParams[13] = new MySqlParameter("?CompleteRatio", MySqlDbType.Float);
arParams[13].Value = completeRatio;
arParams[14] = new MySqlParameter("?Status", MySqlDbType.VarChar, 255);
arParams[14].Value = status;
arParams[15] = new MySqlParameter("?SerializedTaskObject", MySqlDbType.LongText);
arParams[15].Value = serializedTaskObject;
arParams[16] = new MySqlParameter("?SerializedTaskType", MySqlDbType.VarChar, 255);
arParams[16].Value = serializedTaskType;
int rowsAffected = AdoHelper.ExecuteNonQuery(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public int Update <T>(List <T> entityList)
{
if (entityList == null || entityList.Count == 0)
{
return(0);
}
object val = 0;
IDbTransaction transaction = null;
IDbConnection connection = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
connection = GetConnection();
transaction = GetTransaction();
T firstEntity = entityList[0];
Type classType = firstEntity.GetType();
PropertyInfo[] properties = ReflectionHelper.GetProperties(firstEntity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(firstEntity, DbOperateType.UPDATE, properties);
String strSQL = EntityHelper.GetUpdateSql(tableInfo);
/*tableInfo.Columns.Add(tableInfo.Id.Key, tableInfo.Id.Value);
* IDbDataParameter[] parms = tableInfo.GetParameters();*/
foreach (T entity in entityList)
{
tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.UPDATE, properties);
List <IDbDataParameter> paramsList = tableInfo.GetParameterList();
IDbDataParameter dbParameter = DbFactory.CreateDbParameter(tableInfo.Id.Key, tableInfo.Id.Value);
paramsList.Add(dbParameter);
IDbDataParameter[] parms = tableInfo.GetParameters(paramsList);
val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL, parms);
/*if (AdoHelper.DbType == DatabaseType.ACCESS)
* {
* strSQL = SQLBuilderHelper.builderAccessSQL(classType, tableInfo, strSQL, parms);
* val = AdoHelper.ExecuteNonQuery(connection, CommandType.Text, strSQL);
* }
* else
* {
* val = AdoHelper.ExecuteNonQuery(connection, CommandType.Text, strSQL, parms);
* }*/
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (m_Transaction == null)
{
connection.Close();
}
}
return(Convert.ToInt32(val));
}
public GroupMasterDto GetGroupMasterDetailsByID(int groupID)
{
GroupMasterDto grp = new GroupMasterDto();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupId", groupID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader dr = obj.ExecDataReaderProc("uspGroupGetByGroupId", parms);
if (dr.Read())
{
grp.GroupID = Convert.ToInt32(dr["GroupID"]);
grp.GroupCode = Convert.ToString(dr["GroupCode"]);
grp.GroupRefNumber = Convert.ToString(dr["GroupRefNumber"]);
grp.GroupName = Convert.ToString(dr["GroupName"]);
grp.TEGroupName = Convert.ToString(dr["TEGroupName"]);
grp.PanchayatID = Convert.ToInt32(dr["PanchayatID"]);
grp.VillageID = Convert.ToInt32(dr["VillageID"]);
grp.ClusterID = Convert.ToInt32(dr["ClusterID"]);
grp.ClusterName = Convert.ToString(dr["ClusterName"]);
grp.Village = Convert.ToString(dr["Village"]);
if (dr["FormationDate"] != DBNull.Value)
{
grp.FormationDate = Convert.ToDateTime(dr["FormationDate"]);
}
grp.Phone = Convert.ToString(dr["Phone"]);
grp.Email = Convert.ToString(dr["Email"]);
grp.Address = Convert.ToString(dr["Address"]);
if (dr["MeetingFrequency"] != DBNull.Value)
{
grp.MeetingFrequency = Convert.ToInt32(dr["MeetingFrequency"]);
}
if (dr["FederationTranStartDate"] != DBNull.Value)
{
grp.FederationTranStartDate = Convert.ToDateTime(dr["FederationTranStartDate"]);
}
if (dr["DateOfClosure"] != DBNull.Value)
{
grp.DateOfClosure = Convert.ToDateTime(dr["DateOfClosure"]);
}
if (dr["MeetingDay"] != DBNull.Value)
{
grp.MeetingDay = Convert.ToByte(dr["MeetingDay"]);
}
if (dr["MeetingStartTime"] != DBNull.Value)
{
grp.MeetingStartTime = (TimeSpan)dr["MeetingStartTime"];
}
if (dr["MeetingEndTime"] != DBNull.Value)
{
grp.MeetingEndTime = (TimeSpan)dr["MeetingEndTime"];
}
if (dr["RegularSavingAmount"] != DBNull.Value)
{
grp.RegularSavingAmount = Convert.ToDecimal(dr["RegularSavingAmount"]);
}
if (dr["RegularSavingsAhId"] != DBNull.Value)
{
grp.RegularSavingsAhId = Convert.ToInt32(dr["RegularSavingsAhId"]);
}
grp.RegularSavingAccountHead = Convert.ToString(dr["RegularSavingAccountHead"]);
if (dr["MeetingDate"] != DBNull.Value)
{
grp.MeetingDate = Convert.ToDateTime(dr["MeetingDate"]);
}
grp.LockStatus = Convert.ToString(dr["LockStatus"]);
grp.LockStatusCode = Convert.ToString(dr["LockStatusCode"]);
}
return(grp);
}
public PageResult <T> FindPage <T>(string strSQL, ParamMap param) where T : new()
{
PageResult <T> pageResult = new PageResult <T>();
List <T> list = new List <T>();
IDataReader sdr = null;
IDbConnection connection = null;
try
{
connection = GetConnection();
bool closeConnection = GetWillConnectionState();
strSQL = strSQL.ToLower();
String countSQL = SQLBuilderHelper.builderCountSQL(strSQL);
String columns = SQLBuilderHelper.fetchColumns(strSQL);
int count = this.Count(countSQL, param);
T entity = new T();
Type classType = entity.GetType();
PropertyInfo[] properties = ReflectionHelper.GetProperties(classType);
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties);
if (param.IsPage && !SQLBuilderHelper.isPage(strSQL))
{
strSQL = SQLBuilderHelper.builderPageSQL(strSQL, param.OrderFields, param.IsDesc);
}
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
if (param.getInt("page_offset") > count)
{
int limit = param.getInt("page_limit") + count - param.getInt("page_offset");
if (limit > 0)
{
strSQL = SQLBuilderHelper.builderAccessPageSQL(strSQL, param, limit);
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSQL, param.toDbParameters());
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
}
else
{
strSQL = SQLBuilderHelper.builderAccessPageSQL(strSQL, param);
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSQL, param.toDbParameters());
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
}
else
{
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSQL, param.toDbParameters());
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
pageResult.Total = count;
pageResult.DataList = list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(pageResult);
}
static void Main(string[] args)
{
Console.WriteLine("====**************** PDF.NET SOD 控制台测试程序 **************====");
Assembly coreAss = Assembly.GetAssembly(typeof(AdoHelper));//获得引用程序集
Console.WriteLine("框架核心程序集 PWMIS.Core Version:{0}", coreAss.GetName().Version.ToString());
Console.WriteLine();
Console.WriteLine(" 应用程序配置文件默认的数据库配置信息：\r\n 当前使用的数据库类型是：{0}\r\n 连接字符串为:{1}\r\n 请确保数据库服务器和数据库是否有效且已经初始化过建表脚本（项目下的2个sql脚本文件），\r\n继续请回车，退出请输入字母 Q ."
, MyDB.Instance.CurrentDBMSType.ToString(), MyDB.Instance.ConnectionString);
Console.WriteLine("=====Power by Bluedoctor,2015.2.8 http://www.pwmis.com/sqlmap ====");
string read = Console.ReadLine();
if (read.ToUpper() == "Q")
{
return;
}
Console.WriteLine("当前机器的分布式ID：{0}", CommonUtil.CurrentMachineID());
Console.WriteLine("测试分布式ID：秒级有序");
for (int i = 0; i < 50; i++)
{
Console.Write(CommonUtil.NewSequenceGUID());
Console.Write(",");
}
Console.WriteLine();
Console.WriteLine("测试分布式ID：唯一且有序");
for (int i = 0; i < 20000; i++)
{
long seq = CommonUtil.NewUniqueSequenceGUID();
if (i <= 50)
{
Console.Write(CommonUtil.NewUniqueSequenceGUID());
Console.Write(",");
}
}
Console.WriteLine();
IDataParameter[] paraArr = new IDataParameter[] {
MyDB.Instance.GetParameter("P1", 111),
MyDB.Instance.GetParameter("P2", "abc'ee<edde/>e"),
MyDB.Instance.GetParameter("P3", DBNull.Value),
};
string str = DbParameterSerialize.Serialize(paraArr);
Console.WriteLine("测试参数序列化：{0}", str);
IDataParameter[] paraArr2 = DbParameterSerialize.DeSerialize(str, MyDB.Instance);
Console.WriteLine("测试反序列化成功！");
LocalDbContext localDb = new LocalDbContext();
var entitys = localDb.ResolveAllEntitys();
localDb.CurrentDataBase.RegisterCommandHandle(new TransactionLogHandle());
Table_User user = new Table_User();
user.Name = "zhang san";
user.Height = 1.8f;
user.Birthday = new DateTime(1980, 1, 1);
user.Sex = true;
localDb.Add(user);
user.Name = "lisi";
user.Height = 1.6f;
user.Birthday = new DateTime(1982, 3, 1);
user.Sex = false;
localDb.Add(user);
Console.WriteLine("测试 生成事务日志 成功！（此操作将写入事务日志信息到数据库中。）");
//var logList = localDb.QueryList<MyCommandLogEntity>(OQL.From(new MyCommandLogEntity()).Select().END);
AdoHelper db = MyDB.GetDBHelperByConnectionName("local");
var logList = OQL.From <MyCommandLogEntity>().Select().END.ToList(db);
foreach (MyCommandLogEntity log in logList)
{
var paras = DbParameterSerialize.DeSerialize(log.ParameterInfo, db);
int count = db.ExecuteNonQuery(log.CommandText, log.CommandType, paras);
Console.WriteLine("执行语句：{0} \r\n 受影响行数：{1}", log.CommandText, count);
}
Console.WriteLine("测试 运行事务日志 成功！（此操作将事务日志的操作信息回放执行。）");
//写入10000条日志，有缓存，可能不会写完
Console.WriteLine("测试日志写入10000 条信息...");
CommandLog loger = new CommandLog();
for (int t = 0; t <= 1; t++)
{
System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(WriteLog));
thread.Name = "thread" + t;
thread.Start(loger);
}
loger.Flush();
Console.WriteLine("按任意键继续");
Console.ReadLine();
EntityUser etu = new EntityUser();
ITable_User itu = etu.AsEntity();
DateTime dtt = itu.Birthday;
//测试 AdoHelper的并发能力
//for (int i = 0; i < 100; i++)
//{
// System.Threading.Thread t = new System.Threading.Thread(
// new System.Threading.ParameterizedThreadStart(TestDataSetAndOQL));
// t.Name = "thread "+i;
// t.Start();
//}
//测试生成列的脚本
EntityCommand ecmd = new EntityCommand(new Table_User(), new SqlServer());
string table_script = ecmd.CreateTableCommand;
Console.Write("1，测试 OpenSession 长连接数据库访问...");
TestDataSetAndOQL(null);
Console.WriteLine("OK");
//
Console.WriteLine("2，测试OQL 转SQL...");
RoadTeam.Model.CS.TbCsEvent CsEvent = new RoadTeam.Model.CS.TbCsEvent();
CsEvent.EventID = 1;
OQL oql = OQL.From(CsEvent)
.Select(CsEvent.EventCheck, CsEvent.EventCheckInfo, CsEvent.EventCheckor, CsEvent.EventCheckTime)
.Where(CsEvent.EventID)
.END;
Console.WriteLine(oql.ToString());
Console.WriteLine("-----------------------");
RoadTeam.Model.CS.TbCsEvent CsEvent2 = new RoadTeam.Model.CS.TbCsEvent();
CsEvent.EventID = 1;
OQL oql2 = OQL.From(CsEvent2)
.Select(true, CsEvent2.EventCheck, CsEvent2.EventCheckInfo, CsEvent2.EventCheckor, CsEvent2.EventCheckTime)
.Where(CsEvent2.EventID)
.END;
Console.WriteLine(oql2.ToString());
Console.WriteLine("-----------------------");
Console.WriteLine("OK");
//
Console.Write("3，测试实体类动态增加虚拟属性...");
UserModels um1 = new UserModels();
um1.AddPropertyName("TestName");
um1["TestName"] = 123;
int testi = (int)um1["TestName"];
um1["TestName"] = "abc";
string teststr = (string)um1["TestName"];
Console.WriteLine("OK");
//
Console.Write("4，测试缓存...");
var cache = PWMIS.Core.MemoryCache <EntityBase> .Default;
cache.Add("UserModels", um1);
var cacheData = cache.Get("UserModels");
cache.Remove("UserModels");
Console.WriteLine("OK");
//
Console.Write("5，测试自动创建实体类数据库表...");
AutoCreateEntityTable <LT_Users>();
AutoCreateEntityTable <LT_UserRoles>();
Console.WriteLine("OK");
Console.WriteLine("------------测试暂时停止，回车继续运行------");
Console.ReadLine();
//return;
Console.Write("6，测试实体类的外键查询...");
TestEntityFK();
Console.WriteLine("OK");
Console.Write("7，测试实体类批量插入...");
OqlInTest();
Console.WriteLine("OK");
Console.WriteLine("8，测试SOD POCO实体类性能...");
Console.WriteLine("SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User");
for (int i = 0; i < 10; i++)
{
Console.WriteLine("-------------Testt No.{0}----------------", i + 1);
TestPocoQuery();
}
Console.WriteLine("--------OK---------------");
Console.Write("9，测试OQL IN 子查询...");
TestInChild();
Console.WriteLine("OK");
//TestFun(1, 2, 3);
Console.WriteLine("10，测试泛型 OQL --GOQL");
TestGOQL();
Console.WriteLine("OK");
Console.WriteLine("11，测试OQL 批量更新（带条件更新）...");
UpdateTest();
Console.WriteLine("OK");
Console.WriteLine("12，测试批量数据插入性能....");
//InsertTest();
Console.WriteLine("13，OQL 自连接...");
OqlJoinTest();
//
Console.Write("14，根据接口类型，自动创建实体类测试...");
TestDynamicEntity();
Console.WriteLine("OK");
//
Console.WriteLine("15，Sql 格式化查询测试( SOD 微型ORM功能)...");
AdoHelper dbLocal = new SqlServer();
dbLocal.ConnectionString = MyDB.Instance.ConnectionString;
//DataSet ds = dbLocal.ExecuteDataSet("SELECT * FROM Table_User WHERE UID={0} AND Height>={1:5.2}", 1, 1.80M);
/*
* 下面的写法过时
* var dataList = dbLocal.GetList(reader =>
* {
* return new
* {
* UID=reader.GetInt32(0),
* Name=reader.GetString(1)
* };
* }, "SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={0:5.2}",1, 1.60);
*/
var dataList = dbLocal.ExecuteMapper("SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={1:5.2}", 1, 1.60)
.MapToList(reader => new
{
UID = reader.GetInt32(0),
Name = reader.GetString(1)
});
Console.WriteLine("OK");
//
Console.Write("16，测试属性拷贝...");
V_UserModels vum = new V_UserModels();
vum.BIGTEAM_ID = 123;//可空属性，如果目标对象不是的话，无法拷贝
vum.REGION_ID = 456;
vum.SMALLTEAM_ID = 789;
UserModels um = vum.CopyTo <UserModels>();
Console.WriteLine("OK");
//
Console.Write("17，测试【自定义查询】的实体类...");
UserPropertyView up = new UserPropertyView();
OQL q11 = new OQL(up);
OQLOrder order = new OQLOrder(q11);
q11.Select()
.Where(q11.Condition.AND(up.PropertyName, "=", "总成绩").AND(up.PropertyValue, ">", 1000))
.OrderBy(order.Asc(up.UID));
AdoHelper db11 = MyDB.GetDBHelperByConnectionName("local");
var result = EntityQuery <UserPropertyView> .QueryList(q11, db11);
//下面2行不是必须
q11.Dispose();
Console.WriteLine("OK");
//EntityContainer ec = new EntityContainer(q11);
//var ecResult = ec.MapToList(() => {
// return new { AAA = ec.GetItemValue<int>(0), BBB = ec.GetItemValue<string>(1) };
//});
/////////////////////////////////////////////////////
Console.WriteLine("18，测试实体类【自动保存】数据...");
TestAutoSave();
Console.WriteLine("19，测试OQL上使用聚合函数...");
OQLAvgTest();
/////////////////测试事务////////////////////////////////////
Console.WriteLine("20，测试测试事务...");
TestTransaction();
TestTransaction2();
Console.WriteLine("事务测试完成！");
Console.WriteLine("-------PDF.NET SOD 测试全部完成-------");
Console.ReadLine();
}
public int Insert <T>(T entity)
{
if (entity == null)
{
return(0);
}
object val = 0;
IDbTransaction transaction = null;
IDbConnection connection = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
connection = GetConnection();
transaction = GetTransaction();
Type classType = entity.GetType();
//从实体对象的属性配置上获取对应的表信息
PropertyInfo[] properties = ReflectionHelper.GetProperties(classType);
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.INSERT, properties);
//获取SQL语句
String strSql = EntityHelper.GetInsertSql(tableInfo);
//获取参数
IDbDataParameter[] parms = tableInfo.GetParameters();
//执行Insert命令
val = AdoHelper.ExecuteScalar(connection, transaction, CommandType.Text, strSql, parms);
//Access数据库执行不需要命名参数
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
//如果是Access数据库，另外执行获取自动生成的ID
String autoSql = EntityHelper.GetAutoSql();
val = AdoHelper.ExecuteScalar(connection, transaction, CommandType.Text, autoSql);
}
//把自动生成的主键ID赋值给返回的对象
if (!tableInfo.NoAutomaticKey)
{
if (AdoHelper.DbType == DatabaseType.SQLSERVER || AdoHelper.DbType == DatabaseType.MYSQL || AdoHelper.DbType == DatabaseType.ACCESS || AdoHelper.DbType == DatabaseType.SQLITE)
{
PropertyInfo propertyInfo = EntityHelper.GetPrimaryKeyPropertyInfo(entity, properties);
ReflectionHelper.SetPropertyValue(entity, propertyInfo, val);
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (m_Transaction == null)
{
connection.Close();
}
}
return(Convert.ToInt32(val));
}
/// <summary>
/// Retrieve the WorkStudy Details
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
private DataSearch <RNDWorkStudy> GetWorkStudies(DataGridoption option)
{
AdoHelper ado = new AdoHelper();
//SqlDataReader reader = null;
List <RNDWorkStudy> lstWorkStudy = new List <RNDWorkStudy>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
lstSqlParameter.Add(new SqlParameter("@CurrentPage", option.pageIndex));
lstSqlParameter.Add(new SqlParameter("@NoOfRecords", option.pageSize));
AddSearchFilter(option, lstSqlParameter);
try
{
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDWorkStudy_Read", "RND", lstSqlParameter.Cast <object>().ToArray()))
{
if (reader.HasRows)
{
RNDWorkStudy WS = null;
while (reader.Read())
{
WS = new RNDWorkStudy();
WS.total = Convert.ToInt32(reader["total"]);
WS.RecId = Convert.ToInt32(reader["RecId"]);
WS.WorkStudyID = Convert.ToString(reader["WorkStudyID"]);
WS.StudyType = Convert.ToString(reader["StudyType"]);
WS.StudyTypeDesc = Convert.ToString(reader["StudyTypeDesc"]);
WS.StudyTitle = Convert.ToString(reader["StudyDesc"]);
WS.StudyDesc = Convert.ToString(reader["StudyDesc"]);
WS.PlanOSCost = Convert.ToDecimal(reader["PlanOSCost"]);
WS.AcctOSCost = Convert.ToDecimal(reader["AcctOSCost"]);
WS.StudyStatus = Convert.ToString(reader["StudyStatus"]);
WS.StudyStatusDesc = Convert.ToString(reader["StudyStatusDesc"]);
WS.StartDate = Convert.ToString(reader["StartDate"]);
WS.DueDate = Convert.ToString(reader["DueDate"]);
// WS.DueDate = (!string.IsNullOrEmpty(reader["DueDate"].ToString())) ? Convert.ToDateTime(reader["DueDate"]) : (DateTime?)null;
WS.CompleteDate = Convert.ToString(reader["CompleteDate"]);
WS.Plant = Convert.ToString(reader["Plant"]);
WS.PlantDesc = Convert.ToString(reader["PlantDesc"]);
WS.TempID = Convert.ToString(reader["TempID"]);
WS.EntryBy = Convert.ToString(reader["EntryBy"]);
WS.EntryDate = (!string.IsNullOrEmpty(reader["EntryDate"].ToString())) ? Convert.ToDateTime(reader["EntryDate"]) : (DateTime?)null;
WS.Experimentation = Convert.ToString(reader["Experimentation"]);
WS.FinalSummary = Convert.ToString(reader["FinalSummary"]);
WS.Uncertainty = Convert.ToString(reader["Uncertainty"]);
lstWorkStudy.Add(WS);
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
DataSearch <RNDWorkStudy> ds = new DataSearch <RNDWorkStudy>
{
items = lstWorkStudy,
total = (lstWorkStudy != null && lstWorkStudy.Count > 0) ? lstWorkStudy[0].total : 0
};
return(ds);
}
public DbDataReader GetUsersInRole(
int siteId,
int roleId,
string searchInput,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("u.* ");
//sqlCommand.Append("u.UserID as UserID, ");
//sqlCommand.Append("u.Name As Name, ");
//sqlCommand.Append("u.Email As Email, ");
//sqlCommand.Append("u.LoginName ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = :RoleID ");
sqlCommand.Append("WHERE u.SiteID = :SiteID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (u.Name LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LoginName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.Email LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LastName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.FirstName LIKE :SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append("ORDER BY u.Name ");
sqlCommand.Append("LIMIT :PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET :OffsetRows ");
}
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[5];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":RoleID", DbType.Int32);
arParams[1].Value = roleId;
arParams[2] = new SqliteParameter(":SearchInput", DbType.String);
arParams[2].Value = "%" + searchInput + "%";
arParams[3] = new SqliteParameter(":PageSize", DbType.Int32);
arParams[3].Value = pageSize;
arParams[4] = new SqliteParameter(":OffsetRows", DbType.Int32);
arParams[4].Value = pageLowerBound;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
private DataSearch <RNDTesting> GetTestingMaterial(DataGridoption option)
{
_logger.Debug("GetTestingMaterial");
AdoHelper ado = new AdoHelper();
//SqlDataReader reader = null;
List <RNDTesting> lstTestingMaterial = new List <RNDTesting>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
lstSqlParameter.Add(new SqlParameter("@CurrentPage", option.pageIndex));
lstSqlParameter.Add(new SqlParameter("@NoOfRecords", option.pageSize));
AddSearchFilter(option, lstSqlParameter);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDTestingMaterial_Read", "RND", lstSqlParameter.Cast <object>().ToArray()))
{
if (reader.HasRows)
{
RNDTesting TM = null;
while (reader.Read())
{
TM = new RNDTesting();
TM.total = Convert.ToInt32(reader["total"]);
TM.TestingNo = Convert.ToInt32(reader["TestingNo"]);
TM.WorkStudyID = Convert.ToString(reader["WorkStudyID"]);
TM.LotID = Convert.ToString(reader["LotID"]);
TM.MillLotNo = Convert.ToInt32(reader["MillLotNo"]);
TM.SoNum = Convert.ToString(reader["SoNum"]);
TM.Hole = Convert.ToString(reader["Hole"]);
TM.PieceNo = Convert.ToString(reader["PieceNo"]);
TM.Alloy = Convert.ToString(reader["Alloy"]);
TM.Temper = Convert.ToString(reader["Temper"]);
TM.CustPart = Convert.ToString(reader["CustPart"]);
TM.UACPart = Convert.ToInt32(reader["UACPart"]);
TM.GageThickness = Convert.ToString(reader["GageThickness"]);
TM.Orientation = Convert.ToString(reader["Orientation"]);
TM.Location1 = Convert.ToString(reader["Location1"]);
TM.Location2 = Convert.ToString(reader["Location2"]);
TM.Location3 = Convert.ToString(reader["Location3"]);
TM.SpeciComment = Convert.ToString(reader["SpeciComment"]);
TM.TestType = Convert.ToString(reader["TestType"]);
TM.SubTestType = Convert.ToString(reader["SubTestType"]);
TM.Status = Convert.ToChar(reader["Status"]);
TM.Selected = Convert.ToChar(reader["Selected"]);
TM.EntryDate = (!string.IsNullOrEmpty(reader["EntryDate"].ToString())) ? Convert.ToDateTime(reader["EntryDate"]) : (DateTime?)null;
TM.EntryBy = Convert.ToString(reader["EntryBy"]);
TM.TestLab = Convert.ToString(reader["TestLab"]);
TM.Printed = Convert.ToChar(reader["Printed"]);
TM.Replica = Convert.ToString(reader["Replica"]);
// TM.RCS = (!string.IsNullOrEmpty(reader["RCS"].ToString())) ? Convert.ToChar(reader["RCS"]) : (char?)null;
lstTestingMaterial.Add(TM);
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
DataSearch <RNDTesting> ds = new DataSearch <RNDTesting>
{
items = lstTestingMaterial,
total = (lstTestingMaterial != null && lstTestingMaterial.Count > 0) ? lstTestingMaterial[0].total : 0
};
return(ds);
}
public DbDataReader GetPage(
int siteId,
string searchInput,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("r.RoleID AS RoleID, ");
sqlCommand.Append("r.SiteID AS SiteID, ");
sqlCommand.Append("r.RoleName AS RoleName, ");
sqlCommand.Append("r.DisplayName AS DisplayName, ");
sqlCommand.Append("r.SiteGuid AS SiteGuid, ");
sqlCommand.Append("r.RoleGuid AS RoleGuid, ");
sqlCommand.Append("COUNT(ur.UserID) As MemberCount ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON ur.RoleID = r.RoleID ");
sqlCommand.Append("WHERE r.SiteID = :SiteID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (DisplayName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (RoleName LIKE :SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("r.RoleID, ");
sqlCommand.Append("r.SiteID, ");
sqlCommand.Append("r.RoleName, ");
sqlCommand.Append("r.DisplayName, ");
sqlCommand.Append("r.SiteGuid, ");
sqlCommand.Append("r.RoleGuid ");
sqlCommand.Append("ORDER BY r.DisplayName ");
sqlCommand.Append("LIMIT :PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET :OffsetRows ");
}
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[4];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":SearchInput", DbType.String);
arParams[1].Value = "%" + searchInput + "%";
arParams[2] = new SqliteParameter(":PageSize", DbType.Int32);
arParams[2].Value = pageSize;
arParams[3] = new SqliteParameter(":OffsetRows", DbType.Int32);
arParams[3].Value = pageLowerBound;
return(AdoHelper.ExecuteReader(
connectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// 提交
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
AdoHelper adoHelper = AdoHelper.CreateHelper("DB_Instance");
string id = lbCouponId.Text.Trim();
string coupId = id;
string couponType = ddlCouponType.SelectedValue;
decimal value = 0;
decimal.TryParse(txtValue.Text, out value);
string context = txtContext.Text;
DateTime startTime = DateTime.Now;
DateTime.TryParse(hfStart.Value, out startTime);
DateTime endTime = DateTime.Now;
DateTime.TryParse(hfEnd.Value, out endTime);
int couponDay = int.Parse(this.txtDay.Text.Trim());
string qrCode = QRCodeUrl.ImageUrl;
int isPost = cbQF.Checked ? 1 : 0;
string postObject = ddlMember.SelectedValue;
string getPlaceInfo = this.ddlGetPlaceInfo.SelectedValue;
decimal minPrice = decimal.Parse(this.txtMinPrice.Text.Trim());
DataTable dt = new DataTable();
int index = 1;
if (couponType == "DJ")
{
string strMem = "select memberId,tel from T_Member_Info ";
if (postObject == "new")
{
strMem += " where memberId,tel not in (select memberId from T_Base_Coupon);";
}
DataSet ds = adoHelper.ExecuteSqlDataset(strMem);
if (ds != null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
{
dt = ds.Tables[0];
}
index = ds.Tables[0].Rows.Count;
}
string cid = (lbCouponId.Text).Trim();
int effect = cbEffect.Checked ? 1 : 0;
int use = lbUse.Text == "已使用"?1:0;
string remark = txtRemark.Text;
string couponCode = id;
StringBuilder strSql = new StringBuilder();
if (couponId == "")
{
int success = 0;
int fail = 0;
for (int i = 0; i < index; i++)
{
string memberId = "";
if (dt.Rows.Count > 0)
{
memberId = dt.Rows[i][0].ToString();
}
coupId = id + i.ToString().PadLeft(6, '0');
if (couponType == "CZ")
{
cid = "http://" + System.Web.Configuration.WebConfigurationManager.AppSettings["coupon"].ToString() + "/Coupon.aspx?type=CZ&cid=" + coupId;
QRCode qrc = new QRCode(cid);
qrCode = qrc.CreateQRCode();
QRCodeUrl.ImageUrl = qrCode;
}
strSql.Clear();
strSql.Append("insert into T_Base_Coupon(");
strSql.Append("CouponId,CouponType,CouponValue,Context,StartTime,EndTime,QRCodeUrl,isEffect,isUse,Remark,isPost,postObject,CouponCode,MemberId,CouponDay,GetPlaceInfo,minPrice)");
strSql.Append(" values (");
strSql.Append("@CouponId,@CouponType,@CouponValue,@Context,@StartTime,@EndTime,@QRCodeUrl,@isEffect,@isUse,@Remark,@isPost,@postObject,@CouponCode,@MemberId,@CouponDay,@GetPlaceInfo,@minPrice)");
SqlParameter[] parameters =
{
new SqlParameter("@CouponId", SqlDbType.VarChar),
new SqlParameter("@CouponType", SqlDbType.VarChar),
new SqlParameter("@CouponValue", SqlDbType.Decimal),
new SqlParameter("@Context", SqlDbType.VarChar),
new SqlParameter("@StartTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@QRCodeUrl", SqlDbType.VarChar),
new SqlParameter("@isEffect", SqlDbType.Int),
new SqlParameter("@isUse", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.VarChar),
new SqlParameter("@isPost", SqlDbType.Int),
new SqlParameter("@postObject", SqlDbType.VarChar),
new SqlParameter("@CouponCode", SqlDbType.VarChar),
new SqlParameter("@MemberId", SqlDbType.VarChar),
new SqlParameter("@CouponDay", SqlDbType.Int),
new SqlParameter("@GetPlaceInfo", SqlDbType.VarChar),
new SqlParameter("@minPrice", SqlDbType.Decimal)
};
parameters[0].Value = coupId;;
parameters[1].Value = couponType;
parameters[2].Value = value;
parameters[3].Value = context;
parameters[4].Value = startTime;
parameters[5].Value = endTime;
parameters[6].Value = qrCode;
parameters[7].Value = effect;
parameters[8].Value = use;
parameters[9].Value = remark;
parameters[10].Value = isPost;
parameters[11].Value = postObject;
parameters[12].Value = couponCode;
parameters[13].Value = memberId;
parameters[14].Value = couponDay;
parameters[15].Value = getPlaceInfo;
parameters[16].Value = minPrice;
int rows = adoHelper.ExecuteSqlNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
if (isPost == 1)
{
new MobileInfo().GetMess(dt.Rows[i][1].ToString(), context);
}
success++;
//this.ClientScript.RegisterStartupScript(this.GetType(), "close", "<script>alert('添加成功');layer_close_refresh();</script>");
couponId = id;
}
else
{
fail++;
}
}
//LogAdd.CreateLog(Session["UserId"].ToString(), "添加优惠卷“" + context + "共" + success + "张”", "添加", "", "", Request.Url.ToString());
this.ClientScript.RegisterStartupScript(this.GetType(), "close", "<script>alert('成功添加优惠卷设置');layer_close_refresh();</script>");
}
else
{
//if (couponType == "CZ")
// cid = "http://" + System.Web.Configuration.WebConfigurationManager.AppSettings["coupon"].ToString() + "/Coupon.aspx?type=DJ&cid=" + cid;
//QRCode qrc = new QRCode(cid);
//qrCode = qrc.CreateQRCode();
//QRCodeUrl.ImageUrl = qrCode;
//StringBuilder strSql = new StringBuilder();
strSql.Append("update T_Base_Coupon set ");
strSql.Append("CouponType=@CouponType,CouponValue=@CouponValue,Context=@Context,StartTime=@StartTime,EndTime=@EndTime,QRCodeUrl=@QRCodeUrl,isEffect=@isEffect,isUse=@isUse,Remark=@Remark,isPost=@isPost,postObject=@postObject,CouponDay=@CouponDay,GetPlaceInfo=@GetPlaceInfo,minPrice=@minPrice ");
strSql.Append(" where CouponId=@CouponId");
strSql.Append("");
SqlParameter[] parameters =
{
//new SqlParameter("@CouponId", SqlDbType.VarChar,50),
new SqlParameter("@CouponType", SqlDbType.VarChar, 50),
new SqlParameter("@CouponValue", SqlDbType.Int, 4),
new SqlParameter("@Context", SqlDbType.VarChar, 50),
new SqlParameter("@StartTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@QRCodeUrl", SqlDbType.VarChar),
new SqlParameter("@isEffect", SqlDbType.Int),
new SqlParameter("@isUse", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.VarChar),
new SqlParameter("@isPost", SqlDbType.Int),
new SqlParameter("@postObject", SqlDbType.VarChar),
new SqlParameter("@CouponDay", SqlDbType.Int),
new SqlParameter("@CouponId", SqlDbType.VarChar),
new SqlParameter("@GetPlaceInfo", SqlDbType.VarChar),
new SqlParameter("@minPrice", SqlDbType.Decimal)
};
parameters[0].Value = couponType;
parameters[1].Value = value;
parameters[2].Value = context;
parameters[3].Value = startTime;
parameters[4].Value = endTime;
parameters[5].Value = qrCode;
parameters[6].Value = effect;
parameters[7].Value = use;
parameters[8].Value = remark;
parameters[9].Value = isPost;
parameters[10].Value = postObject;
parameters[11].Value = couponDay;
parameters[12].Value = id;
parameters[12].Value = getPlaceInfo;
parameters[13].Value = minPrice;
int rows = adoHelper.ExecuteSqlNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
//LogAdd.CreateLog(Session["UserId"].ToString(), "修改优惠卷“" + context + "”", "修改", "", "", Request.Url.ToString());
this.ClientScript.RegisterStartupScript(this.GetType(), "close", "<script>alert('修改成功');layer_close_refresh();</script>");
GetData();
}
}
}
private List <MediaType> GetAllItems(bool force = false)
{
return(AdoHelper.ExecCachedListProc <MediaType>("sp_cms_media_types_select", force));
}
public async Task <DbDataReader> GetUsersInRole(
int siteId,
int roleId,
string searchInput,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("u.* ");
sqlCommand.Append("FROM mp_users u ");
sqlCommand.Append("JOIN mp_userroles ur ");
sqlCommand.Append("ON u.userid = ur.userid ");
sqlCommand.Append("AND ur.roleid = :roleid ");
sqlCommand.Append("WHERE u.siteid = :siteid ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (u.name LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.loginname LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.email LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.lastname LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.firstname LIKE :searchinput) ");
sqlCommand.Append(")");
}
sqlCommand.Append("ORDER BY u.name ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Value = roleId;
arParams[2] = new NpgsqlParameter("searchinput", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[2].Value = "%" + searchInput + "%";
arParams[3] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Value = pageSize;
arParams[4] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[4].Value = pageLowerBound;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
private List <MediaItem> GetAllItems(bool force = false)
{
return(AdoHelper.ExecCachedListProc <MediaItem>("sp_cms_media_items_select", force, true, OverwriteResult));
}
/// <summary>
/// Inserts a row in the mp_SystemLog table. Returns new integer id.
/// </summary>
/// <param name="logDate"> logDate </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="culture"> culture </param>
/// <param name="url"> url </param>
/// <param name="shortUrl"> shortUrl </param>
/// <param name="thread"> thread </param>
/// <param name="logLevel"> logLevel </param>
/// <param name="logger"> logger </param>
/// <param name="message"> message </param>
/// <returns>int</returns>
public int Create(
DateTime logDate,
string ipAddress,
string culture,
string url,
string shortUrl,
string thread,
string logLevel,
string logger,
string message)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SystemLog (");
sqlCommand.Append("LogDate, ");
sqlCommand.Append("IpAddress, ");
sqlCommand.Append("Culture, ");
sqlCommand.Append("Url, ");
sqlCommand.Append("ShortUrl, ");
sqlCommand.Append("Thread, ");
sqlCommand.Append("LogLevel, ");
sqlCommand.Append("Logger, ");
sqlCommand.Append("Message )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?LogDate, ");
sqlCommand.Append("?IpAddress, ");
sqlCommand.Append("?Culture, ");
sqlCommand.Append("?Url, ");
sqlCommand.Append("?ShortUrl, ");
sqlCommand.Append("?Thread, ");
sqlCommand.Append("?LogLevel, ");
sqlCommand.Append("?Logger, ");
sqlCommand.Append("?Message )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[9];
arParams[0] = new MySqlParameter("?LogDate", MySqlDbType.DateTime);
arParams[0].Value = logDate;
arParams[1] = new MySqlParameter("?IpAddress", MySqlDbType.VarChar, 50);
arParams[1].Value = ipAddress;
arParams[2] = new MySqlParameter("?Culture", MySqlDbType.VarChar, 10);
arParams[2].Value = culture;
arParams[3] = new MySqlParameter("?Url", MySqlDbType.Text);
arParams[3].Value = url;
arParams[4] = new MySqlParameter("?ShortUrl", MySqlDbType.VarChar, 255);
arParams[4].Value = shortUrl;
arParams[5] = new MySqlParameter("?Thread", MySqlDbType.VarChar, 255);
arParams[5].Value = thread;
arParams[6] = new MySqlParameter("?LogLevel", MySqlDbType.VarChar, 20);
arParams[6].Value = logLevel;
arParams[7] = new MySqlParameter("?Logger", MySqlDbType.VarChar, 255);
arParams[7].Value = logger;
arParams[8] = new MySqlParameter("?Message", MySqlDbType.Text);
arParams[8].Value = message;
int newID = Convert.ToInt32(AdoHelper.ExecuteScalar(
writeConnectionString,
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public async Task <DbDataReader> GetPage(
int siteId,
string searchInput,
int pageNumber,
int pageSize)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
NpgsqlParameter[] arParams = new NpgsqlParameter[4];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("searchinput", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Value = "%" + searchInput + "%";
arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Value = pageSize;
arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("r.roleid, ");
sqlCommand.Append("r.siteid, ");
sqlCommand.Append("r.rolename, ");
sqlCommand.Append("r.displayname, ");
sqlCommand.Append("r.siteguid, ");
sqlCommand.Append("r.roleguid, ");
sqlCommand.Append("COUNT(ur.userid) As membercount ");
sqlCommand.Append("FROM mp_roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_userroles ur ");
sqlCommand.Append("ON ur.roleid = r.roleid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("r.siteid = :siteid ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (displayname LIKE :searchinput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (rolename LIKE :searchinput) ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("r.roleid, ");
sqlCommand.Append("r.siteid, ");
sqlCommand.Append("r.rolename, ");
sqlCommand.Append("r.displayname, ");
sqlCommand.Append("r.siteguid, ");
sqlCommand.Append("r.roleguid ");
sqlCommand.Append("ORDER BY r.displayname ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
