Read Data from Excel and Export to Sql server

SqlBulkCopy Use for Insert Bulk Data into sql server.You have to create one table inside the sql server and create only structure of the table which we have in Excel .and Use the code Below.

//File upload pick the file name and Details is a sheet name Inside the .Xls file

DataTable dt = new DataTable();

protected void Button2_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileUpload1.PostedFile.FileName +";Extended Properties=Excel 8.0; ");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Details$] ", con);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
InsertBulkDATA();

}
private void InsertBulkDATA()
{
SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["kumarConnectionString"].ConnectionString);
sbc.DestinationTableName = "tblData";
sbc.WriteToServer(dt);
}

Hosting WCF Services in IIS

This is the link which will describe how to Host wcf services in IIS

http://www.youtube.com/watch?v=mX8quq7MoeI&feature=related

WCF Implementation(for retrieve Data and match the data for login)

File---> New WEBSITE---->WCF SERVICES
we have Iservice.cs Interface and second is Service.cs Class

Inside the Iservice.cs (Interface) we have to define one interface for that.(that will be accessible with other application)

[OperationContract]
DataTable GetUserId();

second is....Service.cs Class give the method body which we create interface...

}
public DataTable GetUserId()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["kumarConnectionString"].ConnectionString);
string cmd = "select Name from customer";
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd, con);
DataSet ds = new DataSet();
da.Fill(ds);

return ds.Tables[0];
}
after that Build the website....

///////Use service with other application.....

create new website

Add services with your application :- Right click on solution explorer there is Add Service Reference select your services otherwise paste the url of service and ok .

////Inside the button click u can acces use that....

protected void Button1_Click(object sender, EventArgs e)
{
ServiceReference1.ServiceClient objService = new ServiceReference1.ServiceClient();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["kumarConnectionString"].ConnectionString);
con.Open();
DataTable ds = new DataTable();
ds = objService.GetUserId();
if (ds.Rows.Count > 0)
{
foreach (DataRow item in ds.Rows)
{
string id = item["name"].ToString();
if (id == textUserId.Text)
{
lblMessage.Text = "Valid";
break;
}
else
lblMessage.Text = "InValid";
}

}
}


///////want to use same application.........

Right click on solution explorer on same website and click on AddNewItem pick NewForm ...
Inside new form ..............

Add services with your application :- Right click on solution explorer there is Add Service Reference select your services otherwise paste the url of service and ok .


protected void Button1_Click(object sender, EventArgs e)
{
Service sf = new Service();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["kumarConnectionString"].ConnectionString);
con.Open();
DataTable ds = new DataTable();
ds = sf.GetUserId();
if (ds.Rows.Count > 0)
{
foreach (DataRow item in ds.Rows)
{
string id = item["name"].ToString();
if (id == textUserId.Text)
{
lblMessage.Text = "Valid";
break;
}
else
lblMessage.Text = "InValid";
}

}
}

WCF Introduction

//This is the link for Understand the End Point.
http://www.youtube.com/watch?v=GC344FZeWqc&feature=related

Windows Communication Foundation (Code named Indigo) is a programming platform and runtime system for building, configuring and deploying network-distributed services. It is the latest service oriented technology; Interoperability is the fundamental characteristics of WCF. It is unified programming model provided in .Net Framework 3.0. WCF is a combined features of Web Service, Remoting, MSMQ and COM+. WCF provides a common platform for all .NET communication.

WCF services provide better reliability and security in compared to ASMX web services.

In WCF, there is no need to make much change in code for implementing the security model and changing the binding. Small changes in the configuration will make your requirements.

WCF has integrated logging mechanism, changing the configuration file settings will provide this functionality. In other technology developer has to write the code.

EndPoint:---WCF Service is a program that exposes a collection of Endpoints. Each Endpoint is a portal for communicating with the world.

All the WCF communications are take place through end point. End point consists of three components. 1)Address 2)Binding 3)CONTRACT

1) Address:- Basically URL, specifies where this WCF service is hosted .Client will use this url to connect to the service. e.g

2) Binding:- Binding will describes how client will communicate with service. There are different protocols available for the WCF to communicate to the Client.

Simple definition for Binding describes how the client will communicate with service. We can understand with an example.

Consider a scenario say, I am creating a service that has to be used by two type of client. One of the client will access SOAP using http and other client will access Binary using TCP. How it can be done? With Web service it is very difficult to achieve, but in WCF its just we need to add extra endpoint in the configuration file.
EX:- endpoint address="http://localhost:8090/MyService/MathService.svc"
contract="IMathService"
binding="wsHttpBinding"/>
endpoint address="net.tcp://localhost:8080/MyService/MathService.svc"
contract="IMathService"
binding="netTcpBinding"/>


3)cONTRACT:- Collection of operation that specifies what the endpoint will communicate with outside world.In WCF, all services are exposed as contracts. Contract is a platform-neutral and standard way of describing what the service does. Mainly there are four types of contracts available in WCF.......

Service Contract :-Service contracts describe the operation that service can provide. For Eg, a Service provide to know the temperature of the city based on the zip code, this service is called as Service contract. It will be created using Service and Operational Contract attribute.


Data Contract :- Data contract describes the custom data type which is exposed to the client. This defines the data types, that are passed to and from service. Data types like int, string are identified by the client because it is already mention in XML schema definition language document, but custom created class or data types cannot be identified by the client e.g. Employee data type. By using DataContract we can make client to be aware of Employee data type that are returning or passing parameter to the method.


Message Contract :-Default SOAP message format is provided by the WCF runtime for communication between Client and service. If it is not meeting your requirements then we can create our own message format. This can be achieved by using Message Contract attribute.



Fault Contract :- Suppose the service I consumed is not working in the client application. I want to know the real cause of the problem. How I can know the error? For this we are having Fault Contract. Fault Contract provides documented view for error occurred in the service to client

Application Pool and Application Domain? What is the difference between the two.

1)Application pool is a grouping of URLs that is routed to one or more worker processes.

2)An application pool is a group of one or more URLs of different Web applications and Web sites. Any Web directory or virtual directory can be assigned to an application pool.
Every application within an application pool shares the same worker process executable, W3wp.exe, the worker process that services one application pool is separated from the worker
process that services another [Like starting MS Word and opening many word documents]. Each separate worker process provides a process boundary so that when an application is assigned to one application pool, problems in other application pools do not affect the application. This
ensures that if a worker process fails, it does not affect the applications running in other application pools. [i.e] for Eg., If word document is having issue it should not
logically affect your Excel Sheet isn’t it. application domain is a mechanism (similar to a process in an operating system) used to isolate executed software applications from one another so that they do not affect each other. [i.e] opening of MS WORD doesn’t affect MS EXCEL
you can open and close both the applications any time since there is no dependency between the applications. Each application domain has its own virtual address space which scopes the resources for the application domain using that address space.

sql server funcation Return Table (User Defined Function)

create function fun(@id int)
returns table
as
return (select name from students where id=@id)


//How to use in sql server
select * from master.dbo.fun(1);

////how to use in asp.net

con.open();
SqlCommand cmd = new SqlCommand("select name from schools.dbo.fun(3)", con);
SqlDataReader dr2 = cmd.ExecuteReader();

if (dr2.HasRows)
{
while (dr2.Read())
{
string s= dr2.GetString(0);
}
}
con.close();



///another Function return string

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
RETURN 'Doe, John'
END

//how to use function in sql server

PRINT Schools.dbo.GetFullName();

sql server DataBase Reference(Help)

http://www.functionx.com/sqlserver/index.htm