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