By the following query you will get the foreign key and primary key relationship between all the tables in database.
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
Get foreign key of a table in SQL
Get Primary key of a table in SQL
By the following query you will get column name of table in current database.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = ‘Table_Name’
LINQ In Javascript
By the following code you can use the LINQ in javascript.
For that you need JSLINQ.js file.
You will get it from http://jslinq.codeplex.com/releases/view/28886
<script type=”text/javascript” src=”JSLINQ.js”></script>
<script type=”text/javascript”>
function LINQInJavaScript() {
var myList = [
{ FirstName: “Chris”, LastName: “Pearson” },
{ FirstName: “Kate”, LastName: “Johnson” },
{ FirstName: “Josh”, LastName: “Sutherland” },
{ FirstName: “John”, LastName: “Ronald” },
{ FirstName: “Steve”, LastName: “Pinkerton” }
];
var whereExample1 = JSLINQ(myList).Where(function(item) { return item.FirstName == “Chris”; });
alert(whereExample1.items[0].FirstName);
</script>
Javascript function for multiplication in table dynamically
Consider the table will be generated dynamically like below,
For ex,
var table = document.getElementById(“DynamicTable”);//In this div, you have to put table tag.
for (var i = 0; i <= rowLength; i++) {
var row = table.insertRow(i);
for (var j = 0; j < columnLength + extraColumns; j++) {
var col = row.insertCell(j);
col.innerHTML = “<input type=’text’ onkeyup=’GetSelectedRow(this)’/>’;
}
}
Then function will be follows,
function GetSelectedRow(txt) {
var row = txt.parentNode.parentNode;
var price = Number(row.cells[0].getElementsByTagName(“input”)[0].value);
var qty = Number(row.cells[1].getElementsByTagName(“input”)[0].value);
var Total = price * qty;
row.cells[2].innerHTML = Total;
}
Multiplication of two columns in gridview and display the result in third column with footer dynamically
We can display the multiplication result of two column value in third column and net total in footer of gridview.
In .aspx page,
<asp:GridView ID=”GridView1″ runat=”server”>
<Columns>
<asp:TemplateField HeaderText=”Price”>
<ItemTemplate>
<asp:TextBox ID=”txtPrice” runat=”server” onkeyup=”GetSelectedRow(this)” ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Qty”>
<ItemTemplate>
<asp:TextBox ID=”txtQty” runat=”server” onkeyup=”GetSelectedRow(this)”></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Total”>
<ItemTemplate>
<asp:Label ID=”lblTotal” runat=”server”></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID=”Label1″ runat=”server” Text=”Total”></asp:Label>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
In Javascript,
function GetSelectedRow(txt) {
var row = txt.parentNode.parentNode;
var price = Number(row.cells[0].getElementsByTagName(“input”)[0].value);
var qty = Number(row.cells[1].getElementsByTagName(“input”)[0].value);
var Total = price * qty;
row.cells[2].getElementsByTagName(“span”)[0].innerHTML = Total;
var NetTotal = 0;
var grid = document.getElementById(“<%=GridView1.ClientID%>”);
var gridLength = grid.rows.length;
for (i = 1; i < gridLength; i++) {
if (grid.rows[i].cells[0].colSpan == 1) {
if (typeof (grid.rows[i].cells[2].text) != ‘undefined’) // This checking is for to support in all browser
number = grid.rows[i].cells[2].text;
else if (typeof (grid.rows[i].cells[2].textContent) != ‘undefined’)
number = grid.rows[i].cells[2].textContent;
else if (typeof (grid.rows[i].cells[2].innerText) != ‘undefined’)
number = grid.rows[i].cells[2].innerText;
//if (!isNaN(number))
NetTotal = Number(NetTotal) + Number(number);
}
}
GrandTotal = Number(NetTotal) – Number(number);
var footerText = (grid.rows[gridLength – 1].cells[2]);
if (typeof (footerText.text) != ‘undefined’)
footerText.text = GrandTotal;
else if (typeof (footerText.textContent) != ‘undefined’)
footerText.textContent = GrandTotal;
else if (typeof (footerText.innerText) != ‘undefined’)
footerText.innerText = GrandTotal;
}
}
Nested GridView in C#.Net
Inside the gridview’s templatefield of itemtemplate you can add one more gridview.
In .aspx page,
<asp:GridView ID=”GridView1″ runat=”server”>
<Columns>
<asp:BoundField DataField=”SNo” HeaderText=”S.No”/>
<asp:BoundField DataField=”CategoryName” HeaderText=”Product Name”/>
<asp:TemplateField>
<ItemTemplate>
<asp:GridView ID=”GridView2″ runat=”server” AutoGenerateColumns=”false”>
<Columns>
<asp:BoundField DataField=”SNo” HeaderText=”S.No”/>
<asp:BoundField DataField=”CategoryName” HeaderText=”Product Name”/>
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
In code behind,
protected void Page_Load(object sender, EventArgs e)
{
da.Fill(dt);
GridView1.DataSource = dt; // Data source for outer grid
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
GridView gv = (GridView)e.Row.FindControl(“GridView2”); //Finding inner grid
DataTable dt = new DataTable();
da.Fill(dt);
gv.DataSource = dt; // Data source for inner grid.
gv.DataBind();
}
}
Dataset to XML document in VB.NET
Dim doc As XmlDataDocument = New XmlDataDocument(ds)
Response.Clear()
Response.ContentType = “text/xml”
Response.ContentEncoding = System.Text.Encoding.UTF8
doc.Save(Response.Output)
Response.End()
(OR)
Dim doc As XmlDataDocument = New XmlDataDocument(ds)
doc.LoadXml(ds.GetXml)
doc.Save(Response.Output)
Regular expression in javascript
In Javascript,
function Validate()
{
var txt = document.getElementById(“<%=txt.ClientID %>”);
var regEx = /^[0-9]{1,6}?$/; // For not more than 6 numbers
if (regEx.test(txt.value))
alert(“Correct.”);
else
{
alert(“Only 6 numbers.”);
txt.focus();
}
}
In CodeBehind,
<asp:TextBox ID=”txt” runat=”server” onblur=”Validate()”>
AJAX AutoCompleteExtender without WebService
Consider an example of search by name,
In database create a table that consist of names.
In design page,
<div>
Name to search
<asp:TextBox ID=”TextBox1″ runat=”server”></asp:TextBox>
<asp:ToolkitScriptManager ID=”ToolkitScriptManager1″ runat=”server”>
</asp:ToolkitScriptManager>
<asp:AutoCompleteExtender ID=”AutoCompleteExtender1″ TargetControlID=”TextBox1″ runat=”server”
UseContextKey=”True” ServiceMethod=”GetCountries” MinimumPrefixLength=”1″
CompletionSetCount=”1″ CompletionInterval=”50″>
</asp:AutoCompleteExtender>
</div>
In code behind,
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCountries(string prefixText)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings[“dbconnection”].ToString());
con.Open();
SqlCommand cmd = new SqlCommand(“select name from tblName where name like @Name+’%'”, con);
cmd.Parameters.AddWithValue(“@Name”, prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List<string> CountryNames = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CountryNames.Add(dt.Rows[i][0].ToString());
}
return CountryNames;
}
Passing column name as argument to procedure in SQL
By the following way you can pass the column name as a parameter/argument to the procedure in SQL.
create proc PROCEDURENAME
as
begin
declare @CoumnName varchar(30),@Query varchar(100);
set @ColumnName=’ColumnName’;
set @Query=’select FoodName,ImageUrl,Price from tblFood where ‘+@ColumnName+’=condition’
exec sp_executesql @Query
end
Gobi N 