SProcedureBasedPattern

This project is useful for developers who use MS SQL as their DataBase. If during the work you need to execute a lot of Stored Procedures then possibly you need convenient tool for that.

Overview

By means of this library you can get the list of all Stored Procedures from DataBase. If you want execute one of them you should Load it and provide input parameters. After that SP is executed. In the context of OutPut, SP can be executed in 4 different ways: return set of rows, return value, return nothing (ExecuteNonQuery), or you can MAP returned object to a class.

Usage

Just add SPBP.DLL to your project. It may be Console, Web or Desktop application.

Using example

Here is explanation of Console application code.

First of all you need to declare a DbAgent variable:

DbAgent agent = new DbAgent(<name>, <connection string>.<state>);

(If agent state isn't true it will throw an exception in procedure execution . )

Then get the all stored procedures existing in that DataBase:

ProcedureFactory fact = SqlManager.GetProceduresFactory(agent);

Select the Procedure:

DataSItem procedure = fact.Procedures["procedurename"];

Set values to parameters if procedure needs :

  //fill params  values 
                    foreach (DataParam param in procedure.Params.Values)
                    {
                        if (param.Direction == ParamDirection.Input)
                        {
                            Console.Write(param.Name + " : ");
                            param.Value = Console.ReadLine();
                        }

                    }

Get output params after execution :

If procedure has output params it will be added to DsItem Output params .

 if (item.HasOutputParam)
                    {
                        foreach (DataParam prm in item.OutputParams.Values)
                        {
                            Console.WriteLine(prm.Name + " = " + prm.Value);
                        }
                    }

Get marked class collection :

IBag<Item> items;
ExecResult result = procedure.ExecuteDataReaderByRef(agent, out items);

OR

DataSet set = new DataSet();
procedure.ExecDataSet(agent, out set);

Code sample

Examples of Procedures (has return value , output params , without rows , etc )

1 . Add Employee stored procedure .  As we can see from the picture 1  it has  return param and  input params . 

Picture1

2 . Delete Employee . It doesn't return any rows , but has return parameter .

Picture 2

3. Test procedure . Has return param , output params , and return rows

Picture 3

Execution Sample

First Step

DbAgent agent = new DbAgent("Employees", datasource, true); //create db agent 
ProcedureFactory fact = SqlManager.GetProceduresFactory(agent);  
fact .AddReturnValueToEachProcedure();
/*add return parametr to all procedure (by default the procedures doesn't have  return parameter )*/

Execute AddEmployee

 DataSItem item = fact.Procedures[proc]; //select target procedure (AddEmployee)
 //fill input params  values 
 	 foreach (DataParam param in item.Params.Values)
                    {
                        if (param.Direction == ParamDirection.Input)
                        {
                            Console.Write(param.Name + " : ");
                            param.Value = Console.ReadLine();
                        }

                    }

Execute equiped procedure

//execute procedure 
ExecResult res = DBCommander.ExecuteNonQueryProcedure(agent, item);

or

 ExecResult res = item .ExecuteNonQuery(agent); //by the extension method 

When the procedure was executed the ExecResult get the return parameter and the time of execution . In order to display you need just use res.ToString() method .

Execute procedure in Picture 3 Procedure selection section is the same .

Execution

DataSet set ;
 ExecResult res = item .ExecDataSet(agent ,out set );

And Item willbe filled with output params .

Get List of Marked items

Overview

For example we want to get list of marked objects that we declared in our project .

How to declare marked class ?

There are 2 way to mark your class as DbObject . 1. Inherit from DbOBject and override Abstract methods in this .
2. Use Custom attributes . (DbObject attribute and ColumnName)

Inheritance Example

 public class Employe:DbObject
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public DateTime CreatedDate { get; set;  }
        public string Role { get; set;  }
        public decimal Salary { get; set;  }
        public string Department { get; set;  }

    public override void SetItemFromDb(ref SqlDataReader reader)
        {
            Id = Convert.ToInt32(reader["id"].ToString() );
            FullName = reader["FullName"].ToString();
            CreatedDate = Convert.ToDateTime(reader["CreatedDate"].ToString());
            Role = reader["Role"].ToString();
            Salary = Convert.ToDecimal(reader["Salary"].ToString());
            Department = reader["Department"].ToString();

        }

        public override DbObject CreateInstance()
        {
            return new Employe(); 
        }
    }

Execution :

IBag<Employe> employes; //get employes fro inherited class
 ExecResult result = fact.Procedures[proc].ExecDataReadByInheritance(agent,out employes);

Attributed item example

[DbObject]
    public class NewEmp
    {
        [ColumnName("id")]
        public int Id { get; set; }

        [ColumnName("FullName")]
        public string FullName { get; set; }

        [ColumnName("CreatedDate")]
        public DateTime CDate { get; set; }
        [ColumnName("Role")]
        public string Roles { get; set; }


        [ColumnName("Salary")]
        public decimal Money { get; set; }
       /* 
		if property will  be attributed like that  The property name (Department)  should be the same as  the  procedure returns .  
		*/
        [ColumnName] 
        public string Department { get; set; }
    }

Execution :

IBag<NewEmp> employes;
DataSItem item = fact.Procedures[procname];
ExecResult result = item.ExecuteDataReaderByRef(agent,out employes);

Asynchronous execution

Async Result item "ExecAsyncResult"

 public class ExecAsyncResult
    {
        public ExecResult Result { get; set; }
        public DataSItem ExecutedProcedure { get; set; } //executed procedure 
        //it can be dictionary ,  data set  , and etc ... 
        public object Object { get; set; }
        private AsyncExecutionType _type = AsyncExecutionType.ExecNonQuery; // by default 
        public AsyncExecutionType ExecutionType { get { return _type; } set { _type = value; } }
        public override string ToString()
        {
            return string .Format("[{0}] - Type : {1}",Result.ToString(),ExecutionType.ToString());
        }

    }

Execution Methods: DataSet

ExecAsyncResult res = await _selectedProcedure.ExecDataSetAsync(_currentAgent);
DataSet setresult=res.Object as DataSet;

Non Query

ExecAsyncResult result = await _selectedProcedure.ExecuteNonQueryAsync(_currentAgent);

IBag Collection:

 ExecAsyncResult result = await _selectedProcedure.ExecuteDataReaderByRefAsync<Employee>(_currentAgent);

 IBag<NewEmp> employes = result.Object as IBag<Employee>;

Inheritance mode

ExecAsyncResult result = await _selectedProcedure.ExecDataReadByInheritanceAsync<Employee>(_currentAgent);

 IBag<NewEmp> employes = result.Object as IBag<Employee>;

History

DLL added

Procedure executor added

Library update(added asynchronous methods of execution )

License

Copyright 2017 Freeedy Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.