Pabitra@Microsoft.Research: November 2017

Wednesday 29 November 2017

JavaScript get hh:mm:tt format i.e. hour:minute: AM/PM format from java script date

Key Code:

<script type="text/javascript" src="http://www.datejs.com/build/date.js"></script>
<script>
function startTime() {
document.getElementById('lblTime').innerHTML = new Date().toString("hh:mm tt");
}
</script>

Design:
<body onload="startTime()">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</body>

Details Code :

<!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>
<title></title>
<script type="text/javascript" src="http://www.datejs.com/build/date.js"></script>
<script>
function startTime() {
document.getElementById('lblTime').innerHTML = new Date().toString("hh:mm tt");
}
</script>

</head>
<body onload="startTime()">
<div id="divHeader">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</div>

<div id="divBody">
<p>Wel Come</p>
</div>
<div id="divFooter">
<p> Copyright 1999-2017</p>
</div>
</body>
</html>

JavaScript Timer function clock for 12 hour

Key Note:
Design a page with body onload event

<body onload="startTime()">
<div id="divHeader">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</div>
</body>

JavaScript:

<script type="text/javascript">
function startTime() {
var today = new Date();
var h = today.getHours();
var m = today.getMinutes();
var s = today.getSeconds();
var ampm = h >= 12 ? 'PM' : 'AM';
h = h % 12;
h = h ? h : 12; // the hour '0' should be '12'
m = m < 10 ? '0' + m : m;
s = checkTime(s);
var strTime = h + ':' + m +':'+s+ ' ' + ampm;
document.getElementById('lblTime').innerHTML = strTime;
var t = setTimeout(startTime, 500);
}
function checkTime(i) {
if (i < 10) { i = "0" + i }; // add zero in front of numbers < 10
return i;
}

</script>

Details Code:

<!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>
<title></title>
<script type="text/javascript">
function startTime() {
var today = new Date();
var h = today.getHours();
var m = today.getMinutes();
var s = today.getSeconds();
var ampm = h >= 12 ? 'PM' : 'AM';
h = h % 12;
h = h ? h : 12; // the hour '0' should be '12'
m = m < 10 ? '0' + m : m;
s = checkTime(s);
var strTime = h + ':' + m +':'+s+ ' ' + ampm;
document.getElementById('lblTime').innerHTML = strTime;
var t = setTimeout(startTime, 500);
}
function checkTime(i) {
if (i < 10) { i = "0" + i }; // add zero in front of numbers < 10
return i;
}
</script>
</head>
<body onload="startTime()">
<div id="divHeader">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</div>

<div id="divBody">
<p>Wel Come</p>
</div>
<div id="divFooter">
<p> Copyright 1999-2017</p>
</div>
</body>

</html>

JavaScript Timer function clock for 24 hour

Key Note:
Design a page with body onload event

<body onload="startTime()">
<div id="divHeader">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</div>
</body>

JavaScript:

<script>
function startTime() {
var today = new Date();
var amOrpm = today.toLocaleString([], { hour: '2-digit', minute: '2-digit' });

var h = today.getHours();
var m = today.getMinutes();
var s = today.getSeconds();
m = checkTime(m);
s = checkTime(s);
document.getElementById('lblTime').innerHTML =
h + ":" + m + ":" + s+ amOrpm[amOrpm.length - 2] + amOrpm[amOrpm.length - 1];
var t = setTimeout(startTime, 500);
}
function checkTime(i) {
if (i < 10) { i = "0" + i }; // add zero in front of numbers < 10
return i;
}
</script>

Details Code:

<!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>
<title></title>
<script>
function startTime() {
var today = new Date();
var amOrpm = today.toLocaleString([], { hour: '2-digit', minute: '2-digit' });
var h = today.getHours();
var m = today.getMinutes();
var s = today.getSeconds();
m = checkTime(m);
s = checkTime(s);
document.getElementById('lblTime').innerHTML =
h + ":" + m + ":" + s + amOrpm[amOrpm.length - 2] + amOrpm[amOrpm.length - 1];
var t = setTimeout(startTime, 500);
}
function checkTime(i) {
if (i < 10) { i = "0" + i }; // add zero in front of numbers < 10
return i;
}
</script>

</head>
<body onload="startTime()">
<div id="divHeader">
<div style="text-align:right;">Current Time:<label id="lblTime"></label></div>
</div>

<div id="divBody">
<p>Wel Come</p>
</div>

<div id="divFooter">
<p> Copyright 1999-2017</p>
</div>

</body>

</html>

c# get AM or PM from DateTime

use this namespace

using System.Globalization;

string amOrPmvalue=DateTime.Now.ToString("tt", CultureInfo.InvariantCulture);
Response.Write(amOrPmvalue);

Tuesday 28 November 2017

Send SMS by validating Mobile number, filterText

Design

<div>

<asp:TextBox ID="txtMob" TextMode="MultiLine"
onchange="if (this.value.length > 21) {this.value=this.value.substring(0,1); return false; }"
onkeypress="if (this.value.length > 21) {this.value=this.value.substring(0,21); return false; }"
runat="server">
</asp:TextBox>
<ajaxToolkit:FilteredTextBoxExtender ID="flterMobile" runat="server"
TargetControlID="txtMob" FilterType="Custom" ValidChars="1234567890,"
></ajaxToolkit:FilteredTextBoxExtender>

<asp:Button ID="btnSubmit"
runat="server" Text="Submit" onclick="btnSubmit_Click" />
</div>

Code:

using System.Text;

protected void btnSubmit_Click(object sender, EventArgs e)
{
StringBuilder strListMobileNumber = new StringBuilder();
string strvalidString = string.Empty;
string strInvalidString = string.Empty;
string inputstr = txtMob.Text.Trim();
int strLen = inputstr.Length;
if (inputstr.Contains(","))
{
string[] arr = inputstr.Split(',');
foreach (string str in arr)
{
strLen = str.Length;
if (strLen > 10)
{
strInvalidString += " " + str;
}
else
{
if (strLen == 10 && (str.StartsWith("7") || str.StartsWith("8") || str.StartsWith("9")))
{
if (strListMobileNumber.ToString().Trim() == string.Empty)
{
strListMobileNumber.Append("91" + str);
}
else {
strListMobileNumber.Append("," + "91" + str);
}
}
else { strInvalidString += " " + str; }
}

}
}
else
{
if (strLen > 10)
{
strInvalidString = inputstr;
}
else
{
if (strLen == 10 && (inputstr.StartsWith("7") || inputstr.StartsWith("8") || inputstr.StartsWith("9")))
strListMobileNumber.Append("91" + inputstr);
else { strInvalidString = inputstr; }
}

}

if (strInvalidString != string.Empty)
{ return;
}
string data = strListMobileNumber.ToString();
//sendsms
}

Saturday 25 November 2017

export to excel using jquery ajax(ajax.googleapis)

Key Code:
Jquery Library with function

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$('#btnExpot').click(function () {

window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('div[id$=divContentToBePrint]').html()));
e.preventDefault();
});
})

</script>

Button which click event will call the jquery click event to export to excel
<input type="button" id="btnExpot" value="Print To Excel"/>

Div Content Which to Be exported to Ms Excel format.
<div id="divContentToBePrint">

<h3>Application Form</h3>
<table border="1">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<td><input type="text" id="txtID" placeholder="Enter ID" title="Enter your ID" /></td>
<td><input type="text" id="txtName" placeholder="Enter Name" title="Enter your full name"/></td>
<td><input type="button" id="btnAdd" value="Add" title="click here for add new record"/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>1</td>
<td>PABITRA</td>
<td>
<input type="button" id="btnEdit" value="Edit" title="click here for edit the record"/>
<input type="button" id="btnDelete" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>2</td>
<td>Microsoft</td>
<td>
<input type="button" id="Button3" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button4" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>3</td>
<td>MS Sql Server</td>
<td>
<input type="button" id="Button1" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button2" value="Delete" title="click here for delete the record"/>
</td>
</tr>
</tbody>
</table>

</div>
=============================================================

complete sample code , details sample code

<!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>
<title>Pabitra Custom Print To Excel</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$('#btnExpot').click(function () {

window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('div[id$=divContentToBePrint]').html()));
e.preventDefault();
});
})
</script>

</head>
<body>
<div>
<input type="button" id="btnExpot" value="Print To Excel"/>
</div>
<div id="divContentToBePrint">

<h3>Application Form</h3>
<table border="1">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<td><input type="text" id="txtID" placeholder="Enter ID" title="Enter your ID" /></td>
<td><input type="text" id="txtName" placeholder="Enter Name" title="Enter your full name"/></td>
<td><input type="button" id="btnAdd" value="Add" title="click here for add new record"/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>1</td>
<td>PABITRA</td>
<td>
<input type="button" id="btnEdit" value="Edit" title="click here for edit the record"/>
<input type="button" id="btnDelete" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>2</td>
<td>Microsoft</td>
<td>
<input type="button" id="Button3" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button4" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>3</td>
<td>MS Sql Server</td>
<td>
<input type="button" id="Button1" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button2" value="Delete" title="click here for delete the record"/>
</td>
</tr>
</tbody>
</table>

</div>
</body>

</html>

print div content using javascript function

Key Code:

Script
------------

<script language="javascript" type="text/javascript">
function Clickheretoprint() {
var disp_setting = "toolbar=yes,location=no,directories=yes,menubar=yes,";
disp_setting += "scrollbars=yes,width=650, height=600, left=100, top=25";
var content_vlue = document.getElementById("divContentToBePrint").innerHTML;
var docprint = window.open("", "", disp_setting);
docprint.document.open();
docprint.document.write("<html><head><title>Pabitra Print Form</title><style type='text/css' media='all'>.repfmtheader{font-family : Cambria,Baskerville Old Face,Times New Roman;font-size : 18px;font-style : normal;font-variant :normal;font-weight : bold;} .repfmt{font-family: Arial Narrow,Times New Roman,Arial;font-size : 15px;font-weight:bold;}</style>");
docprint.document.write("<style type='text/css' media='all'>.repfmtsno{font-family: tahoma,Arial,Times New Roman;font-size : 15px;padding-left : 15px;}</style>");
docprint.document.write('</head><body onLoad="self.print()"><center>');
docprint.document.write(content_vlue);
docprint.document.write('</center></body></html>');
docprint.document.close();
docprint.focus();
}

</script>

Called the javascript function in button
------------------------------------------------
<input type="button" value="Print" onclick="Clickheretoprint();"/>

Div Content With Div ID
------------------
<div id="divContentToBePrint">

<h3>Application Form</h3>
<table border="1">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<td><input type="text" id="txtID" placeholder="Enter ID" title="Enter your ID" /></td>
<td><input type="text" id="txtName" placeholder="Enter Name" title="Enter your full name"/></td>
<td><input type="button" id="btnAdd" value="Add" title="click here for add new record"/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>1</td>
<td>PABITRA</td>
<td>
<input type="button" id="btnEdit" value="Edit" title="click here for edit the record"/>
<input type="button" id="btnDelete" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>2</td>
<td>Microsoft</td>
<td>
<input type="button" id="Button3" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button4" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>3</td>
<td>MS Sql Server</td>
<td>
<input type="button" id="Button1" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button2" value="Delete" title="click here for delete the record"/>
</td>
</tr>
</tbody>
</table>

</div>

=================================================================

Brif Page Code or details Sample Code

<!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>
<title>Pabitra Custom Print Page</title>
<script language="javascript" type="text/javascript">
function Clickheretoprint() {
var disp_setting = "toolbar=yes,location=no,directories=yes,menubar=yes,";
disp_setting += "scrollbars=yes,width=650, height=600, left=100, top=25";
var content_vlue = document.getElementById("divContentToBePrint").innerHTML;
var docprint = window.open("", "", disp_setting);
docprint.document.open();
docprint.document.write("<html><head><title>Pabitra Print Form</title><style type='text/css' media='all'>.repfmtheader{font-family : Cambria,Baskerville Old Face,Times New Roman;font-size : 18px;font-style : normal;font-variant :normal;font-weight : bold;} .repfmt{font-family: Arial Narrow,Times New Roman,Arial;font-size : 15px;font-weight:bold;}</style>");
docprint.document.write("<style type='text/css' media='all'>.repfmtsno{font-family: tahoma,Arial,Times New Roman;font-size : 15px;padding-left : 15px;}</style>");
docprint.document.write('</head><body onLoad="self.print()"><center>');
docprint.document.write(content_vlue);
docprint.document.write('</center></body></html>');
docprint.document.close();
docprint.focus();
}

</script>

</head>
<body>
<div>
<input type="button" value="Print" onclick="Clickheretoprint();"/>


</div>
<div id="divContentToBePrint">

<h3>Application Form</h3>
<table border="1">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<td><input type="text" id="txtID" placeholder="Enter ID" title="Enter your ID" /></td>
<td><input type="text" id="txtName" placeholder="Enter Name" title="Enter your full name"/></td>
<td><input type="button" id="btnAdd" value="Add" title="click here for add new record"/></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>1</td>
<td>PABITRA</td>
<td>
<input type="button" id="btnEdit" value="Edit" title="click here for edit the record"/>
<input type="button" id="btnDelete" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>2</td>
<td>Microsoft</td>
<td>
<input type="button" id="Button3" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button4" value="Delete" title="click here for delete the record"/>
</td>
</tr>
<tr>
<td>3</td>
<td>MS Sql Server</td>
<td>
<input type="button" id="Button1" value="Edit" title="click here for edit the record"/>
<input type="button" id="Button2" value="Delete" title="click here for delete the record"/>
</td>
</tr>
</tbody>
</table>

</div>
</body>
</html>

Tuesday 21 November 2017

number and decimal value only take the textbox

Key Work:

<asp:TextBox ID="txtDecimalOnly" onkeypress="return validateFloatKeyPress(this,event);" runat="server"></asp:TextBox>

<asp:TextBox ID="txtNumberOnly" onkeypress="return isNumber(event)" runat="server"></asp:TextBox>

function isNumber(evt) {
evt = (evt) ? evt : window.event;
var charCode = (evt.which) ? evt.which : evt.keyCode;
if (charCode > 31 && (charCode < 48 || charCode > 57)) {
return false;
}
return true;
}

function validateFloatKeyPress(el, evt) {
var charCode = (evt.which) ? evt.which : event.keyCode;
var number = el.value.split('.');
if (charCode != 46 && charCode > 31 && (charCode < 48 || charCode > 57)) {
return false;
}
//just one dot (thanks ddlab)
if (number.length > 1 && charCode == 46) {
return false;
}
//get the carat position
var caratPos = getSelectionStart(el);
var dotPos = el.value.indexOf(".");
if (caratPos > dotPos && dotPos > -1 && (number[1].length > 1)) {
return false;
}
return true;
}
=============================================
Details Page Description

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>
<script type="text/javascript">

function isNumber(evt) {
evt = (evt) ? evt : window.event;
var charCode = (evt.which) ? evt.which : evt.keyCode;
if (charCode > 31 && (charCode < 48 || charCode > 57)) {
return false;
}
return true;
}

function validateFloatKeyPress(el, evt) {
var charCode = (evt.which) ? evt.which : event.keyCode;
var number = el.value.split('.');
if (charCode != 46 && charCode > 31 && (charCode < 48 || charCode > 57)) {
return false;
}
//just one dot (thanks ddlab)
if (number.length > 1 && charCode == 46) {
return false;
}
//get the carat position
var caratPos = getSelectionStart(el);
var dotPos = el.value.indexOf(".");
if (caratPos > dotPos && dotPos > -1 && (number[1].length > 1)) {
return false;
}
return true;
}

</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtDecimalOnly" onkeypress="return validateFloatKeyPress(this,event);" runat="server"></asp:TextBox>

<asp:TextBox ID="txtNumberOnly" onkeypress="return isNumber(event)" runat="server"></asp:TextBox>
</div>
</form>
</body>
</html>

Saturday 18 November 2017

Sql: Sample Stored Procedure format using try catch and error handling

/****************************************************
AUTHOR NAME : PABITRA BEHERA
OBJECTIVE : GET EMPLOYEE RELATED DATA BY SELECTION
CREATED DATE: 17 NOVEMBER 2017
MODIFY DATE : 17 NOVEMBER 2017
MODIFY RESON:
****************************************************
*/
CREATE PROCEDURE [dbo].[USP_GET_SELECTION_DATA]
(
@Action VARCHAR(50)=NULL,
@Emp_Code VARCHAR(8)=NULL,
@Emp_Name VARCHAR(70)=NULL,
@message VARCHAR(100)=NULL OUT
)
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @ERROR_COUNT INT
--SET @ERROR_COUNT=0;
BEGIN TRAN GET_EMP_DATA
--*********BEGIN TRY
BEGIN TRY
BEGIN
--*****************************
---EXISTS Employee Code OR Not
IF(@Action='EXIST_EMPLOYEE')
BEGIN
SELECT Emp_Code FROM Mst_Employee WHERE Emp_Code=@Emp_Code;
--IF(@@ERROR<>0)
--SET @ERROR_COUNT=100;
END
COMMIT TRAN GET_EMP_DATA
--********* COMMIT TRAN
END
END TRY
--*********END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
--******ROLLBACK TRAN
ROLLBACK TRAN GET_EMP_DATA
--******ROLLBACK TRAN
END
END CATCH

END

sql : use of try , catch in procedure

CREATE PROCEDURE DBO.USP_GETALL_DOC_ID
(
@ID INT
)
as
BEGIN
BEGIN TRAN MyTransation
BEGIN TRY
BEGIN
--*****************************
SELECT * FROM MST_DOCUMENT WHERE ID=@ID ;
--*****************************
END
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRAN MyTransation
END
END CATCH

END

Tuesday 14 November 2017

Performing a Transaction Using ADO.NET ,ado.net Transaction, SqlConnection Transaction, use of Transaction in c# for submit data into database, sql Transaction

void SubmitForm()
{
string IPAddress = Convert.ToString(HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"]);
SqlConnection objCon = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConStr"].ConnectionString.ToString());
SqlTransaction objTran;
if (objCon.State == ConnectionState.Closed)
objCon.Open();
objTran = objCon.BeginTransaction();
try
{
new SqlCommand("insert into tblSampleTest(name) values ('Pabitra Microsoft Research');", objCon, objTran).ExecuteNonQuery();
new SqlCommand("insert into tblSampleTestLog (name,EntryDate,IPAddress) values ('Pabitra Microsoft Research',getdate(),'"+IPAddress+"');", objCon, objTran).ExecuteNonQuery();
objTran.Commit();
}
catch (SqlException SqlEx)
{
objTran.Rollback();
throw (SqlEx);
}
catch (Exception ex)
{
throw(ex);
}
finally
{
objCon.Close();
}

}

Monday 13 November 2017

GridView serial number

<asp:GridView ID="gvStudentData" runat="server">
<Columns>
<asp:TemplateField HeaderText="Sl No">
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<%#Eval("Name")%>
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>

Friday 10 November 2017

generate csv file from c# datatable

Use this code :

Design

<div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
</div>

css

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

protected void btnSubmit_Click(object sender, EventArgs e)
{

DataTable dtCSV = new DataTable();
dtCSV.Columns.AddRange(new DataColumn[] {
new DataColumn("Emp_Code", typeof(string)),
new DataColumn("Name", typeof(string)),
new DataColumn("City", typeof(string)),
new DataColumn("DOB", typeof(DateTime)),
new DataColumn("Mobile", typeof(string)),
new DataColumn("ctc", typeof(decimal)),
new DataColumn("status", typeof(bool))
});

dtCSV.Rows.Add("1001", "PABITRA BEHERA", "Dhenkanal", "1990-04-28", "9000000001", 23.6, true);
dtCSV.Rows.Add("1002", "TRUPTI RANJAN PANIGRAHI", "Bhadrak", "1990-05-29", "9000000002", 9.9, true);
dtCSV.Rows.Add("1003", "GAYATRI PRASAD DAS", "Khordha","1990-06-30", "9000000003", 10.9, true);
dtCSV.Rows.Add("1004", "Himansu sethi", "Bhubaneswr","1990-07-26", "9000000004", 11.9, true);

using (StreamWriter writer = new StreamWriter("D:\\PABITRA\\Test_Pabitra_"+DateTime.Now.ToString("yyyyMMddhhmmssmm")+".csv"))
{
ConvertDataTableToCSV(dtCSV, writer, true);
}
}
public static void ConvertDataTableToCSV(DataTable objDatatable, TextWriter objTextWriter, bool IsHeader)
{
if (IsHeader)
{
IEnumerable<String> HeaderText = objDatatable.Columns
.OfType<DataColumn>()
.Select(column => ResetBlank(column.ColumnName));

objTextWriter.WriteLine(String.Join(",", HeaderText));
}

IEnumerable<String> items = null;

foreach (DataRow row in objDatatable.Rows)
{
items = row.ItemArray.Select(r => ResetBlank(r == null ? string.Empty :r.ToString()));
objTextWriter.WriteLine(String.Join(",", items));
}
objTextWriter.Flush();
}
private static string ResetBlank(string value)
{
return String.Concat("\"",
value.Replace("\"", "\"\""), "\"");
}

------------------------------------------------------------------------------

All Code
-------------------

Design
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
</div>
</form>
</body>

</html>

css

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSubmit_Click(object sender, EventArgs e)
{

DataTable dtCSV = new DataTable();
dtCSV.Columns.AddRange(new DataColumn[] {
new DataColumn("Emp_Code", typeof(string)),
new DataColumn("Name", typeof(string)),
new DataColumn("City", typeof(string)),
new DataColumn("DOB", typeof(DateTime)),
new DataColumn("Mobile", typeof(string)),
new DataColumn("ctc", typeof(decimal)),
new DataColumn("status", typeof(bool))
});

dtCSV.Rows.Add("1001", "PABITRA BEHERA", "Dhenkanal", "1990-04-28", "9000000001", 23.6, true);
dtCSV.Rows.Add("1002", "TRUPTI RANJAN PANIGRAHI", "Bhadrak", "1990-05-29", "9000000002", 9.9, true);
dtCSV.Rows.Add("1003", "GAYATRI PRASAD DAS", "Khordha","1990-06-30", "9000000003", 10.9, true);
dtCSV.Rows.Add("1004", "Himansu sethi", "Bhubaneswr","1990-07-26", "9000000004", 11.9, true);

using (StreamWriter writer = new StreamWriter("D:\\PABITRA\\Test_Pabitra_"+DateTime.Now.ToString("yyyyMMddhhmmssmm")+".csv"))
{
ConvertDataTableToCSV(dtCSV, writer, true);
}
}
public static void ConvertDataTableToCSV(DataTable objDatatable, TextWriter objTextWriter, bool IsHeader)
{
if (IsHeader)
{
IEnumerable<String> HeaderText = objDatatable.Columns
.OfType<DataColumn>()
.Select(column => ResetBlank(column.ColumnName));

objTextWriter.WriteLine(String.Join(",", HeaderText));
}

IEnumerable<String> items = null;

foreach (DataRow row in objDatatable.Rows)
{
items = row.ItemArray.Select(r => ResetBlank(r == null ? string.Empty :r.ToString()));
objTextWriter.WriteLine(String.Join(",", items));
}
objTextWriter.Flush();
}
private static string ResetBlank(string value)
{
return String.Concat("\"",
value.Replace("\"", "\"\""), "\"");
}
}

Thursday 9 November 2017

sql : get table column information using sql query also with the help of system table

SELECT sc.name 'ColumnName',st.Name 'DataType',
sc.max_length 'Length',
(case when sc.is_nullable='True' then 'Yes' else 'No' end) as 'Nullable',
ISNULL(i.is_primary_key, 0) 'PrimaryKeyStatus'
FROM sys.columns sc
INNER JOIN sys.types st ON sc.user_type_id = st.user_type_id
LEFT OUTER JOIN sys.index_columns sic ON sic.object_id = sc.object_id AND sic.column_id = sc.column_id
LEFT OUTER JOIN sys.indexes i ON sic.object_id = i.object_id AND sic.index_id = i.index_id
WHERE sc.object_id = OBJECT_ID('YourTableName')

Thursday 2 November 2017

c# Datatable Adding column and row in shortest way.

DataTable dtStudent = new DataTable();
//Add new column
dtStudent.Columns.AddRange (
new DataColumn[] { new DataColumn("SlNo", typeof(int)), new DataColumn("RollNumber", typeof(string)), new DataColumn("DateOfJoin", typeof(DateTime)), new DataColumn("Place", typeof(string)), new DataColumn("Course", typeof(string)), new DataColumn("Remark", typeof(string)) }
);
// Add value to the related column
dtStudent.Rows.Add(1, "10001", DateTime.Now, "Bhubaneswar", "MCA", "Good");

Pabitra@Microsoft.Research

Translate