Typed Query

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace SP_Test_In_Out {
 
    public class Test {
 
        static void Main(string[] args) {
 
            #region Turn on global debugging features
 
            //Set up to QueryPerformed event to show queries executing in real time. Debugging feature.
            Sql.Settings.QueryPerformed += new Sql.Settings.QueryPerformedDelegate(QueryPerformed);
            Sql.Settings.UseParameters = false; //Turn off parameters so queries are easier to read.
            Sql.Settings.ReturnResultSize = true;
 
            #endregion
 
            using(Sql.Transaction transaction = new Sql.Transaction(MyDatabase.INSTANCE)) {
 
                Proc proc = Proc.INSTANCE;  //Get instance of stored procedure object
 
                int inParam = 25;
                int outParam = 0;   //Note: Out parameter
 
                Sql.IResult result = proc.Execute(inParam, ref outParam, transaction);  //Execute stored procedure and get result back.
 
                for(int index = 0; index < result.Count; index++) {
 
                    Row row = proc[index, result];
 
                    Guid id = row.Id;
                    int intValue = row.IntValue;
                }
 
                transaction.Commit();
            }
        }
 
        private static void QueryPerformed(string pSql, int pRows, Sql.QueryType pQueryType, DateTime? pStart, DateTime? pEnd, Exception pException, IsolationLevel pIsolationLevel, int? pResultSize, ulong? pTransactionId) {
            System.Console.WriteLine(pSql);
        }
    }
/*
--
--  Sql to create stored procedure
--
CREATE TABLE IntTable (
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    IntValue INTEGER NOT NULL   
);
GO
CREATE PROCEDURE SP_Test_In_Out (@In_param INTEGER, @Out_param INTEGER OUT)AS
    SET @Out_param = @In_param;
     
    DELETE FROM IntTable;
     
    INSERT INTO IntTable (Id, IntValue) VALUES(NEWID(), 12345);
    INSERT INTO IntTable (Id, IntValue) VALUES(NEWID(), 123456);
    INSERT INTO IntTable (Id, IntValue) VALUES(NEWID(), 1234567);
    INSERT INTO IntTable (Id, IntValue) VALUES(NEWID(), 12345678);
     
    SELECT Id, IntValue FROM dbo.IntTable ORDER BY IntValue ASC;
 
GO
*/
    //
    //  Generated stored procedure definitions
    //
    public sealed class Proc : Sql.AStoredProc {
 
        public static readonly Proc INSTANCE = new Proc();
 
        public readonly Sql.Column.GuidColumn Id;
        public readonly Sql.Column.IntegerColumn IntValue;
 
        public Proc()
            : base(MyDatabase.INSTANCE, "SP_Test_In_Out", typeof(Row)) {
 
            Id = new Sql.Column.GuidColumn(this, "Id", false);
            IntValue = new Sql.Column.IntegerColumn(this, "IntValue", false);
 
            AddColumns(Id, IntValue); //-->Note: Columns can be added to proc in the same way as a table definition.
        }
 
        public Sql.IResult Execute(int @In_param, ref int @Out_param, Sql.Transaction pTransaction) {
 
            SqlParameter p0 = new SqlParameter("@In_param", SqlDbType.Int);
            p0.Direction = ParameterDirection.Input;
            p0.Value = @In_param;
 
            SqlParameter p1 = new SqlParameter("@Out_param", SqlDbType.Int);
            p1.Direction = ParameterDirection.InputOutput;
            p1.Value = @Out_param;
 
            Sql.IResult result = ExecuteProcedure(pTransaction, p0, p1);
 
            @Out_param = (int)p1.Value;
            return result;
        }
 
        public Row this[int pIndex, Sql.IResult pResult] {
            get { return (Row)pResult.GetRow(this, pIndex); }
        }
    }
 
    public sealed class Row : Sql.ARow {
 
        private new Proc Tbl {
            get { return (Proc)base.Tbl; }
        }
 
        public Guid Id {
            get { return Tbl.Id.ValueOf(this); }
            set { Tbl.Id.SetValue(this, value); }
        }
 
        public int IntValue {
            get { return Tbl.IntValue.ValueOf(this); }
            set { Tbl.IntValue.SetValue(this, value); }
        }
 
        public Row()
            : base(Proc.INSTANCE) {
        }
    }
 
    //
    //Database definition class. Note only one instance is required in your application
    //
    public class MyDatabase : Sql.ADatabase {
 
        public readonly static Sql.ADatabase INSTANCE = new MyDatabase();
 
        private MyDatabase() : base("<<db>>", Sql.DatabaseType.Mssql) {
        }
 
        protected override string ConnectionString {
            get {   //*** Note: Connection details here ***//
                return "Data Source=localhost\\SQLEXPRESS;User Id=login;Password=password;database=db_name;";
            }
        }
 
        public override System.Data.Common.DbConnection GetConnection(bool pCanBeReadonly) {
            lock(this) {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                return connection;
            }
        }
    }
}