|
I have a static database (well at least for as long as this software runs) and I wondering if any one know if it is better to load all the relevant data into a dataset first and then just work off that the rest of the program or to do many direct hits, returning two or 3 rows at a time, using DataReader?
I’m very new to databases and I’m probably missing some case info that may make a difference. If so, please let me know and I’ll try to be more specific.
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
Generally the most efficient in terms of performance is to retrieve only the data that you need.
Whether you use DataSets or DataReaders is more a matter of your programming style, or the nature of the project (small vs large). DataSets are really nice if you are not planning on using business entity objects. If you are planning on using objects, then datareaders are probably a better bet.
If you are new to data access programming, I would say that you should either use DataSets, or use an OR Mapper, such as NHibernate.
my blog
|
|
|
|
|
Hi
I'm from Germany and got a little Problem.
I'm Writing a little Windows Application in C#.
It shows a Datagrid from one Table. Over textboxes
i can add or change Records. Further I can delete
Records. Now the Problem. The Datagrid is in the
first Form. What i need is another Datagrid, in another
Form by clicking on a menuItem. By Click on the MenuItem
the Form is shown but the DataGrid is empty! How can I realize this?
I'm sorry for my fearsome English, but I have no Time;)
FormFilter(MenuItem) is called from the main Function like this:
public FormFilter frm3;
private void miFilter_Click(object sender, System.EventArgs e)
{
frm3=new FormFilter();
frm3.Show();
}
Code FormFilter:
namespace DatabaseAccessWithADONET
{
///
/// Summary description for FormFilter.
///
public class FormFilter : System.Windows.Forms.Form
{
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.Button btExit;
//DataFilterClass dfc = new DataFilterClass();
//public CurrencyManager currManager;
private DataSet ds;
private System.Windows.Forms.DataGrid dataGrid2;
private OleDbDataAdapter dbAdapter;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public FormFilter()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(FormFilter));
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.btExit = new System.Windows.Forms.Button();
this.textBox2 = new System.Windows.Forms.TextBox();
this.textBox1 = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.dataGrid2 = new System.Windows.Forms.DataGrid();
this.groupBox1.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
this.SuspendLayout();
//
// groupBox1
//
this.groupBox1.Controls.Add(this.btExit);
this.groupBox1.Controls.Add(this.textBox2);
this.groupBox1.Controls.Add(this.textBox1);
this.groupBox1.Controls.Add(this.label2);
this.groupBox1.Controls.Add(this.label1);
this.groupBox1.Location = new System.Drawing.Point(8, 24);
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(248, 184);
this.groupBox1.TabIndex = 4;
this.groupBox1.TabStop = false;
this.groupBox1.Text = "Filter";
//
// btExit
//
this.btExit.FlatStyle = System.Windows.Forms.FlatStyle.System;
this.btExit.Location = new System.Drawing.Point(136, 136);
this.btExit.Name = "btExit";
this.btExit.TabIndex = 4;
this.btExit.Text = "Exit";
this.btExit.Click += new System.EventHandler(this.btExit_Click);
//
// textBox2
//
this.textBox2.Location = new System.Drawing.Point(120, 56);
this.textBox2.Name = "textBox2";
this.textBox2.Size = new System.Drawing.Size(120, 20);
this.textBox2.TabIndex = 3;
this.textBox2.Text = "";
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(120, 24);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(120, 20);
this.textBox1.TabIndex = 2;
this.textBox1.Text = "";
//
// label2
//
this.label2.Location = new System.Drawing.Point(8, 56);
this.label2.Name = "label2";
this.label2.TabIndex = 1;
this.label2.Text = "Text";
//
// label1
//
this.label1.Location = new System.Drawing.Point(8, 24);
this.label1.Name = "label1";
this.label1.TabIndex = 0;
this.label1.Text = "ID";
//
// dataGrid2
//
this.dataGrid2.CaptionText = "FilterResults";
this.dataGrid2.DataMember = "";
this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid2.Location = new System.Drawing.Point(272, 24);
this.dataGrid2.Name = "dataGrid2";
this.dataGrid2.Size = new System.Drawing.Size(448, 256);
this.dataGrid2.TabIndex = 5;
this.dataGrid2.Click += new System.EventHandler(this.dataGrid2_Click);
//
// FormFilter
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("$this.BackgroundImage")));
this.ClientSize = new System.Drawing.Size(760, 382);
this.Controls.Add(this.dataGrid2);
this.Controls.Add(this.groupBox1);
this.Name = "FormFilter";
this.Text = "FormFilter";
this.groupBox1.ResumeLayout(false);
((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
this.ResumeLayout(false);
}
#endregion
public void FormFilter_Load(object sender, System.EventArgs e)
{
ds = new DataSet();
string stConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\Artikel_db.mdb";
OleDbConnection conn = new OleDbConnection(stConnection);
OleDbCommand dbArtikel = new OleDbCommand("select * from tbl_Artikel",conn);
dbAdapter = new OleDbDataAdapter(dbArtikel);
dbAdapter.Fill(ds, "tbl_Artikel");
dataGrid2.DataSource = ds.Tables["tbl_Artikel"].DefaultView;
}
private void btExit_Click(object sender, System.EventArgs e)
{
this.Close();
}
}
}
|
|
|
|
|
First you can implement a property or a function in your FormFilter (the 2nd form). Let's call it BindData().
BindData will be used to pass the datatable from the 1st form and to bind it to FormFilter.
public void BindData(DataTable dataTable)
{
myDataGrid.DataSource = dataTable.DefaultView;
}
[edit]You might need to pass the datatable as ref[/edit]
Then call the property/function before you show the 2nd form in miFilter
private void miFilter_Click(object sender, System.EventArgs e)
{
frm3=new FormFilter();
frm3.BindData(ds.Tables["tbl_Artikel"])
frm3.Show();
}
I haven't checked it so I'm not sure whether you can copy and paste the code, but the idea is something like that.
Cheers,
Edbert P.
Sydney, Australia.
|
|
|
|
|
I have a problem running the Sql Server as localhost. It accepts the loopback address 127.0.0.1 though, and it also accepts running with the computer name.
The problem is that the self installing starter kits from www.asp.net requires an MS Sql Server that runs under the name localhost.
Does anyone know how to solve this problem.
|
|
|
|
|
Can you run
ping localhost
and verify that it works?
Generally I'd expect specifying localhost to work, as well as 127.0.0.1
Can you try
isql -S localhost -E
or
isql -S localhost -U sa -P password
and see if they work?
At the risk of sounding patronising, can you be sure it's a connection issue, not that you haven't created any required tables, for instance...
Steve S
Developer for hire
|
|
|
|
|
Hi,everyone. i'm a newbie of VC,i'm sorry if the question bother you...
Here it is:
when i first added the Microsoft DataGrid Control, Version 6.0(OLEDB), and added a member variable to a class by Class Wizard. However, for some reason I didn't do it rightly, so I wanted to delete it. (I think i must delete it in a wrong way and left something!) I navigated to the variable in the header file, and I deleted it.
However, when I tried to reinsert the variable (once again by using Class Wizard, and choosing Add Member Variable), the former context menu didn't come out:
"The ActiveX Control "Microsoft DataGrid Control, Version 6.0(OLEDB)" has not been inserted into the project. Developer Studio will do this now and generate a C++ wrapper class for it." -- Dialog is expected.
instead, "Add Member Varible" dialog appears and the varible type datagrid1 exsits and i can't use it properly. i have searched in files i can't find string "datagrid" anymore,why does the compiler remember that 
so i think i should reinsert the component,another question comes up:
why do they have "Project->Add to project->Components and Controls"
but don't have "Project->Minus to project->Components and Controls"
i don't know how to do,and hope someone can help me,and hope i describe the problem clearly...
Best wishes for all of you! and this is really a great place! i'm so enjoying being here and learn a lot! ^.^
Thank you! and Happy Thanksgiving!
You will when you believe!
|
|
|
|
|
i'm trying to restore a db from a windows app., but i had an error stating that the DB is in use and can't be restored.
can anyone help please ...
*********
CODE
********
public static void RestoreDB(string path)
{
string commandString="USE MASTER RESTORE DATABASE Guirguis FROM DISK = '"+path+"'"; //Guirguis : DB to restore
SqlConnection newConn=new SqlConnection();
newConn.ConnectionString="workstation id="+System.Environment.MachineName+";packet size=4096;integrated security=SSPI;data s" +"ource=\".\";persist security info=False;initial catalog=master";
command=new SqlCommand(commandString,newConn);
dbConnection.Close(); //connection may be using Guirguis
newConn.Open();
try
{
command.ExecuteNonQuery();
}
catch(Exception e)
{
newConn.Close();
throw new Exception(e.Message);
}
newConn.Close();
newConn.Dispose();
}
*******************
thanx
|
|
|
|
|
to restore a database you have to amke sure that there is no user currently logged into the database. Atleast in SQL Server you do.
So go into enterprise manage and check if ther are any users logged into before attempting a restore.
Kick the users out and then attempt the restore.
If it works then your code is fine , you jsut have to make sure that no user is using the db before attempting the restore
|
|
|
|
|
Hi,
I'm using the ADO.NET with C#. When I perform an SQL query on a table in which the primary key consists of multiple fields and the SQL query is done on fewer fields than the primary key I get an exception.
("No value given for one or more required parameters")
I get why this may not be allowed, but I could to this in the old ADO.
(let's say I want all the x's of y, then y & x consist of the primary key, but I still need to create a query that returns all the x's of a certain y)
Does anybody know if they changed that? or do I need to do something else.
Thanks!
|
|
|
|
|
Since you don't post your query or the C# code you are using to access it diagnosing the problem may be a little challenging.
sharonz wrote:
("No value given for one or more required parameters")
This indicates that there is a problem with the parameters you pass to the query rather than the query itself.
e.g.
SqlCommand cmd = new SqlCommand("MyStoredProc", connection);
cmd.Parameters.Add("@MyFirstParameter", someValue);
cmd.ExecuteNonQuery();with the corresponding SQL
CREATE PROCEDURE dbo.MyStoredProc
@MyFirstParameter int,
@MySecondParameter int
AS
...
GO This would most likely give the same error message because you have missed one of the parameters.
If this does not give you the idea for the answer, please post the relevant code snippets.
Do you want to know more?
WDevs.com - The worlds first Developers Services Provider
|
|
|
|
|
Hi gurus,
I have created a blank Access database (mdb) and I would like to know how I can add a table, fields (columns) and records (rows) in C#.
Can anyone show me a sample code to do these operations please?
Best regards
There is no spoon.
|
|
|
|
|
I don't know Access specific syntax... But I can give some generic advice.
The OleDbCommand can accept just about any SQL string that you could give directly to the database. Therefore you just create your SQL and pass it to the command object then do command.ExecuteNonQuery() [as you don't require any results back] and it should work.
Do you want to know more?
WDevs.com - The worlds first Developers Services Provider
|
|
|
|
|
Anybody knows any good free data transformation tool and OLAP client tool? Thanks a lot.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Grid only views Date part of a datetime objects(Fields) ...is there any mean to view the time part only or even both date and time.
thanx
Mr.Cooper
|
|
|
|
|
This probably belongs in the ASP.NET forum, but I'll take a stab at it anyway. I think you need to set the DataFormatString property of the appropriate BoundColumn object. Try, for example, setting it to {0:t} or {0:g}.
For more date/time formatting options, see Standard DateTime Format Strings[^] and Custom DateTime Format Strings[^] in the .NET Framework documentation.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Using the following code the first insert is ignored so only the 'y' and 'z' rows appear in the table. The table is empty before any of the inserts. Anybody know why this is happening? Is there an easier way of getting data into a table?
OleDbConnection cn = new OleDbConnection();<br />
cn.ConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;" +<br />
@"Data Source=" + sDBPath + ";" +<br />
@"Jet OLEDB:Engine Type=5";<br />
<br />
cn.Open();<br />
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM Info",cn);<br />
OleDbCommand cmdInsert = new OleDbCommand();<br />
cmdInsert.Connection = cn;<br />
<br />
cmdInsert.CommandText = @"INSERT INTO Info (InfoDesc, InfoVal) VALUES (?, ?)";<br />
cmdInsert.Parameters.Add(new OleDbParameter("InfoDesc",OleDbType.VarWChar,50,"InfoDesc"));<br />
cmdInsert.Parameters.Add(new OleDbParameter("InfoVal",OleDbType.VarWChar,50,"InfoVal"));<br />
adap.InsertCommand = cmdInsert;<br />
<br />
DataSet ds = new DataSet();<br />
adap.Fill(ds,"Info");<br />
<br />
DataRow dr = null;<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "x";<br />
dr["InfoVal"] = "x";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "y";<br />
dr["InfoVal"] = "y";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "z";<br />
dr["InfoVal"] = "z";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
adap.Update(ds,"Info");<br />
ds.Dispose();<br />
cn.Close();>
Rugby League: The Greatest Game Of All.
|
|
|
|
|
I have a query which inserts data from one database to another. The two tables are identical. The problem is that when viewed through enterprise manager, you can see all the records(200 of them). However, when doing a select * in query analyser, it shows the results as 0. Has anyone ever come across this? If so, please let me know whats happening here. If you restart sql, you can then see the records in query analyser.
|
|
|
|
|
Sometimes in SQL 2000 you can end up with two tables that are named the same, but have different owners. So when you view in Enterprise Manager, you may be viewing the table owned by dbo, but when logged into query analyzer, you may be viewing the table owned by joeblow.
my blog
|
|
|
|
|
Hello,
I'm trying to write a stored procedure that retrieves the info for a quote, and number of products in that quote using COUNT. But if there's a quote that doesn't have any products yet (you create a quote with customer information and then you start adding products), no record is returned.
This is my code:
ALTER PROCEDURE dbo.Orders_ListaCotizaciones
(@NombreUsuario varchar(20))
AS SELECT dbo.Cotizaciones.ID_pedido, dbo.Cotizaciones.Nombre,
dbo.Cotizaciones.Ciudad + ', ' + dbo.Cotizaciones.Estado AS CiudadEstado,
COUNT(dbo.Cotizacion_Shutters.ID_shutter) AS NumShutters
FROM dbo.Cotizacion_Shutters LEFT OUTER JOIN
dbo.Cotizaciones ON dbo.Cotizacion_Shutters.ID_pedido = dbo.Cotizaciones.ID_pedido
INNER JOIN dbo.Empresas ON dbo.Cotizaciones.ID_empresa = dbo.Empresas.ID_empresa
INNER JOIN dbo.Usuarios ON dbo.Empresas.ID_empresa = dbo.Usuarios.ID_empresa
WHERE (dbo.Usuarios.NombreUsuario = @NombreUsuario)
GROUP BY dbo.Cotizaciones.ID_pedido, dbo.Cotizaciones.Nombre,
dbo.Cotizaciones.Ciudad + ', ' + dbo.Cotizaciones.EstadoThe Cotizaciones table may have a record with no associated records int Cotizacion_Shutters, and I want the stored procedure to return 0 for that quote. (The rest of the JOINs are to get the distributor ID from the logged on user name)
Just in case you wanted to know, I'm designing my stored procedure in Access (but it allows me to modify the SQL.)
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
You have your OUTER JOIN the wrong way round. A LEFT outer join gives you all the rows in the left hand table joined with any matching rows in the right-hand table. If there are no rows on the right, the row will contain NULL for all columns in the right-hand table.
Either swap dbo.Cotizaciones and dbo.Cotizacion_Shutters around, or use a RIGHT OUTER JOIN. I don't think there's any performance difference between LEFT and RIGHT joins, the query optimiser typically does an equally good job whichever way you express your query.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you very much for your help!! It works now!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
How to represent duration (in hours:minutes:sec) in SQL knowing that there is no data type to hold time objects ... only datetime
thanx in advance
Mr.Cooper
|
|
|
|
|
Use a datetime and ignore the date. If not, you could store a time in seconds and convert it, or whatever else you like.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
hi all i'm using VB.Net and i have a form that i need to have the results displayed into labels that populate the form.
this is what i have so far
**************** CODE *****************
Private Sub SearchTBBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchTBBtn.Click
Dim Cnxn As New ADODB.Connection
Dim cmdChange As ADODB.Command
'Connection String
Dim strCnxn As String
'SQL Query
Dim strSQL As String
'search criteria
Dim strSearchLName, strSearchFName As String
Dim Now As DateTime
Dim UserPassID As Integer
UserPassID = Val(lblPassID.Text)
'Now = DateTime()
' Now = Format(Now, "h:nnAM/PM")
strSearchLName = txtLName.Text
strSearchFName = txtFName.Text
' define strSQLUpdate to insert the fields
strSQL = "SELECT * FROM Contacts WHERE (LName) = '" & strSearchLName & "' AND (FName) = '" & strSearchFName & "'"
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RMSC LOGIN\MYDataBase.mdb;Persist Security Info=False"
Cnxn = New ADODB.Connection
Cnxn.Open(strCnxn)
cmdChange = New ADODB.Command ' Create command object
cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = strSQL
cmdChange.Execute() ' execute the SQL command
' Fill the fields with the results
'************* This is where i have a problem
Dim rsContact As New ADODB.Recordset
rsContact = cmdChange
lblPassID.Refresh()
lblLName.Refresh()
Cnxn.Close() ' Close everything
Cnxn = Nothing
End Sub
*************** END CODE ******************
i guess i don't knowhow to populate the labels with the results of cmdChange
any help would be great
thank you
Help is great only if yu ask correctly
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
