Datatable, DataSet, Dataview and SQL query

Task: Datatable, DataSet, Dataview and SQL query

Description: Datatable, DataSet,  Dataview and SQL query.

1) Merge two DataTable into one.

var dt1 = new DataTable(); //contain data

var dt2 = new DataTable(); //contain data

var result = dt1.AsEnumerable()

    .Union(dt2.AsEnumerable());

DataTable dtnew = result.CopyToDataTable();

2) DataView Example of rowfilter, distinct and sorting .

DataTable dt= new DataTable(); //contain data
DataView dv = new DataView(dt);

dv.RowFilter = "status =1";

dt = dv.ToTable();

DataTable dtprod= new DataTable(); //contain data

DataView dv = new DataView(dtprod);

dv.RowFilter = "status =1";

string[] colname = { "product_id", "product_name", "product_code", "price", "quantity" };

dv.Sort = "product_code asc";

dtprod= dv.ToTable(true, colname);

3) SQl query for permission of execution of execute/create/delete/update and and few more quries.

sp_helptext StoredProcdeureName

GRANT privileges ON object TO user;

REVOKE privileges ON object FROM user;

Execute permission stored procdeure for user

GRANT EXECUTE TO username;

other permission to user

GRANT SELECT, INSERT, DELETE, UPDATE TO username;


//Delete Duplicate Rows in a Table.

DELETE

FROM TABLENAME

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM TABLENAME

GROUP BY duplicateColumn1, duplicateColumn2)

Image Resize with auto-crop

Task: Image resize, auto-crop and auto scale.

Description: Here is sample code for image resize with auto-crop while maintaining its ratio. below function we need to pass image path, maximum width and height for resizing. And then below code will resized to given width & height & crop the remaining part which not covered under given measurement. 

public void drawimage(string ThumbnailPath, int maxwidth, int maxheight)

    {

        System.Drawing.Image image = System.Drawing.Image.FromFile(ThumbnailPath);

       

        if (maxheight == 0 || maxwidth == 0)

        {

            maxwidth = image.Width;

            maxheight = image.Height;

        }

        Size ThumbNailSize = setimagesize(maxwidth, maxheight, image.Width, image.Height);

        System.Drawing.Image ImgThnail;

        if (image.Width < maxwidth || image.Height < maxheight)

        {

            ImgThnail = new Bitmap(ThumbNailSize.Width, ThumbNailSize.Height);

        }

        else

        {

            ImgThnail = new Bitmap(maxwidth, maxheight);

        }

       

        System.Drawing.Graphics graphics = System.Drawing.Graphics.FromImage(ImgThnail);

        graphics.InterpolationMode = InterpolationMode.HighQualityBicubic;

        graphics.SmoothingMode = SmoothingMode.HighQuality;

        graphics.PixelOffsetMode = PixelOffsetMode.HighQuality;

        graphics.CompositingQuality = CompositingQuality.HighQuality;

        if (image.Width < maxwidth || image.Height < maxheight)

        {

            graphics.DrawImage(image, 0, 0, ThumbNailSize.Width, ThumbNailSize.Height);

        }

        else

        {

            int xcord = 0;

            int ycord = 0;

            {

                xcord = (ThumbNailSize.Width - maxwidth) / 2;

                ycord = (ThumbNailSize.Height - maxheight) / 2;

            }

            graphics.DrawImage(image, -xcord, -ycord, ThumbNailSize.Width, ThumbNailSize.Height);

        }

        ImageCodecInfo[] info = ImageCodecInfo.GetImageEncoders();

        EncoderParameters encoderParameters;

        encoderParameters = new EncoderParameters(1);

        encoderParameters.Param[0] = new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, 100L);

        string sExt = System.IO.Path.GetExtension(ThumbnailPath);

        if (sExt.ToString() == ".png")

        {

            Response.ContentType = "image/png";

            ImgThnail.Save(Response.OutputStream, image.RawFormat);

        }

        else

        {

            Response.ContentType = "image/jpeg";

            ImgThnail.Save(Response.OutputStream, info[1], encoderParameters);

        }

        image.Dispose();

    }

    // below code is my old one for calculating height and width with maintain image ratio.

public Size setimagesize(int maxwidth, int maxheight, int OriginalWidth, int OriginalHeight)

    {

        Size NewSize = new Size();

        if (OriginalHeight < maxheight && OriginalWidth < maxwidth)

        {

            NewSize = new Size(OriginalWidth, OriginalHeight);

            return NewSize;

        }

        int sNewWidth = OriginalWidth;

        int sNewHeight = OriginalHeight;

        int tempheight = 0;

        int tempwidht = 0;

        if (OriginalWidth >= OriginalHeight)

        {

            if (OriginalWidth >= maxwidth)

            {

                sNewWidth = maxwidth;

                sNewHeight = OriginalHeight * maxwidth / OriginalWidth;

            }

            if (sNewHeight < maxheight)

            {

                tempheight = sNewHeight;

                sNewHeight = maxheight;

                sNewWidth = sNewWidth * maxheight / tempheight;

            }

        }

        else

        {

            if (OriginalHeight >= maxheight)

            {

                sNewHeight = maxheight;

                sNewWidth = OriginalWidth * maxheight / OriginalHeight;

            }

            if (sNewWidth < maxwidth)

            {

                tempwidht = sNewWidth;

                sNewWidth = maxwidth;

                sNewHeight = sNewHeight * maxwidth / tempwidht;

            }

        }

        NewSize = new Size(sNewWidth, sNewHeight);

        return NewSize;

    }

WebService with JSON implementation

Task: WebService with JSON implementation in asp.net

Description: Here sample of WebService implementation in asp.net which returns only JSON format, default format of webservice is xml. But we have convert the xml response into JSON format here. As nowdays we need to JSON for IPHON and Android App. Also we can use WebApi which is latest technology. Which we will describe later. 

using System;

using System.Collections;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Xml.Linq;

using System.Web.Script.Services;

using System.Data;

using System.Web.Script.Serialization;

using System.Collections.Generic;

using System.IO;

/// <summary>

/// Summary description for MyWebService

/// </summary>

[WebService(Namespace = "http://www.weburl.com/")]

[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 MyWebService : System.Web.Services.WebService {

   

    [WebMethod]

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]

    public void ShowUserDetail(string userid, string accesstoken)

    {

        MyUser myuser = new MyUser();

        List<MyUser> buser = new List<MyUser>();

        bool status = myuser.ShowProfile(userid, accesstoken);

        if (status)

        {

            Status = "Success";

            StatusMessage = "Success With Data";

            buser.Add(bringuser);

        }

        else

        {

            Status = "Failed";

            StatusMessage = "Authentication Failed";

        }

        var resp = new JSONEnvelope<MyUser>(buser, Status, StatusMessage);

        JavaScriptSerializer Machinejson = new JavaScriptSerializer();

        this.Context.Response.AppendHeader("Access-Control-Allow-Origin", "*");

        this.Context.Response.ContentType = "application/json; charset=utf-8"; // to remove xml tag from response

        this.Context.Response.Write(Machinejson.Serialize(resp));

    }

}

Here is Class for getting Data from database . 

/// <summary>

/// Summary description for MyUser

/// </summary>

public class MyUser

{

      public int Id { get; set; }

      public string UserName { get; set; }

      public string firstName { get; set; }

      public string Email { get; set; }

      public string phone { get; set; }

      public string profile_pic_url { get; set; }

      public string shortdescrition { get; set; }

public bool ShowProfile(string userid, string accesstoken)

      {

          DataLayer dl = new DataLayer();

          DataTable dt = dl.GetProfile(userid, accesstoken ); // Return Datatable

          if (dt.Rows.Count > 0)

          {

              Id = Convert.ToInt32(dt.Rows[0]["id"]);

              UserName = dt.Rows[0]["username"].ToString();

              firstName = dt.Rows[0]["firstname"].ToString();

              Email = dt.Rows[0]["email"].ToString();

              phone = dt.Rows[0]["phone"].ToString();

              profile_pic_url = dt.Rows[0]["profile_pic_url"].ToString();

              shortdescrition = dt.Rows[0]["shortdescription"].ToString();

              return true;

          }

          return false;

      }

}

 

Here Class for generating a Envelope for JSON response. 

public class JSONEnvelope<T>

{

    public List<T> Data { get; private set; }

    public string Status { get; set; }

    public string StatusMessage { get; set; }

    public JSONEnvelope(IEnumerable<T> items, string status, string statusmessage)

    {

        Data = new List<T>(items);

        Status = status;

        StatusMessage = statusmessage;

    }

}