protected void YesButton_Click(object sender, System.EventArgs e)
{
SqlServer server = SqlServer.CurrentServer;
server.Connect();
SqlDatabase database = SqlDatabase.CurrentDatabase(server);
SqlStoredProcedure sproc = database.StoredProcedures[Request["sproc"]];
if (sproc == null)
{
server.Disconnect();
// Stored procedure doesn't exist - break out and go to error page
Response.Redirect(String.Format("error.aspx?error={0}", 1001));
return;
}
// Delete the sproc
sproc.Remove();
server.Disconnect();
// Redirect to info page
Response.Redirect("storedprocedures.aspx?database=" + Server.UrlEncode(Request["database"]));
}
public async Task SqlStoredProcedureCreateAndUpdate()
{
var storedProcedure = await CreateSqlStoredProcedure(null);
Assert.AreEqual(_storedProcedureName, storedProcedure.Data.Resource.Id);
// Seems bug in swagger definition
//Assert.AreEqual(TestThroughput1, container.Data.Options.Throughput);
bool ifExists = await SqlStoredProcedureCollection.ExistsAsync(_storedProcedureName);
Assert.True(ifExists);
// NOT WORKING API
//ThroughputSettingsData throughtput = await container.GetMongoDBCollectionThroughputAsync();
SqlStoredProcedure storedProcedure2 = await SqlStoredProcedureCollection.GetAsync(_storedProcedureName);
Assert.AreEqual(_storedProcedureName, storedProcedure2.Data.Resource.Id);
//Assert.AreEqual(TestThroughput1, container2.Data.Options.Throughput);
VerifySqlStoredProcedures(storedProcedure, storedProcedure2);
// TODO: use original tags see defect: https://github.com/Azure/autorest.csharp/issues/1590
SqlStoredProcedureCreateUpdateData updateOptions = new SqlStoredProcedureCreateUpdateData(AzureLocation.WestUS, storedProcedure.Data.Resource)
{
Options = new CreateUpdateOptions {
Throughput = TestThroughput2
}
};
storedProcedure = (await SqlStoredProcedureCollection.CreateOrUpdateAsync(WaitUntil.Completed, _storedProcedureName, updateOptions)).Value;
Assert.AreEqual(_storedProcedureName, storedProcedure.Data.Resource.Id);
storedProcedure2 = await SqlStoredProcedureCollection.GetAsync(_storedProcedureName);
VerifySqlStoredProcedures(storedProcedure, storedProcedure2);
}
protected void ScriptSprocs()
{
foreach (string sprocName in exportParams.SprocsToScript)
{
// export current sproc
SqlStoredProcedure sproc = database.StoredProcedures[sprocName];
if (sproc == null)
{
string msg = string.Format(
"Cannot find the stored procedure {0} in the database {1}",
sprocName, database.Name);
throw new SqlExporterException(msg);
}
// get sproc script
string sql = sproc.Script(
SqlScriptType.Create |
SqlScriptType.Drop |
SqlScriptType.Comments |
SqlScriptType.Permissions);
writer.WriteSprocScript(sproc.Name, sql);
UpdateProgress(sproc.Name);
}
}
public PMSUser ValidateGoogleUser(string username)
{
try
{
SqlStoredProcedure sp = new SqlStoredProcedure("[dbo].[usp_GetOAuthDetails]", ConfigManager.GetNewSqlConnection);
sp.AddParameterWithValue("@UserName", SqlDbType.VarChar, 256, ParameterDirection.Input, username);
var ds = sp.ExecuteDataSet();
if (ds.Tables.Count > 0)
{
PMSUser user = new PMSUser()
{
UserId = ds.Tables[0].Rows[0]["UserId"].ToString(),
UserName = ds.Tables[0].Rows[0]["UserName"].ToString(),
Email = ds.Tables[0].Rows[0]["Email"].ToString(),
UserFullName = ds.Tables[0].Rows[0]["UserFullName"].ToString()
};
return(user);
}
else
{
return(null);
}
}
catch (Exception ex)
{
return(null);
}
}
public List <Users> GetUserList()
{
// Create the list of user and return
try
{
SqlStoredProcedure sp = new SqlStoredProcedure("[dbo].[usp_UsersSelectAll]", ConfigManager.GetNewSqlConnection);
var ds = sp.ExecuteDataSet();
var myData = ds.Tables[0].AsEnumerable().Select(r => new Users
{
UserName = r.Field <string>("UserName"),
UserFullName = r.Field <string>("EmployeeFullName"),
Email = r.Field <string>("Email"),
PasswordHash = r.Field <string>("PasswordHash"),
UserId = r.Field <long>("UserId"),
ReleavingDate = r.Field <DateTime?>("ReleavingDate"),
UserType = r.Field <string>("UserType")
});
var list = myData.ToList();
return(list);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public void CreatesNewSoredProcedure()
{
SqlStoredProcedure someProc = new SqlStoredProcedure("aName");
Assert.AreEqual(someProc.StoredCommand.CommandText, "aName");
Assert.AreEqual(someProc.StoredCommand.CommandType, CommandType.StoredProcedure);
}
public void ReturnValuesWorks()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RETURN_TEST")
.WithParam("RetVal").AsReturnValue()
.Execute();
Assert.AreEqual(results["RetVal"], -4);
}
public void AddsNewProperties()
{
SqlStoredProcedure someProc = new SqlStoredProcedure("aName").WithParam("@SomeParam").As(SqlDbType.Bit).AsOutput();
Assert.AreEqual(someProc.Parameters.Count(), 1);
Assert.AreEqual(someProc.Parameters.Peek().Direction, System.Data.ParameterDirection.Output);
Assert.AreEqual(someProc.Parameters.Peek().ParameterName, "@SomeParam");
}
public Boolean bookingExists(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_EXISTS")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Exists").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Exists"]);
}
public Boolean deletePerson(Int32 id_guest)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_DELETE_PERSON")
.WithParam("@IdGuest").As(SqlDbType.Int).Value(id_guest)
.WithParam("@Deleted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Deleted"]);
}
public Boolean deleteRoom(Int32 id_room)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_DELETE_ROOM")
.WithParam("@IdRoom").As(SqlDbType.Int).Value(id_room)
.WithParam("@Deleted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Deleted"]);
}
public void ExecuteWithComeback()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RESULTS_TEST")
.Execute();
DataSet retVals = (DataSet)results["ReturnedValues"];
Assert.True(retVals.Tables[0].Rows.Count > 0);
}
public async Task TearDown()
{
SqlStoredProcedure storedProcedure = await SqlStoredProcedureCollection.GetIfExistsAsync(_storedProcedureName);
if (storedProcedure != null)
{
await storedProcedure.DeleteAsync(WaitUntil.Completed);
}
}
public Int32 getExtraNights(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_GET_EXTRA_NIGHTS")
.WithParam("IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Extra").As(SqlDbType.Int).AsOutput()
.Execute();
return((Int32)results["@Extra"]);
}
public Boolean setCheckOutGuests(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_CHECKED_OUT_GUESTS")
.WithParam("IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Seted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Seted"]);
}
public float getStayPrice(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_GET_STAY_PRICE")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Price").As(SqlDbType.Float).AsOutput()
.Execute();
return((float)results["@Price"]);
}
public Boolean bookingHasAllInclusive(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_ALL_INCLUSIVE")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Return").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Return"]);
}
public void HasNoComeback()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].OUTPUT_TEST")
.WithParam("@anOutput").AsOutput().As(SqlDbType.VarChar).WithMaximumSize(50)
.WithParam("@aValue").As(SqlDbType.Int).Value(10)
.WithParam("@anotherOutput").AsOutput().As(SqlDbType.Int)
.Execute();
results["ReturnedValues"].GetType();
}
public Boolean deleteBooking(Int32 id_booking, String descr)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_EXISTS")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Descr").As(SqlDbType.VarChar).Value(id_booking)
.WithParam("@Deleted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Deleted"]);
}
private void VerifySqlStoredProcedures(SqlStoredProcedure expectedValue, SqlStoredProcedure actualValue)
{
Assert.AreEqual(expectedValue.Id, actualValue.Id);
Assert.AreEqual(expectedValue.Name, actualValue.Name);
Assert.AreEqual(expectedValue.Resource.Id, actualValue.Resource.Id);
Assert.AreEqual(expectedValue.Resource.Rid, actualValue.Resource.Rid);
Assert.AreEqual(expectedValue.Resource.Ts, actualValue.Resource.Ts);
Assert.AreEqual(expectedValue.Resource.Etag, actualValue.Resource.Etag);
Assert.AreEqual(expectedValue.Resource.Body, actualValue.Resource.Body);
}
public void ReturnsValidDataTable()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RESULTS_TEST")
.AsDataTable()
.Execute();
DataTable retVals = (DataTable)results["ReturnedValues"];
Assert.True(retVals.Rows.Count > 0);
}
public bool PersonExistance(String docType, Decimal docNumber)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_PERSON_EXISTS")
.WithParam("@DocType").As(SqlDbType.VarChar).Value(docType)
.WithParam("@DocNumber").As(SqlDbType.Decimal).Value(docNumber)
.WithParam("@GuestExist").As(SqlDbType.Bit).AsOutput()
.Execute();
return((bool)results["@GuestExist"]);
}
public Boolean saveBill(Int32 id_booking, String payment)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_BILL")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@PaymentMode").As(SqlDbType.VarChar).Value(payment)
.WithParam("@Inserted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Inserted"]);
}
public Int32 addExtraToStay(Int32 id_booking, Int32 id_extra)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_EXTRA_STAY")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@IdExtra").As(SqlDbType.Int).Value(id_extra)
.WithParam("@IdExInserted").As(SqlDbType.Int).AsOutput()
.Execute();
return((Int32)results["@IdExInserted"]);
}
public Boolean insertHolderToBooking(Int32 id_booking, Int32 id_guest)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES]. SP_INSERT_HOLDER_BOOKING")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@IdGuest").As(SqlDbType.Int).Value(id_guest)
.WithParam("@Inserted").As(SqlDbType.Bit).AsOutput()
.Execute();
return((Boolean)results["@Inserted"]);
}
public void userLoginIsNotCorrect()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].validateUserPass")
.WithParam("@User").As(SqlDbType.VarChar).Value(user.ToString())
.WithParam("@Pass").As(SqlDbType.VarChar).Value("WRONG")
.WithParam("@RESULT").As(SqlDbType.Bit).AsOutput()
.Execute();
Assert.False((Boolean)results["@RESULT"]);
}
public DataTable extraBilled(DateTime from, DateTime to)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_EXTRA_BILLED")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return((DataTable)results["@ReturnedValues"]);
}
public DataTable outService(DateTime from, DateTime to)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_OUT_SERVICE")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return((DataTable)results["@ReturnedValues"]);
}
public DataTable guestsPoints(DateTime from, DateTime to)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_GUESTS_POINTS")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return((DataTable)results["@ReturnedValues"]);
}
public DataTable occupiedRooms(DateTime from, DateTime to)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_OCCUPIED_ROOMS")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return((DataTable)results["@ReturnedValues"]);
}
