public void GetConnectionSettings_CallWithValidConnectionString_ReturnsConnectionStringSettings()
{
IDbConnectionService dbConnectionService = new DbConnectionService(_connectionString);
ConnectionStringSettings result = dbConnectionService.GetConnectionSettings();
Assert.IsTrue(result != null);
}
public static void initializeQuestions(int fragebogenid)
{
DbConnectionService db = (DbConnectionService)Current.Properties["db"];
DataTable dt = db.executeCommand("SELECT * FROM `t_sbf_binnen` join t_fragebogen_unter_maschine on F_Id_SBF_Binnen = p_id where fragebogennr = " + fragebogenid);
List <Question> questions = new List <Question>();
List <Answer> allAnswers = new List <Answer>();
foreach (DataRow row in dt.Rows)
{
var rowHasNullValues = false;
row.ItemArray.ToList().ForEach(obj => rowHasNullValues = obj == DBNull.Value);
if (rowHasNullValues)
{
continue;
}
List <Answer> answers = new List <Answer>();
for (int i = 2; i <= 5; i++)
{
answers.Add(new Answer(row[i].ToString()));
}
answers.ForEach(answer => allAnswers.Add(answer));
answers[Convert.ToInt32(row["RichtigeAntwort"]) - 1].Set_Right();
questions.Add(new Question(Convert.ToInt32((row["P_Id"])), row["Frage"].ToString(), answers));
}
SharedDataController.sharedData.Questions = questions;
SharedDataController.sharedData.Answers = allAnswers;
}
public async Task <int> insert(DbConnectionService dbcs)
{
string sql = "INSERT INTO ArticleContents VALUES(:Id, :FileName, :ContentType, :ArticleId, :CreatedAt, :UpdatedAt)";
SequenceService seq = new SequenceService();
var Id = seq.getNextVal(SequenceService.seqType.ArticleContents);
var now = DateTime.Now;
var result = await dbcs.GetConnection().ExecuteAsync(sql, new
{
Id = Id
,
FileName = this.FileName
,
ContentType = this.ContentType
,
ArticleId = this.ArticleId
,
CreatedAt = now
,
UpdatedAt = now
}, dbcs.GetTransaction());;
return(result);
}
public async Task <Article> getAsync(DbConnectionService dbcs, string sql, object param)
{
string constr = Startup.ConnectionString;
var result = await dbcs.GetConnection().QueryAsync <Article, ApplicationUser, ArticleContents, Article>(temp_sql + sql,
(article, user, articleContents) =>
{
article.User = user;
if (article.Contents == null)
{
article.Contents = new List <ArticleContents>();
}
if (articleContents != null)
{
article.Contents.Add(articleContents);
}
return(article);
},
param);
foreach (var a in result)
{
return(a);
}
throw new InvalidOperationException("Error:Not Found Article");
}
public static void initializeQuestionsRadioOperator()
{
DbConnectionService db = (DbConnectionService)Current.Properties["db"];
DataTable dt = db.executeCommand("SELECT * FROM `t_funk_ubi` join t_fragebogen_funk_ubi on F_id_Funk_UBI = Id");
List <Question> questions = new List <Question>();
List <Answer> allAnswers = new List <Answer>();
foreach (DataRow row in dt.Rows)
{
var rowHasNullValues = false;
row.ItemArray.ToList().ForEach(obj => rowHasNullValues = obj == DBNull.Value);
if (rowHasNullValues)
{
continue;
}
List <Answer> answers = new List <Answer>();
for (int i = 2; i <= 5; i++)
{
answers.Add(new Answer(row[i].ToString()));
}
answers.ForEach(answer => allAnswers.Add(answer));
answers[0].Set_Right();
questions.Add(new Question(Convert.ToInt32((row["Id"])), row["Frage"].ToString(), answers));
}
SharedDataController.sharedData.Questions = questions;
SharedDataController.sharedData.Answers = allAnswers;
}
public void OdbcDriverCheck(string version, int lcid, bool expected)
{
var coreShell = Substitute.For <ICoreShell>();
var driverPath = @"c:\windows\system32\driver.dll";
var sm = new ServiceManager();
coreShell.Services.Returns(sm);
var fs = Substitute.For <IFileSystem>();
fs.FileExists(Arg.Any <string>()).Returns(true);
fs.GetFileVersion(driverPath).Returns(new Version(version));
sm.AddService(fs);
var registry = Substitute.For <IRegistry>();
sm.AddService(registry);
var odbc13Key = Substitute.For <IRegistryKey>();
odbc13Key.GetValue("Driver").Returns(driverPath);
var hlkm = Substitute.For <IRegistryKey>();
hlkm.OpenSubKey(@"SOFTWARE\ODBC\ODBCINST.INI\ODBC Driver 13 for SQL Server").Returns(odbc13Key);
registry.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64).Returns(hlkm);
var app = Substitute.For <IApplication>();
app.LocaleId.Returns(lcid);
sm.AddService(app);
var ui = Substitute.For <IUIService>();
sm.AddService(ui);
ui.When(x => x.ShowErrorMessage(Arg.Any <string>())).Do(x => {
var arg = x.Args()[0] as string;
arg.Should().Contain(CultureInfo.GetCultureInfo(lcid).Name);
});
var process = Substitute.For <IProcessServices>();
sm.AddService(process);
process.When(x => x.Start(Arg.Any <string>())).Do(x => {
var arg = x.Args()[0] as string;
arg.Should().Contain(CultureInfo.GetCultureInfo(lcid).Name);
});
var service = new DbConnectionService(coreShell);
service.CheckSqlOdbcDriverVersion().Should().Be(expected);
if (!expected)
{
ui.Received(1).ShowErrorMessage(Arg.Any <string>());
process.Received(1).Start(Arg.Any <string>());
}
}
public async Task <int> insert(DbConnectionService dbcs)
{
string sql = "INSERT INTO Article VALUES(:Id, :UserId, :PostDate, :Text, :CreatedAt, :UpdatedAt)";
SequenceService seq = new SequenceService();
var Id = seq.getNextVal(SequenceService.seqType.Article);
var now = DateTime.Now;
var result = await dbcs.GetConnection().ExecuteAsync(sql, new
{
Id = Id
,
UserId = this.User.Id
,
PostDate = this.PostDate
,
Text = this.Text
,
CreatedAt = now
,
UpdatedAt = now
}, dbcs.GetTransaction());
if (result == 1)
{
this.Id = Decimal.ToInt64(Id);
List <Task <int> > task = new List <Task <int> >();
foreach (var artC in this.Contents)
{
artC.ArticleId = this.Id;
task.Add(artC.insert(dbcs));
}
int[] retC = await Task.WhenAll(task);
foreach (var r in retC)
{
if (r == 0)
{
throw new InvalidOperationException("Error:Insert ArticleContents");
}
result += r;
}
return(result);
}
else
{
throw new InvalidOperationException("Error:Insert Article");
}
}
public static void ConfigureDbConnection <TDbConnectionConfig>(this IServiceCollection services, string connectionStringSection, Func <string, string, TDbConnectionConfig> constructor)
where TDbConnectionConfig : DbConnectionConfigBase
{
services.AddSingleton(sp =>
{
var connectionStingSection = sp.GetRequiredService <IConfiguration>().GetSection("ConnectionStrings").GetSection(connectionStringSection);
string connectionString = connectionStingSection.GetValue <string>("ConnectionString");
string providerName = connectionStingSection.GetValue <string>("ProviderName");
TDbConnectionConfig connection = constructor(connectionString, providerName);
DbConnectionService.Register(connection);
return(connection);
}
);
}
public static void initializeQuestionnaire()
{
DbConnectionService db = (DbConnectionService)App.Current.Properties["db"];
MySqlCommand cmd = db.connection.CreateCommand();
cmd.CommandText = "select * from t_fragebogen_unter_maschine;";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
}
}
}
public MainWindow()
{
InitializeComponent();
initController();
this.data = SharedDataController.sharedData;
DbConnectionService db = new DbConnectionService();
App.Current.Properties["applicationTitle"] = this.applicationTitle.Content;
App.Current.Properties["db"] = db;
//App.initializeQuestions(1);
// get application title from application settings
applicationTitle.Content = Schifffahrt.Properties.Settings.Default.ApplicationTitle;
courseHistory.Text = Schifffahrt.Properties.Settings.Default.CourseHistory;
}
private void SubmitButton_Click(object sender, RoutedEventArgs e)
{
string username = UsernameBox.Text;
string password = PasswordBox.Password;
DbConnectionService.SetCredentials(username, password);
Context = DbConnectionService.GetConnection();
if (!DbConnectionService.TestConnection(Context))
{
Console.WriteLine("Login error");
return;
}
NavigationService.Navigate(new MainPage(Context));
}
public async Task <IActionResult> FollowUser(string userName, string isFollow)
{
var user = new ApplicationUser();
user = await user.getUserAsync(userName);
var loginUser = new ApplicationUser();
loginUser = await loginUser.getUserAsync(User.Identity.Name);
var dbcs = new DbConnectionService();
try
{
dbcs.Open();
dbcs.BeginTran();
var follow = new FollowUsers(loginUser, user);
if (isFollow == "true")
{
// フォロー解除
await follow.delete(dbcs);
isFollow = "false";
}
else
{
//フォロー
await follow.insert(dbcs);
isFollow = "true";
}
dbcs.CommitTran();
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
return(BadRequest("Network Error"));
}
finally
{
dbcs.Close();
}
return(Ok(new { isFollow = isFollow }));
}
public async Task <IActionResult> Index(string UserName, int?pageNum)
{
var DispUser = new ApplicationUser();
DispUser = await DispUser.getUserAsync(UserName);
ViewBag.user = DispUser;
ViewData["pageNum"] = pageNum ?? 1;
var dbcs = new DbConnectionService();
dbcs.Open();
// ログインしている場合、ログインユーザのマイページかとフォロー状態をチェック
if (User.Identity.IsAuthenticated)
{
var LoginUser = new ApplicationUser();
LoginUser = await LoginUser.getUserAsync(User.Identity.Name);
if (LoginUser.Id == DispUser.Id)
{
ViewBag.mypage = true;
ViewBag.follow = "false";
}
else
{
ViewBag.mypage = false;
ViewBag.follow = await FollowUsers.checkFollow(dbcs, LoginUser.Id, DispUser.Id);
}
}
string sql = " where Article.UserId = :UserId " +
" order by PostDate desc";
var list = await new Article().search(dbcs, sql, new { UserId = DispUser.Id });
dbcs.Close();
ViewBag.list = PaginatedService <Article> .Create(list, pageNum ?? 1, 8);
return(View("Index"));
}
static void Main(string[] args)
{
var dbConfigService = DbConfigService.GetInstance(DbEnum.File);
// File
Console.WriteLine("Db Info : Arquivo");
Console.WriteLine(dbConfigService.CreateConfigJson());
// Queue
Console.WriteLine("Db Info : Mensageria");
dbConfigService.DbType = DbEnum.Queue;
Console.WriteLine(dbConfigService.CreateConfigJson());
// Environment Variable
Environment.SetEnvironmentVariable("host", "192.168.0.1");
Environment.SetEnvironmentVariable("port", "3666");
Environment.SetEnvironmentVariable("user", "userEnvironment");
Environment.SetEnvironmentVariable("password", "passEnvironment");
Environment.SetEnvironmentVariable("database", "EnvironmentDatabase");
Console.WriteLine("Db Info : Variaveis de Ambiente");
dbConfigService.DbType = DbEnum.EnvironmentVariables;
Console.WriteLine(dbConfigService.CreateConfigJson());
// Database
Console.WriteLine("Database : EnvironmentVariables");
var connService = new DbConnectionService(DbEnum.EnvironmentVariables);
Console.WriteLine(connService.GetConnString());
connService.Execute("SELECT * FROM tb_teste t WHERE t.id = 0");
Console.WriteLine("-----------------------------------------");
Console.WriteLine("Database : File");
connService = new DbConnectionService(DbEnum.File);
Console.WriteLine(connService.GetConnString());
connService.Execute("SELECT * FROM tb_teste t WHERE t.id = 0");
Console.WriteLine("-----------------------------------------");
Console.ReadKey();
}
public async Task <int> update(DbConnectionService dbcs)
{
if (this.Id == 0)
{
throw new InvalidOperationException("Error:Id is Null");
}
string sql = "UPDATE Article SET Text = :Text, UpdatedAt = :UpdatedAt where Id = :Id)";
var result = await dbcs.GetConnection().ExecuteAsync(sql, new
{
Id = this.Id
,
Text = this.Text
,
UpdatedAt = DateTime.Now
}, dbcs.GetTransaction());
if (result == 0)
{
throw new InvalidOperationException("Error:Update Article");
}
return(result);
}
public async Task <IEnumerable <Article> > search(DbConnectionService dbcs, string sql, object param)
{
string constr = Startup.ConnectionString;
var tmpDict = new Dictionary <long, Article>();
var result = await dbcs.GetConnection().QueryAsync <Article, ApplicationUser, ArticleContents, Article>(temp_sql + sql,
(article, user, articleContents) =>
{
Article art;
if (!tmpDict.TryGetValue(article.Id, out art))
{
tmpDict.Add(article.Id, art = article);
}
if (art.User == null)
{
art.User = user;
}
if (art.Contents == null)
{
art.Contents = new List <ArticleContents>();
}
if (articleContents != null)
{
art.Contents.Add(articleContents);
}
return(art);
},
param
, splitOn: "Id");
return(tmpDict.Values);
}
public override async Task OnConnectedAsync()
{
var user = new ApplicationUser();
user = await user.getUserAsync(Context.User.Identity.Name);
var dbcs = new DbConnectionService();
dbcs.Open();
var fu = new FollowUsers();
var list = await fu.GetListAsync(dbcs, user.Id);
List <Task> tasks = new List <Task>();
tasks.Add(Groups.AddToGroupAsync(Context.ConnectionId, user.UserName));
foreach (var f in list)
{
tasks.Add(Groups.AddToGroupAsync(Context.ConnectionId, f.FollowUser.UserName));
}
await Task.WhenAll(tasks);
await base.OnConnectedAsync();
}
public ContentFragmentQueryService(DbConnectionService db)
{
_db = db;
}
public void OneTimeSetUp()
{
db = new DbConnectionService();
}
public ContentFragmentService(DbConnectionService db, ContentFragmentProvider fragmentProvider)
{
_db = db;
_fragmentProvider = fragmentProvider;
}
public void OneTimeTearDown()
{
var db = new DbConnectionService();
db.DeleteIfDoesExist(ContentController.Db);
}
public void GetConnectionSettings_CallWithInvalidConnectionString_ThrowsException()
{
IDbConnectionService dbConnectionService = new DbConnectionService(_connectionString + "-Invalid");
Assert.ThrowsException <ConfigurationErrorsException>(() => dbConnectionService.GetConnectionSettings());
}
public void DbConnectionService_InitWithValidConnectionString_ReturnsDbConnectionInstanceInstance()
{
IDbConnectionService dbConnectionService = new DbConnectionService(_connectionString);
Assert.IsTrue(dbConnectionService != null);
}
private ContentFragmentProvider(DbConnectionService db)
{
_db = db;
}
