using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Data.OracleClient;
using System.Data.SqlClient;
namespace tjsj._class
{
/// <summary>
/// dataManage 的摘要说明。
/// </summary>
public class dataManage
{
public string connString;//连接字符串
public OracleConnection myConnection;//连接connection对象
public OracleCommand myCommand;//command 对象
public OracleDataReader myDataRead;//dataread对象
public DataSet myDataSet= new DataSet();//dataset 对象
public OracleCommandBuilder myOracleCommandBuilder;//连接data 和dataset连接的桥梁
public OracleDataAdapter myOracleDataAdapter;
/// <summary>
/// 连接支付串的建立,已经重载
/// </summary>
public dataManage()
{
connString= ConfigurationSettings.AppSettings["ConnMe"];
}//sub
public dataManage(string P_Conn)
{
connString= ConfigurationSettings.AppSettings[P_Conn];
}//sub
/// <summary>
/// 形成dataRead对象,使用形成的DataRead 对象时调用 myDataRead。
/// </summary>
/// <param name="P_StrSql">需要执行sql的语句</param>
public void SetDataRead(string P_StrSql)
{
myConnection = new OracleConnection(connString);
myCommand = new OracleCommand(P_StrSql,myConnection);
myConnection.Open();
myDataRead = myCommand.ExecuteReader();
}
/// <summary>
/// 预计连续形成dataRead对象,使用形成的DataRead 对象时调用 myDataRead,使用前需要调用对象的setCommandSql()函数。
/// </summary>
/// <param name="P_StrSql"></param>
public void SetDataRead_more(string P_StrSql)
{
myCommand = new OracleCommand(P_StrSql,myConnection);
myDataRead = myCommand.ExecuteReader();
}
/// <summary>
/// 执行不需要返回结果的sql 语句
/// </summary>
/// <param name="P_StrSql"> 需要执行sql的语句</param>
public void setCommandSql(string P_StrSql)
{
myConnection = new OracleConnection(connString);
myCommand = new OracleCommand(P_StrSql,myConnection);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
/// <summary>
/// 执行不需要返回结果的sql语句的准备工作.
/// </summary>
public void setCommandSql()
{
myConnection = new OracleConnection(connString);
myConnection.Open();
}
/// <summary>
/// 执行多条不需要返回结果的sql语句.是否需要关闭连接
/// </summary>
/// <param name="P_StrSql">需要执行的sql语句</param>
/// <param name="P_tf">true关闭数据连接 false不关闭连接</param>
public void setCommandSql(string P_StrSql ,bool P_tf)
{
myCommand = new OracleCommand(P_StrSql,myConnection);
myCommand.ExecuteNonQuery();
if (P_tf)
myConnection.Close();
}
/// <summary>
/// 调用方法是 使用 myDataSet 加 P_StrTable的名称
/// </summary>
/// <param name="P_strSql">需要执行sql的语句</param>
/// <param name="P_StrTable">形成的dataset里的datatable的名称</param>
public void SetDataSet(string P_strSql,string P_StrTable)
{
myConnection = new OracleConnection(connString);
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = new OracleCommand(P_strSql, myConnection);
adapter.Fill(myDataSet,P_StrTable);
}
/// <summary>
/// 准备一个dataset和数据库的连接,使用方法是 调用 myDataSet 修改数据后 调用 setDataSetBackDataSave。
/// </summary>
/// <param name="P_strsql"></param>
/// <param name="P_strTable"></param>
public void setDataSetBackDataPre(string P_strsql,string P_strTable)
{
myConnection = new OracleConnection(connString);
myOracleDataAdapter = new OracleDataAdapter(P_strsql, myConnection);
myOracleCommandBuilder = new OracleCommandBuilder(myOracleDataAdapter);
myOracleDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myConnection.Open();
myOracleDataAdapter.FillSchema(myDataSet, SchemaType.Source, P_strTable);
}
public void setDataSetBackData(string p_table,string p_where,string p_coloun_value )
{
string i_old,i_have,i_coloun="";
int i_count;
i_old=p_coloun_value;
//Hashtable iht= new Hashtable();
ArrayList ial_key= new ArrayList();
ArrayList ial_text= new ArrayList();
do
{
i_count=i_old.IndexOf("#");
i_have=i_old.Substring(0,i_count);
i_old=i_old.Substring(i_count+1);
i_count=i_have.IndexOf(":");
if(i_old.Length >1)
{
i_coloun += i_have.Substring(0,i_count) + ",";
ial_key.Add(i_have.Substring(0,i_count).Trim());
ial_text.Add(i_have.Substring(i_count+1));
}
else
{
i_coloun += i_have.Substring(0,i_count) + "";
ial_key.Add(i_have.Substring(0,i_count).Trim());
ial_text.Add(i_have.Substring(i_count+1));
}
}
while (i_old.Length >1);
string isql="select "+ i_coloun +" from "+p_table+" where " + p_where;
string isql_del="delete "+p_table+" where " + p_where;
setCommandSql(isql_del);
setDataSetBackDataPre(isql,"i_0101");
//myDataSet.Tables["i_0101"].Rows.Clear();
DataRow idr;
idr = myDataSet.Tables["i_0101"].NewRow();
for (int i =0;i<ial_key.Count;i++)
{
idr[ial_key[i].ToString().Trim()]=ial_text[i].ToString().Trim();
}
myDataSet.Tables["i_0101"].Rows.Add(idr);
setDataSetBackDataSave("i_0101");
}
/// <summary>
/// 保存myDataSet连接的数据库。
/// </summary>
/// <param name="P_strTable">myDataSet 里的表名</param>
public void setDataSetBackDataSave(string P_strTable)
{
myOracleDataAdapter.Update(myDataSet, P_strTable);
myConnection.Close() ;
}
/// <summary>
/// 处理存储过程并返回参数。
/// </summary>
/// <param name="P_procedureName">procedurename存储过程名</param>
/// <param name="P_proDimName">参数列表,每个参数是一个hashtable</param>
public void setprocude(ArrayList P_proDimName ,string P_procedureName)
{
int i;
Hashtable ith = new Hashtable();
string idimName="",idimKind="",idimIO="",idimsize="";
myConnection = new OracleConnection(connString);
myCommand=new OracleCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = P_procedureName.Trim();
myCommand.CommandType = CommandType.StoredProcedure;
for(i=0;i<P_proDimName.Count ;i++)
{
ith=(Hashtable)P_proDimName[i];
idimName=ith["name"].ToString();
idimKind=ith["kind"].ToString();
idimIO=ith["io"].ToString();
if (idimKind.Trim().Equals("varchar"))
{
idimsize=ith["size"].ToString();
switch (idimIO.Trim())
{
case "in":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.Input;
break;
case "out":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.Output;
break;
case "inout":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.InputOutput;
break;
}
}//if
else
{
switch (idimIO.Trim())
{
case "in":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.Input;
break;
case "out":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.Output;
break;
case "inout":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.InputOutput;
break;
}
}//if_else
}//for
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
/// <summary>
/// 处理存储过程并返回参数。
/// </summary>
/// <param name="P_procedureName">procedurename存储过程名</param>
/// <param name="P_proDimName">参数列表,每个参数是一个hashtable</param>
public void setProcude_DataSet(ArrayList P_proDimName ,string P_procedureName)
{
int i;
Hashtable ith = new Hashtable();
string idimName="",idimKind="",idimIO="",idimsize="";
ArrayList ial= new ArrayList();
myConnection = new OracleConnection(connString);
myCommand=new OracleCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = P_procedureName.Trim();
myCommand.CommandType = CommandType.StoredProcedure;
for(i=0;i<P_proDimName.Count ;i++)
{
ith=(Hashtable)P_proDimName[i];
idimName=ith["name"].ToString();
idimKind=ith["kind"].ToString();
idimIO=ith["io"].ToString();
switch (idimKind.Trim())
{
case "varchar":
idimsize=ith["size"].ToString();
switch (idimIO.Trim())
{
case "in":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.Input;
break;
case "out":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.Output;
break;
case "inout":
myCommand.Parameters.Add(idimName, OracleType.VarChar, int.Parse(idimsize)).Direction =ParameterDirection.InputOutput;
break;
}
break;
case "number":
switch (idimIO.Trim())
{
case "in":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.Input;
break;
case "out":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.Output;
break;
case "inout":
myCommand.Parameters.Add(idimName, OracleType.Number).Direction =ParameterDirection.InputOutput;
break;
}
break;
case "cursor":
switch (idimIO.Trim())
{
case "in":
myCommand.Parameters.Add(idimName, OracleType.Cursor).Direction =ParameterDirection.Input;
break;
case "out":
ial.Add(idimName);
myCommand.Parameters.Add(idimName, OracleType.Cursor).Direction =ParameterDirection.Output;
break;
case "inout":
myCommand.Parameters.Add(idimName, OracleType.Cursor).Direction =ParameterDirection.InputOutput;
break;
}
break;
}//switch
}//for
myOracleDataAdapter= new OracleDataAdapter(myCommand);
for (i=0;i<ial.Count;i++)
{
if (i==0)
myOracleDataAdapter.TableMappings.Add("Table",ial[i].ToString().Trim());
else
myOracleDataAdapter.TableMappings.Add("Table" + i.ToString().Trim(),ial[i].ToString().Trim());
}
myOracleDataAdapter.Fill(myDataSet);
}
/// <summary>
///
/// </summary>
public void setprocude()
{
OracleConnection conn = new OracleConnection(connString);
// define the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";
// add the parameters including the two REF CURSOR types to retrieve
// the two result sets
cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =
ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
// create the DataAdapter and map tables
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "EMPLOYEES");
da.TableMappings.Add("Table1", "JOBS");
// create and fill the DataSet
da.Fill(myDataSet);
// output the second employee (zero-based array) and job title
// based on the relation
// Console.WriteLine("Employee ID: " +
// ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +
// "; Job Title: " +
// ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(
// "EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);
}
}
}