Using SQLCE As A Middle Tier Caching Solution
Dec 26, 2007
Hi All,
Im trying to migrate a N tiered solution written in Java and Berkeley DB(in memory DB product) to MS stack. ie the in memory DB is being used as a middle tier caching solution.
Im considering using SQLCE where Berkely DB was being used, im guessing that SQLCE being an inproc DB may be faster than using SQLExpress etc.
Has any one tried to do this before? and if so can you share your experience?
Alternatively if you can provide any resources on the web that discuss SQLCE performance(ie not when run on WinCE etc)/ comparison of query performance between SQLCE and SQL Express/Standard that would be great.
Thanks in advance
-Melvin
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
There doesn't seem to be consensus about when to put code in thedatabase or in the middle tier. There was a long discussion about thisin an Oracle newsgroup (message ID:ULcQb.466$KU5.37@nwrddc02.gnilink.net).Elsewhere there's been discussion about Microsoft SQL Server 2005adding the CLR to support stored procedures in languages such as C#. Ascan of the Web and discussion forums finds differing opinions aboutthis.Two authors have written articles that fall on different sides of thedebate."Keys to the Database"http://www.intelligententerprise.co...icleID=50500830"SOA, Multi-Tier Architectures and Logic in the Database"http://www.sqlsummit.com/Articles/L...TheDatabase.HTMJoe Celko wrote the first article, but his objections point toMicrosoft SQL Server 2005:"I have an article at WWSUG.com on how much I hate the CLR stuff thatMicrosoft is putting out."http://blog.intelligententerprise.c...ves/002419.html"The bad news is that SQL Server 2005 will you define your ownaggregatefunctions in a CLR language."Message id: Join Bytes!IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is thisa non-issue or are all three companies misguided?
View 49 Replies
View Related
Feb 15, 2008
I create data collection applications on Mobile Devices for field biologists. The biolgists need to sync thier field data with a local database upon returning from the field. I am planning on using sqlce as my mobile database and am trying to get a grasp of what my synchronization options are. Here are some of my questions.
1.) Is there any way to synchronize data between a SQLCE database on the mobile device and a SQLCE database on the desktop (third party or othewise)? It appears to me that the answer is no, which is a shame. Whats the use of having a free light weight database that you can use on a mobile device and as a backend on a desktop but not sync data between them?
2.) If my hunch is right, what are my sync options? Installing SQL Server on every field computer is not an option ($$$). If I'm bound to using SQL Server as the backend database, is it possible to have a single SQL Server database on a webserver that the field locations could sync to remotely?
Any feedback to get me started in the right direction would be greatly appreciated.
Mike
View 1 Replies
View Related
Feb 19, 2008
I take it SSCE 3.5 only runs under .Net 3.5? My project was updated from VS05 to VS08 but the target is still .Net 2.0 would this be why I cant use the SQLCE namespace.
Forgive me for being noobalishous but I have been thinking that SSCE 3.5 would be a great solution for my app as I want to be able to use SQL Server Express 2005 and some other Local DB system (SSE is too bulky for my app when networking is not required). But if the prereq is that the client must have .Net 3.5 installed then I am back to square 1 because .Net 3.5 is like 195Mb install (which i tried installing as a prereq on my friend's Vista Ultimate(?) machine yesterday and it crashed it). Aaargh!! How are you supposed to develop small DB apps (3.5Mb max for app footprint) in the VS environment that don't require massive installation prereqs???
Dont get me wrong I love VS but surely there has to be a way to minimise prereq overheads.
View 5 Replies
View Related
Sep 13, 2005
I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).
View 7 Replies
View Related
May 22, 2008
Hi
I think the subject is pretty clear.
We are thuinking of installing Server 2008 in x64 mode on a new Application-Tier-Machine. But due to existing projects which use several databases the SQL-machine runs on a Server 2003 x32.
Would it be possible to install the Reporting Services x64 binaries on the app-tier but use a x32 SQL-Instance for the data?
Thanks in advance
View 1 Replies
View Related
Jul 20, 2005
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
View 2 Replies
View Related
Feb 6, 2006
I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
View 3 Replies
View Related
Jun 9, 2006
Does anyone have any instructions on how to go about uprading a .sdf file
from sqlce 2.0 to sqlce 3.0?
I found this on an msdn website
(link: http://www.microsoft.com/sql/editions/sqlmobile/upgrading.mspx) but
as you can see its very lacking on detail. (I included the link and content
referring to upgrading the database files so you won't have to go to the link
to see the part referring to upgrading the database.) I tried to run the upgrade.exe on my device but it doesn't explain how you're supposed to point it to the .sdf that you are trying to update. Here is the content on the above web page.
Upgrading Database Files
You can upgrade a database created with an earlier version of SQL Server CE
2.0 to a database created with SQL Server Mobile by using the SQL Server
Mobile Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on
a smart device.
When you run the SQL Server Mobile Database Upgrade tool, the new SQL Server
Mobile database is created on the smart device. The new database, with the
file name extension .sdf, contains all the data that was in the source
database. You need to reinitialize the upgraded database to continue using it
for replication.
Note: If you are using replication or remote data access (RDA) as a
connectivity solution before upgrading to SQL Server Mobile, you must
synchronize the source SQL Server CE database (subscription database) with
the SQL Server database (Publisher). The reason for synchronizing before
upgrading to SQL Server Mobile is to ensure that any changes that exist in
the tables on the SQL Server CE database are updated on the SQL Server
database, because after the upgrade you must resubscribe or repull using the
new database.
View 3 Replies
View Related
Oct 11, 2006
I presently run on my server SQL SERVER CE 2.0 and want to install, in addition on SQL Server 2.0, SQL Server Mobile 2005.
Is there a procedure to have both installed and running on a computer?
Thanks!
Marco
View 2 Replies
View Related
Feb 27, 2007
Hello all,
Q1:In all asp.net books the authors would tell you to seperate your presentation , business, and data layer but why does the wizards for the sql data source combine the presentation layer with the data layer?
Q2:Is there a way to retreive a dataset after you execut the sqldatasource.insert() command? .......I have a store procedure(sp) that i am using for inserting records. The sp is where i 'm validating all my data (shop standard) for example, ssn length, last name <> 0 etc. If there are any errors i grab all the error code from another table and return it. I can't seem to figure out how to get the dataset....
View 3 Replies
View Related
Feb 10, 2005
Hey all.
I've just been doing some surfing on the pros and cons of multi-tiered environments. I see a lot of conflicting information out there.
I'd like to get everybody's opinion of the advantages and disadvantages of two-tiered vs three-tiered applications, specifically with a SQL Server back-end and all the functionality that comes with it (TSQL, triggers, user-defined functions, etc...).
Also, some definitions or principles regarding what tasks should be handled in which layers (especially business logic).
Brett? Pat? RJabarov? Rudi? Others?
View 14 Replies
View Related
Nov 30, 2006
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.
The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks,
Barry G. Sumpter
Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express
Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.
View 17 Replies
View Related
Nov 30, 2006
using System;
namespace BaResearch.Data.msSql
{
interface ISqlDataObject
{
System.Data.SqlClient.SqlConnection Connection { get; }
string ConnectionString { get; set; }
}
}
___________________________________using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BaResearch.Data.msSql
{
public class SqlDataObject : ISqlDataObject
{
private string _ConnectionString;
public SqlConnection Connection
{
get { return new SqlConnection(_ConnectionString); }
}
public string ConnectionString
{
get
{
return _ConnectionString;
}
set
{
_ConnectionString = value;
}
}
}
}
__________________________________using System;
namespace BaResearch.Data
{
interface IBid
{
string Client { get; set; }
string Contact { get; set; }
string Sponsor { get; set; }
string Priority { get; set; }
string BidStatus { get; set; }
}
}
_____________________________________________using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace BaResearch.Data.msSql
{
public class Bid : SqlDataObject, IBid
{
#region private member & variables
private int _BidID;
private string _DateCreated;
private string _CreatedBy;
private string _Client;
private string _Contact;
private string _Sponsor;
private string _Priority;
private string _BidStatus;
private SqlDataAdapter sqlda = new SqlDataAdapter();
private SqlCommand _command;
private SqlParameter[] _parameters =
{
new SqlParameter("@client",SqlDbType.NVarChar),
new SqlParameter("@contact",SqlDbType.NVarChar),
new SqlParameter("@sponsor",SqlDbType.NVarChar),
new SqlParameter("@priority",SqlDbType.NVarChar),
new SqlParameter("@bidstatus",SqlDbType.NVarChar),
};
#endregion
#region public member variables - stored procedures
//private string sp_Create = "bids_sp_insert";
//private string sp_Update_ByID = "bids_sp_updatebyid";
//private string sp_Delete_ByID = "bids_sp_deletebyid";
//private string sp_Select_All = "bids_sp_selectall";
//private string sp_Select_ByID = "bids_sp_selectbyid";
//private string sp_Select_ByValue = "bids_sp_selectbyvalue";
#endregion
#region constructors and destructors
public Bid()
{
if (this.ConnectionString == null)
{
this.ConnectionString = ConfigurationManager.ConnectionStrings["SQL.ConnectionString"].ConnectionString;
}
else
{
new Bid(this.ConnectionString);
}
}
public Bid(string ConnectionString)
{
this.ConnectionString = ConnectionString;
}
#endregion
#region methods & events
/// <summary>
/// Attach parameters to an SqlCommand
/// </summary>
/// <param name="sqlcmd">SqlCommand</param>
private void attachparameters(SqlCommand sqlcmd)
{
sqlcmd.Parameters.Clear();
foreach (SqlParameter param in _parameters)
{
sqlcmd.Parameters.Add(param);
}
}
/// <summary>
/// Assign value to the parameters
/// </summary>
/// <param name="sqlcmd">SqlCommand</param>
/// <param name="value">Client object</param>
private void assignparametervalues(SqlCommand sqlcmd, Bid value)
{
this.attachparameters(sqlcmd);
// todo: assign parameter values here
sqlcmd.Parameters[0].Value = value.Client;
sqlcmd.Parameters[1].Value = value.Contact;
sqlcmd.Parameters[2].Value = value.Sponsor;
sqlcmd.Parameters[3].Value = value.Priority;
sqlcmd.Parameters[4].Value = value.BidStatus;
}
public virtual void Create(Bid value)
{
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"INSERT INTO bids (client, contact, sponsor, priority, bidstatus, createdby)
VALUES (@client, @contact, @sponsor, @priority, @bidstatus, @createdby);";
_command.CommandType = CommandType.Text;
try
{
sqlda.InsertCommand = _command;
this.assignparametervalues(sqlda.InsertCommand, value);
sqlda.InsertCommand.Parameters.AddWithValue("@createdby", value.CreatedBy);
sqlda.InsertCommand.Parameters.Add("@bidid", SqlDbType.Int);
sqlda.InsertCommand.Parameters["@bidid"].Direction = ParameterDirection.ReturnValue;
sqlda.InsertCommand.Connection.Open();
sqlda.InsertCommand.ExecuteNonQuery();
sqlda.InsertCommand.Connection.Close();
sqlda.InsertCommand.Connection.Dispose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public virtual int Update(Bid value)
{
int result;
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"UPDATE bids SET client=@client, contact=@contact, sponsor=@sponsor,
priority=@priority, bidstatus=@bidstatus
WHERE bidid=@bidid";
_command.CommandType = CommandType.Text;
try
{
sqlda.UpdateCommand = _command;
using (SqlCommand _cmd = sqlda.UpdateCommand)
{
this.assignparametervalues(_cmd, value);
_cmd.Parameters.AddWithValue("@bidid", value.BidID);
_cmd.Connection.Open();
result = _cmd.ExecuteNonQuery();
_cmd.Connection.Close();
_cmd.Connection.Dispose();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return result;
}
public virtual void Delete(Bid value)
{
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"DELETE bids WHERE bidid=@bidid;";
_command.CommandType = CommandType.Text;
try
{
sqlda.DeleteCommand = _command;
using (SqlCommand _cmd = sqlda.DeleteCommand)
{
_cmd.Parameters.AddWithValue("@bidid", value.BidID);
_cmd.Connection.Open();
_cmd.ExecuteNonQuery();
_cmd.Connection.Close();
_cmd.Connection.Dispose();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public virtual DataSet List(string filter)
{
DataSet result = new DataSet();
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"SELECT * FROM bids_view_listall
WHERE (clientname like @value) or (contactname like @value)
or (sponsor like @value) ORDER BY datecreated DESC";
_command.CommandType = CommandType.Text;
try
{
if (filter != null)
{
filter = filter.Replace(" ", "%");
}
sqlda.SelectCommand = _command;
sqlda.SelectCommand.Parameters.AddWithValue("@value", "%" + filter + "%");
sqlda.Fill(result);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return (DataSet)result;
}
#endregion
#region IBid Members
public string Client
{
get
{
return _Client;
}
set
{
_Client = value;
}
}
public string Contact
{
get
{
return _Contact;
}
set
{
_Contact = value;
}
}
public string Sponsor
{
get
{
return _Sponsor;
}
set
{
_Sponsor = value;
}
}
public string Priority
{
get
{
return _Priority;
}
set
{
_Priority = value;
}
}
public string BidStatus
{
get
{
return _BidStatus;
}
set
{
_BidStatus = value;
}
}
#endregion
#region Properties
public int BidID
{
get
{ return _BidID; }
set
{ _BidID = value; }
}
public string DateCreated
{
get
{
return _DateCreated;
}
set
{
_DateCreated = value;
}
}
public string CreatedBy
{
get
{
return _CreatedBy;
}
set
{
_CreatedBy = value;
}
}
#endregion
}
}
_____________________________________________
Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.
View 5 Replies
View Related
Sep 21, 2007
I have created an application that I intended to be 3-tier, but I am not sure if I did it properly. I constructed it like this: I created a DLL that contains methods that validate the passed parameters, checks the data against business rules, and issues ADO.NET methods to access the data. The ASP.NET presentation layer uses Object Data Sources to link to these methods. With this architecture I consider the ASP.NET pages to be the presentation layer, the DLL to be the business layer, and the database itself to be the data layer.
Now I am wondering if the standard practice is to have a further division? In this case, there would be a business layer DLL whose only purpose is to validate the parameters passed to it by the presentation layer, and to do business rules checking. There would also be a data layer DLL whose purpose is to accept parameters from the business layer and issue ADO.NET methods to access the database. In this scenario the database itself would be considered part of the data layer, or not considered to be a layer at all.
Either one will work, but I would like to implement the architecture that is most accepted, and allows the easiest maintenance. What is the best practice for designing a 3-tier architecture?
View 3 Replies
View Related
Oct 27, 2006
I have a 3 tier merge replication architecture that I am having some problems making changes to. We have a 3 tier architecture with many laptop instances on the bottom tier. It is exceedingly hard to coordinate having all laptop instances online simultaneously during non business hours with users present for local administration. The requirement is to change the name of the top tier without effecting the replication from the middle to bottom tiers.
I have so far renamed the top tier's instance, reconfigured publication, created a new top tier publication, and a new middle tier subscription. The top tier article has been set to delete all data in the subscriber's table when applying the snapshot. Making data changes at the middle tier filters correctly to both the top and bottom tiers. However, any changes made at the top or bottom tiers filter to the middle tier but no further. At that point, there are no errors, but simply no data needing to be merged.
Does anyone know how dropping and recreating a top tier publication could cause a 3 tiered replication architecture to behave like this? Is there any solution short of having to recreate subscriptions on all the bottom tier laptops?
View 5 Replies
View Related
Nov 28, 2006
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -
Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try
the new version is much more elegant: -
Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If
Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" ....
There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try
This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?
View 4 Replies
View Related
Mar 4, 2006
How can
ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString
be embedded in the declaration of sqldatasource?
Thanks.
View 1 Replies
View Related
Mar 25, 2008
Hi ,
We are trying to implement a 2 tier architecture for our inbuild SQL 2005 backed online application. We want the SQL Reporting Server Interface --- Reporting service website to sit on Server 'A' and the actual reporting server to sit on Server 'B'.
Can someone suggest or advise us how to proceed with this.
Server 'A' is our application server and has IIS & Application.
Server 'B' is our DB server and has IIS,SQL 2005, Reporting Server,Intergration Service.
All suggestions are welcome.
Thanks,
Namit Sethi
View 1 Replies
View Related
Nov 6, 2007
We have a 2-tier architecture with thick client (.NET 2.0) applications directly accessing the SQL Server database. We are struggling to manage lots of users while maintaining security. Granting lots of users directly to the database seems to be tough to manage. In fact, we would like to let supervisors without DBA previlege to add and remove users of our applications. Using SQL Authentication (a single account to access the database) is the other alternative but it is not considered as a secure solution.
I would appreciate if anyone gives me suggestions on how to handle this, without moving to a 3-tier architecture (dedicated middle-tier DB access layer running a custom user account).
Thanks in advance.
View 4 Replies
View Related
Mar 7, 2007
Hello Everyone
I'm looking for outside opinion on how to implement the business layer of an application. The straight forward idea I know of would be to develop a component that encapsulates all the business rules under objects. Client applications would access this component, and the business component would talk directly to the database. This was great for me when I used Access databases.
Now I have been introduced to SQL server and the stored procedures. I think stored procedures are one of the best features because these procedures can enforce rules as sort of a "last line of defence". So this is where my question comes in. Where would the best place be to implement the business layer? Through a component, such as XML services that allow other clients to access the business rules OR stored procedures.
I do understand that components/dlls provide MUCH more flexibility then stored procedures.
But, if I do take the component route:
- Other then SQL queries that get called multiple times, is there any use for stored procedures?
- For some case scenarios such as web sites that access a Business Layer, should the business layer use ONE sql server login/role to access the data or create a sql login/role for each major segment/service? E.g. one login for the accounting side, one login for the
web site services, one side for HR and the business layer will have to decide which one to use depending on the service requested.
- Would it be in the best interest of security that I allow the business components to send SQL queuries such as Insert/Delete/Update? I loved the idea of stored procedures because if for some reason, someone steals the business layer userId/password, they are only exposed to the assigned stored procedures and cannot do "Delete From Table". Unfortunately, this practice has led to an abundance of stored procedures (the list is HUGE).
If I do take the Sql stored procedure route:
- If someone steals any user Id/password, they will only be exposed to the assigned stored procedures and THOSE stored procedures contain the business rules. Thus the damage can be minimized.
- If some developer makes a mistake down the road or forgets some sort of business aspect (and is not going through the business layer), again, stored procedures would offer a last "line of defense". Lets face it, there are some applications that do not necessary require a business layer
I'd like to get everyone's opinion on how they partition the business rules with SQL server? Do you use SQL server stored procedures to only enforce formating rules (such as upper case, lower, etc) while the business rules are stored in a component?
Do you use stored procedures to enforce all business rules? None?
Thank you for your time,
Adrian
View 7 Replies
View Related
Oct 27, 2015
I want to create one bacpac file but it does fails, Need I delete all the descriptions before to begin? How odd! I don't understand why can we choose some scripting options such as we did on 'Generate Scripts' task..I get errors such as: dbo.table.field.Ms_Description is not supported when used as part of a data package
View 2 Replies
View Related
Oct 31, 2006
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
View 1 Replies
View Related
Sep 21, 2007
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View 9 Replies
View Related
Oct 25, 2007
I have a table Customer with 200000 records.
When I run query
DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID
At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.
When i run the query
SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts
Pls. tell me why? and How can i rewrite it.
View 8 Replies
View Related
Dec 7, 2006
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
"SELECT TOP 20 to 40 * FROM Northwind"
Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ
View 2 Replies
View Related
Dec 29, 2003
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
View 4 Replies
View Related
Sep 9, 2005
Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies
View Related
Dec 12, 2001
Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?
I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...
View 2 Replies
View Related
May 3, 2006
So I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.
Thanks in advance for any help.
View 3 Replies
View Related
Apr 5, 2007
Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
View 7 Replies
View Related
Nov 15, 2013
I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.
Example data:
Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh
The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:
R
L
W
View 5 Replies
View Related
Jan 7, 2004
-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!
create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'
select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
go
drop table UHS_t1
View 10 Replies
View Related
