Basic Intro to Cursor in MS SQL

Cursor works as a foreach loop in ms sql.  
@@FETCH_STATUS = 0 -> The FETCH statement was successful if  ,
@@FETCH_STATUS = -1 -> The FETCH statement failed or the row was beyond the result set,
@@FETCH_STATUS = -2 -> The row fetched is missing.


DECLARE @ServiceId INT
    DECLARE @ContentId INT
    DECLARE @ContentExist INT = 1

    --cursor start
    DECLARE monitoring_cursor CURSOR
    FOR
    SELECT ID
        ,ContentID
    FROM dbo.[Services]
   
    OPEN monitoring_cursor

    FETCH NEXT
    FROM monitoring_cursor
    INTO @ServiceId
        ,@ContentId



    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF NOT EXISTS (
                SELECT *
                FROM dbo.ContentData
                WHERE ContentID = @ContentId
                  )
        BEGIN
            SET @ContentExist = 0;
        END

        IF @ContentExist = 0
        BEGIN
            INSERT INTO dbo.Monitoring (
                [Date]
                ,[Status]
                ,[Description]
                )
            VALUES (
                GETDATE()
                ,'Failed'
                ,'Service' + @ServiceId + 'no content'
                )
        END
        ELSE
        BEGIN
            INSERT INTO dbo.Monitoring (
                [Date]
                ,[Status]
                ,[Description]
                )
            VALUES (
                GETDATE()
                ,'OK'
                ,NULL
                )
        END

        FETCH NEXT
        FROM monitoring_cursor
        INTO @ServiceId
            ,@ContentId
    END

    CLOSE monitoring_cursor

    DEALLOCATE monitoring_cursor
        --- cursor end

Function To Check ASCII or UTF-8 Format

public string CheckEncoding(string input)
        {
            string returnValue = String.Empty;
            string sOut = Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(input));
            if (input == sOut)
            {
                return "ASCII";
            }
            else
            {
                var utf8Format = Encoding.UTF8;
                byte[] b = utf8Format.GetBytes(input);
                int taster = b.Length;
                bool checkUtf8 = false;
                if (b.Length >= 3 && b[0] == 0xEF && b[1] == 0xBB && b[2] == 0xBF)
                {
                   checkUtf8 = true;
                }
                else
                {
                    int i = 0;
                    while (i < taster - 4)
                    {
                        if (b[i] <= 0x7F)
                        {
                            i += 1;
                            continue;
                        }
                        if (b[i] >= 0xC2 && b[i] <= 0xDF && b[i + 1] >= 0x80 && b[i + 1] < 0xC0)
                        {
                            i += 2;
                            checkUtf8 = true;
                            continue;
                        }
                        if (b[i] >= 0xE0 && b[i] <= 0xF0 && b[i + 1] >= 0x80 && b[i + 1] < 0xC0 && b[i + 2] >= 0x80 &&
                            b[i + 2] < 0xC0)
                        {
                            i += 3;
                            checkUtf8 = true;
                            continue;
                        }
                        if (b[i] >= 0xF0 && b[i] <= 0xF4 && b[i + 1] >= 0x80 && b[i + 1] < 0xC0 && b[i + 2] >= 0x80 &&
                            b[i + 2] < 0xC0 && b[i + 3] >= 0x80 && b[i + 3] < 0xC0)
                        {
                            i += 4;
                            checkUtf8 = true;
                            continue;
                        }
                        checkUtf8 = false;
                        break;
                    }
                }
                if(checkUtf8 == true)
                {
                    returnValue = "UTF-8";
                }
            }
            return returnValue;
        }

Basic Introduction To ASP.NET - MVC 3 Framework

• First of all, install mvc 3 framework. Download From: http://www.asp.net/mvc/mvc3.
• After installing, Open Microsoft Visual Studio. Goto File and Create a New Project.
• Choose ASP.NET MVC 3 Web Application and choose the Empty Template / Internet Application.
• Now in Solution Explorer, right click the Controllers Folder and add Controller. (Lets name it HomeController). Also add a model in Model Folder (Lets name it HomeModel).
• Open the HomeController.cs and modifiy it as: (include using MvcApplication1.Models)

• Open SQL server and in your database, create a table. Here, infoTable is created as:

• Open the HomeModel.cs and modifiy it as: (include using MvcApplication1.Connection)

• Here, In Connection Folder , DatabaseConnection class method getConnection() returns connection string where you define your connection to your server,database.

• Then add a View by clicking inside public ActionResult Index() method.

• Open Index.cshtml and write the html code.

• demo.css looks like:

• Output :

NOTE:

• Content Folder include all css files and images that are being used by the project.
• Global.asax.cs  includes the public static void RegisterRoutes(RouteCollection routes) method where default controller and its view can be set as per required during build.

Creating and Deploying a Simple WCF service (VS'10-C#)

WCF (Windows Communication Foundation) services supports more protocols for transporting messages between software entities (including protocols: HTTP,TCP,MSMQ{Microsoft Message Queuing}).But ASP.NET Web Services only supports HTTP protocol.

STEP I 

Creating a table

• Open MS SQL Server, Goto Object Explorer tab , Select your Database and Create a table.
• Here I'm creating a table 'productTable ' in DREAMTECH (data source) -> allTest (Database) and Fill it as shown below:

   

Fig: productTable

STEP II

Creating a WCF Service

• Open Visual studio and Create File -> New Website-> WCF Service
• Expand App_Code Folder in Solution Explorer.
• Open IService.cs file and Comment all code which is inside public interface IService and rewrite (include namespace = using System.Data; ) :

            [ServiceContract]
            public interface IService{

            [OperationContract]
            DataSet queryInventoryById(int productId);

            }

• Open Service.cs file and Comment all code inside public class Service : IService and rewrite (include namespace =  using System.Data; and  using System.Data.SqlClient; ) :

         public class Service : IService
         {       
        public DataSet queryInventoryById(int productId){
        SqlConnection sc = new SqlConnection("data source=DREAMTECH; Initial Catalog=allTest;
         User id =sa;  Password=sushil47");
        SqlDataAdapter sa = new SqlDataAdapter("Select * from productTable where                                             productId="+productId+"",sc);
        DataSet ds = new DataSet();
        sa.Fill(ds);
        return ds;
                  }

            }

• Build it (F5) and copy "http://localhost:9715/WCFServiceTest/Service.svc?singleWsdl" .

STEP III

Creating a WCF application

•  Open Visual studio and Create File -> New Website-> ASP.NET Website
•  Open Default.aspx in designer mode and Drag a textbox(id=txtProductId), button(id=btnProcess) and gridview(id=GridView1).
•  Now Goto Solution Explorer and on root website path -> Add Service Reference.

•  Copy the previous link in the Address section and Press OK.
• On button event write the following code (Default.aspx.cs):

       protected void btnProcess_Click(object sender, EventArgs e)

    {

        int productId = Convert.ToInt32(txtProductId.Text);

        ServiceReference1.ServiceClient objServiceProxy = new ServiceReference1.ServiceClient();

        GridView1.DataSource = objServiceProxy.queryInventoryById(productId).Tables[0];

        GridView1.DataBind();

    }

•    Build it and Result will be:

Timespan Use

   

string sHours = '03:00:00';

int hoursInt;

TimeSpan ts = TimeSpan.Parse(sHours);

hoursInt = ts.TotalHours;

Result:

 hoursInt = 3

Note: Can compute TotalDays, TotalMinutes, TotalSeconds, TotalMilliseconds

Converting integer hours to datetime format in MS SQL SERVER 

DECLARE @DateTimeHours datetime

DECLARE @IntHours  int

SET @IntHours  =  3

SET @DateTimeHours = CONVERT(datetime,CONVERT(varchar, DATEADD(SECOND, @IntHours *3600, 0), 24))

OUTPUT:

1900-01-01 03:00:00.000

If you want to convert in present datetime:

 SET @DateTimeHours = CONVERT(varchar,GETDATE(),103) + CONVERT

(datetime,CONVERT(varchar, DATEADD(SECOND, @IntHours *3600, 0), 24))

OUTPUT:

2013-08-12 03:00:00.000

Making a New Job in MS SQL SERVER 2008 R2

• Go to Object Explorer on the left tab of MS SQL Server Management Studio.
• Right click on SQL Server Agent and Run it.
• Maximize it and you will find the Job folder. Then Create a New Job on right clicking the Job folder.
• A window will open with a General page. Fill the Name and Description of your Job. Remember the Enable is checked.
• Go to Steps page and Create a New Job Step.
• Fill the Step Name, Select Type, Select Database , Fill Command and Parse it. Go to Advanced Page if You want to set any action during Job Step success and failure. Press OK when command is successfully parsed. 
• Example: 
• Step Name :  InsertStep
• Type : Transact-SQL script (T-SQL)
• Database : testdb
• Command: 
• INSERT INTO [dbo].[UserTable] (UserName,Address) VALUES ('Earth','Milky Way Galaxy')
• Parse the Command and Click OK
• Go to Schedules page and Create a New Schedule or Pick one. 
• Creating New Schedule
• Fill the name.
• Set Frequency, Daily Frequency and Duration.
• Click OK.
• Check if Job is executed successfully. (Checking [dbo].[UserTable] )

Table Variable (Create,Insert,Select,Update)

-Table variable are alternative to temporary table and used for lesser sets of records. If large then temporary tables are used (#tempTable).

...Creating Table Variable

DECLARE @tempTable(

     userID int,

     userName nvarchar(50)

)

...Inserting into Table Variable

INSERT INTO @tempTable (userID , userName)

     SELECT userID, userName FROM UserInfo

...Selecting from Table Variable

SELECT * FROM @tempTable

...Updating Table Variable

UPDATE @tempTable

     SET userName =  'Newton'

WHERE userID = 1 

Substring in Data Binding C# ASP.NET

...Default.aspx

<asp:Label ID = "lblUserName" runat= "server" Text ='<%# String.Format("{0}", bindSubstring(Eval("UserName").ToString() , 20 )%>' > </asp:Label>

...Default.aspx.cs

public string bindSubstring ( string data , int lenToShow)

{

       if( data.length > lenToShow)

       {

        return data.substring( 0 , lenToShow);

       }

       else

      {

       return data; 

      }

}

How To Make A Dynamic Table in ASP.NET (C#)

..Default.aspx.cs

using System.Text;
StringBuilder strBldr = new StringBuilder();

strBldr .Append("<table id='tempTable'>");

strBldr .Append("<tr><th>S.N.</th><th>Name</th><th>Address</th></tr>");

for (int i =1 ; i<=5 ; i++)

{

strBldr.Append("<tr> ");

strBldr.AppendFormat("<td {0}</td>", i);

strBldr.AppendFormat("<td>{0}</td>", Your Name Data );

strBldr.AppendFormat("<td>{0}</td>", Your Address Data);

strBldr.Append("</tr>");

}

strBldr.Append("</table>");

literal_id.Text = strBldr.ToString();

..Default.aspx

<div id="divTable" visible="true">

    <asp:Literal ID="literal_id" runat="server"></asp:Literal>

</div>

Handler.ashx with uploadify.js (jQuery File Uploader Plugin) in ASP.NET (C#)

You can download this plugin from here : http://www.uploadify.com/download/

...WebForm.aspx

<script type="text/javascript" src="js/jquery-1.4.1.js"></script>

<link href="css/uploadify.css" rel="stylesheet" type="text/css" />

<script type="text/javascript" src="js/jquery.uploadify.js"></script>

<script type="text/javascript">

      $(document).ready(function () {

        $('#file_upload').uploadify({

            'multi': true,

            'auto': false,

            'swf': 'swf/uploadify.swf',

            'uploader': 'UploadHandler.ashx',

            'fileTypeDesc': 'Image Files',

            'fileTypeExts': '*.gif; *.jpg; *.png',

            'fileSizeLimit': '1024KB',

            'uploadLimit': 3,

            'successTimeout': '5',

            'onUploadSuccess': function (file, data, response) {

              alert('The file ' + file.name + ' was successfully uploaded with a response of ' + response + ':' + data);

                   }

        })

    });

</script>

<body>

<h3>Multiple File Upload Example</h3>

<div id="test">

  <input type="file" name="file_upload" id="file_upload" />

  <br /> 

<a id="btnupload"  href="javascript:$('#file_upload').uploadify('upload','*')">Upload Files</a>

 <a id="btnCancelUpload"  href="javascript:$('#file_upload').uploadify('cancel','*')" >Cancel Upload </a>

 </div>

</body>

...UploadHandler.ashx

<%@ WebHandler Language="C#" Class="UploadHandler" %>

using System;

using System.Web;

using System.IO;

public class UploadHandler : IHttpHandler

{
    public void ProcessRequest(HttpContext context)

    {

        Random rnd = new Random();

        string path = "~/UploadFiles/";

        String filename = HttpContext.Current.Request.Headers["X-File-Name"];

        if (string.IsNullOrEmpty(filename) && HttpContext.Current.Request.Files.Count <= 0)

        {

            context.Response.Write("{success:false}");

        }

        else

        {

            string mapPath = HttpContext.Current.Server.MapPath(path);

            if (Directory.Exists(mapPath) == false)

            {

                Directory.CreateDirectory(mapPath);

            }

            if (filename == null)

            {

                //This work for IE

                try

                {

                    HttpPostedFile uploadedfile = context.Request.Files[0];

                    filename = uploadedfile.FileName;

                    string strExtension = Path.GetExtension(filename).ToLower();

                    filename = filename.Substring(0, filename.Length - strExtension.Length);

                    filename = filename + '_' + rnd.Next(111111, 999999).ToString() + strExtension;                    

                    uploadedfile.SaveAs(mapPath + "\\" + filename);

                    FileStream fs = new FileStream(mapPath + "\\" + filename, FileMode.Open);

                    mapPath = mapPath.Replace(HttpContext.Current.Server.MapPath("~/"), "");

                    mapPath = mapPath.Replace("\\", "/");

                    mapPath = mapPath.Replace("%", "-");

                    mapPath = mapPath.Replace("#", "-");

                    context.Response.Write("{success:true, name:\"" + filename + "\", path:\"" + mapPath + filename + "\"}");

                }

                catch (Exception)

                {

                    context.Response.Write("{success:false}");

                }

            }

            else

            {

                //This work for Firefox and Chrome.

                filename = filename.Replace("%20", " ");

                string strExtension = Path.GetExtension(filename).ToLower();

                filename = filename.Substring(0, filename.Length - strExtension.Length);

                filename = filename + '_' + rnd.Next(111111, 999999).ToString() + strExtension;

                FileStream fileStream = new FileStream(mapPath + "\\" + filename, FileMode.OpenOrCreate);

                try

                {

                    Stream inputStream = HttpContext.Current.Request.InputStream;

                    CopyStream(inputStream, fileStream);

                    mapPath = mapPath.Replace(HttpContext.Current.Server.MapPath("~/"), "");

                    mapPath = mapPath.Replace("\\", "/");

                    mapPath = mapPath.Replace("%", "-");

                    mapPath = mapPath.Replace("#", "-");

                    context.Response.Write("{success:true, name:\"" + filename + "\", path:\"" + mapPath + filename + "\"}");

                }

                catch (Exception)

                {

                    context.Response.Write("{success:false}");

                }

                finally

                {

                    fileStream.Close();

                }

            }

        }

    }  

    public static long CopyStream(Stream source, Stream target)

    {

        const int bufSize = 0x1000;

        byte[] buf = new byte[bufSize];

        long totalBytes = 0;

        int bytesRead = 0;

        while ((bytesRead = source.Read(buf, 0, bufSize)) > 0)

        {

            target.Write(buf, 0, bytesRead);

            totalBytes += bytesRead;

        }

        return totalBytes;

    }

    public bool IsReusable

    {

        get

        {

            return false;

        }

    }

}

Basic AJAX Call

...Javascript tag

<script type="text/javascript" src="js/jquery-1.4.1.js"></script>

<script type="text/javascript">
$('#btnClick').click(function(){
var name = "Nepal";
$.ajax({
                type: "post",
                async: false,
                url: "/WebService.asmx/displayName",
                data: JSON.stringify({ name: name }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (data) {
                alert(data.d);
                 }
           });
});
</script>

...WebService.asmx

<%@ WebService Language="C#" Class="WebService" %>

using System;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 

 [System.Web.Script.Services.ScriptService]

public class WebService: System.Web.Services.WebService {

    [WebMethod]

    public string displayName(string name) {

        return "Hello "+name+"!!";

    }

}