//here we process the individual member public void ProcessMember(int memberId) { string karmaRoot = Path.Combine(WebRoot, karmaDir); string memberKarmaFile = Path.Combine(karmaRoot, memberId.ToString() + ".xml"); XmlDocument doc = new XmlDocument(); string xml = "<karma>" + BusinessLogic.Data.GetDataSetAsNode(ConnectionString, MemberKarmaSummarySQL(memberId), "summary").OuterXml; xml += BusinessLogic.Data.GetDataSetAsNode(ConnectionString, MemberKarmaHistorySQL(memberId), "history").OuterXml + "</karma>"; doc.LoadXml(xml); string forumTopics = "SELECT count(id) from forumTopics WHERE memberID = " + memberId.ToString(); string forumComments = "SELECT count(id) from forumComments WHERE memberID = " + memberId.ToString(); int topicCount = _sqlhelper.ExecuteScalar <int>(forumTopics); int commentCount = _sqlhelper.ExecuteScalar <int>(forumComments); XmlNode karma = doc.SelectSingleNode("//karma"); XmlNode forum = umbraco.xmlHelper.addTextNode(doc, "forum", ""); forum.AppendChild(umbraco.xmlHelper.addTextNode(doc, "topics", topicCount.ToString())); forum.AppendChild(umbraco.xmlHelper.addTextNode(doc, "comments", commentCount.ToString())); karma.AppendChild(forum); if (File.Exists(memberKarmaFile)) { File.Delete(memberKarmaFile); } doc.Save(memberKarmaFile); doc = null; }
public string CheckOrUnCheck(string strBillNO, int iCheckFlag) { string strResult = ""; OperatorModel cuModel = OperatorProvider.Provider.GetCurrent(); string strSqlCmd = ""; if (iCheckFlag == 0 || iCheckFlag == 1) { strSqlCmd = " UPDATE dbo.MY_NeedCheckProject SET FCheckFlag=@CheckFlag, " + "FCheckPeople=@FPeople,FCheckDate=GETDATE() WHERE FID=@BillNO "; SqlParameter[] para = { new SqlParameter("@CheckFlag", SqlDbType.Int), new SqlParameter("@FPeople", SqlDbType.VarChar), new SqlParameter("@BillNO", SqlDbType.VarChar) }; para[0].Value = iCheckFlag; para[1].Value = cuModel.UserCode; para[2].Value = strBillNO; sqlHelpService.ExecuteScalar(CommandType.Text, strSqlCmd, para); } else { strSqlCmd = " UPDATE dbo.MY_NeedCheckProject SET FCancelFlag=1,FCancelPeople=@FPeople,FCancelDate=GETDATE() " + " WHERE FID=@BillNO "; SqlParameter[] para = { new SqlParameter("@FPeople", SqlDbType.VarChar), new SqlParameter("@BillNO", SqlDbType.VarChar) }; para[0].Value = cuModel.UserCode; para[1].Value = strBillNO; sqlHelpService.ExecuteScalar(CommandType.Text, strSqlCmd, para); } strResult = iCheckFlag == 0 ? "反审核" : iCheckFlag == 1 ? "审核" : "作废" + "成功!"; return(strResult); }
/// <summary> /// 审核、反审核和作废单据 /// </summary> /// <param name="strBillNO"></param> /// <param name="iCheckFlag"></param> /// <returns></returns> public string CheckOrUnCheck(string strBillNO, int iCheckFlag) { string strResult = ""; OperatorModel cuModel = (new MyCommonFuncApp()).GetCurrentUser(); string strSqlCmd = ""; if (iCheckFlag == 0 || iCheckFlag == 1) { strSqlCmd = " UPDATE dbo.MY_ProjProgressMain SET FCheckFlag=@CheckFlag, " + "FCheckPeople=@FPeople,FCheckDate=GETDATE() WHERE FBillNO=@BillNO "; SqlParameter[] para = { new SqlParameter("@CheckFlag", SqlDbType.Int), new SqlParameter("@FPeople", SqlDbType.VarChar), new SqlParameter("@BillNO", SqlDbType.VarChar) }; para[0].Value = iCheckFlag; para[1].Value = cuModel.UserCode; para[2].Value = strBillNO; sp.ExecuteScalar(CommandType.Text, strSqlCmd, para); } else { strSqlCmd = " UPDATE dbo.MY_ProjProgressMain SET FCancelFlag=1,FCancelPeople=@FPeople,FCancelDate=GETDATE() " + " WHERE FBillNO=@BillNO "; SqlParameter[] para = { new SqlParameter("@FPeople", SqlDbType.VarChar), new SqlParameter("@BillNO", SqlDbType.VarChar) }; para[0].Value = cuModel.UserCode; para[1].Value = strBillNO; sp.ExecuteScalar(CommandType.Text, strSqlCmd, para); } strResult = iCheckFlag == 0?"反审核":iCheckFlag == 1?"审核":"作废" + "成功!"; return(strResult); }
public Form GetFormFromRecordId(Guid recordId) { string sql = @"SELECT Form AS FormId FROM UFRecords WHERE Id = @id"; return(GetForm(sqlHelper.ExecuteScalar <Guid>(sql, sqlHelper.CreateParameter("@id", recordId)))); }
/// <summary> /// Initializes a new instance of the <see cref="PreValue"/> class. /// </summary> /// <param name="DataTypeId">The data type id.</param> /// <param name="Value">The value.</param> public PreValue(int DataTypeId, string Value) { object id = SqlHelper.ExecuteScalar <object>( "Select id from cmsDataTypePreValues where [Value] = @value and DataTypeNodeId = @dataTypeId", SqlHelper.CreateParameter("@dataTypeId", DataTypeId), SqlHelper.CreateParameter("@value", Value)); if (id != null) { _id = int.Parse(id.ToString()); } initialize(); }
public Package(Guid Id) { int installStatusId = SqlHelper.ExecuteScalar <int>( "select id from umbracoInstalledPackages where package = @package and upgradeId = 0", SqlHelper.CreateParameter("@package", Id)); if (installStatusId > 0) { Initialize(installStatusId); } else { throw new ArgumentException("Package with id '" + Id.ToString() + "' is not installed"); } }
public bool IsInPublishedHierarchy(int nodeId, ISqlHelper sqlHelper) { var parentId = sqlHelper.ExecuteScalar<int>("select parentId from [umbracoNode] where id = @nodeId", sqlHelper.CreateParameter("nodeId", nodeId)); if (parentId == -1) return true; else { var parentIsPublished = sqlHelper.ExecuteScalar<int>("select count(*) from [cmsDocument] where nodeId = @nodeId and published = 1 ", sqlHelper.CreateParameter("nodeId", parentId)) > 0; if (!parentIsPublished) return false; else return (IsInPublishedHierarchy(parentId, sqlHelper)); } }
public FieldCondition UpdateFieldCondition(FieldCondition fieldCondition) { string sql = "UPDATE UFfieldconditions set enabled = @enabled, actiontype = @actiontype, logictype = @logictype WHERE field = @field"; sqlHelper.ExecuteNonQuery(sql, sqlHelper.CreateParameter("@field", fieldCondition.Field), sqlHelper.CreateParameter("@enabled", fieldCondition.Enabled), sqlHelper.CreateParameter("@actiontype", fieldCondition.ActionType), sqlHelper.CreateParameter("@logictype", fieldCondition.LogicType)); fieldCondition.Id = sqlHelper.ExecuteScalar <Guid>("Select Id from UFfieldconditions where field = @field", sqlHelper.CreateParameter("@field", fieldCondition.Field)); storage.DeleteFieldConditionRules(fieldCondition); if (fieldCondition.Rules != null) { List <FieldConditionRule> _rules = new List <FieldConditionRule>(); foreach (FieldConditionRule fcr in fieldCondition.Rules) { fcr.FieldCondition = fieldCondition.Id; _rules.Add(storage.InsertFieldConditionRule(fcr)); } fieldCondition.Rules = _rules; } return(fieldCondition); }
//The fastest way to test if a field is actually there public bool FieldExists(Guid id) { string sql = @"SELECT UFfields.Caption FROM UFfields where UFfields.id = @id"; string caption = sqlHelper.ExecuteScalar <string>(sql, sqlHelper.CreateParameter("@id", id)); return(!string.IsNullOrEmpty(caption)); }
/// <summary> /// Creates a new language given the culture code - ie. da-dk (denmark) /// </summary> /// <param name="cultureCode">Culturecode of the language</param> public static void MakeNew(string cultureCode) { lock (Locker) { var culture = GetCulture(cultureCode); if (culture != null) { //insert it SqlHelper.ExecuteNonQuery( "insert into umbracoLanguage (languageISOCode) values (@CultureCode)", SqlHelper.CreateParameter("@CultureCode", cultureCode)); InvalidateCache(); //get it's id var newId = SqlHelper.ExecuteScalar <int>("SELECT MAX(id) FROM umbracoLanguage WHERE languageISOCode=@cultureCode", SqlHelper.CreateParameter("@cultureCode", cultureCode)); //load it and raise events using (var dr = SqlHelper.ExecuteReader(string.Format("{0} where id = {1}", m_SQLOptimizedGetAll, newId))) { while (dr.Read()) { var ct = new Language(); ct.PopulateFromReader(dr); ct.OnNew(new NewEventArgs()); } } } } }
public Property(int Id) { _id = Id; _pt = umbraco.cms.businesslogic.propertytype.PropertyType.GetPropertyType( SqlHelper.ExecuteScalar <int>("select propertytypeid from cmsPropertyData where id = @id", SqlHelper.CreateParameter("@id", Id))); _data = _pt.DataTypeDefinition.DataType.Data; _data.PropertyId = Id; }
/// <summary> /// Executes a command that returns a single value. /// </summary> /// <typeparam name="T">The type of the value.</typeparam> /// <param name="commandText">The command text.</param> /// <param name="parameters">The parameters.</param> /// <returns>The return value of the command.</returns> /// <exception cref="umbraco.DataLayer.SqlHelperException">If a data source error occurs.</exception> public T ExecuteScalar <T>(string commandText, params IParameter[] parameters) { foreach (ISqlHelperExtension extension in m_Extensions) { extension.OnExecuteScalar(m_SqlHelper, ref commandText, ref parameters); } return(m_SqlHelper.ExecuteScalar <T>(commandText, parameters)); }
public int GetPageCount(int pageSize, out int totalCount) { int recordCount = (int)sqlHelper.ExecuteScalar("SELECT COUNT(*) FROM TB_NEWS WHERE IsDeleted=0", CommandType.Text); int pageCount = (int)Math.Ceiling(recordCount / (pageSize * 1.0)); totalCount = recordCount; return(pageCount); }
public Domain(string DomainName) { object result = SqlHelper.ExecuteScalar <object>( "select id from umbracoDomains where domainName = @DomainName", SqlHelper.CreateParameter("@DomainName", DomainName)); if (result == null || !(result is int)) { throw new Exception(string.Format("Domain name '{0}' does not exists", DomainName)); } InitDomain((int)result); }
/// <summary> /// Fixes the size of the Value field in UFRecordDataString so that it's compatible with the corresponding old Courier table if necessary. /// </summary> private void FixDataStringLength(ISqlHelper sqlHelper) { // Get the max length of a string in the source UFRecordDataString table. int maxLength = sqlHelper.ExecuteScalar <int>("SELECT MAX(LEN([Value])) FROM [UFRecordDataString]"); if (maxLength > DefaultStringValueLength) { ApplicationContext.Current.DatabaseContext.Database.Execute( string.Format("ALTER TABLE [UFRecordDataString] ALTER COLUMN[Value] NVARCHAR({0});", maxLength) ); } }
static void Main(string[] args) { ISqlHelper sqlHelper = MySqlHelper.Instance; //string insertSql = "insert into [Users](Name,Age) values('ww',13);"; //int res = sqlHelper.ExecuteNonQuery(insertSql); //Console.WriteLine($"插入{res}记录"); string selectSql = "select count(*) from [Users];"; object count = sqlHelper.ExecuteScalar(selectSql); Console.WriteLine($"共有{count}记录"); }
private static int GetRelations(string alias, int parentId) { ISqlHelper sqlhelper = umbraco.BusinessLogic.Application.SqlHelper; int result = sqlhelper.ExecuteScalar <int>( @" SELECT count(umbracoRelation.id) FROM umbracoRelation INNER JOIN umbracoRelationType ON umbracoRelationType.id = umbracoRelation.relType AND umbracoRelationType.alias = @alias where parentId = @parent " , sqlhelper.CreateParameter("@parent", parentId), sqlhelper.CreateParameter("@alias", alias) ); return(result); }
/// <summary> /// 不带参数的文本 /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public static List <Dictionary <string, object> > ExecuteScalar(string cmdText) { List <Dictionary <string, object> > resDics = new List <Dictionary <string, object> >(); var connDics = new ConnectionHelper().GetOrgConnDic(); foreach (var dic in connDics) { Dictionary <string, object> resDic = new Dictionary <string, object>(); resDic["code"] = dic["code"]; resDic["name"] = dic["name"]; ISqlHelper sqlHelper = SqlConvertHelper.GetInstallSqlHelper(dic["connectionstring"]); resDic["data"] = sqlHelper.ExecuteScalar(cmdText); resDics.Add(resDic); } return(resDics); }
/// <summary> /// 返回第一行第一列的值 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="spr"></param> /// <returns></returns> public static List <OrganizationExecuteResult> ExecuteScalar(CommandType cmdType, string cmdText, List <SqlParameter> spr) { List <OrganizationExecuteResult> result = new List <OrganizationExecuteResult>(); var organizationEntitys = new ConnectionHelper().GetOrganizationEntitys(); foreach (var organizationEntity in organizationEntitys) { OrganizationExecuteResult organizationExecuteResult = new OrganizationExecuteResult(); organizationExecuteResult.Code = organizationEntity.Code; organizationExecuteResult.Name = organizationEntity.Name; ISqlHelper sqlHelper = SqlConvertHelper.GetInstallSqlHelper(organizationEntity.Connectionstring); organizationExecuteResult.Data = sqlHelper.ExecuteScalar(cmdType, cmdText, spr); result.Add(organizationExecuteResult); } return(result); }
public async Task AddSupplierAsync(Supplier supplier) { string sql = string.Format("INSERT INTO [Supplier] ([CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," + "[ZIPCode],[FAXNo],[DateFormat],[noofstages],[CompanyProfileID],[PoLetterHead]) VALUES ('{0}', '{1}','{2}','{3}', '{4}','{5}','{6}', '{7}','{8}','{9}','{10}', '{11}','{12}','{13}','{14}')", supplier.CompanyId, supplier.Name.Replace("'", "''"), supplier.ContactPersonName.Replace("'", "''"), supplier.PhoneNo, supplier.EmailID.Replace("'", "''"), supplier.Address.Replace("'", "''"), supplier.City.Replace("'", "''"), supplier.State.Replace("'", "''"), supplier.Country.Replace("'", "''"), supplier.ZIPCode.Replace("'", "''"), supplier.FAXNo, supplier.DateFormat, supplier.noofstages, supplier.CompanyProfileID, supplier.PoLetterHead); sql = sql + " Select Scope_Identity()"; var supplierId = _sqlHelper.ExecuteScalar(ConnectionSettings.ConnectionString, sql, CommandType.Text); foreach (SupplierTerms term in supplier.Terms) { sql = string.Format("INSERT INTO [dbo].[supplierterms]([supplierId],[sequenceNo],[terms]) VALUES ('{0}', '{1}','{2}')", supplierId, term.SequenceNo, term.Terms.Replace("'", "''")); await _sqlHelper.ExecuteNonQueryAsync(ConnectionSettings.ConnectionString, sql, CommandType.Text); } }
/// <summary> /// 同步查询操作 /// </summary> /// <param name="connection">连接字符串</param> /// <param name="cmd">sql语句</param> /// <param name="param">参数</param> /// <param name="flag">true存储过程,false sql语句</param> /// <returns>T</returns> public T ExecuteScalar <T>(string connection, string cmd, DynamicParameters param, bool flag = false) { return(_sqlhelper.ExecuteScalar <T>(connection, cmd, param, flag)); }
/// <summary> /// Initializes a new instance of the <see cref="CMSNode"/> class. /// </summary> /// <param name="uniqueID">The unique ID.</param> public CMSNode(Guid uniqueID) { _id = SqlHelper.ExecuteScalar <int>("SELECT id FROM umbracoNode WHERE uniqueID = @uniqueId", SqlHelper.CreateParameter("@uniqueId", uniqueID)); setupNode(); }
public void SqlServerHelperForScalar() { var result = helper.ExecuteScalar("SELECT COUNT(*) FROM TB_MESSAGE"); Assert.AreEqual(Convert.ToInt32(result), 2); }
public TScalarType ExecuteScalar <TScalarType>(string commandText, params IParameter[] parameters) { return(SqlHelper.ExecuteScalar <TScalarType>(commandText, parameters)); }
public async Task AddCustomerAsync(Customer customer) { if (customer.Name == null) { customer.Name = string.Empty; } if (customer.AddressLine1 == null) { customer.AddressLine1 = string.Empty; } if (customer.City == null) { customer.City = string.Empty; } if (customer.State == null) { customer.State = string.Empty; } if (customer.ZIPCode == null) { customer.ZIPCode = string.Empty; } if (customer.ContactPersonName == null) { customer.ContactPersonName = string.Empty; } if (customer.TelephoneNumber == null) { customer.TelephoneNumber = string.Empty; } if (customer.FaxNumber == null) { customer.FaxNumber = string.Empty; } if (customer.EmailAddress == null) { customer.EmailAddress = string.Empty; } if (customer.TruckType == null) { customer.TruckType = string.Empty; } if (customer.CollectFreight == null) { customer.CollectFreight = string.Empty; } if (customer.Comments == null) { customer.Comments = string.Empty; } if (customer.FOB == null) { customer.FOB = string.Empty; } if (customer.Terms == null) { customer.Terms = string.Empty; } if (customer.ShipVia == null) { customer.ShipVia = string.Empty; } if (customer.EndCustomerName == null) { customer.EndCustomerName = string.Empty; } if (customer.Billing == null) { customer.Billing = string.Empty; } string sql = string.Format($"INSERT INTO [dbo].[customer] ([CompanyId],[Name],[AddressLine1],[City],[State],[ZIPCode],[ContactPersonName],[TelephoneNumber],[FaxNumber],[EmailAddress],[TruckType],[CollectFreight],[Comments],[Surcharge],[FOB],[Terms],[RePackingCharge],[ShipVia],[invoicingtypeid],[endcustomername],[DisplayLineNo],[Billing],[RePackingPoNo]) VALUES ('{customer.CompanyId}', " + $"'{customer.Name.Replace("'", "''")}', '{customer.AddressLine1.Replace("'", "''")}', '{customer.City.Replace("'", "''")}', '{customer.State.Replace("'", "''")}', '{customer.ZIPCode.Replace("'", "''")}', '{customer.ContactPersonName.Replace("'", "''")}', '{customer.TelephoneNumber.Replace("'", "''")}', " + $"'{customer.FaxNumber.Replace("'", "''")}', '{customer.EmailAddress.Replace("'", "''")}', '{customer.TruckType.Replace("'", "''")}', '{customer.CollectFreight.Replace("'", "''")}', " + $"'{customer.Comments.Replace("'", "''")}', '{customer.Surcharge}', '{customer.FOB.Replace("'", "''")}', '{customer.Terms.Replace("'", "''")}', '{customer.RePackingCharge}', " + $"'{customer.ShipVia.Replace("'", "''")}', '{1}', '{customer.EndCustomerName.Replace("'", "''")}','{ customer.DisplayLineNo}','{ customer.Billing.Replace("'", "''")}','{ string.Empty}')"); sql = sql + " Select Scope_Identity()"; var customerId = _sqlHelper.ExecuteScalar(ConnectionSettings.ConnectionString, sql, CommandType.Text); foreach (CustomerShippingInfo term in customer.ShippingInfos) { if (term.Name == null) { term.Name = string.Empty; } if (term.ContactPersonName == null) { term.ContactPersonName = string.Empty; } if (term.AddressLine1 == null) { term.AddressLine1 = string.Empty; } if (term.City == null) { term.City = string.Empty; } if (term.State == null) { term.State = string.Empty; } if (term.ZIPCode == null) { term.ZIPCode = string.Empty; } sql = string.Format($"INSERT INTO [dbo].[customershippinginfo] ([CustomerID] ,[Name] ,[ContactPersonName] ,[AddressLine1] ,[City] ,[State],[ZIPCode],[IsDefault],[IsOld]) VALUES ( " + $"'{customerId}' , '{term.Name.Replace("'", "''")}' , '{term.ContactPersonName.Replace("'", "''")}' ,'{term.AddressLine1.Replace("'", "''")}', '{term.City.Replace("'", "''")}' , '{term.State.Replace("'", "''")}' , '{term.ZIPCode.Replace("'", "''")}' , '{term.IsDefault}','{false}')"); await _sqlHelper.ExecuteNonQueryAsync(ConnectionSettings.ConnectionString, sql, CommandType.Text); } }