Vinit Patel

Archive for the ‘Sql Server’ Category

Hi,

If you have the Data in Sql table like

Value

15

17

 

Now, If you want to print the values like $15 and $17. So you will have to do like this…

SELECT LEFT(‘$’ + CONVERT(VARCHAR, CAST(‘15.00’ AS MONEY), 1), LEN(‘$’+ CONVERT(VARCHAR, CAST(‘15.00’ AS MONEY), 1)) – 3)

Tags: ,

For Linq to Sql and entity reference you can check the Rick Strahl’s Blog.

Here, is the Link.

http://west-wind.com/weblog/posts/137000.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+RickStrahl+%28Rick+Strahl%27s+WebLog%29&utm_content=Google+Reader

This is really good.

Create Pager with Alphabets from A-Z

private void CreateAlphaPagings()
{
DataTable dt = new DataTable();
dt.Columns.Add(“PageIndex”);
dt.Columns.Add(“PageText”);
if (this.ViewState[“Paging”] == null)
{
for (int i = 65; i <= 90; i++)
{
DataRow dr = dt.NewRow();
dr[0] = Char.ConvertFromUtf32(i);
dr[1] = Char.ConvertFromUtf32(i);
dt.Rows.Add(dr);
}
DataRow drNew = dt.NewRow();
drNew[“PageIndex”] = “All”;
drNew[“PageText”] = “All”;
dt.Rows.Add(drNew);
this.ViewState[“Paging”] = dt;
}
else
dt = (DataTable)this.ViewState[“Paging”];
DataList1.DataSource = dt;
DataList1.DataBind();
}

Bind Gridview

private void BindGrid(string StartAlpha)
{
string sql = “”;
if (StartAlpha == “All”)
sql = “Select * from Customers Order By Cus_Code Desc”;
else
sql = “Select * from Customers Where Cus_Name Like ‘” + StartAlpha
+ “%’ Order By Cus_Code Desc “;

SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”);
DataTable dtSelect = new DataTable();
da.Fill(dtSelect);
GridView1.DataSource = dtSelect;
GridView1.DataBind();
}

Page Load Event

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.ViewState[“SelectedText”] = “All”;
CreateAlphaPagings();
BindGrid(this.ViewState[“SelectedText”].ToString());
}
}

DataList ItemCommand Event

BindGrid

Protected void Datalist1_ItemCommand(object source, DataListCommandEventArgs e ) { LinkButton lbkbtnPaging = (LinkButton)e.CommandSource;

BindGrid(e.CommandArgument.ToString());

this.ViewState[“SelectedText”] = e.CommandArgument.ToString();

CreateAlphaPagings();

}

DataList ItemDataBound Event

protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.AlternatingItem
|| e.Item.ItemType == ListItemType.Item)
{
if (this.ViewState[“SelectedText”] != null)
{
LinkButton lbkbtnPaging = (LinkButton)e.Item.FindControl(“lnkbtnPaging”);
if (this.ViewState[“SelectedText”].ToString() == lbkbtnPaging.Text)
lbkbtnPaging.Enabled = false;
}
}
}

Create Pager with Alphabets available in SQL Server Table

private void CreatePagingsWithFirstLetter()
{
DataTable dtSelect = new DataTable();
if (this.ViewState[“Paging”] == null)
{
SqlDataAdapter da = new SqlDataAdapter(“Select Distinct Upper(Left(Cus_Name,1))
[PageText], Upper(Left(Cus_Name,1)) [PageIndex] from Customers Order By
PageText Asc”, “YourConnectionString”);

da.Fill(dtSelect);

DataRow drNew = dtSelect.NewRow();
drNew[“PageIndex”] = “All”;
drNew[“PageText”] = “All”;
dtSelect.Rows.Add(drNew);

this.ViewState[“Paging”] = dtSelect;
}
else
dtSelect = (DataTable)this.ViewState[“Paging”];

DataList1.DataSource = dtSelect;
DataList1.DataBind();
}

Create Alphabetic Pager with Stored Procedure

Create Proc Proc_Paging(@OptionId Int) As  
Begin  
	Create Table #Paging (PageIndex VarChar(3), PageText VarChar(3)) 	

	If (@OptionId=1)
	Begin
		Declare @StartIndex Int, @EndIndex Int, @Incr Int  
		Select @StartIndex = Ascii('A')  
		Select @EndIndex = Ascii('Z')  
		Select @Incr = @StartIndex  
		While (@EndIndex >= @Incr )  
		Begin  
			Insert Into #Paging  
			Select Char(@Incr), Char(@Incr)  
			Select @Incr = @Incr+1  
		End  
	End
	Else
	begin
		Insert Into #Paging
		Select Distinct Upper(Left(Cus_Name,1)) [PageIndex], 
		Upper(Left(Cus_Name,1))[PageText] 
		from Customers Order By PageText Asc
	End

	Insert Into #Paging Values ('All', 'All')  
	Select * from #Paging 
End

The Stored Procedure takes a parameter @OptionId to decide the type output. If the parameter @OptionId is 1, 
it will return all alphabets from A to Z. Otherwise it will return only the first letter of the Customer Name 
column in the Customer Table.                             
 
To create the pager, we have to bind the Stored Procedure with the DataList control with the following code.

private void CreateAlphaPagings()                                         
 {                                         
 DataTable dt = new DataTable(); 
 if (this.ViewState["Paging"] == null) 
 { 
 SqlDataAdapter da = new SqlDataAdapter("Proc_Paging", "YourConnectionString"); 
 da.SelectCommand.CommandType = CommandType.StoredProcedure; 
 da.SelectCommand.Parameters.Add("@OptionId", SqlDbType.Int).Value                                         = 2; 
 da.Fill(dt); 
 this.ViewState["Paging"] = dt; 
 } 
 else 
 dt = (DataTable)this.ViewState["Paging"]; 
 
 DataList1.DataSource = dt; 
 DataList1.DataBind();                                         
 }

Let suppose we have in the notepad file or other type of text file the data is in the tabular format.

Like,

___________________________________________

1             xyz                  abc@info.com

2            abc                   xyz@info.com

___________________________________________

Now, If you want to store the data according to its fields like,

No.               Name                  EMailId

then do the following.

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist ‘C:\CategoryTable.txt’, @doesExist OUTPUT
SET NOCOUNT OFF
Select @doesExist

The above line is for check that if the file exists or not !!!

If the File exists then it returns the value = 1.

If yes then do the following.

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

BULK INSERT #Temp
FROM ‘C:\CategoryTable.txt’
WITH
(
FIELDTERMINATOR =’\t’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
END

Select * From #Temp

Drop table #Temp

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[GetEmpBookIssueDetail]

@DeptExamBookIssueID int,

@FromDate varchar(100)=null,

@ToDate varchar(100)=null

AS

BEGIN

Declare @FDate Datetime

Declare @TDate Datetime

if @FromDate is not null

Begin

Exec GetDateByDate @FromDate=@FromDate,@Date=@FDate output

ENd

if @ToDate is not null

Begin

Exec GetDateByDate @FromDate=@ToDate,@Date=@TDate output

ENd

SELECT dd.DeptExamBookIssueID,dd.EmployeeName,dd.Designation,dd.BookCode,b.BookTitle,b.Price,

Convert(varchar,dd.IssueDate,103) as IssueDate,

Convert(varchar,dd.ReturnDate,103) as ReturnDate

FROM DeptExamBookIssueDetails dd

LEFT JOIN DeptExamPaper de ON de.DeptExamPaperID = dd.DeptExamPaperID

LEFT JOIN Books b ON dd.BookCode = b.BookCode

WHERE dd.SoftStatus = 0

AND dd.DeptExamBookIssueID = @DeptExamBookIssueID

OR

(

(

Convert(char(10),IssueDate,101)>=Convert(char(10),@FDate,101) AND

Convert(char(10),IssueDate,101)<=Convert(char(10),@TDate,101)

)

Or

(

Convert(char(10),ReturnDate,101)>=Convert(char(10),@FDate,101)

AND Convert(char(10),ReturnDate,101)<=Convert(char(10),@TDate,101)

)

)

ORDER BY DeptExamBookIssueID DESC

END


Calendar

July 2017
M T W T F S S
« Jan    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Stats Of Blog

  • 6,848 hits

Cluster Map