How programmatically set Custom Page Size while exporting Crystal Report using VB.NET

How programmatically set Custom Page Size while exporting Crystal Report using VB.NET You can easily set paper size with export functionality. Simply design crystal report as you do and bind it with database, dataset or any other data source. When you export crystal report its takes paper size based on the crystal report design page size, if your requirement to display or print exported file in particular format then following code will help to do this. Following example tested in 1) .NET Framework 2.0 and Visual Studio 2005 2) Crystal Report with Visual Studio 2005 DLL references requires VB.NET Code 'Fill dataset as with desired data needs to populates crystal report Dim dsReportOut As New DataSet Dim rptDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument Dim reportPath As String = "CrystalReportFilePath" Dim fileName As String = "ExportFileName.doc" 'Build the report logic here. Declare a report document. rptDoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument() rptDoc.Load(String.Concat(reportPath, "CrystalReportFileName.rpt")) rptDoc.SetDataSource(dsReportOut) 'Set Paper Size as A4 its letter size rptDoc.PrintOptions.PaperSize = CrystalDecisions.Shared.PaperSize.PaperLetter --**(See below for other PaperSize options) 'Export crystal report into MSWord rptDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, String.Concat(reportPath, fileName)) --***(See below for other Export Format Options) 'Open(Exported file)MSWord File Dim ProcessStartInfo As New System.Diagnostics.ProcessStartInfo ProcessStartInfo.FileName = String.Concat(reportPath, fileName) ProcessStartInfo.WindowStyle = ProcessWindowStyle.Maximized System.Diagnostics.Process.Start(ProcessStartInfo) ** Others PaperSize Options *** Others Export Options

Fastest way to insert bulk records into SQL Server DB using bulkCopy in VB.NET

Fastest way to insert bulk records into SQL Server DB using SqlBulkCopy in VB.NET There are multiple ways to insert bulk data into SQL server database table to increase better performance and reduce multiple database hits. Following example tested in 1) .NET Framework 2.0 and Visual Studio 2005 2) SQL Server 2005 and SQL Express 3) Microsoft Enterprises Library 3.0 for database operation 1. Passing XML string to Stored Procedure In this method you can combine your data into XML and pass this XML as a string to SQL server stored procedure, this way you can increase your database hit performance up to 70% instead of using Line By Line insert methods. Example: I am giving example for two records you can increase records in XML (no limit) Sample XML String <Employees> <Emp> <Empno>1000</Empno> <Ename>Ritesh</Ename> <Deptno>A91</Deptno> </Emp> <Emp> <Empno>2000</Empno> <Ename>Rajiv</Ename> <Deptno>B56</Deptno> </Emp> </Employees> SQL Server Database table "Employee" CREATE TABLE [dbo].[Employee]( [Empno] [int] NOT NULL, [Ename] [nvarchar](100) NULL, [Deptno] [nvarchar](100) NULL ) ON [PRIMARY] END GO SQL Server Stored Procedure ALTER PROCEDURE ADDXML_EMP ( @prmEmployee XML, @prmOutStatus INT OUT ) AS DECLARE @XMLDocPointer INT EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmEmployee BEGIN SET NOCOUNT ON INSERT INTO Employee ( [Empno], [Ename], [Deptno] ) SELECT Empno, Ename, Deptno FROM OPENXML(@XMLDocPointer,'/Employees/Emp',2) WITH ( Empno integer, Ename nvarchar(100), Deptno nvarchar(100) ) SET @prmOutStatus = @@Error RETURN @@Error END VB.NET Code to execute stored procedure Namespace Imports Microsoft.Practices.EnterpriseLibrary.Data Imports System.Data.SqlClient Imports System.Xml Imports System.Data.Common Dim XMLString As String 'USE GetXML method for XMLString XMLString = "<Employees><Emp><Empno>1000</Empno><Ename>Ritesh </Ename><Deptno>A91</Deptno></Emp><Emp><Empno>2000 </Empno><Ename>Rajiv</Ename><Deptno>B56</Deptno></Emp> </Employees>" 'CreateDatabase method will create database object based on the connection name Dim connectDB As Database = ConfigDatabaseFactory.CreateDatabase("SQLServerDBConnectionString") Dim command As DbCommand command = connectDB.GetStoredProcCommand("AddXML_EMP") connectDB.AddInParameter(command, "@prmEmployee", DbType.Xml, XMLString) connectDB.AddOutParameter(command, "@prmOutStatus", DbType.Int32, 0) connectDB.ExecuteNonQuery(command) Note: If you are reading XML from file then constructed your string using following lines of code Dim xmlFilePath As String = "C:\Employees.xml" Dim xmlDoc As New XmlDocument xmlDoc.Load(xmlFilePath) Dim XMLString As String = xmlDoc.OuterXml.ToString() Data Inserted into Database table Empno Ename Deptno 1000 Ritesh A91 2000 Rajiv B56 Drawback XML methods is very reliable and fast performing method but If you have any special character in your data like notes field in database table column then this method will give error saying "Invalid character in XML" or if you have Image in the column then XML would help, to over come this another method to use SqlBulkCopy Note: If you want to do above method for Oracle Stored procedure the see below link. http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html 2. Using "SqlBulkCopy" method to insert data into Database table 'Fill this data from any database like Oracle or SQL Server 'using some query like "Select * from Employee" Dim dsServerData As New DataSet 'Create Databse Dim localConnectDB As Database = ConfigDatabaseFactory.CreateDatabase("DBConString") 'Open Connection Using localConnection As DbConnection = localConnectDB.CreateConnection() 'Open Connection localConnection.Open() 'Create Transaction if you want Using transaction As DbTransaction = localConnection.BeginTransaction() 'Create SqlBulkCopy Using localCopy As New SqlBulkCopy(localConnection, SqlBulkCopyOptions.TableLock, transaction) Try 'loop if you have multiple table in a dataset For tableCount As Integer = 0 To dsServerData.Tables.Count - 1 'Define the BatchSize , it can be configurable too localCopy.BatchSize = dsServerData.Tables(tableCount).Rows.Count 'Define the table name where you want to insert data 'if you have exact table name in dataset then use 'localCopy.DestinationTableName = dsServerData.Tables(tableCount).TableName localCopy.DestinationTableName = "Employee" 'This "WriteToServer" will insert your complete table data into database table localCopy.WriteToServer(dsServerData.Tables(tableCount)) Next transaction.Commit() Catch ex As Exception transaction.Rollback() Finally 'Close Connection and bulkCopy localCopy.Close() localConnection.Close() End Try End Using End Using End Using Note: This method inserts 0.28 minutes to insert 1 million records in database Some other methods to load huge TextFile into SQL server database table http://blog.stevienova.com/2008/01/16/net-fastest-way-to-load-text-file-to-sql-sqlbulkcopy/

How to change mouse Icon when drag the items from DataGridView to another using VB.NET

How to change mouse Icon when drag the items from data grid view using VB.NET Let say you have some data rows in data grid and you can select multiple rows from grid and you can drag selected rows into some other data grid or other controls This time you will get default mouse icon and if you want to change the mouse icon with custom image then write following code in your data grid view events Set DataGridView Properties at design time Allowdrop = true Following code are tested with 1) .NET Framework 2.0 2) Visual Studio 2005 3) VB.NET Window Application 4) Window XP 'Following event will help to drag items on mouse left click button and this event will activate drag drop event of data grid view Private Sub DataGridView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown Dim gridColumnIndex As Integer 'Get the Index of Row which is being Dragged 'We would use this Index on Drop to identify which Row was dragged and get the values from that row If e.Button = Windows.Forms.MouseButtons.Left Then gridColumnIndex = DataGridView1.HitTest(e.X, e.Y).RowIndex If gridColumnIndex > -1 Then DataGridView1.DoDragDrop(gridColumnIndex, DragDropEffects.Move) End If End If End Sub 'This event will remove default mouse icon Private Sub DataGridView1_GiveFeedback(ByVal sender As Object, ByVal e As System.Windows.Forms.GiveFeedbackEventArgs) Handles DataGridView1.GiveFeedback e.UseDefaultCursors = False End Sub 'This event will change mouse icon with custom icon based on the one item selected or multiple items selections Private Sub DataGridView1_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragOver e.Effect = DragDropEffects.Move 'if selected item is 1 then set desired icon, if more the one then set multidrag icon else set default If DataGridView1.SelectedRows.Count = 1 Then Windows.Forms.Cursor.Current = New Cursor("C:\Icon\ImageSingleDrag.ico") ElseIf DataGridView1.SelectedRows.Count > 1 Then Windows.Forms.Cursor.Current = New Cursor("C:\Icon\ImageMultiDrag.ico") Else Windows.Forms.Cursor.Current = Cursors.Default End If End Sub 'This event will change mouse icon to no Drag icon if you move item to some other place where you can't drag the items Private Sub DataGridView1_DragLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DragLeave Windows.Forms.Cursor.Current = New Cursor("C:\Icon\ImageNoDrag.ico") End Sub 'This event will deselect the items from data gird Private Sub DataGridView1_CellMouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseUp Dim index As Integer index = e.RowIndex If index > -1 Then If DataGridView1.Rows(index).Selected = False Then Me.DataGridView1.ClearSelection() Me.DataGridView1.Rows(index).Selected = True End If End If End Sub If are dragging yout items into DataGridView2 then write your action code in following events Private Sub DataGridView2_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles dgNegotiationList.DragDrop 'Write your code to do action for drag items End Sub Problem: DataGridView CellDoubleClick and Drag drop both not woking togather Solution: Click Here Learn more about how to Drag and Drop items from data grid view. See the following sites. http://www.codeproject.com/KB/cpp/DataGridView_Drag-n-Drop.aspx http://www.pcreview.co.uk/forums/thread-3430543.php

How to use XML in SQL Server and Oracle database Stored Procedure to create dynamic InClause for select query

How to use XML in SQL Server and Oracle database Stored Procedure to create dynamic InClause for select query Lots of time you have a requirement to get multiple rows from database, that time you have to create InClause and send it to stored procedure. Oracle you can pass Array to stored procedure and it will solve you purpose to create InClause inside stored procedure, (see article using link http://riteshk.blogspot.com/2009/03/how-to-send-array-as-input-parameter-to.html) But in SQL Server database you don't have any way to pass Array to stored procedure, only way available to use XML for this. Here I am describing the way to pass XML to create InClause in SQL server and Oracle stored procedure. XML format can be multiple types and based on that your stored procedure structure will change. Following code has been tested in following environments 1) .NET framework 2.0 2) Oracle 10g 3) SQL Express 4) Microsoft Enterprises Library 3.0 Sample Xml which you will pass it to Stored Procedure <ArrayList> <Values><Value>40415</Value></Values> <Values><Value>59556</Value> </Values> <Values> <Value>67900</Value></Values> </ArrayList> Oracle Stored Procedure CREATE OR REPLACE PROCEDURE Pass_Array_Oracle ( prmInClause IN XMLTYPE, prmOutCursor OUT SYS_REFCURSOR ) AS BEGIN OPEN prmOutCursor FOR SELECT * FROM Employee EMP WHERE EMP.EMPNO IN ( SELECT XMLTYPE.EXTRACT (VALUE (EMP),'/Value/text()').getstringval() AS iEMPNO FROM TABLE (XMLSEQUENCE (prmInClause.EXTRACT ('/ArrayList/Values/Value'))) EMP ); END; SQL Stored Procedure CREATE PROCEDURE Pass_Array_SqlServer ( @prmInClauseData XML ) AS DECLARE @XMLDocPointer INT EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmInClauseData BEGIN --CREATE INCLAUSE FROM INPUT PARAMETER XML VALUE SELECT * FROM EMPLOYEE WHERE EMPNO IN ( SELECT Value FROM OPENXML (@XMLDocPointer, '/ArrayList/Values', 2) WITH (Value NVARCHAR(36))) END VB.NET Code from where You will execute stored procedure 'This method will execute Oracle and SQL Server Stored Procedure and give you 'data in the form of dataset Public Sub ExecuteStoredProcedure(ByVal xmlString As String, _ ByVal connectionString As String, ByVal spName As String, ByVal IsOracle As Boolean) Dim employeeCommand As DbCommand Dim dsData As New DataSet Dim connectDB As Database = DatabaseFactory.CreateDatabase(connectionString) employeeCommand = connectDB.GetStoredProcCommand(spName) If IsOracle Then connectDB.AddXmlInParameter(employeeCommand, "@prmInClauseData", xmlString) End If connectDB.AddCursorOutParameter(employeeCommand, "@prmOutCursor") dsData = connectDB.ExecuteDataSet(employeeCommand) End Sub How to use from VB.Net code Dim xmlString As String = "<ArrayList><Values><Value>40415</Value></Values>< Values><Value>59556</Value></Values><Values><Value> 67900</Value></Values></ArrayList>" Dim sqlConnectionString As String = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\EMPLOYEE.mdf;Integrated Security=True;User Instance=True" Dim sqlSpName As String = "Pass_Array_SqlServer" 'Execute SQL Server Stored Procedure from VB.NET Code ExecuteStoredProcedure(xmlString, sqlConnectionString, sqlSpName, False) 'This will give you 3 nos of rows because XML have 3 Empno MessageBox.Show(dsDataSet.Tables(0).Rows.Count) Dim oracleConnectionString As String = "Data Source=DSNName;User ID=UserId;Password=Password;" Dim oracleSpName As String = "Pass_Array_Oracle" 'Execute Oracle Stored Procedure from VB.NET Code ExecuteStoredProcedure(xmlString, oracleConnectionString, oracleSpName, True) 'This will give you 3 nos of rows because XML have 3 Empno MessageBox.Show(dsDataSet.Tables(0).Rows.Count)