codb.cs
来自「oracle的oci驱动,在c#中定义,速度非常快,尤其可以批量的读取,插入数据」· CS 代码 · 共 697 行 · 第 1/3 页
CS
697 行
break;
default:
list_fieldInfo[i].dwFieldLength *= 2;//unicode字符,占2个byte
m_nRecordLength += list_fieldInfo[i].dwFieldLength;
break;
}
}
i = m_nRecordLength * m_nPerFetch;
if (capacity_result_buffer < i)
{
capacity_result_buffer = i;
result_buffer = new byte[i];
}
if (m_bIsIncludeLobOrLbi)
{
if (capacity_pInp4LBI < m_nPerFetch)
{
capacity_pInp4LBI = m_nPerFetch;
m_pInp4LBI = new char[capacity_pInp4LBI];
}
}
//绑定数组以获取数据
i = 0;
UInt32 currpos = 0;
IntPtr pDefn = IntPtr.Zero;
for (; i < m_nCurrentNumOfColumn; ++i)
{
if (SQLT_LBI == list_fieldInfo[i].dwFieldType)
{
//定义输出
status = OCIDefineByPos(stmthp, out pDefn, errhp,
i + 1, //起始数值是从 1开始,不是从0开始
ref result_buffer[currpos],
(Int32)list_fieldInfo[i].dwFieldLength,
(UInt16)SQLT_LBI,
ref m_pInp4LBI[0], //查询指示符
IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
status = OCIDefineArrayOfStruct(pDefn, errhp,
m_nRecordLength, //Skip parameter for the next data value.
sizeof(char), //Skip parameter for the next indicator location
0, //Skip parameter for the next return length value
0 //Skip parameter for the next return code
);
}
else
{
//定义输出
status = OCIDefineByPos(stmthp, out pDefn, errhp,
i + 1, //起始数值是从 1开始,不是从0开始
ref result_buffer[currpos],
(Int32)list_fieldInfo[i].dwFieldLength,
(UInt16)list_fieldInfo[i].dwFieldType,
IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
status = OCIDefineArrayOfStruct(pDefn, errhp,
m_nRecordLength, //Skip parameter for the next data value.
0, //Skip parameter for the next indicator location
0, //Skip parameter for the next return length value
0 //Skip parameter for the next return code
);
}
currpos += list_fieldInfo[i].dwFieldLength;
}
return 0;
}
public Int32 FetchNext()
{
bool m_bSltErr = false;
memset(ref result_buffer[0], 0, (UInt32)result_buffer.Length);
if (m_bIsIncludeLobOrLbi)
memset(ref m_pInp4LBI[0], 0, m_nPerFetch << 1);
Int32 status = OCIStmtFetch(stmthp, errhp, m_nPerFetch, OCI_FETCH_NEXT, OCI_DEFAULT);
//if (0 != status)
//{
// this.OciReportError(errhp);
// //return 0;
//}
UInt32 m_nCurrentFetchRecord;
status = OCIAttrGet(stmthp, OCI_HTYPE_STMT, out m_nCurrentFetchRecord, IntPtr.Zero, OCI_ATTR_ROWS_FETCHED, errhp);
//检查long raw 是否有截断
if (m_nCurrentFetchRecord > 0 && m_bIsIncludeLobOrLbi)
{
for (UInt32 i = 0; i < m_nCurrentFetchRecord; ++i)
{
if (0 < m_pInp4LBI[i])
{
m_bSltErr = true;
break;
}
}
}
if (m_bSltErr)
return -2;//有截断
else
return (Int32)m_nCurrentFetchRecord;
}
public Int32 StoreGPS(String user_id, String str_gps)
{
Int32 status = 0, fid = 0;
String sql = "select scott.seq_msgtransfer.nextval from dual";
String[] s_arr = str_gps.Split('`');
String sql_gps = String.Format("INSERT INTO T_KEEPERSTRACK(FID,KEEPERSN,TRACKTIME,COORDX,COORDY,INSERTTIME) VALUES({0},'{1}',sysdate,'{2}','{3}',sysdate)",
fid, user_id, s_arr[0], s_arr[1]);
if (!isOpened)
{
status = Open(user, pwd, dblink, false);
if (0 != status)
return -1;
}
status = OCIStmtPrepare(stmthp, errhp, sql, sql.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
IntPtr pDefn= IntPtr.Zero;
char indp = (char)0, rlenp = (char)4;
status = OCIDefineByPos(stmthp, out pDefn, errhp, 1, ref fid, 4, SQLT_INT, ref indp, ref rlenp, IntPtr.Zero, OCI_DEFAULT);
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_EXACT_FETCH);
if (OCI_ERROR == status)
{
Int32 errcode = 0;
OCIErrorGet(errhp, 1, IntPtr.Zero, out errcode,
null, (UInt32)0, OCI_HTYPE_ERROR);
if (12571 == errcode || 12541 == errcode)
{
Thread.Sleep(5000);
status = Open(user, pwd, dblink, false);
if (0 != status)
return -1;
status = OCIStmtPrepare(stmthp, errhp, sql, sql.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
status = OCIDefineByPos(stmthp, out pDefn, errhp, 1, ref fid, 4, SQLT_INT, ref indp, ref rlenp, IntPtr.Zero, OCI_DEFAULT);
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_EXACT_FETCH);
if (OCI_ERROR == status)
{
Close();
loginError("");
return -1;
}
}
else
{
Close();
loginError("");
return -1;
}
}
status = OCIStmtPrepare(stmthp, errhp, sql_gps, sql_gps.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_COMMIT_ON_SUCCESS);
if (OCI_ERROR == status)
{
Close();
loginError("");
return -1;
}else
return 0;
}
public Int32 GetSelectInt32(String sql)
{
/**
* sword OCIDefineByPos (
OCIStmt *stmtp, //语句句柄
OCIDefine **defnpp,//定义句柄—用于数组变量
OCIError *errhp,
ub4 position,//位置序号(从1 开始)
dvoid *valuep, //输出的变量名
sb4 value_sz, //变量长度
ub2 dty, //数据类型
dvoid *indp, //指示器变量/指示器变量数组，如果此字段可能存在空值，则要指示器变量，否则单条处理时为NULL
ub2 *rlenp, //提取的数据长度
ub2 *rcodep, //列级返回码数组指针
ub4 mode //OCI_DEFAULT
);
*/
Int32 data = 0;
Int32 status = OCIStmtPrepare(stmthp, errhp, sql, sql.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
IntPtr pDefn;
char indp = (char)0, rlenp = (char)4;
status = OCIDefineByPos(stmthp, out pDefn, errhp, 1, ref data, 4, SQLT_INT, ref indp, ref rlenp, IntPtr.Zero, OCI_DEFAULT);
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_EXACT_FETCH);
return data;
}
public Boolean StoreMessage(String user_id, String user_name, String strMsg, Int32 nFid, byte nType)
{
// TRUNCATE TABLE scott.MSGTRANSFER
//Int32 nFid = GetSelectInt32("SELECT SEQ_MSGTRANSFER.NEXTVAL FROM DUAL");
//nIndex_msg = nFid;
byte[] array_msg = System.Text.Encoding.Unicode.GetBytes(strMsg);
DateTime date = System.DateTime.Now;
String str_datetime = date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo);
String sql_insert = String.Format("INSERT INTO MSGTRANSFER(FID,FUSERID,FUSERNAME,FTIME,FMESSAGE,FTYPE) VALUES ({0},'{1}','{2}',to_date('{3}','yyyy-mm-dd hh24:mi:ss'),empty_blob(),{4})", nFid, user_id, user_name, str_datetime, nType);
//准备 insert sql语句
Int32 status = OCIStmtPrepare(stmthp, errhp, sql_insert, sql_insert.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
{
OciReportError(errhp);
return false;
}
//执行 insert sql 语句 Insert an empty locator
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
{
OciReportError(errhp);
status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
return false;
}
//select for update
String sql_update = String.Format("SELECT FMESSAGE FROM MSGTRANSFER WHERE FID={0} FOR UPDATE", nFid);
status = OCIStmtPrepare(stmthp, errhp, sql_update, sql_update.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
//分配lob定位符句柄
IntPtr blob;
status = OCIDescriptorAlloc(envhp, out blob, OCI_DTYPE_LOB, 0, IntPtr.Zero);
IntPtr p_Define;
status = OCIDefineByPos(stmthp, out p_Define, errhp, 1, ref blob, -1,
SQLT_BLOB, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
{
OciReportError(errhp);
OCIDescriptorFree(blob, OCI_DTYPE_LOB);
status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
return false;
}
//打开描述符，以读写方式
status = OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE);
UInt32 nSizeOfLob = 0;
UInt32 amt = (UInt32)array_msg.Length; UInt32 nSieOfMsg = amt;
status = OCILobGetLength(svchp, errhp, blob, out nSizeOfLob);
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?