|
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("Data Source=PRG1;Initial Catalog=MeriDhun;Integrated Security=True");
cn.Open();
string sql = "usp_zone";
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
DataSet ds = new DataSet();
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.CommandText = sql;
da.Fill(ds);
Session["test"] = ds;
}
protected void btnSubmit_Click1(object sender, EventArgs e)
{
DataSet ds1 = new DataSet();
ds1 = (DataSet)Session["test"];
DataRow[] dr;
DataTable dt = ds1.Tables[0].Clone();
//string a = Convert.ToString(txtNo.Text.ToString());
//int y = Convert.ToInt32(txtNo.Text.Length.CompareTo(3));
string[] strInput = txtNo.Text.Split("-".ToCharArray());
if (strInput.Length > 1)
{
string query = ("t1 <="+ strInput[0] + " and " + "t2 >=" + strInput[1]);
dr = ds1.Tables[0].Select(query);
}
else
{
string query = ("t1 <=" + txtNo.Text.ToString() + " and " + "t2 >=" + txtNo.Text.ToString());
dr = ds1.Tables[0].Select(query);
}
foreach (DataRow dr1 in dr)
{
dt.Rows.Add(dr1.ItemArray);
}
int i = Convert.ToInt32(dt.Rows[0]["zone"].ToString());
DataRow[] dr2;
DataTable dt1 = ds1.Tables[1].Clone();
dr2 = ds1.Tables[1].Select(("weight=\'"
+ (txtWeight.Text + "\'")));
foreach (DataRow dr3 in dr2)
{
dt1.Rows.Add(dr3.ItemArray);
}
lbl.Text = dt1.Rows[0][i].ToString();
}
}
=====================================================
CREATE PROCEDURE usp_zone AS
BEGIN
declare @tbl table
(
t1 bigint,
t2 bigint,
zone bigint
)
insert into @tbl(t1,t2,zone)
select
(case when (charindex('-',No)-1 > 0) then
convert(bigint,substring(No,1,charindex('-',No)-1)) else No end )as t1,
(case when (charindex('-',No)-1 > 0) then
convert(bigint,substring(No,charindex('-',No)+1,len(No))) else No end) as t2,
Zone as zone
from [926]
select * from @tbl
--select * from [926]
select * from zone
END
GO
Alpesh
|
|
|
|