BLOG用SQL语句是不能直接插入的,必须先插入一个空数据empty_blob(),然后再 private void SaveFile(int data_id,byte[] p_Blob,string id,string photo,string tablename,OracleConnection mConn) { try { OracleDataAdapter photoAdapter; DataSet photoDataSet; DataTable photoTable; DataRow photoRow; photoAdapter = new OracleDataAdapter( "SELECT "+id+","+photo+"" + " FROM "+tablename+" WHERE "+id+" = " + data_id,mConn); photoDataSet= new DataSet(tablename); photoAdapter.UpdateCommand = new OracleCommand ("UPDATE "+tablename+" SET " + ""+photo+" = :vPHOTO " + "WHERE "+id+" = :vID",mConn); photoAdapter.UpdateCommand.Parameters.Add(":vPHOTO", OracleType.Blob, p_Blob.Length, photo); photoAdapter.UpdateCommand.Parameters.Add(":vID", OracleType.Int32, 0, id); photoAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Configures the schema to match with Data Source photoAdapter.FillSchema(photoDataSet, SchemaType.Source, tablename); // Fills the DataSet with 'drivers' table data photoAdapter.Fill(photoDataSet,tablename); // Get the current driver ID row for updation photoTable = photoDataSet.Tables[tablename]; photoRow = photoTable.Rows.Find(data_id); // Start the edit operation on the current row in // the 'drivvers' table within the dataset. photoRow.BeginEdit(); // Assign the value of the Photo if not empty if (p_Blob.Length != 0) { photoRow[photo] = p_Blob; } // End the editing current row operation photoRow.EndEdit(); // Update the database table 'drivers' photoAdapter.Update(photoDataSet,tablename); } catch(Exception e) { throw e; } } #endregion用DATASET 方式插入数据
using System.Data;
using System.Data.OracleClient;
class Sample
{
public static void Main()
{
string myConnString = "user id=zk;data source=zkqx;password=*****";
OracleConnection myConnection = new OracleConnection(myConnString); OracleCommand catCMD = myConnection.CreateCommand();
catCMD.CommandText = " SELECT CZYDM,CZYMC,CZYMM,CZYQX FROM ZC_CZY "; myConnection.Open(); OracleDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", myReader.GetString(0),myReader.GetString(1),myReader.GetString(2),myReader.GetString(3));
} myReader.Close(); myConnection.Close();
}
}
只是你需要配置好oracle客户端,链接串,其它没有什么
private string ConnectionString()
{
string strConnectionString = "Provider=OraOLEDB.Oracle.1;User ID="+this.UserName+";Password="+this.Password
+";Data Source="+this.UserName+";";
return strConnectionString;
}
上面是构造连接字符串;this.UserName是登陆用户;后面是密码;this.UserName是服务器IP
OleDbConnection conn = new OleDbConnection(this.ConnectionString());
conn.Open();
试试看吧
OracleConnection conn=new OracleConnection(@"Server=zhangln;uid=ehr;pwd=;");
try
{
conn.Open();
OracleCommand cmd=new OracleCommand("select * from A001",conn);
OracleDataReader reader=cmd.ExecuteReader();
while(reader.Read())
Console.WriteLine(reader["A00100"]);
}
catch(OracleException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
msdn上就能找到的
下面的代码通过DataAdapter向oracle中写如数据 OleDbConnection OleDbCon = new OleDbConnection();
OleDbDataAdapter da;
OleDbCommand cmd;
string connStr;
private string SqlStr; DataTable dTable = new DataTable();
DataRow dRow;
dTable.Columns.Add("sno", Type.GetType("System.Int32"));
dTable.Columns.Add("name", Type.GetType("System.String"));
dTable.Columns.Add("age", Type.GetType("System.Int32")); dRow = dTable.NewRow();
dRow[0] = 1003;
dRow[1] = "sea";
dRow[2] = 25;
dTable.Rows.Add(dRow); dRow = dTable.NewRow();
dRow[0] = 1004;
dRow[1] = "seal";
dRow[2] = 25;
dTable.Rows.Add(dRow); connStr = @"provider=OraOLEDB.Oracle; data source=sde; user id=××; password=××;";
SqlStr = "insert into testtable(sno, name, age) values(:sno, :name, :age)"; OleDbCon.ConnectionString = connStr;
cmd = new OleDbCommand(connStr, OleDbCon);
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlStr;
cmd.Parameters.Add("sno", OleDbType.Integer, 10, "sno");
cmd.Parameters.Add("sname", OleDbType.VarChar, 16, "name");
cmd.Parameters.Add("age", OleDbType.Integer, 3, "age"); da = new OleDbDataAdapter("select * from testtable", OleDbCon);
da.InsertCommand = cmd;
da.Fill(dTable);
da.Update(dTable);
OleDbCommand cmd =new OleDbCommand(cnnStr, conn);
cmd.CommandType =CommandType.Text;
cmd.CommandText =cnnStr;
cmd.CommandText="INSERT INTO " + tName + "(fileName, postTime, fileSize, fileType, prefolder, fileContent)"
+ " VALUES(:fileName, :postTime, :fileSize, :fileType, :prefolder, :fileContent)";
cmd.Parameters.Add("fileName", OleDbType.VarChar, 50);
cmd.Parameters[0].Value =fileName;
cmd.Parameters.Add("postTime", OleDbType.VarChar, 20);
cmd.Parameters[1].Value =postTime;
cmd.Parameters.Add("fileSize", OleDbType.Integer, int.MaxValue);
cmd.Parameters[2].Value =fileSize;
cmd.Parameters.Add("fileType", OleDbType.VarChar, 10);
cmd.Parameters[3].Value =fileType;
cmd.Parameters.Add("prefolder", OleDbType.VarChar, 30);
cmd.Parameters[4].Value =preFolder;
cmd.Parameters.Add("fileContent", OleDbType.Binary, fileContent.Length);
cmd.Parameters[5].Value =fileContent;
cmd.ExecuteNonQuery(); 第一个可以实现批量的入库,第二个只能一条条存
http://community.csdn.net/Expert/topic/4905/4905858.xml?temp=.6675531此贴已结,请看新贴:
http://community.csdn.net/Expert/topic/4905/4905858.xml?temp=.6675531
private void SaveFile(int data_id,byte[] p_Blob,string id,string photo,string tablename,OracleConnection mConn)
{
try
{
OracleDataAdapter photoAdapter;
DataSet photoDataSet;
DataTable photoTable;
DataRow photoRow; photoAdapter = new OracleDataAdapter(
"SELECT "+id+","+photo+"" +
" FROM "+tablename+" WHERE "+id+" = " + data_id,mConn);
photoDataSet= new DataSet(tablename);
photoAdapter.UpdateCommand = new OracleCommand
("UPDATE "+tablename+" SET " +
""+photo+" = :vPHOTO " +
"WHERE "+id+" = :vID",mConn);
photoAdapter.UpdateCommand.Parameters.Add(":vPHOTO",
OracleType.Blob, p_Blob.Length, photo);
photoAdapter.UpdateCommand.Parameters.Add(":vID",
OracleType.Int32, 0, id); photoAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Configures the schema to match with Data Source
photoAdapter.FillSchema(photoDataSet, SchemaType.Source, tablename); // Fills the DataSet with 'drivers' table data
photoAdapter.Fill(photoDataSet,tablename); // Get the current driver ID row for updation
photoTable = photoDataSet.Tables[tablename];
photoRow = photoTable.Rows.Find(data_id); // Start the edit operation on the current row in
// the 'drivvers' table within the dataset.
photoRow.BeginEdit();
// Assign the value of the Photo if not empty
if (p_Blob.Length != 0)
{
photoRow[photo] = p_Blob;
}
// End the editing current row operation
photoRow.EndEdit(); // Update the database table 'drivers'
photoAdapter.Update(photoDataSet,tablename); }
catch(Exception e)
{
throw e;
}
} #endregion用DATASET 方式插入数据
http://blog.csdn.net/web_gus/archive/2004/10/13/134907.aspx