Data fetch

Hi,

I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks

hi,

yes, it's possible... you just have to change your mind about how you fetch and work with data... do no longer "think" in terms of "tables" like in older JET paradgm where you opened a table and navigate via seek methods to the required "record", but "think" in terms of compacted recordsets.. thus filter the data you really need both in horizzontal and vertical terms..

in horizzontal term, returns only the actual columns you need, so provide the restricted column list of the projection you want (do not write SELECT * FROM but SELECT colX, colY, colZ FROM)..

in vertical term, provide a filter condition to the FROM clause in order to return just the "bunch" of rows you are interested with, so write

SELECT <col_list> FROM dbo.Customers WHERE CustomerID = theCustomerYouAreLookingFor

instead of

SELECT * FROM dbo.Customers

and the navigate to the customer row via client side Ado.Net/ADO/whatever seek methods..

regards

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data Design Issues

I am working on a Project Management application, and I have two data design issues I am debating.

The key element of this app, as you might expect, is Project. The project will proceed through many phases, from Planning, to Pre-Design, Design, Bid, Construction and Post Construction. All along the way there are a number of discrete tasks that must be performed and tracked.

I bounce back and forth in my mind between a single Project table that encapsulates all of these tasks, but am hesitant because I'm not a big fan of large monolithic tables. Alternatively, I could logically create separate tables for the various phases. However, this would create a series of one-to-one relationships between Project and the Phase tables, and require extra joins. The performance hit would probably not be too bad, but I would need to add extra code in either the app code or stored procedures to create an empty record in each of the phase tables when a new project is added. (Obviously, projects in planning or design will not have active records in the Construction and Post Construction tables). What are your thoughts about these choices?

Secondly, I have to manage data for a lot of individuals, which basically break down into two groups. First are internal employees who will have tasks routed to them, be invited to meetings, etc. Second are external vendors, basically contractors and consultants, who will be performing work and also be invited to meetings and such. I need to track the participants in meetings and inspections, so will have a Meeting Participant table to capture the many-to-many relationship. My issue is structuring the handling of the people. One option is to have a Person table, which basically includes everybody, with a flag field for internal or external people, and categories for their roles. Second is a table for internal folks and a table for external, or separate tables for Consultants, Contractors, and internal Employees. However, this makes capturing the meeting participants more cumbersome.

What is the collective wisdom on these? Thanks!

Jeff LittleThe place to start is the Time Tracker starter kit table design and make modification as needed. The second place is to test drive Enterprise Project Server it comes with Database templates for OLTP and OLAP to build cubes for the project. Check the link below for a demo I attended a while back with OLAP cubes, you can also search the TechNet site for more Project demos. Hope this helps.
http://www.microsoft.com/technet/community/events/project/tnt1-64.mspx

Kind regards,
Gift Peddie|||Here's my suggestion:

Project (ProjectID, ...)
Task (ProjectID, PhaseID, TaskID, TaskTypeID, TaskName, ...)
Phase (PhaseID, Name, ...)
TaskType (TaskTypeID, Name, ...)

Person (PersonID, PersonTypeID, PersonName, Username, ...)
PersonType (PersonTypeID, Name, Internal Bit, ...)|||Hi Jeff,

First off I would go with the idea of multple tables for the project instead of one. I have seen way to many applications where the number of fields and record length is outrageous given that most of the time a third to a half of the fields aren't populated. As for your concerns about adding an empty record, why? Simply do an outer join, where no matching record exists the field values from the applicable table will be null.

Lastly, people are people unless there is some compelling reason such as significant data requirements for internal as opposed to external they belong in the same table.

Cheers

Data corruption in ldf when debugging WinForm app

I'm trying to build my 1st WinForm app which uses SqlExpress and when I click Debug and test my app everything is fine (though occasionally I do get time out errors - but that's another question I guess), however I can't then debug the app again because I get the following error:

"The log scan number (34:410:82) passed to log scan in database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. \r\nAn error occurred during recovery, preventing the database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.\r\nCannot open user default database. Login failed.\r\nLogin failed for user 'SQurreL\\Simon'."

I can't delete the ldf or mdb files in the debug\bin folder because they are in use, so I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again... which is beginning to get right on my nerves now

Can anyone help with my sanity please?

Hi ganseki,

I think the error message has claimed very clearly: " .....that the log file (.ldf) does not match the data file (.mdf)." If mdf file and the corresponding ldf file cannot match, we cannot open that database again--that's designed by microsoft. So, we need to "If this error occurred during replication, re-create the publication. Otherwise, restore from backup..".

I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again

I think what you did is right. So, does this error still occure after you did that? Also i'm curious about what you did to that database thru your application, that you get that error message.

Hope my suggestion helps

|||

Thanks for the reply.

Any idea why that is happening each time I debug the app? Or is it normal for WinForms development to have to jump through those hoops in order to test the changes.

It takes longer to stop the service, kill the process, delete the files and restart the service than to make a change to a column width in a datagridview, which if I then find is too narrow or too wide means I have to go through that whole process again just to check that changing the width from 150 to 100 isn't enough? This involves no change to the database, all that would have happened is a select to get the data for the grid, but it happens even without any database interaction - i.e. just opening the app (which starts with a blank page).

After debugging I click the close button on the form, the app closes and Visual Studio quits debug mode. I make changes (which can be as trivial as my illustration above) and then have to go through that routine. And that happens each and every time I want to debug.

It is driving meinsane
Is there something I should be doing differently to stop this from happening?

data connection behind firewall

Hello,

I am trying to connect a web app to a SQL 2005 that is behind a firewall.

The challenge is this, the SQL is behind a firewall and the webserver is at a different location. From my desktop development environment, I connect through a VPN first and then can point to the internal IP of the DB server. This works fine as long as the VPN is connected.

But how do I do this from a hosted web application that is not on my local machine or using my VPN?

Is there some sort of tunneling Connection that I can use within code?

thanks

As you are going to deploy the web site to a remote location, to have SQL Connection via Tabular Data Stream would require opening up extra ports (1433 and 1434) - since the days of the Slammer worm, I doubt if any person in charge of a company network would allow these ports to be opended up to the outside world.

Can you deploy a copy of the database to the remote site? You could use SQL Express provided the database size was within 2Gb. Alternatively you write a web service, which only require Port 80 to be open through the firewall from your external host into your DMZ.

|||

Thanks for the reply. The challenge is that there is a database collecting live survey data...and that DB is in a different physical location behind a firewall, etc.

The application I am writting is on a different server/ different building, etc. The DB server has VPN access to it but I don't know how to include that in my code so that my app can talk to it and pull the live data.

(and you are correct, just opening those ports would be dangerous.)

|||

In which case you need to have the database both on the remote server on within your LAN. As it is a survey database, would i be correct to asume that data is only every inserted (unlil a survey is deleted en-mass)?

In which case include a web service on your remote server, set only to respond to the IP address of your LAN. Depending on the senitivity of your data you may elect to run HTTPS for the web service.

The key point is identifying batches of records to copy. Without knowing your database, I cannot comment of the best means of setting up the database to do this. If only inserting as I have suggested that you need a method that will return a manageable block of records. This method will be called by a Windows Service on a machine within your LAN which will then call a "make it so" stored procedure on your local database.

The required ports will be already open from your LAN out and into your remote IIS server.

data application blocks for windows mobile

I've read that microsoft.applicationblocks.data for .net v2 can't be deployed to a mobile app, and my experience bears that out. So I 'm wondering if there are application blocks for windows mobile 5 that would know how to both talk to sql server mobile and sql server 2005. I see OpenNetCF has a port but as far as I can tell, they only address sql server mobile and not talking to a sql server 2005 remote database. I can use the OpenNetCF version for my sql server mobile requirements, but I'm hoping there is an encapsulation of sqlclient calls for communication with my server db.

thanks

braden

There is also a nice port at www.businessanyplace.net

http://www.businessanyplace.net/?p=daabcf

and someone did a dual database (SqlClient and System.Data.SqlServerCe) block in .Net Developer's Journal a few months ago as well.

-Darren

|||Thanks Darren. I ended up doing my own by copying the methods I needed from the full fw set.

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
Regards
You may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.mic rosoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>