コード例 #1
0
ファイル: QueryHelper.cs プロジェクト: cgabilla/cKarp
        internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime)
        {
            DateTime started = DateTime.Now;
            IDataReader reader = null;
            IDbCommand cmd = null;
            bool bConnOpenedHere = EnsureConnectionOpen(conn);
            IDbTransaction transaction = conn.BeginTransaction();
            DataSet retv = new DataSet();
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandTimeout = timeout;
                cmd.Transaction = transaction;
                cmd.CommandText = cmdText;
                DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
                for (int i = 0; i < paramList.Length; i++)
                {
                    IDbDataParameter idb = cmd.CreateParameter();
                    cmd.Parameters.Add(BuildParameter(idb, paramList[i]));
                    if(reqLenTypes.Contains(idb.DbType))
                    {
                        if (idb is OdbcParameter)
                        {
                            (idb as OdbcParameter).Size = paramList[i].Size;
                        }
                        else if (idb is CtreeSqlParameter)
                        {
                            (idb as CtreeSqlParameter).Size = paramList[i].Size;
                        }
                    }
                }
                switch(connType)
                {
                    case EDtxReaderType.Adapter:
                    case EDtxReaderType.FaircomAdapter:
                        {
                            IDbDataAdapter adap = GetAdapter(connType, cmd);
                            retv.BeginInit();

                            //Since the FillSchema and Fill functions return the same instance of 'DataTable'
                            //There is probably a better way of doing this, but for sake of explination
                            //Read the db schema
                            bool bSchemaFound = false;
                            DataTable[] dta = adap.FillSchema(retv, SchemaType.Source);
                            DataTable clone = null;
                            if(dta.Length > 0)
                            {
                                bSchemaFound = true;
                                dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable'
                                retv.Tables.Remove(dta[0]); //Drop the table from the dataset
                                clone = dta[0].Clone(); //Clone the results
                                dta[0].TableName = "Table"; //Rename the datatable instance back to table
                            }
                            adap.Fill(retv); //Fill 'Table' with the actual results
                            if(bSchemaFound && clone != null)
                                retv.Tables.Add(clone); //Now add the 'schematable' back to the results

                            retv.EndInit();
                            break;
                        }
                    default:
                        {
                            DataTable dt;
                            reader = cmd.ExecuteReader();
                            if (reader.FieldCount > 0)
                            {
                                retv.Tables.Add(dt = new DataTable("Table"));
                                retv.Tables.Add(reader.GetSchemaTable());
                                switch (connType)
                                {
                                    case EDtxReaderType.FaircomReader:
                                    case EDtxReaderType.Reader:
                                        {
                                            dt.Load(reader, LoadOption.OverwriteChanges);
                                        }
                                        break;
                                    case EDtxReaderType.FaircomReaderSafe:
                                    case EDtxReaderType.ReaderSafe:
                                    default:
                                        {

                                            bool columnsBuilt = false;
                                            while (reader.Read())
                                            {
                                                if (columnsBuilt == false)
                                                {
                                                    BuildColumnData(dt, reader);
                                                    columnsBuilt = true;
                                                }
                                                AddDataRow(dt, reader);
                                            }
                                        }
                                        break;

                                }
                            }
                            break;
                        }
                }
                transaction.Commit();
                executionTime = DateTime.Now - started;
                //Now update parameter inputs with their respective values
                for (int i = 0; i < paramList.Length; i++)
                {
                    IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
                    if(p.Direction != ParameterDirection.Input)
                    {
                        paramList[i].OutValue = p.Value;
                    }
                }
            }
            catch(Exception e)
            {
                transaction.Rollback();
                executionTime = DateTime.Now - started;
                HelperFunctions.LogException(e, false);
                throw e;
            }
            finally
            {
                if(reader != null)
                {
                    if(!reader.IsClosed)
                        reader.Close();
                    reader.Dispose();
                }
                if(cmd != null)
                {
                    cmd.Dispose();
                }
                if(bConnOpenedHere)
                {
                    conn.Close();
                }
            }

            return retv;
        }
コード例 #2
0
ファイル: QueryHelper.cs プロジェクト: cgabilla/cKarp
        private static IDataParameter BuildParameter(IDataParameter dbDataParameter, ParamHelper dtxParam)
        {
            Debug.Assert(dbDataParameter != null, "DataParamter instance required");
            Debug.Assert(dtxParam != null, "DtxParam instance required.");

            dbDataParameter.Direction = dtxParam.Direction;
            dbDataParameter.ParameterName = dtxParam.Name;
            dbDataParameter.DbType = ConvertSystemTypeToDbType(dtxParam.Value.GetType());
            dbDataParameter.Value = dtxParam.Value;
            return dbDataParameter;
        }