|
Hi,
I am a little lost in this situation.
I am required to reuse SQL queries , also I am required NOT to ceate stored procesdures as they reside on SQL server, but there should be some form on the application side to store my SQL queries and call them when required.
What is the best practice to achieve this,
I am thinking of storing on .resx files
Please let me know the best Solution.
Thanks,
Happy Programming!
Regards,
ctrlnick !
|
|
|
|
|
Where you store the query text is probably immaterial, because wither you store the query in .resx or hard-code the query into the application you'll still need to recompile when you change the query. However, you should define an interface or at least a data layer class which defines the queries and their parameters and hides the implementation of how you call and execute your queries from your application.
If you are working with DataSet objects then your interface will define the return values as DataSet/DataTable objects. Otherwise, your interface will define the return values as your custom objects.
If you are working with a large number of queries you will need to define multiple interfaces which break up your queries into logical groups which match your object model.
Here's a simple implementation w/o using an interface:
implementation:
<br />
public class DataLayer {<br />
public DataSet GetSomeData(int param1, string param2){<br />
DataSet myData = new DataSet();<br />
<br />
using(DbConnection conn = GetConnectionFactoryMethod()){<br />
using(DbCommand cmd = new DbCommand(conn, "SELECT .... FROM .... WHERE col1 = @param1 AND col2 = @param2")){<br />
cmd.Parameters.Add("@param1", .....);<br />
cmd.Parameters.Add("@param2", .....);<br />
<br />
cmd.Parameters["@param1"].Value = param1;<br />
cmd.Parameters["@param2"].Value = param2;<br />
<br />
DbDataAdapter da = new DbDataAdapter(cmd);<br />
da.Fill(myData);<br />
}<br />
}<br />
<br />
return myData;<br />
}<br />
<br />
public DataSet GetSomeDifferentData(string param1, bool param2){<br />
}<br />
<br />
private DbConnection GetConnectionFactoryMethod(){<br />
return new SqlConnection("... connection string ...");<br />
}<br />
}<br />
usage:
<br />
DataLayer myDataLayer = new DataLayer();<br />
<br />
DataSet myData = myDataLayer.GetSomeData(5, "my string");<br />
DataSet myData2 = myDataLayer.GetSomeDifferentData("some value", true);<br />
<br />
By doing this you can store your query text however you like (I say just hard-code the query text into the data layer class). The advantage of doing it this way is that your queries are de-coupled from your application. You can change your data provider or switch to stored procedures or whatever. As long as you return the same dataset (or object) it doesn't matter how you do it.
|
|
|
|
|
Dear All,
I want to have a survey on locking mechanisms in SQL Server. I have studied different locking modes including shared,exclusive, intent shared ,...
Besides I have studied different locking modes, table hints and different Isolation levels in SQL server. But I still don't know what should I do if I want to take for example an exclusive lock at a row-level. There are few examples on SQL Server help. Would you please introduce me a reference with examples of these kinds.
Thanks in advance
|
|
|
|
|
MozhdehQeraati wrote: if I want to take for example an exclusive lock at a row-level.
You do not normally need to do this sort of thing. If you do your insert/update/delete in a consistent manner and use transactions where necessary you can leave the rest to SQL Server.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I want to control it myself. I have many clients that do read and write in on a DB server by long time transactions. But I don't know what to do.
|
|
|
|
|
I would firstly look at why you have long transactions - there is probably a design flaw somewhere. SQL Server is designed to work with many clients performing simultaneous updates without the need for the developer to attempt to control locking under anything but very exceptional circumstances.
I have been asked about this in the past and it has usually been because the developer either didn't really understand how SQL Server locking works, or they felt they could do a better job than the guys who wrote the code at Microsoft.
If you really want to do it yourself then may I wish you luck, and your end users a lot of patience 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i want to update an image field of a record in SQL but i can not .
i must first delete the record and then insert it but i want to update the record.
please help
|
|
|
|
|
fix your code.
If you want more help, try posting some details of the code you're using, so we can look at it and try to help.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You can learn the with content from the new SQL Server 2008 Jumpstart event. The site contains presentations, recordings, hands-on labs and demonstrations for the SQL Server 2008 JumpStart technical training event.
There are five tracks:
The online material includes presentations, videos, hands on labs and manuals, recorded webcasts, and the SQL Server 2008 Virtual PC used for the labs.
You'll be asked to register if you want to access the training material from the event. All the resources you need for testing your application on SQL Server can be downloaded from the Application Compatibility & Upgrade Track page.
If you are conducting Application Compatibility testing, remember to sign-in with your Windows LiveID and report your results through the SQL Server App Compat Testing Results Survey. If you encountered bugs with any of the product features, submit feedback to the SQL Server Engineering team so we can fix it. For online support go to the App Compat and Upgrade forum.
|
|
|
|
|
Hi all,
I am getting an 'incorrect syntax near StoredProcName' error on debugging the beginings of my web app.
I know the sql syntax is correct as I have tested it in management studio express(2005) annd it returns exactly what I expect. The error is only occuring when calling from my page.
Here's my page code followed by a very simplified stored procedure(I know my complex one will return the correct result and this has the same error).
Page code:
SqlConnection conn = new SqlConnection(connString);
string sPName = "getProjectProModules2";
SqlCommand cmd = new SqlCommand(sPName, conn);
SqlParameter par1 = new SqlParameter("@modCount", SqlDbType.Int);
par1.Direction = ParameterDirection.Output;
par1.Size = 1;
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@singleModule", SqlDbType.VarChar);
par2.Direction = ParameterDirection.Output;
par2.Size = 20;
cmd.Parameters.Add(par2);
conn.Open();
SqlDataReader recaffected;
try
{
recaffected = cmd.ExecuteReader();
if (recaffected.HasRows)
{
int i = 0;
while (recaffected.Read())
{
if (i == 0)
{
string countStr = cmd.Parameters["@modCount"].Value.ToString();
count = Convert.ToInt16(countStr);
}
moduleList[i] = cmd.Parameters["@singleModule"].Value.ToString();
i++;
}
recaffected.Close();
recaffected.Dispose();
return moduleList;
}
else
{
moduleList[0] = "No Modules";
return moduleList;
}
}
catch (Exception ex)
{
moduleList[0] = ex.Message.ToString();
moduleList[1] = ex.Data.ToString();
moduleList[2] = ex.Source.ToString();
return moduleList;
}
finally
{
conn.Close();
conn.Dispose();
}
SP Code:
ALTER PROCEDURE dbo.getProjectProModules2
@modCount int OUTPUT, @singleModule varchar(50) OUTPUT
AS
select '1'
RETURN
If I don't put the parameter size in my page code, it errors saying invalid parameter size.
Really not sure whats going wrong, I've looked up the code all over the place and from what i can see I'm doing it right. Anyone have any idea what the problem is?
Thanks in advance.
Mark
OK, fixed that error for those interested, I was not setting the command type to be StoredProcedure. However, I only seem to be able to return null as my parameter values. Still if there's any suggestions I would appreciate it.
modified on Tuesday, May 27, 2008 5:41 PM
|
|
|
|
|
To return parameters, you create local variables and pass them to the parameters, from memory.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
OK, so I have created 2 local variables, 1 int and 1 string, these I am passing to the paramater as the value,
I.E.
string singleModuleStr = "";
int countInt = 0;
--it doesn't like me not assigning them....
.....
par1.value = countInt;
.....
par2.value = singleModuleStr;
do i then assign the par2.value etc to these variables at recAffected.Read()?
Don't want to sound dumb but I'm used to just returning a select list and I want to be able to do it each possible way to broaden my knowledge.
Thanks for the help so far.
mark
|
|
|
|
|
Hey there,
I'm having a little issue with a query. Basically I'm trying to do some paging. There's many ways to do it, I know, but what I came up with was this:
SELECT TOP 10 columnA, columnB
FROM table
WHERE columnA not in (
SELECT TOP 21190 columnA
FROM table
ORDER BY columnC DESC)
ORDER BY columnC DESC
Some notes about it:
columnA is the key
columnD, columnC and columnA are index1
columnD and columnA are index2
columnC is index3.
In SELECT TOP 21190, that number is pageNumber * pageSize
Now... it seems that the very last 'ORDER BY columnC DESC'is the problem. Like it is the query takes around 1 minute to run. If I take that line out, the query runs in less than a second. Or, if I change the ORDER BY to be done by columnA, it also runs in less than a second.
Any ideas?
|
|
|
|
|
You need to do an order ASC in one of those queries for this to work. Then you don't need the 'not in', which is your problem. Select in desc order, ending with the first record you want. Then select in your outer query the number you want, in ASC order.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi, I want to debug a stored proc on our server but the database contains close to 30k stored procs. So when I press the expand button on the server explorer pane I have to wait for at least 30 minutes before VS unfreezes... This is getting quite annoying...
Out of these 30k stored proces around 95% are useless to me so I would like to filter them by their names.
All the ones I don't want start with 'zDP_'.
Do you know if there is a way to apply a filter before pressing the +?
Alternatively, is there another tool I could use to debug my stored procs?
We are running SQL Server 2005.
Thanks
Fred
modified on Tuesday, May 27, 2008 12:30 PM
|
|
|
|
|
hi everybody
I created a website with SQL express database in server explorer, everything is going just fine and now I want to upload the site and the database to the internet so that my customer can test it.
The server on which I want to upload my site to requires me to upload a .mdf file to the server, I searched my computer for the mdf file but I couldn't find it.
Any help please?
Thanks
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
we have a database publishing wizard to do so search google and download it
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
|
hi friends
i have a server that installed o it a sql server 2000
i want to create a connection to the database on this sql server.
my server name is (local);
when i try to login into my database from query analyzer:
i type "(local)" instead of server name and username and password of a auser that i created for login to database.
its worked correctly.
but when i try to do this action from my home pc, an error appear that contanis below message:
"Sqlserver does not exist or access denied."
i enter my server ip instead of "(local)" in my query analyzer from my home pc.
now how can i login to my database from my home pc????
what do i enter server name ? an ip address or else???
please help me
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
(local) only works if you are logged in locally to the machine where the instance of SQL Server is installed. You will probably need to use the IP address of the machine from home, unless you have some way to resolve the hostname (like if you are logged in thru a VPN). Then you can type in your username and password into QueryAnalyzer just like you would if you were on the machine locally.
|
|
|
|
|
Video:
http://www.microsoft.com/hk/webcast/default.aspx?sid=211[^]
SQL server 2008 will help enable information technology to drive business intelligence throughout the organization. All your saved data could be easy to analyze and present by reports and charts for you to study the trends and make the right business decision.
|
|
|
|
|
Thanks for sharing!
The latest version looks good; I am considering upgrade to this 2008 version.
|
|
|
|
|
How can i pass the parameters to triggers?
|
|
|
|
|
You don't. The trigger has all the data from the insert/update/delete in special tables called inserted and deleted. Google triggers and yuo will find plenty of examples.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi..
can anybody tell me using c#.net to create advanced search like all words, anywords, and exact word, if i have to search in 5-6 tables, means if i give "red" in search text box then how can i search red in 5-6 tables and i dont know which column contains the word red.
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
