| This small set of examples is designed to show classic ASP developers how to perform a few commonly used ADO capabilities in ADO.NET syntax. Being new to .NET, I'm sure your head is spinning just a bit at trying to figure out where to get started with ADO.NET. You are not alone... Keep in mind though, ADO.NET is considerably more powerful and flexible than ADO and you need to pick yourself up an ADO.NET book to get the most out of ADO.NET. There are a ton of new capabilities that you to learn for more complex database programming needs. |
| The code samples below are specific to SQL server but could easily be adapted for most other databases. And, of course, the .NET language of choice is C#. |
|
|
| ADO - Execute SQL Statement With No Return Value |
Set oCon = Server.CreateObject("ADODB.Connection")
oCon.CursorLocation = adUseClient
oCon.open "connection string"
oCon.Execute "my sql string or stored procedure code"
If oCon.STATE = adStateOpen Then oCon.Close
Set oCon = Nothing
|
| ADO.NET - Execute SQL Statement With No Return Value |
SqlConnection oConn = new SqlConnection();
int nRowsAffected=0;
try
{
oConn.ConnectionString = "connection string";
oConn.Open();
SqlCommand oCmd = new SqlCommand("update mytable set mycol='hello' where blah='stuff'",oConn);
// Or a stored procedure...
// SqlCommand oCmd = new SqlCommand("exec myprocedurename 'hello'",oConn);
nRowsAffected = oCmd.ExecuteNonQuery();
}
catch (Exception err) { throw err; }
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
|
| ADO - Disconnected Recordset From SQL Query |
Set oCon = Server.CreateObject("ADODB.Connection")
Set oRec = Server.CreateObject("ADODB.Recordset")
oCon.CursorLocation = adUseClient
oCon.open "connection string"
oRec.Open "select * from mytable", oCon,adOpenStatic, adLockReadOnly
Set oRec.ActiveConnection = nothing
If oRec.STATE = adStateOpen Then oRec.Close
If oCon.STATE = adStateOpen Then oCon.Close
|
| ADO.NET - Disconnected Recordset From SQL Query |
SqlConnection oConn = new SqlConnection();
DataTable oTable = new DataTable();
try
{
oConn.ConnectionString = "connection string";
oConn.Open();
SqlDataAdapter oDA = new SqlDataAdapter("select username from my table",oConn);
oDA.Fill(oTable);
oConn.Close();
}
catch (Exception e) { }
finally{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
// To Iterate Through Rows
foreach(DataRow oRow in oTable.Rows)
{
Response.Write(oRow["username"].ToString());
}
|
| ADO - Disconnected Recordset From Stored Procedure |
Set oCon = Server.CreateObject("ADODB.Connection")
Set oRec = Server.CreateObject("ADODB.Recordset")
oCon.CursorLocation = adUseClient
oCon.open "connection string"
oRec.Open "myprocedurename('varchar variable',numbervaluenoquotes)", oCon,adOpenStatic, adLockReadOnly,adCmdStoredProc
Set oRec.ActiveConnection = nothing
If oRec.STATE = adStateOpen Then oRec.Close
If oCon.STATE = adStateOpen Then oCon.Close
|
| ADO.NET - Disconnected Recordset From Stored Procedure |
SqlConnection oConn = new SqlConnection();
DataTable oTable = new DataTable();
try
{
oConn.ConnectionString = "connection string";
oConn.Open();
SqlDataAdapter oDA = new SqlDataAdapter("exec myprocedurename 'varchar variable',numbervalue",oConn);
oDA.Fill(oTable);
oConn.Close();
}
catch (Exception e) { }
finally{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
// To Iterate Through Rows
foreach(DataRow oRow in oTable.Rows)
{
Response.Write(oRow["username"].ToString());
}
|
| ADO - Get New Identity In SQL Server With ADODB.Command |
Dim sFName
Dim sLName
Dim sNewID
sFName = "George W."
sLName = "Bush"
Set oCon = Server.CreateObject("ADODB.Connection")
Set oCom = Server.CreateObject("ADODB.Command")
oCon.CursorLocation = adUseClient
oCon.open "your connection string goes here"
with oCom
.activeconnection = oCon
.commandtext = "MyProcedureToSaveRecord"
.commandtype = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@FName", adVarWChar, adParamInput, 40, sFName)
.Parameters.Append .CreateParameter("@LName", adVarWChar, adParamInput, 40, sLName)
.Parameters.Append .CreateParameter("@NewID", adInteger, adParamOutput, 0)
end with
oCom.Execute , , adExecuteNoRecords
if err.number = 0 then
Select Case trim(oCom("@RETURN_VALUE"))
Case "0": sNewID = trim(oCom("@NewID"))
Case Else: ' received an error
End Select
else
response.write err.description
end if
If oCon.STATE = adStateOpen Then oCon.Close
Set oCom = nothing
Set oCon = Nothing
|
| ADO.NET - Get New Identity In SQL Server |
SqlConnection oConn = new SqlConnection();
string sFName = "George W.";
string sLName = "Bush";
string sRet="";
string sNewID="";
try
{
oConn.ConnectionString = "connection string";
oConn.Open();
SqlCommand oCmd = new SqlCommand("dbo.MyProcedureToSaveRecord",oConn);
oCmd.CommandType=CommandType.StoredProcedure;
oCmd.Parameters.Add(new SqlParameter("@RETURN_VALUE",SqlDbType.Int,0));
oCmd.Parameters.Add(new SqlParameter("@FName",SqlDbType.NVarChar,40));
oCmd.Parameters.Add(new SqlParameter("@LName",SqlDbType.NVarChar,40));
oCmd.Parameters.Add(new SqlParameter("@NewID",SqlDbType.Int,0));
oCmd.Parameters["@FName"].Value = sFName;
oCmd.Parameters["@LName"].Value = sLName;
oCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
oCmd.Parameters["@NewID"].Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
sNewID = oCmd.Parameters["@NewID"].Value.ToString();
sRet = oCmd.Parameters["@RETURN_VALUE"].Value.ToString();
}
catch (Exception err) { throw err; }
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
|