Create And Configure Databases Via C#

Feb 20, 2008



I am using Visual Studio 2008 and have created a .mdf database within my project. I want to be able to do a few things and I am not quite sure where to start. The program I am writing manages rental properties. Thus, each rental property that the user owns needs its own sql express database. For this to work:

1. I need to be able to dynamically create a database for each property that the user owns.
Is it best to do this as described here http://msdn2.microsoft.com/en-us/library/bb399420.aspx ? Or is it best to have a template database and then make a duplicate of the database using the File.Copy() method.

If it is best to do it programatically as shown in the link above, how do you create relationships between tables?

2. I need to be able to dynamically add users to the database via c# code. How does one do this? Is it possible do through LINQ?


Hopefully someone can point me in the right direction. Thanks

Nick

View 6 Replies


ADVERTISEMENT

Configure Replication Between 2 Databases

Nov 6, 2012

I have a software that is running with SQL Server 2005. The software has an utility to configure replication between 2 databases. When I was trying to configure it, for some reason the tool convert my main user database under system databases.

I need to put it back again under user databases.

Im thinking to backup my database first and put it offline and the restore the database again. But Im not to expert in SQL and also I am not sure if this will work putting back the database as a user database.

View 4 Replies View Related

Programatically Create And Configure A FlatFileConnectionManager

Jul 18, 2006

I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.

Here is some of my code:

Package p = New package();

ConnectionManager sourceCM = p.Connections.Add("FLATFILE");

sourceCM.Name = "some-name";

ConnectionManagerFlatFileClass cmffc = sourceCM.InnerObject()

 

I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:

ConnectionManagerFlatFileClass cmffc = (ConnectionManagerFlatFileClass)sourceCM.InnerObject()

Now it will compile. But when I run it I get the error:

"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"

 

Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?

-Jamie

 

P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!

View 3 Replies View Related

Can You Create Folders Under Databases In SMS

Aug 20, 2006

I see under databases in the object explorer of SQL Server Management Studio that there are two sub-folders: System Databases and Database Snapshots.

Is there a way to create other folders here? It would be nice to be able to group the various databases (ex. Samples).

tia

View 1 Replies View Related

How To Create Databases In SQL Express

Jun 15, 2006

 Autofreak wrote:

Hi All,

I have installed the SQL Server 2005 Express edition and its running fine.

I like to connect to the server and create database. Should I do it

through command prompt only or any tool is available like

Enterprise manager for this ?





View 1 Replies View Related

Create A Relationship In Defirent Databases

Jun 16, 2008

Hi,
 Is it possible to create a relationship between to tables in a deferent databases and How to do it from SQL Server Management Server!?
 
Thanks.

View 1 Replies View Related

Does SQL7 Create All Databases On C: Drive Only?

Dec 8, 1998

Hi,
Will SQL7 create all databases on C: drive only
from now on?

After SQL7 Beta3 installation I tried to create a new db
but in the 'Browse' window in 'Create DB' procedure
I can see only C: & D: drives (btw D: is CD-ROM).
It doesn't see my E:, F: & G: hard drives.
When I try to create my db using 'create database'
statement it again gives me an error 'path not found'.

Also, I have 512 Mb free on C: drive, but
when I create new db with 100-mb data + 50-mb log
on C: SQL7 says that I don't have enough space for
this db.

Does anybody had the same problem already?
I need to convert my Sybase db into SQL Server7
and got this problem with creating db right in the start.

Thank you

View 1 Replies View Related

Cannot Create Databases On SQL Express 2005

Aug 9, 2006

Help me please!
I've installed SQL Express 2005 on my XP. I'm trying to create a database but I keep getting the error no matter what I do.

Msg 1802, Level 16, State 4, Server MYCOMPUTERSQLSERV, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server MYCOMPUTERSQLSERV, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:Program FilesExos9300DatabaseExos93.mdf'.

View 1 Replies View Related

How To Create A Report Using Multiple Databases

Jan 31, 2007

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @Start) AND (Updated <= @End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

View 10 Replies View Related

How To Create A Report Using Multiple Databases

Jan 31, 2007

I have two database:

FoxPro database - contains client info (name, address)

SQL Server database - contains appointmetns, orders, jobs etc.

How can I create reports using both databases?

Thank you

View 3 Replies View Related

Can't Create Databases In Management Studio

Feb 5, 2006

I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...



TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server could not load DCOM. (Microsoft SQL Server, Error: 7404)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476



However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).

View 6 Replies View Related

Cannot Create Databases (and Other Issues) From Management Studio

Feb 6, 2006

 
I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...
 
TITLE: Microsoft SQL Server Management Studio------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server could not load DCOM. (Microsoft SQL Server, Error: 7404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476
 
However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).

View 2 Replies View Related

SQL Server 2008 :: Create View For Each Table In The Databases?

May 27, 2015

We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.

CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase

We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.

the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.

CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]

View 6 Replies View Related

Large Databases - Create Indexes For Fields That Are Used In Where Statements?

Aug 29, 2013

For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?

View 4 Replies View Related

Two Databases/servers - Can I Connect To Both To Create New Table On One Server?

Nov 20, 2007



Good morning all,

I need to create a new table on a SQL Server 2005 Database, but the data needed for the table is kept in different databases on different servers.

Half the data is on the SQL 2005 database, so that shouldn't be too much of a problem, but the other half is on a SQL Express database on a different server, is it possible to write a T-SQL query to retrieve this data on the other server?

Normally I use a create table + insert into kind of query to create custom tables but I don't know how to connect to the SQL Express server/database using T-SQL (if it's possible at all?) to do this.

Any tips, hints, ideas very welcome. Please use small words and short sentences.

Thanks,

Paul

View 3 Replies View Related

Cannot Open / Create SQL Express Databases (.mdf Files) In Visual Studio 2005 Professional Edition

Sep 7, 2007

Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder.  If I attempt either method, I get the following dialog box:Required Components MissingConnections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.  Please verify the installation of the component or download from the URL:  http://go.microsoft.com/fwlink/?LinkID=49251 The link above just takes you to the download page for Sql Server Express 2005. I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running.  The existing database will work properly in my web application, however I cannot access it through Visual Studio.As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error.  But I was able to fix that by renaming my SQLEXPRESS folder in C:Documents and SettingsuserLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Data.  When the web page ran, it created another SQLEXPRESS folder.However, I cannot figure out why I am having the other issue.Thanks in advancePatrick  

View 1 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Please Help Me To Configure SQL

Oct 29, 2007

Guys , I have a dedicated server ... I've installed SQL SERVER 2005 and I have a problem to connect to SQL from local machines in the office ... Remote connections are on , server BRowser starts automatically ... for example from remote machinne I mean from Server where SQL is installed I can connect to SQL ... FireWall i've turned off.... What can be the problem ???? 

View 3 Replies View Related

How To Configure SQL 2000

Nov 23, 2006

Hi, I got a website and everything works fine with SQL Server 2000, but now I modified the way of authentication using Forms and when I enter in a protected area I got this error:
An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by thefact that under the default settings SQL Server does not allow remoteconnections. (provider: Named Pipes Provider, error: 40 - Could notopen a connection to SQL Server)
The problem is that I don´t have SQL Server 2005 installed so I don´t know why my webapp is trying to contact it, where I can configure this settings so it will look for the Server that exists?
Thanks.

View 2 Replies View Related

Trying To Configure SQL Query

Mar 13, 2008

In my DAL, I have 3 tables -
Footballer (FootballerID = Primary Key)
Aspnet Membership Table (UserID=Primary Key)
Favourite Footballers (Junction Table with 2 Primary Keys FootballerID and UserID -  both with foreign relationships to their respective data table)
 
On my sports site,  visitors can view various footballers and store their favourites in th FavouriteFootballers Table by clicking on a button.
This stores the Logged in UserID and FootballerID in the junction table.
 I now need to  write a select query that allows users to click a link and view their favourites which they stored - lets say in a data list on another aspx page.
This would include other columns from the Footballer Table such as Name, Age, Profile ect
I think I am right in that I need to use a Select clause as I am using SQl data adapters in the DAL.
 Be very pleased if someone knows how to contruct the query and give me an example how to code a method in the BLL
Some code is below
 
 
 Method that Stores FootballerID and UserIDprotected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
 
// Assuming only have one button in the item templateInt32 fid = Int32.Parse(e.CommandArgument.ToString());Guid userid = (Guid)Membership.GetUser().ProviderUserKey;
 
 
 
 
// Connect to the db and insert the recordusing (SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True"))
{
 
 string query = string.Format("INSERT INTO FavouriteFootballer(UserID, ArtistID) VALUES('{0}',{1})", userid, fid);
 SqlCommand cmd = new SqlCommand(query, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
 
Code in FavouriteFootballersBLL
 Its not complete -withotu parameter -  i think maybe I also need to reference the Footballer Table and not the Junction one
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]public DataSet1.FavouriteFootballerDataTable GetFootballerFavourites()
{
return Adapter.GetFootballerFavourites();
}
 
 
 

View 2 Replies View Related

How To Configure SQLDataSource

May 14, 2008

Hi,
I am really having a hard time creating a parameter for the RadioButtonList radSearchField.  I want to type 's' in the text the choose the column for the query to execute the Select statement.  I was able to create a parameter for the txtSearchText but can't get the radSearchField working.
My form contains four objects.



Textbox to perform the query.  (name: txtSearchText)

View 5 Replies View Related

Configure Backup

Apr 8, 2005

Is there a way to automate backup? Like it has a schedule. Is this in standard edition.?
Please help a newbie.

View 5 Replies View Related

Configure SQL XML Support In IIS

May 17, 2006

I have configured SQL XML Support in IIS but still i am not able to Use
http://localhost/ss?Sql=select+*+from+users+for+xml+auto
 
I am getting Internal Server Error

View 1 Replies View Related

How To Configure A2K To SQL Server 7.0

Mar 19, 2001

Hello All,

We've A2k db which we plan to migrate to SQL 7.0 very soon. Though am familiar with upsizing wizard BUT don't know how to configure Access forms, reports etc. to talk to SQL back end tables. So that information entered via Access forms can be stored in sql and reports can be generated using sql tables..

Thanks in advance..

View 1 Replies View Related

How To Configure SQL Mail In 7.0

May 18, 2001

Hi,
I want to send a email after completion of DTS package.
How can I configure SQL Mail??

bye
K.R.Kanth

View 1 Replies View Related

How Do I Configure Sql Mail

Sep 2, 2000

hi
Can anyone help me in configuring Sql mail in SQL server 7.0

View 2 Replies View Related

Configure SQL Mail

Jul 30, 2003

Hi,
I'm using Exchange server for our mail communication. If wanted to config SQL Mail in SQL Server 2000 is Ok
1. Insall outlook in sql server
2. US Exchange mail server name in sql mail or any procedure I need to do?
Thanks,
Ravi

View 3 Replies View Related

How To Configure SQL SERVER 6.5

Oct 27, 1998

What are the steps in configuring sql server 6.5 to have excellent performance?
what are the items that I should think of and based on what condition I can set up the configuration?

Ahmed

View 1 Replies View Related

Which Raid To Configure

Mar 17, 2004

Hello,
I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that.
I am confused here about which RAID should I install on this server RAID 1 or RAID 5.
The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5

View 7 Replies View Related

Cannot Configure Distribution

Jan 28, 2008

Forum,

I cannot create a Distribution Server on SQL Server 2005 SP1. When I right click on Replication and click "Configure Distribution" and hour glass appears for a minute, and then nothing happens.

I get the same result if I try to create a publication.

Any suggestions on what to do?

Thank you!

View 4 Replies View Related

How To Configure Sql Server

Jul 20, 2005

Hi All,Here is my questionWhat might you configure on a SQL Server so that a Web server runningon the same machine, with limited memory, will continue to performoptimally?Thank youRaju

View 1 Replies View Related

Unable To Configure Add-in

Feb 22, 2007

I have installed SP2 on my SQL 2005 instance which also houses RS of course. Additionally I installed WSS3.0 without configuring it so it has the Object Model installed (should be enough?)

I am running WSS 3.0 on a seperate Server and have the Add-in installed on there.

Following the installation instructions I am going to Application Management to configure WSS, but there is no section called "Reporting Services". Looking on the Report Server, in the Configuration I see the following error under the SharePoint Integration Setting:


The report server cannot access settings in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model is not installed or the Report Server Web Service and Windows service accounts do not have access to SharePoint databases. To configure service account access, use SharePoint Central Administration.

In my SharePoint Site I do see a SharePoint section under the "Site Settings" for managing shared schedules but I think the main issue is, that I don't see that section in Central Administration.

Any help is greatly appreciated.

View 3 Replies View Related







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