Dare Devil Act (Khatron K Khiladi)

Bulk Upload of Excel in SQL SERVER

This Stored PRocedure takes the Uploaded Path of excel and inserts the fields in a table as defined.

CREATE PROCEDURE Procedure_Name
@ExcelPath text, @STATUSTEXT NVARCHAR(1) OUTPUT
AS
BEGIN
DECLARE @intErrorCode INT
SET NOCOUNT OFF;
BEGIN TRAN SP_TRAN
DECLARE
@service_provider varchar(300),
@path varchar(300),
@dbtype varchar(500),
@query varchar(300),
@datasource varchar(300)
set @service_provider='Microsoft.Jet.OLEDB.4.0'
set @dbtype='Excel 8.0;DATABASE='
set @path=@ExcelPath
set @query='select * from [Sheet1$]'
set @datasource=@dbtype+@path

EXEC('INSERT INTO Table_Name (Field1, Field2, Field3) SELECT convert(date, [Date],105), ltrim(rtrim(ExcelField1)) ,ltrim(rtrim(ExcelField2)) ,ltrim(rtrim(ExcelField3)) FROM OPENROWSET ('''+@service_provider+''', '''+@datasource+''', '''+@query+ ''') a')

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

COMMIT TRAN
SELECT @STATUSTEXT = '0'

PROBLEM:
IF (@intErrorCode <> 0)
BEGIN
SELECT @STATUSTEXT = '1'
ROLLBACK TRAN
END
SET NOCOUNT ON;
END

File Upload User Control

What it does?
The utility is a small user control that has the following in the ascx file

a. asp Label for texts before the file upload
b. asp file upload
c. asp Button
d. asp Label for Status.

In the ascx.cs file
There are 3 properties
a. setLabelText/getLabelText
b. setButtonText/getButtonText
c. setUploadPath/getUploadPath
and a delegate to pass the file full path with file name to the aspx page that is calling the User Control.
In the Page Load, the Text of the Labels are set that are passed by the calling page.
In the Button Click Event, the file is uploaded and the status of the Error Label is set and finally we are setting the event with the string filename.

In the aspx page, in Page Load we are setting the properties, calling the delegate and just writing the file path in the screen, this can be used to pass the information to a SP for Bulk Upload of the Excel File in database. (in another post )



Put the following code in ascx file
<div>
<span>
<asp:label id="lblUpload" runat="server"></asp:label>: <asp:fileupload id="txtUpload" runat="server">
</asp:fileupload></span>
<span><asp:button runat="server" id="btnUpload" onclick="btnUpload_Click"></asp:button></span>
<span> <asp:label id="lblError" runat="server"></asp:label></span>
</div>


Put the following in .ascx.cs file
public delegate void SendMsgToPage(string MessageToPage);

public partial class UploadFile : System.Web.UI.UserControl
{
public event SendMsgToPage sndMsgToPage;
private string _setLabelText, _setButtonText, _fileUploadPath, _file, _dbData;
bool _WriteToDb;

public void setLabelText(string Text)
{
_setLabelText = Text;
}
public string getLabelText()
{
if (_setLabelText == null || _setLabelText == "")
{
_setLabelText = "Select File";
}
return _setLabelText;
}
public void setButtonText(string Text)
{
_setButtonText = Text;
}
public string getButtonText()
{
if (_setButtonText == null || _setButtonText == "")
{
_setButtonText = "Upload";
}
return _setButtonText;
}

public void setUploadPath(string Text)
{
_fileUploadPath = Text;
}
public string getUploadPath()
{
if (_fileUploadPath == null || _fileUploadPath == "")
{
_fileUploadPath = "Uploads";
}
return _fileUploadPath;
}

protected void Page_Load(object sender, EventArgs e)
{
lblUpload.Text = _setLabelText;
btnUpload.Text = _setButtonText;
lblError.Visible = false;
}

protected void btnUpload_Click(object sender, EventArgs e)
{
lblError.Visible = true;
try
{
_file = Server.MapPath(_fileUploadPath) + "\\" + txtUpload.FileName;
txtUpload.SaveAs(_file);
lblError.Text = "File: " + txtUpload.FileName + " Uploaded Successfully";
}
catch (Exception ex)
{
_file = "";
lblError.Text = "File: " + txtUpload.FileName + " couldnot be uploaded due to " + ex.Message;
}
finally
{
sndMsgToPage(_file);
}
}
}

Put the following in the aspx file calling the User Control

<%@ Register src="Controls/UploadFile.ascx" tagname="UploadFile" tagprefix="uc1" %>

<uc1:uploadfile id="fileUpd" runat="server">
</uc1:uploadfile>


in the aspx.cs file
fileUpd.setLabelText(Put Label Text);
fileUpd.setButtonText(Put Button Text);
fileUpd.setUploadPath(Set the Upload Path); //should be a folder inside the current project
fileUpd.sndMsgToPage += delegate(string fileUploadPath)
{
Response.Write(fileUploadPath);
};

SAP DOTNET CONNECTOR 3.0

Sap came with a new version (3.0) of SAP Connector for Dotnet last Dec. It made the SAP connectivity from Dotnet easier compared to the Last Version (SAPConnector 2.0). Earlier the connector only worked with C# in VS 2003. It created a proxy for the SAP RFC ( Remote Function Call) beign called and so at any point of time if the RFC was modified the proxy needs to be re-generated. Now with 3.0 it is much easier to code with. Reference the "sapnco.dll" and the "sapnco_utils.dll" in your VS project (version doesnot matter, it can be 2K3/2K5/2K8).
Add the following code
const string ABAP_AS_POOLED = "DV";
RfcDestination destination = RfcDestinationManager.GetDestination(ABAP_AS_POOLED);
IRfcFunction function = null;
RfcSessionManager.BeginContext(destination);

try
{
function = destination.Repository.CreateFunction(Your RFC Function);
function.SetValue(RFC Import Parameter, Value to be Passed);
function.Invoke(destination);
IRfcTable detail = function[RFC Function Out Table].GetTable();
for (int i = 0; i < detail.RowCount; i++)
{
//set the current row - the row used for Get*/Set* operations
Response.Write(detail.GetValue(i));
}
}
catch (RfcBaseException ex)
{
Response.Write(ex.ToString());
return;
}
The above code just gives a brief idea of how to call a RFC.
For binding the output with a DataGrid/GridView, One has to create a datatable and update it and then bind the data table with the DataGrid/GridView.

Link to SAP Blog Help
http://weblogs.sdn.sap.com/cs/blank/view/wlg/23051