From StudentEnrolmentFile a,
Payment_Master b,
ProgramPaymentSchemeFile c
where a.IDNumber = b.IDNumber
And a.ProgCode = b.ProgCode
And a.ProgCode = c.ProgCode
And a.SchemeDesc = c.SchemeDesc
And b.WeekNum = c.SchemeWkNum
And b.WeekPercent = c.SchemeWkPercent
To be honest, you would be better off either storing this as a view in the database or turn it into a stored procedure rather than running it in the code behind. Querying from code behind like this is no different than the query you have here. You could
do it like this (using a SQL Data Reader):
string _csMyConn = your database connection string;
string MyQuery = "Select c.SchemePayNum, c.SchemeWkNum, c.SchemeWkPercent, c.SchemeType, b.PayAmt, b.LuDateTime From StudentEnrolmentFile AS a, Payment_Master AS b, ProgramPaymentSchemeFile AS c where a.IDNumber = b.IDNumber And a.ProgCode = b.ProgCode And a.ProgCode = c.ProgCode And a.SchemeDesc = c.SchemeDesc And b.WeekNum = c.SchemeWkNum And b.WeekPercent = c.SchemeWkPercent";
SqlConnection conn = new SqlConnection(_csMyConn);
SqlCommand cmd = new SqlCommand(MyQuery, conn);
conn.Open();
SqlDataReader MyDataSet = cmd.ExecuteReader();
while (MyDataSet.Read())
{
// in here you response the values to screen using labels and set the label text to the value like this: using label1 as example
label1.Text = MyDataSet.GetValue(0).ToString();
}
Your question is not very clear. You are asking to convert SQL to SQL, what does that mean? What does C# (a programming language) have to do with it.
Regardless, SQL statements don't come a lot simpler than the one you have shown (I don't see any grouping, sub queries, calls to user defined functions, etc). What have you done so far to solve your problem? Are you stuck on something? Show us what you
have done so far and tell us why you are stuck.
The very first statement I mentioned was how to translate that SQL Alias into SQL Statement in C#. Sorry I was not so clear on this, that SQL statement was written in SQL Query in SQL Server Management Studio not in MS Visual Studio. If there's a way for
us to translate that into a Visual C# syntax that would be great.
What I would suggest is turn that SQL statement into a stored procedure and then from your code call the proc by name. Its easier and keeps your code cleaner. Anytime you run an inline query, it can start making your code behind messy and hard to read. Also,
procs being stored on the DB are usually faster in response times.
string MyQuery = "Select c.SchemePayNum, c.SchemeWkNum, c.SchemeWkPercent, c.SchemeType, b.PayAmt, b.LuDateTime From StudentEnrolmentFile AS a, Payment_Master AS b, ProgramPaymentSchemeFile AS c where a.IDNumber = b.IDNumber And a.ProgCode = b.ProgCode And
a.ProgCode = c.ProgCode And a.SchemeDesc = c.SchemeDesc And b.WeekNum = c.SchemeWkNum And b.WeekPercent = c.SchemeWkPercent";
Friend, there is no C# syntax for SQL that I know of, a query is a query no matter how you slice it. However, you can either run the query like shown above or call a stored procedure from code behind.
CREATE PROCEDURE [dbo].[SAMPLE]
AS
BEGIN
Select c.SchemePayNum,
c.SchemeWkNum,
c.SchemeWkPercent,
c.SchemeType,
b.PayAmt,
b.LuDateTime
From StudentEnrolmentFile a,
Payment_Master b,
ProgramPaymentSchemeFile c
where a.IDNumber = b.IDNumber
And a.ProgCode = b.ProgCode
And a.ProgCode = c.ProgCode
And a.SchemeDesc = c.SchemeDesc
And b.WeekNum = c.SchemeWkNum
And b.WeekPercent = c.SchemeWkPercent;
END
protected void Button1_Click(object sender, System.EventArgs e)
{
con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=will;Integrated Security=True";
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SAMPLE";
dataadpt.SelectCommand = cmd;
dataadpt.Fill(datl);
con.Close();
GridView1.DataSource = datl;
GridView1.DataBind();
}
A.Venkatesan
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
Marked as answer by bbcompent1 on Feb 23, 2012 03:13 PM
sheen_buhay
Member
711 Points
539 Posts
Sql Aliases
Feb 07, 2012 01:07 PM|LINK
Hi,
Is there a way to convert this SQL Query Aliases below to an SQL Statement in C#?
Please refer below:
Select c.SchemePayNum,
c.SchemeWkNum,
c.SchemeWkPercent,
c.SchemeType,
b.PayAmt,
b.LuDateTime
From StudentEnrolmentFile a,
Payment_Master b,
ProgramPaymentSchemeFile c
where a.IDNumber = b.IDNumber
And a.ProgCode = b.ProgCode
And a.ProgCode = c.ProgCode
And a.SchemeDesc = c.SchemeDesc
And b.WeekNum = c.SchemeWkNum
And b.WeekPercent = c.SchemeWkPercent
Please help. Thanks
SheenBuhay
bbcompent1
All-Star
33063 Points
8516 Posts
Moderator
Re: Sql Aliases
Feb 07, 2012 01:19 PM|LINK
To be honest, you would be better off either storing this as a view in the database or turn it into a stored procedure rather than running it in the code behind. Querying from code behind like this is no different than the query you have here. You could do it like this (using a SQL Data Reader):
string _csMyConn = your database connection string; string MyQuery = "Select c.SchemePayNum, c.SchemeWkNum, c.SchemeWkPercent, c.SchemeType, b.PayAmt, b.LuDateTime From StudentEnrolmentFile AS a, Payment_Master AS b, ProgramPaymentSchemeFile AS c where a.IDNumber = b.IDNumber And a.ProgCode = b.ProgCode And a.ProgCode = c.ProgCode And a.SchemeDesc = c.SchemeDesc And b.WeekNum = c.SchemeWkNum And b.WeekPercent = c.SchemeWkPercent"; SqlConnection conn = new SqlConnection(_csMyConn); SqlCommand cmd = new SqlCommand(MyQuery, conn); conn.Open(); SqlDataReader MyDataSet = cmd.ExecuteReader(); while (MyDataSet.Read()) { // in here you response the values to screen using labels and set the label text to the value like this: using label1 as example label1.Text = MyDataSet.GetValue(0).ToString(); }bbcompent1
All-Star
33063 Points
8516 Posts
Moderator
Re: Sql Aliases
Feb 07, 2012 01:20 PM|LINK
Using GetValue for each of these gives you a greater deal of control on how your data will display on the screen. Also, reader is much faster too.
Paul Linton
Star
13421 Points
2535 Posts
Re: Sql Aliases
Feb 07, 2012 10:32 PM|LINK
Your question is not very clear. You are asking to convert SQL to SQL, what does that mean? What does C# (a programming language) have to do with it.
Regardless, SQL statements don't come a lot simpler than the one you have shown (I don't see any grouping, sub queries, calls to user defined functions, etc). What have you done so far to solve your problem? Are you stuck on something? Show us what you have done so far and tell us why you are stuck.
sheen_buhay
Member
711 Points
539 Posts
Re: Sql Aliases
Feb 17, 2012 07:39 AM|LINK
@paul,
The very first statement I mentioned was how to translate that SQL Alias into SQL Statement in C#. Sorry I was not so clear on this, that SQL statement was written in SQL Query in SQL Server Management Studio not in MS Visual Studio. If there's a way for us to translate that into a Visual C# syntax that would be great.
SheenBuhay
bbcompent1
All-Star
33063 Points
8516 Posts
Moderator
Re: Sql Aliases
Feb 21, 2012 12:41 PM|LINK
What I would suggest is turn that SQL statement into a stored procedure and then from your code call the proc by name. Its easier and keeps your code cleaner. Anytime you run an inline query, it can start making your code behind messy and hard to read. Also, procs being stored on the DB are usually faster in response times.
bbcompent1
All-Star
33063 Points
8516 Posts
Moderator
Re: Sql Aliases
Feb 21, 2012 03:42 PM|LINK
Friend, there is no C# syntax for SQL that I know of, a query is a query no matter how you slice it. However, you can either run the query like shown above or call a stored procedure from code behind.
venkatmca008
Participant
1810 Points
341 Posts
Re: Sql Aliases
Feb 21, 2012 04:02 PM|LINK
hi u can create procedure and use in C#
CREATE PROCEDURE [dbo].[SAMPLE] AS BEGIN Select c.SchemePayNum, c.SchemeWkNum, c.SchemeWkPercent, c.SchemeType, b.PayAmt, b.LuDateTime From StudentEnrolmentFile a, Payment_Master b, ProgramPaymentSchemeFile c where a.IDNumber = b.IDNumber And a.ProgCode = b.ProgCode And a.ProgCode = c.ProgCode And a.SchemeDesc = c.SchemeDesc And b.WeekNum = c.SchemeWkNum And b.WeekPercent = c.SchemeWkPercent; END protected void Button1_Click(object sender, System.EventArgs e) { con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=will;Integrated Security=True"; con.Open(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "SAMPLE"; dataadpt.SelectCommand = cmd; dataadpt.Fill(datl); con.Close(); GridView1.DataSource = datl; GridView1.DataBind(); }Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
sheen_buhay
Member
711 Points
539 Posts
Re: Sql Aliases
Feb 22, 2012 12:32 AM|LINK
@venkatmca008,
I appreciate your time on this looking into my issue. I will give it a try later. Thanks.
SheenBuhay