|
Some time back I had a requirement of using connecting string passed as command line argument to an application.
The problem was that the application was using Enterprise Library 1.1 which uses dataconfig.config file for database connection information.
After some research i was able to find a way to create Enterprise Library Database object from Connection String. I thought that this would also be useful to other developers.
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
private static Database CreateDatabaseFromConnectionString(string connString)
{
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connString);
DatabaseSettings settings = new DatabaseSettings();
DatabaseTypeData type = new DatabaseTypeData("Sql Server", "Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null");
settings.DatabaseTypes.Add(type);
ConnectionStringData connectionString = new ConnectionStringData("DBConnection");
connectionString.Parameters.Add(new ParameterData("server", builder.DataSource));
connectionString.Parameters.Add(new ParameterData("database", builder.InitialCatalog));
connectionString.Parameters.Add(new ParameterData("integrated security", "false"));
connectionString.Parameters.Add(new ParameterData("user id", builder.UserID));
connectionString.Parameters.Add(new ParameterData("password", builder.Password));
settings.ConnectionStrings.Add(connectionString);
Microsoft.Practices.EnterpriseLibrary.Data.Configuration.InstanceData instance = new Microsoft.Practices.EnterpriseLibrary.Data.Configuration.InstanceData("DBConnection", "Sql Server", "DBConnection");
settings.Instances.Add(instance);
Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationDictionary configurations = new Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationDictionary();
configurations.Add("dataConfiguration", settings);
Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationContext context = Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationManager.CreateContext(configurations);
Database database = new DatabaseProviderFactory(context).CreateDatabase("DBConnection");
return database;
}
Of course you can also set the integrated security parameter if you want.
cheers
|
|
|
|
|
i'm using a stored procedure to get two values out from the procedure after execution,usually one one value can be passed out by a procedure,can i use refernce varialbles for this,
if my procedure wants to return a set of records how can i implement the procedure
|
|
|
|
|
You can use out parameter if you have to return more than one values..
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
|
|
|
|
|
Hi everybody.
I have a table in database Access with other font(example:VN-Times)
I want to convert all data and structure to SQL 2005 with Unicode font.
Please to help me.
Thanks for all suggetion
if you cound not try then
msgbox("you will lose")
else
msgbox("you can change yourself")
end if
|
|
|
|
|
Define the string datatype column in SQLServer table as nvarchar...
and then import data it will work..
Thanks
Hem
|
|
|
|
|
Is possible to create an XmlDataSource that has 50 columns merge an sql database with 14 columns into and force the other 36 columns except and false or any value I want with c#
Learning to Code
|
|
|
|
|
You is going to get flamed if you post the same question in multple forums.
Read the forum guidlines
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to get the number of opened accounts per month in. For the most part I can get this information but if there was no account opening for March then March does not show up in the list. Here is what I have.
select Months, count(*) as 'Account Openings' from
(
SELECT
CASE
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'
ELSE 'December'
END AS Months
FROM account
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'
) as OpenedPerMonth
group by Months
order by Months
I would get something like this as output
April 12
August 44
February 55
January 19
July 12
June 11
March 42
May 11
September 6
Thanks
Matt
|
|
|
|
|
Have you turned the ANSI_NULLS OFF?
Blog link to be reinstated at a later date.
|
|
|
|
|
select Months, isnull(count(*),0) as 'Account Openings' from<br />
(<br />
SELECT<br />
CASE<br />
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'<br />
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'<br />
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'<br />
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'<br />
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'<br />
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'<br />
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'<br />
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'<br />
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'<br />
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'<br />
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'<br />
ELSE 'December'<br />
END AS Months<br />
FROM account<br />
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'<br />
) as OpenedPerMonth<br />
group by Months<br />
order by Months
Hope it will help.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
why dont you use month() function 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I need to retreive some info from an SQL2005 db, but am not knowledgeable in SQL to work out an efficient statement. Your help will be greatly appreciated.
I need to retreive data for a given user(db_User) whilst counting info from a second column(db_Hours).
However to make things a bit more difficult id like to count/sum all the (db_Hours) values as two named fields. The values of these named fields will be grouped according to a 3rd column(db_Valid) will either be 'yes', 'edit' or 'no'.
SO in other words id like one statement (if possible) that will get me all the values of (db_Hours) for a named user, as two variables. One being the sume of all the 'db_Hours' where 'db_Valid' is equal to "yes", and a second where 'db_Valid' is not equal to 'yes'.
I hope this makes sense, and that some kind person is able to help me out.
Many Thanks guys! 
|
|
|
|
|
Why don't you give a sample of the tables you are trying to pull from and the relevant fields that are in those tables.
I'm guessing you are going to have a statemnet that will include a Group By clause and Sum function, but we can't create it for you without specifics. It may end up to be something like this:
SELECT User, Valid, SUM(Hours) FROM MyTableName GROUP BY User, Valid
|
|
|
|
|
Hi I am trying to find out how many records I have between two time periods. I have the following code:
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE (Scan_Time between '9/29/2008 9:00:00 AM' AND '9/29/2008 09:59:59 AM')
This will work and give me the number of records that are between the specified time ranges. However, I will be running this query each day and would like to not have to go in each day and specify the date. I will be clearing the database each day and thought that I could just remove the date portion, however, when I do this there are no records reported.
I am new to SQL and any help would be greatly appreciated
Thanks
.NET Framework Data Provider for SQL Server Mobile Edition
version 3.00.000
|
|
|
|
|
You could use DATEPART function and perhaps even GROUP BY hour (again by using datepart) if needed. This way you would get all counts for all hours in single statement.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks for the idea Mika. I had thought about using DATEPART, but was not sure if I would be able to shorten the time period if I needed to i.e. between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
You're welcome. Just use hh for the first parameter and use the DATEPART function in either WHERE clause or GROUP BY.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that worked for reporting at 1 hour intervals. Any idea how I could search between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
If you want to use 10 minutes interval, you could use a trick like this:
SELECT (DATEPART( minute, GETDATE()) / 10)
It's not pretty, but gives you full 10 minutes (0-5)
The need to optimize rises from a bad design
|
|
|
|
|
create a stored procedure and make the user/app calling it pass in the dates they wanted to count between. or if you just wanted to do 2 queries datepart with MIN in place of HH would work the same, mm is month as i remind my self every so often when i use date part to mess with minutes.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Thanks for the help. Using the datepart and using a union to have 2 queries appears to work.
Thanks for the help
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(hh,Scan_Time) between 09 and 09 union SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(mi,Scan_Time) between 50 and 59
|
|
|
|
|
listen,
check functions:
dateadd(),getdate(),datediff()
will do fine
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
hi to all,
required connection string in asp for sql server 2005 express
its urgent.
please help me out.
i tried many... they are
1. objConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=ip address;UID=kjkl;PWD=jhjk"
2.objConn.ConnectionString = "Provider=SQLNCLI;Server=ip address\SQLEXPRESS;Database=645654;UID=321321;PWD=23164;"
3.objConn.ConnectionString = "Provider=SQLOLEDB.1;UID=321321;Password=mypassword; Initial Catalog=mydatabase; Data Source=.\SQLExpress"
4. 'DSN="Driver={SQL Server};Server=ip address;Database=cfsdf;UID=35131;PWD=23131"
nothing works
help me plz
thanks
|
|
|
|
|
This issue is answered several times.
Even in this forum.
Even within few days.
So, scroll questions down a bit in this forum and you may find what you need.
The need to optimize rises from a bad design
|
|
|
|
|
i did wat u said. but when i'm saying that i tried many it means i search many and even this forum also. And i found nothing helpful.
so help me.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
