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

Showing posts with label C#. Show all posts

Read Excel Data

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions; //split words from a line
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
StreamWriter sw = new StreamWriter(@"c:\text.txt");
string connString = "";
string fileExtension = "";
string fileName = "";

string ExcelPath = @"C:\Test.xlsx";
fileName = Path.GetFileName(ExcelPath);
fileExtension = Path.GetExtension(ExcelPath);

if (fileExtension == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
}
else if (fileExtension == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
}

OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text; // Declaration of cmd as text
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd); //Take data from source to data table
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Adding all sheet Names into tables

string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
DataTable dtColumnName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, getExcelSheetName, null });

DataView dv = dtColumnName.DefaultView;
dv.Sort = "ORDINAL_POSITION";
dtColumnName = dv.ToTable();

cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";

dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
List<string> listColumn = new List<string>();
foreach (DataRow row in dtColumnName.Rows)
{
listColumn.Add(row["Column_name"].ToString());
}
foreach (string prime in listColumn) // Loop through List with foreach
{
sw.Write(prime + '\t');
}
sw.WriteLine();
foreach (DataRow row in dtExcelRecords.Rows)
//for(int i=0 ;i<dtExcelRecords.Rows.Count;i++)
{

foreach (var item in row.ItemArray) // Loop over the items.
//for(int j=0;j<dtExcelRecords.Columns .Count ;j++)
{
//sw.Write(dtExcelRecords.Rows[i][j].ToString());
sw.Write(item); // Invokes ToString abstract method.
sw.Write('\t');
}
sw.WriteLine();

}

//Console.Write(getExcelSheetName);
sw.Close();
StreamReader sr = new StreamReader(@"c:\text.txt");

string[] columns;
string line;
while ((line = sr.ReadLine()) != null)
{
columns = line.Split('\t'); //using for single character as delimiter
//Regex.Split(line, "\t"); //using for string as delimiter
foreach (string part in columns)
{
Console.WriteLine(part);
}

}
Console.Read();
sr.Close();
}
}
}


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();

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);

}

How we can download data from website using Script task with C# code

How can we download data from website using Script task with C# code

/*
We have customer details in CSV files on monthly basis in http site of jcubefinance.com.
We need to download CSV files on the basis of date and create same date folder and load the files.
Example of files in http:
http://jcubefinance.com/customer/2012-01-01/customer.csv,
http://jcubefinance.com/customer/2012-02-01/customer.csv,
http://jcubefinance.com/customer/2012-03-01/customer.csv
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web;
using System.Net;
using System.Collections.Generic;

namespace ST_b30a48fe352a48ddbef16d2da49c11ae.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{

WebClient mySSISWebClient = new WebClient();
mySSISWebClient.Credentials = new NetworkCredential("username", "password"); // Give Credentails username and password
String URI = @"http://jcubefinance.com/customer/"; // Give http site name.

string URIPath = String.Empty;
string URIFullpath = String.Empty;

string sfile1 = @"C:\Test\Test.html";
string sfile2 = @"C:\Test\NewTest.html";
string sfile3 = @"C:\Test\";

mySSISWebClient.DownloadFile(URI, sfile1); // It download data from this http://jcubefinance.com/customer/ website and save into C:\Test\Test.html
//Sample data <a href="2012-01-01/">2012-01-01/</a>
// <a href="2012-02-01/">2012-02-01/</a>

string data = string.Empty;

StreamReader reader = new StreamReader(sfile1); // read the data from sfile1

string line;
int len = 0; ;
int len1 = 0; ;

while ((line = reader.ReadLine()) != null) // read line by line
{
if (line.StartsWith("<a href"))
{
len = line.IndexOf(">");
len = len + 1;
len1 = line.IndexOf("</a>");
data = line.Substring(len, len1 - len); // data : 2012-01-01/
URIPath = URI + data; //URIPath : http://jcubefinance.com/customer/2012-01-01/

mySSISWebClient.DownloadFile(URIPath, sfile2); // It download data from this http://jcubefinance.com/customer/2012-01-01/ website and save into C:\Test\NewTest.html
string path = string.Empty;
path = sfile3 + data;
if (!Directory.Exists(path)) // Creating Date folder in local Server according to Date if directory is not exists. path: C:\Test\2012-01-01
{
Directory.CreateDirectory(path);
}

StreamReader sreader = new StreamReader(sfile2);
string sdata = string.Empty;
string sline;
int slen = 0; ;
int slen1 = 0; ;

while ((sline = sreader.ReadLine()) != null)
{
if (sline.StartsWith("<a href"))
{
slen = sline.IndexOf(">");
slen = slen + 1;
slen1 = sline.IndexOf("</a>");
sdata = sline.Substring(slen, slen1 - slen); //sdata: customer.csv
URIFullpath = URIPath + sdata; //URIFullpath: http://jcubefinance.com/customer/2012-01-01/customer.csv

mySSISWebClient.DownloadFile(URIFullpath, path + sdata); // It download data from this http://jcubefinance.com/customer/2012-01-01/customer.csv website and save into C:\Test\2012-01-01\customer.csv

}
}
sreader.Close(); //close the sfile2

}
}
reader.Close(); // close the sfile1

Dts.TaskResult = (int)ScriptResults.Success;
}

}

}

Convert SQL data to XML

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {



            try
            {
                string conn = @"Data Source=Admin123; database=etltest;uid=sa;pwd=password";
                SqlConnection myConn = new SqlConnection(conn);
                SqlCommand myCmd = new SqlCommand();
                string query = "select * from D_EMPLOYEE_INFO";

                SqlDataAdapter myAdp = new SqlDataAdapter(query, myConn);

                DataSet dsData = new DataSet();

                myConn.Open();

                myAdp.Fill(dsData);
                dsData.Tables[0].TableName = "D_employee";

                string appPath = @"C:";

                    if (!Directory.Exists(appPath + "\\XmlDocs"))
                        System.IO.Directory.CreateDirectory(appPath + "\\XmlDocs");

                    string xmlDocName = appPath + "\\XmlDocs\\" + dsData.Tables[0].TableName + ".xml";
                    if (File.Exists(appPath + "\\XmlDocs\\" + dsData.Tables[0].TableName + ".xml"))
                        File.Delete(appPath + "\\XmlDocs\\" + dsData.Tables[0].TableName + ".xml");

                    StreamWriter myStreamWriter = new StreamWriter(xmlDocName);
                    dsData.Tables[0].WriteXml(myStreamWriter);
                    myStreamWriter.Close();

                myConn.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }
    }
}

Convert CSV to XML

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

namespace csvTOxml
{
    class Program
    {
        static void Main(string[] args)
        {

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\;Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";

        OleDbConnection conn = new OleDbConnection(strConn);

        OleDbDataAdapter da = default(OleDbDataAdapter);

        DataSet ds = new DataSet();

        OleDbCommand mycmd = new OleDbCommand("Select * from test.csv", conn);

        try
            {
            da = new OleDbDataAdapter(mycmd);

            da.Fill(ds);
            }

        catch (Exception ex)
           {
            Console.WriteLine(ex.ToString());

            return;

           }

        ds.WriteXml("C:\\CSV.xml");

         }
     }
}