Tuesday, 30 September 2014
Find user defined database name in Sq l server
how to create tablename dynamically in SQL server
Write this quary and get table name append with current date
CREATE proc createtable
as
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-----
---Temprory table-----------------------------------------------------------
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
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.
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---
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
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 proc uspInsertBulkRow
@tablevariable _table readonlyas
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
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.
,name,dob from student)q where R>1
(4)After delete command table look like.
Refrence Link
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"];
}
Labels:
MVC
Location:
Ghaziabad, Uttar Pradesh, India
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.
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().
{
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{
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"
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.
Subscribe to:
Posts (Atom)