Unicode text stream [DT_NTEXT] CONVERT TO string [DT_STR]

I am sure that most of us facing this problem, while extract data from Excel source.

As my experience, I don’t see any other alternative than two step conversion that is

Unicode text stream [DT_NTEXT] à Unicode string [DT_WSTR] à string [DT_STR]

The following images will explain the real scenario:

Create Stored Procedure with Example

Right click on programmability, and then click New Stored Procedure.

This will display the template as follow:

Then click Shift+Ctrl+M, this will pop up as window as follow:

Modify the stored procedure as you want some think like bellow: This is a simple stored procedure without parameter.

Also stored procedure can be modifying as follow:

Stored procedure with INPUT Parameter:

Execute the SP with NationalIDNumber

Stored procedure with INPUT & OUTPUT Parameter:

ADO.NET Retrieve data based on AutoPostBack Option

The following screen will be clearly explain the requirement, First Product category need to select, based on the Product Category, Product Sub Category have to filter under Product Sub Category combo box, based on the Sub Category selection, all the product should be filler and display under product. Then the based on the selected product details should be display.

Before the coding:

Final Output after the coding:

The following picture shows the AutoPostBack option for a combo box:

The following Image displays about the connection string in “config” file

The C# code as following:

Data Provider Model – Introduction to ADO.NET

ADO.NET relies on the functionality in a small set of core classes. These can divide into two groups:

1. Contain and Manage Data (DataSet, DataTable, DataRow, DataRelation, and etc.) :

The data container classes are no matter what data source you use, after extract the data, it’s stored using the same data container: the specialized DataSet Classes.

2.Connect to the specific data source (Connection, Command, DataReader and ect.):

For this group of classes exists for in several flavors. Each set of data interaction classes is called ADO.NET data provider. Data providers are customized so that each one uses the best-performing way of interacting with its data source such SQL Server data provider is designed to work with SQL server and oracle Data Provider is design for Oracle database. Also each provider designates its own prefix naming classes such as SQL Server provider includes SqlConnection and SqlCommand classes and the oracle provider includes OracleConnection and OracleCommand classes.

ADO.NET Namespaces for SQL Server Data Access

Namespace	Purpose
System.Data.SqlClient	Contains the classes that use to connect to MS SQL Server database and Execute commands ( such as SqlConnection and SqlCommand)
System.Data.SqlType	Contains structures for SQL Server–specific data types such as SqlMoney andSqlDateTime. You can use these types to work with SQL Server data types without needing to convert them into the standard .NET equivalents (such as System.Decimal and  System.DateTime). These types aren’t required, but they do allow you to avoid any potential rounding or conversion problems that could adverselyaffect data.
System.Data	Contains fundamental classes with the core ADO.NET functionality. These include DataSet andDataRelation, which allow us to manipulate structured relational data. These classes are totally independent of any specific type of database or the way you connect to it.

Simple data access steps

1. Create connection, Command, and Data Reader.

2. Use the Data Reader to retrieve the Data and use Web Controls to display the data

3. Close the connection

Here’s an example for Data Connection with SQL Server database.

1.       Create the ASP.NET project

2.       Create a connection string in the WebConfig file.

3.       Add new item -WebForm

4.       Write the connection code (Double click the Form)

5.       Add web List web control and load the data in to it.

The following code will show how to handle Error Exception.

Put the Labelfor to display the error message:

I hope this would give some basic idea about the Data Provider Model and ADO.NET.

Cheers!