Monday, August 10, 2009

PadLeft Function

CREATE FUNCTION dbo.PadLeft

(@String varchar(100),

--Input string to be padded

@Length int,

--Length of final string

@PadChar char(1) --Padding character

) RETURNS varchar(100)

AS

BEGIN

WHILE LEN(@String + 'z') <= @Length

SET @String = @PadChar + @String

RETURN @String

END

GO

PadRight Function

CREATE FUNCTION dbo.PadRight


(@String varchar(100), --Input string to be padded


 @Length int,          --Length of final string


 @PadChar char(1)      --Padding character)


RETURNS varchar(100)


AS


BEGIN
WHILE LEN(@String + 'z') <= @Length


        SET @String = @String + @PadChar


RETURN @String


END

Store and retrieve file with SQL Server

// Store file in SQL Server

FileStream objFileStream = new FileStream("[Path of File]", FileMode.Open);
byte[] Data = new byte[objFileStream.Length];
objFileStream.Read(Data, 0, Convert.ToInt32(objFileStream.Length));

SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["My"].ToString());
objConnection.Open();
SqlCommand objCommand = new SqlCommand("Bytes_Insert");
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add(new SqlParameter("@Data", Data));
objCommand.ExecuteNonQuery();
objConnection.Close();
objFileStream.Close();


// Retrieve file from SQL Server

SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["My"].ToString());
objConnection.Open();
SqlCommand objCommand = new SqlCommand("Bytes_ListAll");
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adpt = new SqlDataAdapter(objCommand);
DataSet ds = new DataSet();
adpt.Fill(ds);

byte[] Data = (byte[]) ds.Tables[0].Rows[0]["Data"];
File.WriteAllBytes("[Path to store File]", Data);

Read Excel file in Asp.Net

using Microsoft.Office.Interop.Excel;

private Excel.Application ExcelObj = null;

// Create Object with File path
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open((Server.MapPath("..//Data") + "\\"
+ fu.FileName), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, true);

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

// get the first and only worksheet from the collection of worksheets
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

// Loop through total row count
for (int i = 0; i < worksheet.Rows.Count; i++)
{
// Get value from ranges.
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());

// In Array, You will get the cell value
System.Array myvalues = (System.Array)range.Cells.Value2;

// By Row, Column
string Value1 = myvalues.GetValue(1, 1) != null ? myvalues.GetValue(1, 1).ToString() : string.Empty;
string Value2 = myvalues.GetValue(1, 2) != null ? myvalues.GetValue(1, 2).ToString() : string.Empty;

}

Read Excel File with out Excel Object
=====================================

string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("") + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(strConn);
con.Open();
if (con.State == ConnectionState.Open)
{

OleDbDataAdapter adp = new OleDbDataAdapter("Select * From [test$A1:D65536]", con);
DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);
}
con.Close()

Create and Export-Import Excel file in Asp.Net

Create and Export-Import Excel file in Asp.Net
=======================================


// This method create an Excel file and export it for download
private void CreateExcelFileandDownload()
{

try
{
// Create a new Excel file.

string[] connectStrings = new string[] {
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\TEMP\\TestExcel2003Output.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;\";",
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\";"
};

string dropTableStmt = "DROP TABLE [test]";
string createTableStmt = "CREATE TABLE [test] ( [Integer] int, [String] varchar(40), [Double] float, [Date] datetime, [Boolean] bit )";
string insertStmt = "INSERT INTO [test] ([Integer], [String], [Double], [Date], [Boolean]) VALUES ({0}, '{1}', {2}, '{3}', {4})";
object[] data = new object[] {

new object[] { 2628013, "Anderson", 0.617715356, new DateTime( 2008, 5, 5 ), true },

new object[] { 2628015, "Rainaud", 0.64933168, new DateTime( 2007, 4, 10 ), false },

new object[] { 2628017, "Dennis", 0.62140731, new DateTime( 2006, 3, 15 ), true },

new object[] { 2628019, "Schoenster", 0.599058708, new DateTime( 2005, 2, 20 ), false },

new object[] { 2628041, "Ganun", 0.593402527, new DateTime( 2004, 1, 25 ), true }

};

foreach (string connect in connectStrings)
{
OleDbConnection con = new OleDbConnection(connect);
con.Open();
if (con.State == ConnectionState.Open)
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandTimeout = 0;
try
{
// Only need this on runs subsequent to first time
cmd.CommandText = dropTableStmt;
cmd.ExecuteNonQuery();
}
catch
{
// First run will cause exception because table (worksheet) doesn't exist
}

cmd.CommandText = createTableStmt;
cmd.ExecuteNonQuery();
foreach (object[] row in data)
{
cmd.CommandText = String.Format(insertStmt, row[0], row[1], row[2], row[3], row[4]);
cmd.ExecuteNonQuery();
}

cmd.Dispose();
if (con.State == ConnectionState.Open)
con.Close();
con.Dispose();
}
}

// Download Created File

// For Office 2007 format
string FileName = @"C:\TEMP\TestExcel2007Output.xlsx";
// For Office 97 - 2003 format
string FileName2 = @"C:\TEMP\TestExcel2003Output.xls";

Response.Clear();
Response.ClearContent();
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Disposition", "attachment; filename=Name.xlsx;");

byte[] buffer = System.IO.File.ReadAllBytes(FileName);

System.IO.MemoryStream mem = new System.IO.MemoryStream();
mem.Write(buffer, 0, buffer.Length);

mem.WriteTo(Response.OutputStream);
Response.End();
}
catch (Exception ex)
{
// throw an exception
}

}

Use Custom paging for DataList, GridView in Asp.Net

Use Custom paging for Datalist, GridView in Asp.Net
=============================================

Suppose your HTML layout is like;
------------------------------------------

// Stylesheet
/* Start Pager 2 style */
.Pager2 { border-collapse:collapse;}
.Pager2 a { color:#0080C0; font-weight:bold; margin:1px; padding:2px 5px; border:1px solid white; text-decoration:none }
.Pager2 a:hover { color:White; font-weight:bold; border:1px #0080C0 solid; background-color:#0080C0 }
.Pager2 span { margin:1px; padding:2px 5px; background-color:#0080C0; color:White; border:1px #0080C0 solid}
/* End Pager 2 style */

// Page HTML layout
<div>
Name : <asp:TextBox ID="txtName" runat="server"></asp:TextBox> <asp:Button ID="btnGo" runat="server" Text="Go" OnClick="btnGo_Click" />

<asp:DataList ID="dlCompanylist" RepeatColumns="1" RepeatDirection="Horizontal" runat="server">
<ItemTemplate>
<table width="200px" cellpadding="1" cellspacing="1" style="border-collapse:collapse">
<tr>
<td valign="top" style="width:100px">ID :</td>
<td><%#Eval("ID").ToString() %></td>
</tr>
<tr>
<td valign="top" >Name</td><td><%#Eval("Name").ToString() %>
</td>
</tr>
<tr>
<td valign="top">Date</td>
<td><%#Eval("RegisterDate").ToString() %></td>
</tr>
<tr>
<td colspan="2" style="border-bottom:solid 1px gray"> </td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<br />
<asp:Literal ID="ltPaging" runat="server"></asp:Literal>
</div>

// Javascript Method
<script language="javascript" type="text/javascript">

function next_prev_page(val)
{

location.href = "zzCustomPaging.aspx?page=" + val;

}
</script>

// Code Behind Part
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGrid();
}
}

private void BindGrid()
{
Company objCompany = new Company();

objCompany.name = txtName.Text.Trim();
objCompany.status = 3;
objCompany.SortBy = "Name";
objCompany.SortOrder = SortDirection.Ascending;
objCompany.PageNo = Request.QueryString["Page"] != null ? int.Parse(Request.QueryString["Page"].ToString()) - 1 : 0;
objCompany.PageSize = 5;

DataSet dsCompanyList = objCompany.GetCompanyRegisterListAll();

if (dsCompanyList != null && dsCompanyList.Tables[0].Rows.Count > 0)
{
dlCompanylist.DataSource = dsCompanyList.Tables[0];
dlCompanylist.DataBind();

ltPaging.Text = this.Paging(objCompany.PageNo + 1, int.Parse(dsCompanyList.Tables[1].Rows[0][0].ToString()), objCompany.PageSize);
}
}

private string Paging(int Page, int TotalRecords, int PageSize)
{
int PageNo = 0;
string NextPage = "";
string PreviousPage = "";
string Print = "";
string Range = "";
string Pager = "";

int Start = 0;
int StartRange = 0;
int EndRange = 0;

if (TotalRecords > PageSize)
{
double RecordForPaging = Math.Ceiling((Convert.ToDouble(TotalRecords) / Convert.ToDouble(PageSize)));
double RecordPage, v;
int NewNo;

if (RecordForPaging > Math.Floor(RecordForPaging))
{
RecordPage = (Math.Floor(RecordForPaging)) + 1;
}
else
{
RecordPage = RecordForPaging;
}

if (RecordPage <= PageSize)
v = RecordPage;
else
v = 5;

if (Page != 1)
PreviousPage = "<div class='Pager2'><a href=javascript:next_prev_page(" + (Page - 1) + ");>PREVIOUS</a></div>";
else
PreviousPage = "";

if (Page != RecordPage)
NextPage = "<div class='Pager2'><a href=javascript:next_prev_page(" + (Page + 1) + ");>NEXT</a></div>";

Print = "";

if (Page == 1)
{
for (PageNo = 1; PageNo <= v; PageNo++)
{
if (RecordPage >= PageNo)
{
if (PageNo == Page)
{
Print += " <b class='Pager2'><span>" + PageNo + "</span></b>";
}
else
{
Print += " <b class='Pager2'><a href=javascript:next_prev_page(" + PageNo + ");>" + PageNo + "</a></b>";
}
}
}
}
else if (Page <= RecordPage)
{
if (PageNo <= RecordPage)
NewNo = 2;
else
NewNo = Page - 5;

if (PageNo <= RecordPage)
NewNo = Page - 5;

for (PageNo = NewNo; PageNo <= Page + 5; PageNo++)
{
if (PageNo > 0)
{
if (PageNo == Page)
Print += " <b class='Pager2'><span>" + PageNo + "</span></b>";
else
{
if (PageNo <= RecordPage)
Print += " <b class='Pager2'><a href=javascript:next_prev_page(" + PageNo + ");>" + PageNo + "</a></b>";
}
}
}
}

Start = (Page - 1) * PageSize;
StartRange = Start + 1;
EndRange = Start + PageSize;

if (EndRange >= TotalRecords)
EndRange = TotalRecords; //end display
Range = StartRange + "-" + EndRange + " of " + TotalRecords;
Pager = "<table width='100%' border='0' style='border-collapse:collapse' ><tr><TD ALIGN='right' width='20%'><TABLE border='0'><TR>";
Pager += "<td width='70px'>" + PreviousPage + "</td><td NOWRAP width='200px'> <div>" + Print + " </div></td><td NOWRAP width='70px' align='left'>" + NextPage + "</td>";
Pager += "</TR></TABLE></TD><td WIDTH='80%' ><div align='left'>" + Range + "</div></td>";
Pager += "</td></tr></table>";
return Pager;
}

return string.Empty;
}

Use Dynamic stylesheet class for messages in Asp.Net

Hi,

In the web application, some times we need to use a message style for
only one Label, like, suppose if our data has been added successfully, we display
message like 'Records has been added successfully'. So, depend on
system's different situation, we have to display label message with style color
combination.

So, Here you can find the solution. By it, you can display your message
with sytle as per your system's situation.

Just copy and paste the style script and copy C# method in your page.

You can find it here...

<style type="text/css">

.MessageSuccess
{
background: #EFF4EA url(images/icn_successful.gif) center no-repeat;
background-position: 15px 5px; /* x-pos y-pos */
font-weight:bold;
text-align: left;
padding: 5px 20px 5px 45px;
border-top: 1px solid #1E8B18;
border-bottom: 1px solid #1E8B18;
color:#555555;

}
.MessageInfo
{
background: #FFFFD2 url(images/icn_info.gif) center no-repeat;
background-position: 15px 5px; /* x-pos y-pos */
text-align: left;
font-weight:bold;
padding: 5px 20px 5px 45px;
border-top: 1px solid #CACA00;
border-bottom: 1px solid #CACA00;
color:#555555;

}
.MessageError
{
background: #FFEAEA url(images/icn_error.gif) center no-repeat;
background-position: 15px 5px; /* x-pos y-pos */
text-align: left;
font-weight:bold;
padding: 5px 20px 5px 45px;
border-top: 1px solid #FF6F6F;
border-bottom: 1px solid #FF6F6F;
color:#555555;

}
</style>

(Download these images for stylesheet)




<form id="form1" runat="server">
<asp:Label ID="lblMessage" runat="server" />
</form>

protected void Page_Load(object sender, EventArgs e)
{
lblMessage.Text = "Records has been Added Successfully.";
this.SetStyle(lblMessage, MessageType.Info);
}

private void SetStyle(Label objLabel, MessageType msgType)
{
if (string.IsNullOrEmpty(objLabel.Text.Trim()))
objLabel.CssClass = "";
else
{
switch (msgType)
{
case MessageType.Error:
objLabel.CssClass = "MessageError";
break;
case MessageType.Info:
objLabel.CssClass = "MessageInfo";
break;
case MessageType.Success:
objLabel.CssClass = "MessageSuccess";
break;
}
}
}

// Define enum in the outer side of your page class.
public enum MessageType
{
Success,
Info,
Error
}

Now, Run your page and check it.