public Dictionary <int, LanguageStat> GetAudioLanguageStatsForAnime()
{
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, lan.LanguageName ";
command.CommandText += "FROM AnimeSeries ser ";
command.CommandText += "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText +=
"INNER JOIN CrossRef_Languages_AniDB_File audio on audio.FileID = anifile.FileID ";
command.CommandText += "INNER JOIN Language lan on audio.LanguageID = lan.LanguageID ";
command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, lan.LanguageName ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int animeID = int.Parse(rdr[0].ToString());
string mainTitle = rdr[1].ToString().Trim();
string lanName = rdr[2].ToString().Trim();
if (animeID == 7656)
{
Debug.Print("");
}
if (!dictStats.ContainsKey(animeID))
{
LanguageStat stat = new LanguageStat();
stat.AnimeID = animeID;
stat.MainTitle = mainTitle;
stat.LanguageNames = new List <string>();
stat.LanguageNames.Add(lanName);
dictStats[animeID] = stat;
}
else
{
dictStats[animeID].LanguageNames.Add(lanName);
}
}
}
}
return(dictStats);
}
public IEnumerable <ResultClass> Query <ResultClass>(string Query)
{
//Used for select statements
System.Xml.Serialization.XmlSerializer serializer;
//Declare an array of ResultClass which is a template object
ResultClass[] results;
//Open a connection to the database
this.Open();
using (System.Data.IDbCommand command = this._conn.CreateCommand())
{
//Assign the command the query string
command.CommandText = Query;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
using (System.Data.DataSet set = new DataSet("output"))
{
using (System.Data.DataTable table = new DataTable())
{
//load the DataReader
table.Load(reader);
set.Tables.Add(table);
//Set the table name to the result class, without this, the program would not be able to serialize objects that are not from only one table. E.G - Joins
table.TableName = typeof(ResultClass).Name;
using (System.IO.MemoryStream xmlStream = new System.IO.MemoryStream())
{
set.WriteXml(xmlStream);
//Set the start position
xmlStream.Position = 0;
System.Diagnostics.Debug.WriteLine(System.Text.Encoding.Default.GetString(xmlStream.ToArray()));
serializer = new System.Xml.Serialization.XmlSerializer(typeof(ResultClass[]), new System.Xml.Serialization.XmlRootAttribute("output"));
results = ((ResultClass[])serializer.Deserialize(xmlStream));
}
}
}
}
}
//Close the connection to the database
this.Close();
return(results);
}
public static List <dynamic> DynamicSqlQuery(this Database database, string sql, System.Data.Common.DbTransaction transaction = null, params object[] parameters)
{
TypeBuilder builder = createTypeBuilder(
"MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
using (System.Data.IDbCommand command = database.Connection.CreateCommand())
{
try
{
if (database.Connection.State != ConnectionState.Open)
{
database.Connection.Open();
}
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = sql;
command.CommandTimeout = command.Connection.ConnectionTimeout;
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
using (System.Data.IDataReader reader = command.ExecuteReader())
{
var schema = reader.GetSchemaTable();
foreach (System.Data.DataRow row in schema.Rows)
{
string name = (string)row["ColumnName"];
//var a=row.ItemArray.Select(d=>d.)
Type type = (Type)row["DataType"];
if (type != typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
{
type = typeof(Nullable <>).MakeGenericType(type);
}
createAutoImplementedProperty(builder, name, type);
}
}
}
finally
{
database.Connection.Close();
command.Parameters.Clear();
}
}
Type resultType = builder.CreateType();
return(database.SqlQuery(resultType, sql, parameters).Cast <dynamic>().ToList());
}
public static void ExecuteReader(
this System.Data.IDbCommand command,
DbReadHandler handle)
{
LastCommand = command.CommandText;
using (var reader = command.ExecuteReader()) {
if (handle != null)
{
handle(reader);
}
command.Dispose();
}
}
public IDataReader ExecReader(string commandText, IDataParameter[] parameters)
{
Connect();
Logger.I(string.Concat(commandText, "\tPrm:", ParametersToString(parameters)));
comm.Parameters.Clear();
comm.CommandText = commandText;
if (parameters != null)
{
for (int loop = 0; loop < parameters.Length; loop++)
{
comm.Parameters.Add(parameters[loop]);
}
}
return(comm.ExecuteReader());
}
public virtual IDataReader ExecuteDataReader(System.Data.IDbCommand command)
{
try
{
Open();
return(command.ExecuteReader());
}
catch (Exception ex)
{
Stoca.Log.LogManager.GetLog().Error(Stoca.Common.ExceptionManager.EXCEPTION_EXECUTEDATAREADER, ex, command);
throw ex;
}
finally
{
Dispose();
}
}
public IDataReader ExecReader(string commandText, IDataParameter[] parameters)
{
Connect();
Utility.WriteTrace(commandText);
comm.Parameters.Clear();
comm.CommandText = commandText;
if (parameters != null)
{
for (int loop = 0; loop < parameters.Length; loop++)
{
comm.Parameters.Add(parameters[loop]);
}
}
return(comm.ExecuteReader());
}
public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
{
//TypeBuilder builder = createTypeBuilder(
// "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
using (System.Data.IDbCommand command = database.Connection.CreateCommand())
{
try
{
database.Connection.Open();
command.CommandText = sql;
command.CommandTimeout = command.Connection.ConnectionTimeout;
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
using (System.Data.IDataReader reader = command.ExecuteReader())
{
return(GetResult(reader));
//var schema = reader.GetSchemaTable();
//foreach (System.Data.DataRow row in schema.Rows)
//{
// string name = (string)row["ColumnName"];
// //var a=row.ItemArray.Select(d=>d.)
// Type type = (Type)row["DataType"];
// if (type != typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
// {
// type = typeof(Nullable<>).MakeGenericType(type);
// }
// createAutoImplementedProperty(builder, name, type);
//}
}
}
finally
{
database.Connection.Close();
command.Parameters.Clear();
}
}
//Type resultType = builder.CreateType();
//return database.SqlQuery(resultType, sql, parameters);
}
/// <summary>
/// Reads the index into a dictionary with a filename=>MyFile mapping
/// </summary>
/// <returns></returns>
public Dictionary <String, MyFile> GetFiles()
{
// TODO: perhaps keep a running copy in memory so we dont have to fetch from DB?
Dictionary <String, MyFile> files = new Dictionary <String, MyFile>();
DbReader reader = commandGetFiles.ExecuteReader();
while (reader.Read())
{
files.Add(reader["path"].ToString(),
new MyFile(reader["path"].ToString(), char.Parse(reader["type"].ToString()),
long.Parse(reader["modtime"].ToString()), long.Parse(reader["size"].ToString()), reader["checksum"].ToString()));
}
return(files);
}
/// <summary>
/// Get's all the video quality settings (comma separated) that apply to each group
/// </summary>
/// <returns></returns>
public Dictionary <int, HashSet <string> > GetAllVideoQualityByGroup()
{
Dictionary <int, HashSet <string> > allVidQuality = new Dictionary <int, HashSet <string> >();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT ag.AnimeGroupID, anifile.File_Source ";
command.CommandText += "from AnimeGroup ag ";
command.CommandText += "INNER JOIN AnimeSeries ser on ser.AnimeGroupID = ag.AnimeGroupID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID ";
// See Note 1
command.CommandText += "GROUP BY ag.AnimeGroupID, anifile.File_Source ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int groupID = int.Parse(rdr[0].ToString());
string vidQual = rdr[1].ToString().Trim();
HashSet <string> vids;
if (allVidQuality.ContainsKey(groupID))
{
vids = allVidQuality[groupID];
}
else
{
vids = new HashSet <string>(StringComparer.InvariantCultureIgnoreCase);
allVidQuality.Add(groupID, vids);
}
if (!vids.Contains(vidQual))
{
vids.Add(vidQual);
}
}
}
}
return(allVidQuality);
}
public AnimeVideoQualityStat GetEpisodeVideoQualityStatsForAnime(ISessionWrapper session, int aID)
{
AnimeVideoQualityStat stat = new AnimeVideoQualityStat
{
VideoQualityEpisodeCount = new Dictionary <string, int>()
};
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber "
+ "from AnimeSeries ser "
+ "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID "
+ "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID "
+ "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID "
+ "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID "
+ "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash "
+ "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID ";
// See Note 1
command.CommandText += "WHERE aniep.EpisodeType = 1 " // normal episodes only
+ "AND anime.AnimeID = " + aID.ToString()
+
" GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source, aniep.EpisodeNumber ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
stat.AnimeID = int.Parse(rdr[0].ToString());
stat.MainTitle = rdr[1].ToString().Trim();
string vidQual = rdr[2].ToString().Trim();
int epNumber = int.Parse(rdr[3].ToString());
if (!stat.VideoQualityEpisodeCount.ContainsKey(vidQual))
{
stat.VideoQualityEpisodeCount[vidQual] = 1;
}
else
{
stat.VideoQualityEpisodeCount[vidQual]++;
}
}
}
return(stat);
}
public void Transaction_Test()
{
//Add a table, insert some data, and then crash.
//this *should* roll back the transaction entirely
string query = Mother.AddTable();
query += "\n";
query += Mother.InsertData();
query += "\n";
query += Mother.MalformedScript();
var scriptRunner = new SqlScriptRunner.ScriptRunner(query, new ScriptProcessing.SqliteScriptProcessor());
using (var connection = new SQLiteConnection(Mother.ConnectionString()))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
var success = scriptRunner.Execute(connection, transaction);
if (success)
{
transaction.Commit();
}
}
catch (Exception)
{
transaction.Rollback();
}
}
}
//Now that the transaction has failed, lets assert
//make sure that the data is actually in the DB now...
using (var connection = new SQLiteConnection(Mother.ConnectionString()))
{
connection.Open();
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = Mother.SelectData();
//this should throw our sqliteexception
var data = cmd.ExecuteReader();
}
}
/// <summary>
/// Get's all the video quality settings (comma separated) that apply to each group
/// </summary>
/// <returns></returns>
public Dictionary <int, string> GetAllVideoQualityByAnime()
{
Dictionary <int, string> allVidQuality = new Dictionary <int, string>();
using (var session = JMMService.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, anifile.File_Source ";
command.CommandText += "FROM AnimeSeries ser ";
command.CommandText += "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1
command.CommandText += "GROUP BY anime.AnimeID, anime.MainTitle, anifile.File_Source ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int groupID = int.Parse(rdr[0].ToString());
string vidQual = rdr[2].ToString().Trim();
if (!allVidQuality.ContainsKey(groupID))
{
allVidQuality[groupID] = "";
}
if (allVidQuality[groupID].Length > 0)
{
allVidQuality[groupID] += ",";
}
allVidQuality[groupID] += vidQual;
}
}
}
return(allVidQuality);
}
public Dictionary <int, LanguageStat> GetSubtitleLanguageStatsByAnimeResults(ISession session, int aID)
{
Dictionary <int, LanguageStat> dictStats = new Dictionary <int, LanguageStat>();
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anime.AnimeID, anime.MainTitle, lan.LanguageName ";
command.CommandText += "FROM AnimeSeries ser ";
command.CommandText += "INNER JOIN AniDB_Anime anime on anime.AnimeID = ser.AniDB_ID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID ";
command.CommandText += "INNER JOIN Language lan on subt.LanguageID = lan.LanguageID ";
command.CommandText += "WHERE anime.AnimeID = " + aID.ToString();
command.CommandText += " GROUP BY anime.AnimeID, anime.MainTitle, lan.LanguageName ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int animeID = int.Parse(rdr[0].ToString());
string mainTitle = rdr[1].ToString().Trim();
string lanName = rdr[2].ToString().Trim();
if (!dictStats.ContainsKey(animeID))
{
LanguageStat stat = new LanguageStat();
stat.AnimeID = animeID;
stat.MainTitle = mainTitle;
stat.LanguageNames = new List <string>();
stat.LanguageNames.Add(lanName);
dictStats[animeID] = stat;
}
else
{
dictStats[animeID].LanguageNames.Add(lanName);
}
}
}
return(dictStats);
}
/// <summary>
/// Print a list of the accounts in the database
/// </summary>
public void ShowUsers()
{
Console.WriteLine("== users ==");
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "SELECT id, name FROM users";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader ["name"]);
}
reader.Close();
} catch (Exception) {
//
}
}
protected SqlDataReader ExecuteCommandReader(System.Data.IDbCommand command)
{
lMgr.Connection = LibraryMgr.GetConnection(this.connectionStrType);
lMgr.SetCommandConnection(command);
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
return((SqlDataReader)command.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception exception)
{
command.Connection.Close();
WebUtils.HandleException(exception);
return(null);
}
}
public void AddTableInsertDataByTransactionTest()
{
var ScriptRunner = new SqlScriptRunner.ScriptRunner(Mother.AddTable(),
new ScriptProcessing.SqliteScriptProcessor());
using (var connection = new SQLiteConnection(Mother.ConnectionString()))
{
var success = false;
connection.Open();
using (var t = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
success = ScriptRunner.Execute(t.Connection, t);
t.Commit();
}
catch (Exception)
{
t.Rollback();
throw;
}
}
if (success)
{
ScriptRunner = new ScriptRunner(Mother.InsertData());
bool s = ScriptRunner.Execute(connection);
Assert.IsTrue(s);
//make sure that the data is actually in the DB now...
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = Mother.SelectData();
var data = cmd.ExecuteReader();
Assert.IsNotNull(data);
Assert.IsTrue(data.FieldCount > 0);
data.Read();
Assert.IsTrue(data[0] != null);
Assert.IsNotNull(data[0]);
}
}
Mother.DropTable(Mother.ConnectionString());
}
/// <summary>
/// Persists data to and from the specified excel file
/// </summary>
/// <param name="excelFilepath">The excel file that should be read into memory</param>
public ExcelPersister(string excelFilepath)
{
if (!System.IO.File.Exists(excelFilepath))
{
throw new System.IO.FileNotFoundException(excelFilepath);
}
// keep a pointer to the excel file
_ExcelFile = excelFilepath;
var table = new System.Data.DataTable("Sheet1");
try
{
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetExcelConnectionString(excelFilepath, true)))
{
using (System.Data.IDbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [Sheet1$]";
connection.Open();
using (System.Data.IDataReader dr = cmd.ExecuteReader())
{
table.Load(dr);
}
}
}
}
catch (Exception ex)
{
if ((ex.Message.IndexOf("Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine", StringComparison.OrdinalIgnoreCase) >= 0))
{
logger.Error("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920", ex);
throw new System.Exception("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920");
}
logger.Error("Problem reading excel file: " + excelFilepath, ex);
throw;
}
// assign the data table before exiting the constructor
this.Data = table;
}
internal List <TotalSoldModel> GetTotalSoldFromDb()
{
totalSold = new List <TotalSoldModel>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.CommandText = storedProc_GetInventoryTotalsByType;
command.CommandType = System.Data.CommandType.StoredProcedure;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
totalSold.Add(MapTotalSoldFromDb(reader));
}
}
}
return(totalSold);
}
internal bool SimulateSaleFromDb()
{
bool success = false;
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.CommandText = storedProc_SimulateSale;
command.CommandType = System.Data.CommandType.StoredProcedure;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
if (reader.RecordsAffected > 0)
{
success = true;
}
}
}
return(success);
}
/// <summary>
/// Gets a list fo all the possible video quality settings for the user e.g. dvd, blu-ray
/// </summary>
/// <returns></returns>
public List <string> GetAllVideoQuality()
{
List <string> allVidQuality = new List <string>();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT Distinct(File_Source) FROM AniDB_File";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
string vidQual = rdr[0].ToString().Trim();
allVidQuality.Add(vidQual);
}
}
}
return(allVidQuality);
}
public List <BatchModel> GetAllBatchesFromDb()
{
batches = new List <BatchModel>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
string text = $"select * from dbo.OnHandInventory";
command.CommandText = text;
command.CommandType = CommandType.Text;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
batches.Add(MapBatchesFromDb(reader));
}
}
}
return(batches);
}
/// <summary>
/// Get an account from the database via a known ID
/// </summary>
/// <param name="id"></param>
/// <returns>null if not found</returns>
public ServerUser GetUserByName(String userName)
{
ServerUser user = null;
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "SELECT * FROM users WHERE name='" + userName + "';";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
user = new ServerUser(reader["id"].ToString(), reader["name"].ToString(), reader["password"].ToString());
}
reader.Close();
}
catch (Exception e) {
Console.WriteLine("There was an error fetching the account " + e.Message);
}
return(user);
}
/// <summary>
/// Get an account from the database via a known ID
/// </summary>
/// <param name="userName"></param>
/// <returns>null if not found</returns>
public ServerUser GetUserByName(String userName)
{
ServerUser account = null;
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "select * from oc_users where uid='" + userName + "';";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
account = new ServerUser(reader["uid"].ToString(), reader["uid"].ToString(), reader["password"].ToString());
}
reader.Close();
}
catch (Exception e) {
Console.WriteLine("There was an error fetching the account " + e.Message);
}
return(account);
}
internal bool CreateBatchRecordFromDb(string nameOf, int?total)
{
batches = new List <BatchModel>();
bool success = false;
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.CommandText = storedProc_CreateBatchRecord;
command.CommandType = System.Data.CommandType.StoredProcedure;
// Add input parameter.
SqlParameter parameterNameOf = new SqlParameter();
parameterNameOf.ParameterName = "@nameOf";
parameterNameOf.SqlDbType = SqlDbType.NVarChar;
parameterNameOf.Direction = ParameterDirection.Input;
parameterNameOf.Value = nameOf;
// Add input parameter.
SqlParameter parameterTotalMade = new SqlParameter();
parameterTotalMade.ParameterName = "@total";
parameterTotalMade.SqlDbType = SqlDbType.Int;
parameterTotalMade.Direction = ParameterDirection.Input;
parameterTotalMade.Value = total;
command.Parameters.Add(parameterNameOf);
command.Parameters.Add(parameterTotalMade);
using (System.Data.IDataReader reader = command.ExecuteReader())
{
if (reader.RecordsAffected > 0)
{
success = true;
}
}
}
return(success);
}
public string[][] GetIndexData(string entityType, string techName, TechCycle cycle, string dataId)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var indexDefine = entity.indexdefinition.FirstOrDefault(p => p.name == techName);
if (indexDefine == null)
{
return(null);
}
var fields = JsonConvert.DeserializeObject <IList <Field> >(indexDefine.fields);
string tableName = "Tech_" + entityType + "_" + indexDefine.table_name + "_" + cycle;
string sql = @"select * from " + tableName + " where f_code='" + dataId + "' order by date desc limit 500";
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql;
IDataReader reader = commond.ExecuteReader();
List <string[]> indexData = new List <string[]>();
while (reader.Read())
{
var data = new List <string>();
data.Add(DateTime.Parse(reader["date"] + "").ToString("yyyyMMdd"));
data.AddRange(fields.Select(filed => reader[filed.name] + ""));
indexData.Add(data.ToArray());
}
entity.Database.Connection.Close();
indexData.Reverse();
return(indexData.ToArray());
}
}
}
/// <summary>
/// 执行查询语句，返回一条记录对应的对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSQL">查询语句</param>
/// <returns></returns>
public T query <T>(string strSQL, params IDataParameter[] iParms) where T : class, new()
{
System.Data.IDbConnection iConn = this.GetConnection();
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, strSQL, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
if (iReader.Read())
{
PropertyInfo[] pis = typeof(T).GetProperties();
T model = new T();
foreach (PropertyInfo pi in pis)
{
pi.SetValue(model, iReader[pi.Name], null);
}
return(model);
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
return(null);
}
public void AddTableInsertDataTemplatedTest()
{
var ScriptRunner = new SqlScriptRunner.ScriptRunner(Mother.AddTable(), new ScriptProcessing.SqliteScriptProcessor());
ScriptRunner.Parameters = new Dictionary <string, string>();
using (var connection = new SQLiteConnection(Mother.ConnectionString()))
{
var success = ScriptRunner.Execute(connection);
if (success)
{
ScriptRunner = new ScriptRunner(Mother.TemplatedInsert(), new ScriptProcessing.SqliteScriptProcessor());
long yValue = Mother.TemplateInteger();
long zValue = Mother.TemplateInteger();
ScriptRunner.Parameters.Add("ZVALUE", zValue.ToString());
ScriptRunner.Parameters.Add("YVALUE", yValue.ToString());
bool s = ScriptRunner.Execute(connection);
Assert.IsTrue(s);
//make sure that the data is actually in the DB now...
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = Mother.SelectDataByYValue(yValue);
var data = cmd.ExecuteReader();
Assert.IsNotNull(data);
Assert.IsTrue(data.FieldCount > 0);
data.Read();
long zValueDB = (long)data[2];
Assert.AreEqual(zValueDB, zValue);
}
}
Mother.DropTable(Mother.ConnectionString());
}
public string GetAllVideoQualityForGroup(int animeGroupID)
{
string vidQuals = "";
using (var session = JMMService.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anifile.File_Source ";
command.CommandText += "from AnimeGroup ag ";
command.CommandText += "INNER JOIN AnimeSeries ser on ser.AnimeGroupID = ag.AnimeGroupID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1
command.CommandText += "where ag.AnimeGroupID = " + animeGroupID.ToString();
command.CommandText += " GROUP BY anifile.File_Source ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
string vidQual = rdr[0].ToString().Trim();
if (vidQuals.Length > 0)
{
vidQuals += ",";
}
vidQuals += vidQual;
}
}
}
return(vidQuals);
}
/// <summary>
/// Gets a list of all the possible subtitle languages
/// </summary>
/// <returns></returns>
public List <string> GetAllUniqueSubtitleLanguages()
{
List <string> allLanguages = new List <string>();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT Distinct(lan.LanguageName) ";
command.CommandText += "FROM CrossRef_Subtitles_AniDB_File subt ";
command.CommandText += "INNER JOIN Language lan on subt.LanguageID = lan.LanguageID ";
command.CommandText += "ORDER BY lan.LanguageName ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
string lan = rdr[0].ToString().Trim();
allLanguages.Add(lan);
}
}
}
return(allLanguages);
}
