public void Get_Table_Definition()
{
var server = new SqlServer();
var table = server.GetTableDefinition("localhost", "master", "spt_fallback_db");
table.ShouldNotBeNull();
table.Columns.Count().ShouldNotEqual(0);
}
public void SqlServer_Connect_UserIDInDomainFormat_ConnectionStringIsNotIntegratedSecurityEqualsSSPI()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
dbSource.UserID = "Dev2\\TestUser";
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
try
{
// expect this call to throw a 'login failed' exception
sqlServer.Connect(dbSource.ConnectionString);
}
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{
}
//------------Assert Results-------------------------
Assert.IsFalse(sqlServer.ConnectionString.Contains("Integrated Security=SSPI"));
}
finally
{
sqlServer.Dispose();
}
}
/// <summary>
/// �õ�ʵ��
/// </summary>
/// <returns></returns>
public static SqlServer SqlDataBase()
{
if (_SqlDataBase == null)
{
_SqlDataBase = new Universal_ClassLibrary.DataBase.SqlServer(ConfigHelper.GetAppSettings("SqlServer_NewSaaS"));
}
return _SqlDataBase;
}
public void FecharCaixa()
{
var impostos = 200;
var database = new SqlServer();
var vendas = database.Selecionar("Dinheiro em caixa");
var vendasDeOntem = database.Selecionar("Dinheiro em caixa de ontem");
var totalDeVendas = vendas - vendasDeOntem;
totalDeVendas = totalDeVendas - impostos;
database.Atualizar("vendas líquidas", totalDeVendas.ToString());
database.Deletar("venda bruta");
}
public void Should_replace_where_with_included_databases()
{
var queryType = new SqlServerConnections();
var sqlServer = new SqlServer("foo", "foo", true, new[] {new Database {Name = "bar"},}, null);
var queryLocator = new QueryLocator(null);
var queries = queryLocator.PrepareQueries(new[] {queryType.GetType()}, false);
foreach (var query in queries)
{
var actual = queryType.ParameterizeQuery(query.CommandText, sqlServer);
Assert.That(actual, Is.StringContaining("AND (d.Name IN ('bar')"));
}
}
public void Test_Type_SQLServerDatabase()
{
// Define tipo Sql
AbstractDataBase dataBase = new SqlServer();
// Atribuindo command
DbCommand cmd = dataBase.Command;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT, DELETE, INSERT, UPDATE";
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
reader.Close();
cmd.Connection.Close();
Assert.IsInstanceOfType(dataBase, typeof(SqlServer));
}
// ReSharper disable InconsistentNaming
public void SqlServer_Connect_ConnectionStringIsInvalid_ThrowsArgumentException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect("xxx", CommandType.Text, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_Connect_CommandTextIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect("Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;", CommandType.Text, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_Connect_ConnectionStringIsNull_ThrowsArgumentNullException()
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect(null, CommandType.Text, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchDatabases_ConnectionNotInitialized_ThrowsConnectFirstException()
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.FetchDatabases();
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_Connect_CommandTextIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect("Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;", CommandType.Text, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_Connect_ConnectionStringIsInvalid_ThrowsArgumentException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect("xxx", CommandType.Text, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDataTable_CommandIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.FetchDataTable((IDbCommand)null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_CreateCommand_ConnectionNotInitialized_ThrowsConnectFirstException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.CreateCommand();
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
//protected HyperLink CreateRoleLink;
protected override void OnLoad(EventArgs e)
{
if (!Page.IsPostBack)
{
SqlServer server = SqlServer.CurrentServer;
server.Connect();
SqlDatabase database = SqlDatabase.CurrentDatabase(server);
RolesGrid.DataSource = database.DatabaseRoles;
RolesGrid.DataBind();
//CreateRoleLink.NavigateUrl="CreateDatabaseRole.aspx?database=" + Request["database"];
server.Disconnect();
}
base.OnLoad(e);
}
public void UsuarioAtualizarInserir()
{
try
{
SqlServer sqlServer = new SqlServer();
sqlServer.limparSqlParameterCollection();
sqlServer.addSqlParameterCollection("@Nome", usuarioNovo.Nome);
sqlServer.addSqlParameterCollection("@Email", usuarioNovo.Email);
sqlServer.addSqlParameterCollection("@Usuario", usuarioNovo.UsuarioNome);
sqlServer.addSqlParameterCollection("@Senha", usuarioNovo.Senha);
sqlServer.addSqlParameterCollection("@Nascimento", usuarioNovo.Nascimento);
sqlServer.addSqlParameterCollection("@idAgenteCustodia", 1);
object a = sqlServer.excultarAcao(CommandType.StoredProcedure, "uspUsuarioInserirAtualizar");
}
catch (Exception ex)
{ MessageBox.Show("Erro: " + ex.Message); }
MessageBox.Show("Dados pessoais atualizado com sucesso!", "Atualização", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public JObject TimeLoad(long start, long limit, string field, string direction, string searchInfo)
{
try
{
DBSortPage dbsort = new DBSortPage(start, limit, field, direction);
DBServer dbserver = new SqlServer();
string filterf = string.Format("select distinct intRollCount from V_Attendance_Detail where nvcElectiveNum='{0}'", searchInfo);
JObject jobj = null;
DataTable table = null;
table = dbserver.ExecuteTable(filterf);
jobj = CreateJsonInstance_Time(table);
return(jobj);
}
catch (Exception ex)
{
return(ShowDataLoadError(ex.Message));
}
}
public JObject PageLoad(long start, long limit, string field, string direction, string searchInfo)
{
try
{
DBSortPage dbsort = new DBSortPage(start, limit, field, direction);
DBServer dbserver = new SqlServer();
string filterf = string.Format("select nvcElectiveNum,nvcCourseName,timeAndPlace,attendanceCount from V_Attendance_TeaCourse where nvcLoginCode='{0}'", searchInfo);
JObject jobj = null;
DataTable table = null;
table = dbserver.ExecuteTable(filterf);
jobj = CreateJsonInstance_Major(table);
return(jobj);
}
catch (Exception ex)
{
return(ShowDataLoadError(ex.Message));
}
}
private List <NatureArea> GetNatureAreaInfos(SqlConnection sqlConnection, SqlBuilder builder, SqlBuilder.Template template, int infoLevel)
{
var natureAreas = new List <NatureArea>();
builder.Select("na.id AS Id, na.localId AS LocalId, na.naturnivå_id AS NaturnivaId, na.kartlagt AS Kartlagt, na.institusjon AS Institusjon" + (infoLevel == 2 ? ", na.kartlegger_id AS KartleggerId" : string.Empty));
string sql = template.RawSql;
var infos = sqlConnection.Query <NatureAreaInfoDto>(sql, template.Parameters).ToList();
foreach (var info in infos)
{
var natureArea = new NatureAreaExport
{
Id = info.Id,
UniqueId = new Identification {
LocalId = info.LocalId
},
Nivå = (NatureLevel)info.NaturnivaId,
Surveyed = info.Kartlagt,
Institution = info.Institusjon
};
if (infoLevel == 2 && info.KartleggerId.HasValue)
{
natureArea.Surveyer = new Contact {
Id = info.KartleggerId.Value
};
}
natureAreas.Add(natureArea);
}
if (infoLevel == 1)
{
natureAreas = SqlServer.SetParameters(sql.Contains("WHERE"), natureAreas).ToList();
}
else if (infoLevel == 2)
{
SqlServer.SetMetadata(natureAreas);
}
return(natureAreas);
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDataSet_CallsNestedFactory_ParamsOnly_UsesNestedCommand()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock <IDbFactory>();
var mockCommand = new Mock <IDbCommand>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny <CommandBehavior>())).Returns(new Mock <IDataReader>().Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var added = new SqlCommand().Parameters;
mockCommand.Setup(a => a.Parameters).Returns(added);
var helpTextCommand = new Mock <IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny <CommandBehavior>())).Returns(new Mock <IDataReader>().Object);
DataTable dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add(new object[] { "Bob" });
dt.Rows.Add(new object[] { "Dave" });
factory.Setup(a => a.GetSchema(It.IsAny <IDbConnection>(), "Databases")).Returns(dt);
var conn = new Mock <IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
conn.Setup(a => a.ConnectionString).Returns("bob");
conn.Setup(a => a.CreateCommand()).Returns(mockCommand.Object);
factory.Setup(a => a.CreateConnection(It.IsAny <string>())).Returns(conn.Object);
factory.Setup(a => a.FetchDataSet(It.IsAny <DbCommand>())).Returns(new DataSet());
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
pvt.SetField("_command", mockCommand.Object);
//------------Execute Test---------------------------
sqlServer.FetchDataSet(new SqlParameter[] { });
factory.Verify(a => a.FetchDataSet(It.IsAny <IDbCommand>()));
}
finally
{
sqlServer.Dispose();
}
}
private string LoguearUsuario(out bool resultado, out int idUsuario)
{
string mensaje = "";
idUsuario = 0;
string usuario = Txt_Usuario.Text;
SHA256CryptoServiceProvider sha256 = new SHA256CryptoServiceProvider();
UTF8Encoding utf8 = new UTF8Encoding();
string password = BitConverter.ToString(sha256.ComputeHash(utf8.GetBytes(Txt_Password.Text)));
Parametros listaParametros = new Parametros();
SqlServer sqlServer = new SqlServer();
listaParametros.AgregarParametro("Usuario", usuario);
listaParametros.AgregarParametro("Password", password);
DataTable dataTable = sqlServer.EjecutarSp("SP_Get_Usuario", listaParametros);
if (dataTable.Rows.Count == 0)
{
resultado = false;
mensaje = "Error con la BD";
}
else
{
if (dataTable.Rows[0].ItemArray[0].ToString() == "ERROR")
{
resultado = false;
mensaje = dataTable.Rows[0].ItemArray[1].ToString();
}
else if (dataTable.Rows[0].ItemArray[0].ToString() == "")
{
resultado = false;
mensaje = "Password o Usuario Incorrecto";
}
else
{
resultado = true;
idUsuario = int.Parse(dataTable.Rows[0].ItemArray[0].ToString());
}
}
return(mensaje);
}
/// <summary>
/// 教师获取当前学期课程列表 2015-08-15 20:58:41
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void AttendanceList(HttpContext context)
{
string json = "{\"success\":false}";
string term = context.Request.QueryString["term"];
//string term = "2015-2016-1";
string nvcLoginCode = context.Request.QueryString["userno"];
try
{
string cmdText = string.Format("select nvcCourseName,nvcElectiveNum,timeAndPlace,attendanceCount,totalCount from {0} where nvcLoginCode='{1}' AND nvcElectiveNum like '%{2}%'", ATView, nvcLoginCode, term);
DBServer server = new SqlServer();
DataTable table = server.ExecuteTable(cmdText);
JArray array = new JArray();
#region if
if (table != null && table.Rows.Count != 0)
{
foreach (DataRow row in table.Rows)
{
string nvcCourseName = (row["nvcCourseName"] != null) ? row["nvcCourseName"].ToString() : null;
string nvcElectiveNum = (row["nvcElectiveNum"] != null) ? row["nvcElectiveNum"].ToString() : null;
string timeAndPlace = (row["timeAndPlace"] != null) ? row["timeAndPlace"].ToString() : null;
string attendanceCount = (row["attendanceCount"].ToString() != "") ? row["attendanceCount"].ToString() : "0";
string totalCount = (row["totalCount"].ToString() != "") ? row["totalCount"].ToString() : "0";
JObject obj = new JObject(
new JProperty("nvcCourseName", nvcCourseName),
new JProperty("nvcElectiveNum", nvcElectiveNum),
new JProperty("timeAndPlace", timeAndPlace),
new JProperty("attendanceCount", attendanceCount),
new JProperty("totalCount", totalCount)
);
array.Add(obj);
}
json = array.ToString();
context.Response.Write(json);
}
#endregion
}
catch
{
context.Response.Write(json);
}
}
public Collection <Metadata> GetMetadatasBySearchFilter(
Collection <NatureLevel> natureLevels,
Collection <string> natureAreaTypeCodes,
Collection <string> descriptionVariableCodes,
Collection <int> municipalities,
Collection <int> counties,
Collection <int> conservationAreas,
Collection <string> institutions,
IEnumerable <int> redlistAssessmentUnits,
IEnumerable <int> redlistCategories,
string geometry,
string boundingBox,
int espgCode)
{
var metadatas = new Collection <Metadata>();
var builder = new SqlBuilder();
bool nonEmptyGeometry =
ConstructBuilder(
natureLevels, natureAreaTypeCodes, descriptionVariableCodes, municipalities, counties,
conservationAreas, institutions, redlistAssessmentUnits, redlistCategories, geometry,
boundingBox, espgCode, builder);
if (!nonEmptyGeometry)
{
return(metadatas);
}
var template = builder.AddTemplate("SELECT /**select**/ FROM Naturområde na /**join**/ /**where**/");
builder.Select("DISTINCT na.localId");
List <Guid> localIds = null;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
localIds = conn.Query <Guid>(template.RawSql, template.Parameters).ToList();
}
return(SqlServer.GetMetadatasByNatureAreaLocalIds(localIds.Select(lid => lid.ToString()).ToList(), true));
}
public void SqlServer_Connect_InvalidLogin_ThrowsSqlException()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
dbSource.Password = Guid.NewGuid().ToString(); // Random invalid password
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect(dbSource.ConnectionString);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public ModApiPaymentHostedService(ILogger <ModApiPaymentHostedService> logger, SqlServer sqlServer, IConfiguration config, Share.ShareService shareService)
{
this._logger = logger;
this._sqlServer = sqlServer;
this._cts = new CancellationTokenSource();
this._config = Startup.Config.ModApi;
this._shareService = shareService;
this._paymentConverters = new List <IPaymentConverter>();
this._paymentConverters.Add(new TinkoffConverter());
this._paymentConverters.Add(new RaiffeisenConverter());
this._paymentConverters.Add(new SberbankConverter());
this._paymentConverters.Add(new SbAcqConverter());
this._paymentConverters.Add(new SbRegConverter());
//this._paymentConverters.Add(new YksRegConverter());
}
public static void Remove(Pallet pallet)
{
try
{
using (var connection = SqlServer.OpenConnection())
{
var strSql = "DELETE FROM Pallets WHERE Id = @Id";
using (var command = new SqlCommand(strSql, connection))
{
command.Parameters.AddWithValue("@Id", pallet.Id);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
}
}
public List <InterviewTypeDto> getInterviewList()
{
string query = @"SELECT id,name FROM INTERVIEWTYPE";
using (var connection = new SqlConnection(SqlServer.SqlServerConnection))
{
connection.Open();
DataTable dt = SqlServer.GetQueryResult(connection, query);
var result = (from dr in dt.AsEnumerable()
select new InterviewTypeDto
{
id = dr["id"].ToString(),
name = dr["name"].ToString()
}).ToList();
return(result);
}
}
/// <summary>
/// 获取学生缺勤列表信息
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void AttendanceView(HttpContext context)
{
string json = null;
string nvcYear = context.Request.QueryString["nvcYear"];
string nvcTerm = context.Request.QueryString["nvcTerm"];
string nvcStuNo = context.Request.QueryString["userno"];
try
{
string cmdText = string.Format("select nvcElectiveNum,nvcCourseName,nvcName,dtmRollTime,nvcReason from {0} where nvcStuNo='{1}' AND nvcYear='{2}' AND nvcTerm='{3}' order by dtmRollTime DESC;", ASView, nvcStuNo, nvcYear, nvcTerm);
DBServer server = new SqlServer();
DataTable table = server.ExecuteTable(cmdText);
JArray array = new JArray();
#region if
foreach (DataRow row in table.Rows)
{
string nvcElectiveNum = (row["nvcElectiveNum"] != null) ? row["nvcElectiveNum"].ToString() : null;
string nvcCourseName = (row["nvcCourseName"] != null) ? row["nvcCourseName"].ToString() : null;
string nvcName = (row["nvcName"] != null) ? row["nvcName"].ToString() : null;
string dtmRollTime = (row["dtmRollTime"] != null) ? row["dtmRollTime"].ToString() : null;
string nvcReason = (row["nvcReason"] != null) ? row["nvcReason"].ToString() : null;
JObject obj = null;
obj = new JObject(
new JProperty("nvcElectiveNum", nvcElectiveNum),
new JProperty("nvcCourseName", nvcCourseName),
new JProperty("nvcName", nvcName),
new JProperty("dtmRollTime", dtmRollTime),
new JProperty("nvcReason", nvcReason)
);
array.Add(obj);
}
json = array.ToString();
context.Response.Write(json);
#endregion
}
catch
{
context.Response.Write(null);
}
}
private List <SqlServer> ReadSqlDatabases(IResourceGroup resourceGroup)
{
var sqlServers = new List <SqlServer>();
var azureSqlServers = _azure.SqlServers.ListByResourceGroup(resourceGroup.Name);
foreach (var azureSqlServer in azureSqlServers)
{
var sqlServer = new SqlServer(azureSqlServer);
foreach (var database in azureSqlServer.Databases.List())
{
//TODO Ignore "master" database?
sqlServer.Databases.Add(new SqlDatabase {
Name = database.Name
});
}
sqlServers.Add(sqlServer);
}
return(sqlServers);
}
/// <summary>
/// 根据选课号，获取该课程的辅导员重点监控学生 2015年8月20日17:31:31
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void Attend_Fourth(HttpContext context)
{
string json = "{\"success\":false}";
string nvcElectiveNum = context.Request.Form["nvcElectiveNum"];
#region try
try
{
string cmdText = string.Format("select BC.nvcStuNo,BC.nvcStuName,BC.nvcClass,BC.nvcStuPhoto from {0} AS BC where BC.nvcFreeFlag='0' AND BC.nvcElectiveNum='{2}' AND BC.nvcStuNo in (select nvcStuNo from {1});", BCView, AFView, nvcElectiveNum);
DBServer server = new SqlServer();
DataTable table = server.ExecuteTable(cmdText);
JArray array = new JArray();
foreach (DataRow row in table.Rows)
{
string nvcStuNo = (row["nvcStuNo"] != null) ? row["nvcStuNo"].ToString() : null;
string nvcStuName = (row["nvcStuName"] != null) ? row["nvcStuName"].ToString() : null;
string nvcClass = (row["nvcClass"] != null) ? row["nvcClass"].ToString() : null;
string nvcStuPhoto = (row["nvcStuPhoto"] != null) ? row["nvcStuPhoto"].ToString() : null;
JObject obj = null;
obj = new JObject(
new JProperty("nvcStuNo", nvcStuNo),
new JProperty("nvcStuName", nvcStuName),
new JProperty("nvcClass", nvcClass),
new JProperty("nvcStuPhoto", nvcStuPhoto)
);
array.Add(obj);
}
json = new JObject(
new JProperty("success", true),
new JProperty("student", array),
new JProperty("total", table.Rows.Count)
).ToString();
context.Response.Write(json);
}
catch
{
context.Response.Write(json);
}
#endregion
}
public JObject Add(JObject data)
{
try
{
DBServer dbserver = new SqlServer();
Vote model = new Vote(dbserver);
data.Remove("ID");
data.Add(new JProperty("ID", GUID.NewGuid()));
string nvcElectiveNum = data["nvcElectiveNum"].ToString().Replace("\"", "").Trim();
string nvcStuNo = data["nvcStuNo"].ToString().Replace("\"", "").Trim();
string cmdText = string.Format("select Count(*) from V_Evaluate_Vote where nvcElectiveNum='{0}' and nvcStuNo='{1}'", nvcElectiveNum, nvcStuNo);
DBServer server = new SqlServer();
object o = dbserver.ExecuteScalar(cmdText);
if (Convert.ToInt32(o) != 0)
{
return(ShowExecuteError("你已经为该老师投过票！"));
}
else
{
long count = GetVotedCount(nvcStuNo);
if (model.Create(data))
{
string msg = string.Format("投票成功！(总共3票，还有{0}票)", 2 - count);
return(ShowSuccess(msg));
}
else
{
return(ShowExecuteError("保存失败."));
}
}
}
catch (Exception ex)
{
return(ShowExecuteError(ex.Message));
}
}
private AppService DeployBackend(ResourceGroup resourceGroup)
{
var appServicePlan = new Plan(CorrectNaming("asp-be"), new PlanArgs
{
ResourceGroupName = resourceGroup.Name,
Kind = "App",
Sku = new PlanSkuArgs
{
Tier = "Basic",
Size = "B1",
},
});
var appInsights = new Insights(CorrectNaming("ai-be"), new InsightsArgs
{
ApplicationType = "web",
ResourceGroupName = resourceGroup.Name
});
var username = config.Get("sqlAdmin") ?? "pulumi";
var password = config.RequireSecret("sqlPassword");
var sqlServer = new SqlServer(CorrectNaming("sqls"), new SqlServerArgs
{
ResourceGroupName = resourceGroup.Name,
AdministratorLogin = username,
AdministratorLoginPassword = password,
Version = "12.0",
});
var database = new Database("sql", new DatabaseArgs
{
ResourceGroupName = resourceGroup.Name,
ServerName = sqlServer.Name,
RequestedServiceObjectiveName = "S0",
});
var app = new AppService(CorrectNaming("as-be"), new AppServiceArgs
{
ResourceGroupName = resourceGroup.Name,
AppServicePlanId = appServicePlan.Id,
AppSettings =
{
{ "APPINSIGHTS_INSTRUMENTATIONKEY", appInsights.InstrumentationKey },
public bool LoadById(int idNumber)
{
using (var db = SqlServer.CreateAndOpenConnection())
{
using (var cmd = new SqlCommand("Patient_OpenById", db)
{
CommandType = CommandType.StoredProcedure
})
{
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = idNumber;
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(false);
}
Id = reader.GetInt32From("Id");
Lastname = reader.GetStringFrom("Lastname");
Firstname = reader.GetStringFrom("Firstname");
Middlename = reader.GetStringFrom("Middlename");
NameExtension = reader.GetStringFrom("NameExtension");
Gender = reader.GetStringFrom("Gender");
BirthDate = reader.GetDateTimeFrom("BirthDate");
Department = reader.GetStringFrom("Department");
BloodType = reader.GetStringFrom("BloodType");
Height = reader.GetDecimalFrom("Height");
Weight = reader.GetDecimalFrom("Weight");
Remarks = reader.GetStringFrom("Remarks");
RecordInfo.LoadValuesFrom(reader);
//RecordInfo.CreatedDate = reader.GetDateTimeFrom("Created");
//RecordInfo.ModifiedDate = reader.GetDateTimeFrom("Modified");
//RecordInfo.CreatedBy = reader.GetStringFrom("CreatedBy");
//RecordInfo.ModifiedBy = reader.GetStringFrom("ModifiedBy");
}
}
}
return(true);
}
public static ApiResponse.RespuestaVentaSicom EnviarVenta(string Recibo)
{
try
{
ApiResponse.RespuestaVentaSicom response = null;
SqlServer _Helper = new SqlServer();
string[] data = new string[2];
string Fecha = "";
string Rom = "";
string Cantidad = "";
bool EsGnv = false;
IDataReader oLector = _Helper.RecuperarVenta(Convert.ToInt64(Recibo), true);
while (oLector.Read())
{
Fecha = oLector["FechaSicom"].ToString();
Cantidad = oLector["Cantidad"].ToString().Replace(",", ".");
Rom = oLector["Rom"].ToString();
EsGnv = _Helper.EsLecturaChipObligatoria(Convert.ToInt32(oLector["IdProducto"].ToString()));
}
oLector.Close();
if ((EsGnv) && (!string.IsNullOrEmpty(Rom)))
{
ApiResponse.VentaSicom venta = new ApiResponse.VentaSicom();
venta.fecha = Fecha;
venta.volumen = Cantidad;
string Json = "";
Json = JSON.Serialize <ApiResponse.VentaSicom>(venta);
string json = ApiSICOM.ExecuteApiCommand("EnviarVenta", Json, Rom);
response = JSON.Deserialize <ApiResponse.RespuestaVentaSicom>(json);
}
else
{
response = new ApiResponse.RespuestaVentaSicom();
response.estado = 0;
response.texto = "Venta de combustible liquido";
}
return(response);
}
catch { throw; }
}
public ConexaoSql(SqlServer SQLServer, bool producao)
{
try
{
_SqlServer = SQLServer;
_producao = producao;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("Failed to establish a connection") >= 0)
{
throw new Exception("NÃO FOI POSSÍVEL CONECTAR AO BANCO DE DADOS. SERVIDOR PODE ESTAR EM MANUTENÇÃO OU SEU COMPUTADOR PODE ESTAR SEM REDE OU INTERNET, TENTE NOVAMENTE MAIS TARDE.\n" + ex.Message);
}
else
{
throw new Exception("NÃO FOI POSSÍVEL CONECTAR AO BANCO DE DADOS. TENTE NOVAMENTE. " + ex.Message);
}
}
}
public void SqlServer_Connect_ValidLogin_IsConnectedIsTrue()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.Connect(dbSource.ConnectionString);
//------------Assert Results-------------------------
Assert.IsTrue(sqlServer.IsConnected);
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchDatabases_CallsSchemaFunctionWithCorrectyParams()
{
//------------Setup for test--------------------------
var factory = new Mock <IConnectionBuilder>();
var mockCommand = new Mock <IDbCommand>();
var helpTextCommand = new Mock <IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny <CommandBehavior>())).Returns(new Mock <IDataReader>().Object);
var dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add("Bob");
dt.Rows.Add("Dave");
mockCommand.Setup(a => a.ExecuteReader()).Returns(dt.CreateDataReader);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var conn = new Mock <ISqlConnection>();
conn.Setup(a => a.GetSchema("Databases")).Returns(dt);
conn.Setup(a => a.State).Returns(ConnectionState.Open);
factory.Setup(builder => builder.BuildConnection(It.IsAny <string>())).Returns(conn.Object);
var sqlServer = new SqlServer(factory.Object);
try
{
sqlServer.Connect("");
//------------Execute Test---------------------------
var output = sqlServer.FetchDatabases();
Assert.AreEqual("Bob", output[0]);
Assert.AreEqual("Dave", output[1]);
conn.Verify(a => a.GetSchema("Databases"), Times.Once());
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchDataTable_OnException()
{
//------------Setup for test--------------------------
var factory = new Mock <IConnectionBuilder>();
var mockCommand = new Mock <IDbCommand>();
mockCommand.Setup(a => a.ExecuteReader())
.Throws(new DbEx("There is no text for object "));
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var helpTextCommand = new Mock <IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny <CommandBehavior>()))
.Throws(new DbEx("There is no text for object "));
var dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add("Bob");
dt.Rows.Add("Dave");
var conn = new Mock <ISqlConnection>();
conn.Setup(a => a.GetSchema("Databases")).Returns(dt);
conn.Setup(a => a.State).Returns(ConnectionState.Open);
factory.Setup(builder => builder.BuildConnection(It.IsAny <string>())).Returns(conn.Object);
conn.Setup(connection => connection.CreateCommand()).Returns(mockCommand.Object);
var sqlServer = new SqlServer(factory.Object);
//------------Execute Test---------------------------
try
{
sqlServer.Connect("");
sqlServer.FetchDataTable(mockCommand.Object);
//------------Assert Results-------------------------
}
finally
{
mockCommand.Verify(a => a.ExecuteReader());
sqlServer.Dispose();
}
}
public void SqlServer_FetchDataTable_CommandIsNull_ThrowsArgumentNullException()
{
//------------Setup for test--------------------------
var conBuilder = new Mock <IConnectionBuilder>();
var sqlServer = new SqlServer(conBuilder.Object);
try
{
//------------Execute Test---------------------------
sqlServer.FetchDataTable((IDbCommand)null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
private string GetProtocolContent()
{
string text = "";
string textContent = "";
int year = System.Convert.ToInt32(DateTime.Now.Year.ToString());
int month = System.Convert.ToInt32(DateTime.Now.Month.ToString());
string term = string.Empty;
if (month < 9 && month >= 3)
{
term = (year - 1) + "-" + year + "-" + 2;
}
else
{
term = year + "-" + (year + 1) + "-" + 1;
}
DBServer dbserver = new SqlServer();
string cmdText = string.Format("select txtMatter from V_Evaluate_Setting where term='{0}'", term);
textContent = System.Convert.ToString(dbserver.ExecuteScalar(cmdText));
try
{
string file = "~/application/Evaluate/Setting/protocol.html";
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(file), FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs, Encoding.UTF8);
text = sr.ReadToEnd();
string fileContent = textContent;
FileStream fss = new FileStream(HttpContext.Current.Server.MapPath(fileContent), FileMode.Open, FileAccess.Read);
StreamReader srr = new StreamReader(fss, Encoding.UTF8);
textContent = srr.ReadToEnd();
sr.Close();
fs.Close();
srr.Close();
fss.Close();
}
catch
{
}
string result = text.Replace("<!--{Augus:ProtocolContent}-->", textContent);
return(result);
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchStoredProcedures_RollbackTransaction()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var dbTran = new Mock<IDbTransaction>();
conn.Setup(a => a.BeginTransaction()).Returns(dbTran.Object);
factory.Setup(a => a.CreateConnection(It.IsAny<string>())).Returns(conn.Object);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
sqlServer.Connect("a");
sqlServer.BeginTransaction();
sqlServer.RollbackTransaction();
factory.Verify(a => a.CreateConnection(It.IsAny<string>()));
conn.Verify(a => a.Open());
dbTran.Verify(a => a.Rollback());
dbTran.Verify(a => a.Dispose());
conn.Verify(a => a.BeginTransaction());
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchStoredProcedures_FuncReturnsSPs()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
var mockReader = new Mock<IDataReader>();
var queue = new Queue<DataTable>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
var somethingAdded = false;
var funcAdded = false;
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.StoredProcedure, "Dave.Bob")).Returns(mockCommand.Object);
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, "sp_helptext 'Dave.Bob'")).Returns(helpTextCommand.Object);
DataTable dt = new DataTable();
dt.Columns.Add("ROUTINE_NAME");
dt.Columns.Add("ROUTINE_TYPE");
dt.Columns.Add("SPECIFIC_SCHEMA");
dt.Rows.Add(new object[] { "Bob", "SQL_SCALAR_FUNCTION", "Dave" });
queue.Enqueue(dt);
queue.Enqueue(new DataTable()); // no params
factory.Setup(a => a.CreateTable(mockReader.Object, LoadOption.OverwriteChanges)).Returns(queue.Dequeue);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
//------------Execute Test---------------------------
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) =>
{
somethingAdded = true; return true;
};
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> funcProcessor = (command, list, arg3, a) =>
{
funcAdded = true; return true;
};
sqlServer.FetchStoredProcedures(procProcessor, funcProcessor);
Assert.IsTrue(funcAdded);
Assert.IsFalse(somethingAdded);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchStoredProcedures_EmptyReturnsNothing()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
var mockReader = new Mock<IDataReader>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
var somethingAdded = false;
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
DataTable dt = new DataTable();
dt.Columns.Add("ROUTINE_NAME");
dt.Columns.Add("ROUTINE_TYPE");
dt.Columns.Add("SPECIFIC_SCHEMA");
factory.Setup(a => a.CreateTable(mockReader.Object, LoadOption.OverwriteChanges)).Returns(dt);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection",conn.Object);
//------------Execute Test---------------------------
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) => { somethingAdded = true; return true; };
sqlServer.FetchStoredProcedures(procProcessor,procProcessor);
Assert.IsFalse(somethingAdded);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchStoredProcedures_FunctionProcessorIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
Func<IDbCommand, List<IDbDataParameter>, string,string, bool> procProcessor = (command, list, arg3,a) => false;
sqlServer.FetchStoredProcedures(procProcessor, null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDatabases_ConnectionNotInitialized_ThrowsConnectFirstException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.FetchDatabases();
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDataTable_CommandIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var sqlServer = new SqlServer();
try
{
//------------Execute Test---------------------------
sqlServer.FetchDataTable((IDbCommand)null);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_CreateCommand_TranactionStarted_CommandTransactionIsNotNull()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString);
sqlServer.BeginTransaction();
//------------Execute Test---------------------------
var command = sqlServer.CreateCommand();
//------------Assert Results-------------------------
Assert.IsNotNull(command);
Assert.IsNotNull(command.Transaction);
}
finally
{
sqlServer.RollbackTransaction();
sqlServer.Dispose();
}
}
public void SqlServer_FetchTableValuedFunctions_AssertSelectTextIsDifferent()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
List<IDbDataParameter> procedureCommandParameters = null;
string procedureHelpText = null;
string select = "";
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString);
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) =>
{
if (dbCommand.CommandText == "dbo.bob")
{
procedureCommandParameters = list;
procedureHelpText = helpText;
select = bob;
}
return true;
};
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) => true;
//------------Execute Test---------------------------
sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
}
finally
{
sqlServer.Dispose();
}
//------------Assert Results-------------------------
Assert.AreEqual("@country", procedureCommandParameters.First().ParameterName);
Assert.IsTrue(procedureHelpText.Contains(@"insert into @Countries
select CountryID from dbo.Country"));
Assert.AreEqual("select * from dbo.bob(@country)", select);
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDatabases_CallsSchemaFunctionWithCorrectyParams()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
var mockReader = new Mock<IDataReader>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
DataTable dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add(new object[] { "Bob"});
dt.Rows.Add(new object[] { "Dave" });
factory.Setup(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases")).Returns(dt);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
//------------Execute Test---------------------------
var output =sqlServer.FetchDatabases();
Assert.AreEqual("Bob",output[0]);
Assert.AreEqual("Dave",output[1] );
factory.Verify(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases"), Times.Once());
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDataTable_AddParams_VerifyAllAdded()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var added = new SqlCommand().Parameters;
mockCommand.Setup(a => a.Parameters).Returns(added);
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
DataTable dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add(new object[] { "Bob" });
dt.Rows.Add(new object[] { "Dave" });
factory.Setup(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases")).Returns(dt);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
pvt.SetField("_command",mockCommand.Object);
//------------Execute Test---------------------------
IDbDataParameter[] param = new IDbDataParameter[] { new SqlParameter("a", "a"), new SqlParameter("b", "b") };
SqlServer.AddParameters(mockCommand.Object,param);
Assert.AreEqual(2,added.Count);
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void Execute_Arbitrary_Sql()
{
var server = new SqlServer();
Assert.DoesNotThrow(() => server.Execute("localhost", "master", "SELECT COUNT(*) FROM sys.databases"));
}
public void Enumerate_Tables_In_A_Database()
{
var server = new SqlServer();
var databases = server.EnumerateTables("localhost", "master");
databases.Count().ShouldNotEqual(0);
}
public void Enumerate_Databases_On_A_Server()
{
var server = new SqlServer();
var databases = server.EnumerateDatabases("localhost");
databases.Count().ShouldNotEqual(0);
}
public void SqlServer_FetchDatabases_SortedListOfNames()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
var expected = new List<string>();
using (var connection = new SqlConnection(dbSource.ConnectionString))
{
connection.Open();
var databases = connection.GetSchema("Databases");
connection.Close();
var names = databases.Rows.Cast<DataRow>().Select(row => (row["database_name"] ?? string.Empty).ToString()).Distinct().OrderBy(s => s);
expected.AddRange(names);
}
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString);
//------------Execute Test---------------------------
var actual = sqlServer.FetchDatabases();
//------------Assert Results-------------------------
CollectionAssert.AreEqual(expected, actual);
}
finally
{
sqlServer.Dispose();
}
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchStoredProcedures_TableValuesProcWithParamsFuncReturnsSPs()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
var mockReader = new Mock<IDataReader>();
var queue = new Queue<DataTable>();
var param = new Mock<IDataParameterCollection>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
mockCommand.Setup(a => a.Parameters).Returns(param.Object);
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
var somethingAdded = false;
var funcAdded = false;
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.StoredProcedure, "Dave.Bob")).Returns(mockCommand.Object);
factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, "sp_helptext 'Dave.Bob'")).Returns(helpTextCommand.Object);
DataTable dt = new DataTable();
dt.Columns.Add("ROUTINE_NAME");
dt.Columns.Add("ROUTINE_TYPE");
dt.Columns.Add("SPECIFIC_SCHEMA");
dt.Rows.Add(new object[] { "Bob", "SQL_TABLE_VALUED_FUNCTION", "Dave" });
queue.Enqueue(dt);
var dtParams = new DataTable();
dtParams.Columns.Add("PARAMETER_NAME");
dtParams.Columns.Add("DATA_TYPE");
dtParams.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
dtParams.Rows.Add(new object[] { "@moo", SqlDbType.VarChar, 25 });
queue.Enqueue(dtParams); // no params
factory.Setup(a => a.CreateTable(It.IsAny<IDataReader>(), It.IsAny<LoadOption>())).Returns(queue.Dequeue);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
//------------Execute Test---------------------------
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) =>
{
somethingAdded = true; return true;
};
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> funcProcessor = (command, list, arg3, a) =>
{
Assert.AreEqual("select * from Dave.Bob(@moo)", a);
funcAdded = true; return true;
};
sqlServer.FetchStoredProcedures(procProcessor, funcProcessor);
Assert.IsTrue(funcAdded);
Assert.IsFalse(somethingAdded);
param.Verify(a=>a.Add(It.IsAny<object>()),Times.Once());
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchStoredProcedures_CorrectDataReturned()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
IDbCommand procedureCommand = null;
List<IDbDataParameter> procedureCommandParameters = null;
string procedureHelpText = null;
IDbCommand functionCommand = null;
List<IDbDataParameter> functionCommandParameters = null;
string functionHelpText = null;
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString);
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) =>
{
if (dbCommand.CommandText == "dbo.Pr_CitiesGetCountries")
{
procedureCommand = dbCommand;
procedureCommandParameters = list;
procedureHelpText = helpText;
}
return true;
};
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) =>
{
if (dbCommand.CommandText == "dbo.fn_Greeting")
{
functionCommand = dbCommand;
functionCommandParameters = list;
functionHelpText = helpText;
}
return true;
};
//------------Execute Test---------------------------
sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
}
finally
{
sqlServer.Dispose();
}
//------------Assert Results-------------------------
SqlServerTestUtils.Verify_FetchStoredProcedures_Pr_CitiesGetCountries(procedureCommand, procedureCommandParameters, procedureHelpText);
SqlServerTestUtils.Verify_FetchStoredProcedures_Fn_Greeting(functionCommand, functionCommandParameters, functionHelpText);
}
// ReSharper disable InconsistentNaming
public void SqlServer_FetchDataTable_OnException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Throws(new DbEx("There is no text for object "));
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Throws(new DbEx("There is no text for object "));
DataTable dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add(new object[] { "Bob" });
dt.Rows.Add(new object[] { "Dave" });
factory.Setup(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases")).Returns(dt);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
//------------Execute Test---------------------------
sqlServer.FetchDataTable(mockCommand.Object);
factory.Verify(a=>a.CreateTable(It.IsAny<IDataReader>(),LoadOption.OverwriteChanges));
//------------Assert Results-------------------------
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchStoredProcedures_WithClrTypeStoredProcedure_CorrectDataReturned()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
List<IDbDataParameter> procedureCommandParameters = null;
string procedureHelpText = null;
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString);
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) =>
{
if (dbCommand.CommandText == "Warewolf.RunWorkflowForSql")
{
procedureCommandParameters = list;
procedureHelpText = helpText;
}
return true;
};
Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) => true;
//------------Execute Test---------------------------
sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
}
finally
{
sqlServer.Dispose();
}
//------------Assert Results-------------------------
SqlServerTestUtils.Verify_FetchStoredProcedures_WarewolfRunForSql(procedureCommandParameters, procedureHelpText);
}
// ReSharper disable InconsistentNaming
public void SqlServer_CreateCommand_CreateCommand()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var factory = new Mock<IDbFactory>();
var mockCommand = new Mock<IDbCommand>();
mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
var added = new SqlCommand().Parameters;
mockCommand.Setup(a => a.Parameters).Returns(added);
var helpTextCommand = new Mock<IDbCommand>();
helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
DataTable dt = new DataTable();
dt.Columns.Add("database_name");
dt.Rows.Add(new object[] { "Bob" });
dt.Rows.Add(new object[] { "Dave" });
factory.Setup(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases")).Returns(dt);
var conn = new Mock<IDbConnection>();
conn.Setup(a => a.State).Returns(ConnectionState.Open);
conn.Setup(a => a.ConnectionString).Returns("bob");
conn.Setup(a => a.CreateCommand()).Returns(mockCommand.Object);
factory.Setup(a => a.CreateConnection(It.IsAny<string>())).Returns(conn.Object);
var sqlServer = new SqlServer(factory.Object);
try
{
PrivateObject pvt = new PrivateObject(sqlServer);
pvt.SetField("_connection", conn.Object);
//------------Execute Test---------------------------
sqlServer.CreateCommand();
conn.Verify(a => a.CreateCommand());
}
finally
{
sqlServer.Dispose();
}
}
public void SqlServer_FetchDataSet_CorrectDataReturned()
{
//------------Setup for test--------------------------
var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
var sqlServer = new SqlServer();
try
{
sqlServer.Connect(dbSource.ConnectionString, CommandType.StoredProcedure, "Pr_CitiesGetCountries");
//------------Execute Test---------------------------
var actualDataSet = sqlServer.FetchDataSet(new SqlParameter("@Prefix", "a"));
//------------Assert Results-------------------------
Assert.AreEqual(1, actualDataSet.Tables.Count);
var actual = actualDataSet.Tables[0];
SqlServerTestUtils.Verify_DataTable_CountriesPrefixIsA(actual);
}
finally
{
sqlServer.Dispose();
}
}
