How to create a drag able and resizable div in ASP.Net/C# OR How to make a div Dragable and Resizable using Jquery in ASP.Net/C#
DOWNLOAD SOURCE CODE FOR DAG ABLE AND RE-SIZABLE DIV
Resize and drag a div in ASPX page/HTML Page
Here we are going to demonstrate making a div that can be drag and resize by users. We are achieving this drag and resize of div functionality using Jquery files. We already posted some article related with Jquery and Javascript. Here we are again demonstrating a detailed post regarding Draggable and Resizable div using Jquery files.
Drag able and Resizable Div using Jquery in HTML/ASPX Page
For implementing drag and resize Div, we need to download following jquery and css files and included in the ASPX/HTML page.
1. jquery.min.js – Download from here
2. jquery-ui.min.js – Download from here
3. jquery-ui.css – Download from here
After downloading above files we need to create a HTML/ASPX Page and include above files as mentioned below.
<script src="jquery.min.js" type="text/javascript"></script> <script src="jquery-ui.min.js" type="text/javascript"></script> <link rel="stylesheet" href="jquery-ui.css" type="text/css" media="all" />
HTML/ASPX Page for creating drag able and re sizable div
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Page</title>
<script src="jquery.min.js" type="text/javascript"></script>
<script src="jquery-ui.min.js" type="text/javascript"></script>
<link rel="stylesheet" href="jquery-ui.css"
type="text/css" media="all" />
<style type="text/css">
#resizediv
{
width: 150px;
height: 150px;
padding: 0.5em;
background: #EB5E00;
color: #fff;
}
</style>
<script type="text/javascript">
$(document).ready(function () {
$("#resizediv").resizable();
$("#resizediv").draggable();
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div id="resizediv">
Drag me ... or Resize me....<br />
This is a Div that can be Drag and Resize by you.. .
</div>
</form>
</body>
</html>
------
DOWNLOAD SOURCE CODE FOR DAG ABLE AND RE-SIZABLE DIV
How to implement BalloonPopupExtender in ASP.Net/C# OR Ballon Popup Extender Sample in ASP.Net/C#
DOWNLOAD SOURCE CODE FOR BALLOON POPUP EXTENDER EXAMPLE
How to display good POPUP Window in ASP.Net using Ajax?
Most of the ASP.Net application requires showing some POPUP window to user for showing some messages or information. Also in order to create a ToolTips in ASP.Net/C# web application it’s very difficult to achieve for good looking ToolTip. In the newest Ajax Control Toolkit includes an awesome good looking and more functional popup extender called Balloon POPUP Extender. It’s a very light weight and having good look and feel popup, also we can apply custom styles for POPUP. Balloon POPUP Extender can use as ToolTips in ASP.Net application.
Download and Register New AjaxControl Toolkit
For implementing Balloon POPUP Extender in ASP.Net/C# application, we need to download newest Ajax Control Toolkit and register to the Project references. You can download new Ajax Control Toolkit from this link. Right click the ‘References’ under the ASP.Net project from Visual Studio and browse the dll and press OK.
In the ASPX page we need to register this ajax toolkit dll like below mentioned code.
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
How to implement Balloon POPUP Extender in ASP.Net/C# Application?
After adding and register the Ajax Control Toolkit as mentioned above, we can start to implement the Balloon POPUP Extender. We can implement Balloon POPUP Extender while clicking/on mouse over/on focus of any server controls in ASP.Net. Below mentioned is the codes for implementing the same.
<ajax:BalloonPopupExtender ID="PopupControlExtender2" runat="server" TargetControlID="txtUserName" BalloonPopupControlID="pnlBalloon" Position="BottomRight" BalloonStyle="Cloud" BalloonSize="Medium" UseShadow="true" ScrollBars="Auto" DisplayOnMouseOver="true" DisplayOnFocus="false" DisplayOnClick="true" />
There are some properties are available for Balloon POPUP Extender control and we can adjust the size,color,style and controls of POPUP using these properties. Below are the main properties of Balloon POPUP Extender
TargetControlID – The ID of the control to attach to.
BalloonPopupControlID – The ID of the control to display.
Position – Optional setting specifying where the popup should be positioned relative to the target control. (TopRight, TopLeft, BottomRight, BottomLeft, Auto) Default value is Auto.
OffsetX/OffsetY – The number of pixels to offset the Popup from its default position, as specified by Position. Default value is 0.
BalloonStyle – Optional setting specifying the theme of balloon popup. (Cloud, Rectangle, Custom). Default value is Rectangle.
BalloonSize – Optional setting specifying the size of balloon popup. (Small, Medium and Large). Default value is Small.
CustomCssUrl – This is required if user choose BalloonStyle to Custom. This specifies the url of custom css which will display custom theme.
CustomClassName – This is required if user choose BalloonStyle to Custom. This specifies the name of the css class for the custom theme.
UseShadow – Optional setting specifying whether to display shadow of balloon popup or not.
ScrollBars – Optional setting specifying whether to display scrollbar if contents are overflowing. This property contains 5 options – None, Horizontal, Vertical, Both and Auto. Default value is Auto.
DisplayOnMouseOver – Optional setting specifying whether to display balloon popup on the client onMouseOver event. Default value is false.
DisplayOnFocus – Optional setting specifying whether to display balloon popup on the client onFocus event. Default value is false.
DisplayOnClick – Optional setting specifying whether to display balloon popup on the client onClick event. Default value is true.
Animations – Generic animations for the PopupControlExtender.
OnShow – The OnShow animation will be played each time the popup is displayed. The popup will be positioned correctly but hidden. The animation can use <HideAction Visible=”true” /> to display the popup along with any other visual effects.
OnHide – The OnHide animation will be played each time the popup is hidden.
A very simple example for Balloon POPUP Extender in ASP.Net/C# using Ajax
Here we are demonstrating a very simple for implementing Ajax Balloon POPUP Extender with full downloadable source code.
ASPX Page
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="BaloonPopup._Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"
TagPrefix="ajax" %>
<asp:Content ID="HeaderContent" runat="server"
ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server"
ContentPlaceHolderID="MainContent">
<h2>
Welcome to Baloon Popup Extender Sample
</h2>
<p>
For Website/WebApplication creation <a href="http://www.tuvian.com"
title="aps.net">www.tuvian.com</a>.
</p>
<div>
<ajax:ToolkitScriptManager ID="Scriptmanager1" runat="server">
</ajax:ToolkitScriptManager>
<div style="border: 1px solid gray; padding: 10px; margin: 10px;">
<h3>
Cloud Style Baloon Popup Example</h3>
<br />
<br />
<ajax:BalloonPopupExtender ID="PopupControlExtender2" runat="server"
TargetControlID="txtUserName"
BalloonPopupControlID="pnlBalloon" Position="BottomRight" BalloonStyle="Cloud"
BalloonSize="Medium" UseShadow="true" ScrollBars="Auto" DisplayOnMouseOver="true"
DisplayOnFocus="false" DisplayOnClick="true" />
UserName :
<asp:TextBox runat="server" ID="txtUserName" />
<asp:Panel runat="server" ID="pnlBalloon">
This is the Cloud Style Ballon Popup</asp:Panel>
</div>
<div style="border: 1px solid gray; padding: 10px; margin: 10px;">
<h3>
Rectangular Baloon Popup Example</h3>
<br />
<br />
<ajax:BalloonPopupExtender ID="Balloonpopupextender1" runat="server"
TargetControlID="lblShow"
BalloonPopupControlID="pnlRectangularBallon" Position="TopRight"
BalloonStyle="Rectangle"
BalloonSize="Medium" UseShadow="true" ScrollBars="Auto"
DisplayOnMouseOver="false"
DisplayOnFocus="false" DisplayOnClick="true" />
<asp:Label runat="server" ID="lblShow"
Text="Click Here to Show the Rectangular Balloon Popup" />
<asp:Panel runat="server" ID="pnlRectangularBallon">
This is the rectangular ballon popup</asp:Panel>
</div>
<div style="border: 1px solid gray; padding: 10px; margin: 10px;">
<h3>
Custom Style Baloon Popup Example</h3>
<br />
<br />
<ajax:BalloonPopupExtender ID="Balloonpopupextender2" runat="server"
TargetControlID="txtCustomBallonPopup"
BalloonPopupControlID="pnlCustomBallon" Position="BottomRight"
BalloonStyle="Custom"
BalloonSize="Medium" UseShadow="true" ScrollBars="Auto"
DisplayOnMouseOver="true"
CustomCssUrl="Styles/BalloonPopupOvalStyle.css"
CustomClassName="oval" DisplayOnFocus="false"
DisplayOnClick="true" />
<asp:TextBox runat="server" ID="txtCustomBallonPopup" />
<asp:Panel runat="server" ID="pnlCustomBallon">
This is the Custom Style ballon popup</asp:Panel>
</div>
</div>
</asp:Content>
--------------
DOWNLOAD SOURCE CODE FOR BALLOON POPUP EXTENDER EXAMPLE
How to create always visible div using Ajax/ Always visible div in ASP.Net using Ajax
DOWNLOAD SOURCE CODE FOR ALWAYS VISIBLE DIV
Create very simple always visible div using ajax in ASP.Net
Here we are demonstrating how to create a very simple always visible div using Ajax in ASP.Net/C#. Some of the WebPages we may need to show some information always visible to user even they are scrolling down and up. In this situation we can achieved it by different method. In our previous post we already posted how to create a always visible div using CSS. Here we are going to implement the always visible div using Ajax in ASP.Net/C#.
Register Ajax Control Toolkit
In order to implement always visible div using Ajax, we need to download and refer ajax control toolkit to our project. First of all download ajaxtoolkit dll from here and right click the ‘References’ under the ASP.Net project from Visual Studio and browse the dll and press OK.
In the ASPX page we need to register this ajax toolkit dll like below mentioned code.
<%@ Register Assembly=”AjaxControlToolkit” Namespace=”AjaxControlToolkit” TagPrefix=”ajax” %>
How to implement AlwaysVisibleControlExtender in AJAX Controls?
Always vissible Control Extender in Ajax control tool kit is using to implement always vissible div or any controls in ASP.Net/C#.
After registering the ajax control tool kit we can implement alwaysvisibleextender as below mentioned code.
<ajax:alwaysvisiblecontrolextender id="ace" runat="server" targetcontrolid="pnlTimer" verticalside="Top" verticaloffset="10" horizontalside="Right" horizontaloffset="10" scrolleffectduration=".1" />
We are having some properties in the alwaysvisiblecontrolextender and we can adjust the postion and controls for always visible extender. Below are the main properties of alwaysvissibleextender in ajax.
TargetControlID – ID of control for this extender to always make visible
HorizontalOffset – Distance to the HorizontalSide edge of the browser in pixels from the same side of the target control. The default is 0 pixels.
HorizontalSide – Horizontal edge of the browser (either Left, Center, or Right) used to anchor the target control. The default is Left.
VerticalOffset – Distance to the VerticalSide edge of the browser in pixels from the same side of the target control. The default is 0 pixels.
VerticalSide – Vertical edge of the browser (either Top, Middle, or Bottom) used to anchor the target control. The default is Top.
ScrollEffectDuration – Length in seconds of the scrolling effect to last when the target control is repositioned. The default is .1 second.
ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AjaxControls.aspx.cs" Inherits="ExperimentLab.AjaxControls" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <ajax:ToolkitScriptManager ID="Scriptmanager1" runat="server"> </ajax:ToolkitScriptManager> <div> <ajax:AlwaysVisibleControlExtender id="ace" runat="server" targetcontrolid="pnlTimer" verticalside="Top" verticaloffset="10" horizontalside="Right" horizontaloffset="10" scrolleffectduration=".1" /> <div runat="server" id="pnlTimer" style="border: 2px solid green; height: 100px; width: 200px; text-align: center; padding-top: 50px; font-weight: bold;"> This div is always visible to user </div> <br /> <br /> Scroll down . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> Scroll down <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> Scroll down <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> . <br /> Still 'Always visible div' is available ...! </div> </form> </body> </html> ------
Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)
Definition and very simple examples for each JOINS in SQL
Here we are demonstrating with examples all types of JOINS in SQL Server. First of all we created sample tables and as per the table data we are explaining each SQL JOINS with most understandable examples.
Sample tables
In the following tables, Department.DepartmentID is the primary key, while Employee.DepartmentID is a foreign key.
|
|
||||||||||||||||||||||||||||
Inner join
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) – then return all records which satisfy the join predicate.
SQL specifies two different syntactical ways to express joins. The first, called “explicit join notation”, uses the keyword JOIN, whereas the second uses the “implicit join notation”. The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates. Those filter-predicates function comparably to join-predicates in the explicit notation.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins (see below).
Example of an explicit inner join:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID Is equivalent to: SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID
Explicit Inner join result:
|
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
|
Smith |
34 |
Clerical |
34 |
|
Jones |
33 |
Engineering |
33 |
|
Robinson |
34 |
Clerical |
34 |
|
Steinberg |
33 |
Engineering |
33 |
|
Rafferty |
31 |
Sales |
31 |
Types of inner joins
Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.
SELECT Employee.lastName, Employee.DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID; SQL provides optional syntactic sugar for expressing equi-joins, by way of the USING construct (Feature ID F402): SELECT Employee.lastName, DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department USING(DepartmentID);
The USING clause is supported by MySQL, Oracle and PostgreSQL.
Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
The above sample query for inner joins can be expressed as a natural join in the following way:
SELECT * FROM employee NATURAL JOIN department
The result appears slightly different, however, because only one DepartmentID column occurs in the joined table.
|
DepartmentID |
Employee.LastName |
Department.DepartmentName |
|
34 |
Smith |
Clerical |
|
33 |
Jones |
Engineering |
|
34 |
Robinson |
Clerical |
|
33 |
Steinberg |
Engineering |
|
31 |
Rafferty |
Sales |
Cross join
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.
If A and B are two sets, then the cross join is written as A × B.
The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.
Example of an explicit cross join:
SELECT * FROM employee CROSS JOIN department
Example of an implicit cross join:
SELECT *
FROM employee, department;
|
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
|
Rafferty |
31 |
Sales |
31 |
|
Jones |
33 |
Sales |
31 |
|
Steinberg |
33 |
Sales |
31 |
|
Smith |
34 |
Sales |
31 |
|
Robinson |
34 |
Sales |
31 |
|
Jasper |
NULL |
Sales |
31 |
|
Rafferty |
31 |
Engineering |
33 |
|
Jones |
33 |
Engineering |
33 |
|
Steinberg |
33 |
Engineering |
33 |
|
Smith |
34 |
Engineering |
33 |
|
Robinson |
34 |
Engineering |
33 |
|
Jasper |
NULL |
Engineering |
33 |
|
Rafferty |
31 |
Clerical |
34 |
|
Jones |
33 |
Clerical |
34 |
|
Steinberg |
33 |
Clerical |
34 |
|
Smith |
34 |
Clerical |
34 |
|
Robinson |
34 |
Clerical |
34 |
|
Jasper |
NULL |
Clerical |
34 |
|
Rafferty |
31 |
Marketing |
35 |
|
Jones |
33 |
Marketing |
35 |
|
Steinberg |
33 |
Marketing |
35 |
|
Smith |
34 |
Marketing |
35 |
|
Robinson |
34 |
Marketing |
35 |
|
Jasper |
NULL |
Marketing |
35 |
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
Left outer join
The result of a left outer join (or simply left join) for tables A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
|
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
|
Jones |
33 |
Engineering |
33 |
|
Rafferty |
31 |
Sales |
31 |
|
Robinson |
34 |
Clerical |
34 |
|
Smith |
34 |
Clerical |
34 |
|
Jasper |
NULL |
NULL |
NULL |
|
Steinberg |
33 |
Engineering |
33 |
Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in A.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
|
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
|
Smith |
34 |
Clerical |
34 |
|
Jones |
33 |
Engineering |
33 |
|
Robinson |
34 |
Clerical |
34 |
|
Steinberg |
33 |
Engineering |
33 |
|
Rafferty |
31 |
Sales |
31 |
|
NULL |
NULL |
Marketing |
35 |
In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality.
Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn’t have an employee.
Example full outer join:
SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
|
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
|
Smith |
34 |
Clerical |
34 |
|
Jones |
33 |
Engineering |
33 |
|
Robinson |
34 |
Clerical |
34 |
|
Jasper |
NULL |
NULL |
NULL |
|
Steinberg |
33 |
Engineering |
33 |
|
Rafferty |
31 |
Sales |
31 |
|
NULL |
NULL |
Marketing |
35 |
Self-join
A self-join is joining a table to itself.
Example
A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.
Considering a modified Employee table such as the following:
|
Employee Table |
|||
|
EmployeeID |
LastName |
Country |
DepartmentID |
|
123 |
Rafferty |
Australia |
31 |
|
124 |
Jones |
Australia |
33 |
|
145 |
Steinberg |
Australia |
33 |
|
201 |
Robinson |
United States |
34 |
|
305 |
Smith |
United Kingdom |
34 |
|
306 |
Jasper |
United Kingdom |
NULL |
An example solution query could be as follows:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;
Which results in the following table being generated.
|
Employee Table after Self-join by Country |
||||
|
EmployeeID |
LastName |
EmployeeID |
LastName |
Country |
|
123 |
Rafferty |
124 |
Jones |
Australia |
|
123 |
Rafferty |
145 |
Steinberg |
Australia |
|
124 |
Jones |
145 |
Steinberg |
Australia |
|
305 |
Smith |
306 |
Jasper |
United Kingdom |
How to Create a Data Table Dynamically with sample data and Bind to Grid/Create datatable with sample data.
How to create a datatable with sample data
We are going to demonstrate how to create a sample datatable dynamically in ASP.Net. Here we are creating a ASPx page with a grid view. In the page load of the ASPx page will call a bind method which creating a datatable dynamically and bind the datatable with datagrid. In BindGridviewData function create data for an employee and binding employee data to the employee grid.
ASPxPage
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DynamicCreateDataTable.aspx.cs" Inherits="ExperimentLab.DynamicCreateDataTable" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="gdEmployee" runat="server"> </asp:GridView> </div> </form> </body> </html>
Code Behind
protected void Page_Load(object sender, EventArgs e)
{
BindGridviewData();
}
//Creating datatable dynamically and bind data to a grid
protected void BindGridviewData()
{
DataTable dt = new DataTable();
//Create datatable Columns dynamically
dt.Columns.Add("EmployeeID", typeof(Int32));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("City", typeof(string));
//Create data table row dynamically add
DataRow dtrow = dt.NewRow(); // Create New Row
//Assign data to datarow dynamically
dtrow["EmployeeID"] = 1; //Bind Data to Columns
dtrow["EmployeeName"] = "Ramraj";
dtrow["Department"] = "Admin";
dtrow["City"] = "Calicut";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow(); // Create New Row
//Assign data to datarow dynamically
dtrow["EmployeeID"] = 2; //Bind Data to Columns
dtrow["EmployeeName"] = "Malhothra";
dtrow["Department"] = "HR";
dtrow["City"] = "Mumbai";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow(); // Create New Row
//Assign data to datarow dynamically
dtrow["EmployeeID"] = 3; //Bind Data to Columns
dtrow["EmployeeName"] = "Sinan Hafis";
dtrow["Department"] = "Admin";
dtrow["City"] = "Delhi";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow(); // Create New Row
//Assign data to datarow dynamically
dtrow["EmployeeID"] = 4; //Bind Data to Columns
dtrow["EmployeeName"] = "Wazim Jafar";
dtrow["Department"] = "Finance";
dtrow["City"] = "Goa";
dt.Rows.Add(dtrow);
gdEmployee.DataSource = dt;
gdEmployee.DataBind();
}
RESULT
Dynamic datatable in ASP.Net
How to delete large amount of rows from table in SQL
How to delete huge amount of data from a table in SQL
In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.
Query for recursive deletion from the table in SQL
Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query
WHILE exists ( SELECT * FROM myTable WHERE name like ‘%ab%’ ) DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;
How to Create and use Table-Valued Parameter in C# and T-SQL/ How to pass table to stored procedures in SQL
What is Table – Valued Parameter in SQL Server 2008?
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.
What we are going to do with Table – Valued Parameter?
We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.
Create a Table for insert data using Table – Valued Parameter
Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.
CREATE TABLE Officer( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(50), SALARY DECIMAL(18, 0))
Stored Procedure for insert data by accepting Table Valued Parameter
Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.
CREATE PROCEDURE InsertOfficerDetails ( @OfficerData OfficerDetails readonly ) AS INSERT INTO Officer (Name, Salary) SELECT Name, Salary FROM @OfficerData;
C# code to call Stored Procedure to insert data in to the table using Table – Valued Parameter
We are creating a simple ASPX page with a single button. When we click this button we are calling above stored procedure by creating and passing some amount of sample data to the stored procedure as Table – Valued Parameter.
ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CallSP.aspx.cs" Inherits="ExperimentLab.CallSP" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="btnCallSP" runat="server" Text="Call SP" OnClick="btnCallSP_Click" /> </div> </form> </body> </html>
Code Behind
protected void btnCallSP_Click(object sender, EventArgs e)
{
try
{
DataTable dt = new DataTable();
DataColumn dtCol = new DataColumn();
dtCol.ColumnName = "ID";
dt.Columns.Add(dtCol);
dtCol = new DataColumn();
dtCol.ColumnName = "Name";
dt.Columns.Add(dtCol);
dtCol = new DataColumn();
dtCol.ColumnName = "Salary";
dt.Columns.Add(dtCol);
for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["Name"] = "Name " + i;
dr["Salary"] = 1000 + i;
dr["ID"] = i;
dt.Rows.Add(dr);
}
string connStr = ConfigurationManager.
AppSettings["LocalSqlServer"].ToString();
SqlConnection con = new SqlConnection(connStr);
using (var conn = new SqlConnection(connStr))
using (var cmd = conn.CreateCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.InsertOfficerDetails";
SqlParameter param = cmd.Parameters.AddWithValue("@OfficerData", dt);
cmd.ExecuteNonQuery();
}
}
catch (Exception a)
{
Response.Write(a.Message);
}
}
Web.Config
<appSettings> <add key="LocalSqlServer" value="Database=testDB;Server=Servername\SQLEXPRESS;User Id=userid;Password=password"/> </appSettings>
Hence we discussed about how to create and use Table valued parameters, how to create a store procedure with table type as parameter, how to pass table to stored procedure in C#/Asp.Net, how to insert multiple rows of data to a table with table valued parameter in SQL, how to insert bulk data to SQL table using Table Valued Parameter in SQL Server 2008 etc..
Site Viewers
Recent Comments