CSV to DataTable Conversion

public static DataTable csvToDataTable(string file, bool isRowOneHeader)
{

DataTable csvDataTable = new DataTable();

//no try/catch - add these in yourselfs or let exception happen
String[] csvData = File.ReadAllLines(HttpContext.Current.Server.MapPath(file));

//if no data in file ‘manually’ throw an exception
if (csvData.Length == 0)
{
throw new Exception(”CSV File Appears to be Empty”);
}

String[] headings = csvData[0].Split(’,');
int index = 0; //will be zero or one depending on isRowOneHeader

if(isRowOneHeader) //if first record lists headers
{
index = 1; //so we won’t take headings as data

//for each heading
for(int i = 0; i < headings.Length; i++)
{
//replace spaces with underscores for column names
headings[i] = headings[i].Replace(” “, “_”);

//add a column for each heading
csvDataTable.Columns.Add(headings[i], typeof(string));
}
}
else //if no headers just go for col1, col2 etc.
{
for (int i = 0; i < headings.Length; i++)
{
//create arbitary column names
csvDataTable.Columns.Add(”col”+(i+1).ToString(), typeof(string));
}
}

//populate the DataTable
for (int i = index; i < csvData.Length; i++)
{
//create new rows
DataRow row = csvDataTable.NewRow();

for (int j = 0; j < headings.Length; j++)
{
//fill them
row[j] = csvData[i].Split(’,')[j];
}

//add rows to over DataTable
csvDataTable.Rows.Add(row);
}

//return the CSV DataTable
return csvDataTable;

}

Check Box Click Enable Disable Row Elements

Step :1 (JavaScript)

function EnableDisableTextBox(objCheckBox, objName,objName2)
{
try
{
var chk = document.getElementById(objCheckBox);
var CategoryName = document.getElementById(objName);
var CategoryName2 = document.getElementById(objName2);
//var CategoryABBR = document.getElementById(objABBR);

if(chk.checked)
{
clr="FFFFFF";
CategoryName.Enabled = true;
CategoryName.removeAttribute('disabled');
CategoryName.style.background=clr;
CategoryName.focus();

CategoryName2.Enabled = true;
CategoryName2.removeAttribute('disabled');
CategoryName2.style.background=clr;


//CategoryABBR.Enabled = true;
//CategoryABBR.style.background=clr;
// CategoryABBR.removeAttribute('disabled');


}
else
{
//clr="D4D0C8";
clr="F6F6F6";

CategoryName.setAttribute('disabled','disabled');
CategoryName.style.background=clr;
CategoryName.Enabled = false;
CategoryName.value='';
CategoryName2.setAttribute('disabled','disabled');
CategoryName2.style.background=clr;
CategoryName2.Enabled = false;
CategoryName2.value='';
// CategoryABBR.setAttribute('disabled','disabled');
// CategoryABBR.style.background=clr;
// CategoryABBR.Enabled = false;
}

}
catch(e)
{
}

}


Step:2 (CS File)

protected void rptLinks_OnItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
HtmlInputCheckBox chkFunctions = HtmlInputCheckBox)e.Item.FindControl("chkFunctions");
chkFunctions.Attributes.Add("onclick", "EnableDisableTextBox('" + chkFunctions.ClientID + "','" + txtQuantity.ClientID + "','" + txtTo.ClientID + "')");
}

}


Step 3(aspx)


<input id="chkFunctions" runat="server" type="checkbox" />

Clear all the fields

var elements = document.getElementsByTagName("input");
for (var ii=0; ii < elements.length; ii++) {
if (elements[ii].type == "text") {
elements[ii].value = "";
}
}

Multiple Fields Search Stored Procedure

ALTER PROCEDURE XPS_TemplateSearch
@Template_Name varchar(100) = null,
@TemplateType_Code int = null,
@Subject varchar(200) = null
AS
BEGIN
--select * from [XPS_Template] where Template_Name=@Template_Name or TemplateType_Code=@TemplateType_Code or Subject=@Subject is_Active=1

IF(@Template_Name = '')
SET @Template_Name = null
IF(@TemplateType_Code = -1)
SET @TemplateType_Code = null
IF(@Subject = '')
SET @Subject = null


--DECLARE @Qry VARCHAR(1000)
--SET @Qry = 'select * from [XPS_Template] where is_Active=1 and ( Template_Name like ''%'+@Template_Name+'%'' or TemplateType_Code ='+convert(varchar(20),@TemplateType_Code)+' or Subject Like ''%'+@Subject+'%'')'
--select @Qry
--print @Qry

select *
from [XPS_Template]
where is_Active=1
and Template_Name like case when @Template_Name is not null then @Template_Name
when @Template_Name is null then Template_Name end
and TemplateType_Code = case when @TemplateType_Code is not null then @TemplateType_Code
when @TemplateType_Code is null then TemplateType_Code end
and [Subject] like case when @Subject is not null then @Subject
when @Subject is null then [Subject] end

/*and (
Template_Name like ISNULL(@Template_Name,Template_Name)
or TemplateType_Code = ISNULL(@TemplateType_Code,TemplateType_Code)
or [Subject] like ISNULL(@Subject,[Subject])
)
*/


END

Dynamic Query Like

--SET @Qry = 'select * from [XPS_Template] where is_Active=1 and ( Template_Name like ''%'+@Template_Name+'%'' or TemplateType_Code ='+convert(varchar(20),@TemplateType_Code)+' or Subject Like ''%'+@Subject+'%'')'
--select @Qry
--print @Qry

dynamic Query For any Colum name and Value

CREATE PROCEDURE [dbo].[Display_Select_Student]
(
@Col_Name varchar(500)=null,
@Col_Value varchar(500)=null
)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @Qry VARCHAR(1000)
SET @Qry = 'SELECT * FROM [Student] WHERE '+@Col_Name+'='''+@Col_Value+''''

EXEC(@Qry)


SET NOCOUNT OFF;
END