How to call database function in asp.net ?



Hi
There are 3 type functions in Sqlserver 2008. These are
1.Scalar function
2.Inline scalar Function
3.Table valued Function

Scalar Function: It is used to return the single value from database.
Inline Scalar Function:It is similar to scalar function but it doesn’t contain function body.It’s means doesn’t contain Begin/End block.
Table-Valued Function:It is used to return the table data type.

Here i m going to show to how to call simple scalar function in asp.net

Step1:Create the Scalar function in database like this

CREATE FUNCTION [dbo].[Multiply](@A int,@B int)

RETURNS int
AS
BEGIN
Return @A*@B
END

Step2: Create one simple page and write the code on click event like this

protected void Button1_Click(object sender, EventArgs e)
{
int a1 = Convert.ToInt32(TextBox1.Text);
int b1 = Convert.ToInt32(TextBox2.Text);
SqlConnection con = new SqlConnection("Data Source=.\\sqlExpress;Initial Catalog=Test;Integrated Security=True");
SqlCommand cmd = new SqlCommand("Select dbo.Multiply(@a,@b)", con);
cmd.Parameters.AddWithValue("@a", a1);
cmd.Parameters.AddWithValue("@b", b1);
con.Open();
Label1.Text = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}

Advertisements

Can we keep multiple catch block in C# ?


Yes. We can keep the multiple catch block in C#. A try block can throw the multiple exceptions,which can be handle by using multiple catch block. We can use in this scenario

protected void BtnSubmit_Click(object sender, EventArgs e)
{
int x = 0;
int div = 0;

try
{
div = 100 / x;
Response.Write("Not excecuted line");
}
catch (DivideByZeroException de)
{
Response.Write("Divide of Zero exception");
}
catch (Exception ee)
{
Response.Write("Exception");
}
finally
{
Response.Write(" Finally block executed");
}

}

How to filter case sensitive data in sqlserver 2008 ?


Hi
Today i was working on one of my personal project, there was requirement to filter the field from database on basis of case sensitive. For example user has given password “TEST” then database should not accept “Test” as input password.

Sqlserver default it will be in-case sensitive. We have to make it case sensitive like this

We can do using so many approach like this

First Method

Select EmpName from tblEmp where BINARY_CHECKSUM(EmpName)=BINARY_CHECKSUM(‘Hello’)

Second Method

Select EmpName from tblEmp where EmpName=’Hello’ COLLATE SQL_Latin1_General_CP1_CS_AS

Select EmpName from tblEmp where EmpName COLLATE Latin1_General_CS_AS=’Hello’

How to sort Name by asc from XML file and populate the dropdown?



Hi
This is the frequently asked question in interview. “Write a program to sort the Empname by asc from Xml file and populate the dropdown”.

We can do using 2 approach
1. using dataset and dataview concept
2. using Linq to Xml concept

Here i m writting code using dataset and dataview concept
Step1: Create some Xml file like this

<?xml version="1.0" encoding="utf-8" ?>
<Emps>
<Emp>
<Id>1</Id>
<Name>Ram</Name>
<Address>xBangalore</Address>
</Emp>
<Emp>
<Id>2</Id>
<Name>RamMohan</Name>
<Address>Chenai</Address>
</Emp>
<Emp>
<Id>3</Id>
<Name>Ram</Name>
<Address>Nepal</Address>
</Emp>
</Emps>

Step 2: Write the C# code in codebehind file like this

protected void Page_Load(object sender, EventArgs e)
{

string filepath = Server.MapPath("../Emp.xml");
using (DataSet ds = new DataSet())
{
ds.ReadXml(filepath);
DataView dv = new DataView();
dv =ds.Tables["Emp"].DefaultView;
dv.RowFilter = "Name='Ram'";
dv.Sort = "Address Asc";
DropDownList1.DataSource = dv;
DropDownList1.DataTextField = "Address";
DropDownList1.DataValueField = "Id";
DropDownList1.DataBind();

}

}

I hope it will help to someone.