Showing posts with label Concepts. Show all posts

Bulk Insert Using Sql Type – User Defined Table Type

Another one of the Bulk sending Method is SQL Type Method. In this Method we will create the User-Defined Table Type in SQL Server 2008 and send it as a single parameter.

The following example will describe,

BulkCopyUsingType.cs

protected void Page_Load(object sender, EventArgs e)

{

DataTable dtDataTable = new DataTable("UserDetailes");

DataRow drDataRow = null;

dtDataTable.Columns.Add("System", typeof(string));

dtDataTable.Columns.Add("NoOfEmployess", typeof(int));

drDataRow = dtDataTable.NewRow();

drDataRow["System"] = "FireFly";

drDataRow["NoOfEmployess"] = "1000";

dtDataTable.Rows.Add(drDataRow);

drDataRow = dtDataTable.NewRow();

drDataRow["System"] = "Viking";

drDataRow["NoOfEmployess"] = "2421";

dtDataTable.Rows.Add(drDataRow);

using (SqlConnection objCon = new SqlConnection("pwd=pfp123;UID=pfp;database=usrdb;server=10.20.36.61"))

{

objCon.Open();

using (SqlCommand objCmd = new SqlCommand())

{

objCmd.CommandText = "usp_TypedParamInsert";

objCmd.CommandType = CommandType.StoredProcedure;

objCmd.Connection = objCon;

SqlParameter param1 = new SqlParameter("@LocationID", SqlDbType.Int);

param1.Value = 1;

objCmd.Parameters.Add(param1);

SqlParameter param = new SqlParameter("@UsersCollection", SqlDbType.Structured);

param.Value = dtDataTable; //Directly send the Bulk data as a table.

objCmd.Parameters.Add(param);

objCmd.ExecuteNonQuery();

}

In DB, we need to do the followings,

--Create Destination Table

CREATE TABLE [dbo].[tblUserDefinedTypedData](

[LocationID] [tinyint] NOT NULL,

[System] [varchar](20) NOT NULL,

[NoOfEmployess] [int] NOT NULL

) ON [PRIMARY]

-- Create the Type of User-Defined Table structure

CREATE TYPE [dbo].tblPFPSystem AS TABLE

(

[System] [varchar](20) NOT NULL,

[NoOfEmployess] [int] NOT NULL

)

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- Now we create the SP for insert operation

CREATE PROCEDURE [dbo].[usp_TypedParamInsert]

(

@LocationID INT,

@UsersCollection AS [dbo].[tblPFPSystem] READONLY

)

BEGIN

SET NOCOUNT ON;

--This row performs bulk copy of object DataTable to Table in SQL

INSERT INTO [tblUserDefinedTypedData] SELECT @LocationID,* FROM @UsersCollection

END

Now, we will run the application, the DataTable Values are getting inserted into DB. In this example I have send a DataTable as hard coded one, instead of this you may use your data tables according to your needs. Now the Results shows like below,

03:36 | Category: Concepts | 1 comments

Bulk Copy Using XML / DB Insert Using XML

Bulk Copy Using XML

Most of the times we are sending large amount of data from our application to DB. We are having different options to update the bulk data. One way is XML parameter, that is we will send a large data in XML format and within the Database we will get in a Table format and stored it in DB.

The following example will describe,

BulkCopyXML.cs

protected void Page_Load(object sender, EventArgs e)

{

string sInputXML = "<XMLDatas><XMLData><LocationID>1</LocationID><System>FF</System>
<NoOfEmployees>28</NoOfEmployees></XMLData>" +

"<XMLData><LocationID>2</LocationID><System>Adjudication</System>
<NoOfEmployees>54</NoOfEmployees></XMLData>" +

"<XMLData><LocationID>1</LocationID><System>Viking</System>
<NoOfEmployees>100</NoOfEmployees></XMLData></XMLDatas>";

using(SqlConnection objCon = new SqlConnection(CommonBehavior.GetMainConnectionString()))

{

objCon.Open();

using (SqlCommand objCmd = new SqlCommand())

{

objCmd.CommandText = "usp_GetXML";

objCmd.CommandType = CommandType.StoredProcedure;

objCmd.Connection = objCon;

SqlParameter objParam = new SqlParameter("@xmlInput",sInputXML);

objCmd.Parameters.Add(objParam);

objCmd.ExecuteNonQuery();

}

In DB, First we create a tblXmlData table,

CREATE TABLE [dbo].[tblXmlData](

[LocationID] [tinyint] NOT NULL,

[System] [varchar](20) NOT NULL,

[NoOfEmployess] [int] NOT NULL

) ON [PRIMARY]

And the we create the SP usp_GetXML,

--select * from tblXMLData

Author : Suresh Kumar N

Execution : usp_GetXML '<XMLDatas>

<XMLData><LocationID>2</LocationID><System>Adjudication</System>
<NoOfEmployees>54</NoOfEmployees></XMLData>

<XMLData><LocationID>1</LocationID><System>Viking</System>
<NoOfEmployees>100</NoOfEmployees></XMLData>

</XMLDatas>'

CREATE PROC usp_GetXML

(

@xmlInput XML

)

AS BEGIN

SET NOCOUNT ON

SET ANSI_NULLS ON

SET ARITHABORT ON

DECLARE @TempTable TABLE (LocationID TinyInt, System varchar(20),NoOfEmployees int)

INSERT INTO @TempTable(LocationID,System,NoOfEmployees)

SELECT

XmlTable.Data.value('(./LocationID)[1]','tinyint'),

XmlTable.Data.value('(./System)[1]','varchar(20)'),

XmlTable.Data.value('(./NoOfEmployees)[1]','int')

FROM @xmlInput.nodes('//XMLData') AS XmlTable(Data)

INSERT INTO tblXMLData

SELECT LocationID,System,NoOfEmployees FROM @TempTable

END

In SP, XMLTable(Data) is a alias name and with the alias name we will get values of the defined columns. Defined Columns must be the name of the innermost tag.i.e insert values

Now, we will run the application, the XML inputs are getting inserted into DB. In this example I have send a initialized string, instead of this you may for the XML tag as your needs.

02:14 | Category: Concepts | 0 comments

Windows Services Creation

Go to File -- > New -- > Project -- >Visual C# -- > Windows -- > Windows Service .

Then Create a New Project. It shows(Service1.cs),

Then Right Click and Click the Add Installer bar, ProjectInstaller.cs File will be created

Its having two controls, ServiceProcessInstaller1, and ServiceInstaller1

In ServiceProcessInstaller1 == > Go to Properties and Modify Account as Local Service . It is aoid the Set Service Login prompt being asked about the system username and password

ServiceAccount Enumeration

ServiceAccount

stackoverflow

In ServiceInstaller1 ==> Go to Properties and Modify StartType as Automatic , Servicename à Your Service Name , Display Name ( This name is shown in the Services), Description (This Name is shown in the Services)

Right Click Windows Service Project and Add Folder and file called CommonBehaviours.cs

CommonBehaviours.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace WindowsService.Function
{
    public class WriteToFile
    {
        public void WriteToFile1()
        {
            string strDateTime = DateTime.Now.ToString();
            StreamWriter sw = new StreamWriter(Path.Combine("D:\\temp\\", "Suresh.txt"), true);
            sw.WriteLine(strDateTime);

            sw.Close();
        }
    }

}

Service1.cs

using System;
using System.ServiceProcess;
using System.Timers;
namespace TestWindowsService
{
    partial class Service1 : ServiceBase
    {   //Make the service at particular time period
        Timer TimerObj = new Timer(ConvertMinutesToMilliseconds(30));
        public Service1()
        {
            InitializeComponent();
        }
        protected override void OnStart(string[] args)
        {   // TODO: Add code here to start your service.
            TimerObj.Elapsed += new ElapsedEventHandler(OnElapsedTime);
            TimerObj.Enabled = true;
        }
        protected override void OnStop()
        {   // TODO: Add code here to perform any tear-down necessary to stop your service.
            TimerObj.Enabled = false;
        }
        protected void OnElapsedTime(object source, ElapsedEventArgs e)
        {  //Write the current datetime to the file
           WindowsService.Function.WriteToFile objWriteToFile = new WindowsService.Function.WriteToFile();
        }
        public static double ConvertMinutesToMilliseconds(double minutes)
        {
            return TimeSpan.FromMinutes(minutes).TotalMilliseconds;
        }
    }
}

How to start service in Code

Go to ServiceInstaller1 -- > Create an event , Write a code to start the service like shown below.