Saturday, 9 March 2013
How to use DataView AddNew Method to add a new row to the DataView in ado.net
<%@ Page Language="C#" AutoEventWireup="true" %>
Using AllowEdit property - how to edit update row in a DataView in ado.net
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Button1_Click(object sender, System.EventArgs e)
{
DataTable dt = new DataTable();
dt.TableName = "Books";
DataColumn dc1 = new DataColumn();
dc1.ColumnName = "BookID";
dc1.DataType = typeof(int);
dc1.AllowDBNull = false;
dc1.Unique = true;
DataColumn dc2 = new DataColumn();
dc2.ColumnName = "Category";
dc2.DataType = typeof(string);
DataColumn dc3 = new DataColumn();
dc3.ColumnName = "BookName";
dc3.DataType = typeof(string);
DataColumn dc4 = new DataColumn();
dc4.ColumnName = "Author";
dc4.DataType = typeof(string);
dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4 });
dt.Rows.Add(new object[] { 1, ".NET", "ASP.NET 3.5 Application Architecture and Design", "Vivek Thakur" });
dt.Rows.Add(new object[] { 2, "Games", "Cocos2d for iPhone 0.99 Beginner's Guide", "Pablo Ruiz" });
dt.AcceptChanges();
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
//this line create a new DataView
DataView dView = new DataView(dt);
dView.Sort = "BookName DESC" ;
Label1.Text = "Here we create a new DataView<br />" +
"and set the sort order (BookName DESC)";
GridView2.DataSource = dView;
GridView2.DataBind();
dView.AllowEdit = true;
//this line edit a row in DataView which index is 0
dView[0]["Category"] = ".NET";
dView[0]["BookName"] = "Microsoft Windows Workflow Foundation 4.0 Cookbook";
dView[0]["Author"] = "Andrew Zhu ";
Label2.Text = "Here we edit a row in DataView, Now the DataView is...";
GridView3.DataSource = dView;
GridView3.DataBind();
Label3.Text = "Now the source DataTable is...";
GridView4.DataSource = dt;
GridView4.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Using AllowEdit property - how to edit update row in a DataView in ado.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2 style="color:DarkBlue; font-style:italic;">
Using AllowEdit property - how to
<br /> edit update row in a DataView in ado.net
</h2>
<hr width="450" align="left" color="CornFlowerBlue" />
<asp:GridView
ID="GridView1"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="700"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkMagenta" Height="30" />
<RowStyle BackColor="DarkKhaki" />
<AlternatingRowStyle BackColor="DarkOliveGreen" />
</asp:GridView>
<asp:Label
ID="Label1"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView2"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="700"
Font-Names="Courier"
>
<HeaderStyle BackColor="Maroon" Height="30" />
<RowStyle BackColor="DarkKhaki" />
<AlternatingRowStyle BackColor="DarkOliveGreen" />
</asp:GridView>
<asp:Label
ID="Label2"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView3"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="700"
Font-Names="Courier"
>
<HeaderStyle BackColor="Maroon" Height="30" />
<RowStyle BackColor="DarkKhaki" />
<AlternatingRowStyle BackColor="DarkOliveGreen" />
</asp:GridView>
<asp:Label
ID="Label3"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView4"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="700"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkMagenta" Height="30" />
<RowStyle BackColor="DarkKhaki" />
<AlternatingRowStyle BackColor="DarkOliveGreen" />
</asp:GridView>
<asp:Button
ID="Button1"
runat="server"
OnClick="Button1_Click"
Text="Populate GridView"
Height="45"
Font-Bold="true"
ForeColor="DodgerBlue"
/>
</div>
</form>
</body>
</html>
How to use DataView Delete Method to delete a row at the specified index in ado.net
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Button1_Click(object sender, System.EventArgs e)
{
DataTable dt = new DataTable();
dt.TableName = "Books";
DataColumn dc1 = new DataColumn();
dc1.ColumnName = "BookID";
dc1.DataType = typeof(int);
dc1.AllowDBNull = false;
dc1.Unique = true;
DataColumn dc2 = new DataColumn();
dc2.ColumnName = "Category";
dc2.DataType = typeof(string);
DataColumn dc3 = new DataColumn();
dc3.ColumnName = "BookName";
dc3.DataType = typeof(string);
DataColumn dc4 = new DataColumn();
dc4.ColumnName = "Author";
dc4.DataType = typeof(string);
dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4 });
dt.Rows.Add(new object[] { 1, "jQuery", "CMS Design Using PHP and jQuery", "Kae Verens" });
dt.Rows.Add(new object[] { 2, "iPhone", "iPhone Applications Tune-Up", "Loyal Moses" });
dt.Rows.Add(new object[] { 3, "jQuery", "jQuery Mobile First Look: RAW", "Giulio Bai" });
dt.AcceptChanges();
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
//this line create a new DataView
DataView dView = new DataView(dt);
dView.Sort = "BookName DESC" ;
Label1.Text = "Here we create a new DataView<br />" +
"and set the sort order (BookName DESC)";
GridView2.DataSource = dView;
GridView2.DataBind();
//this line delete a row from DataView which index is 1
dView.Delete(1);
Label2.Text = "Here we delete a row from DataView, Now the DataView is...";
GridView3.DataSource = dView;
GridView3.DataBind();
Label3.Text = "Now the source DataTable is...";
GridView4.DataSource = dt;
GridView4.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>How to use DataView Delete Method to delete a row at the specified index in ado.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2 style="color:DarkBlue; font-style:italic;">
How to use DataView Delete Method
<br /> to delete a row at the specified index in ado.net
</h2>
<hr width="575" align="left" color="CornFlowerBlue" />
<asp:GridView
ID="GridView1"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="575"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkOrchid" Height="30" />
<RowStyle BackColor="HotPink" />
<AlternatingRowStyle BackColor="DeepPink" />
</asp:GridView>
<asp:Label
ID="Label1"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView2"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="575"
Font-Names="Courier"
>
<HeaderStyle BackColor="Navy" Height="30" />
<RowStyle BackColor="HotPink" />
<AlternatingRowStyle BackColor="DeepPink" />
</asp:GridView>
<asp:Label
ID="Label2"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView3"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="575"
Font-Names="Courier"
>
<HeaderStyle BackColor="Navy" Height="30" />
<RowStyle BackColor="HotPink" />
<AlternatingRowStyle BackColor="DeepPink" />
</asp:GridView>
<asp:Label
ID="Label3"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView4"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="575"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkOrchid" Height="30" />
<RowStyle BackColor="HotPink" />
<AlternatingRowStyle BackColor="DeepPink" />
</asp:GridView>
<asp:Button
ID="Button1"
runat="server"
OnClick="Button1_Click"
Text="Populate GridView"
Height="45"
Font-Bold="true"
ForeColor="DodgerBlue"
/>
</div>
</form>
</body>
</html>
Tuesday, 12 June 2012
How to Remove, Delete double records but still have the values intact.
First we get all the double records. In this case I have a table called "test" with 2 columns "tst_id char(26) DEFAULT newid(),tst_name varchar(50)".
Secondly we use the GROUP attribute in the <cfquery> tag to only get SINGLE records. In this query we declare a temporary table (MSSQL - memory only) and we insert the SINGLE records in that table
then we DELETE all the double items and after that insert all the single items back into the original table from the temporary table.
Leaves you with all the double items removed!! BUT BE CAREFULL, this method doesn't take into account that records may have a relation with other tables in the database...
<cfquery>
SELECT tst_id,tst_name
FROM test
WHERE UPPER(tst_name) IN (
SELECT UPPER(tst_name) as tst_name
FROM test
GROUP BY tst_name
HAVING (COUNT(UPPER(tst_name)) > 1)
)
ORDER BY tst_name
</cfquery>
<cfsavecontent variable="sqlStmt">
DECLARE @tmp TABLE (tst_id char(36),tst_name varchar(50))
<!--- Only insert single items of the double records --->
<cfoutput query="qDouble" group="tst_name">
INSERT INTO @tmp VALUES ('#tst_id#','#tst_name#')
</cfoutput>
<!--- Delete ALL double records --->
DELETE
FROM test
WHERE UPPER(tst_name) IN (
SELECT UPPER(tst_name) as tst_name
FROM test
GROUP BY tst_name
HAVING (COUNT(UPPER(tst_name)) > 1)
)
<!--- Insert the single records back in the database --->
INSERT INTO test (tst_id,tst_name)
SELECT tst_id,tst_name FROM @tmp
</cfsavecontent>
<!--- Execute the query --->
<cfquery>
#PreserveSingleQuotes(sqlStmt)#
</cfquery>
Secondly we use the GROUP attribute in the <cfquery> tag to only get SINGLE records. In this query we declare a temporary table (MSSQL - memory only) and we insert the SINGLE records in that table
then we DELETE all the double items and after that insert all the single items back into the original table from the temporary table.
Leaves you with all the double items removed!! BUT BE CAREFULL, this method doesn't take into account that records may have a relation with other tables in the database...
<cfquery>
SELECT tst_id,tst_name
FROM test
WHERE UPPER(tst_name) IN (
SELECT UPPER(tst_name) as tst_name
FROM test
GROUP BY tst_name
HAVING (COUNT(UPPER(tst_name)) > 1)
)
ORDER BY tst_name
</cfquery>
<cfsavecontent variable="sqlStmt">
DECLARE @tmp TABLE (tst_id char(36),tst_name varchar(50))
<!--- Only insert single items of the double records --->
<cfoutput query="qDouble" group="tst_name">
INSERT INTO @tmp VALUES ('#tst_id#','#tst_name#')
</cfoutput>
<!--- Delete ALL double records --->
DELETE
FROM test
WHERE UPPER(tst_name) IN (
SELECT UPPER(tst_name) as tst_name
FROM test
GROUP BY tst_name
HAVING (COUNT(UPPER(tst_name)) > 1)
)
<!--- Insert the single records back in the database --->
INSERT INTO test (tst_id,tst_name)
SELECT tst_id,tst_name FROM @tmp
</cfsavecontent>
<!--- Execute the query --->
<cfquery>
#PreserveSingleQuotes(sqlStmt)#
</cfquery>
Subscribe to:
Posts (Atom)