TODO:
Have you ever wanted to know which verbs to use when defining your OperationContract in a WCF service?
SOLUTION:
// Get an employee by ID - GET - employee/id ( [WebGet(UriTemplate = "/employee/{id}")] )
// Create a new employee - POST - employee [WebInvoke(Method = "POST", UriTemplate = "/employee/{xml}")] )
// Update an employee - PUT - employee/id [WebInvoke(Method = "PUT", UriTemplate = "/employee/{xml}")]
// Delete an employee - DELETE - employee/id [WebInvoke(Method = "DELETE", UriTemplate = "/employee/{id}")]
NOTES:
There are no notes on this topic
TODO:
You try to view your Restful WCF service in a browser from Visual Studio and get the "Endpoint not found" error.
SOLUTION:
Right click on your .svc file. Look for "Factory="= "System.ServiceModel.Activation.WebServiceHostFactory" and delete it. You can now Right click your .svc file and choose "View in browser"
NOTES:
There are no notes on this topic
TODO:
You are attempting to query a table, and you receive the message: The relationship 'x' was not loaded because the type 'x' is not available.
SOLUTION:
Ensure your connection string is exists and is valid. If you renamed your model be sure to change the .csdl and .ssdl entry in the connection string. Once you correct the connection string, the error will go away.
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to call a stored procedure in C# that has output parameters?
SOLUTIONS:
System.Data.Objects.ObjectParameter param1 = new ObjectParameter("paraminsp1", typeof(bool));
System.Data.Objects.ObjectParameter param2 = new ObjectParameter("paraminsp2", 0.000); //default so EF works
using (MyEntities MyDatabase = new MyEntities())
{
MyDatabase.sp_name(x, 2, param1, param2);
}
NOTES:
There are no notes on this topic
TODO:
You are migrating old code to the latest version of Entity Framework, and you no longer have the Detach() method. To fix, use the code below.
SOLUTION:
((IObjectContextAdapter)myModel).ObjectContext.Detach(myobject);
NOTES:
There are no notes on this topic.
TODO:
You have purchased a new Motorola BLINK Baby Monitor and you are having issues viewing the camera remotely.
SOLUTION:
I am listing the things I have learned as there are numerous issues I encountered.
1. ***IMPORTANT*** - Even though you camera will connect to a WPA2 wireless network, this will cause the camera to not be able to be viewed remotely. You will receive a message about "Connecting through relay", or other meaningless messages.
2. Make sure UPnP is enabled on your wireless network.
3. Ensure wireless isolation is OFF.
NOTES:
#1 was the kicker for me. Luckily my wifi allows WPA and WPA2 at the same time. Once I flicked the switch, it instantly joined up remotely. This will also fix issues when viewing locally.
TODO:
Have you ever wanted to perform a join during an Update using T-SQL?
SOLUTION:
UPDATE
Person
SET
Person.Name = PersonArchive.Name
FROM
Person
INNER JOIN
PersonArchive
ON
Person.Id = PersonArchive.Id
NOTES:
You can also add a WHERE clause as to not update all records.
TODO:
Have you ever wanted to use a CASE statement in your T-SQL Select Query?
SOLUTION:
SELECT Column =
CASE
WHEN SomeValue = 0 THEN 'Zero'
WHEN SomeValue < 0 THEN 'Minus'
ELSE 'Plus'
END
FROM MyTable
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?
SOLUTION:
Step 1: Create the User-Definied Table Type in SQL Server
CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
[Id] [int] NULL,
[FieldOne] [int] NULL,
[FieldTwo] [int] NULL
)
Step 2: Create the DataTable and call the Stored Procedure
using (DataTable myDataTable = new DataTable())
{
myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
DataRow dataRow = null;
for (int i = 0; i < 100; i++)
{
dataRow = myDataTable.NewRow();
dataRow[“FieldOne] = 1;
dataRow[“FieldTwo”] = 2;
dataRow[“FieldThree] = 3;
myDataTable.Rows.Add(dataRow);
}
using (SqlConnection connection = new SqlConnection(“connectionstring”))
{
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 90;
command.CommandText = “MyStoredProcedureName;
command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
command.ExecuteNonQuery();
}
}
}
Step 3: Create the Table
CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)
Step 4: Create the Stored Procedure
CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY
AS
BEGIN
INSERT INTO dbo.MyTable
([FieldOne], [FieldTwo], [FieldThree])
SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType
END
NOTES:
Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.
TODO:
Have you ever wanted to shrink a large SQL Server database in small increments?
SOLUTION:
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'MyDB'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 100
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
exec ( @sql )
if @@ERROR <> 0
begin
print @@ERROR
break
end
print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
NOTES:
There are no notes on this topic