|
It is a little slow going, isn't it?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
No, the connection and it's string use a different idea of "dataSource" - a DGV (and all other data aware controls) have a DataSource Property: DataGridView.DataSource Property (System.Windows.Forms) | Microsoft Learn[^]
Your previous post says you are using that to "get the data into the DGV".
I assume that you are doing something like:
MyDataAdapter.Fill(MyDataTable);
MyMasterDGV.DataSource = myDataTable;
MyDetailDGV.DataSource = myDataTable; Is that correct?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
yes exatly i did this
private string connectionString = "Data Source=DESKTOP-5GRDQ7C\\SQLEXPRESS;Initial Catalog=Orders;Integrated Security=True";
private SqlDataAdapter masterDataAdapter;
private SqlDataAdapter detailDataAdapter;
private DataSet dataSet;
public Form1()
{
InitializeComponent();
}
private void LoadMasterData()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = "SELECT * FROM ordm";
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
masterDataGridView.DataSource = dataTable;
}
}
private void LoadData()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string ordmQuery = "SELECT * FROM ORDM";
masterDataAdapter = new SqlDataAdapter(ordmQuery, connection);
masterDataAdapter.Fill(dataSet, "ORDM");
string ordcQuery = "SELECT * FROM ORDC";
detailDataAdapter = new SqlDataAdapter(ordcQuery, connection);
detailDataAdapter.Fill(dataSet, "ORDC");
}
DataColumn parentColumn = dataSet.Tables["ORDM"].Columns["ORD_NO"];
DataColumn childColumn = dataSet.Tables["ORDC"].Columns["ORD_NO"];
DataRelation relation = new DataRelation("ORDM_ORDC_Relation", parentColumn, childColumn);
dataSet.Relations.Add(relation);
}
private void Form1_Load(object sender, EventArgs e)
{
LoadMasterData();
dataSet = new DataSet();
LoadData();
masterDataGridView.DataSource = dataSet.Tables["ORDM"];
masterDataGridView.SelectionChanged += masterDataGridView_SelectionChanged_1;
}
private void masterDataGridView_SelectionChanged_1(object sender, EventArgs e)
{
if (masterDataGridView.SelectedRows.Count > 0)
{
string orderNumber = Convert.ToString(masterDataGridView.SelectedRows[0].Cells["ordcol"].Value);
LoadChildData(orderNumber);
}
}
private void LoadChildData(string orderNumber)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = $"SELECT * FROM ordc WHERE ord_no = ord_no";
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
childDataGridView.DataSource = dataTable;
}
}
}
modified 27-Jul-23 5:38am.
|
|
|
|
|
I've edited your question and added a code block - this engages the syntax highlighter, preserves the indentation, and makes it easier to read - edit it yourself and you'll see what I did (it's only a couple of clicks to do it and it makes everybody's life easier).
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
OK, that's very definitely the long way round - you don't need the events as if you hook up the DataTables to the DGV correctly using BindingSources, the system will do it all for you.
Do you want me to show you how I would do it, or do you want to fix this line of code?
string query = $"SELECT * FROM ordc WHERE ord_no = ord_no";
If it's the later, then there are two ways to do it - the incredibly dangerous way you are trying to do it, and the proper way which means users can't destroy your database?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
the data from the 1st row is getting saved to the database but the 2nd row is created dainamically so the data in the newly added dainamic rows data is not saving to the database, insted it giving this error
System.Exception: 'Violation of PRIMARY KEY constraint 'PK_Timesheet'. Cannot insert duplicate key in object 'dbo.Timesheet'. The duplicate key value is (2).
The statement has been terminated.
code:
protected void SaveButton_Click(object sender, EventArgs e)
{
string EmpId = Session["EmpId"].ToString();
string StrSQL = "";
clsDB obj = new clsDB();
StrSQL = "SELECT MAX(TaskID) FROM Timesheet";
object maxTaskID = obj.GetScalar(StrSQL);
int TaskID = maxTaskID != DBNull.Value ? Convert.ToInt32(maxTaskID) + 1 : 1;
StrSQL = "INSERT INTO Timesheet ";
StrSQL += "VALUES('" + EmpId + "','" + TaskID + "','" + txttask.Text + "','" + txttaskdate.Text + "', ";
StrSQL += "'" + txtDescription.Text + "', '" + txttimetaken.Text + "')";
Result = Convert.ToInt32(obj.ExecuteNonQuery(StrSQL));
if (Result == 1)
{
string script = "alert('Data saved Successfully')";
ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
}
string message = "";
int currentTaskID = TaskID;
StrSQL = "INSERT INTO Timesheet ("+" EmployeeId, TaskId, TaskDate, TaskName, Description, TimeTaken) VALUES ";
StrSQL += "('" + EmpId + "','" + currentTaskID + "','";
foreach (TextBox textBox in pnlTextBoxes.Controls.OfType<TextBox>())
{
StrSQL += textBox.Text + "','";
}
StrSQL = StrSQL.Substring(0, StrSQL.Length - 2) + ");";
Result = Convert.ToInt32(obj.ExecuteNonQuery(StrSQL));
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('" + message + "');", true);
}
}
|
|
|
|
|
|
Read the error message, it tells you exactly what you are doing wrong.
|
|
|
|
|
Primary Key ("PK") columns must be unique in a table; they cannot have the same column value in any other row in the same table.
What I'd suggest ios that you either user UNIQUEIDENTIFIER values for your ID's and pass a new Guid to the DB from your C# code, or use an IDENTITY column in the DB, and SELECT it back to your C# code immediately after the INSERT using the SCOPE_IDENTITY function.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
protected void Page_PreInit(object sender, EventArgs e)
{
List<string> keys = Request.Form.AllKeys.Where(key => key.Contains("ltIndex")).ToList();
int i = 1;
int j = keys.Count;
j = j / 4;
for (int k = 0; k < j; k++)
{
this.CreateTextBox("ltIndex" + i);
i++;
}
protected void btnAddRow_Click(object sender, EventArgs e)
{
int index = pnlTextBoxes.Controls.OfType<textbox>().ToList().Count + 1;
this.CreateTextBox("ltIndex" + index);
}
private void CreateTextBox(string i)
{
Literal ltIndex = new Literal();
ltIndex.Text = 1 + ".";
pnlTextBoxes.Controls.Add(ltIndex);
Literal lt = new Literal();
lt.Text = "<br>";
pnlTextBoxes.Controls.Add(lt);
}
its provoiding index value as 1 for all the newly added row,its not providing autoincrementing of indexing
|
|
|
|
|
Look atg the code for CreateTextBox :
private void CreateTextBox(string i)
{
Literal ltIndex = new Literal();
ltIndex.Text = 1 + ".";
pnlTextBoxes.Controls.Add(ltIndex);
Literal lt = new Literal();
lt.Text = "<br>";
pnlTextBoxes.Controls.Add(lt);
}
|
|
|
|
|
hi every body.I am coding with c# .net core and my first app is a multilayer database project.
this app get datat from user input control and add/del/edit on it.
I have made 3 project in my solution:
1 : Datalayer 2:Business Layer 3:View
in my forms I should Join three or four or more table with each other and show in a datagridview to the user.so I have coded all sql query in datalayer with ado.net bcoz it is easy to join table instead of EF.
I put my code here and you please see it and notice me about each layers.(I think in BLL layer I just didn't do anything special)
and In the UI I put all of my code that I think it is not very interesting.
so see it :
DAL :
public static DataTable GetAll()
{
using DataTable dt = new();
try
{
string query = "
select
l.LineID,
s.SheetID,
z.ZoneID,
j.JointTypeID,
j.JointClassID ,
j.PipeClassID ,
j.HoldID,
l.[LineNo] ,
s.Sheet,
s.Rev ,
s.RevIndex ,
z.Unit,
z.Area,
s.SheetStatus as LineStatus,
j.JointID,
j.JointNo ,
j.JointIndex ,
j.JointStatus ,
cast(CAST(j.JointSize as decimal(18,2)) as float) as JointSize,
j.[Location],
j.Spool,
jt.JointType,
jc.JointClassName as Class,
pc.PipeClass,
m.MaterialName as Mat,
j.Thk
from
Joint as j
inner join Sheet as s on s.SheetID = j.SheetID and s.VoidDate is null
inner join Line as l on l.LineID=s.LineID
left join Zone as z on z.ZoneID = j.ZoneID
left join JointType as jt on jt.JointTypeID = j.JointTypeID
left join JointClass as jc on jc.JointClassID = j.JointClassID
left join PipingClass as pc on pc.PipeClassID = j.PipeClassID
left join Material as m on m.MaterialID = pc.MaterialID
left join MTO as m1 on m1.SheetID = j.SheetID AND m1.PTNo = Mat1
left join MTO as m2 on m2.SheetID = j.SheetID AND m2.PTNo = Mat2
";
using SqlCommand cmd = new SqlCommand(query, Connection.Conn);
Connection.Open();
using SqlDataReader rd = cmd.ExecuteReader();
dt.Load(rd);
}
catch (SqlException ex)
{
Error = ex.Message;
}
finally
{
Connection.Close();
}
return dt;
}
BLL:
public static DataTable GetAll()
{
return JointDAL.GetAll();
}
UI:
DG_Joint.DataSource = JointBLL.GetAll();
I put code in dgv contentclick() for show selected rows into textbox above it.
(There is another way like bindingsource for do it but it use only that table but my query joined many tables and it doesn't work.
I don't know if I wrote it explicitly or not, but what I mean by creating this topic is to review the multi-layer program, what codes should be written in each layer.
|
|
|
|
|
There are quite a few problems with the code you've shown.
Firstly, your code won't compile. You can't have line-breaks in a standard string. You would need to use either a verbatim string[^] or a raw string[^] for your query.
You should make the query a local const so that you're not tempted to try to inject parameter values into it incorrectly and introduce a SQL Injection[^] vulnerability into your code.
You seem to be using a shared database connection instance. That's a terrible idea - either your code must be restricted to only service one request at a time, or you'll end up with cross-contamination when multiple users try to access your application, since the connection is not thread-safe. Instead, create the connection when you need it, and wrap it in a using block to ensure it's disposed of properly.
Similarly, the SqlCommand and SqlDataReader instances need to be wrapped in using blocks.
Your code currently swallows any exceptions, and returns an empty DataTable instead. The caller is expected to examine the Error property to determine whether an error occurred, and retrieve a tiny portion of the details of that error - assuming the property hasn't been overwritten by a call from a different user in the meantime. Instead, you should let the exception propagate to the caller naturally. If you need to add more details, then throw a different exception, making sure to include the original exception as the InnerException .
public static DataTable GetAll()
{
const string Query = """
SELECT
...
FROM
...
""";
try
{
using (var connection = Connection.CreateAndOpenConnection())
using (var command = new SqlCommand(connection, Query))
using (var reader = command.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
return dt;
}
}
catch (SqlException ex)
{
throw new YourCustomException("There was an error retrieving the list of all things. See the inner exception for details.", ex);
}
}
Once you've dealt with those issues, then you can start working out what you need to put in the other layers. For example, having a BLL which simply passes all calls through to the DAL and returns the results unchanged doesn't make a lot of sense.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have all my data stored in DataTables, when I run my program using multi-threading I get an error message. I don't remember the exact wording but, it said index out of bounds of the array. However it does not appear to be out of bounds. When I run the program without using the multi-threading everything is fine.
Also, most of my methods are static, I lock the datatables when loading data.
I can't find where the problem exist, because where I believe the program line failed it does not show it as being out of bounds.
Any help would be greatly appreciated, thanks in advance.
Michael
|
|
|
|
|
If you're deleting in one thread while retrieving in another, you can get an out of bounds if you don't protect against it.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Without seeing the code it's impossible for anyone to tell you what's going on.
|
|
|
|
|
My program inserts thousands of records into a SQLite database. That works fine.
I do it by building an INSERT statement with 1000 VALUE groups, each group representing a different record to add. If I were to execute a separate INSERT statement for each record, then the insert would take hours, so I make the compound INSERT statement.
So the question is, how can I transform the INSERT query into a parameterized query, if every VALUE group has unique values?
There are four fields that need to be populated for each record, so that means I must create a SQLiteCommand with 4000 parameters?
Or is there a better way?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I don't think you have much of a choice. Sqlite doesn't support any bulk insert so you're either going to reuse the same command over and over again or build a block of inserts.
You also have a command length limit of 1GB but, unless you go nuts building a huge block of insert statements, you'll not likely hit that.
The other problem is a limit on the number of parameters you can have. The default using numbered named parameters is 32766 for recent versions of Sqlite.
Quote: Maximum Number Of Host Parameters In A Single SQL Statement
A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".
Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.
SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.
The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.
|
|
|
|
|
Thanks, just wanted to be sure I wasn't overlooking something.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
SQLite isn't open to SQL Injection, because it doesn't support multiple commands in the same command*: it doesn't support bulk inserts, even via a DataAdapter. it is meant to be a "lite" version, after all!
So if you build your command with multiple VALUE fields, it is technically safe (but needs to be commented in case someone later converts to MSSql / MySql)
I'd recommend using a transaction though, just to speed the operation up if nothing else...
This may help: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite[^]
* Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
OriginalGriff wrote: * Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection. Uh oh, that's what I'm using. I'm setting the CommandText property of a SQLiteCommand to the long INSERT statement.
I wasn't aware that there's any different way. Is there?
EDIT: I misread your post. What is the other way of using the CommandText property instead of a SQLiteCommand? (Just curious)
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No, the CommandText is a property of the SqLiteCommand object: SqliteCommand.CommandText Property (Microsoft.Data.Sqlite) | Microsoft Learn[^]
SqLite is just that: a "lite" version of SQL - it isn't meant for big jobs.
I'd be tempted to say that you might be better off using SQL Server if you are working with large databases - but that's your decision and I don't know enough about your system(s) to reccomend it.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Anyway, I appreciate you pointing out that it's not susceptible to injection attacks. That was the reason for my question.
Thanks!
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Did you actually time the separate inserts? "Thousands" isn't very much and the server is "local". The usual consideration is don't do "mass inserts" with an index ... drop and build the index afterwards.
I've experience seconds, but never "hours".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
I might have used hyperbole, but the database will contain upwards of 4 million rows.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|