Hi all
In VFP we can do this
lnConnection = SQLSTRINGCONNECT(cConnectString) SQLEXEC(lnConnection,"Select * From cname") && puts results into SQLRESULT
I am wondering what is the nearest equivalent in .NET This is what I have come up with, but is there a better way?
SqlConnection sqlConnection1 = new SqlConnection(cConnectString); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT * FROM cname"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader();
Also how would I approach UPDATE and DELETE etc?
Many thanks
Paul Newton
PAUL, Why not use datasets/tableadapters? They have all the CRUD logic built into them.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Paul Newton Sent: 10 February 2016 13:20 To: profox@leafe.com Subject: .NET equivalent of VFP SQL Pass-Through (i.e. without using EF or LINQ)
Hi all
In VFP we can do this
lnConnection = SQLSTRINGCONNECT(cConnectString) SQLEXEC(lnConnection,"Select * From cname") && puts results into SQLRESULT
I am wondering what is the nearest equivalent in .NET This is what I have come up with, but is there a better way?
SqlConnection sqlConnection1 = new SqlConnection(cConnectString); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT * FROM cname"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader();
Also how would I approach UPDATE and DELETE etc?
Many thanks
Paul Newton
[excessive quoting removed by server]
I'll look into them - thanks Dave
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier Sent: 10 February 2016 13:24 To: profoxtech@leafe.com Subject: RE: .NET equivalent of VFP SQL Pass-Through (i.e. without using EF or LINQ)
PAUL, Why not use datasets/tableadapters? They have all the CRUD logic built into them.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Paul Newton Sent: 10 February 2016 13:20 To: profox@leafe.com Subject: .NET equivalent of VFP SQL Pass-Through (i.e. without using EF or LINQ)
Hi all
In VFP we can do this
lnConnection = SQLSTRINGCONNECT(cConnectString) SQLEXEC(lnConnection,"Select * From cname") && puts results into SQLRESULT
I am wondering what is the nearest equivalent in .NET This is what I have come up with, but is there a better way?
SqlConnection sqlConnection1 = new SqlConnection(cConnectString); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT * FROM cname"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader();
Also how would I approach UPDATE and DELETE etc?
Many thanks
Paul Newton
[excessive quoting removed by server]
On Wed, Feb 10, 2016 at 7:20 AM, Paul Newton Paul.Newton@pegasus.co.uk wrote:
Hi all
In VFP we can do this
lnConnection = SQLSTRINGCONNECT(cConnectString) SQLEXEC(lnConnection,"Select * From cname") && puts results into SQLRESULT
I am wondering what is the nearest equivalent in .NET This is what I have come up with, but is there a better way?
SqlConnection sqlConnection1 = new SqlConnection(cConnectString); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT * FROM cname"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader();
Also how would I approach UPDATE and DELETE etc?
In .NET I would do Entity Framework. Below is how to stick EF into MVC and easily combine the two. If you are just starting with .NET this is one of the prefered ways of doing things today.
http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-usin...
Just EF here http://www.entityframeworktutorial.net/
An ORM framework may be overkill for simpler requirements. There are also 'lighter' ORM frameworks than EF, although EF is tightly integrated into Visual Studio. If you just wanted to update a table (Visual FoxPro in this case but the same applies to any back end) you could do something like below. Note use of parameters. Names changed to protect the innocent.
class CustomerDocketWriter : AccountsSoftwareWriterAbstract { public Boolean DoProcessDocket(CustomerDocket updateDocket, SeqcoCompany thisCompany, DateTime stampDate) { UpdateOK = true; AccountsSoftwareConnection.ConnectionString = thisCompany.DataConnectionString; CommandString = @"update swimsinv where id=? set uploaded=.t., upwhen = ?"; AccountsSoftwareCommand.CommandText = CommandString; AccountsSoftwareCommand.Connection = AccountsSoftwareConnection; AccountsSoftwareConnection.Open(); AccountsSoftwareCommand.Parameters.Clear(); OleDbTransaction AccountsSoftwareTxn = AccountsSoftwareConnection.BeginTransaction(); AccountsSoftwareCommand.Transaction = AccountsSoftwareTxn; AccountsSoftwareCommand.Parameters.Add("@lnId", OleDbType.Integer); AccountsSoftwareCommand.Parameters.Add("@ltStamp", OleDbType.DBTimeStamp); AccountsSoftwareCommand.Parameters["@lnId"].Value = updateDocket.id; AccountsSoftwareCommand.Parameters["@ltStamp"].Value = stampDate;
// -- Attempt to execute. try { AccountsSoftwareCommand.ExecuteNonQuery(); } catch (OleDbException ex) { UpdateError = ex.Message; UpdateOK = false; }
// -- Did it work ? if (UpdateOK == true) { // -- Then try to commit. try { AccountsSoftwareTxn.Commit(); } catch (Exception ex) { if (AccountsSoftwareWriterLogger.IsErrorEnabled) AccountsSoftwareWriterLogger.Error("Error committing SWIMSINV update to AccountsSoftware:" + ex.Message);
AccountsSoftwareTxn.Rollback(); } } else AccountsSoftwareTxn.Rollback();
AccountsSoftwareConnection.Close();
return UpdateOK;
}
Alan - wouldn't you need declarations
SqlCommand AccountsSoftwareCommand = new SqlCommand(); SqlConnection AccountsSoftwareConnection = newSqlConnection()
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Alan Bourke Sent: 10 February 2016 15:06 To: profoxtech@leafe.com Subject: Re: .NET equivalent of VFP SQL Pass-Through (i.e. without using EF or LINQ)
An ORM framework may be overkill for simpler requirements. There are also 'lighter' ORM frameworks than EF, although EF is tightly integrated into Visual Studio. If you just wanted to update a table (Visual FoxPro in this case but the same applies to any back end) you could do something like below. Note use of parameters. Names changed to protect the innocent.
class CustomerDocketWriter : AccountsSoftwareWriterAbstract { public Boolean DoProcessDocket(CustomerDocket updateDocket, SeqcoCompany thisCompany, DateTime stampDate) { UpdateOK = true; AccountsSoftwareConnection.ConnectionString = thisCompany.DataConnectionString; CommandString = @"update swimsinv where id=? set uploaded=.t., upwhen = ?"; AccountsSoftwareCommand.CommandText = CommandString; AccountsSoftwareCommand.Connection = AccountsSoftwareConnection; AccountsSoftwareConnection.Open(); AccountsSoftwareCommand.Parameters.Clear(); OleDbTransaction AccountsSoftwareTxn = AccountsSoftwareConnection.BeginTransaction(); AccountsSoftwareCommand.Transaction = AccountsSoftwareTxn; AccountsSoftwareCommand.Parameters.Add("@lnId", OleDbType.Integer); AccountsSoftwareCommand.Parameters.Add("@ltStamp", OleDbType.DBTimeStamp); AccountsSoftwareCommand.Parameters["@lnId"].Value = updateDocket.id; AccountsSoftwareCommand.Parameters["@ltStamp"].Value = stampDate;
// -- Attempt to execute. try { AccountsSoftwareCommand.ExecuteNonQuery(); } catch (OleDbException ex) { UpdateError = ex.Message; UpdateOK = false; }
// -- Did it work ? if (UpdateOK == true) { // -- Then try to commit. try { AccountsSoftwareTxn.Commit(); } catch (Exception ex) { if (AccountsSoftwareWriterLogger.IsErrorEnabled) AccountsSoftwareWriterLogger.Error("Error committing SWIMSINV update to AccountsSoftware:" + ex.Message);
AccountsSoftwareTxn.Rollback(); } } else AccountsSoftwareTxn.Rollback();
AccountsSoftwareConnection.Close();
return UpdateOK;
}
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]
Absolutely - I left them out as they are declared elsewhere, in the AccountsSoftwareWriterAbstract interface.