📄 oledbaccessobj.cs
字号:
/// </summary>
/// <param name="TableName">数据库表名</param>
/// <param name="ColumnName">字段名</param>
/// <returns></returns>
public int GetMaxValueFromTable(string TableName,string ColumnName)
{
if(TableName == string.Empty)
throw new Exception("传入的表名参数TableName为空，在OLEDBAccessObj.GetMaxValueFromTable中");
if(ColumnName == string.Empty)
throw new Exception("传入的列名参数ColumnName为空，在OLEDBAccessObj.GetMaxValueFromTable中");
string strSQL = "Select Max(" + TableName + "." + ColumnName + ") As MaxID From " + TableName;
//System.Windows.Forms.MessageBox.Show(strSQL);
OleDbDataReader dr = this.GetSQLDataReader(strSQL);
dr.Read();
if(dr.GetValue(0) == DBNull.Value)
{
dr.Close();
return 0;
}
int retv = (int)dr.GetValue(0);
dr.Close();
return retv;
}
/// <summary>
/// 清除指定表的内容
/// </summary>
/// <param name="TableName"></param>
public void ClearTable(string TableName)
{
if(TableName.Trim() == string.Empty)
return;
string strSQL = "Delete * From " + TableName;
this.ExecSQLCommand(strSQL);
}
/// <summary>
/// 连接是否打开
/// </summary>
/// <returns></returns>
public bool IsConnected()
{
return _Connection.State == ConnectionState.Open;
}
/// <summary>
/// 处理Access的OLEDB字段,目前只处理表中只有一个BLOB字段的情况
/// </summary>
/// <param name="strSQL">代表带参数的SQL命令,一般是UPDATE和INSERT命令</param>
/// <param name="ParameterName">用于指明SQL参数名称,如"@value"</param>
/// <param name="byteValue">一个Byte数组,用于传送二进制信息</param>
/// <returns>返回此命令影响的行数</returns>
public int DoWithBLOBField(string strSQL,string ParameterName,byte[] byteValue)
{
if(_Connection == null)
throw new Exception("未创建数据连接对象，在OLEDBAccessObj.DoWithBLOBField中");
if(IsConnected() == false)
throw new Exception("数据库连接已关闭，在OLEDBAccessObj.DoWithBLOBField");
if(_Command == null)
_Command = new OleDbCommand();
_Command.Connection = _Connection;
_Command.CommandType = CommandType.Text;
_Command.CommandText = strSQL;
//设置参数
_Command.Parameters.Clear();
_Command.Parameters.Add(ParameterName,OleDbType.Binary);
_Command.Parameters[ParameterName].Value = byteValue;
int ret;
try
{
ret = _Command.ExecuteNonQuery();
}
catch(Exception ex)
{
throw new Exception("向数据库中插入BLOB字段时发生错误，在OLEDBAccessObj.DoWithBLOBField中，系统提示：" + ex.Message);
}
return ret;
}
/// <summary>
/// 将一个表中的BLOB字段内容存为文件
/// </summary>
/// <param name="strSQL">strSQL必须是一个有效的SQL命令，要求只返回一条记录，如果返回多条，则只处理第一条,strSQL的字段列表中必须有一个字段是BLOB字段</param>
/// <param name="BLOBFieldIndex">BLOBFieldIndex代表strSQL中的Select命令字段列表中BLOB字段的索引，从0开始，例如，FileContent是表Files的BLOB字段，则Select FileName,FileContent from Files where FileID=1中BLOBFieldIndex=1 而：Select FileContent,FileName from Files where FileID=1中BLOBFieldIndex=0</param>
/// <param name="SaveFileName">SaveFileName代表要保存的文件名，注意：如果文件已存在，则直接覆盖</param>
public void SaveBLOBToFile(string strSQL,int BLOBFieldIndex,string SaveFileName)
{
FileStream fs;
BinaryWriter bw;
int bufferSize = 100;//The size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize];//The BLOB byte[] buffer to be filled by GetBytes.
long retval;//The bytes returned from GetBytes.
long startIndex = 0;
OleDbDataReader dr;
try
{
fs = new FileStream(SaveFileName,FileMode.OpenOrCreate,FileAccess.Write);
bw = new BinaryWriter(fs);
dr = this.GetSQLDataReader(strSQL);
dr.Read();
//Reset the starting byte for a new BLOB.
startIndex = 0;
//Read bytes into outbyte[] and retain the number of bytes returned.
retval = dr.GetBytes(BLOBFieldIndex,startIndex,outbyte,0,bufferSize);
//Continue reading and writing while there are bytes beyond the size of the buffer.
while(retval == bufferSize)
{
bw.Write(outbyte);
bw.Flush();
//Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = dr.GetBytes(BLOBFieldIndex,startIndex,outbyte,0,bufferSize);
}
//Write the remaining buffer.
bw.Write(outbyte,0,(int)retval);
bw.Flush();
// Close the output file.
bw.Close();
fs.Close();
dr.Close();
}
catch(Exception ex)
{
throw new Exception("在将BLOB字段内容存入文件时发生错误，可能的原因是：1。BLOB字段的索引号错误，2。SQL命令错误。执行的SQL命令为：" + strSQL + " 在OLEDBAccessObj.SaveBLOBToFile()中，系统提示：" + ex.Message );
}
}
/// <summary>
/// 将BLOB字段的内容写入一个内存流中
/// </summary>
/// <param name="strSQL">strSQL必须是一个有效的SQL命令，要求只返回一条记录，如果返回多条，则只处理第一条,strSQL的字段列表中必须有一个字段是BLOB字段</param>
/// <param name="BLOBFieldIndex">BLOBFieldIndex代表strSQL中的Select命令字段列表中BLOB字段的索引，从0开始，例如，FileContent是表Files的BLOB字段，则Select FileName,FileContent from Files where FileID=1中BLOBFieldIndex=1 而：Select FileContent,FileName from Files where FileID=1中BLOBFieldIndex=0</param>
/// <param name="mem">要写入的内存流</param>
public void SaveBLOBToMemoryStream(string strSQL,int BLOBFieldIndex,MemoryStream mem)
{
int bufferSize = 100;//The size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize];//The BLOB byte[] buffer to be filled by GetBytes.
long retval;//The bytes returned from GetBytes.
long startIndex = 0;
OleDbDataReader dr;
try
{
BinaryWriter bw = new BinaryWriter(mem);
dr = this.GetSQLDataReader(strSQL);
dr.Read();
//Reset the starting byte for a new BLOB.
startIndex = 0;
//Read bytes into outbyte[] and retain the number of bytes returned.
retval = dr.GetBytes(BLOBFieldIndex,startIndex,outbyte,0,bufferSize);
//Continue reading and writing while there are bytes beyond the size of the buffer.
while(retval == bufferSize)
{
bw.Write(outbyte);
bw.Flush();
//Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = dr.GetBytes(BLOBFieldIndex,startIndex,outbyte,0,bufferSize);
}
//Write the remaining buffer.
bw.Write(outbyte,0,(int)retval);
bw.Flush();
// Close the output file.
dr.Close();
//流移到开头
mem.Position = 0;
}
catch(Exception ex)
{
throw new Exception("在将BLOB字段内容写入内存流时发生错误，可能的原因是：1。BLOB字段的索引号错误，2。SQL命令错误。执行的SQL命令为：" + strSQL + " 在OLEDBAccessObj.SaveBLOBToMemoryStream()中，系统提示：" + ex.Message );
}
}
/// <summary>
/// 根据SQL命令返回数据集
/// </summary>
/// <param name="strSQL">SQL命令</param>
/// <param name="Parameters">要传给SQL的参数集</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
public DataSet GetSQLDataSet(string strSQL,ListDictionary Parameters,string TableName)
{
try
{
_Command.CommandType = CommandType.Text;
_Command.CommandText = strSQL;
//加入参数
_Command.Parameters.Clear();
foreach(DictionaryEntry myDE in Parameters)
{
_Command.Parameters.Add(myDE.Value.ToString(),myDE.Value);
}
_DataSet = new DataSet();
if(TableName == null)
{
_DataAdapter.Fill(_DataSet);
}
else
{
_DataAdapter.Fill(_DataSet,TableName);
}
return _DataSet;
}
catch(Exception ex)
{
throw new Exception("根据SQL命令返回数据集时出错，在OLEDBAccessObj.getSQLDataSet中，系统提示：" + ex.Message);
}
}
/// <summary>
/// 根据数据库表名取回其所有字段
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetAllColumnsInTable(string tableName)
{
DataTable dt;
if(!IsConnected())
this.ConnectDB();
dt = _Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{null,null,tableName,null});
DataColumn dc = new DataColumn("DATA_TYPE_STR",Type.GetType("System.String"));
dt.Columns.Add(dc);
foreach(DataRow dr in dt.Rows)
{
dr["DATA_TYPE_STR"] = this.DataTypeToStr((OleDbType)dr["DATA_TYPE"]);
}
return dt;
}
/// <summary>
/// 返回数据库的所有表名
/// </summary>
/// <returns></returns>
public ArrayList GetAllTables()
{
DataTable dt;
if(!this.IsConnected())
this.ConnectDB();
dt = _Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"}); ArrayList arr = new ArrayList(); foreach(DataRow dr in dt.Rows) { arr.Add(dr["TABLE_NAME"]); } return arr;
}
/// <summary>
/// 将OLEDB中定义的枚举转换为字符串
/// </summary>
/// <param name="oleValue"></param>
/// <returns></returns>
private string DataTypeToStr(OleDbType oleValue)
{
switch(oleValue)
{
case OleDbType.BigInt:
return "BigInt";
case OleDbType.Binary:
return "Binary";
case OleDbType.Boolean:
return "Boolean";
case OleDbType.BSTR:
return "BSTR";
case OleDbType.Char:
return "Char";
case OleDbType.Currency:
return "Currency";
case OleDbType.Date:
return "Date";
case OleDbType.DBDate:
return "DBDate";
case OleDbType.DBTime:
return "DBTime";
case OleDbType.DBTimeStamp:
return "DBTimeStamp";
case OleDbType.Decimal:
return "Decimal";
case OleDbType.Double:
return "Double";
case OleDbType.Empty:
return "Empty";
case OleDbType.Error:
return "Error";
case OleDbType.Filetime:
return "Filetime";
case OleDbType.Guid:
return "Guid";
case OleDbType.IDispatch:
return "IDispatch";
case OleDbType.Integer:
return "Integer";
case OleDbType.IUnknown:
return "IUnknown";
case OleDbType.LongVarBinary:
return "LongVarBinary";
case OleDbType.LongVarChar:
return "LongVarChar";
case OleDbType.LongVarWChar:
return "LongVarWChar";
case OleDbType.Numeric:
return "Numeric";
case OleDbType.PropVariant:
return "PropVariant";
case OleDbType.Single:
return "Single";
case OleDbType.SmallInt:
return "SmallInt";
case OleDbType.TinyInt:
return "TinyInt";
case OleDbType.UnsignedBigInt:
return "UnsignedBigInt";
case OleDbType.UnsignedInt:
return "UnsignedInt";
case OleDbType.UnsignedSmallInt:
return "UnsignedSmallInt";
case OleDbType.UnsignedTinyInt:
return "UnsignedTinyInt";
case OleDbType.VarBinary:
return "VarBinary";
case OleDbType.VarChar:
return "VarChar";
case OleDbType.Variant:
return "Variant";
case OleDbType.VarNumeric:
return "VarNumeric";
case OleDbType.VarWChar:
return "VarWChar";
case OleDbType.WChar:
return "WChar";
default:
return "UnKnowType";
}
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -