Open UTF-8 CSV File in MS Excel using C#.NET

Sometime there are requirements where we are required to open CSV file in excel.
if it a manual process then there is now issue. MS Excel provide features using which you can open CSV by default.

Excel also provides facility to open non csv files(Delimited Text Files). To use this feature
go to

Note: I using MS EXCEL 2010

Data Ribbon - > Select - > "From Text" Option

Select text file you want to open and follow the wizard.

Above feature also allow you to open UTF-8 format files.

How to achieve the same functionality using C#.NET?
Some will open the file and read it line by line and convert UTF-8 format.
This will take lot of time when you have large file.

Parameters:
xlSheet: reference to Excel worksheet object. The worksheet needs to active worksheet
Filpath: Path to selected Text or CSV File

public void Importcsv(ref Excel.Worksheet xlSheet,string FilePath)
{
Excel.Range ranage = xlSheet.get_Range("$A$1", Type.Missing);
Excel.QueryTable QT = xlSheet.QueryTables.Add("TEXT;" + FilePath, ranage);

QT.Name = Path.GetFileNameWithoutExtension(FilePath);
QT.FieldNames = true;
QT.RowNumbers = true;
QT.FillAdjacentFormulas = false;
QT.PreserveFormatting = true;
QT.RefreshOnFileOpen = false;
QT.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
QT.SavePassword = false;
QT.SaveData = true;
QT.AdjustColumnWidth = true;
QT.RefreshPeriod = 0;
QT.TextFilePromptOnRefresh = false;
QT.TextFilePlatform = 65001;
QT.TextFileStartRow = 1;
QT.TextFileParseType = Excel.XlTextParsingType.xlDelimited;
QT.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
QT.TextFileConsecutiveDelimiter = false;
QT.TextFileTabDelimiter = false;
QT.TextFileSemicolonDelimiter = false;
QT.TextFileCommaDelimiter = true;
QT.TextFileSpaceDelimiter = false;
QT.TextFileDecimalSeparator = ",";
QT.TextFileThousandsSeparator = ".";

// QT.TextFileColumnDataTypes =

The above property defines data type of each column based on the file you have selected the data type is specified based on index like 1, 2 like wise

xlSheet.QueryTables[1].Destination.EntireColumn.AutoFit();

xlSheet.QueryTables[1].Refresh(false);

xlSheet.QueryTables[1].Delete();

xlSheet.Name = Path.GetFileNameWithoutExtension(FilePath);
}

Print document using PDFCreator with C#.NET

Sometime it is required to Generate PDF on the fly using c#.
There are lots of Printer Drivers using which you do you task.

I was in a situation where i have to generate PDF Invoice using EXCEL VSTO. I used PDFCreator to do my job.

I will show you how to print an active Excel Sheet using PDFCreator with C#.NET

First of all Install PDFCreator When i was writing this Post it, the latest version was 1.4.3

Create a windows application and Add reference to Microsoft Excel Object and COM reference to PDFCreator

Add Assembly deceleration for PDFCreator

using PDFCreator;

//Global Deceleration
clsPDFCreator _pdfcreator = null;
string parameters = "/NoProcessingAtStartup";

On form Load Method add following Code

_pdfcreator = new clsPDFCreator();
_pdfcreator.eReady += new __clsPDFCreator_eReadyEventHandler(_pdfcreator_eReady);
_pdfcreator.eError += new __clsPDFCreator_eErrorEventHandler(_pdfcreator_eError);


void _pdfcreator_eError()
{
MessageBox.Show("Error " + _pdfcreator.cError.Description);
}

void _pdfcreator_eReady()
{
//Returns path for generated PDF File
string CreatedFile = _pdfcreator.cOutputFilename;
}

private void printSheet(Excel.Worksheet xlSheet)
{
try
{
if (!_pdfcreator.cStart(parameters, false))
MessageBox.Show("Unable to start default print \"PDFCreator\"", "Print Sheet", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
{
// Set parameters for saving the generating pdf automatically to a directory.
clsPDFCreatorOptions opt = _pdfcreator.cOptions;
opt.UseAutosave = 1;// Use auto save functionality.
opt.UseAutosaveDirectory = 1;// Use directory for saving the file.

opt.AutosaveDirectory = @"C:\PDFFile"; // Name of the output directory.
opt.AutosaveFormat = 0;// Format of file is to be saved. 0 if for pdf.
opt.AutosaveFilename = "Test.pdf";// Name of the output file name.

opt.Papersize = "Letter";

_pdfcreator.cOptions = opt;
_pdfcreator.cClearCache();

_pdfcreator.cDefaultPrinter = "PDFCreator";
string defaultPrinter = _pdfcreator.cDefaultPrinter;

Excel.Application app = Globals.ThisAddIn.Application;

xlSheet.PrintOutEx(Type.Missing, Type.Missing, Type.Missing, Type.Missing, "PDFCreator", Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Wait till doc gets queued up.
while (_pdfcreator.cCountOfPrintjobs != 1) ;

// Start the printer.
_pdfcreator.cPrinterStop = false;

// Wait till all doc get converted to pdf.
while (_pdfcreator.cCountOfPrintjobs != 0) ;

// Stop the printer.
_pdfcreator.cPrinterStop = true;

}
}
catch (Exception exec)
{
MessageBox.Show(exec.Message, "Print Sheet", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
// Close the printer
_pdfcreator.cClose();
// _pdfcreator = null;
}
}

Hope this will help some one.

Thank you.

Create Proxy class using WSDL

Have you been in a situation were you are asked to call a Web Service where you just have a .wsdl generated from Web Service?

Once i was in a situation where i had to call a service with complex data type as input as well as output.
As a .NET developer we know that we can just make Web Reference to the Server URL and we can have all the underlying definition of target web service.
But what happen when it is not possible to have a live URL for the target web service.
Please find a very simple solution for the same.

you can just ask for the .wsdl file from the user/programmer of the target Web Service.

the next step is to create proxy class based on wsdl file.
wsdl command in .net framework provides you this feature where you can create proxy class for your target web service

on the underlying framework run this command

wsdl \out:[path to your new class file] [path to .wsdl file]

that it.

check for the file at path [path to your new class file]

Add that file to you project.

one thing to check and remember do not forget to change the URL property of generated proxy class.
URL should always point to the URL of your target Web Service.

Thank You

Choosing a SQLDataSource, LINQDataSource or EntityDataSource

When it comes to selecting DataSource for control data binding now there are lots of options.
You can select from
SQLDataSource
LINQDataSource and
Entity DataSource.

Each one has their own advantage and disadvantage. when i was googleing for the same a came across with a nice article, that i am sharing here.

This article looks at the implications for developers in making a choice between using one of the three different types of Data Sources in Visual Studio. We hope this information may help you in coming to a decision over using a particular type of data control. All three of the control types can be used to both directly update data in tables and update data in views (subject to certain restrictions imposed in views).

SQLDataSource

This is the oldest technology of the three and has the advantage of simplicity of use for people whom are adept at SQL. The control has separate SQL commands for inserting, deleting and updating data.
If you are not fluent in writing SQL then you will find that you need to get up to speed with SQL to make best use of the control.

SQLDataSource -Advantages

• The SQL in each command can make full use of SQL Server functions and SQL syntax, for example we use COALESCE functions to resolve locking issues when updating records.
• Everything uses standard SQL syntax.

SQLDatasource – Disadvantages

• The control cannot utilise SQL Server TIMESTAMP data for optimistic concurrency.
• Whilst the control does support concurrency conflicts by checking that old data values have not changed during a write-back this can get quite complicated and messy.
• You can find issues (for which there are well know solutions) when performing write-backs with concurrency control (compare all values) and null values. VS 2008 will automatically generate the SQL commands to avoid for these problems (we use a slightly different technique to that used by Visual Studio).
• Data Binding from controls such as the GridView to field names containing spaces or other special characters become read-only.

LINQDataSource

This data source is a step down the road in taking away a lot of the technical detail behind making the mechanics of updating data work and freeing up the developer to focus on more application related issues.
In order to use LINQ you need to either use an external program called SQLMetal (supplied with Visual Studio or a better choice is to use the Free utility SQL Metal Builder which provides a more friendly interface for running SQLMetal which is a command line program), or a manual process to create a dbml file in your VS web site. The dbml file is a roadmap of all your tables and views in the application and contains a lot of clever technology that enables LINQ to offer certain advantages over the SQLDataSource.

LINQDataSource – Advantages

• The control automatically supports SQL Server TIMESTAMP data types for concurrency control. Checking only timestamp’s and primary keys during write-backs.
• Fieldnames with spaces or special characters get aliased automatically, which allows them to be updated.
• A graphical picture in a dbml file gives an easy to use interface to the database tables and views.
• Views can be manually annotated as having a unique field that acts like a primary key allowing them to be updated (by default they are not updateable).
• All the optimistic write-back code is handled for you.
• The visual complexity of the control is significantly reduced over the SQLDataSource, as all the clever concurrency control is no longer required and the control does not require explicit SQL commands for inserting, updating and deleting data.
• Any spaces in entity names are removed; this is simply an aspect how the technology operates.

LINQDataSource – Disadvantages

• The following applies to Binding in controls on a FormView controls, but NOT ListView, GridView or DetailsView. Updating existing records which have null values in numerical or date fields fail to update. Similarly numerical and date fields can not be easily cleared of any value as this causes failures in the Binding. LINQ is not translating empty strings into null values. The only solution we have found is to ensure that all numeric and date data have default values. For existing records default values need to be supplied if they are missing.
• LINQ Where clauses do not support direct date comparisons, so a work-around is required here. The calculation (Year + Month*31 + Day) can be used for date comparisons, for a date range you need ((RequiredDate.Value.Year > OtherDate.Value.Year)||((RequiredDate.Value.Year == OtherDate.Value.Year) && (RequiredDate.Value.Month*31 + RequiredDate.Value.Day) >= (OtherDate.Value.Month*31 + OtherDate.Value.Day))).
• The dbml must be manually updated, or the complete file re-generated.
• Views require the manual addition annotation of the unique keys to support updateability.
• An external program SQLMetal (supplied) is required to automatically generate a dbml file.
• When writing where clause these are written in a C# style, and those adept at SQL but not C# have new skills to learn (this could be considered an advantage for those with C# skills).
• A view with a name like “Orders Qry” will get changed to OrdersQry, whereas a field called “Qrt 1” becomes Qrt_1. So in the case of table/view names the spaces are removed, but for field names they are replaced with an underscore. This inconsistency has been changed in the EntityDataSource where the “_” is used in both cases.
• If you are using SQL Server schemas such as for example a view called Reporting.vw_tblParameters, then SQLMetal gives this object the same name, but interactive drag and drop in Visual Studio drops of the schema prefix in our applications, making it vw_tblParameters. The different behaviour here is a little confusing.

The most significant problem in the above is the first point which relates to null values in date and numeric fields, this can be resolved by ensuring these fields always have values and updating existing data. This problem is no longer an issue with an EntityDataSource.
One other point to consider is the lack of support for the automatic updating of the dbml file. However, using manual techniques new tables and views can be quite easily dragged and dropped onto the dbml layout, and existing tables removed and added back to reflect structural changes.

EntityDataSource

The very latest choice of technology is the EntityDataSource, which extends the approach taken with the LINQDataSource by adding further capabilities.

EntityDataSource – Advantages

The newest type of Data Source has most of the advantages of LINQ and offers additional features.

• The problems with null data values in LINQDataSources do not occur here.
• The graphical schema can be automatically generated within Visual Studio, and automatically updated for underlying changes made to the database structures.
• Combinations of fields for Views are automatically identified as forming a key.
• As with LINQDataSources, TIMESTAMP concurrency is supported, and problem field names dealt with automatically.
• Any spaces in entity names are replaced with an “_” character, unlike LINQ where they were removed.
• Unlike with LINQ the like keyword can be used in a where clause.

EntityDataSource – Disadvantages

• To make Views updateable you need to write stored procedures for the insert, update and delete operations. (In some ways this is not necessarily a disadvantage, but it is inconvenient when compared against LINQ's ability to do this automatically once given the the keys.
• Views which do not have any underlying unique keys are not able to be added to the data model, in such situations for example if used with DropDownLists consider using an SQLDataSource as an alternative. NOTE this normally affects only a few views, and if the EDM can work out the underlying keys from the base tables then this is not a problem. Using a DropDownList based on a view containing for example (SELECT DISTICT Country FROM Customers) would be a problem.
• Field names in tables such as _Country, get assigned a character prefix C, becoming C_Country. LINQ did not do this and allowed field names to start with a _.
• Foreign keys are not exposed in a table, you need to refer to the other side of the relationship to access key values, for example in an Orders table with a SupplierID related to a Suppliers table, you would refer to the field as Suppliers.SupplierID. If you do not like this approach, then by using views, you can directly refer to the foreign keys.

Summary

If you don’t already have a good reason for choosing a type of datasource and you are willing to learn a little C# style syntax then our recommendation would be to use the EntityDataSource. If you are a hardened SQL fan, and want to explicitly use SQL Server specific functions outside of encapsulating them in views then the older SQLDataSource maybe a better choice.
But remember, you can also mix the use of different DataSources in your WebForms, using the most appropriate choice for the task in-hand.

Article source : http://www.upsizing.co.uk/Art37_VSDataSources.aspx
I do not claim for the authority of this article as i am sharing the whole article from the URL Provided above.

Thank You.

MSSQL Server split function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplit] (
    @sInputList VARCHAR(8000)  -- List of delimited items 
    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    )
    RETURNS @List TABLE (Offset int, item VARCHAR(8000))
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    declare @index int
    set @index = 0
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    BEGIN
        SELECT  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1)))
        , @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 
   
        IF LEN(@sItem) > 0 
            INSERT INTO @List SELECT @index,  @sItem
            set @index = @index + 1
    END
   
    IF LEN(@sInputList) > 0
        INSERT INTO @List SELECT @index, @sInputList
            set @index = @index + 1 -- Put the last item in
    RETURN
    END

This Function returns a Table with two fields.
1) Offset a numeric value you can also use that a Sequence no and
2) Item

Thank you.

Insert Data from Excel to Access Using OLEDBDataAdapter

The view of this article is to show use of dataAdapter to update Database.

string file = Application.StartupPath + @"\excel.xlsx";
            DataSet ds = ImportExcelXLS(file, true);

            //SqlConnection conn = new SqlConnection("Data Source=server;Initial Catalog=TestDB;User Id=sa;Password=#######;");
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + @"\test.accdb" + ";Persist Security Info=False;");

            try
            {
                conn.Open();

                DataSet dataSet = new DataSet();

                //SqlDataAdapter adapt = new SqlDataAdapter("select * from test where 0 = 1", conn);
                OleDbDataAdapter adapt = new OleDbDataAdapter("select * from test where 0 = 1", conn);

                //SqlCommandBuilder sqlBld = new SqlCommandBuilder(adapt);
                OleDbCommandBuilder sqlBld = new OleDbCommandBuilder(adapt);

                adapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapt.Fill(dataSet, "test");

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DataRow Newrow = dataSet.Tables["test"].NewRow();
                    Newrow.ItemArray = row.ItemArray;
                    dataSet.Tables["test"].Rows.Add(Newrow);
                }

                //sqlBld = new SqlCommandBuilder(adapt);
                sqlBld = new OleDbCommandBuilder(adapt);

                adapt.InsertCommand = sqlBld.GetInsertCommand();
                adapt.Update(dataSet, "test");

                endTime = System.DateTime.Now;

                TimeSpan result = endTime - startTime;
                string result2 = result.ToString();

                MessageBox.Show("Done\nIt took "  + result2 +" to write data");
            }
            catch(Exception exec)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show(exec.Message);  
            }
            finally
            {
                this.Cursor = Cursors.Default;
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

for ImportExcelXLS(file, true);
Please read my previous Post

Insert Data into Access Database from XML using C#

Add Reference to Microsoft Access 14.0 Object
Add Reference based on Version of Access you are Using  I am using Access 2010



 string file = Application.StartupPath + @"\excel.xlsx";
 DataSet ds = ImportExcelXLS(file, true);

 ds.WriteXml(Application.StartupPath + @"\test.xml", XmlWriteMode.WriteSchema);

 access.Application _accessData;

 _accessData = new access.Application();
 _accessData.Visible = false;
 _accessData.OpenCurrentDatabase(Application.StartupPath + @"\test.accdb");

 _accessData.ImportXML(Application.StartupPath + @"\test.xml", access.AcImportXMLOption.acStructureAndData);
  
 _accessData.CloseCurrentDatabase();
 _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
_accessData = null;

for Method ImportExcelXLS(file, true);
Please check my previous post for loading Excel Data DataSet.

The Simplest Read Excel to Dataset

Understanding Parameters
FileName: Defines path to Excel File
hasHeaders: Defines whether each column in Excel files has header included

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

            DataSet output = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();

                DataTable schemaTable = conn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();

                    if (!sheet.EndsWith("_"))
                    {
                        try
                        {
                            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                            cmd.CommandType = CommandType.Text;

                            DataTable outputTable = new DataTable(sheet);
                            output.Tables.Add(outputTable);
                            new OleDbDataAdapter(cmd).Fill(outputTable);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                        }
                    }
                }
            }
            return output;
        }

Using ASP.NET AJAX Timer Control and ASP.NET AJAX ModalPopup Dialog

Have you ever used ASP.NET AJAX and Timer Control and ModalPopup Ctonrol on a page?
If yes, Then how you Managed to Display Modalpopup when Timer Control Tick event occurs?

Many of you have faced such situation even i have faced the same.
But i used a very simple solution to handle the same.

What is did it is, Stopped the timer control when i view the ModalDialog and then restart the timer when Modal Pup is closed.

You will ask what is the trick in this.
The answer raises another question. How to handle timer control when you have set Modalpop okControlID and CancelControlID.
Because clicking this control does not raise any server event but it is handle at client side.

In order to achive you have handle timer control at client side instead on Code behind

To achieve the same add OkScript, If you just have one button to Close the Modal Popup Control and you have using through OKControlID.
In that Write below code

okscript="okTimerscript();"


function okTimerscript()
{
    var timer = $find('<%= tmrControl.ClientID
    timer.set_enabled(true);   
    Timer._startTimer();
}


you would be wondering about the methods. But, forget about this all these methods will be handled by AJAX Control toolkit Timer.js file.

Enjoy and happy coding...

Thank you.

Displaying Documents in a ASP.NET Modal Dialog Box using Google Doc Viewer

Many people have faced a situation where they have to show documents like .PDF,.Doc ,.Docx and other like images too.

For showing Images there are lots JQuery Plugins available which can accomplish these task and are really good.

But how to show real document to the user, in stead of showing them the option to download you can provide facility to your users where they can view documents without leaving your page.

Today i will show a very simple option for doing the same using good doc viewer.

you might be aware of Google Docs feature where you can store your documents and view and edit the same. But have you ever used the same viewer for viewing document that are stored inside your web directory.

Let see how we can achieve the same using ASP.NET AJAX ModalPopup Control Toolkit.

I will not provide you the ASP.NET page code but will only provide code for related Controls
Like Update Panel, Control for Loading Pupup and closing Popup and Code for Loading document for viewing.

I will provide you the CSS that i have used for display ModalPopUp

.modalbackground
{
background-color:Gray;
opacity: 0.5;
filter:Alpha(opacity=50);
}

.modalpopup
{
background-color:White;
padding:6px 6px 6px 6px;
}




Now lets have the Code behind Event for showing the Document.

I have used Iframe for showing the document in ModalPupup but i am loading the document on Request that is the reason you will find any IFrame tag in in Code posted above.

Please find below code for showing document on Show button Client Event

Done.......

Run your page and check whether you are able to load the document in popup or not..
Some points to remember is that you have access rights to the location from where you are loading the page. and do not forget to add attribute  "embed=true" otherwise it will not load in IFrame.

Thank you,

ASP.NET User Management using MySQL

Hello,

I was asked to implement ASP.NET built in User Management using MySQL. Though It was a new work for me, Because I do have that much knowledge in ASP.NET compared to Winform.

But, Google was with me. So i started exploring the same, and found lots of articles on how to implement.

I would like to explore the same here.

I assume you have already installed MYSQL Server. :)

Steps 1:

Install MYSQL Data Provider for the version of MYSQL Installed.
After you install Data provider for MySQL you will find some new values in Machine.config file related to provider. You will find one more Entry for MySQL With Name "MySQLProfileProvider".

you can locate your Machine.config file at "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\" based on your version of .NET Framework you are using

Step 2:
Time to Make changes in Web.Config File

locate ConnectionStrings Tag and make changes as specified below

[connectionStrings]
[add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/]
[remove name="LocalMySqlServer"/]
[add name="LocalMySqlServer" connectionString="server=127.0.0.1;Database=membership;uid=root;pwd=root;CHARSET=utf8;" providerName="MySql.Data.MySqlClient"/]
[/connectionStrings]


Locate to Member ship tag and do this changes for default provider

[membership defaultprovider="MySQLMembershipProvider"]... [/membership]

Inside membership add setting for MYSQL Provider

[add autogenerateschema="true" connectionstringname="LocalMySqlServer" enablepasswordretrieval="false" enablepasswordreset="true" requiresquestionandanswer="false" applicationname="/" requiresuniqueemail="false" passwordformat="Clear" maxinvalidpasswordattempts="5" minrequiredpasswordlength="7" minrequirednonalphanumericcharacters="1" passwordattemptwindow="10" passwordstrengthregularexpression="" name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"]

Here Note the values for autogenerateschema="true"

This is very important. if autogenerateschema value is set to true. It will create all the tables in MYSQL Database required for user Management.

for managing Profile and Role
do the following changes

[profile]
[providers]
[clear/]
[add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/]
[add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" /]
[/providers]

[roleManager enabled="true" defaultProvider="MySQLRoleProvider"]
[providers]
[clear /]
[add connectionStringName="ApplicationServices" applicationName="/"
name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" /]
[add applicationName="/" name="AspNetWindowsTokenRoleProvider"
type="System.Web.Security.WindowsTokenRoleProvider" /]
[add connectionStringName="LocalMySqlServer" applicationName="/"
name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /]
/providers]
[/roleManager]


That's it.

Run your web site and then check for added table in MYSQL database ...

OpenERP

Hello Everyone,
Having years of experience on .NET Technologies. I have never looked at anything related to open source. But was always impressed with DNN, being an open source CMS i never had a chance to explore it.

For last few months. I was introduce to OpenERP. A Python and PostgreSQL based open source ERP solution. I was impressed with it too and started exploring its functionality. I started dealing in OpenERP to attract new clients and was amazed to have an unexpected response in market.

Today we have four projects running on OpenERP for customization.

I really would like to invest more in OpenERP.

Back again

Hello,

I am back again with new spirit. Since two i didn't a single word, But could not stay without this.

So i am back.

during these two years i came across many technologies like silverlight, WCF , WPF and OpenERP.

So, from now you will find some interesting stuffs on this.

If you want to ask and have any articles related to this, you are welcome.

you can also visit http://www.itlabsindia.in/Askus.html for a unique service.

Thank you,
Rakesh Singh
ITLabsIndia.

C# 4.0

Hello Friends, after a very long i am writing this blog. As i was bussy with changing my current employer. Ever if you had ever visited my profile would have found that i was working with 4Colordesign.com. But, now i have left it and have joined Silver Touch Technologies Ltd. with a new designation "Programmer". This is not important.

Today we are here to introduc ourself with C# 4.0. Yes, somthing new from Microsoft.

keep reading....

Restore SQL Server Database

Backup and Restore operation on any database is a common operation. Some times it becomes very tedious when not handled properly.
There are some points that needs to be kept in mind while restoring a database. Here i am providing the code for restoring SQL Server Database from C# Code.

While restoring Database use should keep in mind that, only one user is using the Database, what i mean to say is Database should be in SINGLE_USER Mode.

you need to fire this SQL Command before Restre

ALTER DATABAES DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

this will make the Database in SINGLE_USER Mode and as soon as the Restore process is complete, it will automatically set back to MULTI_USER Mode.

Below is the Code For restoring the Database.

Make following declerations

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

if Required make reference to
Microsoft.SqlServer.Smo


Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem("BackupFile", DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = "DatabaseName";

//ConnectionString Just make the connection with the Server
//I.E. without providing Initial Catalog Value

SqlConnection sqlCon = new SqlConnection(ConnectionString);
ServerConnection connection = new ServerConnection(sqlCon);
Server sqlServer = new Server(connection);

Database db = sqlServer.Databases["DatabaseName"];
sqlRestore.Action = RestoreActionType.Database;
sqlRestore.ReplaceDatabase = true;

sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 1;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.Information += new ServerMessageEventHandler(sqlRestore_Information);

sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases["MEDICALDATABASE"];
db.SetOnline();
sqlServer.Refresh();

this Code Restore backup database.
also one thing that needs to remember is that, the Backup file must be on the Server machine where you have Installed the SQL Server.

Check yourself....

Swaping two values without using any third variable

Hello,
many times in interviews, A quesion is being asked that "How to swap two values with out using thrid variable".

We know how to swap using thrid vaiable i.e.
a=10;
b=20;
c=0;

c=a;
a=b;
b=c;

Now, if you want to swap two string variables without using third variable use

string A= "string A";
string B= "string B";

A+=b;
B = A.substring(0,A.Length-B.Length);
A = A.substring(B.Length);

in order to swap two integer values you can do the same as

int a = 10;
int b = 20;

A = A+B;
B = A-B;
A = A-B;

OR

A = A ^ B;
B = A ^ B;
A = A ^ B;

but there are exception in using the first option in swaping integer values. second will always give you the result, but sometimes first will result in over/under flow.

check it yourself.

MultiLingual (in different speaking languages) support in your .NET Application

I am supporting a project which is in market, developed by our company. All of a sudden our client appeared and asked for multi-language support for the software. i was very much confused on how to deliver that. But later i found that with the Help of VS.NET it is very much easy to deliver any application with multiple language support.

So, i decided to write this article on my blog about how to create a Multilingual application using .NET, and also what to consider while developing a Multilingual application.

selection of the database is one of the factor for saving data as user has entered. MS SQL Server is one of the option that can be used, as the developement evvironment is Windows and we are using VS i suggest SQL Server 2005 or above as a better option.
SQL Server provides to dataTypes nvarchar and nchar which can used to store data in different language format.

inorder to use the Localization in your application make reference to two namespaces

System.Threadig and System.Globalization

Globalization is the namespace that provides feature for setting the Current Cultur as per user desire.

when you go for localization you should alos consider the input language what i mean to say is that if your providing your application in french language then you should proivde provision for inputing data in french language.

inorder to provide the facility of inputing data in specific language you must have those languages installed if not you can install them. if you can install languages from
Start->Settings->Control Panel->Regional and Language options
Select Language Tab -> Select Details there you can see the list of languages installed and you can add New languages.

for my example i am checking for french language installed on my machine
foreach(InputLanguage Language in InputLanguage.InstalledInputLanguages)
{
if(Language.LayoutName=="French")
{
InputLanguage.CurrentInputLanguage = Language;
break;
}
}

Now lets create Step-by-Step Application for US and French Language
step 1. Create a Blank Windows Applciation.
step 2. Select Form1 that is your default Form, make its Localizable property to true and make its Language property to default, which is already by default.
step 3. Drag and drop a button on the form and set its Text property to "Click Me", Save the application
step 4. Select Form1 and Set it Language property to French (France)
step 5. Select button1 set its Text Property to "Cliquez-moi"
step 6. Save and build your application.
step 7. press F5 to run the application, you will find the button displaying the button1 text as "Click Me" it is because your default language is "US" you need to change the Culture. Inorder to do that, Add following code before callingInitializeComponent();

Thread.CurrentThread.CurrentUICulture = new CultureInfo("fr-FR");
and then press F5 you will see your application displayed French language.

The main magic behind is the resource file that VS studio creates when you set the Language option for your form. Go to Solution explorer and click on Show All Files you will find file named form1.fr-FR.resx double click on it. see the content VS has created mapping data for you. Whenever you change your Culture info your application will look for appropriate file. If found it will look for the actual data and will display the mapped data. If it is not found it will display the default language i.e. "US"

Get Distinct value from an ArrayList

int[] Arr = { 1, 2, 1, 24, 25, 1, 5, 5, 8, 7, 96, 3, 8, 4, 12, 45, 8, 6, 9, 5 };
ArrayList Array = new ArrayList(Arr);
Hashtable ht = new Hashtable();
foreach (int item in Array)
ht[item] = null;
ArrayList AList = new ArrayList(ht.Keys);

A Simple password generator

Here is a simple password generator.
there is nothing special in this code, i have just used the Random class in C# to generate random number and some range of character from Character Array, below code illustrate is more easily.

public string GetRandomPassword(int numChars , int seed)
{
string[] chars = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z",
"0", "1", "2" ,"3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N","O","P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y", "Z" };

Random rnd = new Random(seed);
string random = string.Empty;
for(int i = 0 ; i < numChars ; i++)
{
random += chars[rnd.Next(0 , 62)];
}
return random;
}

Call to the Method above

Random random = new Random();
string Password = GetRandomPassword(15 , random.Next(100000))); //15 character long password

Late binding in C#

There are situations where some time it is viable to go for late binding.
though it is a tedious to perform and also needs carefull overall programming.

I was working on a project where clients were continious asking for new changes and want in Auto updates and at that time found late binding better for the situation.

Though early binding is considered as better option than late binding, but late binding has its own benefits. Late bind is most of used for generic object which needs to be known a run time.

in order to illustrate i have created of class library called Calculation which contains Calculate Class. code of that is below:


using System;
using System.Collections.Generic;
using System.Text;

namespace Calculation
{
public class Calculate
{
public Calculate()
{
}

public Int64 Add(Int64 Value1 , Int64 Value2)
{
return Value1 + Value2;
}

public Int64 Multiply(Int64 Value1 , Int64 Value2)
{
return Value1 * Value2;
}
}
}


in order to use this Calculation.dll at run time don't don't make a reference to Calculation.dll. i have placed the dll on the Startup path of my test application.
code for that is listed below:
do not forget to make a reference to System.Reflection

private void button1_Click(object sender , EventArgs e)
{
Assembly assemblyinfo = Assembly.LoadFile(Application.StartupPath+@"\Calculation.dll");
Type[] types = assemblyinfo.GetTypes();

foreach(Type type in types)
{
MethodInfo[] MI = type.GetMethods(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
object Latebind = Activator.CreateInstance(type);
MessageBox.Show(MI[0].Invoke(Latebind , new object[] { Convert.ToInt64(textBox1.Text),Convert.ToInt64(textBox2.Text)}).ToString());
}
}

here MI[0] referes to Add Method.
you can check the Method order by itterating through MI object