DotNet--->Satheesh: October 2009

Tuesday, October 27, 2009

Making querry from exel or Csv

using System.Collections.Generic;
List<string> allLines = new List<string>();
allLines.Clear();
string[] ldf = { "#$#" }; //put the character "#$#" instead of space..
string[] lines = File.ReadAllLines("D:\stringfile.txt");
string curLine = String.Empty;
for (int j = 0; j < lines.Length; j++)
{
curLine = Insert(lines[j].Split(ldf, StringSplitOptions.None)[0].Replace("'", "''"), lines[j].Split(ldf, StringSplitOptions.None)[1].Replace("'", "''"), 5);
allLines.Add(curLine);
}
string[] _allLines = allLines.ToArray();
File.WriteAllLines("Querrymerged.txt", _allLines);

//Function making the querry using string
private string Insert(string text1, string text2, string no)
{
return "Insert into table (no,feild1,feild2) values (" + no+ ",'" + text1 + "','" + text2 + "')";
}

Thursday, October 8, 2009

SQL-finding duplicate row in table

The querry is

select userid from table group by userid having (count(*)>1) or
select userid from table group by userid having (count(userid)>1)

GridVeiw ; Dropdownlist filling in editing mode

//In editing the dropdownlist is filling On DataGrid

<asp:TemplateField HeaderText="Business Type">
<EditItemTemplate>
<asp:DropDownList ID="DDLedit" runat="server" ></asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("username") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

//**Gridview1_RowDataBound
DataSet Ds;

if (e.Row.RowType == DataControlRowType.DataRow)
{
//if (Gridview1.EditIndex == e.Row.RowIndex)
if((e.Row.RowState & DataControlRowState.Edit)>0)
{
DropDownList dx = new DropDownList();
dx = (DropDownList)e.Row.FindControl("DDLedit");
Ds1 = getUserDetails(); //Get username and userid
dx.DataSource = Ds1;
dx.DataTextField = "username";
dx.DataValueField = "userid";
dx.DataBind();
}

Frame Page using C#

using System.Text;

Response.Write(GEtFramePage(userid).Tostring());

//the method is following..
public string GEtFramePage(string userid)
{
System.Text.StringBuilder strbuild = new System.Text.StringBuilder();
string BottomURL= "https://gmail.com";
TrafficName = getTrafficName1(userid);
StringBuilder str = new StringBuilder();
str.Append("</body></html><html><head>");
str.Append("<frameset rows=79px,* FRAMEBORDER=NO FRAMESPACING=0 BORDER=0>");
str.Append("<frame id=topframe src=toppage.aspx?userid=" + userid + " frameborder=0 MARGINWIDTH=0 MARGINHEIGHT=0 scrolling=no>");
// str.Append("<iframe src =\""+ BottomURL+"\" width=\"100%\" height=\"100%\">");
str.Append("<frame id=urlframe src=" + BottomURL+ "");
str.Append(" MARGINWIDTH=0 MARGINHEIGHT=0>");
str.Append("</frameset>");
str.Append("</head></html>");
string test = str.ToString();
return str.ToString();
}

Session Removing

string mid = "satheesh";
//*************clear the session of the mid

Session[mid] = null;
Session.Remove(mid);
Session.Contents.Remove(mid);
//************************************************

DropDownList Related

//Find the Index of an item--
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf(ddlBusName.Items.FindByText("-Select name-"));

SQL-Stored Procedure(Example)

CREATE procedure [dbo].[sp_Example](@tablename varchar(30))
As /*BY Satheesh 14-09-2009 */
begin
DECLARE @userid int,@count int,@id int

DECLARE @Table_Cursor CURSOR
SET @Table_Cursor=CURSOR FAST_FORWARD FOR select distinct userid from memberblog
--close @Table_Cursor

open @Table_Cursor
fetch next from @Table_Cursor into @userid
while @@FETCH_STATUS = 0
BEGIN
select @count=Count(*) from memberblog where userid=@userid
select @id=isnull(min(id),0) from memberactivitycount where userid=@userid

if(@id>0)
begin
--update
update memberactivitycount set blog=@count where id=@id
end
else
begin
--insert
insert into memberactivitycount (userid, blog ) values (@userid,@count)
end
fetch next from @Table_Cursor into @userid
END
close @Table_Cursor
deallocate @Table_Cursor

/* Excecuting another sp --example is -->exec sp_sentmessage */
/* Excecuting a function from sp --example is -->if dbo.FunctionExm(@MemberID,@UserID)=1 */
end

SQL- function(Example)

/*check whether a member is downline or not
jerome 5_9_09
if a member is downline it return 1 other wise 0
*/
ALTER function [dbo].[IsSponsor](@MemberID int,@UserID int)RETURNS int
as begin
declare @status int,@TempID int
set @status=0
set @TempID=@UserID
if @MemberID=@UserID
set @status=1
else
begin
while @TempID>0
begin
select @TempID=isnull(min(userid),0) from login where username=(select sponsorid from registration where loginid=@UserID)
set @UserID=@TempID
if @TempID=@MemberID
begin
set @status= 1
break
end
end
end
return @status
end
/*
call a function***********
select dbo.IsSponsor(1,1)
print dbo.IsSponsor(1,21)
*/

Arraylist and GenericList

Arraylist
----------
using System.Collections;
ArrayList ContentList = new ArrayList();

Genericlist
---------------
using System.Collections.Generic;
List<string> ContentList = new List<string>();

//find the a item
ContentList[ContentList.IndexOf("Yellow")] = "Black";

Find Difference between Arraylist and Genericlist refer the link

DotNet--->Satheesh