Dot Net Help Coding

Monday, 20 July 2015

English Grammar check

https://app.grammarly.com/docs/28740524

Monday, 22 June 2015

GridView Fixed Header and Freeze Column

<asp:HiddenField ID="hfGridView1SV" runat="server" /> 
<asp:HiddenField ID="hfGridView1SH" runat="server" />

</div>

Ref: http://gridviewscroll.aspcity.idv.tw/Demo.aspx

Tuesday, 16 June 2015

Mapping and Importing Excel Files to a Database using SqlBulkCopy

public void importExcelData(ListItemCollection dbColumns, ListItemCollection excelColumns,
        String tableName)
    {
 
        //set up and make connection
        //connection string specified in the web.config
        ConnectionStringSettings connString = 
              ConfigurationManager.ConnectionStrings["Conn"];
        SqlConnection conn = new SqlConnection(connString.ConnectionString);
        conn.Open();
        using (SqlBulkCopy s = new SqlBulkCopy(conn))
        {
            try
            {
                s.DestinationTableName = tableName;
                s.NotifyAfter = 10000;
                for (int i = 0; i &lt; dbColumns.Count; i++)
                {
                    s.ColumnMappings.Add(excelColumns[i].Value.ToString(),
                       dbColumns[i].Value.ToString());
                }
                s.WriteToServer(data);
                s.Close();
            }
            catch(Exception ex)
            {
                //Error handling
            }
        }
        conn.Close();
    }

Ref: http://blog.shuasolutions.com/2008/10/mapping-and-importing-excel-files-to-a-database-using-sqlbulkcopy/

Friday, 29 May 2015

Download All type of File in Web

protected void btnDownLoad_Click(object sender, EventArgs e)
{
string FilePath = txtFileLogPath.Text.Trim();
string fileName = Path.GetFileName(FilePath);
string fileExtension = Path.GetExtension(FilePath);
if (!string.IsNullOrEmpty(fileExtension))
{
System.IO.FileStream fs = new System.IO.FileStream(FilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] bt = new byte[fs.Length];
fs.Read(bt, 0, (int)fs.Length);
fs.Close();
Response.ContentType = "application/x-unknown/octet-stream";
Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + fileName);
try
{
if (bt != null)
{
System.IO.MemoryStream stream1 = new System.IO.MemoryStream(bt, true);
stream1.Write(bt, 0, bt.Length);
Response.BinaryWrite(bt);
Response.Flush();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
txtFileLogPath.Text = "";
Display(this.GetType(), "Error", "Error Occured");
}
finally
{
Response.End();
txtFileLogPath.Text = "";
}
}
else
{
Display(this.GetType(), "Warning", "Please enter correct path with extension");
txtFileLogPath.Text = "";
}
}
private void Display(Type t, string errorCaption, string errorMessage)
{
string scriptError = "<script>alert('" + errorMessage + "');</script>";
Page.ClientScript.RegisterStartupScript(this.GetType(), errorCaption, scriptError);
}

Wednesday, 27 May 2015

Why/Can we use 'virtual' for properties in classes?

public class Employee
{
public int EmpID { get; set; }
public string EmpName{ get; set; }
public string Address { get; set; }
public virtual ICollection<Dept> dept{ get; set; }
}

public class Dept
{
public int DeptID{ get; set; }
public string DeptName{ get; set; }
public string Email { get; set; }
public virtual Division div { get; set; }
}
--------------------------------------------------------------------------------------

The virtual keyword has an effect when used on properties in EF Code First.

Lazy Loading: Any virtual ICollections will be lazy-loaded unless you specifically mark them otherwise.
More efficient change tracking. If you meet all the following requirements then your change tracking can use a more efficient method by hooking your virtual properties. From the link:

To get change tracking proxies, the basic rule is that your class must be public, non-abstract or non-sealed. Your class must also implement public virtual getters/setters for all properties that are persisted. Finally, you must declare collection based relationship navigation properties as ICollection<T> only. They cannot be a concrete implementation or another interface that derives from ICollection<T> (a difference from the Deferred Loading proxy)

Tuesday, 19 May 2015

Download multiple excel sheet using NPOI.dll

Download latest NPOI.dll and add into your project reference.

using NPOI.HSSF.UserModel;

public partial class WebForm1 : System.Web.UI.Page
{
string Connstring = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
GetData();
}

private void GetData()
{
DataSet ds = new DataSet();
string[] tableNames;
using (SqlConnection con = new SqlConnection(Connstring))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ExcelDownload";

SqlParameter param = new SqlParameter("@tableNames", SqlDbType.VarChar,50);
param.Direction = ParameterDirection.Output;
param.Value = 50;
cmd.Parameters.Add(param);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
string strTableNames = Convert.ToString(cmd.Parameters["@tableNames"].Value);
tableNames = strTableNames.Split(',');
}

}
}

writeDataToExcelFile(ds, tableNames);
}
private void writeDataToExcelFile(DataSet ds, string[] tableNames)
{
for (int i = 0; i < tableNames.Length; i++)
{
ds.Tables[i].TableName = tableNames[i];
}
var workbook = new HSSFWorkbook();
string filename = "MultiplesheetDownloadDemo";
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dt = ds.Tables[i];
if (dt.Rows.Count > 0)
{
var Sheet = workbook.CreateSheet(ds.Tables[i].TableName);

int cols = 0;
var myRow = Sheet.CreateRow(0);
var style1 = workbook.CreateCellStyle();

// cell background
style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;

// font color
var font1 = workbook.CreateFont();
font1.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
style1.SetFont(font1);

foreach (DataColumn column in dt.Columns)
{
var Cell = myRow.CreateCell(cols);
Cell.SetCellValue(column.ColumnName);
Cell.CellStyle = style1;
cols++;
}

for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
{
myRow = Sheet.CreateRow(rowNum + 1);
for (int cellNum = 0; cellNum < dt.Columns.Count; cellNum++)
{
var Cell = myRow.CreateCell(cellNum);
Cell.SetCellValue(Convert.ToString(dt.Rows[rowNum][cellNum]));
}

}
}
}
using (var buffer = new MemoryStream())
{
workbook.Write(buffer);

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename + ".xls"));
Response.Clear();
Response.BinaryWrite(buffer.GetBuffer());
Response.End();
}

}
}

Stored Procedure:

ALTER PROC ExcelDownload
(
@tableNames VARCHAR(500) OUTPUT
)
AS
BEGIN
SET @tableNames = 'tblTable1,tblTable2'
SELECT * FROM dbo.tblTable1
SELECT * FROM dbo.tblTable2
END

Friday, 15 May 2015

Insert Stored Procedure Results Into Table

CREATE PROCEDURE GetList
AS
BEGIN
SELECT
ListName = 'MyList'
,ListNumber = 1
END
GO

-- this table will house our results
CREATE TABLE #List
(
ListName VARCHAR(25),
ListNumber INT
)

-- finally, execute and insert into our table
INSERT INTO #List
(
ListName,
ListNumber
)
EXEC dbo.GetList

select * from #List

Translate