///<summary>
/// Execute a stocked procedure.
/// <param name="schema">
/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns>
/// </summary>
public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
{
DataTable table = null;
if (schema == null)
{
throw new System.ArgumentNullException("schema");
}
ADODB.Recordset record = null;
ADODB.Command command = new ADODB.Command();
command.ActiveConnection = this.pADOConnection;
ADODB.Parameter para = null;
command.CommandText = schema.Name;
command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
if (parameters != null)
{
foreach (SharpQueryParameter classParam in parameters)
{
para = new ADODB.Parameter();
para.Type = DbTypeToDataType(classParam.DataType);
para.Direction = ParamDirectionToADODirection(classParam.Type);
para.Name = classParam.Name;
if (para.Name.StartsWith("["))
{
para.Name = para.Name.Remove(0, 1);
}
if (para.Name.EndsWith("]"))
{
para.Name = para.Name.Remove(para.Name.Length - 1, 1);
}
para.Value = classParam.Value;
command.Parameters.Append(para);
}
}
this.pADOConnection.BeginTrans();
try
{
record = (ADODB.Recordset)command.GetType().InvokeMember(
"Execute",
System.Reflection.BindingFlags.InvokeMethod,
null,
command,
null);
//record.MaxRecords = rows;
table = RecordSetToDataTable(record);
//Procedure is ReadOnly
table.DefaultView.AllowDelete = false;
table.DefaultView.AllowEdit = false;
table.DefaultView.AllowNew = false;
}
catch (System.Exception e)
{
if (schema != null)
{
this.pADOConnection.RollbackTrans();
string mes = schema.Name + "\n\r";
foreach (ADODB.Error err in this.pADOConnection.Errors)
{
mes += "-----------------\n\r";
mes += err.Description + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteProcedureException(mes);
}
else
{
throw new ExecuteProcedureException(e.Message);
}
}
this.pADOConnection.CommitTrans();
return table;
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// Connect to the data source.
//
Console.WriteLine("Connecting to the database ...");
// Get the connection string from App.config. (The data source is created in the
// sample SQLServer2005DB)
string connStr = ConfigurationManager.ConnectionStrings["SQLServer2005DB"].
ConnectionString;
// Open the connection
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// Build and Execute an ADO Command.
// It can be a SQL statement (SELECT/UPDATE/INSERT/DELETE), or a stored
// procedure call. Here is the sample of an INSERT command.
//
Console.WriteLine("Inserting a record to the Person table...");
// 1. Create a command object
ADODB.Command cmdInsert = new ADODB.Command();
// 2. Assign the connection to the command
cmdInsert.ActiveConnection = conn;
// 3. Set the command text
// SQL statement or the name of the stored procedure
cmdInsert.CommandText = "INSERT INTO Person(LastName, FirstName, EnrollmentDate, Picture)"
+ " VALUES (?, ?, ?, ?)";
// 4. Set the command type
// ADODB.CommandTypeEnum.adCmdText for oridinary SQL statements;
// ADODB.CommandTypeEnum.adCmdStoredProc for stored procedures.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. Append the parameters
// Append the parameter for LastName (nvarchar(50)
ADODB.Parameter paramLN = cmdInsert.CreateParameter(
"LastName", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(50))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
50, // Max size of value in bytes
"Zhang"); // Parameter value
cmdInsert.Parameters.Append(paramLN);
// Append the parameter for FirstName (nvarchar(50))
ADODB.Parameter paramFN = cmdInsert.CreateParameter(
"FirstName", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(50))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
50, // Max size of value in bytes
"Rongchun"); // Parameter value
cmdInsert.Parameters.Append(paramFN);
// Append the parameter for EnrollmentDate (datetime)
ADODB.Parameter paramED = cmdInsert.CreateParameter(
"EnrollmentDate", // Parameter name
ADODB.DataTypeEnum.adDate, // Parameter type (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
-1, // Max size (ignored for datetime)
DateTime.Now); // Parameter value
cmdInsert.Parameters.Append(paramED);
// Append the parameter for Picture (image)
// Read the image file into a safe array of bytes
Byte[] bImage = ReadImage(@"MSDN.jpg");
ADODB.Parameter paramImage = cmdInsert.CreateParameter(
"Picture", // Parameter name
ADODB.DataTypeEnum.adLongVarBinary, // Parameter type (Image)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
bImage != null ? bImage.Length : 1, // Max size of value in bytes
bImage); // Parameter value
cmdInsert.Parameters.Append(paramImage);
// 6. Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// Use the Recordset Object.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset represents the entire set of records from a base table or the
// results of an executed command. At any time, the Recordset object refers to
// only a single record within the set as the current record.
//
Console.WriteLine("Enumerating the records in the Person table");
// 1. Create a Recordset object
rs = new ADODB.Recordset();
// 2. Open the Recordset object
string strSelectCmd = "SELECT * FROM Person"; // WHERE ...
rs.Open(strSelectCmd, // SQL statement / table,view name /
// stored procedure call / file name
conn, // Connection / connection string
ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)
ADODB.LockTypeEnum.adLockOptimistic, // Lock type. (locking records only
// when you call the Update method.
(int)ADODB.CommandTypeEnum.adCmdText); // Evaluate the first parameter as
// a SQL command or stored procedure.
// 3. Enumerate the records by moving the cursor forward
rs.MoveFirst(); // Move to the first record in the Recordset
while (!rs.EOF)
{
int nPersonId = (int)rs.Fields["PersonID"].Value;
// When dumping a SQL-Nullable field in the table, need to test it for
// DBNull.Value.
string strFirstName = (rs.Fields["FirstName"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["FirstName"].Value.ToString();
string strLastName = (rs.Fields["LastName"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["LastName"].Value.ToString();
Console.WriteLine("{0}\t{1} {2}", nPersonId, strFirstName, strLastName);
// Update the current record while enumerating the Recordset.
//rs.Fields["XXXX"].Value = XXXX
//rs.Update(); [-or-] rs.UpdateBatch(); outside the loop.
rs.MoveNext(); // Move to the next record
}
}
catch (Exception ex)
{
Console.WriteLine("The application throws the error: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("Description: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// Clean up objects before exit.
//
Console.WriteLine("Closing the connections ...");
// Close the record set if it is open
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// Close the connection to the database if it is open
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// Connect to the data source.
//
Console.WriteLine("Connecting to the database ...");
// Get the connection string
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// Open the connection
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// Build and Execute an ADO Command.
// It can be a SQL statement (SELECT/UPDATE/INSERT/DELETE), or a stored
// procedure call. Here is the sample of an INSERT command.
//
Console.WriteLine("Inserting a record to the CountryRegion table...");
// 1. Create a command object
ADODB.Command cmdInsert = new ADODB.Command();
// 2. Assign the connection to the command
cmdInsert.ActiveConnection = conn;
// 3. Set the command text
// SQL statement or the name of the stored procedure
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. Set the command type
// ADODB.CommandTypeEnum.adCmdText for oridinary SQL statements;
// ADODB.CommandTypeEnum.adCmdStoredProc for stored procedures.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. Append the parameters
// Append the parameter for CountryRegionCode (nvarchar(20)
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
20, // Max size of value in bytes
"ZZ" + DateTime.Now.Millisecond); // Parameter value
cmdInsert.Parameters.Append(paramCode);
// Append the parameter for Name (nvarchar(200))
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
200, // Max size of value in bytes
"Test Region Name"); // Parameter value
cmdInsert.Parameters.Append(paramName);
// Append the parameter for ModifiedDate (datetime)
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // Parameter name
ADODB.DataTypeEnum.adDate, // Parameter type (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
-1, // Max size (ignored for datetime)
DateTime.Now); // Parameter value
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// Use the Recordset Object.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset represents the entire set of records from a base table or the
// results of an executed command. At any time, the Recordset object refers to
// only a single record within the set as the current record.
//
Console.WriteLine("Enumerating the records in the CountryRegion table");
// 1. Create a Recordset object
rs = new ADODB.Recordset();
// 2. Open the Recordset object
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL statement / table,view name /
// stored procedure call / file name
conn, // Connection / connection string
ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)
ADODB.LockTypeEnum.adLockOptimistic, // Lock type. (locking records only
// when you call the Update method.
(int)ADODB.CommandTypeEnum.adCmdText); // Evaluate the first parameter as
// a SQL command or stored procedure.
// 3. Enumerate the records by moving the cursor forward
// Move to the first record in the Recordset
rs.MoveFirst();
while (!rs.EOF)
{
// When dumping a SQL-Nullable field in the table, need to test it for DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// Move to the next record
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("The application throws the error: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("Description: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// Clean up objects before exit.
//
Console.WriteLine("Closing the connections ...");
// Close the record set if it is open
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// Close the connection to the database if it is open
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}
///<summary>
/// Execute a stocked procedure.
/// <param name="schema">
/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns>
/// </summary>
public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
{
DataTable table = null;
if (schema == null)
{
throw new System.ArgumentNullException("schema");
}
ADODB.Recordset record = null;
ADODB.Command command = new ADODB.Command();
command.ActiveConnection = this.pADOConnection;
ADODB.Parameter para = null;
command.CommandText = schema.Name;
command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
if (parameters != null)
{
foreach (SharpQueryParameter classParam in parameters)
{
para = new ADODB.Parameter();
para.Type = DbTypeToDataType(classParam.DataType);
para.Direction = ParamDirectionToADODirection(classParam.Type);
para.Name = classParam.Name;
if (para.Name.StartsWith("["))
{
para.Name = para.Name.Remove(0, 1);
}
if (para.Name.EndsWith("]"))
{
para.Name = para.Name.Remove(para.Name.Length - 1, 1);
}
para.Value = classParam.Value;
command.Parameters.Append(para);
}
}
this.pADOConnection.BeginTrans();
try
{
record = (ADODB.Recordset)command.GetType().InvokeMember(
"Execute",
System.Reflection.BindingFlags.InvokeMethod,
null,
command,
null);
//record.MaxRecords = rows;
table = RecordSetToDataTable(record);
//Procedure is ReadOnly
table.DefaultView.AllowDelete = false;
table.DefaultView.AllowEdit = false;
table.DefaultView.AllowNew = false;
}
catch (System.Exception e)
{
if (schema != null)
{
this.pADOConnection.RollbackTrans();
string mes = schema.Name + "\n\r";
foreach (ADODB.Error err in this.pADOConnection.Errors)
{
mes += "-----------------\n\r";
mes += err.Description + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteProcedureException(mes);
}
else
{
throw new ExecuteProcedureException(e.Message);
}
}
this.pADOConnection.CommitTrans();
return(table);
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// 连接数据源.
//
Console.WriteLine("正在连接数据库 ...");
// 获取连接字符串
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// 打开连接
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// 编写并执行ADO命令.
// 可以是SQL指令(SELECT/UPDATE/INSERT/DELETE),或是调用存储过程.
// 此处是一个INSERT命令示例.
//
Console.WriteLine("将一条记录插入表CountryRegion中...");
// 1. 生成一个Command对象
ADODB.Command cmdInsert = new ADODB.Command();
// 2. 将连接赋值于命令
cmdInsert.ActiveConnection = conn;
// 3. 设置命令文本
// SQL指令或者存储过程名
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. 设置命令类型
// ADODB.CommandTypeEnum.adCmdText 用于普通的SQL指令;
// ADODB.CommandTypeEnum.adCmdStoredProc 用于存储过程.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. 添加参数
// CountryRegionCode (nvarchar(20)参数的添加
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // 参数类型
20, // 参数的最大长度
"ZZ" + DateTime.Now.Millisecond); // 参数值
cmdInsert.Parameters.Append(paramCode);
// Name (nvarchar(200))参数的添加
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
200, // 参数的最大长度
"Test Region Name"); // 参数值
cmdInsert.Parameters.Append(paramName);
// ModifiedDate (datetime)参数的添加
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // 参数名
ADODB.DataTypeEnum.adDate, // 参数类型 (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
-1, // 参数的最大长度 (datetime忽视该值)
DateTime.Now); // 参数值
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. 执行命令
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// 使用Recordset对象.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset表示了数据表中记录或执行命令获得的结果的集合。
// 在任何时候， Recordset对象都指向集合中的单条记录，并将
// 该记录作为它的当前记录。
//
Console.WriteLine("列出表CountryRegion中的所有记录");
// 1. 生成Recordset对象
rs = new ADODB.Recordset();
// 2. 打开Recordset对象
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL指令/表,视图名 /
// 存储过程调用 /文件名
conn, // 连接对象/连接字符串
ADODB.CursorTypeEnum.adOpenForwardOnly, // 游标类型. (只进游标)
ADODB.LockTypeEnum.adLockOptimistic, // 锁定类型. (仅当需要调用
// 更新方法时，才锁定记录）
(int)ADODB.CommandTypeEnum.adCmdText); // 将第一个参数视为SQL命令
// 或存储过程.
// 3. 通过向前移动游标列举记录
// 移动到Recordset中的第一条记录
rs.MoveFirst();
while (!rs.EOF)
{
// 当在表中定义了一个可空字段，需要检验字段中的值是否为DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// 移动到下一条记录
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("应用程序出现错误: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("描述: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// 退出前清理对象.
//
Console.WriteLine("正在关闭连接 ...");
// 关闭record set，当它处于打开状态时
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// 关闭数据库连接，当它处于打开状态时
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}