public bool UserIsExist(string userName)
{
//prepare parameter
var userNames = userName.Split('.');
var firstName = userNames[0];
var lastName = userNames[1];
//init sql query build
var sqlQueryBuilder = new SqlQueryBuilder();
sqlQueryBuilder.Select("*")
.From("Person.Person")
.Where("FirstName", SqlBuilder.Parameter <string>("firstName"))
.AndWhere("LastName", SqlBuilder.Parameter <string>("lastName"));
var sql = sqlQueryBuilder.ToSqlString(_context);
//execute sql
var dynamicModel = _context.SqlExecutor.Select <DynamicModel>(sql, firstName, lastName);
if (dynamicModel.Count == 0)
{
return(false);
}
return(true);
}
public async Task <List <SampleDTO> > GetSampleData(SampleParameter query)
{
return(await _sqlQueryBuilder
.Select("Name")
.From("Sample")
.BuildQuery <SampleDTO>()
.ExecuteToList());
}
public void SqlServerTestSimpleQuery()
{
long id = -1;
SqlQueryBuilder builder = new SqlQueryBuilder(new SqlServerDAOHelper("System.Data.SqlClient"));
builder
.Select(new string[] { "IDINSTANCE" })
.From("BTINSTANCES", "B")
.Where()
.Condition("B", "BARCODE", WhereOperator.EqualTo, "5050925927567");
string query = builder.ToString();
string sqlServerConnectionStr = @"Data Source=192.168.100.42,9433\DEV2016;Initial Catalog=WmX_Kostelia_AH_Test;Persist Security Info=True;User Id=sa;Password=Sql2016$";
var factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = sqlServerConnectionStr;
conn.Open();
var command = conn.CreateCommand();
command.CommandText = query;
foreach (var builderParam in builder.Parameters)
{
var param = command.CreateParameter();
param.ParameterName = builderParam.Name;
param.Value = builderParam.Value;
command.Parameters.Add(param);
}
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
id = dr.GetInt64(0);
}
}
conn.Close();
}
Assert.IsTrue(id != -1);
}
public void OracleTestSimpleQuery()
{
long id = -1;
SqlQueryBuilder builder = new SqlQueryBuilder(new OracleDAOHelper("Oracle.ManagedDataAccess.Client"));
builder
.Select(new string[] { "IDINSTANCE" })
.From("BTINSTANCES", "B")
.Where()
.Condition("B", "BARCODE", WhereOperator.EqualTo, "0003517");
string query = builder.ToString();
string oracleConnectionStr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.42)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)));User Id=QUALITYX_INDUSTRIES_DEV;Password=QUALITYX_INDUSTRIES_DEV;enlist=false";
var factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = oracleConnectionStr;
conn.Open();
var command = conn.CreateCommand();
command.CommandText = query;
foreach (var builderParam in builder.Parameters)
{
var param = command.CreateParameter();
param.ParameterName = builderParam.Name;
param.Value = builderParam.Value;
command.Parameters.Add(param);
}
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
id = dr.GetInt64(0);
}
}
conn.Close();
}
Assert.IsTrue(id != -1);
}
