How to Export datatable to dbf file in ASP.Net C#?

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace Demo

{

    public partial class Create_DBF_File : System.Web.UI.Page

    {

        OleDbConnection dBaseConnection = null;

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!Page.IsPostBack)

            {

                CreateDBFFile();

            }

        }

        private DataTable GenerateData()

        {

            DataTable dt = new DataTable();

            DataRow dr = null;

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));

            dt.Columns.Add(new DataColumn("Column2", typeof(string)));

            dt.Columns.Add(new DataColumn("Column3", typeof(string)));

            dt.Columns.Add(new DataColumn("Column4", typeof(DateTime)));

            int totalRow = 25;

            for (int i = 0; i < totalRow; i++)

            {

                dr = dt.NewRow();

                dr["Column1"] = i + 1;

                dr["Column2"] = "Row" + i;

                dr["Column3"] = "Row" + i;

                dr["Column4"] = DateTime.Now;

                dt.Rows.Add(dr);

            }

            return dt;

        }

        private void CreateDBFFile()

        {

            string filepath = null;

            filepath = Server.MapPath("~//File//");

            string TableName = "T" +DateTime.Now.ToLongTimeString().Replace(":", "").Replace("AM","").Replace("PM", "");

           

            using(dBaseConnection = newOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source=" + filepath + "; " + "Extended Properties=dBase IV"))

            {

                dBaseConnection.Open();

                OleDbCommand olecommand = dBaseConnection.CreateCommand();

                if ((System.IO.File.Exists(filepath + "" + TableName +".dbf")))

                {

                    System.IO.File.Delete(filepath + "" + TableName + ".dbf");

                    olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

                    olecommand.ExecuteNonQuery();

                }

                else

                {

                    olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

                    olecommand.ExecuteNonQuery();

                }

                OleDbDataAdapter oleadapter = newOleDbDataAdapter(olecommand);

                OleDbCommand oleinsertCommand = dBaseConnection.CreateCommand();

                foreach (DataRow dr in GenerateData().Rows)

                {

                    string Column1 = dr["Column1"].ToString();

                    string Column2 = dr["Column2"].ToString();

                    string Column3 = dr["Column3"].ToString();

                    DateTime Column4 = Convert.ToDateTime(dr["Column4"]);

                    oleinsertCommand.CommandText = "INSERT INTO [" + TableName + "] ([Column1], [Column2],[Column3],[Column4]) VALUES ('" + Column1 + "','" + Column2 + "','" + Column3 + "','" + Column4 + "')";

                    oleinsertCommand.ExecuteNonQuery();

                }

            }

            FileStream sourceFile = new FileStream(filepath + "" + TableName +".dbf", FileMode.Open);

            float FileSize = 0;

            FileSize = sourceFile.Length;

            byte[] getContent = newbyte[Convert.ToInt32(Math.Truncate(FileSize))];

            sourceFile.Read(getContent, 0,Convert.ToInt32(sourceFile.Length));

            sourceFile.Close();

            Response.ClearContent();

            Response.ClearHeaders();

            Response.Buffer = true;

            Response.ContentType = "application/dbf";

            Response.AddHeader("Content-Length", getContent.Length.ToString());

            Response.AddHeader("Content-Disposition", "attachment; filename=Demo.dbf;");

            Response.BinaryWrite(getContent);

            Response.Flush();

            System.IO.File.Delete(filepath + "" + TableName + ".dbf");

            Response.End();

        }

    }

}

Don't forgate to create File folder inside your project root folder.

If you are getting the below error:-

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Means, In your local machine AccessDatabaseEngine not registered.

So, You need to download it from Microsoft web site check following link for downloading AccessDatabaseEngine

http://www.microsoft.com/download/en/details.aspx?id=13255

If you are getting the below error in 64 bit machine:-

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

then your machine IIS  

How to export and import in mvc?

  EXPORT
_______________________________________________________________________________

public ActionResult ExportVendor(int id = 0, string check = "")
        {
            VendorItemModel model = new VendorItemModel();
            IEnumerable<VendorItemModel> vendorItem = new List<VendorItemModel>();

            List<GetVendorItems_Result> vendorList;
       
                model.Checked = false;
                vendorList = objVendor.GetAllVendorItems("", "", false, "ROCK, KBJ, TRIPPS", "NC, SC, VA").Where(x => x.IsDeleted == false).ToList();

            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("VendorExport");
            var columns = new[] { "ItemNumber", "ItemDescription", "VendorItemAlias", "Vendor", "LinkBrgItem","ShiptooUom","PriceByUom",
                                "VendorCaseDescription","CurrentPrice","Taxable","Status","Percentage","Freight","Brand","Pack",
                                    "BinLocation","InventoryLocation","InventoryStatus","PhysicallyInventoryUomId",
                                    "PhysicalInventoryConversionFactor","StatesWhereUsed","ConceptWhereUsed"};
            var headerRow = sheet.CreateRow(0);

            //create header
            for (int i = 0; i < columns.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(columns[i]);
            }

            //fill content
            string data = string.Empty;
            for (int i = 0; i < vendorList.Count; i++)
            {

                var rowIndex = i + 1;
                var row = sheet.CreateRow(rowIndex);
                var cell0 = row.CreateCell(0);
                var cell1 = row.CreateCell(1);
                var cell2 = row.CreateCell(2);
                var cell3 = row.CreateCell(3);
                var cell4 = row.CreateCell(4);
                var cell5 = row.CreateCell(5);
                var cell6 = row.CreateCell(6);
                var cell7 = row.CreateCell(7);
                var cell8 = row.CreateCell(8);
                var cell9 = row.CreateCell(9);
                var cell10 = row.CreateCell(10);
                var cell11 = row.CreateCell(11);
                var cell12 = row.CreateCell(12);
                var cell13 = row.CreateCell(13);
                var cell14 = row.CreateCell(14);
                var cell15 = row.CreateCell(15);
                var cell16 = row.CreateCell(16);
                var cell17 = row.CreateCell(17);
                var cell18 = row.CreateCell(18);
                var cell19 = row.CreateCell(19);
                var cell20 = row.CreateCell(20);
                var cell21 = row.CreateCell(21);

                string ItemNumber = vendorList[i].VendorItemNumber != null ? vendorList[i].VendorItemNumber.ToString() : "Null";
                string ItemDescription = vendorList[i].VendorItemDescription != null ? vendorList[i].VendorItemDescription.ToString() : "";                                       //string ItemDescriptionAlias = vendorList[i].VendorItemDescriptionAlias != null ? vendorList[i].VendorItemDescriptionAlias.ToString() : "";
                string VendorItemAlias = vendorList[i].VendorItemDescriptionAlias != null ? vendorList[i].VendorItemDescriptionAlias.ToString() : "";
                string Vendor = vendorList[i].VendorId != null ? vendorList[i].VendorId.ToString() : Convert.ToString(0);
                string LinkBrgItem = vendorList[i].BrgItemId != null ? vendorList[i].BrgItemId.ToString() : Convert.ToString(0);

                string ShiptooUom = vendorList[i].OrderByUomId != null ? vendorList[i].OrderByUomId.ToString() : Convert.ToString(0);
                string PriceByUom = vendorList[i].PriceByUomId != null ? vendorList[i].PriceByUomId.ToString() : Convert.ToString(0);
                string VendorCaseDescription = vendorList[i].VendorCaseDescription != null ? vendorList[i].VendorCaseDescription.ToString() : "";
                string CurrentPrice = vendorList[i].CurrentPrice != null ? vendorList[i].CurrentPrice.ToString() : Convert.ToString(0);
                string Taxable = vendorList[i].Taxable != null ? Convert.ToString(vendorList[i].Taxable) : "";

                string Status = vendorList[i].Status != null ? vendorList[i].Status : "";
                string Percentage = vendorList[i].VendorPercent != null ? vendorList[i].VendorPercent.ToString() : Convert.ToString(0);
                string Freight = vendorList[i].VendorFreight != null ? vendorList[i].VendorFreight.ToString() : Convert.ToString(0);
                string Brand = vendorList[i].BrandId != null ? vendorList[i].BrandId.ToString() : Convert.ToString(0);
                string Pack = vendorList[i].Pack != null ? vendorList[i].Pack.ToString() : Convert.ToString(0);

                string BinLocation = vendorList[i].BinLocation != null ? vendorList[i].BinLocation : "";
                string InventoryLocation = vendorList[i].InventoryLocation != null ? vendorList[i].InventoryLocation : "";
                string InventoryStatus = vendorList[i].InventoryStatus != null ? vendorList[i].InventoryStatus : "S";
                string PhysicallyInventoryUomId = vendorList[i].PhysicalInventoryUomId != null ? vendorList[i].PhysicalInventoryUomId.ToString() : Convert.ToString(0);
                string PhysicalInventoryConversionFactor = vendorList[i].PhysicalInventoryConversionFactor != null ? vendorList[i].PhysicalInventoryConversionFactor.ToString() : Convert.ToString(0);
                string StatesWhereUsed = vendorList[i].StatesWhereUsed != null ? vendorList[i].StatesWhereUsed : "";
                string ConceptWhereUsed = vendorList[i].ConceptWhereUsed != null ? vendorList[i].ConceptWhereUsed : "";

                cell0.SetCellValue(ItemNumber);
                cell1.SetCellValue(ItemDescription);
                cell2.SetCellValue(VendorItemAlias);
                cell3.SetCellValue(Vendor);
                cell4.SetCellValue(LinkBrgItem);
                cell5.SetCellValue(ShiptooUom);
                cell6.SetCellValue(PriceByUom);
                cell7.SetCellValue(VendorCaseDescription);
                cell8.SetCellValue(CurrentPrice);
                cell9.SetCellValue(Taxable);
                cell10.SetCellValue(Status);
                cell11.SetCellValue(Percentage);

                cell12.SetCellValue(Freight);
                cell13.SetCellValue(Brand);
                cell14.SetCellValue(Pack);
                cell15.SetCellValue(BinLocation);
                cell16.SetCellValue(InventoryLocation);
                cell17.SetCellValue(InventoryStatus);

                cell18.SetCellValue(PhysicallyInventoryUomId);
                cell19.SetCellValue(PhysicalInventoryConversionFactor);
                cell20.SetCellValue(StatesWhereUsed);
                cell21.SetCellValue(ConceptWhereUsed);

            }
            var stream = new MemoryStream();
            workbook.Write(stream);
            stream.Close();

            return File(stream.ToArray(), "application/vnd.ms-excel", "VendorExport.xls");
        }


IMPORT
______________________________________________________________________________
 <div class="col-sm-3" style="margin-left: -135px;">
                <button class="btn btn-default btn1" data-toggle="modal" data-backdrop="static" data-target="#FileUploadModel">
                    Import Vendor
                </button>
            </div>

<div class="modal fade" id="FileUploadModel" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
            <h4 class="modal-title" id="ModalLabel">Vendor File Upload </h4>
        </div>
        <div class="clearfix"></div>
        <div class="modal-body">
            <div id="FileUpload">
                @Html.Partial("VendorFileUpload")
            </div>
            <div class="clearfix"></div>
        </div>
        <div class="modal-footer">
            <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
    </div>
</div>


In PartialPage (VendorFileUpload) -
______________________________

@using (Html.BeginForm("ImportXlVendorData", "VendorProduct", FormMethod.Post, new { enctype = "multipart/form-data" }))
{

    <div id="status" style=" color: green; "></div>
    <a class="file-input-wrapper btn btn-default ">
        <input type="file" onchange="ValidateSingleInput(this);" name="file" id="fileInput" data-filename-placement="inside" style="left: -225.5px; top: 13px;">
    </a>
    <input type="submit" value="Upload file" />

}

<script type="text/javascript">
    var _validFileExtensions = [".xls"];

    function ValidateSingleInput(oInput) {
        debugger;
        if (oInput.type == "file") {
            var sFileName = oInput.value;
            if (sFileName.length > 0) {
                var blnValid = false;
                for (var j = 0; j < _validFileExtensions.length; j++) {
                    var sCurExtension = _validFileExtensions[j];
                    if (sFileName.substr(sFileName.length - sCurExtension.length, sCurExtension.length).toLowerCase() == sCurExtension.toLowerCase()) {
                        blnValid = true;
                        break;
                    }
                }

                if (!blnValid) {
                    alert("Sorry, " + sFileName + " is invalid, allowed extensions are: " + _validFileExtensions.join(", "));
                    oInput.value = "";
                    return false;
                }
            }
        }
        return true;
    }

</script>




public ActionResult ImportXlVendorData(HttpPostedFileBase file)
        {
            try
            {
                int result = 0;
                string List = string.Empty;
                //SiteUserBLL siteUserBLL = new SiteUserBLL();
                VendorItemBL vendorBl = new VendorItemBL();
                DataSet ds = new DataSet();
                if (Request.Files["file"].ContentLength > 0)
                {
                    string fileExtension =
                                        System.IO.Path.GetExtension(Request.Files["file"].FileName);

                    string FileName = Request.Files["file"].FileName;
                    string[] strFileName = FileName.Split('.');
                    FileName = strFileName[0];

                    //if (fileExtension == ".xls" || fileExtension == ".xlsx")
                    if (fileExtension == ".xls")
                    {
                        string fileLocation = Server.MapPath("~/Content/VendorImport/") + Request.Files["file"].FileName;
                        if (System.IO.File.Exists(fileLocation))
                        {
                            System.IO.File.Delete(fileLocation);
                        }
                        Request.Files["file"].SaveAs(fileLocation);
                        string excelConnectionString = string.Empty;
                        // excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        //connection String for xls file format.
                        if (fileExtension == ".xls")
                        {
                            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        //connection String for xlsx file format.
                        else if (fileExtension == ".xlsx")
                        {
                            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        }
                        //Create Connection to Excel work book and add oledb namespace
                        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                        excelConnection.Open();


                        DataTable dt = new DataTable();
                        OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + FileName + "$]", excelConnection);
                        ds = new System.Data.DataSet();


                        //Bind all excel data in to data set
                        //MyCommand.Fill(ds, "[Sheet1$]");

                        MyCommand.Fill(ds, "[" + FileName + "$]");

                        int dscoulmn = ds.Tables[0].Columns.Count;

                        string guid = Guid.NewGuid().ToString();

                        if (dscoulmn == 8)
                        {
                            dt = ds.Tables[0];
                            if (dt != null)
                            {
                                VendorItemModel model = new VendorItemModel();
                                foreach (DataRow dr in dt.Rows)
                                {
                                    var vendorList = objVendor.GetAllVendorItems("", "", false, "ROCK, KBJ, TRIPPS", "NC, SC, VA").Where(x => x.VendorItemNumber == dr[0].ToString()).ToList();
                                    if (vendorList.Count() > 0)
                                    {
                                        List += dr[0].ToString() + ",";
                                        Session["Success"] = List + "  all these vendor already exists and other vendor is successfully inserted .!";
                                    }
                                    else
                                    {
                                        model.VendorItemNumber = dr[0] == null ? "" : dr[0].ToString();
                                        model.VendorItemDescription = dr[1].ToString();
                                        model.VendorCaseDescription = dr[2] == null ? "" : dr[2].ToString();
                                        //model.Weight = Convert.ToDecimal(dr[3]);
                                        model.AverageWeight = Convert.ToDecimal(dr[3]);
                                        model.CurrentPrice = Convert.ToDecimal(dr[4]);
                                        if (dr[5].ToString() == "T")
                                        {
                                            model.Taxable = true;
                                        }
                                        else
                                        {
                                            model.Taxable = false;
                                        }
                                        model.Status = dr[6] == null ? "I" : dr[6].ToString();
                                        model.LastUpdatedBy = 0;
                                        model.ConceptsWhereUsed = dr[7] == null ? "" : dr[7].ToString();
                                        model.LastUpdatedDate = DateTime.Now;

                                        model.VendorId = 1;
                                        model.PriceByUomId = 383;
                                        model.InventoryStatus = "S";

                                        if (List == "")
                                        {
                                            result = VendorItemBL.InsertVendorItem1(model.VendorItemNumber, model.VendorItemDescription, model.VendorId, model.PriceByUomId, model.VendorCaseDescription,
                                                model.AverageWeight, model.CurrentPrice, model.Taxable, model.Status, model.LastUpdatedBy, model.ConceptsWhereUsed);

                                            if (result != 0)
                                            {
                                                Session["Success"] = "successfully inserted";
                                            }
                                        }


                                    }
                                }
                            }
                        }

                        excelConnection.Close();
                    }
                    else
                    {
                        TempData["errormsg"] = "Please select .xls/.xlsx file  !";
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
                //TempData["errormsg"] = "Please select xls file type in Correct format.";
                //return RedirectToAction("SiteUser");
            }

            return RedirectToAction("Index");
        }

Imge resizing in asp.net.

 <asp:FileUpload ID="filelogoimage" runat="server" />
 <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            if (filelogoimage.HasFile)
            {
                int length = Convert.ToInt32(filelogoimage.PostedFile.ContentLength);
                if (length < 4100000)
                {
                    string strpath = System.IO.Path.GetExtension(filelogoimage.FileName);
                    if (strpath == ".jpg" || strpath == ".jpeg" || strpath == ".gif" || strpath == ".png")
                    {
                        string orgImage = Convert.ToString(Server.MapPath("/VDIS/Logos/"));
                        string comImage = Convert.ToString(Server.MapPath("/VDIS/Logo/"));


                        filelogoimage.SaveAs(orgImage + filelogoimage.FileName);
                        string fullPath = Path.Combine(Server.MapPath("/VDIS/Logos/"), filelogoimage.FileName);

                        System.Drawing.Image originalImage = System.Drawing.Image.FromFile(fullPath);
                        System.Drawing.Image images = FixedSize(originalImage,350,150);

                        images.Save(comImage + Path.GetFileName(filelogoimage.FileName));              
                       

                        if (imageLogo.Visible == false)
                        {
                            imageLogo.Visible = true;
                        }
                        else
                        {
                            imageLogo.Visible = true;
                        }
                        imageLogo.ImageUrl = "~/VDIS/Logo/" + filelogoimage.FileName;
                        ViewState["previousImage"] = null;
                        ViewState["ImageName"] = filelogoimage.FileName;
                    }
                    else
                    {
                        imageLogo.Visible = false;
                        labelDisplay.Text = "Only Allow JPG,gif,png";
                    }
                }
                else
                {
                    imageLogo.Visible = false;
                    labelDisplay.Text = "File is too large for uploading";
                }
            }
        }

        public static System.Drawing.Image FixedSize(System.Drawing.Image fullSizeImg, int Width, int Height)
        {

            int sourceWidth = fullSizeImg.Width;
            int sourceHeight = fullSizeImg.Height;
            int sourceX = 0;
            int sourceY = 0;
            int destX = 0;
            int destY = 0;

            float nPercent = 0;
            float nPercentW = 0;
            float nPercentH = 0;

            nPercentW = ((float)Width / (float)sourceWidth);
            nPercentH = ((float)Height / (float)sourceHeight);
            if (nPercentH < nPercentW)
            {
                nPercent = nPercentH;
                destX = System.Convert.ToInt16((Width -
                              (sourceWidth * nPercent)) / 2);
            }
            else
            {
                nPercent = nPercentW;
                destY = System.Convert.ToInt16((Height -
                              (sourceHeight * nPercent)) / 2);
            }

            int destWidth = (int)(sourceWidth * nPercent);
            int destHeight = (int)(sourceHeight * nPercent);

            Bitmap bmPhoto = new Bitmap(Width, Height,
                              PixelFormat.Format24bppRgb);
            bmPhoto.SetResolution(fullSizeImg.HorizontalResolution,
                             fullSizeImg.VerticalResolution);

            Graphics grPhoto = Graphics.FromImage(bmPhoto);
            grPhoto.Clear(Color.White);
            grPhoto.InterpolationMode = InterpolationMode.Default;
            grPhoto.CompositingQuality = CompositingQuality.HighQuality;
            grPhoto.SmoothingMode = SmoothingMode.HighQuality;

            grPhoto.DrawImage(fullSizeImg,
                new Rectangle(destX, destY, destWidth, destHeight),
                new Rectangle(sourceX, sourceY, sourceWidth, sourceHeight),
                GraphicsUnit.Pixel);

            grPhoto.Dispose();
            return bmPhoto;
        }

How to write insert, update, delete and status by stored procedure in sqlserver?

INSERT
________________________________________________________________________
CREATE procedure [dbo].[sp_SaveCMS]
(
@Title nvarchar(155),
@Description ntext,
@seo_keywords nvarchar(255),
@seo_descriptions nvarchar(255),
@Image nvarchar(255),
@PageId int
)
As
Begin
Declare @Id int
Set @Id = 0
Begin
Insert Into CMS
(
Title,
[Description],
seo_keywords,
seo_descriptions,
[Image],
PageId
)
Values
(
@Title,
@Description,
@seo_keywords,
@seo_descriptions,
@Image,
@PageId
)
Set @ID=SCOPE_IDENTITY()
--Set @StudentId=@@IDENTITY()
End
Select @Id As ID
End

UPDATE
_____________________________________________________________________________

CREATE procedure [dbo].[sp_UpdateCMS]
(
@Id int,
@Title nvarchar(155),
@Description ntext,
@seo_keywords nvarchar(255),
@seo_descriptions nvarchar(255),
@Image nvarchar(255),
@PageId int
)
As
Begin
Update  CMS
Set
Title = @Title ,
[Description] = @Description ,
seo_keywords = @seo_keywords ,
seo_descriptions = @seo_descriptions ,
[Image]=@Image,
PageId=@PageId
Where
ID=@Id
End
_____________________________________________________________________________

CREATE proc [dbo].[up_InsertUpdateCMS]
@Id int,
@Title nvarchar(100),
@Description nvarchar(Max),
@Seo_Keywords nvarchar(500),
@Seo_Descriptions nvarchar(500)
as begin
if (@Id > 0)
begin
update dbo.CMS set Title=@Title, Description=@Description,Seo_Keywords=@Seo_Keywords, Seo_Descriptions=@Seo_Descriptions
where Id=@Id
end
else begin
insert into dbo.CMS(Title, Description, Seo_Keywords, Seo_Descriptions)
 values(@Title,@Description,@Seo_Keywords,@Seo_Descriptions)
end
end

____________________________________________________________________________

DELETE

CREATE proc [dbo].[up_DeleteCMS]
@Id int
as
begin
delete from dbo.CMS where Id=@Id
end

CREATE proc[dbo].[up_UpdateAdStatus]
@AdID int,
@Ad_IsActive int
as
begin
update dbo.AdsMaster set Ad_IsActive=@Ad_IsActive where AdID=@AdID
end

How to a function call in stored procedure?

CREATE FUNCTION [dbo].[fnGetBookingId](@customerId int)
RETURNS int
AS
BEGIN
    DECLARE @ret int;
    SELECT @ret= Id
  FROM dbo.RoomForCustomer
  where CustomerId=@customerId
     IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END;


CREATE proc [dbo].[GetAllCustomer]
As
Begin
select c.Id, c.Name, c.Gender, c.Religion, c.Email, c.Country, c.PhoneNo, c.Address, c.CraetedDate,
c.Status, c.AgentId,c.PassportNo, dbo.fnGetBookingId(c.Id) as BookingId
from dbo.Customer As c Order by BookingId desc                
END

How to upload file and another data to use the form and work? How to use loader?

There are two css and jquery-alert
_____________________

<link href="@Url.Content("~/Content/jQueryAlert/jquery.alerts.css")" rel="stylesheet" />
<script src="@Url.Content("~/Content/jQueryAlert/jquery.alerts.js")"></script>

<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

<form enctype="multipart/form-data">
    <div id="status" style=" color: green;"></div>

    <div class="col-sm-5">
        <div class="form-group">
            <label>Weekend Date:<span id="errorWeekendDate">*</span></label>
            @Html.TextBoxFor(model => model.WeekendDate, new { @Placeholder = "Weekend Date", @required = "required", @style = "height: 35px;border: 1px solid #ccc;border-radius: 4px;" })
            <div class="clearfix"></div>
        </div>
    </div>
    <div class="col-sm-7">
        <a class="file-input-wrapper btn btn-default" style="margin-bottom: 15px;">
            <input type="file" name="file" id="fileInput" data-filename-placement="inside" style="left: -225.5px; top: 13px;">
        </a>

        <input type="submit" value="Upload file" style="position: relative;top: -5px;" />
    </div>
    <br />
</form>

<div class="clearfix"></div>
<div id="dvmismatch" style="display :none;">
    @Html.Partial("_MismatchVendorList", Model)
</div>


<script type="text/javascript">
    $(function () {
        // When your form is submitted
        $("form").submit(function (e) {
            debugger;
            var errorWeekendDate = document.getElementById('errorWeekendDate');
            var WeekendDate = document.getElementById('WeekendDate');

            if (WeekendDate.value == '') {
                errorWeekendDate.style.color = "red";
                return false;
            }
            else {
                errorWeekendDate.style.color = "";
            }
            e.preventDefault();
            // Serialize your form
            var formData = new FormData($(this)[0]);
            loader();

            // Make your POST
            $.ajax({
                type: 'POST',
                url: '@Url.Action("FileCsv", "InvoiceProcessing")',
                data: formData,
                cache: false,
                contentType: false,
                processData: false,
                success: function (result) {
                    debugger;
                    $("#popup_ok").click();
                    document.getElementById('dvmismatch').style.display = 'block';
                    $("#dvmismatch").html(result);
                },
                error: function (result) { alert(''); }

            });
        });
    })
</script>

<script type="text/javascript">
    function loader() {
        debugger;
        jAlert('<img src="@Url.Content("~/Content/images/ajax-loader-large.gif")" height="60" width="60" />', 'Please wait');
        $("#popup_ok").hide();
    }
</script>

 [HttpPost]
        public ActionResult FileCsv(HttpPostedFileBase file, string WeekendDate)
        {
            try
            {
                int result = 0;
                string List = string.Empty;
                InventoryModel model = new InventoryModel();
                InventoryBL objbl = new InventoryBL();
                DataSet ds = new DataSet();
                if (Request.Files["file"].ContentLength > 0)
                {
                    string fileExtension =
                                        System.IO.Path.GetExtension(Request.Files["file"].FileName);

                    string ImportCSVFile = Request.Files["file"].FileName;
                    string FileName = Request.Files["file"].FileName;
                    string[] strFileName = FileName.Split('.');
                    FileName = strFileName[0];

                    string fileLocation = Server.MapPath("~/Content/CSVFile/") + Request.Files["file"].FileName;
                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["file"].SaveAs(fileLocation);

                    DataTable dt = new DataTable();

                    string fileName1 = Request.Files["file"].FileName;
                    string path = Path.Combine(Server.MapPath("~/Content/CSVFile/"), fileLocation);
          }

                return PartialView("_MismatchVendorList", model);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

How to implement dropdown by json in Jquery?

<select name="ddlCategoryHeader" class="ch_select " id="ddlCategoryHeader"> </select>


<script type="text/javascript">
    $(document).ready(function () {
        loadCategory();
    });

    function loadCategory() {
        debugger;
        $("#ddlCategoryHeader").html('');
        $("#ddlCategoryHeader").append($('<option></option>').val(0).html('--Select Category--'));

        $.ajax({
            url: "@Url.Action("GetAllSortedCategory", "Common")",
            dataType: "json",
            type: "GET",
            success: function (data) {
                debugger;
                $.each(data, function (i, option) {
                    if (option.ParentID == 0)
                        $("#ddlCategoryHeader").append($('<option></option>').val(option.ID).html(option.Name));
                    else
                        $("#ddlCategoryHeader").append($('<option></option>').val(option.ID).html("--" + option.Name));
                });
            },
            error: function () {
                //alert(" Failed to load category.");
            }
        });
    }
</script>

public ActionResult GetAllSortedCategory()
        {
            var cat = _apiCategory.GetAllSortedCategory();

            return Json(cat, JsonRequestBehavior.AllowGet);
        }