|
Thanks Griff.
That's one reason for learning C# rather than VB.Net then C#.
VB.Net seems less popular these days going buy the list of the top 10 popular programming languages.
I hope to learn ways of reducing code that C# seems good at doing.
Brian
|
|
|
|
|
You're welcome!
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
That book must be not available as I get a site not found message when I click on your link.
|
|
|
|
|
Just go to your country's Amazon website and search there.
|
|
|
|
|
|
Good to see something freely available that's educational Gerry.
While to covers the basics of C# for beginners I'm hoping to get a more detailed book that covers a lot of things when learning C#.
It will still be useful to read thanks.
Brian
|
|
|
|
|
How should I start to code in making a game using a c# language?
|
|
|
|
|
The same way you get started with any project:
1) Overview design: planning
2) Specification: convert the overview into a requirements document.
3) Design: convert the spec into classes, data, layers, models, and / or patterns.
4) Development: convert the design into code.
5) Testing: Often done at the same time as development, can be designed before dev begins (TDD).
6) Deployment: release to users.
7) Maintenance: Fixing bugs as they come up, adding features.
Any one of those stages can cause you to go back to any previous stage!
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
Greetings experts
First off, I know how to execute a stored procedure using either C# or VB.
However, this one is a bit challenging because it has dynamic pivot query inside the stored proc.
Here is the stored procedure:
TER PROCEDURE [dbo].[uspGetPivotedData]
AS
drop table dbo.MemberHistory;
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX),@colsFormated AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
FROM Transactions t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = ';WITH CTE AS(SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
t.Balance,
YEAR(eventyear) eventyear,
t.Amount
from dbo.Transactions AS t INNER JOIN dbo.Members AS m ON t .MemberID = m.MemberID INNER JOIN
dbo.PaymentTypes AS p ON t .TypeID = p.PaymentTypeID)
SELECT * INTO #TABLE FROM CTE
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
registrationFee,
Balance, ' + @cols + ' INTO dbo.MemberHistory from #TABLE
pivot
(
MAX(Amount)
for eventyear in (' + @cols + ')
) p ORDER BY TransactionID'
EXECUTE sp_executesql @query
So far, I am using this code to query table called MemberHistory but I would prefer to use the SP instead.
private void Form2_Load(object sender, System.EventArgs e)
{
dataGridView2.DataSource = bindingSource1;
GetData("select * from MemberHistory");
}
private void reloadButton_Click(object sender, System.EventArgs e)
{
GetData(dataAdapter.SelectCommand.CommandText);
}
private void submitButton_Click(object sender, System.EventArgs e)
{
dataAdapter.Update((DataTable)bindingSource1.DataSource);
}
private void GetData(string selectCommand)
{
try
{
String connectionString = ConfigurationManager.ConnectionStrings["Members"].ConnectionString;
dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
dataGridView2.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlException)
{
MessageBox.Show("There is an issue with connection string.");
}
}
Any idea how to replace the embedded sql into the stored procedure shown above?
Sorry for long code.
modified 18-Mar-19 17:41pm.
|
|
|
|
|
The stored procedure loads "MemberHistory"; then your "embedded SQL" selects from MemberHistory (which was loaded by the SP).
One doesn't "replace" the other; one feeds the other. You run the SP; then you run your query.
If you're talking about "combining the two", what's the point?
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
I agree with you but what is different here is that that SP does not run unless you execute it in SSMS using the following:
E [myDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspGetPivotedData]
SELECT 'Return Value' = @return_value
GO
Unlike regular SP, once you execute it, it automatically adds data to the table in this case MemberHistory but with is SP, as stated, you have to always execute it for data to be stored in MemberHistory.
|
|
|
|
|
The SP is only required to run when the "source data" changed; any other time is pointless.
And there is no such thing as a "regular" SP. The fact that this SP loads a table does not make it "non-regular".
I see no reason why you think this SP can only run in "SSMS". (You already stated you know how).
That "wrapper" to handle a "return value" in SSMS is not required for a call from C# or VB:
sql server - How to get return value from stored procedure in Windows form C# - Stack Overflow
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
You're dropping and re-creating the MemberHistory table every time you execute the stored procedure. That is not a good idea.
Firstly, this will require you to connect to your database using an account which has permission to modify the structure of your database. Unless you're writing a database management tool, that's giving the user too much control. You should be connecting as an account which has only the specific permissions required by your application.
Secondly, consider what will happen if two users try to execute your stored procedure at the same time. If the first user tries to select from the table whilst the second user is part-way though executing the stored procedure, they'll get an error telling them that the table doesn't exist.
Change your stored procedure so that it returns the data directly.
ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
FROM Transactions t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
set @query = N'WITH CTE AS
(
SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
t.Balance,
YEAR(eventyear) eventyear,
t.Amount
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
)
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
registrationFee,
Balance,
' + @cols + N'
FROM
CTE
PIVOT
(
MAX(Amount)
FOR eventyear IN (' + @cols + N')
) p
ORDER BY
TransactionID';
EXECUTE sp_executesql @query;
Change your C# code to load the data from the stored procedure.
NB: Although it's not needed in your example, your GetData method will eventually force you to write code which is vulnerable to SQL Injection[^]. You need to modify it so that you can pass parameters to the command properly.
private DataTable GetData(string selectCommand, CommandType commandType, params SqlParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Members"].ConnectionString))
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = commandType;
foreach (ICloneable p in parameters)
{
command.Parameters.Add((SqlParameter)p.Clone());
}
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable result = new DataTable();
da.Fill(result);
return result;
}
}
private void LoadPivotedData()
{
try
{
DataTable table = GetData("dbo.uspGetPivotedData", CommandType.StoredProcedure);
bindingSource1.DataSource = table;
dataGridView2.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlException)
{
MessageBox.Show("There is an issue with connection string.");
}
}
private void Form2_Load(object sender, System.EventArgs e)
{
dataGridView2.DataSource = bindingSource1;
LoadPivotedData();
}
private void reloadButton_Click(object sender, System.EventArgs e)
{
LoadPivotedData();
}
NB 2: Since you're no longer storing the results of your PIVOT query in a table, you won't be able to update them. But with your current code, it doesn't make sense to update the results anyway, since they'll be thrown away as soon as someone else executes your stored procedure.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Greetings Richard,
Thank you so much. Man you make coding look so simple!
As for updating them, all updates are done via my C# and they can only update Members and Transactions tables. Any new records added or updated are stored on those tables
Once the updates are made, they will be picked up by the PIVOT query.
That's the thinking at least.
I am having just one error right now:
Error CS0103 The name 'commandText' does not exist in the current context
which is on this line:
using (SqlCommand command = new SqlCommand(commandText, connection))
At least you clearly understand what I am doing to do
UPDATE: I replaced commandText with selectCommand and it started working.
You are simply awesome!!
Exactly the solution I was looking for.
Thank you very much.
modified 18-Mar-19 17:40pm.
|
|
|
|
|
Hi,
Once we open the program, how to show up msg box and close program if there is no internet connection?
(Off course program should open if internet is on.)
thanks.
|
|
|
|
|
Hi,
I have used two different ways in the past:
1. use P/Invoke to call a native Windows function with this definitions and prototype
[Flags]
enum LP_InternetConnectionState : int {
Configured=0x40,
LAN=0x02,
Modem=0x01,
OffLine=0x20,
Proxy=0x04,
RAS_installed=0x10
}
[DllImport("wininet.dll")]
static extern bool InternetGetConnectedStateEx(ref LP_InternetConnectionState lpdwFlags,
StringBuilder name, int nameLength, int dwReserved);
2. use the Ping class to check some site can be reached, say google.com; the relevant code looks somewhat like this (there are variants for async operation, and overloads that allow for PingOptions including time-out):
Ping ping=new Ping();
byte[] bytes=new byte[32];
IPAddress address=GetIpFromHost(host);
PingReply reply = ping.Send(address, timeout, bytes);
ipStatus=(int)reply.Status;
The advantage of 2 over 1 is it really uses internet; the disadvantage here is it takes a while when the target site can't be reached.
You may want to use Google to find out more about the functions/methods/classes mentioned.
modified 17-Mar-19 21:07pm.
|
|
|
|
|
I'm currently trying to follow some code to learn about C#
In this command
students.Add(newStudent)
does the students get added to newStudent or does newStudent get added to students?
Brian
|
|
|
|
|
The instance pointed to by newStudent is being added to the students collection.
|
|
|
|
|
|
What I'd like to do later on is to write the collected students to a file and be able to read from that file. Can this be done as writing and reading a text file or is a special type of writing code needed?.
students.Add(newStudent);
....
StreamWriter file = new StreamWriter("d:/Students.txt");
file.WriteLine(students);
Brian
|
|
|
|
|
Yeah, that's not going to work. It's going to take what you define as "a special type of writing code", called "serialization".
It can be done with a BinarySerializer, XmlSerializer, Json, ... It can be retrieved and reconstituted back into the list by deserializing the file you wrote with the serializer.
Lesson 10 - Serialization and deserialization in C# .NET[^]
|
|
|
|
|
Thanks for the info Dave.
I had a feeling that you need more than just a text file reader/writer for this type of data.
Brian
|
|
|
|
|
Hi My name is Teame.
I want to develop a cafeteria management system using C#. But, I want to compose the consumption and to reduce from master inputs balance. For example, when I want to make an output called pizza, I have to use a potato, tomato, oil, green paper, and so on inputs. Then the inputs I have used should be reduced from the master inputs balance and will made as an output product called Pizza. So, please, help me to how to reduce the input consumption automatically when the waiter orders a Pizza using C#. I would like to say thank you for your support.
|
|
|
|
|