Wednesday 30 January 2013

SSRS Some Tips


For getting Month and Year :

=Monthname(Datepart("m",Fields!duedateValue.Value)) & " " & Datepart("yyyy", Fields!duedateValue.Value)

Result: May 2013.

Difference Between two Dates:

=DateDiff("d",Fields!tvsi_approveddateValue.Value,Fields!actualclosedateValue.Value)

it will give the
result : 2.


For Month only :

=MonthName(Month(Fields!datedeliveredValue.Value))

Wednesday 16 January 2013

Runing Report on Selected Record


http://social.microsoft.com/Forums/en-CA/crmdevelopment/thread/34f44b87-8ef7-4b3c-8a3e-df22599277c5

first create a datasource.

creating dataset:

select * from filterednew_cool as CRMAF_filterednew_cool

then run and deploy the report.

we will get the option for selected records in running the report.

Wednesday 9 January 2013

retrieve data of lookup field in crm 2011

for this we need to download the xrmservicetoolkit.

http://xrmservicetoolkit.codeplex.com/releases

after download we will get 3 .js files.
1. jquery.js
2.json2.js
3.xrmservicetoolkit.js

just add these three as webresource.

write on javascript function;


function AlertGUID() {

    var primaryContactGUID = Xrm.Page.data.entity.attributes.get("parentcustomerid").getValue()[0].id;
var cols=["lastname"];
var retrievedaccount = XrmServiceToolkit.Soap.Retrieve("account", primaryContactGUID, cols);
var name = retrievedaccount.attributes['lastname'].value;

   alert(name);

}

add these function as webresource.

add 
downloaded webresorce add to the form in onload.
and custom function will give on the onchage of lookup filed.

then run.
for debug. press F12 in the form and 
select your code on : Formscript 
click on debugg. put pointer.

Monday 7 January 2013

Excel Upload/Download- update crm 2011 form

For this
aspx code:


<body>
    <form id="form1" runat="server">
   <asp:FileUpload ID="fu_ImportCSV" runat="server" />
&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="Button2" runat="server" Text="NowUpload"
        onclick="Button2_Click" />
<br/>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="upload" />
    <asp:Button ID="btnimport" runat="server" onclick="btnimport_Click"
        Text="Import" />
        <asp:GridView ID="grvExcelData" runat="server"
        Height="80px" Width="234px">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<%--<Columns>
               <asp:BoundField HeaderText="First Name" />
                <asp:BoundField HeaderText="Last Name"/>
                <asp:BoundField HeaderText="Job Title" />
</Columns>--%>
</asp:GridView>
<asp:Button ID="btnexport" runat="server" Text="Export to Excel"
        onclick="btnexport_Click" />
</form>
&nbsp;&nbsp;&nbsp;
    </form>
</body>


using System;
using System.Collections.Generic;
using System.Linq;
//using System;
//using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Web;
using System.Web.Services;
using System.ServiceModel.Description;
using System.Net;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Xrm.Sdk.Query;
using System.Configuration;
//using Sulekha.Order.CrmSdk;
using System.Linq.Expressions;
using Xrm;
using System.Collections.ObjectModel;
using System.Collections;
using System.Collections.Specialized;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
namespace FullExcel
{
    public partial class Excelwork : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Order_Accepted();
        }

        protected void Order_Accepted()
        {

             ClientCredentials credentials = new ClientCredentials();
            credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
            Uri OrganizationUri = new Uri("http://crmsrv/kryptos/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;
            using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, credentials, null))
            {
                Microsoft.Xrm.Sdk.IOrganizationService service = (Microsoft.Xrm.Sdk.IOrganizationService)serviceProxy;

                QueryExpression query = new QueryExpression("contact");
                query.ColumnSet.AddColumns("firstname", "lastname", "jobtitle", "contactid");
                EntityCollection result1 = serviceProxy.RetrieveMultiple(query);
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                dt.Columns.Add("First Name");
                dt.Columns.Add("Last Name");
                dt.Columns.Add("Job Title");
                dt.Columns.Add("Contact Id");
                //dt.Columns.Add("Product Price");
                //dt.Columns.Add("Order Status");
                //dt.Columns.Add("Shipped to City");
                foreach (Entity entities in result1.Entities)
                {
                    DataRow dr = dt.NewRow();
                    dr["First Name"] = entities.Attributes["firstname"].ToString();
                    dr["Last Name"] = entities.Attributes["lastname"].ToString();
                    dr["Job Title"] = entities.Attributes["jobtitle"].ToString();
                    dr["Contact Id"] = entities.Attributes["contactid"].ToString();
                    dt.Rows.Add(dr);
                }
                ds.Tables.Add(dt);
                grvExcelData.DataSource = ds;
                grvExcelData.DataBind();
                Session["dtlist"] = dt;

                grvExcelData.HeaderRow.Cells[3].Visible = false;
                foreach (GridViewRow gvr in grvExcelData.Rows)
                {
                    gvr.Cells[3].Visible = false;
                }
            }

        }

        protected void btnexport_Click(object sender, EventArgs e)
        {
       


            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.csv");
            Response.Charset = "";
            Response.ContentType = "application/text";          
            DataTable dt1 = (DataTable)Session["dtlist"];      

            grvExcelData.DataSource = dt1;
            grvExcelData.AllowPaging = false;
            grvExcelData.DataBind();
            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < dt1.Columns.Count; k++)
            {
                //add separator
                sb.Append(dt1.Columns[k].Caption + ',');
            }
            //append new line
            sb.Append("\r\n");
            for (int i = 0; i < grvExcelData.Rows.Count; i++)
            {
                for (int k = 0; k < dt1.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(grvExcelData.Rows[i].Cells[k].Text + ',');
                }
                //append new line
                sb.Append("\r\n");
            }
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {

        }

        protected void btnimport_Click(object sender, EventArgs e)
        {
         
        }
        private object ReadToEnd(string filePath)
        {
            DataTable dtDataSource = new DataTable();
            string[] fileContent = File.ReadAllLines(filePath);
            if (fileContent.Count() > 0)
            {
                //Create data table columns
                string[] columns = fileContent[0].Split(',');
                for (int i = 0; i < columns.Count(); i++)
                {
                    dtDataSource.Columns.Add(columns[i]);
                }

                //Add row data
                for (int i = 1; i < fileContent.Count(); i++)
                {
                    string[] rowData = fileContent[i].Split(',');
                    dtDataSource.Rows.Add(rowData);
                }
            }
            return dtDataSource;
        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that the control is rendered */
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            if (fu_ImportCSV.HasFile && fu_ImportCSV.PostedFile.ContentType.Equals("application/vnd.ms-excel"))
            {
                string fn = System.IO.Path.GetFileName(fu_ImportCSV.PostedFile.FileName);
                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".csv";// +fn.Split('.')[1];
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    fu_ImportCSV.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }

                DataTable dtCSVTable = (DataTable)ReadToEnd(fu_ImportCSV.PostedFile.FileName);
                foreach (DataRow dr in dtCSVTable.Rows)
                {
                    string fname = dr["First Name"].ToString();
                    string lname = dr["Last Name"].ToString();
                    string jt = dr["Job Title"].ToString();
                    string cid = dr["Contact Id"].ToString();
                    string city = dr["City"].ToString();
                    Guid conid = new Guid();
                    conid = new Guid(cid);
                    ClientCredentials credentials = new ClientCredentials();
                    credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
                    Uri OrganizationUri = new Uri("http://crmsrv/kryptos/XRMServices/2011/Organization.svc");
                    Uri HomeRealmUri = null;
                    using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, credentials, null))
                    {
                        Microsoft.Xrm.Sdk.IOrganizationService service = (Microsoft.Xrm.Sdk.IOrganizationService)serviceProxy;

                        ColumnSet cols = new ColumnSet(true);
                        var conts = service.Retrieve("contact", conid, cols);

                        conts["firstname"] = fname;
                        conts["lastname"] = lname;
                        conts["jobtitle"] = jt;
                        conts["address1_city"] = city;//dr["City"].ToString();
                        service.Update(conts);
                    }

                }
            }

        }
    }
}

create one folder with name "Data" and create early bound.


Thursday 3 January 2013

export into .csv format

c sharp code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Text;

namespace excel_upload
{
    public partial class upload : System.Web.UI.Page
    {
        protected System.Web.UI.HtmlControls.HtmlInputFile File1;
        protected System.Web.UI.HtmlControls.HtmlInputButton Submit1;
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {
         
            //if (IsPostBack)
            //{
            //    Button1_Click(sender,e);
            //}

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".csv";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }
        }

        protected void btnimport_Click(object sender, EventArgs e)
        {

            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".xls";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }

            string connString = "";
            string strFileType = Path.GetExtension(File1.PostedFile.FileName).ToLower();
            string path = File1.PostedFile.FileName;
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }

            //if (strFileType.Trim() == ".csv")
            //{
            //    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //}
            if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            string query = "SELECT * FROM [Sheet1$]";

            OleDbConnection conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
           // DataSet ds = new DataSet();
            da.Fill(ds);
           
            dt = ds.Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                string name = dr["Name"].ToString();
                string Extention = dr["Extension"].ToString();
                string customer = dr["Customer"].ToString();
                string bt = dr["BASE TABLE"].ToString();

            }
            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();
            ViewState["dtList"] = dt;


            da.Dispose();
            conn.Close();
            conn.Dispose();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that the control is rendered */
        }

        protected void btnexport_Click(object sender, EventArgs e)
        {
            //Response.ClearContent();
            //Response.Buffer = true;
            //Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
            //Response.ContentType = "application/ms-excel";
            //StringWriter sw = new StringWriter();
            //HtmlTextWriter htw = new HtmlTextWriter(sw);
            //grvExcelData.AllowPaging = false;
            //grvExcelData.DataBind();
            //grvExcelData.RenderControl(htw);
            //Response.Write(sw.ToString());
            //Response.End();



            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.csv");
            Response.Charset = "";
            Response.ContentType = "application/text";
            //Button1_Click(sender, e);
            grvExcelData.AllowPaging = false;
            grvExcelData.DataBind();
           

            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < grvExcelData.Columns.Count; k++)
            {
                //add separator
                sb.Append(grvExcelData.Columns[k].HeaderText + ',');
            }
            //append new line
            sb.Append("\r\n");
            for (int i = 0; i < grvExcelData.Rows.Count; i++)
            {
                for (int k = 0; k < grvExcelData.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(grvExcelData.Rows[i].Cells[k].Text + ',');
                }
                //append new line
                sb.Append("\r\n");
            }
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();




            //try
            //{
            //    DataTable dt1 = (DataTable)ViewState["dtList"];
            //    if (dt1 == null)
            //    {
            //        throw new Exception("No Records to Export");
            //    }
            //    string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".csv";
            //    FileInfo FI = new FileInfo(Path);
            //    StringWriter stringWriter = new StringWriter();
            //    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
            //    DataGrid DataGrd = new DataGrid();
            //    DataGrd.DataSource = dt1;
            //    DataGrd.DataBind();

            //    DataGrd.RenderControl(htmlWrite);
            //    string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
            //    if (!Directory.Exists(directory))
            //    {
            //        Directory.CreateDirectory(directory);
            //    }

            //    System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
            //    stringWriter.ToString().Normalize();
            //    vw.Write(stringWriter.ToString());
            //    vw.Flush();
            //    vw.Close();
            //    WriteAttachment(FI.Name, "application/text", stringWriter.ToString());
            //}
            //catch (Exception ex)
            //{
            //    //throw new Exception(ex.Message);
            //}
         
        }
        public static void WriteAttachment(string FileName, string FileType, string content)
        {
            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
            Response.ContentType = FileType;
            Response.Write(content);
            Response.End();

        }

    }
}

aspx code:


<body>
    <form id="Form1" method="post" enctype="multipart/form-data" runat="server">
<input type="file" id="File1" name="File1" runat="server" />
<br/>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="upload" />
    <asp:Button ID="btnimport" runat="server" onclick="btnimport_Click"
        Text="Import" />
        <asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<asp:Button ID="btnexport" runat="server" Text="Export to Excel"
        onclick="btnexport_Click" />
</form>
</body>

reading .csv format file in c sharp

c sharp code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Text;


namespace excel_upload
{
    public partial class onlyupload : System.Web.UI.Page
    {
        protected System.Web.UI.HtmlControls.HtmlInputFile File1;
        protected System.Web.UI.HtmlControls.HtmlInputButton Submit1;
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        //protected void btnimport_Click(object sender, EventArgs e)
        //{
        //    string filePath = string.Empty;
        //    if (fu_ImportCSV.HasFile && fu_ImportCSV.PostedFile.ContentType.Equals("application/vnd.ms-excel"))
        //    {
        //        gv_GridView.DataSource = (DataTable)ReadToEnd(fu_ImportCSV.PostedFile.FileName);
        //        gv_GridView.DataBind();
        //        lbl_ErrorMsg.Visible = false;
        //    }
        //    else
        //    {
        //        lbl_ErrorMsg.Text = "Please check the selected file type";
        //        lbl_ErrorMsg.Visible = true;
        //    }

        //}

        private object ReadToEnd(string filePath)
        {
            DataTable dtDataSource = new DataTable();
            string[] fileContent = File.ReadAllLines(filePath);
            if (fileContent.Count() > 0)
            {
                //Create data table columns
                string[] columns = fileContent[0].Split(',');
                for (int i = 0; i < columns.Count(); i++)
                {
                    dtDataSource.Columns.Add(columns[i]);
                }

                //Add row data
                for (int i = 1; i < fileContent.Count(); i++)
                {
                    string[] rowData = fileContent[i].Split(',');
                    dtDataSource.Rows.Add(rowData);
                }
            }
            return dtDataSource;
        }

        protected void btn_ImportCSV_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            if (fu_ImportCSV.HasFile && fu_ImportCSV.PostedFile.ContentType.Equals("application/vnd.ms-excel"))
            {
                DataTable dtCSVTable = (DataTable)ReadToEnd(fu_ImportCSV.PostedFile.FileName);
                gv_GridView.DataSource = (DataTable)ReadToEnd(fu_ImportCSV.PostedFile.FileName);
                gv_GridView.DataBind();
                lbl_ErrorMsg.Visible = false;
            }
            else
            {
                lbl_ErrorMsg.Text = "Please check the selected file type";
                lbl_ErrorMsg.Visible = true;
            }
        }
    }
}

aspx page:


<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="fu_ImportCSV" runat="server" />
        <asp:Button ID="btn_ImportCSV" runat="server" Text="Import CSV" OnClick="btn_ImportCSV_Click" />
        <br />
        <br />
        <asp:Label ID="lbl_ErrorMsg" runat="server" Visible="false"></asp:Label>
        <br />
        <br />
        <asp:GridView ID="gv_GridView" runat="server" CellPadding="4" ForeColor="#333333"
            GridLines="None">
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#EFF3FB" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    </div>
    </form>
</body>

reference :

http://forums.asp.net/t/1705264.aspx/1

export and import excel sheet through c sharp

 c sharp code :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;

namespace excel_upload
{
    public partial class upload : System.Web.UI.Page
    {
        protected System.Web.UI.HtmlControls.HtmlInputFile File1;
        protected System.Web.UI.HtmlControls.HtmlInputButton Submit1;
        DataTable dt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (IsPostBack)
            //{
            //    Button1_Click(sender,e);
            //}

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".csv";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }
        }

        protected void btnimport_Click(object sender, EventArgs e)
        {

            if ((File1.PostedFile != null) && (File1.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(File1.PostedFile.FileName);

                string naming = fn.Split('.')[0] + DateTime.Now.ToString("yyyymmdd") + ".xls";// +fn.Split('.')[1]; //fn.Substring(0, 4);// fn + DateTime.Now;
                //string sure = naming + DateTime.Now + ".csv";
                string SaveLocation = Server.MapPath("Data") + "\\" + naming;
                try
                {
                    File1.PostedFile.SaveAs(SaveLocation);
                    Response.Write("The file has been uploaded.");
                }
                catch (Exception ex)
                {
                    Response.Write("Error: " + ex.Message);
                    //Note: Exception.Message returns a detailed message that describes the current exception.
                    //For security reasons, we do not recommend that you return Exception.Message to end users in
                    //production environments. It would be better to return a generic error message.
                }
            }
            else
            {
                Response.Write("Please select a file to upload.");
            }

            string connString = "";
            string strFileType = Path.GetExtension(File1.PostedFile.FileName).ToLower();
            string path = File1.PostedFile.FileName;
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }

            //if (strFileType.Trim() == ".csv")
            //{
            //    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //}
            if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            string query = "SELECT * FROM [Sheet1$]";

            OleDbConnection conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
           
            dt = ds.Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                string name = dr["Name"].ToString();
                string Extention = dr["Extension"].ToString();
                string customer = dr["Customer"].ToString();
                string bt = dr["BASE TABLE"].ToString();

            }
            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();
            ViewState["dtList"] = dt;


            da.Dispose();
            conn.Close();
            conn.Dispose();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that the control is rendered */
        }

        protected void btnexport_Click(object sender, EventArgs e)
        {
            //Response.ClearContent();
            //Response.Buffer = true;
            //Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
            //Response.ContentType = "application/ms-excel";
            //StringWriter sw = new StringWriter();
            //HtmlTextWriter htw = new HtmlTextWriter(sw);
            //grvExcelData.AllowPaging = false;
            //grvExcelData.DataBind();
            //grvExcelData.RenderControl(htw);
            //Response.Write(sw.ToString());
            //Response.End();

            try
            {
                DataTable dt1 = (DataTable)ViewState["dtList"];
                if (dt1 == null)
                {
                    throw new Exception("No Records to Export");
                }
                string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xls";
                FileInfo FI = new FileInfo(Path);
                StringWriter stringWriter = new StringWriter();
                HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
                DataGrid DataGrd = new DataGrid();
                DataGrd.DataSource = dt1;
                DataGrd.DataBind();

                DataGrd.RenderControl(htmlWrite);
                string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
                stringWriter.ToString().Normalize();
                vw.Write(stringWriter.ToString());
                vw.Flush();
                vw.Close();
                WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
            }
         
        }
        public static void WriteAttachment(string FileName, string FileType, string content)
        {
            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
            Response.ContentType = FileType;
            Response.Write(content);
            Response.End();

        }

    }
}

asp.net code :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="upload.aspx.cs" Inherits="excel_upload.upload" EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="Form1" method="post" enctype="multipart/form-data" runat="server">
<input type="file" id="File1" name="File1" runat="server" />
<br/>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="upload" />
    <asp:Button ID="btnimport" runat="server" onclick="btnimport_Click"
        Text="Import" />
        <asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<asp:Button ID="btnexport" runat="server" Text="Export to Excel"
        onclick="btnexport_Click" />
</form>
</body>
</html>

create one folder with name Data in project.

reference :



Wednesday 2 January 2013

pagination using stored procedure

first create stored procedure:

alter PROCEDURE GetCustomersPageWise
    @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY salesorderid ASC
      )AS RowNumber
      ,salesorderid
      ,new_vendername
      ,new_couponcode
    INTO #Results
      FROM salesorder
     
      SELECT @RecordCount = COUNT(salesorderid)
      FROM #Results where ((RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1)) and (new_vendername='Cyberplat')
     
      SELECT * FROM #Results
      WHERE ((RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1)) and (new_vendername='Cyberplat')
    
      DROP TABLE #Results
END
GO

give the connect string in web.config

<connectionStrings>
    <add name="dbconnection" connectionString ="Data Source=crmdb1;Integrated Security=true;Initial Catalog= CRM_MSCRM" providerName="System.Data.SqlClient"/>
  </connectionStrings>


aspx code:

<body>
    <form id="form1" runat="server">
    <div>
     <%--PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    <asp:ListItem Text="10" Value="10" />
    <asp:ListItem Text="25" Value="25" />
    <asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />--%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="salesorderid" />
        <asp:BoundField HeaderText="ContactName" DataField="new_vendername" />
        <asp:BoundField HeaderText="CompanyName" DataField="new_couponcode" />
    </Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
    <asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
   
    </div>
    </form>
</body>


c sharp code ;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace testingpopup
{
    public partial class paginationtest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.GetCustomersPageWise(1);
            }

        }
        protected void PageSize_Changed(object sender, EventArgs e)
        {
            this.GetCustomersPageWise(1);
        }


        private void GetCustomersPageWise(int pageIndex)
        {
            string constring = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", 50);
                    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                    cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                    con.Open();
                    IDataReader idr = cmd.ExecuteReader();
                    GridView1.DataSource = idr;
                    GridView1.DataBind();
                    idr.Close();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }

        protected void Page_Changed(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetCustomersPageWise(pageIndex);
        }

        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / 10);
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 0)
            {
                pages.Add(new ListItem("First", "1", currentPage > 1));
                for (int i = 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();
        }

    }
}