Database

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

20-Dec-04 11:30

WaqasButt wrote:
HOw we can take sql server db backup in c#.net?

The same way as any other database access using C#, by using the SqlConnection and SqlCommand objects.

You can write some C# that makes a connection to the database (so long as the user making the connection has the sysadmin, db_owner or db_backupoperator role) then you can issue the BACKUP DATABASE command with the SqlCommand.ExecuteNonQuery()

For details on the BACKUP command you may with to read the SQL Server Documentation[^]

So, your C# code will look something like this:

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("BACKUP DATABASE...", conn);
cmd.ExecuteNonQuery();

Does this help?

Restore a database

bigtone7820-Dec-04 9:32

bigtone78

20-Dec-04 9:32

Does anyone know how to restore a .bak file to a MS SQL Sever. I think the .bak file is a backup of a .mdf file but I don't have the logs or any of the associated files.

Re: Restore a database

Dallas Matthews20-Dec-04 10:01

Dallas Matthews

20-Dec-04 10:01

All Tasks -> Restore Database... -> Restore from Device (radiobutton option) -> Select Devices... -> Add (supply a filename)

After all this, you should be able to do all your "Oks" and restore as normal.

Dallas

Re: Restore a database

bigtone7820-Dec-04 10:09

bigtone78

20-Dec-04 10:09

I've tried that but I get some big long error about my data and log files not being found.

Re: Restore a database

Rob Graham24-Dec-04 6:00

Rob Graham

24-Dec-04 6:00

Choose the options tab and check "force restore over existing database". The3 backup filewas probably created on a different machine and the database and logfiles were located in a diufferent place (dreive/directory) on thet machine. The backuup 'remembers' the original locations, and tries to update the files in that place unless you select this option.

Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright

sql transaction

sianatia20-Dec-04 8:20

sianatia

20-Dec-04 8:20

hi,

i wnat to know if there is a way to execute several sql statements in one transaction like sql Query Analyzer.
for example i want to have this to statements together in one sqlcommand
"delete from mytable

go

delete from mytable2"
in Query Analyzer this works but in .net it doesn't

is there any solution?

Re: sql transaction

Colin Angus Mackay20-Dec-04 8:35

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

20-Dec-04 8:35

The "GO" is a delimiter for the Query Analyser only. You can change it to what ever you want by going to Tools-->Options... and selecting the Connection tab. There is an item called Batch Separator that you can set.

The example you gave does not put both DELETE commands in one transaction

You do not have to separate all commands into individual batches. So you can send two commands in one batch, like this

DELETE FROM mytable; DELETE FROM mytable2;

Of course, if you need to call multiple commands then the the better solution is to use stored procedures and call the stored procedure. In the query analyser:

CREATE PROCEDURE DeleteStuff
AS
    BEGIN TRANSACTION
        DELETE FROM mytable
        DELETE FROM mytable2
    COMMIT TRANSACTION
GO

Then in your .NET application:

SqlCommand cmd = new SqlCommand("DeleteStuff", myConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

Does this help?

Single connection in several forms

hammerj18-Dec-04 23:58

hammerj

18-Dec-04 23:58

I would like to use one connection in my windows application, and I have more than one form.
The problem is that when i drag a DataAdapter from the ToolBox to the form, it wants to create a connection with the wizard.
I don't want this to happen, and I would like some way to use the designer in order to use a DataConnection that was defined elsewhere.

I know I can create the connection to the same database in each form, but my problem with this approach is that there is no real need to create the same DataConnection again and again for no good reason.

I want only to use the designer in order to create the DataAdapter. Is there some way to do it without programming?

Colin Angus Mackay19-Dec-04 2:13

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

19-Dec-04 2:13

You should create a singleton class to manage your connection and have your forms request the connection from your connection management class.

hammerj wrote:
I want only to use the designer in order to create the DataAdapter. Is there some way to do it without programming?

Not that I know of. Why don't you want to program?

j1hammer19-Dec-04 2:34

j1hammer

19-Dec-04 2:34

It is not that I don't want to program. I think it is conceptually wrong to create several objects when I only need one. Lets think of the case where I have 5 connections to the same DB, and then i want to change the connection.

I think of a scenario where I set a connection on one form, and I can use it in the designer for all forms.

Colin Angus Mackay19-Dec-04 2:38

Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

19-Dec-04 2:38

If you are thinking conceptually, then the Visual Studio designer is probably the last thing you want to use.

I think your best approach is to create a class that manages the connection and have your forms use that class. Also, it would probably be a bad idea to have one form set the connection and have the other forms use it from the first form as the connection has nothing to do with the user interface and the data management functions should really be separated out into its own conceptual layer/tier within the application.

Re: Login Failed : user ASPNET (MSDE)

j1hammer19-Dec-04 23:34

j1hammer

19-Dec-04 23:34

Sadly I agree, although using the designer is one of my favorite tools in .NET.

asif m@hmood18-Dec-04 7:28

asif m@hmood

18-Dec-04 7:28

i have MSSql Desktop engine installed and i m connecting to a database from ASP.net page. i m using oleDBconnection but when i Open() connection it gives me exception "Login failed 'MATRIX\ASPNET' " where Matrix is machin name. it is working well in Windows form but only givin problem in webforms , can any body tell wats wrong with it , i m using same connection string as for windows forms.

Mike Dimmick18-Dec-04 8:38

Mike Dimmick

18-Dec-04 8:38

You're using Integrated Security in your connection string; that is, Windows Authentication. In this mode the database uses the credentials of the thread to authenticate the connection. Your Windows Forms application is running under your own user account, which is probably a member of the machine's Administrators group. The machine's Administrators group is granted login rights by default, and is a member of the System Administrators server role by default. In effect, your Windows user account has unlimited control over the database server.

By contrast, ASP.NET applications by default run under the ASPNET account on the local machine. This account is only a member of the Users group, which by default cannot log in to SQL Server.

You can allow the MATRIX\ASPNET user to log in using the following command (use osql to run the command):

EXEC sp_grantlogin 'MATRIX\ASPNET'

The user can now log in to the database server, but won't be able to access any databases. Grant access to the user on the database by executing:

USE database
EXEC sp_grantdbaccess 'MATRIX\ASPNET'

where database is the database that the user is granted access to.

This adds the user to the public database role. All users granted access to a database are members of this role. By default, this role has no permissions on objects created by the database owner. To grant permissions, you need to use the GRANT statement. It's recommended that you grant the public role only the permissions that are needed to perform the tasks. For example, if you wanted to allow all users to read data from the authors table, you would say

GRANT SELECT ON authors TO public

Don't get confused between the REVOKE and DENY statements. DENY allows you to filter members from a group - for example I could use the statement

DENY SELECT ON authors TO Mike

and SQL Server would then allow everyone except me to read from the authors table. REVOKE is used to undo an earlier GRANT or DENY operation.

If you want to give the ASPNET user complete control over the database - which is unadvisable - you can instead run

EXEC sp_addrolemember 'db_owner', 'MATRIX\ASPNET'

I really don't advise this, though, as a mistake in the data access layer could corrupt your database. I recommend restricting the db_owner role to development and deployment use only.

Stability. What an interesting concept. -- Chris Maunder

Libraries

abo el ror18-Dec-04 3:32

abo el ror

18-Dec-04 3:32

What libraries are needed to use the following code:
SQLDMOSvc_Running (SQL SERVER state).
When i use Set oSqlServer = New SQLServer, the (SQLSERVER) command is not recognised. So please supply a help.

MSDE US Date format problem

hugonz17-Dec-04 23:40

hugonz

17-Dec-04 23:40

Hello everyone,

I'm currently building a C# Windows Forms application, and have another machine on my network running MSDE 2000 as the database for my Windows App.

Its working fine except for one problem, MSDE keeps storing dates in US format (mm/dd/yy) instead of the format I want (dd/mm/yy). I'm passing in the dates to store as a string in the format dd/mm/yy in my SQL statements.

In the Regional Settings of the server computer, the locale is set to Enlish (New Zealand), and the short date format on the date tab is set to dd/MM/yy, yet it continues to use the wrong date format in MSDE. Do I need to somehow specify the locale that MSDE uses?

Any help would be greatly appreciated, this one is driving me nuts.

Many thanks, Hugo.

Mike Dimmick18-Dec-04 9:04

Mike Dimmick

18-Dec-04 9:04

You can make osql format dates and times in your locale using the -R option. My installation of osql presents dates in ISO format (yyyy-MM-dd hh:mm:ss.nnn) when this option is not specified. I'm using the Latin1_General_CI_AS collation.

You should use the datetime or smalldatetime data types when handling date information. Always use parameters[^] to submit date/time information - indeed, any data - to the database.

In your application, you should be using the locale-sensitive format strings when using DateTime.ToString. You should check the value of System.Globalization.CultureInfo.CurrentCulture.Name - it should be en-NZ. If it isn't, check that the values in the Regional and Language Options[^] Control Panel applet (Regional Options[^] in Windows 2000) are correct.

Stability. What an interesting concept. -- Chris Maunder

hugonz18-Dec-04 11:19

hugonz

18-Dec-04 11:19

Thanks for your reply Mike,

I've checked the value of System.Globalization.CultureInfo.CurrentCulture.Name on both my development machine (Win XP) and the server machine (Win2k server) and they are both en-NZ. I am using smalldatetime data type to store the date.

Do I use the -R option on the osql command line after installtion, or is that something I need to specify for the installtion? I believe that just makes it use the client's locale settings?

Thanks for your help.

Jon Hulatt19-Dec-04 22:51

Jon Hulatt

19-Dec-04 22:51

set dateformat dmy

However, If you use an intrinsic datetime data type from .Net, then you can use .Net's intrinsic culture support to format the date how you want.

Sql Server doesn't store a data in any particular culture. The date is if you like "rendered" according to various settings/defaults when it's displayed.

using System.Beer;

hugonz21-Dec-04 18:36

hugonz

21-Dec-04 18:36

Thanks Jon,

Yes I was thinking that it was being stored in the database in a certain culture (i.e. US format), but what your saying is that its not stored in any particular format at all? It only gets converted to a "culture" when its displayed?

What I'm doing is reading dates directly from a text file (downloaded from tha bank each day) in the format "dd/mm/yy", and inserting that straight into the MSDE database through .NET. To view that data I created a MS Access project and I am just viewing the tables directly where I am inserting the data. If I insert 12/08/04, Access shows it in the tables as 08/12/04. Is that being stored incorrectly? Or is it just being displayed in the wrong format?

Appreciate your help,

Cheers, Hugo

Installing Database

abo el ror17-Dec-04 14:29

abo el ror

17-Dec-04 14:29

How can I install a Database on the SQL SERVER 2000 programatically using VB.Net code?

With Regards
Rawad Eid

Re: Installing Database

Colin Angus Mackay17-Dec-04 15:28