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); }