ARCHIVED
We all know how to insert a SQL query using ASP.NET the inconvenient in this is that you let the end user see some of your private code and information on your application architecture.

Here I demonstrate how to use the C# in a code behind page to INSERT, SELECT or UPDATE an existing SQL database. In this example I will be using the Microsoft SQL Express Server. Also the connection string in this example is for a local server with integrated security; if you need to connect to an external Server or a MySQL Server go to connectionstring.com to get the replacement connection string code.

First off unlike asp.net insert you don’t use a connection string in the web.config, so no changes need to be done there.

Create an ASPX page with code behind.

In that page you will add 2 text boxes with a button and double click the button to create the event in the code behind then add a label so we can keep track of the status. Just to make it real easy here’s the ASPX code.

You may insert this hilly commented code in the C# but understanding it first is the best way to go about it.

//Add the following namespace

HI Friends.

IF You want to intrect any database in .NEt application . my best approach is ADO.NET . u wrote very un-professional code. Dont need worry about more Connection object .

rajeev.net@hotmail.com:

HI Friends.

IF You want to intrect any database in .NEt application . my best approach is ADO.NET . u wrote very un-professional code. Dont need worry about more Connection object .

Hello,

i dont know whether the above code is un-professional or not,since i m new to asp.net.

anyway how would the code look like using ADO.net? as u mentioned

it that it is more simple and professional.

can u demonstrate with simple example along with code (C#).

thanks.

jack.
 

Using ado.net would look like this. 
This code sample was taken in the MSDN library at http://msdn2.microsoft.com/en-us/library/ms254507.aspx.
 
C#
// Assumes connectionString is a valid connection string.
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here.
}
 
VB
' Assumes connectionString is a valid connection string.
Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Do work here.
End Using

In this code sample you are missing allot of pieces per example you are not closing the connection.
It's not reusing your existing connection ether. The code I posted uses ado.net and manages a lot of
all you do not want to think about, take your time and study it.

I've refined the code above a bit and made a Class of it, here's the code. Just create a MySqlConnection.cs file in App_Code and paste the following code. If you are using a namespace just ad it.

Hi, 

 This Class is specifically design to connect with optimized pooling to Microsoft Sql Server.

 what does it mean??

 and why did u write so long code there? the above code is just for connection open and close write?

 plus why did u save it in app_code folder? any advantage?

 thanks.

  jack.
 

The  MySqlConnection Class "Long Code" allows you to Create a connection to the database and close that connection, with pooling.

Connecting to a database is the single slowest operation performed by a data-centric application. Reusing pooled connections, instead of creating new connections, can improve .NET application performance.

You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider:

• Number of connection pools (Max Number of Pools)
• Maximum connection pool size (Max Pool Size)
• Minimum number of connections in a connection pool (Min Pool Size)
• Number of seconds to keep connections in a connection pool (Connection Lifetime)

The long code optimizes this pooling process and since it returns the connection it's able to verify if you already have an active connection.

By having the long code in his own .cs in App_Code I can use it as a class. In my C# page I can initialize it like this MySqlConnection mySql = new MySqlConnection(); and then intellisence will give me the available Method and Object for this class by typing mySql.

If you let me know how you want to use this or what you are trying to do, I will be able to give you more specific hints. 

 Hi Simon,

 Thanks for the explanation.

 since i m new to this field, i m very confused.

 there are many ways to connect to the sql database like sqldatasource(from design view),objectdatasource, ADO.net etc. 

 i  want to be familiar with just one standard method of insert,delete,update using sql and c#. 

 i m still not sure whether to use ur above code to accomplist the simple task like insert,delete,update etc.

  just little confused.

 thanks.

jack.
 

 Jack,

The huge benefit of the code I suggest above it’s that it is fast, and optimizes your connection. Also it makes sure your connection will succeed and it reuses the same connection so you don’t end up with a bunch un unused active connection to your SQL.

Because when you Open and Close a connection it stays in a kind of Sleep status where it waits to see if it will be reused, this process is probably thought to make it faster. The down side of this is if you have a total of 5 available connections to your SQL and you are using 1 and the 4 others are a Sleep your 6th connection attempt will take forever untill one of the Sleep is Disposed by SQL, you end up with a slow application even if all you code is top notch. Of course what I describe here in my knowledge only applies to Microsoft SQL 2000 and 2005, I just don’t know for the other SQL Servers.

Have you tried to implement the MySqlConnection class to one of your projects?

Simon Deshaies:

If you let me know how you want to use this or what you are trying to do, I will be able to give you more specific hints. 

 ... Hi

I hope its ok i add a sub-question in this forum..

Ive been searching for the answers ive found in this string all night, but still miss some.

Ive been able to do a lot of the things i wish for using a sqldatasource, a detailsview, grid or other controls, but i am interested in creating the most optimal connection like described in this thread.

Basically i want to design an input page, where a user creates a repair-case. The User id is successfully passed throug to this site from a customer overview page.

So i have designed a page with repairtype, problem description, received by, etc etc..It includes textboxes and dropdownlists. + a submit repair button

I understand all of the above, regarding creating a class for database connection which is optimized for pooling, but i dont know how to insert the data chosen/written in my textboxes to the table columns.

Ive seen a lot of code examples but i seem a bit confused, and im searching the right way to do it. My best guess is that im missing code in my button_click event, and a insert <insert @

But dont know how to write these. : / Any help would be appreciated..

 Lastly i wanna give a lot of credit to the forum, where a lot of my answers have been found, but still its the first time im posting, because of your posts about pooling, and connection class were so VERY helpful.

Thanks

UPDATED! 
Since I initially wrote this how to I’ve improved the integration of this class quite a bit, you may read the new how to here, and get the revised sample here.

hello

i come here to want to help from you :) i use visual basic for visual web developer

i have database.mdf and table1 in database.mdf.. table1 has user and productname.. both of them is coulumname.. both of them datatype is nchar

also i have gridview1 and sqldatasource1 and textbox1 and textbox2.. i want gridview to display items that user = textbox1.text and productname = textbox2.text

i want to use select command and conn.open and conn.close and other sqlcommands :)

could you write to me as visual basic code ?

cheers

oguzkaygun,

This is a tad unusual, I don’t write Visual Basic. You should consider learning C#, I believe it's going to be around longer. But what I can do is point you to a C# ~ VB converter http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx will allow you to convert my example to VB. In the C# example I already demonstrate how to do what you are trying to do.