Example #1
0
        } // update ,delete //insert

        private static ExecResult ExecuteDS(DataSItem settingItem, out DataSet ds, DbAgent datasource)
        {
            ExecResult retval = new ExecResult();

            // string cstr = datasource ?? settingItem.ConnectionString;
            //using (_conn = new SqlConnection(cstr))
            //{



            retval.StartMeasure();

            ds = new DataSet();

            SqlConnection con = null;

            if (datasource.ConnectionLevel == ConnectionLevel.Single)//&& datasource.AgentState != AgentState.Connected)
            {
                con = datasource.CreateConnection();
                con.Open();
            }
            else if (datasource.ConnectionLevel == ConnectionLevel.AllInOne)
            {
                con = datasource.Connection;
            }

            // return if is not connected
            if (con == null || con.State != ConnectionState.Open)
            {
                retval.SetCode(-2, "Not connected ! ");
                retval.StopMeashure();
                return(retval);
            }



            using (SqlCommand cmd = new SqlCommand(settingItem.Value, con))
            {
                SqlParameter param;

                cmd.CommandType    = CommandType.StoredProcedure;
                cmd.CommandTimeout = datasource.RunTimeout;
                //set transaction
                if (datasource.TransactionState == TransactionState.ActiveTransaction)
                {
                    cmd.Transaction = datasource.Transaction;
                }

                foreach (DataParam item in settingItem.Params.Values)
                {
                    param = new SqlParameter();
                    param.ParameterName = item.Name;
                    param.Value         = item.Value;
                    param.SqlDbType     = SettingsHelperManager.DetermineSqlDbTYpe(item.Type);
                    param.Direction     = SettingsHelperManager.GetParametrDirection(item.Direction);
                    cmd.Parameters.Add(param);
                }

                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand             = cmd;
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.Fill(ds);
                }

                //set outputparams values
                if (settingItem.HasOutputParam)
                {
                    foreach (DataParam value in settingItem.OutputParams.Values)
                    {
                        value.Value = cmd.Parameters[value.Name].Value.ToString();
                    }
                }

                if (cmd.Parameters.Count > 0)
                {
                    List <DataParam> returnparam = settingItem.GetparamsByDirection(ParamDirection.Return);

                    if (returnparam.Count > 0)
                    {
                        string name = returnparam[0].Name;


                        if (cmd.Parameters[name] != null)
                        {
                            retval.SetCode(Convert.ToInt32(cmd.Parameters[name].Value));
                        }
                    }
                }
            }
            //end of using }
            if (datasource.ConnectionLevel == ConnectionLevel.Single && con != null)
            {
                con.Dispose();
            }

            retval.StopMeashure();
            return(retval);
        }
Example #2
0
        private static ExecResult ExecuteProcedureDRByReflection <T>(DataSItem proc, out IBag <T> container, DbAgent datasource)
        {
            ExecResult result = new ExecResult();  // when user doesn't set return  parameter
            SqlCommand cmd    = null;

            container = new RefBag <T>();


            result.StartMeasure();
            try
            {
                // create and open a connection object "Data Source=FARID-PC;Initial Catalog=InsuranceFactory;Integrated Security=True"
                //using (_conn = new SqlConnection(constr))
                //{

                SqlConnection con = null;
                if (datasource.ConnectionLevel == ConnectionLevel.Single)//&& datasource.AgentState != AgentState.Connected)
                {
                    con = datasource.CreateConnection();
                    con.Open();
                }
                else if (datasource.ConnectionLevel == ConnectionLevel.AllInOne)
                {
                    con = datasource.Connection;
                }

                // return if is not connected
                if (con == null || con.State != ConnectionState.Open)
                {
                    result.SetCode(-2, "Not connected ! ");
                    result.StopMeashure();
                    return(result);
                }

                cmd = new SqlCommand(proc.Value, con)
                {
                    CommandType    = CommandType.StoredProcedure,
                    CommandTimeout = datasource.RunTimeout
                };
                SqlParameter param;

                //set transaction
                if (datasource.TransactionState == TransactionState.ActiveTransaction)
                {
                    cmd.Transaction = datasource.Transaction;
                }

                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var item in proc.Params.Values)
                {
                    param = new SqlParameter
                    {
                        ParameterName = item.Name,
                        Value         = item.Value,
                        SqlDbType     = SettingsHelperManager.DetermineSqlDbTYpe(item.Type),
                        Direction     = SettingsHelperManager.GetParametrDirection(item.Direction)
                    };
                    cmd.Parameters.Add(param);
                }
                _reader = cmd.ExecuteReader();
                while (_reader.Read())
                {
                    container.SetFromReader(ref _reader);
                }
                // } end of using
                if (datasource.ConnectionLevel == ConnectionLevel.Single && con != null)//&& con.State!=ConnectionState.Closed)
                {
                    con.Dispose();
                }

                //set outputparams values
                if (proc.HasOutputParam)
                {
                    foreach (DataParam value in proc.OutputParams.Values)
                    {
                        value.Value = cmd.Parameters[value.Name].Value.ToString();
                    }
                }

                if (cmd.Parameters.Count > 0)
                {
                    List <DataParam> ret = proc.GetparamsByDirection(ParamDirection.Return);
                    if (ret.Count > 0)
                    {
                        if (cmd.Parameters[ret[0].Name] != null)
                        {
                            result.SetCode(Convert.ToInt32(cmd.Parameters[proc.GetparamsByDirection(ParamDirection.Return)[0].Name].Value));
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                throw exc;
            }

            result.StopMeashure();
            return(result);
        }