Batch Update

We usually come across scenarios where we have to update records in batch. A simple example of this scenario is "User preferences" when subscribing to any website. User initially selects some 'preferences' & later he can add some more, or remove the preferences that are already selected.

In my example I am going to batch update user regions. An user can be assigned any number of regions & he can add some more or remove the existing.

Believe me this works fine & this is one of the best methods for batch update.

Initially I used to delete the assigned records and used to bulk insert new records. But I got a foreign key violation error once when I was doing this. Then I was forced to do batch update. Eventhough I have felt little bit difficulty in doing this for the first time but now I am very comfortable with batch update.

-------------------------------------------------------------------------

Simple Idea: (I am talking in respect of user-regions).

First we need to get a dataset of assigned regions of the user from DB. (This means initially assigned records)

Now we need to construct another dataset with the new user- regions. (The one's that are selected & modified by user)

Now we need to compare these two datasets and we have to find the records added by user , modifed by user (in this specific example I dont have this) & deleted by the user.

Now in just one database operation, new records will be inserted to database, modifed records will be updated & deleted records will be deleted.

We have to specify insertcommand, updatecommand & deletecommand of dataadapter and with .Update method we can do this in one step.

(If you are using stored procedure, you must define them in db).

-------------------------------------------------------------------------

Note: Eventhough CommandBuilder can do the same, I feel comfortable with DataAdapter's BatchUpdate. (Update)

-------------------------------------------------------------------------

public int UpdateRegionGroups(int UserId, DataSet dsRegionGroups)

{

DataColumn[] ColPk = new DataColumn[2]; //In my case primary key is defined by 2 columns. It can be 1 or many. Using appropriate size of array for Primary key.

ArrayList ArrorigValues = new ArrayList();

DataRow updrow, newrow, delrow;

DataSet dsOrigRegionGroups = new UserRegionGroups().SelectAssignedRegionGroups(UserId); //This is one database fetch operation.

ColPk[0] = dsOrigRegionGroups.Tables[0].Columns["RegionGroupId"];

ColPk[1] = dsOrigRegionGroups.Tables[0].Columns["UserId"];

dsOrigRegionGroups.Tables[0].PrimaryKey = ColPk;

if (dsOrigRegionGroups.Tables[0].Rows.Count > 0)

for (int i = 0; i < dsOrigRegionGroups.Tables[0].Rows.Count; i++)

{

clsobj2DArray objPK = new clsobj2DArray();

objPK.intField1 = Convert.ToInt32(dsOrigRegionGroups.Tables[0].Rows[i]["RegionGroupId"].ToString());

objPK.intField2 = Convert.ToInt32(dsOrigRegionGroups.Tables[0].Rows[i]["UserId"].ToString());

ArrorigValues.Add(objPK);

}

if (dsRegionGroups.Tables[0].Rows.Count > 0)

{

int OriginalCount = dsRegionGroups.Tables[0].Rows.Count;

for (int i = 0; i < OriginalCount; i++)

{

string[] ArrtoFind = new string[2];

ArrtoFind[0] = dsRegionGroups.Tables[0].Rows[i]["RegionGroupId"].ToString();

ArrtoFind[1] = dsRegionGroups.Tables[0].Rows[i]["UserId"].ToString();

updrow = dsOrigRegionGroups.Tables[0].Rows.Find(ArrtoFind);

if (updrow == null)

{

//InsertRowValues(i, dsRegionGroups);

dsOrigRegionGroups.Tables[0].DefaultView.AllowNew = true;

newrow = dsOrigRegionGroups.Tables[0].NewRow();

newrow.BeginEdit();

newrow["RegionGroupId"] = dsRegionGroups.Tables[0].Rows[i]["RegionGroupId"];

newrow["UserId"] = dsRegionGroups.Tables[0].Rows[i]["UserId"];

dsOrigRegionGroups.Tables[0].Rows.Add(newrow);

newrow.EndEdit();

}

else

{

//You can update the row here. In my specific example I dont need it.

int j, k = -1;

if (ArrorigValues.Count > 0)

{

for (j = 0; j < ArrorigValues.Count; j++)

{

clsobj2DArray objPk1 = new clsobj2DArray();

objPk1 = (clsobj2DArray)ArrorigValues[j];

if ((ArrtoFind[0] == objPk1.intField1.ToString()) && (ArrtoFind[1] == objPk1.intField2.ToString()))

{

k = j;

break;

}

}

if (k != -1)

ArrorigValues.RemoveAt(k);

}

}

}

}

if (ArrorigValues.Count > 0)

{

string[] ArrtoDel = new string[2];

clsobj2DArray objPkDel = new clsobj2DArray();

for (int i = 0; i < ArrorigValues.Count; i++)

{

objPkDel = (clsobj2DArray)ArrorigValues[i];

ArrtoDel[0] = objPkDel.intField1.ToString();

ArrtoDel[1] = objPkDel.intField2.ToString();

delrow = dsOrigRegionGroups.Tables[0].Rows.Find(ArrtoDel);

if (delrow != null)

{

delrow.Delete();

}

}

}

return BatchUpdateUserRegionGroups(dsOrigRegionGroups);

}

-------------------------------------------------------------------------

private int BatchUpdateUserRegionGroups(DataSet dsRegionGroups)

{

SqlConnection cnBatchUpdate = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);

SqlCommand insCommand, updCommand, delCommand;

SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("Table", "UserRegionGroups");

da.SelectCommand = new SqlCommand();

insCommand = new SqlCommand("InsertUserRegionGroups_BatchUpd", cnBatchUpdate);

insCommand.CommandType = CommandType.StoredProcedure;

insCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

insCommand.Parameters.Add("@UserId", SqlDbType.Int);

insCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

insCommand.Parameters["@UserId"].SourceColumn = "UserId";

updCommand = new SqlCommand("InsertUserRegionGroups_BatchUpd", cnBatchUpdate);

updCommand.CommandType = CommandType.StoredProcedure;

updCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

updCommand.Parameters.Add("@UserId", SqlDbType.Int);

updCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

updCommand.Parameters["@UserId"].SourceColumn = "UserId";

delCommand = new SqlCommand("DELUserRegionGroups_BatchUpd", cnBatchUpdate);

delCommand.CommandType = CommandType.StoredProcedure;

delCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

delCommand.Parameters.Add("@UserId", SqlDbType.Int);

delCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

delCommand.Parameters["@UserId"].SourceColumn = "UserId";

da.InsertCommand = insCommand;

da.UpdateCommand = updCommand;

da.DeleteCommand = delCommand;

int retval = 0;

try

{

cnBatchUpdate.Open();

retval = da.Update(dsRegionGroups.Tables[0]);

}

catch (Exception ex)

{

throw new Exception(ex.Message, ex.InnerException);

}

finally

{

cnBatchUpdate.Close();

}

return retval;

}

-------------------------------------------------------------------------

public class clsobj2DArray

{

private int m_var1;

private int m_var2;

public clsobj2DArray()

{ }

public clsobj2DArray(int var1val, int var2val, int var3val)

{

this.m_var1 = var1val;

this.m_var2 = var2val;

}

public int intField1

{

get { return m_var1; }

set { m_var1 = value; }

}

public int intField2

{

get { return m_var2; }

set { m_var2 = value; }

}

}

XML Encryption

Iam going to work on XML Encryption. Here's a quick reference:

 

 

XML Encryption is a specification that defines how to encrypt the content of an XML element. It's recommended by the W3C. XML Encryption encompasses the encryption of any kind of data, including the encryption of XML. What makes it XML Encryption is that an XML element (either an EncryptedData or EncryptedKey element) contains or refers to the cipher text, keying information, and algorithms. Both XML Signature and XML Encryption use KeyInfo element. It appears as the child of a SignedInfo, EncryptedData, or EncryptedKey element and provides information to a recipient about what keying material to use in validating a signature or decrypting encrypted data. KeyInfo is optional: it can be attached in the message, or be delivered through a secure channel.

 

 

XML Encryption Algorithms Reference:

Reference : http://www.w3.org/TR/xmlenc-core/#sec-eg-Element

CheckBox List in C#.NET (Windows applications)

Some of the Properties to be modifed:

Behaviour: CheckonClick = true //If this is set to false you need to click twice to check an item.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Binding to a CheckBox List

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DataSet dsRegionGroups = new ForecastGen.Data.RegionGroups().SelectAllRegionGroups(); //This retrieves the data from DataBase

chkLstRegionGroups.DataSource = dsRegionGroups.Tables[0];

chkLstRegionGroups.ValueMember = "RegionGroupMstId";

chkLstRegionGroups.DisplayMember = "RegionGroupName";

//Please make sure the order of statements is correct.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

To Check/UnCheck an item programmatically

for (Int16 i = 0; i < chkLstRegionGroups.Items.Count; i++)

{

chkLstRegionGroups.SetItemChecked(i, false); //uncheck

}

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Retrieving the Selected Values Programmatically

foreach (DataRowView view in chkLstRegionGroups.CheckedItems)

{

dr = dt.NewRow();

dr[0] = userid;

dr[1] = view[chkLstRegionGroups.ValueMember].ToString();

dt.Rows.Add(dr);

}

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

My Passion for C

Posting some useful information regarding C language & C FAQs
----------------------------------------------------------------------
Developing C programs from Visual Studio.NET
File -> New -> Project ->
Select Visual C++ Project Type -> Win32 -> Win32 Console Application.

----------------------------------------------------------------------

Function Pointer Example:
#include "stdafx.h"
#include "conio.h"
int Add(int a,int b)
{
return a+b;
}

void Dummy()
{
printf("\nI am in a dummy function now");
}

int _tmain(int argc, _TCHAR* argv[])
{
int (*fp)(int,int);
void (*voidfp)();
fp = Add;
printf("%d",fp(2,3));
voidfp = Dummy;
voidfp();
getch();
return 0;
}

------------------------------------------------------
How to find the size of a structure in C
Ans: Size of a structure is the sum of the sizes of individual elements in it.
------------------------------------------------------

Swap 2 variables in one line: a ^= b ^= a ^= b;
but the solution is not portable

------------------------------------------------------
What's a "sequence point"?
A point (at the end of a full expression, or at the , &&, ?:,
or comma operators, or just before a function call) at which all
side effects are guaranteed to be complete.
------------------------------------------------------

Is this correct?
char *p; *p = malloc(10);

Ans: No. p is the pointer, not *p.
------------------------------------------------------

*p++ means what?
it increments p
to increment the value pointed to by p use (*p)++;
------------------------------------------------------

Why doesn't the code "((int *)p)++;" work?

In C, a cast operator is a conversion operator, and by definition it yields an rvalue, which cannot be assigned to, or incremented with ++.
------------------------------------------------------

what is a null pointer?
(null pointer is a language concept)
this is not the address of any object or function. constant zero is assumed as null pointer at compile time (in pointers context)

------------------------------------------------------

what is NULL ?
NULL is a pre-processor macro (its value is zero) which is used to generate null pointers.
null is #define 0 (or)
((void *)0)

------------------------------------------------------
What do "lvalue" and "rvalue" mean?
An "lvalue" denotes an object that has a location; an "rvalue" is any expression that has a value.

------------------------------------------------------

What is assert()?
It is a macro which documents an assumption being made by the
programmer; it terminates the program if the assumption is
violated.

------------------------------------------------------
What does a+++++b mean ?

It's interpreted as "a ++ ++ + b", and cannot be parsed.
------------------------------------------------------
How can I swap two values without using a temporary?
a = a + b;
b = a - b;
a = a - b;

or
a ^= b;
b ^= a;
a ^= b;

------------------------------------------------------
How can I call a function, given its name as a string?
maintain a correspondence table of names and function pointers.

------------------------------------------------------
How can we access command-line arguments?
via argv[] parameter of main function

------------------------------------------------------

How can I invoke another program from within a C program?
'System' will help you.

------------------------------------------------------
How can %f be used for both float and double arguments in printf()?

ans: In variable-length argument lists, types char and short int are promoted to int, and float is promoted to double

------------------------------------------------------
How can I print a '%' character with printf?

"%%"
------------------------------------------------------

Parent Child Ids (Infinite Depth)

Input format:


Output:

CREATE TABLE [dbo].[tblSkillSet](

[SkillId] [int] IDENTITY(1,1) NOT NULL,

[SkillName] [varchar](250) NOT NULL,

[ParentSkillId] [int] NOT NULL CONSTRAINT [DF_tblSkillSet_ParentSkillId] DEFAULT (0),

[Depth] [int] NULL,

[Lineage] [varchar](100) NULL,

CONSTRAINT [PK_tblSkillSet] PRIMARY KEY CLUSTERED

(

[SkillId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--------------------------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SelALLParentsAndChilds]

AS

SET NOCOUNT ON

Create table #tblSkills(SkillId INT, SkillName VARCHAR(8000))

CREATE TABLE #stack (Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

CREATE TABLE #stack1 (SkillId INT ,Parent_Id INT, SkillName VARCHAR(8000) , [level] INT)

DECLARE @maxId INT

DECLARE @strskill VARCHAR(8000)

DECLARE @colskill VARCHAR(8000)

DECLARE @parentId INT

DECLARE @level INT, @line char(20)

Declare @SKillName VARCHAR(50)

SET @strskill=''

DECLARE @PSID INT

DECLARE @OuterPSID INT

DECLARE @PSName VARCHAR(100)

DECLARE cur_skill1 cursor for SELECT SkillId from tblSkillSet WHERE ParentSkillId = 0

OPEN cur_skill1

FETCH next from cur_skill1 into @PSID

SET @OuterPSID = @PSID

WHILE @@FETCH_STATUS=0

BEGIN

DELETE FROM #stack1

SELECT @SkillName =SkillName from tblSkillSet WHERE ParentSkillId =@PSID

INSERT INTO #stack VALUES (@PSID, @SkillName, 1)

SELECT @level = 1

WHILE @level > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE [level] = @level)

BEGIN

SELECT @PSID = Parent_Id FROM #stack WHERE [level] = @level

SELECT @line = space(@level - 1) + @PSID

PRINT @line

DELETE FROM #stack WHERE [level] = @level AND parent_id = @PSID

INSERT #stack

SELECT SkillId , SkillName , @level + 1 FROM tblSkillSet WHERE ParentSkillId = @PSID

DECLARE @strinnerSkillName VARCHAR(100)

SET @strinnerSkillName = ''

IF exists (SELECT * FROM #stack1 WHERE SkillId = @PSID)

BEGIN

SET @strinnerSkillName = (SELECT SkillName from #stack1 WHERE SkillId=@PSID) + '>>'

INSERT #stack1

SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName, @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

ELSE

BEGIN

SET @strinnerSkillName = (SELECT max(SkillName) from tblSkillSet WHERE SkillId=@PSID) + '>>'

INSERT #stack1 SELECT SkillId, ParentSkillId , @strinnerSkillName + SkillName as SkillName , @level + 1

FROM tblSkillSet WHERE ParentSkillId = @PSID

END

IF @@ROWCOUNT > 0

BEGIN

SELECT @level = @level + 1

END

IF exists ( SELECT 'sometext' from #stack WHERE parent_id=@PSID)

SELECT @level=0

END

ELSE

SELECT @level = @level - 1

END -- WHILE

SET @PSName = (SELECT skillName from tblSkillSet WHERE SkillId=@OuterPSID)

INSERT INTO #tblSkills VALUES(@OuterPSID,@PSName)

INSERT INTO #tblSkills SELECT skillId,skillName from #stack1

FETCH next FROM cur_skill1 INTO @PSID

SET @OuterPSID = @PSID

END --for cursor

CLOSE cur_skill1

DEALLOCATE cur_skill1

SELECT * FROM #tblSkills

--------------------------------------------------------------------------------------------------------------------------

Creating a Dictionary Class in C#.NET

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
namespace ForecastGen.Type
{
public class DicDemand : DictionaryBase
{
public int Item (string MonthYear)
{
return((int)Dictionary[MonthYear]);
}
public ICollection Keys
{
get { return Dictionary.Keys; }
}
public ICollection Values
{
get { return Dictionary.Values; }
}
public bool Contains(string MonthYear)
{
return Dictionary.Contains(MonthYear);
}
public void Add(string MonthYear, int DemandValue)
{
if (Dictionary.Contains(MonthYear) == false)
{
Dictionary.Add(MonthYear, DemandValue);
}
}
public void Remove(string MonthYear)
{
if(Dictionary.Contains(MonthYear)==true)
Dictionary.Remove(MonthYear);
}
}
}

Code for Password Encryption in .NET

The namespace for using Cryptography is System.Security.Cryptography.

----------------------------------------------------------------
using System.Security.Cryptography;
----------------------------------------------------------------

MD5CryptoServiceProvider md5hasher = new MD5CryptoServiceProvider();
Byte[] hashedbytes;
UTF8Encoding encoder = new UTF8Encoding();
hashedbytes = md5hasher.ComputeHash(encoder.GetBytes(txtPassword.Text));
-----------------------------------------------------------------
Now hashedbytes contains the encrypted password
-----------------------------------------------------------------
private Byte[] password;
password = hashedbytes
----------------------------------------------------------------

DataType for password in SQL SERVER is "binary(50)"

----------------------------------------------------------------
Stored procedure for creating user:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[CreateUser]
@UserName VARCHAR(50),
@Password BINARY(16)
AS
BEGIN
IF NOT EXISTS(SELECT 'SOMTHING' FROM UserMst WHERE UserName=@UserName)
INSERT INTO
UserMst
(
UserName,
Password)
VALUES
(
@UserName,
@Password
)
RETURN SCOPE_IDENTITY()
END

Array Declaration in C#.NET

Built-in Types:

int[] intArray = new int[36];

For User-defined types:
ForecastGen.Type.DicDemand[] objdicDemand = new ForecastGen.Type.DicDemand[2];

(DicDemand is a class in ForecastGen.Type namespace)