static void Main(string[] args)
{
IDatabaseFactory factory = new DatabaseFactory()
.Connection("TestDb")
.Table<Category>()
.Key(x => x.CategoryId, DatabaseGeneratedOption.Identity)
.Configure()
.Table<Product>()
.Configure();
IDatabase data = factory.CreateDatabase();
Category category1 = new Category { Name = "Category 1" };
Category category2 = new Category { Name = "Category 2" };
Product product1 = new Product { ProductId = "PROD123", Name = "Product 123", Description = "The first Product", Price = 19.99M };
Product product2 = new Product { ProductId = "PROD234", Name = "Product 234", Description = "The second Product", Price = 24.99M };
Product product3 = new Product { ProductId = "PROD345", Name = "Product 345", Description = "The third Product", Price = 29.99M };
data.BeginTransaction();
try
{
data.Insert(category1);
data.Insert(category2);
product1.CategoryId = category1.CategoryId;
product2.CategoryId = category2.CategoryId;
product3.CategoryId = category2.CategoryId;
data.Insert(product1);
data.Insert(product2);
data.Insert(product3);
data.CommitTransaction();
Console.WriteLine("OK");
}
catch(Exception ex)
{
data.RollbackTransaction();
Console.WriteLine(ex.Message);
}
IList<Product> products = data.Query<Product>("SELECT ProductId, CategoryId, Name, Description, Price FROM Product WHERE CategoryId = @CategoryId")
.Parameter("@CategoryId", category2.CategoryId)
.Execute();
foreach (Product product in products)
{
Console.WriteLine(product.ProductId);
}
Console.WriteLine();
Product prodX = data.Select<Product>("PROD123");
Console.WriteLine("{0}\t{1}", prodX.ProductId, prodX.Name);
Console.ReadKey();
}
/// <summary>
/// 同时根据用户ID和用户昵称得到一个对象实体
/// </summary>
public bool GetModel(long lUid, string strScreenName)
{
Database db = DatabaseFactory.CreateDatabase();
string strSql = "select top 1 * FROM users where user_id=" + lUid.ToString() + " and screen_name='" + strScreenName + "'";
DataRow dr = db.GetDataRow(strSql);
if (dr != null)
{
_user_id = Convert.ToInt64(dr["user_id"]);
_screen_name = dr["screen_name"].ToString();
_name = dr["name"].ToString();
_province = dr["province"].ToString();
_city = dr["city"].ToString();
_location = dr["location"].ToString();
_description = dr["description"].ToString();
_url = dr["url"].ToString();
_profile_image_url = dr["profile_image_url"].ToString();
_domain = dr["domain"].ToString();
_gender = dr["gender"].ToString();
if (dr["followers_count"].ToString() != "")
{
_followers_count = Convert.ToInt32(dr["followers_count"]);
}
if (dr["friends_count"].ToString() != "")
{
_friends_count = Convert.ToInt32(dr["friends_count"]);
}
if (dr["statuses_count"].ToString() != "")
{
_statuses_count = Convert.ToInt32(dr["statuses_count"]);
}
if (dr["favourites_count"].ToString() != "")
{
_favourites_count = Convert.ToInt32(dr["favourites_count"]);
}
_created_at = dr["created_at"].ToString();
if (dr["following"].ToString() != "")
{
if (dr["following"].ToString() == "1")
{
_following = true;
}
else
{
_following = false;
}
}
if (dr["verified"].ToString() != "")
{
if (dr["verified"].ToString() == "1")
{
_verified = true;
}
else
{
_verified = false;
}
}
if (dr["allow_all_act_msg"].ToString() != "")
{
if (dr["allow_all_act_msg"].ToString() == "1")
{
_allow_all_act_msg = true;
}
else
{
_allow_all_act_msg = false;
}
}
if (dr["geo_enabled"].ToString() != "")
{
if (dr["geo_enabled"].ToString() == "1")
{
_geo_enabled = true;
}
else
{
_geo_enabled = false;
}
}
_iteration = Convert.ToInt32(dr["iteration"]);
_update_time = dr["update_time"].ToString();
//-------------20120222加--------------
_idstr = dr["idstr"].ToString();
_weihao = dr["weihao"].ToString();
if (dr["bi_followers_count"].ToString() != "")
{
_bi_followers_count = Convert.ToInt32(dr["bi_followers_count"]);
}
if (dr["online_status"].ToString() != "")
{
_online_status = Convert.ToInt32(dr["online_status"]);
}
if (dr["verified_type"].ToString() != "")
{
_verified_type = Convert.ToInt32(dr["verified_type"]);
}
_verified_reason = dr["verified_reason"].ToString();
if (dr["allow_all_comment"].ToString() != "")
{
if (dr["allow_all_comment"].ToString() == "1")
{
_allow_all_comment = true;
}
else
{
_allow_all_comment = false;
}
}
_avatar_large = dr["avatar_large"].ToString();
_lang = dr["lang"].ToString();
//--------------------------------------
//-------------20120317加---------------
_remark = dr["remark"].ToString();
//--------------------------------------
return(true);
}
else
{
return(false);
}
}
public static Entities.Muestreo ObtenerMuestreo(string id)
{
try
{
var database = DatabaseFactory.CreateDatabase();
var cmd = database.GetStoredProcCommand("GetMuestreoById");
database.AddInParameter(cmd, "id", DbType.Decimal, id);
var muestreo = new Entities.Muestreo();
using (var dataReader = database.ExecuteReader(cmd))
{
if (dataReader.Read())
{
if (!(dataReader["Id_Linea"] is DBNull) && (dataReader["Id_Linea"] != null))
{
muestreo.IdLinea = decimal.Parse(dataReader["Id_Linea"].ToString());
}
if (!(dataReader["Id_Ubicacion_Cuelga"] is DBNull) && (dataReader["Id_Ubicacion_Cuelga"] != null))
{
muestreo.IdUbicacionCuelga = decimal.Parse(dataReader["Id_Ubicacion_Cuelga"].ToString());
}
if (!(dataReader["Fecha_Muestreo"] is DBNull) && (dataReader["Fecha_Muestreo"] != null))
{
muestreo.FechaMuestreo = dataReader["Fecha_Muestreo"].ToString();
string[] fecha = muestreo.FechaMuestreo.Split(' ');
muestreo.FechaMuestreo = fecha[0];
}
if (!(dataReader["Hora_Muestreo"] is DBNull) && (dataReader["Hora_Muestreo"] != null))
{
muestreo.HoraMuestreo = dataReader["Hora_Muestreo"].ToString();
string[] valores = muestreo.HoraMuestreo.Split(':');
muestreo.Hora = valores[0];
muestreo.Minutos = valores[1];
}
if (!(dataReader["Temperatura"] is DBNull) && (dataReader["Temperatura"] != null))
{
muestreo.Temperatura = decimal.Parse(dataReader["Temperatura"].ToString());
}
if (!(dataReader["Transparencia"] is DBNull) && (dataReader["Transparencia"] != null))
{
muestreo.Transparencia = dataReader["Transparencia"].ToString();
}
if (!(dataReader["Numero_Individuos_Mt"] is DBNull) && (dataReader["Numero_Individuos_Mt"] != null))
{
muestreo.NumeroIndividuosMt = int.Parse(dataReader["Numero_Individuos_Mt"].ToString());
}
if (!(dataReader["Numero_Individuos_Kg"] is DBNull) && (dataReader["Numero_Individuos_Kg"] != null))
{
muestreo.NumeroIndividuosKg = int.Parse(dataReader["Numero_Individuos_Kg"].ToString());
}
if (!(dataReader["Talla_Promedio"] is DBNull) && (dataReader["Talla_Promedio"] != null))
{
muestreo.TallaPromedio = decimal.Parse(dataReader["Talla_Promedio"].ToString());
}
if (!(dataReader["Numero_Muertos"] is DBNull) && (dataReader["Numero_Muertos"] != null))
{
muestreo.NumeroMuertos = int.Parse(dataReader["Numero_Muertos"].ToString());
}
if (!(dataReader["Factor_Condicion"] is DBNull) && (dataReader["Factor_Condicion"] != null))
{
muestreo.FactorCondicion = dataReader["Factor_Condicion"].ToString();
}
if (!(dataReader["Utm_E_N"] is DBNull) && (dataReader["Utm_E_N"] != null))
{
muestreo.UtmEn = dataReader["Utm_E_N"].ToString();
}
if (!(dataReader["Id_Modulo"] is DBNull) && (dataReader["Id_Modulo"] != null))
{
muestreo.IdModulo = decimal.Parse(dataReader["Id_Modulo"].ToString());
}
}
}
return(muestreo);
}
catch (Exception exception)
{
//insertar log de error
Log.InsLog("Muestreo", "ObtenerMuestreo", exception.Message);
return(null);
}
}
//=================================================交易员==========================================
#region 交易员分页查询
/// <summary>
/// 分页查询用户
/// </summary>
/// <param name="userInfo">查询条件 用户实体</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="rowCount">总页数</param>
/// <returns></returns>
public DataSet GetPagingUser(Model.UM_UserInfo userInfo, int pageNo, int pageSize, out int rowCount)
{
// string SQL_SELECT_CUSTOMER =
//@"select UserName,LoginName,Password,UserID,CertificateStyle,Postalcode,RoleID,CertificateNo,Telephone,Address,Email,QuestionID,Answer,CouterID,Remark,AddType,AddTime FROM UM_UserInfo where 1=1 ";
string SQL_SELECT_CUSTOMER =
@"select a.*,b.name from UM_UserInfo a,CT_Counter b where b.CouterID=a.CouterID ";
if (userInfo.LoginName != null && !string.IsNullOrEmpty(userInfo.LoginName))
{
SQL_SELECT_CUSTOMER += "AND LoginName LIKE '%' + @LoginName + '%' ";
}
if (userInfo.UserName != null && !string.IsNullOrEmpty(userInfo.UserName))
{
SQL_SELECT_CUSTOMER += "AND UserName LIKE '%' + @UserName + '%' ";
}
//if (userInfo.CouterID != int.MaxValue)
//{
// SQL_SELECT_CUSTOMER += "AND CouterID=@CouterID ";
//}
if (userInfo.UserID != int.MaxValue)
{
SQL_SELECT_CUSTOMER += "AND UserID=@UserID ";
}
if (userInfo.RoleID != int.MaxValue)
{
SQL_SELECT_CUSTOMER += "AND RoleID=@RoleID ";
}
if (userInfo.Name != null && !string.IsNullOrEmpty(userInfo.Name)) //柜台名称
{
SQL_SELECT_CUSTOMER += "AND name LIKE '%' + @name + '%' ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_CUSTOMER);
if (userInfo.LoginName != null && !string.IsNullOrEmpty(userInfo.LoginName))
{
database.AddInParameter(dbCommand, "LoginName", DbType.String, userInfo.LoginName);
}
if (userInfo.UserName != null && !string.IsNullOrEmpty(userInfo.UserName))
{
database.AddInParameter(dbCommand, "UserName", DbType.String, userInfo.UserName);
}
//if (userInfo.CouterID != int.MaxValue)
//{
// database.AddInParameter(dbCommand, "CouterID", DbType.Int32, userInfo.CouterID);
//}
if (userInfo.UserID != int.MaxValue)
{
database.AddInParameter(dbCommand, "UserID", DbType.Int32, userInfo.UserID);
}
if (userInfo.RoleID != int.MaxValue)
{
database.AddInParameter(dbCommand, "RoleID", DbType.Int32, userInfo.RoleID);
}
if (userInfo.Name != null && !string.IsNullOrEmpty(userInfo.Name)) //柜台名称
{
database.AddInParameter(dbCommand, "name", DbType.String, userInfo.Name);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_CUSTOMER, pageNo, pageSize, out rowCount, "TSM_CUSTOMER"));
}
public ReconciliationOrdersDao()
{
this.database = DatabaseFactory.CreateDatabase();
}
public Biz_Base(string dataBase)
{
this.m_db = DatabaseFactory.CreateDatabase(dataBase);
}
public UserSignDao()
{
this.database = DatabaseFactory.CreateDatabase();
}
public SEDataBase(string name)
{
_database = DatabaseFactory.CreateDatabase(name);
}
public static Database GetDatabase(string sName)
{
return(DatabaseFactory.CreateDatabase(sName));
}
//IP ED
// toolkit
public static Database GetDatabase()
{
return(DatabaseFactory.CreateDatabase());
}
public GameDao()
{
this._database = DatabaseFactory.CreateDatabase();
}
/// <summary>
/// Commit
/// </summary>
/// <returns>TransactionResult - Success / Failure</returns>
public TransactionResult Commit()
{
TransactionResult result = null;
Database db = DatabaseFactory.CreateDatabase(_myConnection.DatabaseName);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
switch (ScreenMode)
{
case ScreenMode.Add:
//Adding Or Editing MachineProcessor
result = AddEditProcessor(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.Edit:
break;
case ScreenMode.Delete:
result = DeleteProcessor(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.View:
break;
}
transaction.Commit();
return(result);
}
catch (Exception ex)
{
transaction.Rollback();
if (ScreenMode == ScreenMode.Add)
{
ErrorLog.LogErrorMessageToDB("", "MachineProcessorDL.cs", "Commit For Add", ex.Message, _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Adding"));
}
if (ScreenMode == ScreenMode.Edit)
{
ErrorLog.LogErrorMessageToDB("", "MachineProcessorDL.cs", "Commit For Edit", ex.Message, _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Updating"));
}
if (ScreenMode == ScreenMode.Delete)
{
ErrorLog.LogErrorMessageToDB("", "MachineProcessorDL.cs", "Commit For Delete", ex.Message, _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Deleting"));
}
}
}
return(null);
}
/// <summary>
/// Constructor
/// </summary>
public RMAHeaderDAL()
{
db = DatabaseFactory.CreateDatabase("EDU");
}
/// <summary>
/// Update
/// </summary>
/// <param name="info">info</param>
public int Update(VmiTransInfo info)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(TT_WMM_VMI_TRANS_UPDATE);
db.AddInParameter(dbCommand, "@ID", DbType.Int64, info.Id);
db.AddInParameter(dbCommand, "@FID", DbType.Guid, info.Fid);
db.AddInParameter(dbCommand, "@OUTPUT_NO", DbType.String, info.OutputNo);
db.AddInParameter(dbCommand, "@PLANT", DbType.String, info.Plant);
db.AddInParameter(dbCommand, "@SUPPLIER_NUM", DbType.String, info.SupplierNum);
db.AddInParameter(dbCommand, "@WM_NO", DbType.String, info.WmNo);
db.AddInParameter(dbCommand, "@ZONE_NO", DbType.String, info.ZoneNo);
db.AddInParameter(dbCommand, "@T_WM_NO", DbType.String, info.TWmNo);
db.AddInParameter(dbCommand, "@T_ZONE_NO", DbType.String, info.TZoneNo);
db.AddInParameter(dbCommand, "@T_DOCK", DbType.String, info.TDock);
db.AddInParameter(dbCommand, "@PART_BOX_CODE", DbType.String, info.PartBoxCode);
db.AddInParameter(dbCommand, "@SEND_TIME", DbType.DateTime, info.SendTime);
db.AddInParameter(dbCommand, "@TRANS_TYPE", DbType.Int32, info.TransType);
db.AddInParameter(dbCommand, "@TRAN_TIME", DbType.DateTime, info.TranTime);
db.AddInParameter(dbCommand, "@OUTPUT_REASON", DbType.String, info.OutputReason);
db.AddInParameter(dbCommand, "@BOOK_KEEPER", DbType.String, info.BookKeeper);
db.AddInParameter(dbCommand, "@CONFIRM_FLAG", DbType.Int32, info.ConfirmFlag);
db.AddInParameter(dbCommand, "@PLAN_NO", DbType.String, info.PlanNo);
db.AddInParameter(dbCommand, "@ASN_NO", DbType.String, info.AsnNo);
db.AddInParameter(dbCommand, "@RUNSHEET_NO", DbType.String, info.RunsheetNo);
db.AddInParameter(dbCommand, "@ASSEMBLY_LINE", DbType.String, info.AssemblyLine);
db.AddInParameter(dbCommand, "@PLANT_ZONE", DbType.String, info.PlantZone);
db.AddInParameter(dbCommand, "@WORKSHOP", DbType.String, info.Workshop);
db.AddInParameter(dbCommand, "@TRANS_SUPPLIER_NUM", DbType.String, info.TransSupplierNum);
db.AddInParameter(dbCommand, "@PART_TYPE", DbType.Int32, info.PartType);
db.AddInParameter(dbCommand, "@SUPPLIER_TYPE", DbType.Int32, info.SupplierType);
db.AddInParameter(dbCommand, "@RUNSHEET_CODE", DbType.String, info.RunsheetCode);
db.AddInParameter(dbCommand, "@ERP_FLAG", DbType.Int32, info.ErpFlag);
db.AddInParameter(dbCommand, "@LOGICAL_PK", DbType.String, info.LogicalPk);
db.AddInParameter(dbCommand, "@BUSINESS_PK", DbType.String, info.BusinessPk);
db.AddInParameter(dbCommand, "@ROUTE", DbType.String, info.Route);
db.AddInParameter(dbCommand, "@REQUEST_TIME", DbType.DateTime, info.RequestTime);
db.AddInParameter(dbCommand, "@CUST_CODE", DbType.String, info.CustCode);
db.AddInParameter(dbCommand, "@CUST_NAME", DbType.String, info.CustName);
db.AddInParameter(dbCommand, "@COST_CENTER", DbType.String, info.CostCenter);
db.AddInParameter(dbCommand, "@ORGANIZATION_FID", DbType.Guid, info.OrganizationFid);
db.AddInParameter(dbCommand, "@CONFIRM_USER", DbType.String, info.ConfirmUser);
db.AddInParameter(dbCommand, "@CONFIRM_DATE", DbType.DateTime, info.ConfirmDate);
db.AddInParameter(dbCommand, "@LIABLE_USER", DbType.String, info.LiableUser);
db.AddInParameter(dbCommand, "@LIABLE_DATE", DbType.DateTime, info.LiableDate);
db.AddInParameter(dbCommand, "@FINANCE_USER", DbType.String, info.FinanceUser);
db.AddInParameter(dbCommand, "@FINANCE_DATE", DbType.DateTime, info.FinanceDate);
db.AddInParameter(dbCommand, "@SUM_PART_QTY", DbType.Decimal, info.SumPartQty);
db.AddInParameter(dbCommand, "@SUM_OF_PRICE", DbType.Decimal, info.SumOfPrice);
db.AddInParameter(dbCommand, "@STATUS", DbType.Int32, info.Status);
db.AddInParameter(dbCommand, "@CONVEYANCE", DbType.String, info.Conveyance);
db.AddInParameter(dbCommand, "@CARRIER_TEL", DbType.String, info.CarrierTel);
db.AddInParameter(dbCommand, "@SUM_WEIGHT", DbType.Decimal, info.SumWeight);
db.AddInParameter(dbCommand, "@SUM_VOLUME", DbType.Decimal, info.SumVolume);
db.AddInParameter(dbCommand, "@PLAN_SHIPPING_TIME", DbType.DateTime, info.PlanShippingTime);
db.AddInParameter(dbCommand, "@PLAN_DELIVERY_TIME", DbType.DateTime, info.PlanDeliveryTime);
db.AddInParameter(dbCommand, "@PRINT_COUNT", DbType.Int32, info.PrintCount);
db.AddInParameter(dbCommand, "@PRINT_TIME", DbType.DateTime, info.PrintTime);
db.AddInParameter(dbCommand, "@COMMENTS", DbType.String, info.Comments);
db.AddInParameter(dbCommand, "@VALID_FLAG", DbType.Boolean, info.ValidFlag);
db.AddInParameter(dbCommand, "@CREATE_USER", DbType.String, info.CreateUser);
db.AddInParameter(dbCommand, "@CREATE_DATE", DbType.DateTime, info.CreateDate);
db.AddInParameter(dbCommand, "@MODIFY_USER", DbType.String, info.ModifyUser);
db.AddInParameter(dbCommand, "@MODIFY_DATE", DbType.DateTime, info.ModifyDate);
db.AddInParameter(dbCommand, "@LAST_PRINT_USER", DbType.String, info.LastPrintUser);
db.AddInParameter(dbCommand, "@SUM_PACKAGE_QTY", DbType.Int32, info.SumPackageQty);
db.AddInParameter(dbCommand, "@PULL_MODE", DbType.Int32, info.PullMode);
return(int.Parse("0" + db.ExecuteNonQuery(dbCommand)));
}
protected string GetConnectionString()
{
db = DatabaseFactory.CreateDatabase("MyGym");
return(db.ConnectionString);
}
public SEDataBase()
{
_database = DatabaseFactory.CreateDatabase();
}
public DbContentCommentCollection GetCollection(int topCount, string whereClause, string sortClause)
{
StringBuilder query;
Database database;
DbCommand dbCommand;
IDataReader r;
DbContentCommentCollection dbContentCommentCollection;
query = new StringBuilder("SELECT ");
if (topCount > 0)
{
query.Append("TOP ");
query.Append(topCount);
query.Append(" ");
}
foreach (string columnName in InnerJoinFields)
{
query.Append("DbContentComment.");
query.Append(columnName);
query.Append(",");
}
//
// Remove trailing comma
//
query.Length--;
query.Append(" FROM kitCms_Comments AS DbContentComment");
//
// Render where clause
//
if (whereClause != string.Empty)
{
query.Append(" WHERE ");
query.Append(whereClause);
}
//
// Render sort clause
//
if (sortClause != string.Empty)
{
query.Append(" ORDER BY ");
query.Append(sortClause);
}
//
// Render final semicolon
//
query.Append(";");
database = DatabaseFactory.CreateDatabase();
dbCommand = database.GetSqlStringCommand(query.ToString());
#if DEBUG
try
{
r = database.ExecuteReader(dbCommand);
}
catch (Exception e)
{
string msg = e.Message;
throw(new Exception(msg + " --- Query: " + query.ToString()));
}
#else
r = database.ExecuteReader(dbCommand);
#endif
dbContentCommentCollection = new DbContentCommentCollection();
while (r.Read())
{
DbContentComment dbContentComment = ParseFromReader(r, 0, 1);
dbContentCommentCollection.Add(dbContentComment);
}
return(dbContentCommentCollection);
}
/// <summary>
/// Constructor
/// </summary>
public TruckDAL()
{
db = DatabaseFactory.CreateDatabase("PickC");
}
/// <summary>
/// TO DO page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchProposalInvoice(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"select P.ProjectId,P.Title as ProjectTitle,PT.Title as ProposalTrackerTitle ,PT.PONo, Invoices.Milestone,Invoices.InvoiceNo,
Invoices.Status, PT.ProposalTrackerID,Invoices.ID as invoiceId from ProposalTracker PT
LEFT JOIN Projects P on PT.ProjectID = p.ProjectId
LEFT JOIN Invoices ON Invoices.ProposalId = PT.ProposalTrackerID ");
sqlStr.Append("WHERE 1=1 ");
sqlStr.Append("AND (");
sqlStr.Append("P.CompanyID != (select ComID from Companys where CompanyName='Sunnet')");
sqlStr.Append(")");
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append("Invoices.InvoiceNo LIKE @Keywords ");
sqlStr.Append("OR P.Title LIKE @Keywords ");
sqlStr.Append("OR PT.Title LIKE @Keywords ");
sqlStr.Append(") ");
}
if (request.ProjectId != 0)
{
sqlStr.Append("AND P.ProjectID=@ProjectId ");
}
if (request.OrderExpression != "")
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY P.Title ");
}
if (request.OrderDirection != "")
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <ProposalToDoModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString()));
db.AddInParameter(dbCommand, "ProjectId", DbType.Int32, request.ProjectId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <ProposalToDoModel>();
while (dataReader.Read())
{
list.Add(ProposalToDoModel.ReaderBind(dataReader));
}
response.ProposalList = list;
response.ResultCount = list.Count;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
public Biz_Base()
{
this.m_db = DatabaseFactory.CreateDatabase();
}
public SearchInvoiceResponse SearchTimesheetInvoice(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("SELECT ts.ID,[SheetDate] ");
sqlStr.Append(" ,c.CompanyName,p.Title AS ProjectTitle,ts.[TicketID]");
sqlStr.Append(" ,t.Title AS TicketTitle,u.FirstName,[Hours]");
sqlStr.Append(" FROM [PM].[dbo].[TimeSheets] ts ");
sqlStr.Append(" LEFT JOIN dbo.Tickets t ON ts.TicketID =t.TicketID ");
sqlStr.Append(" LEFT JOIN dbo.Users u ON u.UserID=ts.CreatedBy ");
sqlStr.Append(" LEFT JOIN dbo.Companys c ON c.ComID=t.CompanyID ");
sqlStr.Append(" LEFT JOIN dbo.Projects p ON ts.ProjectID=p.ProjectID ");
sqlStr.Append("WHERE 1=1 and p.ProjectID IN (" + request.ProjectIds + ") " +
" and not exists (select * from TSInvoiceRelation where TSId=ts.id)");
if (request.timeTsheetIDs != "" && request.timeTsheetIDs != null)
{
sqlStr.Append("and ts.ID IN (" + request.timeTsheetIDs + ")");
}
if (request.CompanyId > 0)
{
sqlStr.Append("and c.ComID = @CompanyID ");
}
if (request.OrderExpression != "" && request.OrderExpression != null)
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY CompanyName ");
}
if (request.OrderDirection != "" && request.OrderDirection != null)
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <TimesheetInvoiceModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression);
db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection);
// db.AddInParameter(dbCommand, "ProjectIDs", DbType.String, "("+request.ProjectIds+")");
db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <TimesheetInvoiceModel>();
while (dataReader.Read())
{
list.Add(TimesheetInvoiceModel.ReaderBind(dataReader));
}
response.TimesheetList = list;
response.TimesheetCount = list.Count;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
/// <summary>
/// Constructor
/// </summary>
public AddressDAL()
{
db = DatabaseFactory.CreateDatabase("netStock");
}
/// <summary>
/// All Invoices page,Pass Due page,Awaiting Payment page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchInvoices(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"SELECT i.ID,i.ProposalId,i.InvoiceNo,i.Milestone Milestone,c.CompanyName,i.SendOn,i.DueOn,i.ReceiveOn,i.Status,
(case when i.ProposalId>0 then (select SUM(Hours) from TimeSheets where ticketid in
(select TID from ProposalTrackerRelation where wid=i.ProposalId))
else sum(ts.Hours) end) [Hours],
i.Notes,pt.Title AS ProposalTitle,pt.PONo,p.Title as ProjectTitle
FROM Invoices i
LEFT JOIN dbo.TSInvoiceRelation tsr ON i.ID=tsr.InvoiceId
LEFT JOIN dbo.TimeSheets ts ON tsr.TSId=ts.ID
LEFT JOIN dbo.ProposalTracker pt ON pt.ProposalTrackerID=i.ProposalId
LEFT JOIN dbo.Projects p ON (p.ProjectID=ts.ProjectID or p.ProjectID=pt.ProjectID)
LEFT JOIN dbo.Companys c ON p.CompanyID=c.ComID ");
sqlStr.Append("WHERE 1=1 ");
switch (request.Searchtype)
{
case InvoiceSearchType.ProposalOnly:
sqlStr.Append("AND i.ProposalId !=0 ");
break;
case InvoiceSearchType.AwitingPayment:
sqlStr.Append("AND i.Status =" + (int)InvoiceStatus.Awaiting_Payment + " ");
break;
case InvoiceSearchType.Payment_Received:
sqlStr.Append("AND i.Status =" + (int)InvoiceStatus.Payment_Received + " ");
break;
case InvoiceSearchType.PassDue:
sqlStr.Append("AND i.DueOn < (select getdate()-1 ) AND i.Status <=5 ");
break;
case InvoiceSearchType.All:
break;
}
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append("i.InvoiceNo LIKE @Keywords ");
sqlStr.Append("OR p.Title LIKE @Keywords ");
//sqlStr.Append("OR ProposalTracker.Title LIKE %@Keywords% ");
sqlStr.Append(") ");
}
if (request.CompanyId != 0)
{
sqlStr.Append("AND c.ComID=@CompanyId ");
}
if ((int)request.InvoiceStatus != 0)
{
sqlStr.Append("AND i.Status=@Status ");
}
if (request.ProjectId != 0)
{
sqlStr.Append("AND p.ProjectId=@ProjectId ");
}
sqlStr.Append(" GROUP BY i.ID,i.ProposalId,i.InvoiceNo,i.SendOn,i.DueOn,i.ReceiveOn,i.Status,c.CompanyName,i.Notes,pt.Title,i.Milestone,pt.PONo,p.Title ");
if (request.OrderExpression != "")
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY p.Title ");
}
if (request.OrderDirection != "")
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <ProposalInvoiceModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString()));
db.AddInParameter(dbCommand, "ProjectId", DbType.Int32, request.ProjectId);
db.AddInParameter(dbCommand, "CompanyId", DbType.Int32, request.CompanyId);
db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression);
db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection);
db.AddInParameter(dbCommand, "Status", DbType.Int32, request.InvoiceStatus);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <ProposalInvoiceModel>();
while (dataReader.Read())
{
list.Add(ProposalInvoiceModel.ReaderBind(dataReader));
}
response.ResultList = list;
response.ResultCount = list.Count;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
/// <summary>
/// Acesso a dados do usuário.
/// </summary>
public UsuarioData()
{
this.m_db = DatabaseFactory.CreateDatabase();
}
/// <summary>
/// PO List page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchPOlist(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"SELECT C.CompanyName,PT.PONo,PT.Title,PT.ApprovedOn,i.Milestone,i.InvoiceNo,i.Status
FROM dbo.Companys C,dbo.ProposalTracker PT,dbo.Projects P,dbo.Invoices i
WHERE C.ComID=P.CompanyID AND P.ProjectID=PT.ProjectID AND pt.ProposalTrackerID=i.ProposalId AND pt.ApprovedOn IS NOT NULL ");
switch (request.Searchtype)
{
case InvoiceSearchType.ProposalOnly:
sqlStr.Append("AND i.ProposalId !=0 ");
break;
case InvoiceSearchType.AwitingPayment:
sqlStr.Append("AND i.Status =" + (int)InvoiceStatus.Awaiting_Payment + " ");
break;
case InvoiceSearchType.PassDue:
sqlStr.Append("AND i.DueOn < (select getdate() )");
break;
case InvoiceSearchType.All:
break;
}
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append(" PT.PONo LIKE @Keywords ");
sqlStr.Append(") ");
}
if (request.CompanyId != 0)
{
sqlStr.Append("AND c.ComID=@CompanyId ");
}
if ((int)request.InvoiceStatus != 0)
{
sqlStr.Append("AND i.Status=@Status ");
}
if (!string.IsNullOrEmpty(request.ApproveOn))
{
sqlStr.Append(" AND PT.ApprovedOn=@ApprovedOn ");
}
if (request.OrderExpression != "")
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY PT.ApprovedOn ");
}
if (request.OrderDirection != "")
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <POListModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString()));
db.AddInParameter(dbCommand, "CompanyId", DbType.Int32, request.CompanyId);
db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression);
db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection);
db.AddInParameter(dbCommand, "Status", DbType.Int32, request.InvoiceStatus);
db.AddInParameter(dbCommand, "ApprovedOn", DbType.String, request.ApproveOn);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <POListModel>();
while (dataReader.Read())
{
list.Add(POListModel.ReaderBind(dataReader));
}
response.POList = list;
response.POListCount = list.Count;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
//=================================================管理员==========================================
#region 管理员分页查询
/// <summary>
/// 分页查询用户
/// </summary>
/// <param name="managerQueryEntity">查询条件 海外用户实体</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="rowCount">总页数</param>
/// <returns></returns>
public DataSet GetPagingManager(ManagementCenter.Model.UserManage.ManagerQueryEntity managerQueryEntity, int pageNo, int pageSize, out int rowCount)
{
string SQL_SELECT_Manager = @"select UM_UserInfo.*,UM_ManagerBeloneToGroup.ManagerGroupID,UM_ManagerGroup.ManagerGroupName
From UM_UserInfo,UM_ManagerBeloneToGroup,UM_ManagerGroup
Where UM_UserInfo.UserID=UM_ManagerBeloneToGroup.UserID
AND UM_ManagerBeloneToGroup.ManagerGroupID=UM_ManagerGroup.ManagerGroupID ";
if (managerQueryEntity.LoginName != null && !string.IsNullOrEmpty(managerQueryEntity.LoginName))
{
SQL_SELECT_Manager += "AND LoginName LIKE '%' + @LoginName + '%' ";
}
if (managerQueryEntity.UserName != null && !string.IsNullOrEmpty(managerQueryEntity.UserName))
{
SQL_SELECT_Manager += "AND UserName LIKE '%' + @UserName + '%' ";
}
if (managerQueryEntity.UserID != int.MaxValue)
{
SQL_SELECT_Manager += "AND UM_UserInfo.UserID=@UserID ";
}
if (managerQueryEntity.RoleID != int.MaxValue)
{
SQL_SELECT_Manager += "AND RoleID=@RoleID ";
}
if (managerQueryEntity.ManagerGroupID != int.MaxValue)
{
SQL_SELECT_Manager += "AND ManagerGroupID=@ManagerGroupID ";
}
if (managerQueryEntity.ManagerGroupName != null && !string.IsNullOrEmpty(managerQueryEntity.ManagerGroupName))
{
SQL_SELECT_Manager += "AND ManagerGroupName LIKE '%' + @ManagerGroupName + '%' ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_Manager);
if (managerQueryEntity.LoginName != null && !string.IsNullOrEmpty(managerQueryEntity.LoginName))
{
database.AddInParameter(dbCommand, "LoginName", DbType.String, managerQueryEntity.LoginName);
}
if (managerQueryEntity.UserName != null && !string.IsNullOrEmpty(managerQueryEntity.UserName))
{
database.AddInParameter(dbCommand, "UserName", DbType.String, managerQueryEntity.UserName);
}
if (managerQueryEntity.UserID != int.MaxValue)
{
database.AddInParameter(dbCommand, "UserID", DbType.Int32, managerQueryEntity.UserID);
}
if (managerQueryEntity.RoleID != int.MaxValue)
{
database.AddInParameter(dbCommand, "RoleID", DbType.Int32, managerQueryEntity.RoleID);
}
if (managerQueryEntity.ManagerGroupID != int.MaxValue)
{
database.AddInParameter(dbCommand, "ManagerGroupID", DbType.Int32, managerQueryEntity.ManagerGroupID);
}
if (managerQueryEntity.ManagerGroupName != null && !string.IsNullOrEmpty(managerQueryEntity.ManagerGroupName))
{
database.AddInParameter(dbCommand, "ManagerGroupName", DbType.String, managerQueryEntity.ManagerGroupName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_Manager, pageNo, pageSize, out rowCount, "UM_UserInfo"));
}
public GamePrizeDao()
{
this._db = DatabaseFactory.CreateDatabase();
}
/// <summary>
/// 更新数据
/// </summary>
public void Update()
{
try
{
Database db = DatabaseFactory.CreateDatabase();
Hashtable htValues = new Hashtable();
_update_time = "'" + DateTime.Now.ToString("u").Replace("Z", "") + "'";
htValues.Add("screen_name", "'" + _screen_name.Replace("'", "''") + "'");
htValues.Add("name", "'" + _name.Replace("'", "''") + "'");
htValues.Add("province", "'" + _province + "'");
htValues.Add("city", "'" + _city + "'");
htValues.Add("location", "'" + _location.Replace("'", "''") + "'");
htValues.Add("description", "'" + _description.Replace("'", "''") + "'");
htValues.Add("url", "'" + _url.Replace("'", "''") + "'");
htValues.Add("profile_image_url", "'" + _profile_image_url.Replace("'", "''") + "'");
htValues.Add("domain", "'" + _domain.Replace("'", "''") + "'");
htValues.Add("gender", "'" + _gender + "'");
htValues.Add("followers_count", _followers_count);
htValues.Add("friends_count", _friends_count);
htValues.Add("statuses_count", _statuses_count);
htValues.Add("favourites_count", _favourites_count);
htValues.Add("created_at", "'" + _created_at + "'");
//-------------20120222加--------------
htValues.Add("idstr", "'" + _idstr + "'");
htValues.Add("weihao", "'" + _weihao + "'");
htValues.Add("bi_followers_count", _bi_followers_count);
htValues.Add("online_status", _online_status);
htValues.Add("verified_type", _verified_type);
htValues.Add("verified_reason", "'" + _verified_reason + "'");
if (_allow_all_comment)
{
htValues.Add("allow_all_comment", 1);
}
else
{
htValues.Add("allow_all_comment", 0);
}
htValues.Add("avatar_large", "'" + _avatar_large.Replace("'", "''") + "'");
htValues.Add("lang", "'" + _lang.Replace("'", "''") + "'");
//--------------------------------------
//-------------20120317加---------------
htValues.Add("remark", "'" + _remark + "'");
//--------------------------------------
if (_following)
{
htValues.Add("following", 1);
}
else
{
htValues.Add("following", 0);
}
if (_verified)
{
htValues.Add("verified", 1);
}
else
{
htValues.Add("verified", 0);
}
if (_allow_all_act_msg)
{
htValues.Add("allow_all_act_msg", 1);
}
else
{
htValues.Add("allow_all_act_msg", 0);
}
if (_geo_enabled)
{
htValues.Add("geo_enabled", 1);
}
else
{
htValues.Add("geo_enabled", 0);
}
htValues.Add("iteration", 0);
htValues.Add("update_time", _update_time);
db.Update("users", htValues, "user_id=" + _user_id.ToString());
}
catch
{ return; }
}
private void DialogFields_Load(object sender, EventArgs e)
{
lblTableName.Text = tableName;
dgFields.MultiSelect = !keyFlag;
string getFieldsMethod = AppSettings.GetConfigString("GetFieldsMethod");
string sql = "SELECT c.column_id AS DB_ID"
+ ",c.name AS FIELD_NAME"
+ ",TYPE_NAME(c.user_type_id) AS DATA_TYPE"
+ ",c.max_length AS LENGTH"
+ ",COLUMNPROPERTY( c.OBJECT_ID , c.Name ,'PRECISION') AS PRECISION"
+ ",p.Value AS [TITLE_CN]"
+ ",c.name AS [TITLE_EN] "
+ "FROM sys.columns c "
+ "LEFT JOIN sys.table_types t ON t.type_table_object_id = c.object_ID "
+ "LEFT JOIN sys.extended_properties p ON (p.major_id = c.OBJECT_ID OR p.major_id = t.user_type_id) AND p.minor_id = c.column_id AND p.name = 'MS_Description' "
+ "WHERE COALESCE( t.name, OBJECT_NAME(c.OBJECT_ID)) =@TABLE_NAME "
///+ "AND c.name not in ('ID','NID','FID','PARENT_FID','VALID_FLAG','CREATE_USER','CREATE_DATE','MODIFY_USER','MODIFY_DATE') "
+ "ORDER BY c.column_id;";
if (getFieldsMethod.ToLower() == "kt")
{
sql = @"select
DB_ID = a.colorder,
FIELD_NAME = a.name,
DATA_TYPE = k.name,
--Tab_index = case when exists(SELECT name FROM sysindexes WHERE id = (select id from sysobjects where name=d.name) and indid<>'0' and name=a.name) then '1' else '0' end,
LENGTH = COL_LENGTH(d.name,a.name),
PRECISION = COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'PRECISION'),
--Col_scale = isnull(COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'Scale'),0),
TITLE_CN = left(dbo.f_get_des_sql(d.name,a.name,'MS_Description'),case when CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'MS_Description'))>0 then CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'MS_Description'))-1 else 0 end ),
--Note = dbo.f_get_des_sql(d.name,a.name,'MS_Description'),
TITLE_EN = left(dbo.f_get_des_sql(d.name,a.name,'US_Description'),case when CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'US_Description'))>0 then CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'US_Description'))-1 else 0 end )
--,US_Des = dbo.f_get_des_sql(d.name,a.name,'US_Description'),
--Empty = COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'AllowsNull'),
---Col_Deflt = replace(replace(convert(varchar(200),isnull(e.text,'')),'CREATE DEFAULT con_empty AS ',''),'CREATE DEFAULT con_zero AS ',''),
---Endspace = case when A.status & 16 = 0 then '0' else '1' end,
---tab_Key = case when a.name='nid'and exists(select id from dbo.sysobjects where id = object_id('PK_'+d.name)) then '1' else '0' end
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' --U = 用户表 排出dtproperties
left join syscomments e on a.cdefault=e.id
inner join systypes k on k.xtype = a.xtype
WHERE d.name =@TABLE_NAME
---AND a.name not in ('ID','NID','FID','PARENT_FID','VALID_FLAG','CREATE_USER','CREATE_DATE','MODIFY_USER','MODIFY_DATE')
order by DB_ID";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@TABLE_NAME", DbType.AnsiString, tableName);
DataSet ds = db.ExecuteDataSet(cmd);
DataTable dtFields = ds.Tables[0];
foreach (DataRow dr in dtFields.Rows)
{
SelectedField info = new SelectedField();
info.fieldName = dr["FIELD_NAME"].ToString();
info.dataType = dr["DATA_TYPE"].ToString();
info.maxLength = int.Parse(dr["LENGTH"].ToString());
info.precision = int.Parse(dr["PRECISION"].ToString());
info.descCn = dr["TITLE_CN"].ToString();
info.descEn = dr["TITLE_EN"].ToString();
info.keyFieldFlag = keyFlag;
info.tableName = tableName;
loadedFields.Add(info);
}
dgFields.DataSource = null;
dgFields.DataSource = loadedFields;
dgFields.Refresh();
dgFields.ClearSelection();
}
/// <summary>
/// Constructor
/// </summary>
public UsersDAL()
{
db = DatabaseFactory.CreateDatabase("netStock");
}
/// <summary>
/// Opens the connection.
/// </summary>
protected void OpenConnection()
{
db = DatabaseFactory.CreateDatabase("MyGym");
connection = db.CreateConnection();
connection.Open();
}