Search This Blog
Showing posts with label DOT NET. Show all posts
Showing posts with label DOT NET. Show all posts

Wednesday, 12 August 2015

Difference between DataSet.Copy() and DataSet.Clone() Or Difference between DataTable.Copy() and DataTable.Clone()

Step 1:- Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind=" Default.aspx.cs" Inherits="Demo.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Copy vs. Clone</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
    </form>
</body>
</html>

Step 2:- Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace Demo
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataTable dt = GenerateData();

                DataTable dt1 = new DataTable();

                dt1 = dt.Copy();

                dt1 = dt.Clone();
            }

        }

        private DataTable GenerateData()
        {
            DataTable dt = new DataTable();

            DataRow dr = null;

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));

            dt.Columns.Add(new DataColumn("Column2", typeof(string)));

            dt.Columns.Add(new DataColumn("Column3", typeof(string)));

            dt.Columns.Add(new DataColumn("Column4", typeof(DateTime)));

            int totalRow = 25;

            for (int i = 0; i < totalRow; i++)
            {
                dr = dt.NewRow();

                dr["Column1"] = i + 1;

                dr["Column2"] = "Row" + i;

                dr["Column3"] = "Row" + i;

                dr["Column4"] = DateTime.Now;

                dt.Rows.Add(dr);
            }

            return dt;
        }
    }
}

Step 3:-  dt1 = dt.Copy();

Result





Step 4:-  dt1 = dt.Clone();

Result





Note:-

1. DataSet.Copy() / DataTable.Copy() 

It copies both Structure and data.

2.  DataSet.Clone() / DataTable.Clone()   

It only copies the structure but does not Copies the Data.

OR

Clone only copies the schema of a DataSet object, whereas Copy copies both the structure and data of a DataSet object.

Copy = clone + copy data.
Clone = copy data schema


Posted by at No comments:
Labels: , ,

Tuesday, 4 August 2015

How to pass table-valued parameter data to a stored procedure or Use Table-Valued Parameters or Table Value Parameter in SQL Server or Using SQL Server's Table Valued Parameters

Step 1:- Create table

CREATE TABLE DATA
(
       COLUMN1 VARCHAR(50),
       COLUMN2 VARCHAR(50),
       COLUMN3 VARCHAR(50),
       COLUMN4 VARCHAR(50)
)

Step2:- Create user define table Type

CREATE TYPE TABLE_DATA
AS TABLE
(
       COLUMN1 VARCHAR(50),
       COLUMN2 VARCHAR(50),
       COLUMN3 VARCHAR(50),
       COLUMN4 VARCHAR(50)
)

Step3:- Create Insert Store Procedure

CREATE PROC SP_INSERT
@TBLDATA TABLE_DATA READONLY
AS
BEGIN
       INSERT INTO DATA
       SELECT * FROM @TBLDATA
END


Step4:- Default.aspx


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Demo.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Pass table-valued parameter data to a stored procedure </title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
            <asp:Label ID="Label1" runat="server" Text="Message" Visible="False"></asp:Label>
        </div>
    </form>
</body>
</html>

Step5:- Default.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web.UI;

namespace Demo
{
    public partial class Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {

            }
        }

        protected DataTable GeTable()
        {
            DataTable dt = new DataTable();

            DataRow dr;

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));

            dt.Columns.Add(new DataColumn("Column2", typeof(string)));

            dt.Columns.Add(new DataColumn("Column3", typeof(string)));

            dt.Columns.Add(new DataColumn("Column4", typeof(string)));

            int totalRow = 10;

            for (int i = 0; i < totalRow; i++)
            {
                dr = dt.NewRow();

                dr["Column1"] = "Row" + i;

                dr["Column2"] = "Row" + i;

                dr["Column3"] = "Row" + i;

                dr["Column4"] = 1000 / (i + 1);

                dt.Rows.Add(dr);
            }

            return dt;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string strConnection = "server=192.168.1.1; database=Test; uid=sa; pwd=admin";
            SqlConnection connection = new SqlConnection(strConnection);
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "SP_INSERT";
            sqlCommand.Parameters.Add("@TBLDATA", SqlDbType.Structured).Value = GeTable();
            sqlCommand.Connection = connection;

            connection.Open();

            int a = sqlCommand.ExecuteNonQuery();

            connection.Close();

            if (a > 0)
            {
                Label1.Text = "Record Inserted Successfully";
                Label1.ForeColor = Color.Green;
                Label1.Visible = true;
            }
            else
            {
                Label1.Text = "Record Not Inserted";
                Label1.ForeColor = Color.Red;
                Label1.Visible = true;
            }
        }
    }
}

Step6:- Run & Click Insert Button





Step7:- Run the SQL Query

select * from DATA






Note:-

1. Table-valued parameters are read-only in Transact-SQL code.

2. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. 

3. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

4. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.


Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)