C#程序和oracle
别人写的一个连接oracle的程序,然后我测试能否运行,我该在oracle中创建那些数据表呢?
程序如下:using (CoreLab.Oracle.OracleConnection objConn = new CoreLab.Oracle.OracleConnection(ConfigurationManager.AppSettings["connectionString"]))
{
CoreLab.Oracle.OracleTransaction trans = null;
try
{
objConn.Open()
trans = objConn.BeginTransaction();
string sql = "select DataID from system.SSJ_Data where ServerName=:ServerName and FileName=:FileName";
CoreLab.Oracle.OracleCommand objCmd = objConn.CreateCommand();
objCmd.Transaction = trans;
objCmd.CommandText = sql;
objCmd.CommandType = CommandType.Text;
objCmd.Parameters.Add(":ServerName", ServerName);
objCmd.Parameters.Add(":FileName", Path.GetFileName(p));
//CoreLab.Oracle.OracleDataReader objDR = objCmd.ExecuteReader();
int dataID = int.MinValue;
object objResult = null;
objResult = objCmd.ExecuteScalar();
if (objResult != DBNull.Value && objResult != null)
{
dataID = int.Parse(objResult.ToString()); }
objCmd.Parameters.Clear();
bool isInserted = false;
if (dataID == int.MinValue)
{
sql = "select ssj_id.nextval as DataID from dual";
objCmd.CommandText = sql;
dataID = int.Parse(objCmd.ExecuteScalar().ToString());
sql = @"INSERT INTO system.SSJ_Data (DataID,PartNo, ServerID,ServerName,FileName,UploadTime,GIF1,GIF2,JPG1,JPG2,JPG3,JPG4,JPG5,WORD,FCT1,FCT2,FCT3,FCTLV1,FCTLV2,FCTLV3,FCT4,FCT5,FCT6,FCT7,FCT8,FCT9,FCT10,CHAOCHANUM,CHAOCHARATE,DJ,CV,SK,UU,ISOK) VALUES (:DataID,:PartNo,:ServerID,:ServerName,:Filename,sysdate,:GIF1,:GIF2,:JPG1,:JPG2,:JPG3,:JPG4,:JPG5,:WORD,:FCT1,:FCT2,:FCT3,:FCTLV1,:FCTLV2,:FCTLV3,:FCT4,:FCT5,:FCT6,:FCT7,:FCT8,:FCT9,:FCT10,:CHAOCHANUM,:CHAOCHARATE,:DJ,:CV,:SK,:UU,:ISOK)";
isInserted = true; }
else
{
sql = @"update system.SSJ_Data set PartNo=:PartNO, ServerID=:ServerID,ServerName=:ServerName,FileName=:FileName,UploadTime=sysdate,GIF1=:GIF1,GIF2=:GIF2,JPG1=:JPG1,JPG2=:JPG2,JPG3=:JPG3,JPG4=:JPG4,JPG5=:JPG5,WORD=:WORD,FCT1=:FCT1,FCT2=:FCT2,FCT3=:FCT3,FCTLV1=:FCTLV1,FCTLV2=:FCTLV2,FCTLV3=:FCTLV3,FCT4=:FCT4,FCT5=:FCT5,FCT6=:FCT6,FCT7=:FCT7,FCT8=:FCT8,FCT9=:FCT9,FCT10=:FCT10,CHAOCHANUM=:CHAOCHANUM,CHAOCHARATE=:CHAOCHARATE,DJ=:DJ,CV=:CV,SK=:SK,UU=:UU,ISOK=:ISOK where DataID=:DataID";
}
if (string.IsNullOrEmpty(sql))
{
ServerLog(this, string.Concat("由于无法查询序列号ssj_id,保存文件失败:", p)); }
else
{
objCmd.Parameters.Add(":DataID", dataID);
objCmd.Parameters.Add(":PartNO", partNO);
objCmd.Parameters.Add(":ServerID", ServerID);
objCmd.Parameters.Add(":ServerName", ServerName);
objCmd.Parameters.Add(":FileName", Path.GetFileName(p));
objCmd.Parameters.Add(":GIF1", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("gif1") ? fileBufers["gif1"] : null);
objCmd.Parameters.Add(":GIF2", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("gif2") ? fileBufers["gif2"] : null);
objCmd.Parameters.Add(":JPG1", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("jpg1") ? fileBufers["jpg1"] : null);
objCmd.Parameters.Add(":JPG2", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("jpg2") ? fileBufers["jpg2"] : null);
objCmd.Parameters.Add(":JPG3", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("jpg3") ? fileBufers["jpg3"] : null);
objCmd.Parameters.Add(":JPG4", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("jpg4") ? fileBufers["jpg4"] : null);
objCmd.Parameters.Add(":JPG5", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("jpg5") ? fileBufers["jpg5"] : null);
objCmd.Parameters.Add(":WORD", CoreLab.Oracle.OracleDbType.Blob).Value = (fileBufers.ContainsKey("word") ? fileBufers["word"] : null);
objCmd.Parameters.Add(":FCT1", s0[0]);
objCmd.Parameters.Add(":FCT2", s1[0]);
objCmd.Parameters.Add(":FCT3", s2[0]);
objCmd.Parameters.Add(":FCTLV1", s[3]);
objCmd.Parameters.Add(":FCTLV2", s[4]);
objCmd.Parameters.Add(":FCTLV3", s[5]);
objCmd.Parameters.Add(":FCT4", s8[0]);
objCmd.Parameters.Add(":FCT5", s9[0]);
objCmd.Parameters.Add(":FCT6", s10[0]);
objCmd.Parameters.Add(":FCT7", s11[0]);
objCmd.Parameters.Add(":FCT8", s12[0]);
objCmd.Parameters.Add(":FCT9", s13[0]);
objCmd.Parameters.Add(":FCT10", s14[0]);
objCmd.Parameters.Add(":CHAOCHANUM", s15[0]);
objCmd.Parameters.Add(":CHAOCHARATE", s16[0]);
objCmd.Parameters.Add(":DJ", s17[0]);
objCmd.Parameters.Add(":CV", s18[0]);
objCmd.Parameters.Add(":SK", s19[0]);
objCmd.Parameters.Add(":UU", s20[0]);
objCmd.Parameters.Add(":ISOK", s21[0]);
objCmd.CommandText = sql;
int modifiedCount = objCmd.ExecuteNonQuery();
objCmd.Parameters.Clear();
if (modifiedCount == 1)
{
//保存成功、接着保存子表数据
StringBuilder sqlBuffer = new StringBuilder();
if (!isInserted)
{
sql = string.Concat("delete system.SSJ_SData where DataID=", dataID);
objCmd.CommandText = sql;
modifiedCount = objCmd.ExecuteNonQuery(); }
sql = @"insert into system.SSJ_SData values(sdata_id.nextval,:dataID,:NOi,:x1,:y1,:z1,:x2,:y2,:z2,:err)";
objCmd.CommandText = sql;
objCmd.Parameters.Add(":dataID", dataID);
objCmd.Parameters.Add(":NOi", (Int32)0);
objCmd.Parameters.Add(":x1", (float)0);
objCmd.Parameters.Add(":y1", (float)0);
objCmd.Parameters.Add(":z1", (float)0);
objCmd.Parameters.Add(":x2", (float)0);
objCmd.Parameters.Add(":y2", (float)0);
objCmd.Parameters.Add(":z2", (float)0);
objCmd.Parameters.Add(":err", (float)0);
int RowNO = 1;
for (int i = 0; i < dataList.Count; i += 7)
{
objCmd.Parameters["NOi"].Value = RowNO++;
objCmd.Parameters["x1"].Value = dataList[i];
objCmd.Parameters["y1"].Value = dataList[i + 3];
objCmd.Parameters["z1"].Value = dataList[i + 5];
objCmd.Parameters["x2"].Value = dataList[i + 1];
objCmd.Parameters["y2"].Value = dataList[i + 4];
objCmd.Parameters["z2"].Value = dataList[i + 6];
objCmd.Parameters["err"].Value = dataList[i + 2];
modifiedCount = objCmd.ExecuteNonQuery();
}
ServerLog(this, "上传数据成功!");