Adding DataRelations between Datatables in c#

Adding DataRelations between Datatables in c#



In a DataSet with multiple DataTable objects, you can use DataRelation objects to relate one table to another, to navigate through the tables, and to return child or parent rows from a related table.

Adding a DataRelation to a DataSet adds, by default, a UniqueConstraint to the parent table and a ForeignKeyConstraintto the child table. For more information about these default constraints, see DataTable Constraints (ADO.NET).

The following code example creates a DataRelation using two DataTable objects in a DataSet. Each DataTable contains a column named CustID, which serves as a link between the two DataTable objects. The example adds a singleDataRelation to the Relations collection of the DataSet. The first argument in the example specifies the name of theDataRelation being created. The second argument sets the parent DataColumn and the third argument sets the childDataColumn.

customerOrders.Relations.Add("CustOrders",
customerOrders.Tables["Customers"].Columns["CustID"],
customerOrders.Tables["Orders"].Columns["CustID"]);

HierarchyID Data Type in SQL Server 2008

 The HierarchyID data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy.

 Facts about the HierarchyId data type:

Here are a few facts that you must know before starting to use HierarchyID:

1. New system CLR type supporting trees.
2. Internally stored as varbinary <= 900 bytes.
3. Holds a path that provides a topological sort of a tree.
4. Has a set of methods that provide tree functionality: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write.
5. Efficient querying that can utilize depth-first and breadth-first indexes.

Methods provided are as follows:

• GetAncestor: Returns a HierarchyID that represents the n^th ancestor of this HierarchyID node.
• GetDescendant: Returns a child node of this HierarchyID node.
• GetLevel: Returns an integer that represents the depth of this HierarchyID node in the overall hierarchy.
• GetRoot: Returns the root HierarchyID node of this hierarchy tree. Static.
• IsDescendant: Returns true if the passed-in child node is a descendant of this HierarchyID node.
• Parse: Converts a string representation of a hierarchy to a HierarchyID value. Static.
• Reparent: Moves a node of a hierarchy to a new location within the hierarchy.
• ToString: Returns a string that contains the logical representation of this HierarchyID.

Example

CREATE TABLE dbo.Employees
(
  empid   INT NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_depth_first
  ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first
  ON dbo.Employees(lvl, hid);
CREATE UNIQUE INDEX idx_empid
  ON dbo.Employees(empid);

In the above code snippet, I have created a table using HierarchyID as a data type.

CREATE PROC dbo.usp_AddEmp 
  @empid AS INT, @mgrid AS INT = NULL,
  @empname AS VARCHAR(25), @salary AS MONEY
AS

DECLARE @hid AS HIERARCHYID, @mgr_hid AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);

In the above code snippet, I have created a Stored Procedure that can be used to insert data into the table with HierarchyID as data type in one of the columns.

-- Subtree
SELECT C.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND P.hid.IsDescendant(C.hid) = 1;

-- Path
SELECT P.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND P.hid.IsDescendant(C.hid) = 1;

-- Presentation/sorting
SELECT
  REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;

Click event when Enter button is Pressed in asp.net

Click event when Enter button is Pressed in asp.net

Javascript

function clickButton(e, buttonid){

      var evt = e ? e : window.event;

      var bt = document.getElementById(buttonid);

      if (bt){

          if (evt.keyCode == 13){

                bt.click();

                return false;

          }

      }

}

//code behind TextBox1.Attributes.Add("onkeypress", "return clickButton(event,'" + Button1.ClientID + "')");
 
The code behind generates the following code:

<    asp:Button id="Button1"
           Text="Submit"
           OnClick="SubmitBtn_Click" 
           runat="server" onkeypress="return clickButton(event,'Button1')"/   >

This causes web control Button1 to be clicked when the enter key is hit inside TextBox1.

Naming Conventions in Dotnet – C# and VB.Net

Naming Conventions in Dotnet – C# and VB.Net

Use the following three conventions for capitalizing identifiers.

Pascal Case

The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized. You can use Pascal case for identifiers of three or more characters. For example:

BackColor

Use this for Class, Interface, Enum, function name and Static Field

Camel Case

The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized. For example:

backColor

Use this for Parameter name,

Uppercase

All letters in the identifier are capitalized. Use this convention only for identifiers that consist of two or fewer letters. For example:

System.IO

System.Web.IO

You might also have to capitalize identifiers to maintain compatibility with existing, unmanaged symbol schemes, where all uppercase characters are often used for enumerations and constant values. In general, these symbols should not be visible outside of the assembly that uses them

Export Datatable to Excel in C# Windows application

// Export Datatable to Excel in C# Windows application

using System;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace ExportExcel
{
    public partial class ExportDatatabletoExcel : Form
    {
        public ExportDatatabletoExcel()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
         
            DataTable dt = new DataTable();

            //Add Datacolumn
            DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));

            dt.Columns.Add("LastName", typeof(String));
            dt.Columns.Add("Blog", typeof(String));
            dt.Columns.Add("City", typeof(String));
            dt.Columns.Add("Country", typeof(String));

            //Add in the datarow
            DataRow newRow = dt.NewRow();

            newRow["firstname"] = "Arun";
            newRow["lastname"] = "Prakash";
            newRow["Blog"] = "http://royalarun.blogspot.com/";
            newRow["city"] = "Coimbatore";
            newRow["country"] = "India";

            dt.Rows.Add(newRow);

            //open file
            StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");

            try
            {

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
                }

                wr.WriteLine();

                //write rows to excel file
                for (int i = 0; i < (dt.Rows.Count); i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (dt.Rows[i][j] != null)
                        {
                            wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                        }
                        else
                        {
                            wr.Write("\t");
                        }
                    }
                    //go to next line
                    wr.WriteLine();
                }
                //close file
                wr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

TRY...CATCH in Sql Server

TRY...CATCH in Sql Server

Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.

After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.


BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

WAITFOR in Sql Server 2008

WAITFOR in Sql Server 2008

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.


A. Using WAITFOR TIME

The following example executes the stored procedure sp_update_job at 10:20 P.M. (22:20).

USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
    WAITFOR TIME '22:20';
    EXECUTE sp_update_job @job_name = 'TestJob',
        @new_name = 'UpdatedJob';
END;
GO
B. Using WAITFOR DELAY

The following example executes the stored procedure after a two-hour delay.

BEGIN
    WAITFOR DELAY '02:00';
    EXECUTE sp_helpdb;
END;
GO

http://msdn.microsoft.com/en-us/library/ms187331(v=SQL.100).aspx