c连接oracle数据库的连接语句

连接代码如下：

int main()

{

EXEC SQL BEGIN DECLARE SECTION;

char oc_passwd[101]; /数据库密码/

char oc_userid[101]; /数据库用户名/

char oc_dbname[101]; /数据库名/

char oc_coad[101];

EXEC SQL END DECLARE SECTION;

memset(oc_passwd, 0x00, sizeof(oc_passwd));

memset(oc_userid, 0x00, sizeof(oc_userid));

memset(oc_dbname, 0x00, sizeof(oc_dbname));

/取数据库用户名/

strcpy(oc_userid, "userid");

/取数据库用户密码/

strcpy(oc_passwd, "passwd") ;

/取数据库名/

strcpy(oc_dbname, "dbname");

EXEC SQL CONNECT :oc_userid

IDENTIFIED BY :oc_passwd

USING :oc_dbname;

if (sqlcasqlcode != 0)

{

printf("用户名[%s]密码[%s]数据库[%s]\n", oc_userid, oc_passwd, oc_dbname);

printf("连接数据库失败,sqlcode=%d\n", sqlcasqlcode);

return -1;

}

/读table取coad字段/

memset(oc_coad, 0x00, sizeof(oc_coad));

EXEC SQL SELECT coad

INTO :oc_coad

FROM table

WHERE 1=1;

if (sqlcasqlcode == NORECORD)

{

printf("查询无记录\n");

return -1;

}

else if (sqlcasqlcode != 0)

{

printf("查询失败,sqlcode=%d\n", sqlcasqlcode);

return -1;

}

return 0;

}

　　举个例子，连接SQL：

　　// 打开数据库

　　strDBClassFormat(_T("Provider=MicrosoftJetOLEDB40;Data Source=%s;Jet OLEDB:Database Password=%s"), m_strUnEntryptMdbFilePath,m_strMDBPassword);

　　// 创建连接

　　HRESULT hr = m_pConnectionCreateInstance(_uuidof(Connection));

　　_ConnectionPtr m_pConnection->Open(m_strDBClass,_T(""),_T(""),adConnectUnspecified);

　　// 声明表单指针

　　_RecordsetPtr pBandRecordset;

　　pBandRecordsetCreateInstance(__uuidof(Recordset));

　　// 执行语句

　　CString strSQL（L"SELECT FROM [Band]"）;

　　m_pConnection->Execute((LPCTSTR)strSQL,NULL,0);

　　// 提取某一项 例如BandInfo

　　int iBandInfo = wcscmp(colum, L"BandInfo");

　　while(!recordsetPtr->adoEOF)

　　{

　　var = recordsetPtr->GetCollect(colum);

　　if(varvt != VT_NULL)

　　strName = (LPCSTR)_bstr_t(var);

　　recordsetPtr->MoveNext();

　　}

一般C/S模式连接远程数据库是不会去直连的，而是通过一个中间系统去连接数据库。比如说你举的例子，客户端在上海，数据库在北京，那么需要在北京架一台WebService服务器（或在上海或其他城市也行，最好是在北京，与数据库在同一个内网里，但只有WebService服务器对外，而数据库服务器不对外），然后在WebService中实现增删查改功能，再利用Client端去直接访问这些WebService功能即可。

1、建立存储：

CREATE PROCedure[owner]procedurename[;number]

　　　　　　　　　　[[(]@parameter_name datatype[=default][OUTput]

　　　　　　　　　　[,@parameter_name datatype[=default][OUTput]][)]]

　　　　　　　　　　[WITH RECOMPILE]

　　　　　　　　　　AS SQL_statements

2、存储规则:

　　　　　　　　[EXECute][@return-status=]

　　　　　　　　　　[[[server]database]owner]procedurename[;number]

　　　　　　　　　　[[@parameter_name=]value|[@parameter_name=]@varialbe[OUTput]

　　　　　　　　　　[,[@parameter_name=]value|[@parameter_name=]@variable[OUTput]]]

　　　　　　　　　　[WITH RECOMPILE]

可以处理的，步骤为：

1客户端与服务器连（用Socket通讯）

2客户端向处服务器发送SQL语句，如搜索一个表的数据

3服务器接到请求，执行SQL语句返回一个DataTable

4服务器将这个DataTable进行序列化、并且压缩

5服务器将序列化和压缩后的byte[] msg数组传给客户端

6客户端收到byte[] msg数组先进行解压缩、和反序列化为DataTable

7再将DataTable通过DataSet和SqlDataAdapterUpdate(Table)存在SQL中

下面我只给一些关键的代码，我有实现过的

第一步客户端==》连接服务器并通讯，主要是发送SQL给服务器返回一个DataTable表

using System;

using SystemCollectionsGeneric;

using SystemComponentModel;

using SystemData;

using SystemDrawing;

using SystemLinq;

using SystemText;

using SystemWindowsForms;

using SystemNet;

using SystemNetSockets;

using SystemXml;

using SystemXmlSerialization;

using SystemIO;

using SystemIOCompression;

namespace Client

{

public partial class FormClient : Form

{

DataTable dt;

public FormClient()

{

InitializeComponent();

}

private void ClientTest(string str)

{

Socket client;

String returnData;

byte[] buf = new byte[9991024]; //此处可能有些问题

//IPAddress local = IPAddressParse("1162583127");

IPAddress local = IPAddressParse("127001");//可改为远程IP地址

IPEndPoint iep = new IPEndPoint(local, 6060);

try

{

client = new Socket(AddressFamilyInterNetwork, SocketTypeStream, ProtocolTypeTcp);

clientConnect(iep);

}

catch (SocketException)

{

ConsoleWriteLine("无法连接到服务器!");

return ;

}

//输入exit，可以断开与服务器的连接

if (str == "")

{

return;

}

//发送SQL语句给服务器

clientSend(EncodingUnicodeGetBytes(str));

//得到实际收到的字节总数

Int32 rec = clientReceive(buf);

ConsoleWriteLine(EncodingASCIIGetString(buf, 0, rec));

//接收服务器返回的数据

returnData = SystemTextEncodingUnicodeGetString(buf,0,rec);

//将returnData解压缩后，再反序列化转成DataTable dt = DeserializerDataTable(Decompress(returnData));

ConsoleWriteLine("断开与服务器的连接");

clientClose();

dataGridView1DataSource = dt;

}

//将DataTable表反序列化

private DataTable DeserializerDataTable(string pXml)

{

StringReader strReader = new StringReader(pXml);

XmlReader xmlReader = XmlReaderCreate(strReader);

XmlSerializer serializer = new XmlSerializer(typeof(DataTable));

DataTable dt = serializerDeserialize(xmlReader) as DataTable;

return dt; }

//给服务器传SQL语句

private void buttonSearch_Click(object sender, EventArgs e)

{

string Sql = StringFormat("Select top {0} From Part Where CorpCode_='PT'", textBox1TextTrim()); ;

ClientTest(Sql);

}

#region 压缩和解压缩

public string Compress(string str)

{

byte[] buffer = EncodingUnicodeGetBytes(str);

MemoryStream ms = new MemoryStream();

using (GZipStream zip = new GZipStream(ms, CompressionModeCompress, true))

{

zipWrite(buffer, 0, bufferLength);

}

msPosition = 0;

MemoryStream outStream = new MemoryStream();

byte[] compressed = new byte[msLength];

msRead(compressed, 0, compressedLength);

byte[] gzBuffer = new byte[compressedLength + 4];

SystemBufferBlockCopy(compressed, 0, gzBuffer, 4, compressedLength);

SystemBufferBlockCopy(BitConverterGetBytes(bufferLength), 0, gzBuffer, 0, 4);

return ConvertToBase64String(gzBuffer);

}

//解压缩

public string Decompress(string compressedText)

{

byte[] gzBuffer = ConvertFromBase64String(compressedText);

using (MemoryStream ms = new MemoryStream())

{

int msgLength = BitConverterToInt32(gzBuffer, 0);

msWrite(gzBuffer, 4, gzBufferLength - 4);

byte[] buffer = new byte[msgLength];

msPosition = 0;

using (GZipStream zip = new GZipStream(ms, CompressionModeDecompress))

{

zipRead(buffer, 0, bufferLength);

}

return EncodingUnicodeGetString(buffer);

}

}

#endregion

}

}

第二步服务器收到SQL语句执行，并回传一个表给客户端

服务器接到请求，执行SQL语句返回一个DataTable

服务器将这个DataTable进行序列化、并且压缩

服务器将序列化和压缩后的byte[] msg数组传给客户端

using System;

using SystemCollectionsGeneric;

using SystemComponentModel;

using SystemData;

using SystemDrawing;

using SystemLinq;

using SystemText;

using SystemWindowsForms;

using SystemNet;

using SystemNetSockets; //可以使用套接字

using SystemThreading; //可以使用多线程

using SystemDataSqlClient;

using SystemXml;

using SystemXmlSerialization;

using SystemIO;

using SystemIOCompression;

namespace AppServer

{

public partial class FormApp : Form

{

public FormApp()

{

InitializeComponent();

}

private void FormApp_Load(object sender, EventArgs e)

{

// AppService instance = new AppService();

}

private void buttonStartService_Click(object sender, EventArgs e)

{

AppService();

}

private Socket server;

private Socket client;

private void AppService()

{

/

//本机IP

string name = DnsGetHostName();

IPHostEntry host = DnsGetHostByName(name);

IPAddress id= hostAddressList[0];

/

//IPAddress local = IPAddressParse("1921680100");

IPEndPoint iep = new IPEndPoint(IPAddressAny, 6060);

server = new Socket(AddressFamilyInterNetwork, SocketTypeStream, ProtocolTypeTcp);

// 将套接字与本地终结点绑定

serverBind(iep);

//在本地13000端口号上进行监听

serverListen(10);

while (true)

{

// 得到包含客户端信息的套接字

client = serverAccept();

//创建消息服务线程对象ClientService方法委托给线程

Thread newthread = new Thread(new ThreadStart(ClientService));

// 启动消息服务线程

newthreadStart();

}

}

private Int32 i;

private void ClientService()

{

Socket s = client;

String data = null;

String returnData = null;

byte[] bytes = new byte[1024];

while ((i = sReceive(bytes)) != 0)

{

//接收客户端的SQL

data = SystemTextEncodingUnicodeGetString(bytes, 0, i);

//将接到的String 执行SQL返回表

DBAccess obj = new DBAccess(); //专门传给SQL的类相当于DBHELP

DataTable dt = objFillData(data, "TEST", 1);//执行SQL返回表

//将返回的表转为String,并将returnData压缩

returnData = objCompress(objSerializeDataTableXml(dt));

byte[] msg = SystemTextEncodingUnicodeGetBytes(returnData);

// 发送数据表给客户端 sSend(msg);

if ((i = sReceive(bytes)) == 0)

continue;

}

//关闭套接字

sClose();

}

}

//DBAccess的部份代码

class DBAccess

{

#region DataTable序列化与反序列化

public string SerializeDataTableXml(DataTable dt)

{

StringBuilder sb = new StringBuilder();

XmlWriter writer = XmlWriterCreate(sb);

XmlSerializer serializer = new XmlSerializer(typeof(DataTable));

serializerSerialize(writer, dt);

writerClose();

return sbToString(); }

public DataTable DeserializerDataTable(string pXml)

{

StringReader strReader = new StringReader(pXml);

XmlReader xmlReader = XmlReaderCreate(strReader);

XmlSerializer serializer = new XmlSerializer(typeof(DataTable));

DataTable dt = serializerDeserialize(xmlReader) as DataTable;

return dt; }

#endregion

#region 压缩和解压缩

public string Compress(string str)

{

byte[] buffer = EncodingUnicodeGetBytes(str);

MemoryStream ms = new MemoryStream();

using (GZipStream zip = new GZipStream(ms, CompressionModeCompress, true))

{

zipWrite(buffer, 0, bufferLength);

}

msPosition = 0;

MemoryStream outStream = new MemoryStream();

byte[] compressed = new byte[msLength];

msRead(compressed, 0, compressedLength);

byte[] gzBuffer = new byte[compressedLength + 4];

SystemBufferBlockCopy(compressed, 0, gzBuffer, 4, compressedLength);

SystemBufferBlockCopy(BitConverterGetBytes(bufferLength), 0, gzBuffer, 0, 4);

return ConvertToBase64String(gzBuffer);

}

public string Decompress(string compressedText)

{

byte[] gzBuffer = ConvertFromBase64String(compressedText);

using (MemoryStream ms = new MemoryStream())

{

int msgLength = BitConverterToInt32(gzBuffer, 0);

msWrite(gzBuffer, 4, gzBufferLength - 4);

byte[] buffer = new byte[msgLength];

msPosition = 0;

using (GZipStream zip = new GZipStream(ms, CompressionModeDecompress))

{

zipRead(buffer, 0, bufferLength);

}

return EncodingUnicodeGetString(buffer);

}

}

#endregion

}

第三步调用DataSet和SqlDataAdapterUpdate(Table)存在SQL中，这个是传入一个DataGridView ，可在客户端中使用保存（部份代码，没有连接SQL数据库的）

#region 公共保存DataGridView

public bool DataGridViewSave(DataTable table,string tableName,string CorpCode)

{

string Sql = StringFormat("Select Top 0 From {0} Where CorpCode_='{1}' ", tableName, CorpCode);

SqlDataAdapter sda = new SqlDataAdapter(thisCreateCommand(Sql, null, 1));

SqlCommandBuilder scb = new SqlCommandBuilder(sda);

sdaUpdate(table);

thisClose();

return true;

}

#endregion