Decoding Asp.net , Asp.net MVC, SQL Server ,Firebird,Jquery,JavaScript

Monday, January 14, 2013

OPENDATASOURCE

OpenDataSource(provider_name, init_string)

For example

SELECT
FirstName,
Gender
FROM
OpenDataSource (
'SQLOLEDB',
'DataSource = NOLI\SQL2;UserID=myUserID;Password=myPassword'
).Organisation.dbo.Employees

Another Example

The following example creates an ad hoc connection to the Payroll instance of SQL Server on serverLondon, and queries the AdventureWorks2012.HumanResources.Employee table. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=London\Payroll;Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Employee

The following example creates an ad hoc connection to an Excel spreadsheet in the 1997 - 2003 format.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=London\Payroll;Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Employee

The following example creates an ad hoc connection to an Excel spreadsheet in the 1997 - 2003 format.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

PDF Generate using iTextSharp

using iTextSharp.text;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;
using System.IO;

protected void btnpdf_Click(object sender, EventArgs e)
{

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        string fileName = " EmployeeTranferReport.pdf";
        Response.AppendHeader("Content-Type", "application/pdf");
        Response.AppendHeader("Content-disposition", "attachment; filename=" + fileName);
        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        stringWrite.WriteLine("<html><body encoding=" + BaseFont.IDENTITY_H + " style='font-family:Arial Unicode MS;font-size:12;'> <table style='width:100%'><tr><td align='center'><b>अगरतला नगरपालिका परिषद</b></td></tr><tr><td align='center'>अगरतला</td></tr></table> </body></html>");

        HtmlTextWriter hw = new HtmlTextWriter(stringWrite);
        StringReader sr = new StringReader(stringWrite.ToString());
        Document pdfDoc = new Document(PageSize.A4, 20f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
        PdfWriter wi = PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        pdfDoc.Open();

string fontpath = Environment.GetFolderPath(Environment.SpecialFolder.Fonts) + "\\ARIALUNI.TTF"; // "ARIALUNI.TTF" file copied from fonts folder and placed in the folder

        BaseFont bf = BaseFont.CreateFont(fontpath, BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
        FontFactory.RegisterDirectory(Environment.GetFolderPath(Environment.SpecialFolder.Fonts), true);
        FontFactory.Register(fontpath, "Arial Unicode MS");
        FontFactory.RegisterFamily("Arial Unicode MS", "Arial Unicode MS", fontpath);
        htmlparser.Parse(sr);
        pdfDoc.Close();
        Response.Write(pdfDoc);
        Response.End();

}

PDF Generate in

using iTextSharp.text;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;
using System.IO;

protected void btnpdf_Click(object sender, EventArgs e)
{

string fontpath = Environment.GetFolderPath(Environment.SpecialFolder.Fonts) + "\\ARIALUNI.TTF"; // "ARIALUNI.TTF" file copied from fonts folder and placed in the folder

}

Wednesday, December 26, 2012

Creating Update Panel in Code Behind Dynamically

Creating Update Panel in code behind dynamically can be a bit tricky, comparing to doing it at the front end. Here's a quick view of the code we write to do this.

UpdatePanel up = new UpdatePanel();
up.ID = "UpdatePanel1";
up.ChildrenAsTriggers = true;
up.UpdateMode = UpdatePanelUpdateMode.Conditional;
this.Controls.Add(up);
this.textBox = new TextBox();
this.textBox.ID = "TextBox";
up.ContentTemplateContainer.Controls.Add(this.textBox);
this.label = new Label();
this.label.Text = "Enter your name.";
up.ContentTemplateContainer.Controls.Add(this.label);
Button button = new Button();
button.Text = "Say Hello";
button.Click += new EventHandler(HandleButtonClick);
up.ContentTemplateContainer.Controls.Add(button);

and a testing event handler:

private void HandleButtonClick(object sender, EventArgs eventArgs)
        {
            this.label.Text = "Hello " + this.textBox.Text;
        }

Wednesday, November 28, 2012

Import CSV File Into SQL Server Using Bulk Insert

SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

February 6, 2008 by pinaldave

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GOCREATE TABLE CSVTest(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate SMALLDATETIME)GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTestFROM 'c:\csvtest.txt'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')GO--Check the content of the table.SELECT *FROM CSVTest
GO--Drop the table to clean up database.DROP TABLE CSVTest
GO

LINQ Query Samples

http://msdn.microsoft.com/en-us/vstudio//bb688088