Friday, December 30, 2016

data-tech FaxMan,FaxMan Jr and interfax alternative for sending fax

Last couple of day i spend time on sending Fax in C#.
i just find out 2 service provider can send fax.

data-tech's FaxMan or FaxMan Jr
 http://www.data-tech.com/products/fax.aspx 

and 3rd party web api
https://www.interfax.net/en

data-tech Fax SDK is not working on windows 10 and interfax API is not free for sending fax.

You easily send fax using Faxcomlib. for details please check the sample.

https://code.msdn.microsoft.com/C-Send-Fax-using-fax-Modem-357aca81


Thank you.


Saturday, December 24, 2016

Common T-SQL


Recently i have completed  online training program on T-SQL from Edx.
I just share in my blog that will cover all basic T-SQL.


 -- Display all columns for all customers  
 SELECT * FROM SalesLT.Customer;  
 -- Display customer name fields  
 SELECT Title, FirstName, MiddleName, LastName, Suffix  
 FROM SalesLT.Customer;  
 -- Display title and last name with phone number  
 SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone  
 FROM SalesLT.Customer;  



 -- Customer Companies  
 SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany  
 FROM SalesLT.Customer;  
 --Sales Order Revisions  
 SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,  
        CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate  
 FROM SalesLT.SalesOrderHeader;  


 -- Get middle names if known  
 SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerName  
 FROM SalesLT.Customer;  
 -- Get primary contact details  
 UPDATE SalesLT.Customer  
 SET EmailAddress = NULL  
 WHERE CustomerID % 7 = 1;  
 SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact  
 FROM SalesLT.Customer;  
 -- Get shipping status  
 UPDATE SalesLT.SalesOrderHeader  
 SET ShipDate = NULL  
 WHERE SalesOrderID > 71899;  
 SELECT SalesOrderID, OrderDate,  
   CASE  
    WHEN ShipDate IS NULL THEN 'Awaiting Shipment'  
    ELSE 'Shipped'  
   END AS ShippingStatus  
 FROM SalesLT.SalesOrderHeader;  


 --Retrieve City List  
 SELECT DISTINCT City, StateProvince  
 FROM SalesLT.Address  
 --Retrieve Heaviest Products  
 SELECT TOP 10 PERCENT Name FROM SalesLT.Product ORDER BY Weight DESC;  
 --Retrieve the Heaviest 100 Products Not Including the Heaviest Ten  
 SELECT Name FROM SalesLT.Product ORDER BY Weight DESC  
 OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;  


 --Retrieve Product Details  
 SELECT Name, Color, Size  
 FROM SalesLT.Product  
 WHERE ProductModelID = 1;  
 --Retrieve Products by Color and Size  
 SELECT ProductNumber, Name  
 FROM SalesLT.Product  
 WHERE Color IN ('Black','Red','White') and Size IN ('S','M');  
 --Retrieve Products by Product Number  
 SELECT ProductNumber, Name, ListPrice  
 FROM SalesLT.Product   
 WHERE ProductNumber LIKE 'BK-%';  
 --Retrieve Specific Products by Product Number  
 SELECT ProductNumber, Name, ListPrice  
 FROM SalesLT.Product   
 WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';  



 -- Customer Orders  
 SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.SalesOrderHeader AS oh  
 ON oh.CustomerID = c.CustomerID;  
 -- Customer Orders with Addresses  
 SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,  
        a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.SalesOrderHeader AS oh  
 ON oh.CustomerID = c.CustomerID  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID;  


 -- All customers and their orders  
 SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue  
 FROM SalesLT.Customer AS c  
 LEFT JOIN SalesLT.SalesOrderHeader AS oh  
 ON c.CustomerID = oh.CustomerID  
 ORDER BY oh.SalesOrderID DESC;  
 -- Customers with no address  
 SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone  
 FROM SalesLT.Customer AS c  
 LEFT JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 WHERE ca.AddressID IS NULL;  
 -- Customers and products for which there are no orders  
 SELECT c.CustomerID, p.ProductID  
 FROM SalesLT.Customer AS c  
 FULL JOIN SalesLT.SalesOrderHeader AS oh  
 ON c.CustomerID = oh.CustomerID  
 FULL JOIN SalesLT.SalesOrderDetail AS od  
 ON od.SalesOrderID = oh.SalesOrderID  
 FULL JOIN SalesLT.Product AS p  
 ON p.ProductID = od.ProductID  
 WHERE oh.SalesOrderID IS NULL  
 ORDER BY ProductID, CustomerID;  


 -- Billing addresses  
 SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Main Office';  
 -- Shipping addresses  
 SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Shipping';  
 -- All customer addresses  
 SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Main Office'  
 UNION ALL  
 SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Shipping'  
 ORDER BY c.CompanyName, AddressType;  


 -- Customers with only a main office address  
 SELECT c.CompanyName  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Main Office'  
 EXCEPT  
 SELECT c.CompanyName  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Shipping'  
 ORDER BY c.CompanyName;  
 -- Only customers with both a main office and a shipping address  
 SELECT c.CompanyName  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Main Office'  
 INTERSECT  
 SELECT c.CompanyName  
 FROM SalesLT.Customer AS c  
 JOIN SalesLT.CustomerAddress AS ca  
 ON c.CustomerID = ca.CustomerID  
 JOIN SalesLT.Address AS a  
 ON ca.AddressID = a.AddressID  
 WHERE ca.AddressType = 'Shipping'  
 ORDER BY c.CompanyName;  


 --Retrieve the nname and approximate weight of each product  
 SELECT     ProductID,  
           UPPER(Name) AS ProductName,  
           ROUND(Weight, 0) AS ApproxWeight  
 FROM SalesLT.Product;  
 --Retrieve the month and year products were first sold  
 SELECT     ProductID,  
           UPPER(Name) AS ProductName,  
           ROUND(Weight, 0) AS ApproxWeight,  
           YEAR(SellStartDate) as SellStartYear,  
           DATENAME(m, SellStartDate) as SellStartMonth  
 FROM SalesLT.Product;  
 -- Extract type from product number  
 SELECT     ProductID,  
           UPPER(Name) AS ProductName,  
           ROUND(Weight, 0) AS ApproxWeight,  
           YEAR(SellStartDate) as SellStartYear,  
           DATENAME(m, SellStartDate) as SellStartMonth,  
           LEFT(ProductNumber, 2) AS ProductType  
 FROM SalesLT.Product;  
 -- Filter to include only products with numeric sizes  
 SELECT     ProductID,  
           UPPER(Name) AS ProductName,  
           ROUND(Weight, 0) AS ApproxWeight,  
           YEAR(SellStartDate) as SellStartYear,  
           DATENAME(m, SellStartDate) as SellStartMonth,  
           LEFT(ProductNumber, 2) AS ProductType  
 FROM SalesLT.Product  
 WHERE ISNUMERIC(Size)=1;  


 --Retrieve Companies Ranked by Revenue  
 SELECT     CompanyName,  
           TotalDue AS Revenue,  
           RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue  
 FROM SalesLT.SalesOrderHeader AS SOH  
 JOIN SalesLT.Customer AS C  
 ON SOH.CustomerID=C.CustomerID;  


 --Retrieve Total Sales by Product  
 SELECT Name,SUM(LineTotal) AS TotalRevenue  
 FROM SalesLT.SalesOrderDetail AS SOD  
 JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID  
 GROUP BY P.Name  
 ORDER BY TotalRevenue DESC;  
 -- Only products that cost over $1,000  
 SELECT Name,SUM(LineTotal) AS TotalRevenue  
 FROM SalesLT.SalesOrderDetail AS SOD  
 JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID  
 WHERE P.ListPrice > 1000  
 GROUP BY P.Name  
 ORDER BY TotalRevenue DESC;  
 --Only groupings with sales totals over $20,000  
 SELECT Name,SUM(LineTotal) AS TotalRevenue  
 FROM SalesLT.SalesOrderDetail AS SOD  
 JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID  
 WHERE P.ListPrice > 1000  
 GROUP BY P.Name  
 HAVING SUM(LineTotal) > 20000  
 ORDER BY TotalRevenue DESC;  


 --Retrieve Products whose list price is higher than the average unit price in the SalesOrderDetail table  
 SELECT ProductID, Name, ListPrice from SalesLT.Product  
 WHERE ListPrice >  
 (SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)  
 ORDER BY ProductID;  
 --Retrieve products that are priced $100 or more  
 -- but have sold for a unit price of less than $100  
 SELECT ProductID, Name, ListPrice FROM SalesLT.Product  
 WHERE ProductID IN  
 (SELECT ProductID from SalesLT.SalesOrderDetail  
  WHERE UnitPrice < 100.00)  
 AND ListPrice >= 100.00  
 ORDER BY ProductID;  
 --Retrieve cost, list price, and average selling price for each product  
 SELECT ProductID, Name, StandardCost, ListPrice,  
      (SELECT AVG(UnitPrice)  
       FROM SalesLT.SalesOrderDetail AS SOD  
       WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice  
 FROM SalesLT.Product AS P  
 ORDER BY P.ProductID;  
 --Find products where the average selling price is less than cost  
 SELECT ProductID, Name, StandardCost, ListPrice,  
 (SELECT AVG(UnitPrice)  
  FROM SalesLT.SalesOrderDetail AS SOD  
  WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice  
 FROM SalesLT.Product AS P  
 WHERE StandardCost >  
 (SELECT AVG(UnitPrice)  
  FROM SalesLT.SalesOrderDetail AS SOD  
  WHERE P.ProductID = SOD.ProductID)  
 ORDER BY P.ProductID;  


 --Retrieve sales order data with customer information from a function  
 SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue  
 FROM SalesLT.SalesOrderHeader AS SOH  
 CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI  
 ORDER BY SOH.SalesOrderID;  
 --Retrieve addresses with customer information from a function  
 SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City  
 FROM SalesLT.Address AS A  
 JOIN SalesLT.CustomerAddress AS CA  
 ON A.AddressID = CA.AddressID  
 CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI  
 ORDER BY CA.CustomerID;  


 -- Retrieve product model descriptions  
 SELECT P.ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary  
 FROM SalesLT.Product AS P  
 JOIN SalesLT.vProductModelCatalogDescription AS PM  
 ON P.ProductModelID = PM.ProductModelID  
 ORDER BY ProductID;  
 -- Create a table of distinct colors  
 DECLARE @colors AS TABLE (Color nvarchar(15));  
 INSERT INTO @Colors  
 SELECT DISTINCT Color FROM SalesLT.Product;  
 SELECT ProductID, Name, Color  
 FROM SalesLT.Product  
 WHERE Color IN (SELECT Color FROM @Colors);  
 -- Retrieve product parent categories from a function  
 SELECT C.ParentProductCategoryName AS ParentCategory,  
     C.ProductCategoryName AS Category,  
     P.ProductID, P.Name AS ProductName  
 FROM SalesLT.Product AS P  
 JOIN dbo.ufnGetAllCategories() AS C  
 ON P.ProductCategoryID = C.ProductCategoryID  
 ORDER BY ParentCategory, Category, ProductName;  


 -- Get sales revenue by company and contact (using derived table)  
 SELECT CompanyContact, SUM(SalesAmount) AS Revenue  
 FROM  
      (SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue  
       FROM SalesLT.SalesOrderHeader AS SOH  
       JOIN SalesLT.Customer AS c  
       ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)  
 GROUP BY CompanyContact  
 ORDER BY CompanyContact;  
 -- Get sales revenue by company and contact (using CTE)  
 WITH CustomerSales(CompanyContact, SalesAmount)  
 AS  
 (SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue  
  FROM SalesLT.SalesOrderHeader AS SOH  
  JOIN SalesLT.Customer AS c  
  ON SOH.CustomerID = c.CustomerID)  
 SELECT CompanyContact, SUM(SalesAmount) AS Revenue  
 FROM CustomerSales  
 GROUP BY CompanyContact  
 ORDER BY CompanyContact;  


 --Initial query  
 SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue  
 FROM SalesLT.Address AS a  
 JOIN SalesLT.CustomerAddress AS ca  
 ON a.AddressID = ca.AddressID  
 JOIN SalesLT.Customer AS c  
 ON ca.CustomerID = c.CustomerID  
 JOIN SalesLT.SalesOrderHeader as soh  
 ON c.CustomerID = soh.CustomerID  
 GROUP BY a.CountryRegion, a.StateProvince  
 ORDER BY a.CountryRegion, a.StateProvince;  
 -- With totals  
 SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue  
 FROM SalesLT.Address AS a  
 JOIN SalesLT.CustomerAddress AS ca  
 ON a.AddressID = ca.AddressID  
 JOIN SalesLT.Customer AS c  
 ON ca.CustomerID = c.CustomerID  
 JOIN SalesLT.SalesOrderHeader as soh  
 ON c.CustomerID = soh.CustomerID  
 GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)  
 ORDER BY a.CountryRegion, a.StateProvince;  
 -- Subtotal levels  
 SELECT a.CountryRegion, a.StateProvince,  
 IIF(GROUPING_ID(a.CountryRegion) = 1 AND GROUPING_ID(a.StateProvince) = 1, 'Total', IIF(GROUPING_ID(a.StateProvince) = 1, a.CountryRegion + ' Subtotal', a.StateProvince + ' Subtotal')) AS Level,  
 SUM(soh.TotalDue) AS Revenue  
 FROM SalesLT.Address AS a  
 JOIN SalesLT.CustomerAddress AS ca  
 ON a.AddressID = ca.AddressID  
 JOIN SalesLT.Customer AS c  
 ON ca.CustomerID = c.CustomerID  
 JOIN SalesLT.SalesOrderHeader as soh  
 ON c.CustomerID = soh.CustomerID  
 GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)  
 ORDER BY a.CountryRegion, a.StateProvince;  
 -- Including cities  
 SELECT a.CountryRegion, a.StateProvince, a.City,  
 CHOOSE (1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City), a.City + ' Subtotal', a.StateProvince + ' Subtotal', a.CountryRegion + ' Subtotal', 'Total') AS Level,  
 SUM(soh.TotalDue) AS Revenue  
 FROM SalesLT.Address AS a  
 JOIN SalesLT.CustomerAddress AS ca  
 ON a.AddressID = ca.AddressID  
 JOIN SalesLT.Customer AS c  
 ON ca.CustomerID = c.CustomerID  
 JOIN SalesLT.SalesOrderHeader as soh  
 ON c.CustomerID = soh.CustomerID  
 GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)  
 ORDER BY a.CountryRegion, a.StateProvince, a.City;  


 --Customer sales by parent category  
 SELECT * FROM  
 (SELECT cat.ParentProductCategoryName, cust.CompanyName, sod.LineTotal  
  FROM SalesLT.SalesOrderDetail AS sod  
  JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID  
  JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID  
  JOIN SalesLT.Product AS prod ON sod.ProductID = prod.ProductID  
  JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales  
 PIVOT (SUM(LineTotal) FOR ParentProductCategoryName IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales  
 ORDER BY CompanyName;  


 -- Insert a product  
 INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)  
 VALUES  
 ('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());  
 SELECT SCOPE_IDENTITY();  
 SELECT * FROM SalesLT.Product  
 WHERE ProductID = SCOPE_IDENTITY();  
 -- Insert a new category with two products  
 INSERT INTO SalesLT.ProductCategory (ParentProductCategoryID, Name)  
 VALUES  
 (4, 'Bells and Horns');  
 INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)  
 VALUES  
 ('Bicycle Bell', 'BB-RING', 2.47, 4.99, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE()),  
 ('Bicycle Horn', 'BH-PARP', 1.29, 3.75, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE());  
 SELECT c.Name As Category, p.Name AS Product  
 FROM SalesLT.Product AS p  
 JOIN SalesLT.ProductCategory as c ON p.ProductCategoryID = c.ProductCategoryID  
 WHERE p.ProductCategoryID = IDENT_CURRENT('SalesLT.ProductCategory');  


 -- Update product prices  
 UPDATE SalesLT.Product  
 SET ListPrice = ListPrice * 1.1  
 WHERE ProductCategoryID =  
      (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');  
 -- Discontinue products  
 UPDATE SalesLT.Product  
 SET DiscontinuedDate = GETDATE()  
 WHERE ProductCategoryID = 37  
 AND ProductNumber <> 'LT-L123';  


 -- Delete a product category and its products  
 DELETE FROM SalesLT.Product  
 WHERE ProductCategoryID =  
      (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');  
 DELETE FROM SalesLT.ProductCategory  
 WHERE ProductCategoryID =  
      (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');  


 -- Insert sales order header  
 DECLARE @OrderDate datetime = GETDATE();  
 DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());  
 DECLARE @CustomerID int = 1;  
 DECLARE @OrderID int;  
 SET @OrderID = NEXT VALUE FOR SalesLT.SalesOrderNumber;  
 INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)  
 VALUES  
 (@OrderID, @OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');  
 PRINT @OrderID;  
 -- Insert sales order details  
 DECLARE @SalesOrderID int  
 DECLARE @ProductID int = 760;  
 DECLARE @Quantity int = 1;  
 DECLARE @UnitPrice money = 782.99;  
 SET @SalesOrderID = 0; -- test with the order ID generated for the sales order header inserted above  
 IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)  
 BEGIN  
      INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)  
      VALUES  
      (@SalesOrderID, @Quantity, @ProductID, @UnitPrice)  
 END  
 ELSE  
 BEGIN  
      PRINT 'The order does not exist'  
 END  


 DECLARE @MarketAverage money = 2000;  
 DECLARE @MarketMax money = 5000;  
 DECLARE @AWMax money;  
 DECLARE @AWAverage money;  
 SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)  
 FROM SalesLT.Product  
 WHERE ProductCategoryID IN  
      (SELECT DISTINCT ProductCategoryID  
       FROM SalesLT.vGetAllCategories  
       WHERE ParentProductCategoryName = 'Bikes');  
 WHILE @AWAverage < @MarketAverage  
 BEGIN  
   UPDATE SalesLT.Product  
   SET ListPrice = ListPrice * 1.1  
   WHERE ProductCategoryID IN  
      (SELECT DISTINCT ProductCategoryID  
       FROM SalesLT.vGetAllCategories  
       WHERE ParentProductCategoryName = 'Bikes');  
      SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)  
      FROM SalesLT.Product  
      WHERE ProductCategoryID IN  
      (SELECT DISTINCT ProductCategoryID  
       FROM SalesLT.vGetAllCategories  
       WHERE ParentProductCategoryName = 'Bikes');  
   IF @AWMax >= @MarketMax  
    BREAK  
   ELSE  
    CONTINUE  
 END  
 PRINT 'New average bike price:' + CONVERT(varchar, @AWAverage);  
 PRINT 'New maximum bike price:' + CONVERT(varchar, @AWMax);  


 DECLARE @SalesOrderID int = 0  
 -- uncomment the following line to delete an existing record  
 -- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;   
 BEGIN TRY  
      IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader  
                       WHERE SalesOrderID = @SalesOrderID)  
      BEGIN  
           -- Throw a custom error if the specified order doesn't exist  
           DECLARE @error varchar(25);  
           SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';  
           THROW 50001, @error, 0  
      END  
      ELSE  
      BEGIN  
           DELETE FROM SalesLT.SalesOrderDetail  
           WHERE SalesOrderID = @SalesOrderID;  
           DELETE FROM SalesLT.SalesOrderHeader  
           WHERE SalesOrderID = @SalesOrderID;  
      END  
 END TRY  
 BEGIN CATCH  
      -- Catch and print the error  
      PRINT ERROR_MESSAGE();  
 END CATCH  


 DECLARE @SalesOrderID int = 0  
 -- uncomment the following line to specify an existing order  
 -- SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;   
 BEGIN TRY  
      IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader  
                       WHERE SalesOrderID = @SalesOrderID)  
      BEGIN  
           -- Throw a custom error if the specified order doesn't exist  
           DECLARE @error varchar(25);  
           SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';  
           THROW 50001, @error, 0  
      END  
      ELSE  
      BEGIN  
       BEGIN TRANSACTION  
           DELETE FROM SalesLT.SalesOrderDetail  
           WHERE SalesOrderID = @SalesOrderID;  
           -- THROW 50001, 'Unexpected error', 0 --Uncomment to test transaction  
           DELETE FROM SalesLT.SalesOrderHeader  
           WHERE SalesOrderID = @SalesOrderID;  
       COMMIT TRANSACTION  
      END  
 END TRY  
 BEGIN CATCH  
      IF @@TRANCOUNT > 0  
      BEGIN  
           -- Rollback the transaction and re-throw the error  
           ROLLBACK TRANSACTION;  
           THROW;  
      END  
      ELSE  
      BEGIN  
           -- Report the error  
           PRINT ERROR_MESSAGE();  
      END  
 END CATCH  


















































Tuesday, December 13, 2016

The function import 'DBEntities' cannot be executed because it is not mapped to a store function.


1. Open EDMX in to Design Mode
2. Click Model Browser then type the Store procedure name on search and press Enter.
   It will highlight the store procedure.






3. If you already try to add this store procedure, then in some case it create Function when you have a return value on your SP.  other wise it will not create the function. remove all from function.


3. If your SP has no return type then you may can use a simple return type value like :  
SELECT 1 as DefaultValue  if you already have return type then you don't need that.


4. Select the Store procedure from model browser-> right click -> Add function Import.
 


5. Check Complex and Click Get column Information


6. Click on Create New Complex type.







7. It will create Function and complex type for that Store Procedure.


Click OK and Build your application.

Friday, December 9, 2016

C# convert a DateTime to an int value




 long dtosDateValue = long.Parse(DateTime.Now.ToString("yyyyMMdd"));  
 long dtosTimeValue = long.Parse(DateTime.Now.ToString("HHmmss"));  
 long dtosDateTimeValue = long.Parse(DateTime.Now.ToString("yyyyMMddHHmmss"));  

Monday, October 31, 2016

sql auto increment jump

You resolve your auto increment  jumping from this


you can also resolve the issue into another way if you are using EDMX.

Before inserting data into table get the max id value from your table.


 int maxAge = context.Persons.Max(p => p.Age);  

Now add your increment number maxAge+1  

Map the id value with your table.

NB: You table Identity specification : Is Identity Will be no.

Monday, October 24, 2016

C# read word document and get specific data from document




  // Reding all word document from a specific folder  
       foreach (string file in Directory.EnumerateFiles(@"D:\", "*.doc"))  
       {  
         //string contents = File.ReadAllText(file);  
         Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();  
         object miss = System.Reflection.Missing.Value;  
         object path = file; // @"D:\35339.doc";  
         object readOnly = true;  
         Microsoft.Office.Interop.Word.Document docs = word.Documents.Open(ref path, ref miss, ref readOnly, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss, ref miss);  
         string totaltext = "";  
         for (int i = 0; i < 2; i++) // i set value 2 you can change  
         {  
           totaltext += " \r\n " + docs.Paragraphs[i + 1].Range.Text.ToString();  
         }  
         // Getting data after specific data from the string  
         string _findName = "Name :";  
         string code = totaltext.Substring((totaltext.IndexOf(_findName) + _findName.Length), 20);  
         string _findMrno = "MR. No.      :";  
         string mrNo = totaltext.Substring((totaltext.IndexOf(_findMrno) + _findMrno.Length), 12);  
         string _findAge = "Age     :";  
         string age = totaltext.Substring((totaltext.IndexOf(_findAge) + _findAge.Length), 6);  
         string _findDate = " Date           :";  
         string date = totaltext.Substring((totaltext.IndexOf(_findDate) + _findDate.Length));  
         // You code for insert or other things to do  
         docs.Close();  
         word.Quit();  
       }  



Wednesday, March 16, 2016

The calling thread cannot access this object because a different thread owns it.


On your do work

  private void worker_DoWork(object sender, DoWorkEventArgs e)  
     {  
       this.Dispatcher.Invoke((Action) (() =>  
       {  
         // Your code  
       }));  
     }  


Wednesday, March 9, 2016

C# - Process.Start a ClickOnce application





  string publisher_name = "ABCPublisher";  
       string product_name = "FaxManFMFFileCreator";  
       var shortcutName = string.Concat(Environment.GetFolderPath(Environment.SpecialFolder.Programs), "\\",publisher_name, "\\", product_name, ".appref-ms");  
       Process.Start(shortcutName);  




Devexpress report Export in to file

Test is the devexpress report and you can export in different format.


  XtraReport report = new Test();  
  report.CreateDocument();  
 report.ExportToText("D:\\test1.doc");  


C# kill specific process


  //Threading for speacific printer otherwise it will open default Faxman printer  
       Task.Factory.StartNew(() =>  
       {  
         Thread.Sleep(5000);  
         // Killing Process of default printer  
         Process[] processes = Process.GetProcessesByName("FaxManFMFFileCreator");  
         foreach (var process in processes)  
         {  
           process.Kill();  
         }  
       });  


Tuesday, March 8, 2016

The calling thread must be STA, because many UI components require this.


  // Create a thread  
       Thread newWindowThread = new Thread(new ThreadStart(() =>  
       {  
         // You can use your code  
         // Create and show the Window  
         FaxImageLoad obj = new FaxImageLoad(destination);  
         obj.Show();  
         // Start the Dispatcher Processing  
         System.Windows.Threading.Dispatcher.Run();  
       }));  
       // Set the apartment state  
       newWindowThread.SetApartmentState(ApartmentState.STA);  
       // Make the thread a background thread  
       newWindowThread.IsBackground = true;  
       // Start the thread  
       newWindowThread.Start();  

Monday, March 7, 2016

Mixed mode assembly is built against version 'v1.1.4322' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.":null

Create app.config and modify it


 <?xml version="1.0" encoding="utf-8" ?>  
 <configuration>  
  <startup useLegacyV2RuntimeActivationPolicy="true">  
   <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />  
  </startup>  
 </configuration>  



Thursday, January 28, 2016

devexpress wpf gridcontrol disable context menu


If you want to disable any context menu item in devexpress grid like this.



In XAML


  <dxg:GridControl x:Name="grdProfile"  
                    Width="1070"  
                    Height="400"  
                    HorizontalAlignment="Left"  
                    VerticalAlignment="Top"  
                    AutoGenerateColumns="None"  
                    ItemsSource="{Binding CusHisViewRefillHistoryList}"  
                    SelectedItem="{Binding SelectedCusHisViewRefillHistory}">  
             <dxg:GridControl.Columns>  
               <dxg:GridColumn Width="100"  
                       Binding="{Binding RxNo}"  
                       Header="Rx #" />  
               <dxg:GridColumn Width="70"  
                       Binding="{Binding PhInitials}"  
                       Header="Ph. Initials" />  
               <dxg:GridColumn Width="75"  
                       Binding="{Binding DispDate}"  
                       Header="Disp. Date">  
                 <dxg:GridColumn.EditSettings>  
                   <dxe:TextEditSettings DisplayFormat="MM/dd/yyyy" />  
                 </dxg:GridColumn.EditSettings>  
               </dxg:GridColumn>  
               <dxg:GridColumn Width="60"  
                       Binding="{Binding RefillNo}"  
                       Header="Ref. #" />  
               <dxg:GridColumn Width="60"  
                       Binding="{Binding RefillAuth}"  
                       Header="Ref Auth" />  
               <dxg:GridColumn Width="80"  
                       Binding="{Binding PlanName}"  
                       Header="Plan Name" />  
               <dxg:GridColumn Width="200"  
                       Binding="{Binding DispensedDrug}"  
                       Header="Dispensed Drug" />  
               <dxg:GridColumn Width="70"  
                       Binding="{Binding DispensedQuantity}"  
                       Header="Disp. Qty">  
                 <dxg:GridColumn.EditSettings>  
                   <dxe:TextEditSettings Mask="\d{1,10}\.\d{1,2}?"  
                              MaskType="RegEx"  
                              MaskUseAsDisplayFormat="True" />  
                 </dxg:GridColumn.EditSettings>  
               </dxg:GridColumn>  
               </dxg:GridControl.Columns>  
             <dxg:GridControl.View>  
               <dxg:TableView AlternateRowBackground="{StaticResource AlternateRowColour}" AlternationCount="2">  
                 <dxg:TableView.RowCellMenuCustomizations>  
                   <dxb:BarButtonItem Name="contexMenuShowAll1"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.ShowAllCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Show All" />  
                   <dxb:BarButtonItem Name="contexMenuShowSelectedDrugOnly"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.SelectedDrugCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Show Selected Drug Only" />  
                   <dxb:BarButtonItem Name="contexMenuShowSelectedRxOnly"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.SelectedForRefillCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Show Selected Rx Only" />  
                   <dxb:BarItemLinkSeparator />  
                   <dxb:BarButtonItem Name="contexMenuBillOtherInsurance"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.BillOtherInsurance,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Bill Other Insurance" />  
                   <dxb:BarButtonItem Name="contexMenuEditRx"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.EditRxCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Edit Rx" />  
                   <dxb:BarButtonItem Name="contexMenuReverse"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.RxReversalCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Reverse" />  
                   <dxb:BarButtonItem Name="contexMenuPrintLabel"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.PrintLabelCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Print Label" />  
                   <dxb:BarButtonItem Name="contexMenuPickupInfo"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.PickupInfoCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Pickup Info" />  
                   <dxb:BarItemLinkSeparator />  
                   <dxb:BarButtonItem Name="contexMenuChangeCashtoInsurance"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.ChangeCashToInsuranceCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Change Cash to Insurance" />  
                   <dxb:BarButtonItem Name="contexMenuChangeInsurancetoCash"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.ChangeInsuranceToCashCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Change Insurance to Cash" />  
                   <dxb:BarButtonItem Name="contexMenuTransmitPendingClaim"  
                             Command="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.TransmitPendingClaimCommand,  
                                      RelativeSource={RelativeSource Self}}"  
                             Content="Transmit Pending Claim"  
                             IsEnabled="{Binding Path=(dxb:GridPopupMenuBase.GridMenuInfo).View.DataContext.SelectedCusHisViewRefillHistory.IsPendingClaimsActive,  
                                       RelativeSource={RelativeSource Self},  
                                       Mode=TwoWay,  
                                       UpdateSourceTrigger=PropertyChanged}" />  
                 </dxg:TableView.RowCellMenuCustomizations>  
               </dxg:TableView>  
             </dxg:GridControl.View>  
           </dxg:GridControl>  


In VM:


 private CusHisViewRefillHistory _selectedCusHisViewRefillHistory;  
 public CusHisViewRefillHistory SelectedCusHisViewRefillHistory  
     {  
       get { return _selectedCusHisViewRefillHistory; }  
       set  
       {  
         _selectedCusHisViewRefillHistory = value;  
         RaisePropertyChanged("SelectedCusHisViewRefillHistory");  
         SelectDeSelectRxHistory();  
       }  
     }  
  private void SelectDeSelectRxHistory()  
     {  
       if (SelectedCusHisViewRefillHistory != null)  
       {  
         if (SelectedCusHisViewRefillHistory.paytypeid_FK=="P")  
         {  
           SelectedCusHisViewRefillHistory.IsPendingClaimsActive = false;  
         }  
         else  
         {  
           SelectedCusHisViewRefillHistory.IsPendingClaimsActive = true;  
         }  
       }  
     }            


SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT...