| public static ExecuteReader ( string sql ) : System.Data.SqlClient.SqlDataReader | ||
| sql | string | |
| 리턴 | System.Data.SqlClient.SqlDataReader | |
protected override void BeforeStarting()
{
MySqlHelper db = DbHelper.CreateMySqlHelper("host=localhost;database=liba_user;uid=root;pwd=123456;charset=utf8") as MySqlHelper;
using (System.Data.Common.DbDataReader reader = db.ExecuteReader("SELECT uid,uname from users where email='' order by uid asc", System.Data.CommandBehavior.CloseConnection))
{
//and uname REGEXP('^[0-9a-z_-]+$')
while (reader.Read())
{
string uid = reader.GetInt64(0).ToString();
string uname = reader.GetString(1).Trim();
FinalUrl url = Url.CreateFinalUrl(String.Format("http://bbs.sh.libaclub.com/sendpass.php?action=sendme&username1={0}&id={1}", System.Web.HttpUtility.UrlEncode(Encoding.Default.GetBytes(uname)), uid), null) as FinalUrl;
url.UriEscape = false;
if (null != url)
{
url.ContentHandlers.Add(new Libahander2());
this.ManualQueue(url, false);
}
}
}
}
public static IDataReader GetForModuleWithValues(int moduleID, string sortDirection)
{
string sqlCommand = $@"
SELECT i.*, f.Name AS FieldName, v.FieldValue
FROM i7_sflexi_items i
JOIN i7_sflexi_values v ON v.ItemGuid = i.ItemGuid
JOIN i7_sflexi_fields f ON f.FieldGuid = v.FieldGuid
WHERE ModuleID = ?ModuleID
ORDER BY SortOrder {sortDirection};" ;
var sqlParam = new MySqlParameter("?ModuleID", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input, Value = moduleID
};
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand,
sqlParam
));
}
/// <summary>
/// 根据资源内容id查询子资源
/// </summary>
/// <param name="contentId"></param>
/// <returns></returns>
public List <ResourcePartsContract> GetResourceParts(long contentId)
{
string sql = "SELECT * FROM resourceParts WHERE ContentId = @ContentId";
var _list = new List <ResourcePartsContract>();
using (var dr = MySqlHelper.ExecuteReader(ReadConnectionString, sql, new MySqlParameter("@ContentId", contentId)))
{
while (dr.Read())
{
_list.Add(new ResourcePartsContract()
{
ContentId = dr.GetInt64("ContentId"),
VersionId = dr.GetInt64("VersionId"),
ResourceType = dr.GetString("ResourceType")
});
}
}
return(_list);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_LetterSendLog table.
/// </summary>
/// <param name="rowID"> rowID </param>
public static IDataReader GetOne(
int rowId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_LetterSendLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowID = ?RowID ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RowID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoCountry table.
/// </summary>
/// <param name="countryISOCode2"> countryISOCode2 </param>
public static IDataReader GetByISOCode2(string countryISOCode2)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoCountry ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ISOCode2 = ?ISOCode2 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ISOCode2", MySqlDbType.VarChar, 2);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = countryISOCode2;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Selects all Files for a Player from the mp_MediaFile table.
/// </summary>
/// <param name="trackID">The ID of the Player.</param>
/// <returns>An IDataReader containing the Media File(s) data.</returns>
public static IDataReader SelectByPlayer(int playerId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_MediaFile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID IN (SELECT TrackID FROM mp_MediaTrack WHERE PlayerID = ?PlayerID) ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?PlayerID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetHistoryByModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SharedFilesHistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = ?ModuleID ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the sts_WebFormQuestion table.
/// </summary>
/// <param name="guid"> guid </param>
public static IDataReader GetOne(Guid guid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_WebFormQuestion ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
return(MySqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_SavedQuery table.
/// </summary>
/// <param name="name"> name </param>
public static IDataReader GetOne(string name)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SavedQuery ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Name = ?Name ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?Name", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = name;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader SelectByUser(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("UserGuid = ?UserGuid ");
sqlCommand.Append("ORDER BY PageOrder ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetByUser(string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserClaims ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("UserId = ?UserId ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?UserId", MySqlDbType.VarChar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public Question getQuestion(int questionid)
{
Question q = null;
string sql = "SELECT q_name,c_a,c_b,c_c,c_d,hardlevel,score,answer FROM question WHERE questionid='" + questionid + "';";
MySql.Data.MySqlClient.MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sql, null);
if (dr.Read())
{
q = new Question();
q.questionid = questionid;
q.q_name = dr[0].ToString();
q.c_a = dr[1].ToString();
q.c_b = dr[2].ToString();
q.c_c = dr[3].ToString();
q.c_d = dr[4].ToString();
q.hardlevel = int.Parse(dr[5].ToString());
q.score = int.Parse(dr[6].ToString());
q.answer = dr[7].ToString();
}
return(q);
}
public static IDataReader GetLinks(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Links ");
sqlCommand.Append("WHERE ModuleID = ?ModuleID ");
sqlCommand.Append("ORDER BY ViewOrder, Title ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetModuleDefinition(
Guid featureGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ModuleDefinitions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?FeatureGuid ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = featureGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader SchemaVersionGetSchemaVersion(
Guid applicationId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SchemaVersion ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ApplicationID = ?ApplicationID ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ApplicationID", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = applicationId.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static List <Api.Domain.User> GetAllUsers(string connectionString)
{
List <Api.Domain.User> users = new List <Api.Domain.User>();
using (DbDataReader reader = MySqlHelper.ExecuteReader(connectionString, "SELECT * FROM user"))
{
while (reader.Read())
{
Api.Domain.User user = new Api.Domain.User(
reader.GetInt32("id"),
reader.GetString("firstname"),
reader.GetString("lastname"),
reader.GetString("email"),
reader.GetBoolean("global_admin") ? RoleType.Admin : RoleType.Standard
);
users.Add(user);
}
}
return(users);
}
public static IDataReader Get(Guid itemGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM ws_OrderOffers ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemGuid = ?ItemGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ItemGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public string GetReportTemplate()
{
string strdata = string.Empty;
string query = "SELECT id, data FROM tt_ifs_data where id=303;";
//using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connectionLTE_reference, query))
using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connectionLTE_Ericsson, query))
{
// Check if the reader returned any rows
if (reader.HasRows)
{
while (reader.Read())
{
strdata = System.Text.Encoding.UTF8.GetString((byte[])reader["data"]);
}
}
}
return(strdata);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_PaymentLog table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetOne(Guid rowGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_PaymentLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowGuid = ?RowGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the sts_ga_ApiQueryDefinition table.
/// </summary>
/// <param name="queryDefId"> queryDefId </param>
public static IDataReader GetOne(Guid queryDefId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_ga_ApiQueryDefinition ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QueryDefId = ?QueryDefId ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?QueryDefId", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = queryDefId.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_ContentTemplate table.
/// </summary>
public static IDataReader GetAll(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ContentTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static DataTable GetBySite(int siteId)
{
DataTable dt = new DataTable();
dt.Columns.Add("UrlID", typeof(int));
dt.Columns.Add("FriendlyUrl", typeof(string));
dt.Columns.Add("RealUrl", typeof(string));
dt.Columns.Add("IsPattern", typeof(bool));
StringBuilder sqlCommand = new StringBuilder();
MySqlParameter[] arParams = new MySqlParameter[1];
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_FriendlyUrls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = ?SiteID ORDER BY FriendlyUrl ;");
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
using (IDataReader reader = MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams))
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["UrlID"] = reader["UrlID"];
row["FriendlyUrl"] = reader["FriendlyUrl"];
row["RealUrl"] = reader["RealUrl"];
row["IsPattern"] = reader["IsPattern"];
dt.Rows.Add(row);
}
}
return(dt);
}
public List <Sjqd_StatUsersByISP> GetStatUsersByISP(DateTime begintime, DateTime endtime, int softid,
int platform, int period, string ispName, string netmode)
{
string sql = string.Format(@"select statdate,
?softid softid,?period period ,?platform platform,SUM(usercount) usercount,
?netmode netmode,?ispname IspName
from U_StatUsersByISP A
inner join Cfg_ISP B
on A.IspID=B.id
and A.softid=?softid {0} and A.statdate between ?begintime and ?endtime {1}
and A.period=?period
inner join Cfg_NetModes C
on C.ID=A.netmode {2}
group by statdate
order by statdate desc",
platform == 0 ? "" : " and A.platform=?platform ",
ispName == "所有运营商" ? "" : " and B.E_Name=?ispname ",
netmode == "所有网络" ? "" : " and C.E_Name=?netmode ");
MySqlParameter[] paras =
{
new MySqlParameter("?begintime", begintime.ToString("yyyyMMdd")),
new MySqlParameter("?endtime", endtime.ToString("yyyyMMdd")),
new MySqlParameter("?netmode", netmode),
new MySqlParameter("?ispname", ispName),
new MySqlParameter("?platform", platform),
new MySqlParameter("?softid", softid),
new MySqlParameter("?period", period)
};
List <Sjqd_StatUsersByISP> result = new List <Sjqd_StatUsersByISP>();
using (var reader = MySqlHelper.ExecuteReader(Mysql_Statdb_Connstring, sql, paras))
{
while (reader.Read())
{
result.Add(new Sjqd_StatUsersByISP(reader));
}
}
return(result);
}
public List <MarketplaceInventoryUpdateItem> GetProductsByCompany(int companyId)
{
var results = new List <MarketplaceInventoryUpdateItem>();
var parameters = new Dictionary <string, object>
{
{ "@CompanyId", companyId }
};
using (var conn = new MySqlConnection(_inventoryConnectionString))
{
var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text,
@"SELECT
(@RowNumber := CASE WHEN @EisSKU = p.EisSKU THEN @RowNumber + 1 ELSE 1 END) AS RowNumber,
(@EisSKU := p.EisSKU) AS EisSKU,
vp.Quantity,
pa.LeadtimeShip,
v.SafeQty
FROM products p
INNER JOIN productamazONs pa ON pa.EisSKU = p.EisSKU
INNER JOIN companies c ON c.Id = p.CompanyId
INNER JOIN vendorproductlinks l ON l.EisSKU = p.EisSKU
INNER JOIN vendorproducts vp ON vp.EisSupplierSKU = l.EisSupplierSKU
INNER JOIN vendors v ON v.Id = vp.VendorId
WHERE c.Id = @CompanyId AND vp.Quantity > 0 AND p.IsBlacklisted = 0 AND pa.IsEnabled = 1
HAVING RowNumber = 1
ORDER BY p.EisSKU", parameters);
while (reader.Read())
{
var item = new MarketplaceInventoryUpdateItem();
item.SKU = reader[1].ToString();
item.Quantity = (int)reader[2];
item.LeadtimeShip = reader[3] == DBNull.Value ? 3 : Convert.ToInt32(reader[3]);
item.SafeQty = reader[4] as int?;
results.Add(item);
}
}
return(results);
}
// Get folder structure data
public List <IFSDataModel> GetIFSData()
{
List <IFSDataModel> lstIFSData = new List <IFSDataModel>();
//string query = "select id, parent_id, name from tt_ifs where type = 0;"; // get directories only not files
//string query = "select id, parent_id, name, type, data_id from tt_ifs;";
string query = "select ifs.id, ifs.parent_id, ifs.name, ifs.type, ifs.data_id, ifsdata.data " +
" from tt_ifs ifs " +
" left join tt_ifs_data ifsdata on ifs.data_id = ifsdata.id;";
// Create an instance of the User class
IFSDataModel iFSDataModel;
//using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connectionLTE_reference, query))
using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connectionLTE_Ericsson, query))
{
// Check if the reader returned any rows
if (reader.HasRows)
{
while (reader.Read())
{
iFSDataModel = new IFSDataModel();
iFSDataModel.id = Convert.ToString(reader["id"]);
iFSDataModel.parentid = Convert.ToString(reader["parent_id"]);
iFSDataModel.text = Convert.ToString(reader["name"]);
iFSDataModel.isDirectory = !Convert.ToBoolean(reader["type"]);
iFSDataModel.data_id = Convert.ToString(reader["data_id"]);
if (!(reader["data"] is System.DBNull))
{
iFSDataModel.template = System.Text.Encoding.UTF8.GetString((byte[])reader["data"]);
}
lstIFSData.Add(iFSDataModel);
}
}
}
return(lstIFSData);
}
/// <summary>
/// Gets
/// </summary>
/// <param name="siteGuid"></param>
/// <param name="pageId"></param>
/// <returns></returns>
public static IDataReader GetByCMSPage(Guid siteGuid, int pageId)
{
const string sqlCommand = @"
SELECT
i.ModuleID as moduleId,
i.ItemGuid as itemGuid,
i.ItemID as itemId,
i.SortOrder as sortOrder,
i.CreatedUtc as createdUtc,
m.ModuleTitle as moduleTitle,
m.ViewRoles as moduleViewRoles,
i.ViewRoles as itemViewRoles,
i.EditRoles as itemEditRoles,
pm.PublishBeginDate as publishBeginDate,
pm.PublishEndDate as publishEndDate
FROM i7_sflexi_items i
JOIN mp_PageModules pm on i.ModuleGuid = pm.ModuleGuid
JOIN mp_Modules m on i.ModuleGuid = m.Guid
WHERE i.SiteGuid = ?SiteGuid
AND pm.PageID = ?PageID
ORDER BY SortOrder ASC;" ;
var sqlParams = new List <MySqlParameter>
{
new MySqlParameter("?SiteGuid", MySqlDbType.Guid)
{
Direction = ParameterDirection.Input, Value = siteGuid
},
new MySqlParameter("?PageID", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input, Value = pageId
}
};
return(MySqlHelper.ExecuteReader(
ConnectionString.GetWriteConnectionString(),
sqlCommand,
sqlParams.ToArray()
));
}
public static IDataReader GetSharedFile(int itemId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("ItemID, ");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("UploadUserID, ");
sqlCommand.Append("FriendlyName, ");
sqlCommand.Append("OriginalFileName, ");
sqlCommand.Append("ServerFileName, ");
sqlCommand.Append("SizeInKB, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("FolderID, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("FolderGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("DownloadCount, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("ViewRoles ");
sqlCommand.Append("FROM mp_SharedFiles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemID = ?ItemID ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input,
Value = itemId
};
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public List <clsProductinfo> findProductr(string findtext)
{
MySql.Data.MySqlClient.MySqlDataReader reader = MySqlHelper.ExecuteReader(findtext);
List <clsProductinfo> ClaimReport_Server = new List <clsProductinfo>();
while (reader.Read())
{
clsProductinfo item = new clsProductinfo();
item.Product_id = reader.GetInt32(0);
if (reader.GetValue(1) != null && Convert.ToString(reader.GetValue(1)) != "")
{
item.Product_no = reader.GetString(1);
}
if (reader.GetValue(2) != null && Convert.ToString(reader.GetValue(2)) != "")
{
item.Product_name = reader.GetString(2);
}
if (reader.GetValue(3) != null && Convert.ToString(reader.GetValue(3)) != "")
{
item.Product_salse = reader.GetString(3);
}
if (reader.GetValue(4) != null && Convert.ToString(reader.GetValue(4)) != "")
{
item.Product_address = reader.GetString(4);
}
if (reader.GetString(5) != null && reader.GetString(5) != "")
{
item.Input_Date = Convert.ToDateTime(reader.GetString(5));
}
ClaimReport_Server.Add(item);
//这里做数据处理....
}
return(ClaimReport_Server);
}
public static IDataReader GetByExtra(Guid siteGuid, Guid extraGuid)
{
const string sqlCommand =
@"SELECT
ti.TagItemGuid,
ti.RelatedItemGuid,
ti.SiteGuid,
ti.FeatureGuid,
ti.ModuleGuid,
ti.TagGuid,
ti.ExtraGuid,
ti.TaggedBy,
t.Tag AS TagText
FROM mp_TagItem ti
INNER JOIN mp_Tag t
ON ti.TagGuid = t.Guid
WHERE ExtraGuid = ?ExtraGuid
AND ti.SiteGuid = ?SiteGuid
ORDER BY TagText" ;
var arParams = new List <MySqlParameter>
{
new MySqlParameter("?ExtraGuid", MySqlDbType.VarChar, 36)
{
Direction = ParameterDirection.Input,
Value = extraGuid.ToString()
},
new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36)
{
Direction = ParameterDirection.Input,
Value = siteGuid.ToString()
}
}.ToArray();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand,
arParams
));
}
public static IDataReader GetByItemGuids(List <Guid> itemGuids)
{
// Note: The "WHERE IN" query was busted, and I couldn't figure out how to get it working,
// so I made it build the query manually.
//
// This was how the broken command was rendering: SELECT * FROM i7_sflexi_values WHERE IN ('\'guid1'\','\'guid2'\')
// TODO: Find out how to make "WHERE IN" work
var sqlCommand = $"SELECT * FROM `i7_sflexi_values` WHERE {getItems()};";
string getItems()
{
return(string.Join(" ", itemGuids.Select((x, i) =>
{
var item = $"`ItemGuid` = ?ItemGuid{i}";
if (i < itemGuids.Count() - 1)
{
item += " OR";
}
return item;
})));
}
var sqlParams = new List <MySqlParameter>();
for (var i = 0; i < itemGuids.Count(); i++)
{
sqlParams.Add(new MySqlParameter($"?ItemGuid{i}", MySqlDbType.Guid)
{
Direction = ParameterDirection.Input, Value = itemGuids[i]
});
}
return(MySqlHelper.ExecuteReader(
ConnectionString.GetWriteConnectionString(),
sqlCommand,
sqlParams.ToArray()
));
}
/// <summary>
/// 发送OpenShop中的手机短信
/// </summary>
private static void SendPending()
{
string strMyString = ConfigHelper.ConnectionString.MySqlString;
if (String.IsNullOrEmpty(strMyString))
{
EventLog(string.Format("MySQL 连接字符串无效"));
return;
}
try
{
MySqlHelper oMySqlHelper = new MySqlHelper(strMyString);
EucpHelper oEucpHelper = new EucpHelper();
int nCounter = 0;
// 发送手机短信
string sPending = "SELECT * FROM system_message_queue WHERE status = 1";
Dictionary<int, int> oSendList = new Dictionary<int, int>();
using (MySqlDataReader oDataReader = oMySqlHelper.ExecuteReader(sPending))
{
if (oDataReader.HasRows)
{
while (oDataReader.Read())
{
int nMsgID = (int)oDataReader["id"];
string strMsgType = oDataReader["msg_type"].ToString().Trim();
string strMobile = oDataReader["target_ids"].ToString();
string strContent = oDataReader["msg_content"].ToString();
string strMsgName = oDataReader["msg_name"].ToString();
string strOrderSn = oDataReader["order_sn"].ToString();
int nResult = -1;
if (strMsgType == "1") // 发送邮件
{
nResult = MailHelper.SendMail(ConfigHelper.SmtpConfig.Sender, ConfigHelper.SmtpConfig.From,
strMobile, "筑巢家居商城订单号：" + strOrderSn, strContent);
}
else if (strMsgType == "2") // 发送短信
{
strContent = strContent.Replace("<p>", "");
strContent = strContent.Replace("</p>", "");
nResult = oEucpHelper.SendSms(strMobile, strContent);
}
oSendList.Add(nMsgID, nResult);
nCounter++;
}
}
oDataReader.Close();
}
// 检测余额
string strBalance = oEucpHelper.GetBalance().ToString();
// 更新已发送状态
foreach (int nKey in oSendList.Keys)
{
string strUpdateStatus = "UPDATE system_message_queue SET status = 3, msg_desc = '" + oSendList[nKey].ToString() + ":" + strBalance + "', send_time = NOW() WHERE id = " + nKey.ToString();
oMySqlHelper.ExecuteNonQuery(strUpdateStatus);
}
EventLog(string.Format("已发送{0}条手机短信/邮件", nCounter));
nCounter = 0;
// 接收手机短信
List<object> oGetList = oEucpHelper.ReceiveSms();
foreach (Dictionary<string, object> oMessage in oGetList)
{
string strInsertMessage = "INSERT INTO system_message_receive (mobileNumber, smsContent, sentTime, channelNumber) " +
" VALUES ('" + oMessage["Mobile"].ToString() + "', '" + oMessage["Content"].ToString() + "', '" +
oMessage["SentTime"].ToString() + "', '" + oMessage["Channel"].ToString() + "')";
oMySqlHelper.ExecuteNonQuery(strInsertMessage);
nCounter++;
}
EventLog(string.Format("已接收{0}条手机短信", nCounter));
}
catch (Exception ex)
{
EventLog(string.Format("SendPending 出现错误 {0}", ex.Message));
}
}
