Web and Mobile Apps Codeware: July 2013

Thursday, July 25, 2013

using cross apply in sql | Example for cross apply in sql | Switiching columns and rows

hi in this post i will show an example on how to use cross apply in sql.

Example :

CREATE TABLE #test (
 ID INT
 ,NAME VARCHAR(10)
 ,salary INT
 )

INSERT INTO #test
SELECT 1
 ,'chandan'
 ,100

SELECT *
FROM #test

SELECT A.COLUMN_NAME [Column_name]
 ,CASE 
  WHEN A.COLUMN_NAME = 'ID'
   THEN Convert(VARCHAR, T.ID)
  WHEN A.COLUMN_NAME = 'Name'
   THEN T.NAME
  WHEN A.COLUMN_NAME = 'Salary'
   THEN Convert(VARCHAR, T.salary)
  END [Value]
FROM tempdb.information_Schema.columns A
CROSS APPLY #test T
WHERE table_name LIKE '%#test%'

Monday, July 22, 2013

update table using from clause in a database | using from clause updating table records query example

Below is the query using from clause to update table records :

update Employee
set Deptname = cc.Deptname
FROM Employee dd
inner join Department cc on cc.DeptId = dd.DeptId
where dd.salary is not null

Saturday, July 20, 2013

Example Late binding and early binding in .net | Difference between late and early binding in c#.net

1. Early binding

Early Binding portrays that compiler knows what sort of object it is. (like intellisense which we get while writing the program here we see all the object methods know by the .net)

For example.

if we create object of any class and call any methods of that class that is early binding.

a)
class add
{
int addmethod(int z, int y)
{
return z+y;
}

void main()
{
int value = add.addmethod(5,5);
}

}

b) Function overloading is example for early binding.

2. late binding

Late Binding portrays that compiler does not realize what sort of object it is, what are all the methods and properties it holds. You need to declare it as an object, later you need get the type of the object, methods that are archived in it. Everything will be known at the run time.

Example :

Function overriding is example for late binding.

Wednesday, July 17, 2013

capture dropdownlist value in javascript | Example : Fetch or get asp.net dropdownlist control value in javascript

hi in this post i will show how to capture dropdownlist control value in javascript

Code :

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript">
function getValue() {

var ddl = document.getElementById("<%=DropDownList1.ClientID%>");
var ddlValue = ddl.options[ddl.selectedIndex].value;
alert(ddlValue);

}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="1">IT</asp:ListItem>
<asp:ListItem Value="2">Accounts</asp:ListItem>
<asp:ListItem Value="3">Operations</asp:ListItem>
</asp:DropDownList>
<br />
<asp:Button ID="Button1" runat="server" Text="Get DropDown Selected Value" OnClientClick="return getValue();" />
</div>
</form>
</body>
</html>

Result:

Thursday, July 11, 2013

WCF Method Overloading Example in c#.net | Implementing method overloading in WCF

hi in post i will show how to implement method overloading in WCF using c#.net.

Example :

Take a new WCF Service Application Project.

1. IService1.cs --> Interface code

[ServiceContract]
public interface IService1
{
[OperationContract(Name="GetDataValue1")] //Method overloading
string GetData(int value);

[OperationContract(Name = "GetDataValue2")] //Method overloading
string GetData(string value);
}

2. Service.svc.cs

public class Service1 : IService1
{
public string GetData(int value)
{
return string.Format("You entered: {0}", value);
}

public string GetData(string value)
{
return string.Format("You entered: {0}", value);
}

}

3. Result :

Wednesday, July 10, 2013

using OpenXML in sql server | Example of OpenXML in Sql Server | Reading XML Text using sp_xml_preparedocument

OpenXML in sql server is used to convert a XML Document in to a Sql table.

Below is the Example :

DECLARE @XMLDoc INT
DECLARE @xmlStr VARCHAR(1000)

SET @xmlStr = '<Root> <A><Name>James</Name><Mobile>123456</Mobile></A>
                      <A><Name>Rocky</Name><Mobile>789123</Mobile></A></Root>'

EXEC sp_xml_preparedocument @XMLDoc OUTPUT,
 @xmlStr

SELECT *
FROM OPENXML(@XMLDoc, '/Root/A', 2) WITH (
  NAME VARCHAR(10),
  Mobile VARCHAR(10)
  )

EXEC sp_xml_removedocument @XMLDoc

Output:

using coalesce in sql | coalesce Example

hi in this post i will show what coalesce() function do in sql server using an example.

Example :

CREATE TABLE empDetails (
 nameid INT identity PRIMARY KEY,
 NAME VARCHAR(1000),
 addres VARCHAR(1000),
 mobile VARCHAR(1000)
 )

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Ronnie','india',NULL)

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,'USA','123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Atul',NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Abilash',NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,'india',NULL)

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,NULL,NULL)

select * from empDetails
select nameid , coalesce(name,addres,mobile) as EmpDetails from empDetails 
select nameid , coalesce(name,addres,'') as EmpDetails from empDetails

1. So coalesce returns the first non null value from the all columns mentioned in the  coalesce() function.

2. suppose the first row is having 3 columns of which first two columns having   null value and 3rd column having valid value, then coalesce() will return the 3rd-column value.

3. if all the columns of a row are null then it will return null.

Monday, July 8, 2013

[Resolved] ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified..Column '' cannot be added to non-empty table '' because it does not satisfy these conditions. | Msg 4901, Level 16, State 1 | SQL alter table error

Hi in this post i will show how to resolve this below error.

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column '' cannot be added to non-empty table '' because it does not satisfy these conditions.

1. Creating a scenario where you can get this type of issues.

create table empDetails

(

nameid int identity primary key,

name varchar(1000),

addres varchar(1000),

mobile numeric

)

insert into empDetails(name,addres,mobile) values ('chandan','india',123456789)

select * from empdetails

2. Now i will add a column to this table

alter table empdetails

add [deptid] [int] NOT NULL

Executing this wil give me the error as

To resolve this we will add default value '0' to the column.

Thus the error gets resolved.

Saturday, July 6, 2013

Using ADO.net Entity framework in asp.net example | Bind grid using ado.net Entity Framework | LINQ to ado.net entity data model

Hi in this post i will show how to use ADO.NET entity data model in asp.net.

1. Go to your web application and Add New Item from the solution explorer called ADO.Net Entity Data Model. Giving the file name as myFirstModel.edmx

2.Then it will enter into Entity Data Model Wizard from there select "Generate from database" option. click on next and enter the database connection login details.

3. Then you will see a entity connection string getting generated. Save the entity connection string name as Test_DBEntities and click on next.

4. From the next screen choose the data objects you want to use and finish to exit the data model wizard.

5. After u click on finish, u may get a security warning just click on ok.

6. Now using ado.net entity framework we will bind a gridview

7. Below is the code :

protected void BindGrid()
{
Test_DBEntities db = new Test_DBEntities();

var GridData= from a in db.carWorlds select a;

GridView1.DataSource = GridData.ToList();
GridView1.DataBind();
}

Friday, July 5, 2013

Httpget HttpPost in asp.net MVC Example | Sample asp.net MVC application using httpget and httppost

Httpget HttpPost in MVC

Using a simple controller and views i will show how to use HttpGet and HttpPost in asp.net MVC.

1. HomeController Code:

public class HomeController : Controller
{

[HttpGet]
public ActionResult Index()
{
return View();
}

[HttpPost]
public ActionResult Index(Class1 cs)
{
return RedirectToAction("Home");
}

public ActionResult Home()
{
return View();
}
}

2. Razor View : Index.cshtml

On HttpGet this view will be called.
After we click on submit button the action under the HttpPost in the controller will be called which will redirect the action to Home().

<div>
@using (Html.BeginForm())
{
<div>
<label>
Name</label></div>
<div>
<input type="text" id="txt1" /></div>
<br />
<div>
<label>
Mobile</label></div>
<div>
<input type="text" id="txt2" />
</div>
<br />

<input type="submit" value="Create" />
}
</div>

3. Razor View : Home.cshtml

On HttpPost this Home.cshtml view will be displayed.

<div>
Thanks for filling up the form.
</div>

Result:

1. After we click on create the page is redirected to action mentioned under httppost

Thursday, July 4, 2013

Razor actionlink control examples in MVC | redirecting to different controller action using actionlink in asp.net mvc

hi in this post i will show how using actionlink we can redirect to any action of any controller in asp.net MVC.

1. To redirect to same controller action.

@Html.ActionLink("click here(Redirect to Same Controller Action)", "Index")

//Index is the controller's action name

2. To redirect to different controller action

@Html.ActionLink("click here(Redirect to Other Controller Action)", "Index", "Home", null, null)

//Index is the HomeController action name

Wednesday, July 3, 2013

using trigger how to track all the ddl event changes done in a database | Saving ddl events log in sql server database

hi in this post i will show, using trigger how to track all the dll changes like alter,create,drop etc in a database.

1. Creating a table which we will be using inside the trigger to save the ddl event log generated.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DDLAudit] (
 [eventtime] [varchar](50) NULL
 ,[EventType] [nvarchar](500) NULL
 ,[ServerName] [nvarchar](500) NULL
 ,[DatabaseName] [varchar](256) NULL
 ,[ObjectType] [varchar](256) NULL
 ,[ObjectName] [varchar](125) NULL
 ,[UserName] [varchar](200) NULL
 ,[CommandText] [varchar](max) NULL
 ,[XmlEvent] [xml] NOT NULL
 ,[modifiedby] [varchar](200) NULL
 ,[ModifiedOn] [datetime] NULL
 ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

2. Creating trigger which will get executed for every ddl level changes and will save the event log in the DDLAudit Table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DBAAudit_ALTER_Database] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS

BEGIN
 SET NOCOUNT ON

 DECLARE @ed XML

 SET @ed = EVENTDATA()

 INSERT INTO DDLAudit (
  eventtime
  ,EventType
  ,ServerName
  ,DatabaseName
  ,ObjectType
  ,ObjectName
  ,UserName
  ,CommandText
  ,[XmlEvent]
  ,modifiedby
  ,ModifiedOn
  )
 VALUES (
  convert(VARCHAR(50), getdate(), 109)
  ,@ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(500)')
  ,@ed.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(500)')
  ,@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
  ,CONVERT(VARCHAR(125), @ed.query('data(/EVENT_INSTANCE/ObjectType)'))
  ,@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
  ,suser_name()
  ,Cast(@ed.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS NVARCHAR(max))
  ,@ed
  ,host_name()
  ,GetDate()
  )
END

SET NOCOUNT OFF
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DBAAudit_ALTER_Database] ON DATABASE
GO

3. Result

Now to check alter or drop or create something in the database and then
execute this below select query to check for ddl event changes getting logged in the table.
select * from [DDLAudit]

Tuesday, July 2, 2013

how to access values of the controls present inside the detailsview control | get value of the detailsview textbox field

here i will show how to access the values of the controls present inside the detailsview control like a textbox, label etc

Suppose inside the detailsview you have a textbox control and u want to access the value of it, then use this :

string EmpName = ((TextBox)DetailsView1.FindControl("Emp_Name")).Text;

or u can use

string s = DetailsView1.Rows[0].Cells[1].Text;

Logic to reverse a word string in c# | reverse a string in c#.net

hi a basic code on how to reverse a word(string) in c#.net.

Logic :

string reverseStr()
{
string s = "programming";
char[] c = s.ToCharArray();
string strReverse = "";
for (int i = c.Length - 1; i > -1; i--)
{
strReverse += c[i];
}

return strReverse;
}

Example:

find duplicate records in a datatable using LINQ | Example to find duplicate values from datatable or dataset using Linq Query

hi in this post i will show to find duplicate records in a datatable or dataset using LINQ.

Example:

Below is the sample employee table which i will take it into a datatable and then will search for duplicate employee names present inside the table using a LINQ query.

1. EMP Table

Code :

public void checkDuplicate()
{
string conStr = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString.ToString();
StringBuilder s = new StringBuilder();
SqlConnection s1 = new SqlConnection(conStr);
s1.Open();

string queryString = "SELECT * FROM employee";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, s1);

DataSet employee = new DataSet();
adapter.Fill(employee, "employee");

var duplicateRecords = employee.Tables[0].AsEnumerable()
.GroupBy(r => r["EmpName"]) //coloumn name which has the duplicate values
.Where(gr => gr.Count() > 1)
.Select(g => g.Key);

foreach (var d in duplicateRecords)
{
s.Append("," + d.ToString());
}

Response.Write("<script language='javascript'>alert('Duplicates Names: "+ s.ToString() +"');</script>");
s1.Close();

}

Output :

Output