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