Using A Static Class As Sql Layer ?

Jun 12, 2008

Can I use this class in ASP.NET web site with many visitors?
SqlLayer.cs:
 public static class SqlLayer{
public static string ConnectionString = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString;
public static int ExecuteNonQuery(string StoredProcedureName, string[] ParamNames, object[] ParamValues) {
if (ParamNames.Length != ParamValues.Length) {
throw new Exception("ParamNames.Length != ParamValues.Length");
}
using (SqlConnection cSqlConnection = new SqlConnection(ConnectionString) {
cSqlConnection.Open();
SqlCommand cSqlCommand = cSqlConnection.CreateCommand();
cSqlCommand.CommandType = CommandType.StoredProcedure;
cSqlCommand.CommandText = StoredProcedureName;

cSqlCommand.Parameters.Add("@ReturnValue", DbType.Int32);
cSqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

for (int i = 0; i < ParamValues.Length; i++) {
cSqlCommand.Parameters.AddWithValue(ParamNames[i], ParamValues[i]);
}

cSqlCommand.ExecuteNonQuery();
return (int) cSqlCommand.Parameters["@ReturnValue"].Value;

}
}

public delegate void ActionForReader(SqlDataReader Reader);
public static void ExecuteReader(string StoredProcedureName, string[] ParamNames, object[] ParamValues, ActionForReader cActionForReader) {
using (SqlConnection SqlConnection1 = new SqlConnection(ConnectionString)) {
SqlConnection1.Open();
SqlCommand SqlCommand1 = SqlConnection1.CreateCommand();
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlCommand1.CommandText = StoredProcedureName;

for (int i = 0; i < ParamValues.Length; i++) {
SqlCommand1.Parameters.AddWithValue(ParamNames[i], (ParamValues[i] == null ? DBNull.Value : ParamValues[i]));
}
using (SqlDataReader Reader = SqlCommand1.ExecuteReader()) {
if (cActionForReader != null) {
//if (Reader.HasRows == false) throw new Exception("Reader has no rows");
//if (Reader.RecordsAffected == 0) throw new Exception("Reader RecordsAffected = 0");
while (Reader.Read()) {
if(Reader!=null) cActionForReader(Reader);
}
}
}
}
}


}
  
 
Using:
 SqlLayer.ExecuteNonQuery("SomeStoredProcedure", "ID", ID);


---------


SqlLayer.ExecuteReader("SomeStoredProcedure", new string[]{"Param1","Param2"}, new object[]{Value1,Value2}, Action);

...

void ActionForForumCollection(SqlDataReader Reader) {
LabelContent.Text += Reader[0].ToString()+" - "+Reader[1].ToString();
}  

View 1 Replies


ADVERTISEMENT

SqlDatareader Belongs To Which Class Is It Sealed Or Static Class?

Feb 21, 2008

Hai Guys,
       I have a doubt Regarding SqlDataReader
      i can able to create object to Sqlconnection,Sqlcomand etc...
     but i am unable to create object for SqlDataReader ?
     Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
   sqlDatareader belongs to which class is it sealed or static class?
can we create own class like SqldataReader .......
Reply Me ...... if any one know the answer.............. 
 

View 8 Replies View Related

Quick Q: Regarding Static DataAccess Class And Concurrency

Jan 3, 2005

Im new to SQL and DB in general.
Ive been experimenting and right now I have a 1 static dataAccess class that handles every query to the DB.

Q: Do Static classes such as a static dataAccess class need to be threadsafe as there is only one instance of these within the entire application.

View 1 Replies View Related

Msg 6573 - Method In Assembly Is Not Static - How Do I Make It Static ?

Feb 22, 2006

I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:

My dll name is 'Crystal_Reports_Test_01'
In the DLL, my class is named 'Class01'.
In the DLL, my procedure is named 'TestMe'

I've managed to integrate the DLL into SQL using the following statement:

CREATE ASSEMBLY TEST_ERIC_01
AUTHORIZATION dbo
FROM 'c:mssqlassembliescrystalreports.dll'
WITH PERMISSION_SET = UNSAFE

I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:

create procedure dbo.Crystal_Reports_Test_01(
@Parm1 nvarchar(255)
)
as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe

But I get the following error:

Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.

I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.

Any help would be appreciated ! I'll post the Delphi code (DLL) below.

Thanks,

Eric Gooden

library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.

View 4 Replies View Related

Can SqlDataSource Class Inherite To Another Class

Feb 1, 2008

Does any one inherit SqlDatasource class?
I treid it as :
public class MYDataSource : System.Web.UI.WebControls.SqlDataSource
{public MYDataSource(){
 
}
 
}
Debugger dont give any error or warning when i buld project. But when i use it in a page Visual studio is crashed.
 Can any one help me ?

View 1 Replies View Related

Abstraction Layer

Nov 8, 2007

Hello,

Is anyone using an abstraction layer for the middle tier to interface with so the actual table design is hidden for the application? I have read several articles that it can be done using views http://www.sqlservercentral.com/articles/Database Design/61335/. However, I don't see myself creating a view ontop of every table and allowing dml modifications to happen through them. I think it would make the query optimizier throw up after a certain level of data and views are encountered. I know snonyms are available in 2005, but I have only seen what can be done not what should be done.

I don't have a fear of allowing my .NET developers (I work with very competent people) to create middle tier objects directly against the db tables for oltp dml operations. I don't want to create stored procedures for these because I think more flexibility exists within the middle tier for business logic. Then all select operations are performed against the db with stored procedures. Does anyone out there think I am crazy? Has anyone successfully created an abstraction layer strategy for their entire db?

Thanks!

View 6 Replies View Related

Persistance Layer

Mar 15, 2007

What is Persistance layer?

And is it part of Database layer or other name for database layer?

--Deepak

View 1 Replies View Related

How To Use SCOPE_IDENTITY() In 4-layer Architecture

May 23, 2007

I have the following stored procedure:
INSERT INTO MyTable ( Value1, Value 2)
VALUES( @Value1, @Value2)
SELECT SCOPE_IDENTITY()
How do I put this sp in the DAL typed dataset, so I can get the Identity value in the Business Layer?
 

View 2 Replies View Related

Randomness Better In SQL Or In Application Layer

Aug 17, 2007

Instead of showing the same content (images) when customers visit our application, we want to show random images to make use of all the images that can be displayed.

My question is, is it better to handle randomness at SQL or in the application layer. In sql, we can achieve this by doing order by newid(). My understanding of this is, SQL will create a guid (which is random) for all the rows in the resultset and will sort by the guid. If this code is encapsulated in a SP and if it may be executed over say 200+ or say 1000+ times a day, isn't it better to handle the randomness in the app layer. Let the app layer use resources and sql just retrieve the data necessary.

Any thoughts?

View 1 Replies View Related

Application Layer For SSRS

Feb 19, 2008



Hello,

I'm searching for good documentation for building Application layer for Report (SSRS) using Visual Studio 2005. Kindly forward useful link if possible or walk me through.

Thanks,
Monika

View 11 Replies View Related

Data Access Layer Advice

Jun 19, 2007

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.
 I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...1 SELECT CAR_MAKE, CAR_MODEL,
2 (SELECT DATE_TAKEN
3 FROM PHOTOGRAPHS
4 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS DATE_TAKEN,
5 (SELECT FORMAT
6 FROM PHOTOGRAPHS
7 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS FORMAT,
8 (SELECT REFERENCE
9 FROM PHOTOGRAPHS
10 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS REFERENCE,
11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID
12 FROM MS_PHOTOGRAPHS
13 WHERE (GALLERY_ID = @GalleryID)
This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

View 7 Replies View Related

Creating A Data Access Layer

Mar 10, 2008

Hello, everybody.

In my web application, i'm using 2 tabels; Users(Username(PK), Pwd, Name, Deptid(FK)) n Dept(Deptid(PK), Deptname)).
For creating a Data Access Layer 4 my project, I added dataset as new item n followed the wizard 2 create the required functions.
I have a function GetUser(@uname, @pwd), which takes username n password as input. M using this for authentication purpose.
While executing it poping an ConstrainException.
Plz help me out.

I've tried 2 as clear as possible here. OR u may ask me any other questions for clear picture of the scenario.
Thanks and Regards,
Sankar.

View 1 Replies View Related

Creating Data Access Layer

Jun 11, 2008

I request you plz tell how to create Data Access Layer. I mean DataAccess.dll. So that I can call stored procedure from dataaccess.dll as below.
DataAccess.SqlHelper.ExecuteDataset(DataAccess.DSN.Connection("DBConnectionString"), CommandType.StoredProcedure, "SP_GetEmpIds");
I request you how can I add this stored procedures to DataAccess.dll and function. I am not having any idea in this area. I request you plz give me some suggestions to work with task.

View 3 Replies View Related

SQL - System Table In Data Access Layer?

Apr 5, 2007

How do I get a System Table like 'Sysobjects' into the Data Access Layer?
My app generates tables on the fly, and has to check in the sysobjects table which tables are present.

View 5 Replies View Related

LINQ Layer - How To Invoke A Sort Function

Jun 5, 2007

 
 Dear all,
I'm trying to create a BLL for an objectdatasource to used for a sorted DataGrid. I am trying to learn about how to use LINQ and I'm sure this is a simple question.
var query = (from p in db.MP3Collections select p).OrderBy(?????????);
 
Now I want to invoke the following method  List<MP3Collection> GetAllMP3s(string sortColumns), which can contain a string name for the field in MP3Collection that is to be sorted (and optionally the "desc" tag).
Now I'm resorting tocoding the query on a case by case basis, which is very tiresome (see the partial code below). This works fine, but is very long and tedious coding. I'm not sure how to go about neatening the code, though am sure it involves the ?????? space above.
Any ideas?
 public static List<MP3Collection> GetAllMP3s(string sortColumns)
{System.Diagnostics.Debug.WriteLine("Pure sort expression: " + sortColumns);
bool descending = false;if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
{
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);descending = true;
}string[] columnNames = sortColumns.Split(',');
 MP3DataClassesDataContext db = new MP3DataClassesDataContext();
List<MP3Collection> query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();foreach (string columnName in columnNames)
{switch (columnName.Trim().ToLowerInvariant())
{case "filename":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename ascending");
}
break;case "filepath":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath ascending");
}
break;
//OTHER CASES HEREdefault:
query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();
//throw new ArgumentException("SortColumns contains an invalid column name.");break;
}
}
 
//var query = (from p in db.MP3Collections select p).OrderBy(p=>p.fileName).ToList<MP3Collection>();return query;
}

View 1 Replies View Related

What New Features Of .NET 2.0 Required In A Data Access Layer

Jan 10, 2008

Hi Experts ! I want to use maximum feature of SQL
Server 2005 and ASP.Net 2.0  in making Data Access LayerSuggestions will be welcomed .Thank you
RegardsKAMRAN

View 3 Replies View Related

Making Calculations In A Business Layer Vs Sql Server

Jan 29, 2008

Lead information fields (name, address, etc.)
 
promo_ID: the id of the web form that site visitors filled out to send us their information
PPC_source: If this lead is a result of a Pay-per-click campaingwe capture the name of the search engine
Status_ID: Shows if the lead has been contacted, quoted, sold, bad lead, etc.
I was asked to come up with a summary report calculating the number of leads under each status ID - for each PPC_source
Example:




 

Total leads   |   

Google   |   

Yahoo


Status = pending

 

 

 


Status = contacted

 

 

 


Status = quoted

 

 

 


Status = Sold

 

 

 
Looping over each record (12,000 and counting) to get the sums of each status for each pay-per-click campaign takes a while (not all campaigns / status are listed above).
The question: Is it better to retrieve all records in a dataset and do the math in the business layer or is there a way to do the math in a SQL Procedure, or function.
Thanks.

View 2 Replies View Related

Deleting Using SqlDataAdapter Via A Data Access Layer

Feb 20, 2008

I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId)
2 {
3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection());
4
5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier);
6 p1.Value = (Guid)companyId;
7 p1.Direction = ParameterDirection.Input;
8
9 deleteCommand.Parameters.Add(p1);
10 dataAdapter.DeleteCommand = deleteCommand;
11
12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds.
13
14 try
15 {
16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'"))
17 {
18 row.Delete();
19 }
20 return dataAdapter.Update(companyDS.Tables["pg_Company"]);
21 }
22 catch
23 {
24 return 0;
25 }
26 finally { }
27 }
I thank you in advance for any help here.

View 3 Replies View Related

How To Rollback A Transaction In Data Access Layer

Jul 9, 2007

Hi,

I am having a application in which from the front end i am saving details of three different things



i.Enquiry Details

ii.Parts Details

iii.Machine details



i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

View 4 Replies View Related

EnterpriseLibrary 2006 DATA ACCESS LAYER

Sep 27, 2007

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}


Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST
PLEASE CHECK THE CODE AND REDEFINE THE CODE


using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;-----------------------------------------------ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}




Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:KOTI_PRJSEnterpriseCustomerClass1.cs 34 13 Customer

View 1 Replies View Related

Analysis :: Top N Rows Vary Depending On Reporting Layer?

Oct 16, 2015

trivial example, i have a tabular model for orderlines.Each line has a product.A product fits under a hierarchy Category > Product.I need to display the top N rows by quantity under each category. My can break down as follows: Warehouse,  Product category, Product, PurchaserID

If im to do the top N in excel i use pivot table functions to get the top N by whatever measure i pick from the dropdown:I cant do this in powerbi builder though, as it works very differently, or powerview which again is done differently.Do i have to take a different approach depending on the report platform,  or is there a way to do this in the tabular model that will work on any platform?

View 4 Replies View Related

Should SQL Server Act As A Business Layer In The 3-tier Application Layout.

Mar 7, 2007

Hello Everyone

I'm looking for outside opinion on how to implement the business layer of an application. The straight forward idea I know of would be to develop a component that encapsulates all the business rules under objects. Client applications would access this component, and the business component would talk directly to the database. This was great for me when I used Access databases.

Now I have been introduced to SQL server and the stored procedures. I think stored procedures are one of the best features because these procedures can enforce rules as sort of a "last line of defence". So this is where my question comes in. Where would the best place be to implement the business layer? Through a component, such as XML services that allow other clients to access the business rules OR stored procedures.

I do understand that components/dlls provide MUCH more flexibility then stored procedures.

But, if I do take the component route:

- Other then SQL queries that get called multiple times, is there any use for stored procedures?
- For some case scenarios such as web sites that access a Business Layer, should the business layer use ONE sql server login/role to access the data or create a sql login/role for each major segment/service? E.g. one login for the accounting side, one login for the
web site services, one side for HR and the business layer will have to decide which one to use depending on the service requested.
- Would it be in the best interest of security that I allow the business components to send SQL queuries such as Insert/Delete/Update? I loved the idea of stored procedures because if for some reason, someone steals the business layer userId/password, they are only exposed to the assigned stored procedures and cannot do "Delete From Table". Unfortunately, this practice has led to an abundance of stored procedures (the list is HUGE).

If I do take the Sql stored procedure route:

- If someone steals any user Id/password, they will only be exposed to the assigned stored procedures and THOSE stored procedures contain the business rules. Thus the damage can be minimized.

- If some developer makes a mistake down the road or forgets some sort of business aspect (and is not going through the business layer), again, stored procedures would offer a last "line of defense". Lets face it, there are some applications that do not necessary require a business layer

I'd like to get everyone's opinion on how they partition the business rules with SQL server? Do you use SQL server stored procedures to only enforce formating rules (such as upper case, lower, etc) while the business rules are stored in a component?

Do you use stored procedures to enforce all business rules? None?

Thank you for your time,

Adrian

View 7 Replies View Related

Why You Can't Set Up In 'disabled Status' A Task In Data Flow Layer???

May 26, 2006

In Control Flow you can do that.

View 10 Replies View Related

Error Trying To Add Information To DateTime Field Using Data Access Layer

Sep 20, 2006

I am taking my first stab at a 3 tier architecture within ASP.Net.  I have been adding datasets and creating a new Insert to add certain parts to a table.  I am trying to add a date field called 'DateAdded' and is setup in SQL as a DateTime.  When Visual Studio auto created the dataset, the Insert function is not "DateAdded as Datetime" as I would have expected, but it is "DateAdded as System.Nullable(Of Date)".  There is a space in between 'Of' and 'Date'.  If I keep the space in there the insert function shows an error that says "Arguement not specified for parameter DateAdded of funtion( etc. etc.).  If I take the space out, the error on the insert function goes away but there is an error within the "OfDate" that says "Array bound cannot appear in type specifiers".  I am confused on why the date format changed and how I can get a date to go into the database using the autogenerated datasets from Visual Studio.  Any help would be appreciated.  Thanks, Mike 

View 1 Replies View Related

Stored Procedure Output Parameter's Issue In Dataaccess Layer

Aug 27, 2007

Hi,the stored procedure returns only the first letter of the ouput parameter i.e 'e'.  Actually It must return 'error while updating data'. can anybody helpme to solve this issue.How to set the size of output parameter.spvr_ErrorDescription='error while updating data     which I get from the output parameter of stored procedure. How to set the size of output parameter
in dataaccess layer. Below is the actual code.public string UserCostCenter_Upd(string spvp_Offering,string spvp_UserId,string spvp_CostCenter,DateTime spvp_StartDate,DateTime spvp_ExpiryDate,ref string spvr_ErrorDescription){// The instance ds will hold the result set obtained from the DataRequestDataSet ds = new DataSet();RequestParameter[] parms = new RequestParameter[7];parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);parms[1].Value = spvp_Offering;parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);if (spvp_UserId == null)parms[2].Value = DBNull.Value;elseparms[2].Value = spvp_UserId;parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);if (spvp_CostCenter == null)parms[3].Value = DBNull.Value;elseparms[3].Value = spvp_CostCenter;parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);if (spvp_StartDate == null)parms[4].Value = DBNull.Value;elseparms[4].Value = spvp_StartDate;parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);if (spvp_ExpiryDate == null)parms[5].Value = DBNull.Value;elseparms[5].Value = spvp_ExpiryDate;parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);parms[6].Value = spvr_ErrorDescription;// Create an instance of DataSQLClientAirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;// typDSRef holds the ref. to the strongly typed datasetDataSet typDSRef = (DataSet)ds;DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSetDataHelper helper = new DataHelper();if (!helper.MapResultSet(ref typDSRef, resultDataSet))ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value;spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);return spvr_ErrorDescription ;}

View 5 Replies View Related

Internalization Of (generic) Application Layer Software To The Database TSQL

Jul 20, 2005

Greetings to all database professionals and laymen,Let us make a bold assumption that we have developed a softwaretool for the SQL Server environment which simply acts as an interfacebetween an end-user in an organization and the database, throughthe exclusive use of stored procedures which are authored by theorganization or by software developers.All development work at the application software level may therebybe conducted within SQL, by the development of TSQL storedprocedures and their coordination across an organization.The question then needs to be asked what are the advantages of thisarrangement and what are the disadvantages. I would appreciateyour comments here, as it is difficult for folk heavily involved (eg: me)in something to obtain objective opinion otherwise.Potentially it is possible to construct an entire database applicationsoftware package using only TSQL stored procedures and thistool. When a database backup is conducted not only the data isbacked up but also all the "program development" suite, in theform of stored procedures (and their scheduling, for example).One of the advantages from my perspective is that this arrangementimplies the possibility that all software external to the database maybe made redundant (except this tool), and along with it, all the otherredundancies of managing application development (life-cycles) withinthe database and then coordinating these changes with softwareexternal the database (particulary loading it to the client environment)You see, I believe that (for example) any and every application writtenin VB, C, etc, etc, etc (external to rdbms) expressly for database software(ie: rdbms software such as SQL Server) involves a redundancy of datadefinitions required ---- once within the VB and once within the db.I would appreciate any feedback on any of the above issues, andwish everyone the compliments of the season.Pete BrownFalls CreekNSWOz~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~EDITOR:BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}Thematic Threading: Publications of Peace and Of Great SoulsWebulous Coordinates: http://www.mountainman.com.auQuoteForTheDay:"Consciousness is never experienced in the plural, only inthe singular. How does the idea of plurality (emphaticallyopposed by the Upanishad writers arise at all? .... theonly possible alternative is simply to keep the immediateexperience that consciousness is a singular of which theplural is unkown; that there *is* only one thing and thatwhat seems to be a plurality is merely a series of differentaspects of this one thing produced by deception (the Indianmaya) - in much the same way Gaurisankar and Mt Everest turnout to be the same peak seen from different valleys."- E. Schrodinger, "What is Life"--------------------------------------------------------------------

View 18 Replies View Related

Error Using SQL Datatype Text As Output Parameter From C# Data Access Layer

May 19, 2008



Hello,

My datalayer of C# code is invoking a stored procedure that returns a varchar(max) SQL data type. In my ASP.NET code, I have:

SqlCommand myCommand = new SqlCommand("usp_GetTestString", myConnection);
myCommand.Parameters.Add(new SqlParameter("@TestString", SqlDbType.Text));
myCommand.Parameters["@TestString"].Direction = ParameterDirection.Output;
myConnection.Open();
myCommand.ExecuteNonQuery();
return Convert.ToString(myCommand.Parameters["@TestString"].Value);

The query fails to execute and returns an error: String[1]: the Size property has an invalid size of 0. If I change the SqlDbType.Text parameter type to SqlDBType.Varchar, 100 (or any other fixed varchar length), it works but limits the length my unlimited field text. Any suggestions on how I can use db type text or varchar(max)? The field I need to retrieve is string characters of unlimited length and
hence the datatype varchar(max).

View 3 Replies View Related

Transact SQL :: Present Data In Presentation Layer After Removing Temp Table?

May 21, 2015

Inside of stored procedure you have the code

create table #datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into #datatable
exec ('select *
from datatable
where id = 1')
select * from #datatable

I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.

If I paste the code DROP

TABLE #datatable after
insert into #datatable
exec ('select *
from datatable
where id = 1')

Does it gonna work to present the data in the presentation layer after removing the temp table?

The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.

[URL]

View 5 Replies View Related

How To Return A Static Value?

Apr 25, 2007

I have the following stored procedure...
CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT     Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM      Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE     (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO
The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.
Obviously, I need to wrap this in an IF...EXISTS
IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID)) 
(the SELECT above, since that's what needs to come back if the data exists) 
ELSE
Do the same select but put in a static value for BudgetCode, like '0000' 
GO
How could I do the part where the IF...EXISTS fails?
I'm... not sure I can use RETURNS, since it feeds into this recordset:
rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID") 
and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>  
 

View 4 Replies View Related

What Is The Best Way Yo Add A Static Value Column?

May 30, 2008

Hello,

I have a OLE DB on a Data Flow that execute a query and returns 5 columns i want to add a new column with a static value that a have on a Variable.

I use Derived Column in order to create the new column adding the variable value in the expression field, Is this the best way??

Thanks

View 4 Replies View Related

Getting Is Not Static When Trying To Deploy

Nov 10, 2006

I'm attemping to deploy a crypto class that we use on a desktop app to SQL Server. I'll use the encryptstring/decryptstring found in this class as User Defined Functions on SQL Server. When I try to deploy the class I'm getting this error:



Error 1 Method, property or field 'EncryptString' of class 'EncryptingDecryptingOASISPWDS.Crypto' in assembly 'EncryptingDecryptingOASISPWDS' is not static. EncryptingDecryptingOASISPWDS




Here is the code:



Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Security.Cryptography

Imports System.Text 'for UnicodeEncoding

Imports System.IO 'for MemoryStream, FileStream, Stream, Path

Imports System.Threading 'for Thread.Sleep

Partial Public Class Crypto

'Private Const CodeVersion As String = "01.00.00"

'class member variables

Private m_sPassPhrase As String

'class private variables

Private mbytKey() As Byte 'crypto key

Private mbytIV() As Byte 'Initialization Vector

Private mbKeyIsSet As Boolean = False

Private mksKeyNotSetException As String = "Crypto passphrase is not set."

Private mksPassphraseTooSmall As String = "PassPhrase length must be at least {0} characters."

'--- class constructors

Public Sub New()

'no passphrase is useful for GetHashString/ValidPassword methods

End Sub

Public Sub New(ByVal CryptoPassPhrase As String)

PassPhrase = CryptoPassPhrase

End Sub

'--- class public properties

'generate and store encryption key & iv from passphrase

Public Property PassPhrase() As String

Get

Return m_sPassPhrase

End Get

Set(ByVal Value As String)

Const iMinLength As Integer = -1 '-1 disables min length

m_sPassPhrase = Value.Trim

'enforce a rule on minimum length if desired here

If (Value.Length > iMinLength) Or (iMinLength = -1) Then

Dim sha2 As New SHA256Managed()

'256 bits = 32 byte key

mbytKey = sha2.ComputeHash(BytesFromString(m_sPassPhrase))

'convert to Base64 for Initialization Vector, take last 16 chars

Dim sKey As String = Convert.ToBase64String(mbytKey)

mbytIV = Encoding.ASCII.GetBytes(sKey.Remove(0, sKey.Length - 16))

mbKeyIsSet = True

sha2 = Nothing

Else

mbKeyIsSet = False

Throw New Exception(String.Format(mksPassphraseTooSmall, (iMinLength + 1).ToString))

End If

End Set

End Property



'decrypt a stream

Public Function DecryptStream(ByVal EncryptedStream As MemoryStream) As MemoryStream

If mbKeyIsSet Then

Try

'create Crypto Service Provider, set key, transform and crypto stream

Dim oCSP As New RijndaelManaged()

oCSP.Key = mbytKey

oCSP.IV = mbytIV

Dim ct As ICryptoTransform = oCSP.CreateDecryptor()

Dim cs As CryptoStream = New CryptoStream(EncryptedStream, ct, CryptoStreamMode.Read)

'get bytes from encrypted stream

Dim byteArray(EncryptedStream.Length - 1) As Byte

Dim iBytesIn As Integer = cs.Read(byteArray, 0, EncryptedStream.Length)

cs.Close()

'create and write the decrypted output stream

Dim plainStream As New MemoryStream()

plainStream.Write(byteArray, 0, iBytesIn)

Return plainStream

Catch ex As Exception

Return Stream.Null

End Try

Else

Throw New Exception(mksKeyNotSetException)

End If

End Function

'decrypt a string - wrapper without Base64 flag (True by default)

Public Function DecryptString(ByVal EncryptedString As String) As String

Return _DecryptString(EncryptedString, True)

End Function

'decrypt a string - wrapper with Base64 flag

Public Function DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String

Return _DecryptString(EncryptedString, Base64)

End Function





'encrypt a stream

Public Function EncryptStream(ByVal PlainStream As MemoryStream) As MemoryStream

Try

'open stream for encrypted data

Dim encStream As New MemoryStream()

'create Crypto Service Provider, set key, transform and crypto stream

Dim oCSP As New RijndaelManaged()

oCSP.Key = mbytKey

oCSP.IV = mbytIV

Dim ct As ICryptoTransform = oCSP.CreateEncryptor()

Dim cs As CryptoStream = New CryptoStream(encStream, ct, CryptoStreamMode.Write)

'get input stream into byte array

Dim byteArray() As Byte = PlainStream.ToArray()

'write input bytes to crypto stream and close up

cs.Write(byteArray, 0, PlainStream.Length)

cs.FlushFinalBlock()

cs.Close()

Return encStream

Catch ex As Exception

Return Stream.Null

End Try

End Function

'encrypt a string - wrapper without Base64 flag (True by default)

<Microsoft.SqlServer.Server.SqlFunction()> _

Function EncryptString(ByVal PlainText As String) As String

Return _EncryptString(PlainText, True)

End Function

''encrypt a string - wrapper with Base64 flag

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Function EncryptString2(ByVal PlainText As String, ByVal Base64 As Boolean) As String

Return _EncryptString(PlainText, Base64)

End Function

'calculates the hash of InputValue, returns a string

'- SHA1 hash is always 20 bytes (160 bits)

Public Function GetHashString(ByVal InputValue As String) As String

Try

Dim inputBytes() As Byte = BytesFromString(InputValue)

Dim hashValue() As Byte = New SHA1Managed().ComputeHash(inputBytes)

Return BytesToHexString(hashValue)

Catch ex As Exception

Return String.Empty

End Try

End Function

'returns True if hash of Passphrase matches HashValue

Public Function ValidPassword(ByVal Passphrase As String, ByVal HashValue As String) As Boolean

Return (GetHashString(Passphrase) = HashValue)

End Function





'internal string decryption

Private Function _DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String

Try

'put string in byte array depending on Base64 flag

Dim byteArray() As Byte

If Base64 Then

byteArray = Convert.FromBase64String(EncryptedString)

Else

byteArray = BytesFromString(EncryptedString)

End If

'create the streams, decrypt and return a string

Dim msEnc As New MemoryStream(byteArray)

Dim msPlain As MemoryStream = DecryptStream(msEnc)

Return BytesToString(msPlain.GetBuffer)

Catch ex As Exception

Return String.Empty

End Try

End Function



'internal string encryption

Private Function _EncryptString(ByVal PlainText As String, ByVal Base64 As Boolean) As String

Try

'put string in byte array

Dim byteArray() As Byte = BytesFromString(PlainText)

'create streams and encrypt

Dim msPlain As New MemoryStream(byteArray)

Dim msEnc As MemoryStream = EncryptStream(msPlain)

'return string depending on Base64 flag

If Base64 Then

Return Convert.ToBase64String(msEnc.ToArray)

Else

Return BytesToString(msEnc.ToArray)

End If

Catch ex As Exception

Return String.Empty

End Try

End Function

'returns a Unicode byte array from a string

Private Function BytesFromString(ByVal StringValue As String) As Byte()

Return (New UnicodeEncoding()).GetBytes(StringValue)

End Function

'returns a hex string from a byte array

Private Function BytesToHexString(ByVal byteArray() As Byte) As String

Dim sb As New StringBuilder(40)

Dim bValue As Byte

For Each bValue In byteArray

sb.AppendFormat(bValue.ToString("x2").ToUpper)

Next

Return sb.ToString

End Function

'returns a Unicode string from a byte array

Private Function BytesToString(ByVal byteArray() As Byte) As String

Return (New UnicodeEncoding()).GetString(byteArray)

End Function





'Return True when the file is available for writing

'- returns False if output file locked, for example

Private Function CheckWriteAccess(ByVal FileName As String) As Boolean

'2 second delay with 10,200

Dim iCount As Integer = 0 'retry count

Const iLimit As Integer = 10 'retries

Const iDelay As Integer = 200 'msec

While (iCount < iLimit)

Try

Dim fs As FileStream

fs = New FileStream(FileName, FileMode.Append, _

FileAccess.Write, FileShare.None)

fs.Close()

Return True

Catch ex As Exception

Thread.Sleep(iDelay)

Finally

iCount += 1

End Try

End While

Return False

End Function

End Class

View 4 Replies View Related

Should We Use Static Sqlconnection In Aspnet

May 29, 2008

 Should a static connection be used in application. Is it advisable to use a static connection, i mean everytime should i create a new instance of sqlonnection object and then dispose it after use or should i use a static connection plz advise 

View 2 Replies View Related

Select 'static' Row/result

Oct 17, 2006

Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:


Code:


Select statusID, statusName
From Status



Here is the result set that I am returned:


Code:


22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay



Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.

In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.

again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?

Thanks,

Zoop

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved