DotNet-Jitendra(Asp.Net, C#, Sql Server, Linq, Ajax, Javascript, jQuery, Xml, MVC)

Tuesday 30 September 2014

Find user defined database name in Sq l server

This query show the all user defined database name

select [name] from master.dbo.sysdatabases where name not in ('master','tempdb','model','msdb')

Output:

how to create tablename dynamically in SQL server

Write this quary and get table name append with current date

CREATE proc createtable

begin

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = 'CREATE TABLE Tab_'+convert(varchar,getdate(),112) + '( id bigint,name varchar(200))'

EXEC sp_executesql @SQLString

end

Example of temporary table and Table variable in Sql server for beginners.

---Table variable Example-----

declare @tablevar table(id int,vichelid varchar(100))

insert into @tablevar select ID,vichelid from Tab_20130926

select * from @tablevar

---Temprory table-----------------------------------------------------------

create table #temptable(id int,vichelid varchar(100))

insert into #temptable select ID,vichelid from Tab_20130926

select * from #temptable

drop table #temptable

Also refer this Link for better understanding...Here you can see the advantage and disadvantage and scop of Table variable and temporary table.
http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

http://stackoverflow.com/questions/2742511/temporary-tables-in-sql-server
http://www.sqlservercentral.com/articles/T-SQL/temptablesinsqlserver/1279/
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Conversion failed when converting date and/or time from character string.

Here i have to use Procedure that contain different type of variable have different datatype and i have to specify insert command in single quotes(as a string) and execute insert command using this

EXEC sp_executesql @SQLString command inside sp_GpsdataInsert procedure .

Now i have to execute this procedure then i have to get this error

Conversion failed when converting date and/or time from character string.

Incorrect Procedure:

create proc sp_GpsdataInsert

@dataindication tinyint,

@vichelId varchar(15),

@Datatime datetime,

@lat varchar(15),

@lang varchar(15),

@speed decimal(5,2),

@direction decimal(6,3),

@odometer decimal(9,2),

@Ignition bit,

@digitalInputOne bit,

@digitalInputTwo bit,

@Immobilizer bit,

@GPSValidityStatus char(1)

as begin

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = 'insert into Tab_'+convert(varchar,getdate(),112) +'([date],dataindication,vichelId,Datatime,lat,lang,speed

,direction,odometer,Ignition,digitalInputOne,digitalInputTwo,Immobilizer,GPSValidityStatus) values('''+GETDATE()+''','+@dataindication+','+

@vichelId+','+convert(varchar, @Datatime, 109)+','+@lat+','+@lang+','+@speed+','+@direction+','+@odometer+','+@Ignition+','+@digitalInputOne+','+@digitalInputTwo+','+@Immobilizer+','+

@GPSValidityStatus

+')'

EXEC sp_executesql @SQLString

end

Correct Procedure:

Cast each parameter of procedure as varchar because sp_executesql command operate only string data.

SET @SQLString = 'insert into Tab_'+convert(varchar,getdate(),112) +'([date],dataindication,vichelId,Datatime,lat,lang,speed

,direction,odometer,Ignition,digitalInputOne,digitalInputTwo,Immobilizer,GPSValidityStatus) values

('''+cast(GETDATE() as varchar)+''','+cast(@dataindication as varchar)+','+

@vichelId+','''+cast(@Datatime as varchar)

+''','+@lat+','+@lang+','+cast(@speed as varchar)+','+cast(@direction as varchar)+','

+cast(@odometer as varchar)+','+cast(@Ignition as varchar)+','+cast(@digitalInputOne as varchar)

+','+cast(@digitalInputTwo as varchar)+','+cast(@Immobilizer as varchar)+','+

cast(@GPSValidityStatus as varchar)

+')'

Insert Bulk row at a time in sql table from C#

(1) Initially create Type in sql server...its interesting.

Detail about Type in sql Follow this link:
http://technet.microsoft.com/en-us/library/ms175007.aspx
http://technet.microsoft.com/en-us/library/bb510489.aspx#Restrictions

----Create type-----
create type _table as table
(
E_Name varchar(50),
Salary varchar(200)
)

(2)After that create Procedure in sql:=>here i have to use _table type as table-valued parameter in Procedure.

The table-valued parameter "@tablevariable" must be declared with the READONLY option.

------Create procedure with table-valued parameter---

create proc uspInsertBulkRow

@tablevariable _table readonly
as
begin
insert into mytable(Name,salary) select E_Name,Salary from @tablevariable
end

(3)After that i have to pass datatable as parameter value in procedure and This procedure insert multiple row in sql table.
private DataTable _datatableData(){
       DataTable _dt = new DataTable();
   _dt.Columns.Add("id");
   _dt.Columns.Add("Name");
   _dt.Columns.Add("Salary");
   _dt.Rows.Add("1","Anu", "12000");
   _dt.Rows.Add("3","Anamika", "15000");
   _dt.Rows.Add("4", "Ram", "17000");
   _dt.Rows.Add("5","Aman", "4000");
   _dt.Rows.Add("6","Tarun", "18000");
   _dt.Rows.Add("7","Akash", "5000");
   return _dt;}

using (SqlCommand cmd = new SqlCommand("uspInsertBulkRow", con))
   {
// ------_datatableData() Is a method and its return datatable-----------
   cmd.Parameters.AddWithValue("@tablevariable", _datatableData());
   cmd.CommandType = CommandType.StoredProcedure;
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
   }
Output : It's a simple example.you can use this concept as per requirement

How to delete duplicate row from database table in Sql server

(1) select * from student
duplicate row in table

(2)select ROW_NUMBER() over(partition by name order by name) R
,name,dob from student
R is Row number

(3)delete q from(select ROW_NUMBER() over(partition by name order by name) R
,name,dob from student)q where R>1
(4)After delete command table look like.

Refrence Link

http://www.sqlservercentral.com/articles/duplicate+data/102383/

Wednesday 24 September 2014

Controllers in MVC application

In this article we will discuss about controllers. In previous article , we discussed that, the URL - http://localhost/MVCDemo/Home/Index will invoke Index() function of HomeControllerclass. So, the question is, where is this mapping defined. The mapping is defined in Global.asax. Notice that in Global.asax we have RegisterRoutes() method.
RouteConfig.RegisterRoutes(RouteTable.Routes);

Right click on this method, and select "Go to Definition". Notice the implementation of RegisterRoutes() method in RouteConfig class. This method has got a default route.
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}

The following URL does not have id. This is not a problem because id is optional in the default route.
http://localhost/MVCDemo/Home/Index

Now pass id in the URL as shown below and press enter. Nothing happens.
http://localhost/MVCDemo/Home/Index/10

Change the Index() function in HomeController as shown below.
public string Index(string id)
{
return "The value of Id = " + id;
}

Enter the following URL and press enter. We get the output as expected.
http://localhost/MVCDemo/Home/Index/10

In the following URL, 10 is the value for id parameter and we also have a query string"name".
http://localhost/MVCDemo/home/index/10?name=Pragim

Change the Index() function in HomeController as shown below, to read both the parameter values.
public string Index(string id, string name)
{
return "The value of Id = " + id + " and Name = " + name;
}

Just like web forms, you can also use "Request.QueryString"
public string Index(string id)
{
return "The value of Id = " + id + " and Name = " + Request.QueryString["name"];
}

Creating first asp.net MVC application

In this article we will discuss about
1. Creating an asp.net mvc application
2. Understand how mvc request is processed as apposed to webform request

Creating an mvc application:
1. Open visual studio
2. Click File > New Project
3. Select "Web" from "Installed Templates" section
4. Select ASP.NET MVC 4 Web Application
5. Set Name="MVCDemo"
6. Click OK
7. Select "Empty" template. Select "Razor" as the ViewEngine. There are 2 built in view engines - Razor and ASPX. Razor is preferred by most mvc developers. We will discuss about Razor view engine in detail in a later article.
8. At this point you should have an mvc application created.

Notice that in the solution explorer, you have several folders - Models, Views, Controllers etc. As the names suggest these folders are going to contain Models, Views, and Controllers. We will discuss about Models, Views, and Controllers in a later article.

Now let's add a controller to our project
1. Right Click on "Controllers" folder
2. Select Add > Controller
3. Set Controller Name = HomeController
4. Leave rest of the defaults and click "Add"

We should have HomeController.cs added to "Controllers" folder.

At this point run the application by pressing CTRL+F5. Notice that you get an error as shown below.

To fix this error, we need to add a view with name, "Index". We will discuss about views in detail in next session. Let's fix it another way. The following is the function that is automatically added to HomeController class

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

Change the return type of Index() function from "ActionResult" to "string", and return string "Hello from MVC Application" instead of View().

public string Index()
{
return "Hello from MVC Application";
}

Run the application and notice that, the string is rendered on the screen. When you run the application, by default it is using built-in asp.net development server. Let's use IIS, to run the application instead of the built-in asp.net development server.
1. In the solution explorer, right click on the project and select "Properties"

2. Click on "Web" tab
3. Select "Use Local IIS Web Server" radio button
4. Notice that the Project Url is set to http://localhost/MVCDemo by default
5. Finally click on "Create Virtual Directory" button.

Run the application, and notice that the URL is "http://localhost/MVCDemo/"

Now change the URL to "http://localhost/MVCDemo/Home/index"

In the URL "Home" is the name of the controller and "Index" is the method within HomeController class.

So the improtant point to understand is that the URL is mapped to a controller action method. Where as in web forms application, the URL is mapped to a physical file. For example, in a web application, if we have to display the same message.
1. We add a webform and in the Page_load() event include Response.Write("Hello from ASP.NET Web Forms Application");
2. We then access WebForm1.aspx as shown below
http://localhost/WebFormsApplication/WebForm1.aspx
3. The Page load event gets executed and the message string is displayed.

Monday 22 September 2014

Bulk Edit Update Multiple Rows in ASP.Net GridView using CheckBoxes

In this article I will explain how to edit and update multiple rows in ASP.Net GridView using CheckBoxes i.e. the Rows which are checked will become editable and user can update multiple rows on one single Update button click.

For this sample to work you will need to download the Microsoft Northwind database using the following link

Download Northwind Database

Desingner Source Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

input[type=text], select{background-color:#FFDFD2; border:1px solid #ccc}

</style>

</head>

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" OnRowDataBound = "OnRowDataBound" DataKeyNames = "CustomerId">

<asp:TemplateField>

<asp:CheckBox ID = "chkAll" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />

</HeaderTemplate>

<asp:CheckBox runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Contact Name" ItemStyle-Width = "150">

<asp:Label runat="server" Text='<%# Eval("ContactName") %>'></asp:Label>

<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("ContactName") %>' Visible="false"></asp:TextBox>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Country" ItemStyle-Width = "150">

<asp:Label ID = "lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>

<asp:DropDownList ID="ddlCountries" runat="server" Visible = "false">

</asp:DropDownList>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<br />

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick = "Update" Visible = "false"/>

</form>

</body>

</html>

Code Behind :

using System;

using System.Web.UI.WebControls;

using System.Data;

using System.Linq;

using System.Configuration;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

this.BindGrid();

}

private void BindGrid()

{

SqlCommand cmd = new SqlCommand("SELECT top 10 CustomerId, ContactName, Country FROM Customers");

gvCustomers.DataSource = this.ExecuteQuery(cmd, "SELECT");

gvCustomers.DataBind();

}

private DataTable ExecuteQuery(SqlCommand cmd, string action)

{

string conString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

using (SqlConnection con = new SqlConnection(conString))

{

cmd.Connection = con;

switch (action)

{

case "SELECT":

using (SqlDataAdapter sda = new SqlDataAdapter())

{

sda.SelectCommand = cmd;

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

return dt;

}

case "UPDATE":

con.Open();

cmd.ExecuteNonQuery();

con.Close();

break;

}

return null;

}

protected void Update(object sender, EventArgs e)

{

foreach (GridViewRow row in gvCustomers.Rows)

{

if (row.RowType == DataControlRowType.DataRow)

{

bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;

if (isChecked)

{

SqlCommand cmd = new SqlCommand("UPDATE Customers SET ContactName = @ContactName, Country = @Country WHERE CustomerId = @CustomerId");

cmd.Parameters.AddWithValue("@ContactName", row.Cells[1].Controls.OfType<TextBox>().FirstOrDefault().Text);

cmd.Parameters.AddWithValue("@Country", row.Cells[2].Controls.OfType<DropDownList>().FirstOrDefault().SelectedItem.Value);

cmd.Parameters.AddWithValue("@CustomerId", gvCustomers.DataKeys[row.RowIndex].Value);

this.ExecuteQuery(cmd, "UPDATE");

}

btnUpdate.Visible = false;

this.BindGrid();

}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

SqlCommand cmd = new SqlCommand("SELECT DISTINCT(Country) FROM Customers");

DropDownList ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);

ddlCountries.DataSource = this.ExecuteQuery(cmd, "SELECT");

ddlCountries.DataTextField = "Country";

ddlCountries.DataValueField = "Country";

ddlCountries.DataBind();

string country = (e.Row.FindControl("lblCountry") as Label).Text;

ddlCountries.Items.FindByValue(country).Selected = true;

}

protected void OnCheckedChanged(object sender, EventArgs e)

{

bool isUpdateVisible = false;

CheckBox chk = (sender as CheckBox);

if (chk.ID == "chkAll")

{

foreach (GridViewRow row in gvCustomers.Rows)

{

if (row.RowType == DataControlRowType.DataRow)

{

row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked = chk.Checked;

}

CheckBox chkAll = (gvCustomers.HeaderRow.FindControl("chkAll") as CheckBox);

chkAll.Checked = true;

foreach (GridViewRow row in gvCustomers.Rows)

{

if (row.RowType == DataControlRowType.DataRow)

{

bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;

for (int i = 1; i < row.Cells.Count; i++)

{

row.Cells[i].Controls.OfType<Label>().FirstOrDefault().Visible = !isChecked;

if (row.Cells[i].Controls.OfType<TextBox>().ToList().Count > 0)

{

row.Cells[i].Controls.OfType<TextBox>().FirstOrDefault().Visible = isChecked;

}

if (row.Cells[i].Controls.OfType<DropDownList>().ToList().Count > 0)

{

row.Cells[i].Controls.OfType<DropDownList>().FirstOrDefault().Visible = isChecked;

}

if (isChecked && !isUpdateVisible)

{

isUpdateVisible = true;

}

if (!isChecked )

{

chkAll.Checked = false;

}

btnUpdate.Visible = isUpdateVisible;

}

Download Here

Ref-http://msdn.microsoft.com/en-us/library/aa992036.aspx

Menu

Tuesday 30 September 2014

Wednesday 24 September 2014

Monday 22 September 2014