Amol Patil

DECLARE @my int DECLARE @myDeduct int DECLARE @day INT DECLARE @mydate DATETIME SET @mydate = getdate() SET @myDeduct = 0 SET DateFirst 1 -- Set it monday=1 (value) --Saturday and Sunday on the first and last day of a month will Deduct 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5) SET @myDeduct = @myDeduct + 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5) SET @myDeduct = @myDeduct + 1 SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate))) select (((@my/7) * 5 + (@my%7)) - @myDeduct) as Working_Day_for_month Output:- 23 days for ( August 2011)

Step 1:- Create SQL Function /****** Object: UserDefinedFunction [dbo].[getTotalDaysInMonth] ******/ SET ANSI_ NULL S ON GO SET QUOTED_IDENTIFIER ON GO   -- ============================================= -- Author: -- Create date: <25th July, 2009> -- Description: -- ============================================= CREATE FUNCTION [ dbo ] . [ getTotalDaysInMonth ] ( -- Add the parameters for the function here @anydateofMonth DATETIME ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @totalDaysInMonth INT -- Add the T-SQL statements to compute the return value here   DECLARE @givendate DATETIME SET @givendate = @anydateofMonth   SET @givendate = STR ( YEAR ( @givendate ) ) + '-' + STR ( MONTH ( @givendate ) + 1 ) + '-01'   SELECT @totalDaysInMonth = DATEPART ( dd, DATEADD ( DAY , - 1 , @givendate ) )   -- Return the result of the f...

CREATE PROCEDURE dbo.SearchClients ( @ClientFirstName varchar ( 20 ) = null , @ClientLastName varchar ( 20 ) = null , @ClientHasTattoo bit = null , @JobFinished bit = null , @JobTypeID smallint = null , @ArtistID Smallint = null , @JobTotalHoursMinimum float = null , @JobTotalHoursMaximum float = null , @AdvertisingID smallint = null , @ClientDOBMinimum datetime = null , @ClientDOBMaximum datetime = null , @ClientStreetAddress varchar ( 75 ) = null , @ClientCity varchar ( 20 ) = null , @ClientState varchar ( 2 ) = null , @ClientZip varchar ( 10 ) = null , @ClientHomePhone varchar ( 14 ) = null , @ClientWorkPhone varchar ( 14 ) = null , @ClientEmail varchar ( 50 ) = null , @ClientSex bit = null , @JobDateMinimum datetime = null , @JobDateMaximum datetime = null , @JobTitle varchar ( 50 ) = null ) AS IF @JobFinished IS NULL AND @JobTypeID IS NULL AND @ArtistID IS NULL AND @JobTotalHoursMaximum IS NULL A...

DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) , 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) , 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) , 'First Day of Next Month'

DECLARE @EndOfPrevWeek DateTime DECLARE @StartOfPrevWeek DateTime --get number of a current day (1-Monday, 2-Tuesday... 7-Sunday) SET @TodayDayOfWeek = datepart(dw, GetDate()) print @TodayDayOfWeek --get the last day of the previous week (last Sunday) SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate()) print @EndOfPrevWeek --get the first day of the previous week (the Monday before last) SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate()) print @StartOfPrevWeek --get the last day of the previous week (last Sunday) print DATEADD(dd, 7-@TodayDayOfWeek, GetDate()) --get the first day of the previous week (the Monday before last) print DATEADD(dd, -(@TodayDayOfWeek-1), GetDate())

protected void btnnext_Click(object sender, EventArgs e) {     string[] objRetVals;         string str = readHtmlPage("https://secure.authorize.net/gateway/transact.dll");         objRetVals = Convert.ToString(str).Split('|');         if (objRetVals[0].ToString() == "1")  //--------------------- 1 means successfull payment done         {           string transactionid = objRetVals[6].ToString();           // code for save order in database         } } private String readHtmlPage(string url)     {         String result = "";         String strPost = "x_login=authorized.net_login_name&x_tran_key=TransactionKey&x_method=CC&x_type=AUTH_CAPTURE&x_amount=" + hfototal.Value + "&x_delim_data=TRUE&x_delim_char=|&x_relay_response=FAL...

Finding Country Details from Visitors IP in Asp.net, C# I want to develop a functionality which gives me Visitor's Country details in Asp.net Display Flag of Visitors Country based on Visitors IP Address Gives Information about Visitors Country Currency Information It should also provide Capital details of Visitors Country based on Visitors IP Address. So lets understand step by step how we can Find Visitors Country Details based on Visitors IP Address Step 1: Finding Visitor's IP Address in Asp.net, C# Code. You can use following Code to find Visitors IP Address in Asp.net string VisitorsIPAddr = string .Empty; //Users IP Address. if (HttpContext.Current.Request.ServerVariables[ "HTTP_X_FORWARDED_FOR" ] != null ) { //To get the IP address of the machine and not the proxy VisitorsIPAddr = HttpContext.Current.Request.ServerVariables[ "HTTP_X_FORWARDED_FOR" ].ToString(); } else if (HttpContext.Current.Request.UserHostAddress...

Twitter Tweet Posting from Asp. net Web Application using OAuth. Main Steps 1) Create Twitter Application. 2) Post Tweet from Asp.net Website using Twitterizer Api Now, lets understand each steps in more details, step by step. Create Twitter Application 1) Open  http://twitter.com/apps/new 2) Login to twitter and Fill the Application Information as follow.  - Choose Image for your Twitter Application.  - Fill in Twitter Application Name.  - Description of Twitter Application  - Fill in Application Website name, Organization - Choose Application Type as "Browser" as we are developing asp.net website to post tweet. - Callback Url is url where twitter would redirect after authentication.     - Fill in rest of details as shown in figure and your Twitter Application is ready to use. Now, lets begin the important part, how to post tweet. Post Tweet from Asp.net ...

• Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see. • Try to use constraints instead of triggers, whenever possible. Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible. • Use table variables instead of temporary tables. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only. • Try to use UNION ALL statement instead of UNION, whenever possible. The UNION ALL statement is much faster than UNION, because UNION ALL stat...

Disable Session Stat e Output Buffering Avoid Server-Side Validation Repeater Control Good, DataList, DataGrid, and DataView controls Bad Take advantage of HttpResponse.IsClientConnected before performing a large operation Use HTTPServerUtility.Transfer instead of Response.Redirect Always check Page.IsValid when using Validator Controls Deploy with Release Build Turn off Tracing Page.IsPostBack is your friend Avoid Exceptions Caching is Possibly the number one tip! Create Per-Request Cache Use of StringBuilder for String Manipulation Turn Off ViewState Use Paging Use the AppOffline.htm when updating binaries Use ControlState and not ViewState for Controls Use the Finally Method Option Strict and Option Explicit

.Net Framework 4.0 comes up with some of major changes as compare to previous versions of .Net Framework 3.5 and 2.0 Following are list of Major Changes in .Net 4.0 ControlRenderingCompatabilityVersion Setting in the Web.config File  ClientIDMode Changes  HtmlEncode and UrlEncode Now Encode Single Quotation Marks  ASP. NET Page (.aspx) Parser is Stricter  Browser Definition Files Updated  System.Web.Mobile.dll Removed from Root Web Configuration File  ASP.NET Request Validation   Default Hashing Algorithm Is Now HMACSHA256  Configuration Errors Related to New ASP .NET 4 Root Configuration  ASP.NET 4 Child Applications Fail to Start When Under ASP.NET 2.0 or ASP.NET 3.5 Applications  ASP.NET 4 Web Sites Fail to Start on Computers Where SharePoint Is Installed  The HttpRequest.FilePath Property No Longer Includes PathInfo Values  ASP.NET 2.0 Applications Might Generate HttpException Errors that Reference eurl.ax...

Data Exchange between SQL server & Excel   Sometime we need to pass data to & fro from excel sheet and SQL server. To accomplish this task we can write two functions that one that import data from excel sheet to your table in database here we can use bulk copy class. We could Column mapping to map column from excel sheet to column from database.   Many time we have Identity on First column we should not include that column in our excel sheet which we are going to import into database table. We need to have fair understanding of constraint on column like unique key constraint, check constraint that should not get violated by data in excel sheet.      We will try out our trick on country table which can be created as follows CREATE TABLE [dbo].[Country](     [CountryId] [int] IDENTITY(1,1) NOT NULL,     [CountryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,     [CountryCap...

Introduction to LINQ: LINQ stands for Language Integrated Query. This is new technology in latest .Net Framework 3.5. “Microsoft original motivation behind LINQ was to address the impedance mismatch between programming languages and database.” -Anders Hejlsberg What is LINQ? If someone wants to develop database application on .Net platform the very simple approach he uses ADO.Net. ADO.Net is serving as middle ware in application and provides complete object oriented wrapper around the database SQL. Developing application in C# and VB.Net, so developer must have good knowledge of object oriented concept as well as SQL, so it means developer must be familiar with both technologies to develop an application. LINQ is a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities. LINQ...

Finding Missing Auto-Increment Id from table: 1)Step First: We first create table as follows: create table missingGap(id int identity(1,1),val varchar(50)) 2)Step Two: Now we have to insert few records ,it’s quite state forward insert into missingGap(val)values(1) insert into missingGap(val)values(2) insert into missingGap(val)values(3) insert into missingGap(val)values(4) insert into missingGap(val)values(5) insert into missingGap(val)values(6) insert into missingGap(val)values(7) 3)Step Three : Lets see what is in data store: select * from missingGap id Val 1 1 2 2 3 3 4 4 5 5 6 6 7 7 4) Step Four : Delibrately Create A gap in auto increment id by deleting few records delete from missinggap where id in (2,3,4) id Val 1 1 5 5 6 6 7 7 5) Step Five: Lets finding the missing Entry: select a.id+1 MissingFrom,min(b.id)-1 MissingTill from missingGap a,missingGap b where a.id group by a.id having a.id+1 < min(b.id) The Result: + ------------...

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace MSACCESS { class Program { static void Main(string[] args) { string strDestPath = "I:\\Trial\\school.mdb"; SqlConnection sqlcon=new SqlConnection("Data Source=(Local);Integrated Security=SSPI;Initial Catalog=sangram") ; sqlcon.Open(); SqlCommand sqlcmd = new SqlCommand("select * from course", sqlcon); SqlDataReader sqldr = sqlcmd.ExecuteReader(); LoadFromSql(sqldr,strDestPath,"course"); Console.ReadKey(); } private static void LoadFromSql(SqlDataReader dr1, string Path,string table) { OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source = " + Path ); olecon.Open(); OleDbCommand olecmd = new OleDbCommand(); olecmd.Connection = olecon; string strCno,strCname,strDuration,strFees; while (dr1.Read()) { string ...

ASPX code: using Rad grid <radg:GridTemplateColumn UniqueName="TemplateColumn" HeaderText="Zip Code Count" DataField="ZipCount" >    <ItemTemplate>      <asp:LinkButton runat="server" ID="lnkZipCount" Text=' <% #Eval("ZipCount") %> ' CommandArgument=' <% #Eval("CityID") + ";" +Eval("Sname") %> ' OnClick="lnkZipCount_Click"></asp:LinkButton>    </ItemTemplate>   </radg:GridTemplateColumn> Cs code protected void lnkFunctionalGroup_Click(object sender, EventArgs e)     {         LinkButton lnkPortal = (LinkButton)sender;         string info = lnkPortal.CommandArgument;         string[] arg=new string[2];         char[] splitter= {';'};         arg = info.Split(splitter);      ...