Update database using DataSet and SqlDataAdapter

Here we are going to see how to update database using DataSet and SqlDataAdapter.

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

namespace UpdateDatabase

{

public class TestDataSet : System.Windows.Forms.Form

{

private System.Windows.Forms.DataGrid dataGrid1;

private System.Windows.Forms.Button btnLoad;

private System.Windows.Forms.Button btnUpdate;

private DataSet ds;

private SqlDataAdapter adap;

private SqlConnection con;

private System.Windows.Forms.Label lblTitle;

private System.Windows.Forms.TextBox txtTitle;

private System.ComponentModel.Container components = null;

public TestDataSet()

{

InitializeComponent();

con = new SqlConnection("server=.;uid=sa;pwd=test;database=test");}

///

/// Clean up any resources being used.

///

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.dataGrid1 = new System.Windows.Forms.DataGrid();

this.btnUpdate = new System.Windows.Forms.Button();

this.btnLoad = new System.Windows.Forms.Button();

this.txtTitle = new System.Windows.Forms.TextBox();

this.lblTitle = new System.Windows.Forms.Label();

((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();

this.SuspendLayout();

//

// dataGrid1

//

this.dataGrid1.DataMember = "";

this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;

this.dataGrid1.Location = new System.Drawing.Point(16, 80);

this.dataGrid1.Name = "dataGrid1";

this.dataGrid1.Size = new System.Drawing.Size(568, 280);

this.dataGrid1.TabIndex = 0;

//

// btnUpdate

//

this.btnUpdate.Location = new System.Drawing.Point(176, 369);

this.btnUpdate.Name = "btnUpdate";

this.btnUpdate.Size = new System.Drawing.Size(112, 23);

this.btnUpdate.TabIndex = 1;

this.btnUpdate.Text = "Update Title";

this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);

//

// btnLoad

//

this.btnLoad.Location = new System.Drawing.Point(504, 56);

this.btnLoad.Name = "btnLoad";

this.btnLoad.TabIndex = 2;

this.btnLoad.Text = "Load";

this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);

//

// txtTitle

//

this.txtTitle.Location = new System.Drawing.Point(64, 372);

this.txtTitle.Name = "txtTitle";

this.txtTitle.TabIndex = 3;

this.txtTitle.Text = "";

//

// lblTitle

//

this.lblTitle.AutoSize = true;

this.lblTitle.Location = new System.Drawing.Point(16, 376);

this.lblTitle.Name = "lblTitle";

this.lblTitle.Size = new System.Drawing.Size(26, 16);

this.lblTitle.TabIndex = 4;

this.lblTitle.Text = "Title";

//

// TestDataSet

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(616, 438);

this.Controls.Add(this.lblTitle);

this.Controls.Add(this.txtTitle);

this.Controls.Add(this.btnLoad);

this.Controls.Add(this.btnUpdate);

this.Controls.Add(this.dataGrid1);

this.Name = "TestDataSet";

this.Text = "Data";

((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

this.ResumeLayout(false);

}

#endregion

///

/// The main entry point for the application.

///

[STAThread]

static void Main()

{

Application.Run(new TestDataSet());

}

private void btnLoad_Click(object sender, System.EventArgs e)

{

LoadData();

}

//Load data from database make sure we are fetching primarykey too so we could use adapter

update method

//on dataset because commandbuilder will create command on that primary key.

//Commands will be created as it see rowstate of dataset table's row's rowstate.

private void LoadData()

{

if(ds != null)

ds.Clear();

adap = new SqlDataAdapter("select id,title, description from testtable", con);

ds = new DataSet();

adap.Fill(ds);

dataGrid1.DataSource = ds.Tables[0];

}

//This click will update one of the field in the database using adapter update() method on

dataset.

private void btnUpdate_Click(object sender, System.EventArgs e)

{

SqlCommandBuilder com = new SqlCommandBuilder(adap);

foreach(DataRow dr in ds.Tables[0].Rows)

dr["title"] = txtTitle.Text;

adap.Update(ds);

}

}

}

Pass Array to a Stored Procedure

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.

CREATE PROCEDURE [dbo].[GetData]

@MyCodes as varchar(500) = '', -- comma delimited list of codes, ie: '''ABC'', ''DEF'', ''GHI'''

AS

BEGIN

DECLARE @query as nvarchar(500)

set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

exec SP_EXECUTESQL @query,

N'@p_MyCodes varchar(500)',

@p_MyCodes = @MyCodes

END

The above stored procedure definition will accept a comma-delimited string, which we process as an array using the SQL IN command. Note, we had to use dyanmic SQL to properly form the query (which involves expanding the comma-delimited string).

Simple Method to Pass Array to a Stored Procedure - C# .NET Side

Next, we need to define the method to pass the data and execute the stored procedure from C# .NET.

The first step is to convert our array of data into a comma-delimited string, which is what the stored procedure expects to receive. Depending on your data type, this code may vary. For this example, we are using a .NET collection.

string myCodes = string.Empty; // Initialize a string to hold the comma-delimited data as empty

foreach (MyItem item in MyCollection)

{

if (myCodes.Length > 0)

{

myCodes += ", "; // Add a comma if data already exists

}

myCodes += "'" + item.Name + "'";

}

The code above will create a string in the following format:

'One','Two','Three'

Now that the collection has been converted to a string, we can pass the value as a parameter to the stored procedure by using the following code:

using System;

using System.Data;

using System.Data.SqlClient;

SqlConnection MyConnection = null;

SqlDataReader MyReader = null;

try

{

// Create the SQL connection.

MyConnection = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))

MyConnection.Open();

// Create the stored procedure command.

SqlCommand MyCommand = new SqlCommand("GetData", MyConnection);

// Set the command type property.

MyCommand.CommandType = CommandType.StoredProcedure;

// Pass the string (array) into the stored procedure.

MyCommand.Parameters.Add(new SqlParameter("@MyCodes", myCodes));

// Execute the command

MyReader = MyCommand.ExecuteReader();

// ...

}

catch (Exception excep)

{

}

finally

{

if (MyReader != null)

{

MyReader.Close();

MyReader.Dispose();

}

if (MyConnection != null)

{

MyConnection.Close();

MyConnection.Dispose();

}

}

Export SQL data to CSV/TXT/XLS

The BCP command is used to export sql server table/view to files(Text, Csv and Excel). But the BCP command doesn't product the header (Column Names).

1) To export data to new CSV file with heading(column names), create the following procedure:

CREATE PROCEDURE dbo.proc_generate_csv

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

) AS

Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),

@x varchar(300)

--Generate column names as a recordset

Select @Headers = IsNull(@Headers + ',', '') + Column_Name

From INFORMATION_SCHEMA.COLUMNS

Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

print @Headers

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -C RAW -t "," -r \n'

print @sql

exec master..xp_cmdshell @sql

set @sql = 'exec master..xp_cmdshell ' + @sql

print @sql

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

print @data_file

set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -C RAW -t "," -r \n'

print @sql

exec master..xp_cmdshell @sql

--Copy dummy file to passed CSV file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

print @sql

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

print @sql

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_csv 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_csv 'Northwind', 'Products','C:\Products.csv'

2) To export data to new Excel file with heading(column names), create the following procedure:

CREATE procedure proc_generate_excel

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select

@columns=coalesce(@columns+',','')+column_name

from

information_schema.columns

where

table_name=@table_name

select @columns = '''''' + replace(@columns,',',''''',''''') + ''''''

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file

set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@file_name+'" -c'''

exec(@sql)

--Generate data in the dummy file

set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''

exec(@sql)

--Copy dummy file to passed EXCEL file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_excel 'Northwind', 'Products','C:\Products.xls'

3) To export data to new Text file with heading(column names), create the following procedure:

CREATE PROCEDURE dbo.proc_generate_txt

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

) AS

Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),

@x varchar(300)

--Generate column names as a recordset

Select @Headers = IsNull(@Headers + CHAR(9) ,'') + Column_Name

From INFORMATION_SCHEMA.COLUMNS

Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

print @Headers

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -t'

print @sql

exec master..xp_cmdshell @sql

set @sql = 'exec master..xp_cmdshell ' + @sql

print @sql

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

print @data_file

set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -t"\t"'

print @sql

exec master..xp_cmdshell @sql

--Copy dummy file to passed txt file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

print @sql

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

print @sql

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_txt 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_txt 'Northwind', 'Products','C:\Products.txt'

Import Excel Spreadsheet Data into SQL Server Database Table

Let's take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of nonsense data broken into 2 columns, ID and Data.

I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema.

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

// Connection String to Excel Workbook

string excelConnectionString = @"Provider=Microsoft
   .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
   Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook 

using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

{

    OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

    connection.Open();

    // Create DbDataReader to Data Worksheet

    using (DbDataReader dr = command.ExecuteReader())

    {

        // SQL Server Connection String

        string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

        // Bulk Copy to SQL Server

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

        {

            bulkCopy.DestinationTableName = "ExcelData";

            bulkCopy.WriteToServer(dr);

        }

    }

}

ASP.NET Interview Question

1. Describe the role of inetinfo.exe, aspnet_isapi.dll andaspnet_wp.exe in the page loading process.
inetinfo.exe is theMicrosoft IIS server running, handling ASP.NET requests among other things.When an ASP.NET request is received (usually a file with .aspx extension), the ISAPI filter aspnet_isapi.dll takes care of it by passing the request tothe actual worker process aspnet_wp.exe.

2. What’s the difference between Response.Write() andResponse.Output.Write()?
Response.Output.Write() allows you to write formatted output.


3. What methods are fired during the page load?
Init() - when the page is instantiated
Load() - when the page is loaded into server memory
PreRender() - the brief moment before the page is displayed to the user as HTML
Unload() - when page finishes loading.

4. When during the page processing cycle is ViewState available?
After the Init() and before the Page_Load(), or OnLoad() for a control.

5. What namespace does the Web page belong in the .NET Framework class hierarchy?
System.Web.UI.Page

6. Where do you store the information about the user’s locale?
System.Web.UI.Page.Culture

7. What’s the difference between Codebehind="MyCode.aspx.cs" andSrc="MyCode.aspx.cs"?
CodeBehind is relevant to Visual Studio.NET only.


8. What’s a bubbled event?
When you have a complex control, like DataGrid, writing an event processing routine for each object (cell, button, row, etc.) is quite tedious. The controls can bubble up their eventhandlers, allowing the main DataGrid event handler to take care of its constituents.

9. Suppose you want a certain ASP.NET function executed on MouseOver for a certain button. Where do you add an event handler?
Add an OnMouseOver attribute to the button. Example: btnSubmit.Attributes.Add("onmouseover","someClientCodeHere();");

10. What data types do the RangeValidator control support?
Integer, String, and Date.

11. Explain the differences between Server-side and Client-side code?
Server-side code executes on the server. Client-side code executes in the client's browser.


12. What type of code (server or client) is found in a Code-Behind class?
The answer is server-side code since code-behind is executed on the server. However, during the code-behind's execution on the server, it can render client-side code such as JavaScript to be processed in the clients browser. But just to be clear, code-behind executes on the server, thus making it server-side code.

13. Should user input data validation occur server-side or client-side? Why?
All user input data validation should occur on the server at a minimum. Additionally, client-side validation can be performed where deemed appropriate and feasable to provide a richer, more responsive experience for the user.


14. What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other?
Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server. Server.Transfer does not update the clients url history list or current url. Response.Redirect is used to redirect the user's browser to another page or site. This performas a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.


15. Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
Valid answers are:
· A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
· A DataSet is designed to work without any continuing connection to the original data source.
· Data in a DataSet is bulk-loaded, rather than being loaded on demand.
· There's no concept of cursor types in a DataSet.
· DataSets have no current record pointer You can use For Each loops to move through the data.
· You can store many edits in a DataSet, and write them to the original data source in a single operation.
· Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.


16. What is the Global.asax used for?
The Global.asax (including the Global.asax.cs file) is used to implement application and session level events.

17. What are the Application_Start and Session_Start subroutines used for?
This is where you can set the specific variables for the Application and Session objects.

18. Can you explain what inheritance is and an example of when you might use it?
When you want to inherit (use the functionality of) another class. Example: With a base class named Employee, a Manager class could be derived from the Employee base class.

19. Whats an assembly?
Assemblies are the building blocks of the .NET framework. Overview of assemblies from MSDN


20. Describe the difference between inline and code behind.
Inline code written along side the html in a page. Code-behind is code written in a separate file and referenced by the .aspx page.

21. Explain what a diffgram is, and a good use for one?
The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. A good use is reading database data to an XML file to be sent to a Web Service.

22. Whats MSIL, and why should my developers need an appreciation of it if at all?
MSIL is the Microsoft Intermediate Language. All .NET compatible languages will get converted to MSIL. MSIL also allows the .NET Framework to JIT compile the assembly on the installed computer.

23. Which method do you invoke on the DataAdapter control to load your generated dataset with data?
The Fill() method.


24. Can you edit data in the Repeater control?
No, it just reads the information from its data source.

25. Which template must you provide, in order to display data in a Repeater control?
ItemTemplate.

26. How can you provide an alternating color scheme in a Repeater control?
Use the AlternatingItemTemplate.

27. What property must you set, and what method must you call in your code, in order to bind the data from a data source to the Repeater control?
You must set the DataSource property and call the DataBind method.

28. What base class do all Web Forms inherit from?
The Page class.


29. Name two properties common in every validation control?
ControlToValidate property and Text property.

30. Which property on a Combo Box do you set with a column name, prior to setting the DataSource, to display data in the combo box?
DataTextField property.

31. Which control would you use if you needed to make sure the values in two different controls matched?
CompareValidator control.

32. How many classes can a single .NET DLL contain?
It can contain many classes.


Web Service Questions


1. What is the transport protocol you use to call a Web service?
SOAP (Simple Object Access Protocol) is the preferred protocol.


2. True or False: A Web service can only be written in .NET?
False

3. What does WSDL stand for?
Web Services Description Language.

4. Where on the Internet would you look for Web services?
http://www.uddi.org

5. True or False: To test a Web service you must create a Windows application or Web application to consume this service?
False, the web service comes with a test page and it provides HTTP-GET method to test.


State Management Questions


1. What is ViewState?
ViewState allows the state of objects (serializable) to be stored in a hidden field on the page. ViewState is transported to the client and back to the server, and is not stored on the server or any other external source. ViewState is used the retain the state of server-side objects between postabacks.


2. What is the lifespan for items stored in ViewState?
Item stored in ViewState exist for the life of the current page. This includes postbacks (to the same page).

3. What does the "EnableViewState" property do? Why would I want it on or off?
It allows the page to save the users input on a form across postbacks. It saves the server-side values for a given control into ViewState, which is stored as a hidden value on the page before sending the page to the clients browser. When the page is posted back to the server the server control is recreated with the state stored in viewstate.

4. What are the different types of Session state management options available with ASP.NET?
ASP.NET provides In-Process and Out-of-Process state management. In-Process stores the session in memory on the web server. This requires the a "sticky-server" (or no load-balancing) so that the user is always reconnected to the same web server. Out-of-Process Session state management stores data in an external data source. The external data source may be either a SQL Server or a State Server service. Out-of-Process state management requires that all objects stored in session are serializable.

Add Image to Word document using C# code

Hi everyone,
i'd like to add an image/logo to word document automatically via c# code.
i used with Microsoft.Office.Interop.Word.Document object to open the word documents,
and then i used the Shapes.AddPicture method for inserting the image into the documents.

(I didn't use the InlineShapes.AddPicture method because, these method doesn't support layers etc.)

here is my code:
object FileName = filesCollection.ToString();
object saveFileName = FileName;
object vk_read_only = false;
object vk_visibile = true;
object vk_false = false;
object vk_true = true;
object vk_dynamic = 2;
object vk_missing = System.Reflection.Missing.Value;
object LinkToFile = true;
object myRange = System.Reflection.Missing.Value;
object vk_format = Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatDocument;
object Left = 1;
object Top = 1;
object Width = 498;
object Height = 89;

oWord.Visible = true;
oWord.Activate();

Microsoft.Office.Interop.Word.Document doc = oWord.Documents.Open(ref FileName, ref vk_false, ref vk_read_only,
ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing,
ref vk_visibile, ref vk_true, ref vk_missing, ref vk_missing, ref vk_missing);
doc.Shapes.AddPicture(LogoPath, ref LinkToFile, ref saveWithObject, ref Left, ref Top, ref Width, ref Height, ref vk_missing);
doc.SaveAs(ref FileName, ref vk_format, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing,
ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing, ref vk_missing,
ref vk_missing, ref vk_missing, ref vk_missing);
doc.Close(ref SaveChanges, ref OriginalFormat, ref saveWithObject);

Get IP Address by hostname from Domain name server in C#

This code is in c# to retrieve IP address corresponding to any hostname from Domain name server


string hostname = "www.dotnetspider.com";
IPAddress[] addresslist = Dns.GetHostAddresses(hostname);

foreach (IPAddress theaddress in addresslist)
{
Response.Write(theaddress.ToString());
Response.Write("NewLine");
}


in the second response we have a line brake. age is not visible so i am placing "NewLine" there.

don't forget to use

using System.Net;

Session Variables vs. Hidden Inputs

I often see people asking about how to make data persist across multiple pages of forms, and the two options that inevitably come up are using session variables and using hidden form inputs. Using session variable refers to the practice of saving your form variables as session variables, then accessing them all at once from the session scope at the appropriate time. Using hidden input refers to the practice of accumulating data from previous form in hidden inputs so that it looks to your action page or component that the user just submitted one big form (which, in fact, they technically did).

There's nothing wrong with using hidden inputs, but using session variables is really the better solution. For one thing, it makes the code more versatile and gives you the flexibility to make your application less linear if you want to, or to more easily rearrange your forms. Additionally, the code is much more maintainable and easier to follow.

Some people use the argument of memory usage to suggest that hidden inputs are better since session variables are stored in your server's RAM. Unless you are running ColdFusion off an Apple Newton, RAM is not going to be an issue. Let's prove the point by doing the numbers:

Let's say that a fully filled out form is about 500 bytes of data, or to make the math a little cleaner, let's say 512 bytes, which means that for every kilobyte of RAM allocated to your JVM, you can handle two simultaneous sessions. That means to use a single megabyte of RAM, you would have to have 2048 simultaneous sessions. Admittedly, session data structures themselves (Hashtables) use RAM, so we're not just talking about the bytes of form data, but considering the fact that I have 512MB of RAM allocated to the JVM on my production server, I don't worry much about these details.

If you are concerned about RAM -- if your application use huge sessions and your servers are 10 years old with tiny amounts of memory -- just buy another stick of RAM. In the long run (and maybe even the short run), it will be much cheaper than avoiding session variables.

Send email using smtp object in c#

using System.Web.Mail;

protected void SendEMail(string MailTo, string MailFrom, string MailSubject, string MailBody)
{
MailMessage objEmail = new MailMessage();
objEmail.To = MailTo;
objEmail.From = MailFrom;
objEmail.Subject = MailSubject;
MailBody = MailBody.Replace("\r\n", "");
MailBody = MailBody.Replace("\t", " ");
objEmail.Body = MailBody;
objEmail.Priority = MailPriority.High;
objEmail.BodyFormat = MailFormat.Html;

try
{
SmtpMail.Send(objEmail);
}
catch (Exception exc)
{
Response.Write("Send failure: " + exc.ToString());
}
}

Use of SQL Stored Procedure inADO.NET code.

Here i am share an code snippet for using SQL Stored Procedure in ADO.NET code.

I write and used this code in my shopping cart application.

 public static Product GetAProduct(int productid)

{

    using (SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))

    {

        using (SqlCommand command = new
SqlCommand("GetAProduct", connection))

        {

            command.CommandType =
CommandType.StoredProcedure;

           
command.Parameters.Add(new SqlParameter("@ProductID", productid));

            connection.Open();
Product temp;

            using (SqlDataReader
reader = command.ExecuteReader())

            {

               
reader.Read();

               
temp = new Product( (int)reader["ProductID"],

                                                
(int)reader["CategoryID"],

                                                
(int)reader["ManufacturerID"],

                                                
(string)reader["Name"],

                                                
(int)reader["Weight"],

                                                
(int)reader["Price"],

                                                
(string)reader["Description"]);

            }

            return temp;

        }

    }

}

All the code is same is we use in simple SQL query only difference is that we
set sql command type to StoredProcedure in statement below command.



CommandType = CommandType.StoredProcedure;



one mere thing to be noted is that Connection string is retrieved from .config
file of the project in very first line of the code. As I have user Stored
Procedure named GetAProducts, we should write code for this stored procedurt in
SQL database Stored Procedure. which is as follows.

ALTER PROCEDURE GetAProduct

    @ProductID int


AS

    SELECT * FROM [Products] WHERE [Products].[ProductID] = @ProductID

RETURN

In the code snippet above first CREATE keyword is used in place of ALTER to
create the stored procedure, as soon as we save this stored procedure it is
converted to ALTER. If we want to make any change in this Stored Procedure then
this ALTER keyword is used as it is.

Than you.

Happy Programming.