SQL-MSBI(SSIS,SSAS)-ASP.NET-MONGODB

How to Import XML file Data into SQLServer Table

Sample XML file error_1.xml

<?xml version="1.0" encoding="UTF-8"?>
<api:response type="failure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:api="https://audience.job.com/services/flow/ext-register">-<errors>-<error field="passwordCriteria.password" code="regex">
<![CDATA[Password may not contain spaces ]]>
</error>-<error field="emails[0].value" code="emailInvalidFormat">
<![CDATA[Bad email format]]>
</error></errors></api:response>

Query To view the Data from XML file

SELECT xCol FROM (SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB) AS xCol) AS R(xCol)

The output will be as same as xml format file.
--------------------------------------------
ï»¿<?xml version="1.0" encoding="utf-8"?> <api:response xmlns:api="https://audience.job.com/services/flow/ext-register" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure"> <errors> <error code="regex" field="passwordCriteria.password"><![CDATA[Password may not contain spaces ]]></error> <error code="emailInvalidFormat" field="emails[0].value"><![CDATA[Bad email format]]></error> </errors> </api:response>

If we are converting the xCol into XML data type then It will strip or remove the DTD from XML and stored in Table.

I) SELECT CONVERT(xml, BulkColumn)FROM OPENROWSET(Bulk 'E:\error_1.xml', SINGLE_BLOB) [rowsetresults]

II) SELECT convert(xml,xCol) FROM
(SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB) AS xCol)
AS R(xCol)

Output

<api:response xmlns:api="https://audience.job.com/services/flow/ext-register"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure">
<errors>
<error code="regex" field="passwordCriteria.password">Password may not contain spaces </error>
<error code="emailInvalidFormat" field="emails[0].value">Bad email format</error>
</errors>

</api:response>


using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
// End of code to move
//
    private void InsertXmlCustomersUsingSqlBulkCopy()
    {
        // (YOU MUST CHANGE THE CONNECTION STRING TO MATCH YOUR SYSTEM)
        String sDatabaseConnectionString = @"Data Source=SERVERNAME;Initial Catalog=XMLTest;Integrated Security=True";
        //
    

        // (YOU MUST CHANGE THE PATH TO YOUR SYSTEMS DRIVE:\PATH)
        String sXMLFile = @"D:\XMLData\Customers.XML";
        try
        {
            // Instanciate a new Sql Connection.
            using (SqlConnection oConn = new SqlConnection(sDatabaseConnectionString))
            {
                // Open the Connection to the database
                oConn.Open();
                // Instanciate a new DataSet
                using (DataSet dsTemp = new DataSet())
                {
                    //Read the XML file into the DataSet
                    dsTemp.ReadXml(sXMLFile);
                    // Instante a datatable from the DataSet
                    using (DataTable dt = dsTemp.Tables[0])
                    {
                        // Instanciate a new SqlBulkCopy object using the connection
                        using (SqlBulkCopy sb = new SqlBulkCopy(oConn))
                        {
                            // Assign BatchSize
                            sb.BatchSize = 50;
                            // Assign Destination Table Name
                            sb.DestinationTableName = "Customers";
                            // Map fields from DB Field Names to the XML Field Names
                            sb.ColumnMappings.Add("ID", "ID");
                            sb.ColumnMappings.Add("FirstName", "FirstName");
                            sb.ColumnMappings.Add("LastName", "LastName");
                            sb.ColumnMappings.Add("DOB", "DOB");
                            sb.ColumnMappings.Add("Address", "Address");
                            sb.ColumnMappings.Add("City", "City");
                            sb.ColumnMappings.Add("State", "State");
                            sb.ColumnMappings.Add("Zip", "Zip");
                            sb.WriteToServer(dt);
                        }
                    }
                }
            }
        }
        // Catch any SQL errors first
        catch (SqlException ex)
        {
            MessageBox.Show("Sql Error: " + ex.Message);
        }
        // Was not a SQL error, so handle the Exception
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
    }

How to Create Crystal Report With Simple way

How to create Crystal report simple way?

Step1: Right click on Solution,Select Add New Item.
Step2: Select DataSet from Template. Rename DataSet example : StockDataSet.xsd
Step3: Select DataTable from DataSet components and Drag into DataSet design surface.
Step4: Rename the DataTable and add new columns in data table.
Step5: Now Add new item CrystalReport from Reporting Pan.
Step6: Select Standard ReportCreation wizard.
Step7: Expand Project Data. Then expand ADO.NET DataSets. There we can see Data table which we created in DataSet design.
Step8: Select that Datatable and move to Selected Tables.Click Next
Step9: Select the fields from Available Fields Pan and move to Fields To Display Pan.
Step10: Click Finish. After finishing we will get CrystalReportdesign with fields.
Step11: Now Add new Item Web form and rename.
Step12: Drag CrystalReportViewer into Form.
Step13: Double click on CrystalReportViewer And Add following Code in Crystalreportload.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WindowsFormsTest
{
public partial class StockForm : Form
{
public StockForm()
{
InitializeComponent();
}

private void crystalReportViewer1_Load(object sender, EventArgs e)
{

SqlConnection conn = Common.conn;

conn.Open();
string sql = " select M.Item_Name Item_Name,S.Trans_Date Trans_Date,SUM(S.NofStock) NofStock From Item_Master M inner Join Item_Stock S on M.Item_ID= S.Item_ID Group by M.Item_Name ,S.Trans_Date ";

SqlDataAdapter rd = new SqlDataAdapter(sql, conn);

StockDataSet ds = new StockDataSet(); //Name of data set
rd.Fill(ds, "Stockdtl");

conn.Close();

StockCrystalReport objRpt = new StockCrystalReport(); // Name of crystal report
objRpt.SetDataSource(ds.Tables["Stock"]);

crystalReportViewer1.ReportSource = objRpt;
crystalReportViewer1.Refresh();

}
}
}

How to create common class in Project.

Step1: Add new Item Class. Rename the class.
Step2: Add code in class.

Example Code for Sql Connection class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace WindowsFormsTest
{
class Common
{
static string ConnectionStr = @"Server=Job\MSSQLSERVER2008;Database=Test_Quiz;user=sa;password=password";
public static SqlConnection conn = new SqlConnection(ConnectionStr);
}
}

How to create Crystal report in C#: Steps

How to create Crystal report:

1) From the main menu in Visual Studio C# project select PROJECT-->Add New Item .
Then Add New Item dialogue will appear or (from Reports) and select Crystal Reports from the dialogue box. Give name for report

2) Select Report type from Crystal Reports gallery.

3) Next step is to select the appropriate connection to your database .
Here we are going to select OLEDB Connection for SQL Server to connect Crystal Reports in C#.
Select OLE DB (ADO) from Create New Connection.

4) Select Microsoft OLE DB Provider for SQL Server .

5) The next screen is the SQL Server authentication screen for connecting to the database -
Select your Sql Server name , enter userid , password and select your Database Name.

6) Click next , Then the screen shows OLE DB Property values , leave it as it is , and then click finish button.

7) After you click the finish button , the next window you will get your Server name under OLEDB Connection, from there selected database name and click the tables, then you can see all your tables from your database.

8) Select all fields from table to the right side list.

9) Click Finish Button. Then you can see the Crystal Reports designer window in your C# project.
In the Crystal Reports designer window you can see the selected fields from Product table.
You can arrange the field Objects and design of the screen according your requirements.

10) Now the designing part is over and the next step is to call the Crystal Reports in your C# application
and view it through Crystal Reports Viewer control in C#. Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

11) You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code."using CrystalDecisions.CrystalReports.Engine;"

In this case everytime it will ask user name and password.

In that case we need to write code in forms
using CrystalDecisions.Shared;

ReportDocument cryRpt = new ReportDocument();
TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
Tables CrTables ;

cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt");

crConnectionInfo.ServerName = "YOUR SERVER NAME";
crConnectionInfo.DatabaseName = "YOUR DATABASE NAME";
crConnectionInfo.UserID = "YOUR DATABASE USERNAME";
crConnectionInfo.Password = "YOUR DATABASE PASSWORD";

CrTables = cryRpt.Database.Tables ;
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
{
crtableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}

crystalReportViewer1.ReportSource = cryRpt;
crystalReportViewer1.Refresh();

Or

ReportDocument cryRpt = new ReportDocument();
cryRpt.Load(@"C:\Users\job\Documents\Visual Studio 2008\Projects\Stock_Test\Stock_Test\CrystalReport1.rpt");
cryRpt.SetDatabaseLogon("User Name","Password","Server Name", "Database");
crystalReportViewer1.ReportSource = cryRpt;
crystalReportViewer1.Refresh();

Connection String in Script Task in SSIS

Connection String in Script Task in SSIS

Create ADO.net connection in Connection Managers Tab

(That is .net Providers\ SqlClient Data Provider)
(Connection String: Data Source= Server Name;Initial Catalog= Data base Name;Integrated Security=True;)

using System.Data.SqlClient;

SqlConnection Connection = (SqlConnection)Dts.Connections["Name of Connection manager(Ado connection)"].AcquireConnection(null);
SqlCommand sqlCommand = new SqlCommand();
if (Connection.State == ConnectionState.Closed)
Connection.Open();
sqlCommand.Connection = Connection;
sqlCommand.CommandText = "Write query here or give Stored procedure";
sqlCommand.CommandType = CommandType.StoredProcedure; // give type if it is stored procedure(SP)
sqlCommand.Parameters.AddWithValue("Parameter1", DbType.String).Value = variableName1.Trim(); // passing input parameter into SP
sqlCommand.Parameters.AddWithValue("EParameter2", DbType.String).Value = variableName2.Trim();

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataSet ds1 = new DataSet();
sqlDataAdapter.Fill(ds1);
Dts.Connections["Name of Connection manager(Ado connection)"].ReleaseConnection(null);
Connection.Close();
sqlCommand.Dispose();

How we can create Identity and to set seed and reseed Identity Column In SQL

1)Create Identity Column in a Table

Create Table Emp_Master(Emp_ID int Identity(1,1),Emp_Name Varchar(100))
Identity(1,1) In this first 1 is Identity Seed and second 1 is identity Increment.

Identity Seed : Exposes the Initial row value for an identity column.
Identity Increment: Exposes the value added to the maximum existing row identity value when generating the next identity value.

2)How to find the Identity value of a table
IDENT_CURRENT('Tablename')

select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
5 Jose
6 John

Select IDENT_CURRENT ('Emp_Master') As Value
Value
6

3)How to Reseed the identity value

DBCC CheckIdent(Tablename,Reseed, your desired value)

If we delete data from Emp_Master table, our seed value will not to set back. In that purpose we need to reseed the identity value.
select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo

In this example we deleted empid 5 and 6. Now our current identity value will be 6
Select IDENT_CURRENT ('Emp_Master') As Value
Value
6

And if we insert another record into Emp_Master table the value of Emp_ID will be 7 not be 5
select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
7 John

In this case we need to Reseed the identity column to 4. we deleted where empid is 7
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo

DBCC CheckIdent(Emp_Master,Reseed,4)

Now our seed value is 4 . And if we insert empid into emp_master table the value of Emp_id will be 5.
Select IDENT_CURRENT ('Emp_Master') As Value
Value
4

Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
5 John

How to read and compare single line from a File with another File

public void Main()

{

string Dpath = @"D:\SourceFiles\Customer\Sale_Files\";

string wpath=@"SourceFiles\Customer\Sale_Files\Files.csv";

StreamWriter wr = new StreamWriter(wpath);

DirectoryInfo dirInfo = new DirectoryInfo(Dpath); // directory informations

foreach (FileInfo f in dirInfo.GetFiles()) // file informations from that directory

{

StreamReader rd = new StreamReader(f.FullName); //get full filename

string line1;

line1 = rd.ReadLine(); // Read Single line from File

wr.WriteLine(line1);

MessageBox.Show(f.FullName);

foreach (FileInfo f2 in dirInfo.GetFiles())

{

StreamReader rd1 = new StreamReader(f2.FullName);

string line2;

line2 = rd1.ReadLine();

bool result = line1.Equals(line2, StringComparison.OrdinalIgnoreCase); //Comparison with line1 and line2

if (result == false)

{

MessageBox.Show(f2.FullName);

}