Exemple #1
0
 //A passthrough incase we have a globally defined connection string
 public static WebServiceResult SQLExecuteNonQuery(this DynamicDBRequest Request, string SQLSprocName)
 {
     if (RapidWrapperConfiguration.ConnectionString == null)
     {
         WebServiceResult result = new WebServiceResult();
         result.Success = false;
         result.Exceptions.Add(new Exception("No connection string specified"));
         return(result);
     }
     else
     {
         return(SQLExecuteNonQuery(Request, SQLSprocName, RapidWrapperConfiguration.ConnectionString));
     }
 }
Exemple #2
0
 public static WebServiceResult SQLExecuteMapObjectFromOutputs <T>(this DynamicDBRequest Request, T ResultObject, string SQLSprocName, SpecialPropertyMapper FunctionMap = null)
 {
     if (RapidWrapperConfiguration.ConnectionString == null)
     {
         WebServiceResult result = new WebServiceResult();
         result.Success = false;
         result.Exceptions.Add(new Exception("No connection string specified"));
         return(result);
     }
     else
     {
         return(SQLExecuteMapObjectFromOutputs(Request, ResultObject, SQLSprocName, RapidWrapperConfiguration.ConnectionString, FunctionMap));
     }
 }
Exemple #3
0
        public static WebServiceResult SQLExecuteNonQuery(this DynamicDBRequest Request, string SQLSprocName, string DBConnectionString)
        {
            WebServiceResult Result = new WebServiceResult();

            SprocSchema Schema = null;

            RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);

            using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CompleteSQLRequest(Request, ref Schema);

                try
                {
                    cmd.ExecuteNonQuery();

                    try
                    {
                        foreach (SprocParam outparam in Schema.OutputParamsList)
                        {
                            Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
                        }
                        if (Schema.ReturnValue != null)
                        {
                            Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
                        }
                    }
                    catch (Exception ex)
                    {
                        Result.Exceptions.Add(ex);
                        Result.Success = false;
                        Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
                        return(Result);
                    }
                }
                catch (Exception ex)
                {
                    Result.Exceptions.Add(ex);
                    Result.Success = false;
                    Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
                    return(Result);
                }
            }

            RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
            Result.Success = true;
            return(Result);
        }
Exemple #4
0
 public static WebServiceResult SQLExecuteQuery <T>(this DynamicDBRequest Request, IList <T> ObjectResultList, string SQLSprocName, SpecialPropertyMapper FunctionMap = null)
 {
     ObjectResultList = ObjectResultList ?? (IList <T>)Activator.CreateInstance(typeof(IList <T>));
     if (RapidWrapperConfiguration.ConnectionString == null)
     {
         WebServiceResult result = new WebServiceResult();
         result.Success = false;
         result.Exceptions.Add(new Exception("No connection string specified"));
         return(result);
     }
     else
     {
         return(SQLExecuteQuery(Request, ObjectResultList, SQLSprocName, RapidWrapperConfiguration.ConnectionString, FunctionMap));
     }
 }
Exemple #5
0
        //This is the core bread and butter for creating a sql request dynamically
        internal static void CompleteSQLRequest(this SqlCommand Command, DynamicDBRequest Request, ref SprocSchema Schema)
        {
            //We have a cached schema to work with
            if (Schema != null && (Schema.InputParamsList.Count != 0 || Schema.OutputParamsList.Count != 0))
            {
                foreach (SprocParam param in Schema.InputParamsList)
                {
                    SqlParameter sqlparam = new SqlParameter()
                    {
                        ParameterName = param.ParameterName, Value = Request.GetDBParameterValue(param.ParameterName), Direction = param.Direction, SqlDbType = param.Type
                    };
                    if (param.Size != 0)
                    {
                        sqlparam.Size = param.Size;
                    }
                    Command.Parameters.Add(sqlparam);
                }

                if (Schema.ReturnValue != null)
                {
                    SqlParameter sqlparam = new SqlParameter()
                    {
                        ParameterName = Schema.ReturnValue.ParameterName, Direction = Schema.ReturnValue.Direction, SqlDbType = Schema.ReturnValue.Type
                    };
                    if (Schema.ReturnValue.Size != 0)
                    {
                        sqlparam.Size = Schema.ReturnValue.Size;
                    }
                    Command.Parameters.Add(sqlparam);
                }

                foreach (SprocParam param in Schema.OutputParamsList)
                {
                    SqlParameter sqlparam = new SqlParameter()
                    {
                        ParameterName = param.ParameterName, Size = param.Size, Direction = param.Direction, SqlDbType = param.Type
                    };
                    if (param.Size != 0)
                    {
                        sqlparam.Size = param.Size;
                    }
                    Command.Parameters.Add(sqlparam);
                }
            }
            else //Get the definition and build the cache from the DB
            {
                Schema = new SprocSchema();

                SqlCommandBuilder.DeriveParameters(Command);

                for (int i = 0; i < Command.Parameters.Count; i++)
                {
                    SqlParameter p = Command.Parameters[i];
                    if (p.Direction == System.Data.ParameterDirection.Input)
                    {
                        SprocParam inparam = new SprocParam {
                            ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
                        };

                        object ParamValue = Request.GetDBParameterValue(p.ParameterName);
                        if (ParamValue != null)
                        {
                            p.Value = ParamValue;
                        }
                        Schema.InputParamsList.Add(inparam);
                    }
                    else
                    {
                        if (p.Direction == System.Data.ParameterDirection.InputOutput)
                        {
                            p.Direction = System.Data.ParameterDirection.Output;
                        }
                        if (p.Direction == ParameterDirection.ReturnValue)
                        {
                            Schema.ReturnValue = new SprocParam {
                                ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
                            }
                        }
                        ;
                        else
                        {
                            Schema.OutputParamsList.Add(new SprocParam {
                                ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
                            });
                        }
                    }
                }
            }
        }
Exemple #6
0
        public static WebServiceResult SQLExecuteQuery <T>(this DynamicDBRequest Request, IList <T> ObjectResultList, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
        {
            //TODO: error handeling if all this info is fubar
            //get the types of the item in the result list and the request
            Type             type   = ObjectResultList.GetType().GetGenericArguments()[0];
            WebServiceResult Result = new WebServiceResult();

            //load in a schema if we have one
            SprocSchema Schema = null;

            RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);

            using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                //this call completes the SqlCommand by either filling in the info from the cached schema for the params,
                //or calling the DB to pull down meta data, create the params, and then create them in the cache for use
                //after the first call
                cmd.CompleteSQLRequest(Request, ref Schema);


                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        //do while reader.nextresult for multi record sets.  We can rebuild this
                        while (reader.HasRows && reader.Read())
                        {
                            try
                            {
                                //create a new type in the list and map the data to it.
                                var ListObject = Activator.CreateInstance(type);
                                Helpers.MapDataReaderToObject(ListObject, reader, ref Schema, FunctionMap, Result);
                                ObjectResultList.Add((T)ListObject);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
                                Result.Success = false;
                                Result.Exceptions.Add(ex);
                                return(Result);
                            }
                        }
                    }
                    try
                    {
                        //map the output params
                        foreach (SprocParam outparam in Schema.OutputParamsList)
                        {
                            Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
                        }
                        if (Schema.ReturnValue != null)
                        {
                            Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
                        Result.Success = false;
                        Result.Exceptions.Add(ex);
                        return(Result);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
                    Result.Success = false;
                    Result.Exceptions.Add(ex);
                    return(Result);
                }
            }
            //save the cache to the metafile cache.
            RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
            Result.Success = true;
            return(Result);
        }
Exemple #7
0
        public static WebServiceResult SQLExecuteMapObjectFromXML <T>(this DynamicDBRequest Request, T ResultObject, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
        {
            WebServiceResult Result = new WebServiceResult();

            ResultObject = Activator.CreateInstance <T>();

            SprocSchema Schema = null;

            RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);

            using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CompleteSQLRequest(Request, ref Schema);

                try
                {
                    cmd.ExecuteNonQuery();

                    try
                    {
                        if (Schema.Serializer != null && Schema.XMLOutParam != null)
                        {
                            ResultObject = (T)Helpers.DeserializeDatabaseXML((SqlXml)cmd.Parameters[Schema.XMLOutParam.ParameterName].Value, Schema.Serializer);
                        }
                        else
                        {
                            foreach (SprocParam outparam in Schema.OutputParamsList)
                            {
                                if (outparam.Type == SqlDbType.Xml)
                                {
                                    Schema.Serializer  = new XmlSerializer(typeof(T));
                                    Schema.XMLOutParam = outparam;
                                    ResultObject       = (T)Helpers.DeserializeDatabaseXML((SqlXml)cmd.Parameters[Schema.OutputParamsList[0].ParameterName].SqlValue, Schema.Serializer);
                                }
                                else
                                {
                                    PropertyMap pmap = new PropertyMap();
                                    pmap.ParameterName = outparam.ParameterName;
                                    pmap.PropertyName  = outparam.PropertyName;
                                    Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
                                    Schema.PropertyMapList.Add(pmap);
                                }
                            }
                            if (Schema.ReturnValue != null)
                            {
                                Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Result.Exceptions.Add(ex);
                        Result.Success = false;
                        Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
                        return(Result);
                    }
                }
                catch (Exception ex)
                {
                    Result.Exceptions.Add(ex);
                    Result.Success = false;
                    Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
                    return(Result);
                }
            }

            RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
            Result.Success = true;
            return(Result);
        }
Exemple #8
0
        public static WebServiceResult SQLExecuteMapObjectFromOutputs <T>(this DynamicDBRequest Request, T ResultObject, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
        {
            WebServiceResult Result = new WebServiceResult();

            ResultObject = Activator.CreateInstance <T>();

            SprocSchema Schema = null;

            RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);

            using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CompleteSQLRequest(Request, ref Schema);

                try
                {
                    cmd.ExecuteNonQuery();

                    try
                    {
                        if (Schema.PropertyMapList.Count > 0)
                        {
                            foreach (PropertyMap pmap in Schema.PropertyMapList)
                            {
                                if (pmap.PropInfo != null)
                                {
                                    Helpers.MapValue(ResultObject, cmd.Parameters[pmap.ParameterName].Value, pmap, FunctionMap, Result);
                                }
                                else
                                {
                                    Result.Add(pmap.PropertyName, cmd.Parameters[pmap.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
                                }
                            }
                        }
                        else
                        {
                            foreach (SprocParam outparam in Schema.OutputParamsList)
                            {
                                PropertyInfo pinfo = typeof(T).GetProperty(outparam.PropertyName, Helpers.BindFlags);
                                PropertyMap  pmap  = new PropertyMap();
                                pmap.ParameterName = outparam.ParameterName;
                                if (pinfo != null && pinfo.CanWrite)
                                {
                                    pmap.PropInfo = pinfo;
                                    Helpers.MapValue(ResultObject, cmd.Parameters[outparam.ParameterName].Value, pmap, FunctionMap, Result);
                                }
                                else
                                {
                                    pmap.PropertyName = outparam.PropertyName;
                                    Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
                                }
                                Schema.PropertyMapList.Add(pmap);
                            }
                            if (Schema.ReturnValue != null)
                            {
                                Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Result.Exceptions.Add(ex);
                        Result.Success = false;
                        Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
                        return(Result);
                    }
                }
                catch (Exception ex)
                {
                    Result.Exceptions.Add(ex);
                    Result.Success = false;
                    Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
                    return(Result);
                }
            }

            RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
            Result.Success = true;
            return(Result);
        }