Import the data from Excel to Sql Server

OPENROWSET ( 'provider_name',
{ 'datasource'; 'user_id'; 'password' 'provider_string' },
{ [ catalog.][schema.]object 'query' }
)
In this article, we will only be using the following syntax of the OPENROWSET:OPENROWSET ( 'provider_name', 'provider_string', 'query' )
The provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry. In the case of Excel, the provider_name that we will use is "Microsoft.Jet.OLEDB.4.0".
The provider_string is a provider-specific connection string that is passed to initialize the OLE DB provider. provider_string typically encapsulates all the connection information needed to initialize the provider. For Excel, the provider_string that we will use is "Excel 8.0;Database=Excel File Name".
The query is a string constant sent to and executed by the provider. SQL Server does not process the query but processes query results returned by the provider (a pass-through query).


A B C D E F
------------------------------------------------------------------------------------------------
1 FirstName LastName Address City State ZIP
2 Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
3 Donald Duck Walt Disney World Lake Buena Vista FL 32830
4 George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
5 George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
6 Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
To query this data from the Excel file without using DTS to import it to a table in SQL Server, you can do the following SELECT statement with OPENROWSET:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
Sheet1 is the name of the worksheet in the Excel file. Make sure that the name of the worksheet is followed by the dollar sign ($) when specifying in the query. If the dollar sign is not included, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message:
The Microsoft Jet database engine could not find the object 'Sheet1'.
Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace
[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
The result of this query is as follows:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL NULL
Donald Duck Walt Disney World Lake Buena Vista FL NULL
George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
By default, the first row in the Excel file is assumed to be the column header, as can be seen from the output.
It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine. The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive. If the Excel file specified in the provider_string parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error.].
Just like any SELECT statement, conditions can be included in the query to filter out records from the Excel worksheet by simply specifying it in the WHERE clause. As an example, to display only the addresses of people from Florida you can do the following:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
WHERE [State] = 'FL'
This query gives the following result:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
Donald Duck Walt Disney World Lake Buena Vista FL 32830

Convert datetime and date formats

SELECT GETDATE()
ResuleSet:2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions.
The method he suggested was to useSELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed http://msdn.microsoft.com/en-us/library/ms187928.aspx

Insert multiple records at a time with single insert statement..

This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO
The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. He was quite pleased.USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO
The effective result is same

Hosting of WorkFlow at WCF

Hosting through Web app
protected void btnSubmit_Click(object sender, EventArgs e)
{
WorkflowRuntime WFRuntime = new WorkflowRuntime();
WFRuntime.StartRuntime();
ExternalDataExchangeService edx = new ExternalDataExchangeService();
WFRuntime.AddService(edx);
edx.AddService(new ApproveReq ());
Anada.AbsenceManager.Workflow.Model.Request Req = new Anada.AbsenceManager.Workflow.Model.Request();
Req.RequestId = 1;
Req.EmployeeId = 1;
Req.BossId = 3 ;
Req.StartDate = DateTime.Now;
Req.EndDate = DateTime.Now.AddDays(5);
Req.RequestStateId = 4;
Req.RequestTypeId = 1;
Req.UserName = "Prakash";
Req.CreateDate = DateTime.Now;
Req.ModifyDate = DateTime.Now;
Req.RequestDate = DateTime.Now;
Dictionary paras = new Dictionary();
//paras.Add("CountryID", Convert.ToInt32(txtCountryID .Text));
//paras.Add("mPersonId", Convert.ToInt32(txtPersonId .Text));
//paras.Add("CalendarId",Convert.ToInt32(txtCalendarId .Text));
//paras.Add("username", "Prakash");
//paras.Add("FromDate", Req.StartDate);
//paras.Add("ToDate", Req.EndDate);
//paras.Add("FromDate", Convert.ToDateTime(txtFromDate.Text));
paras.Add("mAppRequest", Req);
////WFSvc.WorkFlowSVCClient obj = new WFSvc.WorkFlowSVCClient();
////obj.GetRequestWFInstance(Anada.AbsenceManager.Workflow.Flows.Request mrequest, ////paras);
WorkflowInstance instance = WFRuntime.CreateWorkflow(typeof(Anada.AbsenceManager.Workflow.Flows.ApproveRequest), paras);
//instance.Start();
}

Custom Code activitys parameters validation

This is the code for validating the parameters of custom code activitys.
Use the validation manager class.
Here testing the Mail objects.
and in activity we have to use the attribute like....

[ActivityValidator (typeof (Anada.AbsenceManager.Workflow.Activity.CalendarPersonIDValidation ))]

public class SendMailValidation : ActivityValidator
{
#region"SendMailActivityParameterValidation"
//

// Method used to validate TO and From Email address
//

//
//
public override ValidationErrorCollection ValidateProperties(ValidationManager manager, object obj)
{
ValidationErrorCollection ValidationErrors = new ValidationErrorCollection(base.ValidateProperties(manager, obj));
SendMailActivity SendMailActivityTobeValidated = new SendMailActivity();
if (SendMailActivityTobeValidated == null)
{
throw new InvalidOperationException("Parameter obj is not of type SendMailActivity");
}
if (!IsValidEmailAddress(SendMailActivityTobeValidated.To))
{
ValidationError CustomActivityValidationError = new ValidationError(string.Format("\'{0}\' is an Invalid destination e-mail address", SendMailActivityTobeValidated.To), 1);
ValidationErrors.Add(CustomActivityValidationError);
}
if (!IsValidEmailAddress(SendMailActivityTobeValidated.From))
{
ValidationError CustomActivityValidationError = new ValidationError(string.Format("\'{0}\' is an Invalid destination e-mail address", SendMailActivityTobeValidated.From), 1);
ValidationErrors.Add(CustomActivityValidationError);
}

return ValidationErrors;
}
public Boolean IsValidEmailAddress(String address)
{
//

// Method used to check the length and formate of Email address
//

//
//
// must only proceed with validation if we have data
// to validate
if (address == null address.Length == 0)
return true;
Regex rx = new Regex(@"[^A-Za-z0-9@\-_.]", RegexOptions.Compiled);
MatchCollection matches = rx.Matches(address);
if (matches.Count > 0)
return false;
// Must have an '@' character
int i = address.IndexOf('@');
// Must be at least three chars after the @
if (i <= 0 i >= address.Length - 3)
return false;
// Must only be one '@' character
if (address.IndexOf('@', i + 1) >= 0)
return false;
// Find the last . in the address
int j = address.LastIndexOf('.');
// The dot can't be before or immediately after the @ char
if (j >= 0 && j <= i + 1)
return false;
return true;
}
#endregion
}

setting Icons to Code activity and designer theme

First To set the Icon to Activity set the property to "EmbededResourse" . Code comes here.
#region look n feel
internal sealed class ApproveRequestCustomActivityDesignerTheme : ActivityDesignerTheme { public ApproveRequestCustomActivityDesignerTheme(WorkflowTheme theme) : base(theme) { this.BorderColor = Color.Chocolate ;
this.BorderStyle = System.Drawing.Drawing2D.DashStyle.Solid;//DashStyle.Solid;
this.BackColorStart = Color.BurlyWood ;
this.BackColorEnd = Color.CornflowerBlue;
this.BackgroundStyle = System.Drawing.Drawing2D.LinearGradientMode.Vertical; //LinearGradientMode.Vertical; } } [ActivityDesignerThemeAttribute(typeof(ApproveRequestCustomActivityDesignerTheme))] public class ApproveRequestCustomActivityDesigner : ActivityDesigner { } #endregion [ToolboxItemAttribute(typeof(ActivityToolboxItem))] [Designer(typeof(ApproveRequestCustomActivityDesigner), typeof(IDesigner))] [ToolboxBitmap(typeof(ApproveRequestActivity), "Resources.ApprovalReq.bmp")]

F11 Debugging is not working in .Net

Hi,
In visual studio while debugging the workflows its not going inside the workflow...