|
giddy_guitarist wrote: 1. Theres a good load because sometimes i need data often and this queries the DB and causes some slightly noticeable lag. Thats why i had the Dataset in mind. 50% of the time , i dont need *live* data. I realized i cant have a method that returns an SqlDataReader without a global open connection.So how do i cache raw?
I'd recommend creating a business layer that has your business objects in it. This separates the database from the user interface (which is regarded as best practice). However, that is quite a complex thing to explain in a forum post. You might want to do a search of "Design Patterns" to get an idea of some of the things you can do. Martin Fowler has a good reference guide on Enterprise Design Patterns and there are also the foundation patterns published by Grady Booch et al. The best introduction to the area is "Head First Design Patterns". It gives some great and easy to understand explanations, but the examples are in Java. If you can live with that I would recommend that book as a good starting point before delving in to the more academic books by Booch et al.
That aside, you can use a SqlDataAdapter to populate a DataSet. I don't tend to use them myself, so you might like to look them up in MSDN.
giddy_guitarist wrote: 2.I have a PersID column in the persons table.Should i let it be an autonumber or Generate some hash because customers once removed are stored in an archive table.New and archived customers should'nt have same IDs , i noticed that even if a delete a customer with ID=2 , the next customer has an ID:3 . But i'm curious about where this is stored? what if i wanted to purge the DB of all customers so ID will start from 1 again?
I'd stick with using an integer and having an IDENTITY set on the column. The smaller the key the more efficient it is. Using a GUID takes 4 times the space of an integer.
If you delete all rows from the table then the next row that is inserted will continue id sequence from where it left off. You can reseed the identity if you want, but I wouldn't bother. The most common reason I've wanted to do this was for testing. And if I'm testing I usually ensure that my whole test environment is reset - that means ditching the database and rebuilding it from a script.
giddy_guitarist wrote: 3. This is slightly embarrasing but , the thing i have to install on my clients pc is the file on this page thats 53 mb right?
Yes. SQL Server 2005 Express edition is quite large - but then again, it is increadibly powerful for how much you paid for it. (i.e. you get a lot of power for nothing).
Remember that a SQL Server is installed on one central machine (a server) and used by many clients. So, it doesn't need to be installed on every machine.
giddy_guitarist wrote: I know i need this to automate the intall correct?:
Unfortunately, I can't see what that is as MSDN isn't working for me at the moment.
|
|
|
|
|
hi Colin,
Yea , dont worry , i did do a good amount of reading on OO development , design patterns etc .. i'm kinda doing a three tiered app. UI, Business and Data. 
My model isnt very *good* though.... i cant seem to seperate business from data sometime. A business operation ends up manipulating data. Like if a something is added to the customers bill , BillingService adds it to the Customers Dues , this is just copying the amount into the Dues table , so this class is somewhat a mix between a data and business entity. Or should i have a seperate class under the dataServies namespace just to put an amount under a customers due?
ok , i have a few more sqlserver questions:
1. When i create a new sql db via VS i get a bunch of xsc , xss , xsd files and a Dataset?[like myDBDataSet] What are all these files?
2. Why does an application setting class have to be created. The only sepecial property i see is the connection string. DO i have to use the above class to change the conn string , my string will be a little complex because the application works over a network and it has to query the network class for the right path to the DB.
About sql server , it just seemed odd that the developer and user has to download the same thing. lol
Thanks!
Gideon
|
|
|
|
|
giddy_guitarist wrote: A business operation ends up manipulating data. Like if a something is added to the customers bill , BillingService adds it to the Customers Dues , this is just copying the amount into the Dues table , so this class is somewhat a mix between a data and business entity. Or should i have a seperate class under the dataServies namespace just to put an amount under a customers due?
Normally, the Data layer (DAL - Data Access/Abstraction Layer) will do all the data manipulation and the business class calls into the DAL.
e.g. This is a very simplistic example, and you'd probably want to have Address in a class of its own, but it illustrates the point:
class Person
{
public void ChangeAddrss(string address1, string address2, string city, string postcode)
{
this.address1 = address1;
this.address2 = address2;
this.ciy = city;
this.postcode = postcode;
Dal.UpdatePersonAddress(this.personId, address1, address2, city, postcode);
}
}
The Dal class does all the work to get data in and out of the database. The business object has no idea what it actually does, it just does it. It could send the data to SQL Server, to an XML file, or where ever. All the Business object knows is that there is something that needs to be stored and the Dal will store it.
giddy_guitarist wrote: 1. When i create a new sql db via VS i get a bunch of xsc , xss , xsd files and a Dataset?[like myDBDataSet] What are all these files?
No idea - I create databases in SQL Server and I don't use DataSets. I don't allow wizards to create anything for me. Wizards are greate for quick throw-away applications. But, if you use them for anything else you are making more work for yourself further down the line. Wizards make life especially complicated when you realise you've forgotten something - it is very hard to make changes to wizard generated code.
giddy_guitarist wrote: 2. Why does an application setting class have to be created. The only sepecial property i see is the connection string.
It is generally good practice to store the connection string in the app.config because then it can be updated without recompiling the applcation. You should not hard code connection strings into the application.
giddy_guitarist wrote: DO i have to use the above class to change the conn string , my string will be a little complex because the application works over a network and it has to query the network class for the right path to the DB.
You don't have to. If you work without wizards it wouldn't create anything like that for you. The wizards guide you in certain ways - not aways the way you want to go.
|
|
|
|
|
got it!
thanks so much.
about the dataset, i dont mean System.Data.DataSet , as soon as a i create a new Sql database (RClick Sol. explorer , Add|New Item|Sql DB) ... an xsc , xss and xsd file gets created and theres a class that gets added into my project : <databasename>DataSet
My database file is HTLDATA.mdf :
http://gidsfiles.googlepages.com/sql_dataset.jpg
And i did'nt do anything but add a new sqldb via VS2005.
Gideon
|
|
|
|
|
hi all
my requirement is like this on updation of table on the webserver an event should rise and let someone on the server should knwo that.....
i want a good site where i can get info. relating this or any suggestions how to do this....
thank you in advance
byebye
|
|
|
|
|
My question is in regards to SQL. I have a char field in a database with entries such as:
CurrentBOMCostRoll_12345_Lau
BackupMaintenance
CurrentBOMProcessor_3452_Lee
The information I need to get at is the numbers in between the underscores. For example, in the first record I need to pull out the 12345. The second record does not have any numbers to pull out. The third record I would pull out the 3452. These are names of jobs listed in the JobSys table. The numbers (_###_) are actually referencing a task number in another table of a different database. My end goal is to link the 2 tables based on the number in between the underscores.
I guess I am trying to unconcatenate a field. Any thoughts? Thanks!
Deb
|
|
|
|
|
select * from x where jobName like '%12345%';
File Not Found
|
|
|
|
|
Hi Deb,
Just tried out for your question. Use the foll. example. This example will returns remaining string after '-'. Do one thing, insert these values (rows) in a temporary table and use the same query again using the temporay table.
Uses : Substring(), CharIndex() and PatIndex():-
For example:
Use PUBS
GO
INSERT INTO @Table1 (col1, col2)
VALUES
SELECT
substring(Emp_Id,charindex('-',emp_id)+1,len(emp_id)-charindex('-',emp_id)) as EmplName,
emp_id
FROM Employee
SELECT * FROM @Table1
/*use the same expression as given below if there are more than 1 dashes in the column. So that you can pull out exact numbers from the second query */
where charindex('-',emp_id) != 0
If that works, then please let me know 
- Harini
|
|
|
|
|
Hi all,
I have a couple of questions that I hoping someone will know off the top of their head regarding SQL Server 2000.
1) Is there a short circuit mechanism in the if/case program controls? I would think so but logic doesn't rule the day unfortunatley.
2) In an update statement, is it necessarily true that a field will be updated in the order it was presented in the update statement?
Best Regards,
Keith
|
|
|
|
|
If you are talking about short circuiting in boolean conditions, then the answer is no.
Fields will not necessarily be updated in the order they are presented. Some fields require more work in an update, and (in a multi-processor environment) the operations to do this can happen alongside other parts of the update.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
wow, no short circuting of booleans, eh? So (X > 0 AND Y < 3), if x is -1 it will still evaluate y < 3? That's crazy!
On doing the updates, I kind of figured that. I'm not too familar on what goes on behind the scenes like if it locks the whole record or locks at the field level.
I can work with both these issues and will be good to know for the future.
Do you know if this is any different in SQL Server 2005? I know my company will be upgrading here sometime in the future ( I have seen the install discs with my own eyes). I just hope that whatever I have done will be easy to port over. 
Best Regards,
Keith
|
|
|
|
|
|
Very interesting. Well, that throws the conventional wisdom out of the window.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
|
I can't understand my friend what do you mean ??
jooooo
|
|
|
|
|
In your OP, you stated that you got a message and then just showed Smileys. I was trying to ask what message you actually got.
BTW - it's not a good idea to use the SA account to connect to SQL Server. This is a full privilege account that should not be used for general purpose access. You should create an account that just does what you need it to do and lower the attack surface of your application.
If you keep using the SA account, there is every chance that somebody could get access to your database and do things to it that you really don't want it to do.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks my friend for your reply >>
the message is >>
Could not load file or Assembly ( ADODB, Version=7.0.3300.0, Culture=neutral , Publickey token = bo3f5711d5.a3a) or one of its dependencies . The system can nptfind the file specified
I hope if you can aid me >>
jooooo
|
|
|
|
|
OK - that's kind of weird because the Sql classes don't use ADODB. They integrate with SQL Server at a much lower level.
Anyway, this section on the MSDN forums might be helpful: Clickety[^]
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks my friend for your interest ..
the problem is solved ,,,
but another problem appear ...
the new message is :
Can not logon to ServerComputerName
so sorry my friend to make you tire ,,
but my work is stopped for this problem
jooooo
|
|
|
|
|
I want to use the following update command multiple times:
command.CommandText = ("UPDATE EndOfMonth SET [Rented Depreciation] = @RD, [Unrented Depreciation] = @UD,");
command.CommandText += ("CashSales = @CS, BuyOuts = @BO, ChargeOffs = @CO WHERE StoreNumber = @SN");
command.Parameters.Add("@RD",OleDbType.Numeric);
command.Parameters.Add("@UD",OleDbType.Numeric);
command.Parameters.Add("@CS",OleDbType.Numeric);
command.Parameters.Add("@BO",OleDbType.Numeric);
command.Parameters.Add("@CO",OleDbType.Numeric);
command.Parameters.Add("@SN",OleDbType.VarChar);
How do I assign values to this command after defining it?
I can assign values to it at this time, but if I call it again, I will be adding more and more parameters, correct?
I am google searching the earth and this site, but only had 3 returns for OleDbCommand UPDATE.
Thanx in advance
Jude
|
|
|
|
|
command.Parameters.Item("@SN").Value = "MyNewValue"
|
|
|
|
|
|
Ok now I am stumped about something. Here is the parameter:
command.Parameters.Add("@RD",OleDbType.Decimal);
Here is the assignment:
command.Parameters["@RD"].Value = decTotDepr; (holds a decimal value of 40873.77)
The field is defined as Number, Field Size: Decimal, Format: General Number, Precision: 18(left at default), Scale: 0, Decimal Places:2 (in access).
When writing to the database, it stores only the integer part(40873).
Do I need to add more information to the definition of the parameter?
Jude
|
|
|
|
|
After scouring the earth for information on scale and finding nothing, I found ONE line defining what scale was in my 900+ page SQL 2000 book and nothing in my 400+ page access book. Just something little I guess you forget when you don't program everyday!
Jude
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
