public DataSet UserSelectAccountWithEmail(string email)
{
//w_user_select_account_email
DataSet ds = new DataSet();
try
{
SqlConnection con = new SqlConnection(dataaccess.configsql.strcon);
con.Open();
SqlCommand command = new SqlCommand("w_user_select_account_email", con);
command.CommandType = CommandType.StoredProcedure;
//1: username:
SqlParameterCollection prmcols = command.Parameters;
prmcols.Add(new SqlParameter("@email", SqlDbType.VarChar, 255));
prmcols["@email"].Value = email;
sqlda.SelectCommand = command;
sqlda.Fill(ds);
con.Close();
}
catch
{
}
return(ds);
}
public bool Save()
{
try
{
SqlCommand cmd = new SqlCommand();
SqlParameterCollection Params = cmd.Parameters;
Params.Add(new SqlParameter("ID", this.ID));
Params.Add(new SqlParameter("Length", this.Length));
Params.Add(new SqlParameter("Width", this.Width));
Params.Add(new SqlParameter("Amount", this.Amount));
Params.Add(new SqlParameter("WaiveID", this.WaiveID));
Params.Add(new SqlParameter("PartID", PartID));
Params.Add(new SqlParameter("MaterialID", this.MaterialID));
this.ID = Execute.Scalar(StoredProcedures.SaveWaiveEditMaterialAmount, Params);
return(true);
}
catch (Exception e)
{
return(false);
}
}
internal static void SaveCode(List <RuleCode> rules)
{
foreach (var ruleCode in rules)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["DSN"]))
using (SqlCommand command = new SqlCommand("[RulesEngine].spa_InsertNewCode", conn))
{
command.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlParameterCollection parameters = command.Parameters;
parameters.Add("@InputTypeId", SqlDbType.VarChar, 50).Value = ruleCode.InputTypeId;
parameters.Add("@OutputTypeId", SqlDbType.VarChar, 50).Value = ruleCode.OutputTypeId;
parameters.Add("@UsrId", SqlDbType.VarChar, 50).Value = ruleCode.CreatedByUserId;
parameters.Add("@CodeText", SqlDbType.VarChar, 50).Value = ruleCode.CodeText;
parameters.Add("@CodeHash", SqlDbType.VarChar, 50).Value = ruleCode.CodeHash;
parameters.Add("@OriginalXPathHash", SqlDbType.VarChar, 50).Value = ruleCode.OriginalXpathHash;
parameters.Add("@OriginalXPath", SqlDbType.VarChar, 50).Value = ruleCode.OriginalXpath;
command.ExecuteNonQuery();
}
}
}
public Boolean AdvertiseInsert(string title, int sort, string link, string image, string ishow, string note)
{
//w_articles_insert
Boolean test = true;
try
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(dataaccess.configsql.strcon);
con.Open();
SqlCommand command = new SqlCommand("w_advertise_insert", con);
command.CommandType = CommandType.StoredProcedure;
SqlParameterCollection prmcols = command.Parameters;
prmcols.Add(new SqlParameter("@title", SqlDbType.NVarChar, 128));
prmcols["@title"].Value = title;
prmcols.Add(new SqlParameter("@sort", SqlDbType.Int, 4));
prmcols["@sort"].Value = sort;
prmcols.Add(new SqlParameter("@link", SqlDbType.VarChar, 255));
prmcols["@link"].Value = link;
prmcols.Add(new SqlParameter("@image", SqlDbType.VarChar, 128));
prmcols["@image"].Value = image;
prmcols.Add(new SqlParameter("@show", SqlDbType.VarChar, 1));
prmcols["@show"].Value = ishow;
prmcols.Add(new SqlParameter("@note", SqlDbType.NVarChar, 255));
prmcols["@note"].Value = note;
sqlda.SelectCommand = command;
sqlda.Fill(ds);
con.Close();
}
catch (Exception ex)
{
Console.Write(ex.ToString());
test = false;
}
return(test);
}
public bool Save()
{
try
{
SqlCommand cmd = new SqlCommand();
SqlParameterCollection Params = cmd.Parameters;
Params.Add(new SqlParameter("ID", this.ID));
Params.Add(new SqlParameter("Title", this.Title));
Params.Add(new SqlParameter("ActivityID", this.ActivityID));
Params.Add(new SqlParameter("CaseID", this.CaseID));
Params.Add(new SqlParameter("PieceworkUnitPrice", this.PieceworkUnitPrice));
Params.Add(new SqlParameter("PieceworkMaterialUnitID", this.PieceworkMaterialUnitID));
Params.Add(new SqlParameter("StructureID", this.StructureID));
this.ID = Execute.Scalar(StoredProcedures.SavePart, Params);
return(true);
}
catch (Exception e)
{
return(false);
}
}
public Boolean OnlineInsert(string name, string nickname, string title, int idgroup, string namegroup, int sort, int type)
{
//w_support_online_insert
try
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(dataaccess.configsql.strcon);
con.Open();
SqlCommand command = new SqlCommand("w_support_online_insert", con);
command.CommandType = CommandType.StoredProcedure;
SqlParameterCollection prmcols = command.Parameters;
prmcols.Add(new SqlParameter("@name", SqlDbType.NVarChar, 64));
prmcols["@name"].Value = name;
prmcols.Add(new SqlParameter("@nickname", SqlDbType.VarChar, 64));
prmcols["@nickname"].Value = nickname;
prmcols.Add(new SqlParameter("@title", SqlDbType.NVarChar, 64));
prmcols["@title"].Value = title;
prmcols.Add(new SqlParameter("@idgroup", SqlDbType.Int, 4));
prmcols["@idgroup"].Value = idgroup;
prmcols.Add(new SqlParameter("@namegroup", SqlDbType.NVarChar, 64));
prmcols["@namegroup"].Value = namegroup;
prmcols.Add(new SqlParameter("@sort", SqlDbType.Int, 4));
prmcols["@sort"].Value = sort;
prmcols.Add(new SqlParameter("@type", SqlDbType.Int, 4));
prmcols["@type"].Value = type;
sqlda.SelectCommand = command;
sqlda.Fill(ds);
con.Close();
}
catch (Exception ex)
{
Console.Write(ex.ToString());
return(false);
}
return(true);
}
public UpdatePersonByPersonIdCommand(SqlCommand sqlCommand)
: base(sqlCommand)
{
Command.CommandText = "[dbo].[spUpdatetblPersonByPersonId]";
Command.CommandType = CommandType.StoredProcedure;
_returnValue = CreateParameter("RETURN_VALUE", SqlDbType.Int, ParameterDirection.ReturnValue);
_personId = CreateParameter("personId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_personFirstName = CreateParameter("personFirstName", SqlDbType.VarChar, ParameterDirection.Input);
_personLastName = CreateParameter("personLastName", SqlDbType.VarChar, ParameterDirection.Input);
_personPhone = CreateParameter("personPhone", SqlDbType.Char, ParameterDirection.Input);
_personEmail = CreateParameter("personEmail", SqlDbType.VarChar, ParameterDirection.Input);
_personTypeId = CreateParameter("personTypeId", SqlDbType.Int, ParameterDirection.Input);
SqlParameterCollection sqlParameterCollection = sqlCommand.Parameters;
sqlParameterCollection.Add(_returnValue);
sqlParameterCollection.Add(_personId);
sqlParameterCollection.Add(_personFirstName);
sqlParameterCollection.Add(_personLastName);
sqlParameterCollection.Add(_personPhone);
sqlParameterCollection.Add(_personEmail);
sqlParameterCollection.Add(_personTypeId);
}
public InsertUserAccessCommand(SqlCommand sqlCommand)
: base(sqlCommand)
{
Command.CommandText = "[dbo].[spInserttblUserAccess]";
Command.CommandType = CommandType.StoredProcedure;
_returnValue = CreateParameter("RETURN_VALUE", SqlDbType.Int, ParameterDirection.ReturnValue);
_userId = CreateParameter("userId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_userName = CreateParameter("userName", SqlDbType.VarChar, ParameterDirection.Input);
_userPassword = CreateParameter("userPassword", SqlDbType.VarChar, ParameterDirection.Input);
_permissionToken = CreateParameter("permissionToken", SqlDbType.Int, ParameterDirection.Input);
_personId = CreateParameter("personId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
SqlParameterCollection sqlParameterCollection = sqlCommand.Parameters;
sqlParameterCollection.Add(_returnValue);
sqlParameterCollection.Add(_userId);
sqlParameterCollection.Add(_userName);
sqlParameterCollection.Add(_userPassword);
sqlParameterCollection.Add(_permissionToken);
sqlParameterCollection.Add(_personId);
}
public UpdateNotificationByNotificationIdCommand(SqlCommand sqlCommand)
: base(sqlCommand)
{
Command.CommandText = "[dbo].[spUpdatetblNotificationByNotificationId]";
Command.CommandType = CommandType.StoredProcedure;
_returnValue = CreateParameter("RETURN_VALUE", SqlDbType.Int, ParameterDirection.ReturnValue);
_notificationId = CreateParameter("notificationId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_orderId = CreateParameter("orderId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_notificationMessage = CreateParameter("notificationMessage", SqlDbType.VarChar, ParameterDirection.Input);
_notificationTypeId = CreateParameter("notificationTypeId", SqlDbType.Int, ParameterDirection.Input);
_isRead = CreateParameter("isRead", SqlDbType.Bit, ParameterDirection.Input);
SqlParameterCollection sqlParameterCollection = sqlCommand.Parameters;
sqlParameterCollection.Add(_returnValue);
sqlParameterCollection.Add(_notificationId);
sqlParameterCollection.Add(_orderId);
sqlParameterCollection.Add(_notificationMessage);
sqlParameterCollection.Add(_notificationTypeId);
sqlParameterCollection.Add(_isRead);
}
/// <summary>
/// 添加调试信息
/// </summary>
/// <param name="businessDebug">调试信息</param>
/// <returns>返回调试信息</returns>
public BusinessDebug AddDebug(BusinessDebug businessDebug)
{
SqlParameterCollection spc = DB.CreateSqlParameterCollection();
spc.Add("@SystemName", businessDebug.SystemName);
spc.Add("@ModuleName", businessDebug.ModuleName);
spc.Add("@CategoryName", businessDebug.CategoryName);
spc.Add("@DebugMessage", businessDebug.DebugMessage);
spc.Add("@DebugText", businessDebug.DebugText);
businessDebug.DebugDateTime = DateTime.Now;
spc.Add("@DebugDateTime", businessDebug.DebugDateTime);
string sql = "insert into g_BusinessDebug(SystemName, ModuleName, CategoryName, DebugMessage, DebugText, DebugDateTime)"
+ "values(@SystemName, @ModuleName, @CategoryName, @DebugMessage, @DebugText, @DebugDateTime)";
DB.ExecuteNonQuerySql(sql, spc);
businessDebug.DebugID = DB.ExeSqlForObject("select max(DebugID) from g_BusinessDebug", null).ToInt64();
return(businessDebug);
}
public UpdateOrderByOrderIdCommand(SqlCommand sqlCommand)
: base(sqlCommand)
{
Command.CommandText = "[dbo].[spUpdatetblOrderByOrderId]";
Command.CommandType = CommandType.StoredProcedure;
_returnValue = CreateParameter("RETURN_VALUE", SqlDbType.Int, ParameterDirection.ReturnValue);
_personId = CreateParameter("personId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_orderId = CreateParameter("orderId", SqlDbType.UniqueIdentifier, ParameterDirection.Input);
_orderEntryDate = CreateParameter("orderEntryDate", SqlDbType.DateTime, ParameterDirection.Input);
_orderFulfillDate = CreateParameter("orderFulfillDate", SqlDbType.DateTime, ParameterDirection.Input);
_orderStatusId = CreateParameter("orderStatusId", SqlDbType.Int, ParameterDirection.Input);
SqlParameterCollection sqlParameterCollection = sqlCommand.Parameters;
sqlParameterCollection.Add(_returnValue);
sqlParameterCollection.Add(_personId);
sqlParameterCollection.Add(_orderId);
sqlParameterCollection.Add(_orderEntryDate);
sqlParameterCollection.Add(_orderFulfillDate);
sqlParameterCollection.Add(_orderStatusId);
}
public string[] GetHR(string Type, string K1, string K2, string K3, string K4, string K5)
{
_connection.Open();
_command = new SqlCommand("[dbo].[AutoComplete]", _connection);
_command.CommandType = CommandType.StoredProcedure;
p = _command.Parameters;
p.Add("Type", SqlDbType.Text).Value = Type;
p.Add("Key1", SqlDbType.Text).Value = K1;
p.Add("Key2", SqlDbType.Text).Value = K2;
p.Add("Key3", SqlDbType.Text).Value = K3;
p.Add("Key4", SqlDbType.Text).Value = K4;
p.Add("Key5", SqlDbType.Text).Value = K5;
returnData = new List <string>();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
while (_reader.Read())
{
returnData.Add(_reader["Label"].ToString());
}
_command.Dispose();
_reader.Dispose();
_connection.Dispose();
return(returnData.ToArray());
}
public bool Save()
{
try
{
SqlCommand cmd = new SqlCommand();
SqlParameterCollection Params = cmd.Parameters;
Params.Add(new SqlParameter("ID", this.ID));
Params.Add(new SqlParameter("Title", this.Title));
Params.Add(new SqlParameter("DateStart", this.DateStart));
Params.Add(new SqlParameter("DateEnd", this.DateEnd));
Params.Add(new SqlParameter("CaseID", this.CaseID));
Params.Add(new SqlParameter("Active", this.Active));
this.ID = Execute.Scalar(StoredProcedures.SaveMilestone, Params);
return(true);
}
catch (Exception e)
{
return(false);
}
}
public RemovedPackageMaterialAmount(Int64 PackageID, Int64 PartID, Int64 MaterialID, decimal Length, decimal Width, Int64 WaiveID)
{
Initialize();
SqlCommand cmd = new SqlCommand();
SqlParameterCollection Params = cmd.Parameters;
Params.Add(new SqlParameter("PackageID", PackageID));
Params.Add(new SqlParameter("PartID", PartID));
Params.Add(new SqlParameter("MaterialID", MaterialID));
Params.Add(new SqlParameter("Length", Length));
Params.Add(new SqlParameter("Width", Width));
Params.Add(new SqlParameter("WaiveID", WaiveID));
DataTable dt = Execute.FillDataTable(StoredProcedures.GetRemovedPackageMaterialAmountByPackageID_PartID_MaterialID_Length_Width_WaiveID, Params);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
Utils.LoadRemovedPackageMaterialAmountByReader(row, this);
}
}
}
public static bool Insert(Object inentity)
{
//TODO:insertte object yada id dönmeli
SqlCommand cmd = new SqlCommand();
PropertyInfo[] props = inentity.GetType().GetProperties();
string sql = @"INSERT INTO {0}
(
Id,Aktif,EklemeTarihi,Islemturu,ekleyenmakadres,
{1}
)
VALUES
(
(select isnull(max(Id),0)+1 from {0}),1,getdate()," +
(int)mycommon.enumcommon.Islemturu.Insert + ",'" + GetMakAdres() + @"',
{2}
)
";
string names = "";
string values = "";
SqlParameterCollection parameters = cmd.Parameters;
foreach (PropertyInfo pi in props)
{
string propName = pi.Name;
object value = pi.GetValue(inentity, null);
if (value == null ||
pi.Name == "Id" ||
pi.Name == "Aktif" ||
pi.Name == "EklemeTarihi" ||
pi.Name == "EkleyenMakAdres" ||
pi.Name == "Islemturu" ||
(pi.PropertyType == typeof(DateTime) && (DateTime)value == DateTime.MinValue)
)
{
continue;
}
if (pi.PropertyType.BaseType.Name.Contains("basemodel"))
{
propName = pi.Name + "_Id";
if (value != DBNull.Value)
{
value = GetValue(value, "Id");
}
}
names = names + propName + ",";
values = values + "@" + propName + ",";
SqlParameter p = new SqlParameter(propName, value);
parameters.Add(p);
}
names = names.Substring(0, names.LastIndexOf(','));
values = values.Substring(0, values.LastIndexOf(','));
sql = string.Format(sql, inentity.GetType().Name, names, values);
cmd.CommandText = sql;
string res = RunSql(cmd);
if (res == "ok")
{
return(true);
}
else
{
return(false);
}
}
public static bool Update(Object inentity)
{
try
{
SqlCommand cmd = new SqlCommand();
PropertyInfo[] props = inentity.GetType().GetProperties();
string sql = "UPDATE {0} SET DegistirmeTarihi=getdate(),DegistirenMakAdres='" + GetMakAdres() +
"',Islemturu=" + (int)mycommon.enumcommon.Islemturu.Update + ",{1} WHERE Id={2}";
string names = "";
SqlParameterCollection parameters = cmd.Parameters;
foreach (PropertyInfo pi in props)
{
if (pi.Name != "Id")
{
string propName = pi.Name;
if (pi.Name == "DegistirmeTarihi" ||
pi.Name == "DegistirenMakAdres" ||
pi.Name == "Islemturu")
{
continue;
}
object value = pi.GetValue(inentity, null);
if (value == null)
{
value = DBNull.Value;
}
if (pi.PropertyType == typeof(DateTime) &&
(DateTime)value == DateTime.MinValue)
{
value = DBNull.Value;
}
if (pi.PropertyType.BaseType.Name.Contains("basemodel"))
{
propName = pi.Name + "_Id";
if (value != DBNull.Value)
{
value = GetValue(value, "Id");
}
}
names = names + propName + "=" + "@" + propName + ",";
SqlParameter p = new SqlParameter(propName, value);
parameters.Add(p);
}
}
names = names.Substring(0, names.LastIndexOf(','));
object Id = inentity.GetType().GetProperty("Id").GetValue(inentity, null);
sql = string.Format(sql, inentity.GetType().Name, names, Id);
cmd.CommandText = sql;
string res = RunSql(cmd);
if (res == "ok")
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public static SqlParameter AddStructured(this SqlParameterCollection spc, string name, string typename, DbDataReader value)
{
var param = SqlParams.Structured(name, typename, value);
return(spc.Add(param));
}
//Adicionando Parâmetros;
public void AdicionarParametros(string nomeParametro, object valorParametro)
{
sqlParameterCollection.Add(new SqlParameter(nomeParametro, valorParametro));
}
public string ZTreeJsonOfDxjb(GgfzInfoModel searchCondition, string dxjbId)
{
string sql0 = "select * from Szgkjc_Dxjb_Sjml where DxjbId=@DxjbId ";
SqlParameterCollection spc = this.DB.CreateSqlParameterCollection();
spc.Add("@DxjbId", dxjbId);
DataTable dtGzhf = DB.ExeSqlForDataTable(sql0, spc, "t");
string sql = @" select * from (
select distinct a.groupID,a.groupName,c.sjmlID,c.sjmlName+' 【'+ isnull(c.sjmlMobile,'') +'】' sjmlName,c.sjmlTel,c.SjmlEmail
from Gwtz_Ggfz a
inner join Gwtz_Ggfz_Sjml b on b.groupID = a.groupID
inner join Gwtz_Sjml c on c.sjmlID = b.sjmlID
where 1=1
) t
where 1=1 ";
spc = this.DB.CreateSqlParameterCollection();
if (!string.IsNullOrEmpty(searchCondition.SjmlName))
{
sql += " and SjmlName like @SjmlName";
spc.Add("@SjmlName", '%' + searchCondition.SjmlName + '%');
}
sql += " order by groupID ";
DataTable dtSjr = this.DB.ExeSqlForDataTable(sql, spc, "t");
string sql2 = @" select distinct a.groupID,a.groupName
from Gwtz_Ggfz a
inner join Gwtz_Ggfz_Sjml b on b.groupID = a.groupID
inner join Gwtz_Sjml c on c.sjmlID = b.sjmlID
where 1=1 ";
SqlParameterCollection spc2 = this.DB.CreateSqlParameterCollection();
if (!string.IsNullOrEmpty(searchCondition.SjmlName))
{
sql2 += " and c.SjmlName like @SjmlName";
spc2.Add("@SjmlName", '%' + searchCondition.SjmlName + '%');
}
DataTable dtSjz = this.DB.ExeSqlForDataTable(sql2, spc2, "t2");
DataRow[] sjzRows = dtSjz.Select("1=1");
string resultJosn = string.Empty;
if (sjzRows.Length > 0)
{
resultJosn = @"[";
for (int i = 0; i < dtSjz.Rows.Count; i++)
{
if (i == 0)
{
resultJosn += @"{""id"":" + dtSjz.Rows[i]["groupID"] + @",""name"":""" + dtSjz.Rows[i]["groupName"] + @""",""open"":""false""";
}
else
{
resultJosn += @",{""id"":" + dtSjz.Rows[i]["groupID"] + @",""name"":""" + dtSjz.Rows[i]["groupName"] + @""",""open"":""false""";
}
if (dtSjr.Select("groupID=" + dtSjz.Rows[i]["groupID"]).Length > 0)
{
DataTable tempdtSjr = dtSjr.Select("groupID=" + dtSjz.Rows[i]["groupID"]).CopyToDataTable();
resultJosn += @",""children"":[";
for (int j = 0; j < tempdtSjr.Rows.Count; j++)
{
DataRow[] rows = dtGzhf.Select("SjmlID=" + tempdtSjr.Rows[j]["sjmlID"]);
if (rows.Length > 0)
{
if (j == 0)
{
resultJosn += @"{""id"":" + tempdtSjr.Rows[j]["sjmlID"] + @",""name"":""" + tempdtSjr.Rows[j]["sjmlName"] + @""",""checked"":true }";
}
else
{
resultJosn += @",{""id"":" + tempdtSjr.Rows[j]["sjmlID"] + @",""name"":""" + tempdtSjr.Rows[j]["sjmlName"] + @""",""checked"":true }";
}
}
else
{
if (j == 0)
{
resultJosn += @"{""id"":" + tempdtSjr.Rows[j]["sjmlID"] + @",""name"":""" + tempdtSjr.Rows[j]["sjmlName"] + @""" }";
}
else
{
resultJosn += @",{""id"":" + tempdtSjr.Rows[j]["sjmlID"] + @",""name"":""" + tempdtSjr.Rows[j]["sjmlName"] + @""" }";
}
}
}
resultJosn += "]";
}
resultJosn += "}";
}
resultJosn += "]";
}
return(resultJosn);
}
SqlParameterCollection CreateSqlParameters(IProcedure procedure)
{
SqlParameterCollection col = new SqlParameterCollection();
SqlParameter par = null;
foreach (var element in procedure.Items) {
DbType dbType = TypeHelpers.DbTypeFromStringRepresenation(element.DataType);
par = new SqlParameter(element.Name,dbType,"",ParameterDirection.Input);
if (element.ParameterMode == ParameterMode.In) {
par.ParameterDirection = ParameterDirection.Input;
} else if (element.ParameterMode == ParameterMode. InOut){
par.ParameterDirection = ParameterDirection.InputOutput;
}
col.Add(par);
}
return col;
}
public void ValidateSqlQuerySpecSerializer()
{
List <SqlQuerySpec> sqlQuerySpecs = new List <SqlQuerySpec>();
sqlQuerySpecs.Add(new SqlQuerySpec()
{
QueryText = "SELECT root._rid, [{\"item\": root[\"NumberField\"]}] AS orderByItems, root AS payload\nFROM root\nWHERE (true)\nORDER BY root[\"NumberField\"] DESC"
});
sqlQuerySpecs.Add(new SqlQuerySpec()
{
QueryText = "Select * from something"
});
sqlQuerySpecs.Add(new SqlQuerySpec()
{
QueryText = "Select * from something",
Parameters = new SqlParameterCollection()
});
SqlParameterCollection sqlParameters = new SqlParameterCollection();
sqlParameters.Add(new SqlParameter("@id", "test1"));
sqlQuerySpecs.Add(new SqlQuerySpec()
{
QueryText = "Select * from something",
Parameters = sqlParameters
});
sqlParameters = new SqlParameterCollection();
sqlParameters.Add(new SqlParameter("@id", "test2"));
sqlParameters.Add(new SqlParameter("@double", 42.42));
sqlParameters.Add(new SqlParameter("@int", 9001));
sqlParameters.Add(new SqlParameter("@null", null));
sqlParameters.Add(new SqlParameter("@datetime", DateTime.UtcNow));
sqlQuerySpecs.Add(new SqlQuerySpec()
{
QueryText = "Select * from something",
Parameters = sqlParameters
});
CosmosJsonDotNetSerializer userSerializer = new CosmosJsonDotNetSerializer();
CosmosJsonDotNetSerializer propertiesSerializer = new CosmosJsonDotNetSerializer();
CosmosSerializer sqlQuerySpecSerializer = CosmosSqlQuerySpecJsonConverter.CreateSqlQuerySpecSerializer(
userSerializer,
propertiesSerializer);
foreach (SqlQuerySpec sqlQuerySpec in sqlQuerySpecs)
{
Stream stream = propertiesSerializer.ToStream <SqlQuerySpec>(sqlQuerySpec);
string result1;
using (StreamReader sr = new StreamReader(stream))
{
result1 = sr.ReadToEnd();
Assert.IsNotNull(result1);
}
stream = sqlQuerySpecSerializer.ToStream(sqlQuerySpec);
string result2;
using (StreamReader sr = new StreamReader(stream))
{
result2 = sr.ReadToEnd();
Assert.IsNotNull(result2);
}
Assert.AreEqual(result1, result2);
}
}
/// <summary>
/// 数据添加和修改函数的统一参数填充
/// </summary>
/// <param name="myParameters"></param>
/// <param name="myEnum_zwjKindofUpdate"></param>
public void ParametersAdd(SqlParameterCollection myParameters, Enum_zwjKindofUpdate myEnum_zwjKindofUpdate)
{
this.myClass_BelongUnit.ParametersAdd(myParameters, "Welder");
myParameters.Add("@KindofEmployer", SqlDbType.NVarChar, 20).Value = this.KindofEmployer;
myParameters.Add("@IdentificationCard", SqlDbType.NChar, 18).Value = this.IdentificationCard;
myParameters.Add("@WelderName", SqlDbType.NVarChar, 10).Value = this.WelderName;
myParameters.Add("@Schooling", SqlDbType.NVarChar, 20).Value = this.Schooling;
myParameters.Add("@Sex", SqlDbType.NChar, 1).Value = this.Sex;
myParameters.Add("@WeldingBeginning", SqlDbType.DateTime).Value = this.WeldingBeginning;
myParameters.Add("@WelderRemark", SqlDbType.NVarChar, 255).Value = this.WelderRemark;
myParameters.Add("@Postcode", SqlDbType.NVarChar, 10).Value = this.Postcode;
myParameters.Add("@WelderAddress", SqlDbType.NVarChar, 255).Value = this.WelderAddress;
myParameters.Add("@WelderTel", SqlDbType.NVarChar, 255).Value = this.WelderTel;
myParameters.Add("@WeldingExperiences", SqlDbType.NVarChar, 255).Value = this.WeldingExperiences;
myParameters.Add("@KindofEmployerWelderID", SqlDbType.Int).Direction = ParameterDirection.InputOutput;
switch (myEnum_zwjKindofUpdate)
{
case Enum_zwjKindofUpdate.Add:
myParameters.Add("@KindofUpdate", SqlDbType.NVarChar, 20).Value = Enum_zwjKindofUpdate.Add.ToString();
break;
case Enum_zwjKindofUpdate.Modify:
myParameters["@KindofEmployerWelderID"].Value = this.KindofEmployerWelderID;
myParameters.Add("@KindofUpdate", SqlDbType.NVarChar, 20).Value = Enum_zwjKindofUpdate.Modify.ToString();
break;
}
}
public SimpleFileModel AddFile(int systemID, string moduleCode, string categoryCode, string masterID, string fileName, string fileType, byte[] fileContent, FileState fileState)
{
if (systemID <= 0)
{
throw new ArgumentException("参数 systemID 不能小于零！");
}
if (moduleCode.IsEmpty())
{
throw new ArgumentException("参数 moduleCode 不能为空！");
}
if (categoryCode.IsEmpty())
{
throw new ArgumentException("参数 categoryCode 不能为空！");
}
if (masterID.IsEmpty())
{
throw new ArgumentException("参数 masterID 不能为空！");
}
if (fileName.IsEmpty())
{
throw new ArgumentException("参数 fileName 不能为空！");
}
if (fileType.IsEmpty())
{
throw new ArgumentException("参数 fileType 不能为空！");
}
if (fileContent == null || fileContent.Length <= 0)
{
throw new ArgumentException("参数 fileContent 不能为空！");
}
string sql = "select isnull(max(fileid),0) from g_businessfile";
SimpleFileModel sfm = new SimpleFileModel();
sfm.FileID = DB.ExeSqlForObject(sql, null).ToInt32(0) + 1;
sfm.SystemID = systemID;
sfm.ModuleCode = moduleCode;
sfm.CategoryCode = categoryCode;
sfm.MasterID = masterID;
sfm.FileName = fileName;
sfm.FileType = fileType;
sfm.FileSize = fileContent.Length;
sfm.FilePath = "";
sfm.CreateDateTime = DateTime.Now;
sfm.FileStatus = fileState;
SqlParameterCollection spc = DB.CreateSqlParameterCollection();
spc.Add("@FileID", sfm.FileID);
spc.Add("@SystemID", sfm.SystemID);
spc.Add("@ModuleCode", sfm.ModuleCode);
spc.Add("@CategoryCode", sfm.CategoryCode);
spc.Add("@MasterID", sfm.MasterID);
spc.Add("@FileName", sfm.FileName);
spc.Add("@FileType", sfm.FileType);
spc.Add("@FileSize", sfm.FileSize);
spc.Add("@FilePath", sfm.FilePath);
spc.Add("@FileContent", fileContent);
spc.Add("@CreateDateTime", sfm.CreateDateTime);
spc.Add("@Status", (int)fileState);
if (this.IsUseInnerTransaction)
{
DB.BeginTransaction();
}
try
{
sql = @"insert into g_BusinessFile(FileID,SystemID,ModuleCode,CategoryCode,MasterID,
FileName,FileType,FileSize,FileContent,FilePath,CreateDateTime,Status)
values(@FileID,@SystemID,@ModuleCode,@CategoryCode,@MasterID,
@FileName,@FileType,@FileSize,@FileContent,@FilePath,@CreateDateTime,@Status)";
// sql = @"insert into g_BusinessFile(SystemID,ModuleCode,CategoryCode,MasterID,
//FileName,FileType,FileSize,FileContent,FilePath,CreateDateTime,Status)
//values(@SystemID,@ModuleCode,@CategoryCode,@MasterID,
//@FileName,@FileType,@FileSize,@FileContent,@FilePath,@CreateDateTime,@Status)";
DB.ExecuteNonQuerySql(sql, spc);
if (this.IsUseInnerTransaction)
{
DB.CommitTransaction();
}
}
catch
{
if (this.IsUseInnerTransaction)
{
DB.RollbackTransaction();
}
throw;
}
return(sfm);
}
//METODO PARA ADICIONAR PARAMETRO A COLEÇÃO
public void addSqlParameterCollection(String nomeParametro, object valorParametro)
{
sqlParameterCollection.Add(new SqlParameter(nomeParametro, valorParametro));
}
protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack)
{
int numRecords = Convert.ToInt32(this.cnt.Text);
int batchSize = Convert.ToInt32(this.sz.Text);
int numBatches = numRecords / batchSize;
StringBuilder sb = new StringBuilder();
string sql = "EXEC [Traffic].[AddPageView] @pvid{0} out, @userid{0}, @pvurl{0};";
for (int i = 0; i < batchSize; i++)
{
sb.AppendFormat(sql, i);
}
string query = sb.ToString();
using (SqlConnection conn = new SqlConnection(ConnString))
{
conn.Open();
conn.StatisticsEnabled = true;
SqlParameterCollection p = null;
for (int j = 0; j < numBatches; j++)
{
using (TransactionScope scope = new TransactionScope())
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.EnlistTransaction(Transaction.Current);
p = cmd.Parameters;
Guid userId = Guid.NewGuid();
for (int i = 0; i < batchSize; i++)
{
p.Add("pvid" + i, SqlDbType.BigInt).Direction =
ParameterDirection.Output;
p.Add("userid" + i, SqlDbType.UniqueIdentifier).Value = userId;
p.Add("pvurl" + i, SqlDbType.VarChar, 256).Value =
"http://www.12titans.net/test.aspx";
}
try
{
cmd.ExecuteNonQuery();
scope.Complete();
}
catch (SqlException ex)
{
EventLog.WriteEntry("Application",
"Error in WritePageView: " + ex.Message + "\n",
EventLogEntryType.Error, 101);
}
}
}
}
StringBuilder result = new StringBuilder();
result.Append("Last pvid = ");
result.Append(p["pvid" + (batchSize - 1)].Value);
result.Append("<br/>");
IDictionary dict = conn.RetrieveStatistics();
foreach (string key in dict.Keys)
{
result.Append(key);
result.Append(" = ");
result.Append(dict[key]);
result.Append("<br/>");
}
this.info.Text = result.ToString();
}
}
}
public BusinessLog AppendLog(BusinessLog businessLog)
{
SqlParameterCollection spc = DB.CreateSqlParameterCollection();
spc.Add("@SystemName", businessLog.SystemName);
spc.Add("@ModuleName", businessLog.ModuleName);
spc.Add("@CategoryName", businessLog.CategoryName);
spc.Add("@Operation", businessLog.Operation);
spc.Add("@KeyString", businessLog.KeyString);
spc.Add("@PriorStatus", businessLog.PriorStatus);
spc.Add("@PostStatus", businessLog.PostStatus);
spc.Add("@MessageInfo", businessLog.MessageInfo);
spc.Add("@OperatorID", businessLog.OperatorID);
spc.Add("@OperatorName", businessLog.OperatorName);
businessLog.OperateDateTime = DateTime.Now;
spc.Add("@OperateDateTime", businessLog.OperateDateTime);
string sql = "insert into g_BusinessLog(SystemName, ModuleName, CategoryName, KeyString, Operation, PriorStatus, PostStatus, MessageInfo, OperatorID, OperatorName, OperateDateTime)"
+ "values(@SystemName, @ModuleName, @CategoryName, @KeyString, @Operation, @PriorStatus, @PostStatus, @MessageInfo, @OperatorID, @OperatorName, @OperateDateTime)";
DB.ExecuteNonQuerySql(sql, spc);
businessLog.LogID = DB.ExeSqlForObject("select max(LogID) from g_BusinessLog", null).ToInt64();
return(businessLog);
}
public static SqlParameter AddUdtOutput(this SqlParameterCollection spc, string name, string udtname)
{
var param = SqlParams.UdtOutput(name, udtname);
return(spc.Add(param));
}
public SpeciesServiceStatus Add(string name, string version, string type, string author, string email, string assemblyFullName, byte [] assemblyCode)
{
if (name == null || version == null || type == null || author == null || email == null || assemblyFullName == null || assemblyCode == null)
{
// Special versioning case, if all parameters are not specified then we return an appropriate error.
InstallerInfo.WriteEventLog("AddSpecies", "Suspect: " + Context.Request.ServerVariables["REMOTE_ADDR"].ToString());
return(SpeciesServiceStatus.VersionIncompatible);
}
version = new Version(version).ToString(3);
bool nameInappropriate = WordFilter.RunQuickWordFilter(name);
bool authInappropriate = WordFilter.RunQuickWordFilter(author);
bool emailInappropriate = WordFilter.RunQuickWordFilter(email);
bool inappropriate = nameInappropriate | authInappropriate | emailInappropriate;
bool insertComplete = false;
bool allow = !Throttle.Throttled(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"AddSpecies5MinuteThrottle"
);
if (allow)
{
allow = !Throttle.Throttled(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"AddSpecies24HourThrottle"
);
if (!allow)
{
return(SpeciesServiceStatus.TwentyFourHourThrottle);
}
}
else
{
return(SpeciesServiceStatus.FiveMinuteThrottle);
}
try
{
using (SqlConnection myConnection = new SqlConnection(ServerSettings.SpeciesDsn))
{
myConnection.Open();
SqlTransaction transaction = myConnection.BeginTransaction();
SqlCommand mySqlCommand = new SqlCommand("TerrariumInsertSpecies", myConnection, transaction);
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameterCollection cmdParms = mySqlCommand.Parameters;
SqlParameter parmName = cmdParms.Add("@Name", SqlDbType.VarChar, 255); parmName.Value = name;
SqlParameter parmVersion = cmdParms.Add("@Version", SqlDbType.VarChar, 255); parmVersion.Value = version;
SqlParameter parmType = cmdParms.Add("@Type", SqlDbType.VarChar, 50); parmType.Value = type;
SqlParameter parmAuthor = cmdParms.Add("@Author", SqlDbType.VarChar, 255); parmAuthor.Value = author;
SqlParameter parmAuthorEmail = cmdParms.Add("@AuthorEmail", SqlDbType.VarChar, 255); parmAuthorEmail.Value = email;
SqlParameter parmExtinct = cmdParms.Add("@Extinct", SqlDbType.TinyInt, 1); parmExtinct.Value = 0;
SqlParameter parmDateAdded = cmdParms.Add("@DateAdded", SqlDbType.DateTime, 8); parmDateAdded.Value = DateTime.Now;
SqlParameter parmAssembly = cmdParms.Add("@AssemblyFullName", SqlDbType.Text, Int32.MaxValue); parmAssembly.Value = assemblyFullName;
SqlParameter parmBlackListed = cmdParms.Add("@BlackListed", SqlDbType.Bit, 1); parmBlackListed.Value = inappropriate;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
// 2627 is Primary key violation
if (e.Number == 2627)
{
return(SpeciesServiceStatus.AlreadyExists);
}
else
{
throw;
}
}
int introductionWait = (int)ServerSettings.IntroductionWait;
Throttle.AddThrottle(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"AddSpecies5MinuteThrottle",
1,
DateTime.Now.AddMinutes(introductionWait)
);
int introductionDailyLimit = (int)ServerSettings.IntroductionDailyLimit;
Throttle.AddThrottle(
Context.Request.ServerVariables["REMOTE_ADDR"].ToString(),
"AddSpecies24HourThrottle",
introductionDailyLimit,
DateTime.Now.AddHours(24)
);
insertComplete = true;
SaveAssembly(assemblyCode, version, name + ".dll");
transaction.Commit();
}
}
catch (ApplicationException e) {
InstallerInfo.WriteEventLog("AddSpecies", e.ToString());
return(SpeciesServiceStatus.AlreadyExists);
}
catch (Exception e) {
InstallerInfo.WriteEventLog("AddSpecies", e.ToString());
if (insertComplete)
{
RemoveAssembly(version, name);
}
return(SpeciesServiceStatus.ServerDown);
}
if (inappropriate)
{
if (nameInappropriate)
{
return(SpeciesServiceStatus.PoliCheckSpeciesNameFailure);
}
if (authInappropriate)
{
return(SpeciesServiceStatus.PoliCheckAuthorNameFailure);
}
if (emailInappropriate)
{
return(SpeciesServiceStatus.PoliCheckEmailFailure);
}
return(SpeciesServiceStatus.AlreadyExists);
}
else
{
return(SpeciesServiceStatus.Success);
}
}
/// <summary>
/// 数据添加和修改函数的统一参数填充
/// </summary>
/// <param name="myParameters"></param>
/// <param name="myEnum_zwjKindofUpdate"></param>
public void ParametersAdd(SqlParameterCollection myParameters, Enum_zwjKindofUpdate myEnum_zwjKindofUpdate)
{
this.myClass_WeldingParameter.ParametersAdd(myParameters, "Issue");
myParameters.Add("@IssueNo", SqlDbType.NVarChar, 20).Value = this.IssueNo;
//myParameters.Add("@IssueStatus", SqlDbType.Int).Value = Class_DataValidateTool.CouvertuintToint(this.IssueStatus);
myParameters.Add("@IssueStatus", SqlDbType.Int).Value = (int)this.IssueStatus;
myParameters.Add("@SignUpDate", SqlDbType.DateTime).Value = this.SignUpDate;
myParameters.Add("@WeldingProcessAb", SqlDbType.NVarChar, 10).Value = this.WeldingProcessAb;
myParameters.Add("@ShipClassificationAb", SqlDbType.NVarChar, 10).Value = this.ShipClassificationAb;
myParameters.Add("@ShipboardNo", SqlDbType.NVarChar, 10).Value = this.ShipboardNo;
myParameters.Add("@IssueEmployerHPID", SqlDbType.NChar, 4).Value = this.EmployerHPID;
myParameters.Add("@KindofEmployer", SqlDbType.NVarChar, 20).Value = this.KindofEmployer;
myParameters.Add("@IssueWPSNo", SqlDbType.NVarChar, 50).Value = this.IssueWPSNo;
myParameters.Add("@IssueRemark", SqlDbType.NVarChar, 255).Value = this.IssueRemark;
myParameters.Add("@KindofEmployerIssueID", SqlDbType.Int).Direction = ParameterDirection.InputOutput;
switch (myEnum_zwjKindofUpdate)
{
case Enum_zwjKindofUpdate.Add:
myParameters.Add("@KindofUpdate", SqlDbType.NVarChar, 20).Value = Enum_zwjKindofUpdate.Add.ToString();
break;
case Enum_zwjKindofUpdate.Modify:
myParameters["@KindofEmployerIssueID"].Value = this.KindofEmployerIssueID;
myParameters.Add("@KindofUpdate", SqlDbType.NVarChar, 20).Value = Enum_zwjKindofUpdate.Modify.ToString();
break;
}
}
public void AddParametros(String nome, Object valor)
{
Colecao.Add(new SqlParameter(nome, valor));
}
/// <summary>
///
/// </summary>
/// <param name="parameters"></param>
/// <param name="name"></param>
/// <param name="value"></param>
internal void AddSqlParameter(SqlParameterCollection parameters, string name, object value)
{
parameters.Add(new SqlParameter(name, value));
}