Converts a able object to A recordset object.
In. net, ADO. NET is used to replace ADO for data access. However, some COM controls only support ADO and do not support ADO. net. To use this type of control, only data objects in ADO. Net can be used. For example, datatable Is A recordset In ADO (the DataSet object is essentially a set of datatable, so this article only describes datatable object conversion ).
Ideas
1. After creating A recordset object, create a field in the column corresponding to the datatable. Therefore, you need to convert the Data Type of ADO. net to the Data Type of ADO;
2. Open the recordset object, corresponding to each row in the datatable object, create a record in the recordset object, and assign values to each field.
Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Data. sqlclient;
Using ADODB;
Using system. Text. regularexpressions;
Using system. reflection;
Namespace owc
{
Public class DBA
{
Sqlconnection conn;
Sqldataadapter da;
Sqlcommand comm;
Datatable dt = new datatable ();
String connstr = "Server = localhost; database = cases20061227; uid = sa; Pwd = sa ;";
String selstr = "select * From Table1 ";
Public datatable getdata ()
{
Conn = new sqlconnection (connstr );
DA = new sqldataadapter (selstr, Conn );
Da. Fill (DT );
Return DT;
}
Public recordset getrecordset ()
{
ADODB. connectionclass connado = new connectionclass ();
ADODB. recordsetclass recordado = new recordsetclass ();
String connstr = "driver = {SQL Server}; server = localhost; uid = sa; Pwd = sa; database = cases20061227 ";
Connado. Open (connstr, "", "",-1 );
Recordado. activeconnection = connado;
Recordado. cursortype = ADODB. cursortypeenum. adopenstatic;
Recordado. cursorlocation = ADODB. cursorlocationenum. aduseclient;
String strsql = "select * From Table1 ";
Recordado. Open (strsql, connado, ADODB. cursortypeenum. adopenstatic, ADODB. locktypeenum. adlockbatchoptimistic, 1 );
Return recordado;
}
// Missing namespace using system. reflection;
Public recordset convertdatatabletorecordset (datatable table)
{
Recordset rs = new recordsetclass ();
Foreach (datacolumn DC in table. columns)
{
Rs. Fields. _ append (DC. columnname, getdatatype (DC. datatype),-1, fieldattributeenum. adfldisnullable );
}
Rs. Open (missing. Value, missing. Value, cursortypeenum. adopenunspecified, locktypeenum. adlockunspecified,-1 );
Foreach (datarow DR in table. Rows)
{
Rs. addnew (missing. Value, missing. Value); object O;
For (INT I = 0; I <Table. Columns. Count; I ++)
{
Rs. Fields [I]. value = Dr [I];
O = Rs. Fields [I]. value;
}
}
Return Rs;
}
Public static datatypeenum getdatatype (type datatype)
{
Switch (datatype. tostring ())
{
Case "system. boolean": Return datatypeenum. adboolean;
Case "system. Byte": Return datatypeenum. adunsignedtinyint;
Case "system. Char": Return datatypeenum. adchar;
Case "system. datetime": Return datatypeenum. addate;
Case "system. Decimal": Return datatypeenum. addecimal;
Case "system. Double": Return datatypeenum. addouble;
Case "system. int16": Return datatypeenum. adsmallint;
Case "system. int32": Return datatypeenum. adinteger;
Case "system. int64": Return datatypeenum. adbigint;
Case "system. sbyte": Return datatypeenum. adtinyint;
Case "system. Single": Return datatypeenum. adsingle;
Case "system. String": Return datatypeenum. advarchar;
Case "system. uint16": Return datatypeenum. adunsignedsmallint;
Case "system. uint32": Return datatypeenum. adunsignedint;
Case "system. uint64": Return datatypeenum. adunsignedbigint;
Default: Throw new exception ("no corresponding data type ");
}
}
}
}
Note:
1. You need to add a reference to the ADODB database, which is located on the com page and named "Microsoft ActiveX Data Objects 2.0 library ";
2. C # syntax does not contain default parameters. You can only use the value attribute of system. reflection. Missing. However, this attribute can only be used for reference types, but not for enumeration and integer equivalent types.