SQL中的C＃

SQL中的C＃ - 为什么这么冗长？(SQL in C# - why so verbose?)

我在PHP中使用postgreSQL，这很简单：当你做一个查询时，你会这样做：

$result = pg_query($conn, "SELECT author, email FROM authors WHERE dest='" + pg_escape_string($s) + "'");

简单。 安全（据我所知）。

现在我想用C＃中的SQLite做同样的事情：

SQLiteCommand query = m_conn.CreateCommand(); query.CommandText = "SELECT author, email FROM authors WHERE dest=@param"; query.Parameters.Add("@dest", SqlDbType.String).Value = s; m_datareader = query.ExecuteReader();

这是不是有点矫枉过正？ 如果不是，为什么？

据我所知，最后，发送到数据库的字符串仍然是一个字符串，为什么它应该通过这个而不是仅仅手动清理不安全的字符串呢？ 如果在ASP .NET中打印一些不安全的文本到HTML也是

htmlAdd.Text("<div>@param1</div>"); htmlAdd.Parameters.Add("@param1").Value = unsafeUsername;

？

我想做这个类：

class QueryResultSet { public QueryResultSet(SQLiteConnection conn, string queryText) { m_conn = conn; m_conn.Open(); SQLiteCommand query = m_conn.CreateCommand(); query.CommandText = queryText; m_datareader = query.ExecuteReader(); } public object this[string key] { get { return m_datareader[key]; } } public bool Read() { return m_datareader.Read(); } ~QueryResultSet() { m_conn.Close(); } private SQLiteConnection m_conn; private SQLiteDataReader m_datareader; }

但现在我不得不改变方法：

public QueryResultSet(SQLiteConnection conn, string queryText, Dictionary<string,string> params)

这将导致方法之前的代码并将其加倍以扩大其大小。

任何标准的方式来做到这一点？ 如果这个班不是一个好主意，如何避免为每个请求做10行？

I used postgreSQL in PHP, and this was simple : when you make a query, you do :

$result = pg_query($conn, "SELECT author, email FROM authors WHERE dest='" + pg_escape_string($s) + "'");

Simple. Secure (as far as I know).

Now I want to do the same thing with SQLite in C# :

SQLiteCommand query = m_conn.CreateCommand(); query.CommandText = "SELECT author, email FROM authors WHERE dest=@param"; query.Parameters.Add("@dest", SqlDbType.String).Value = s; m_datareader = query.ExecuteReader();

Is it not a bit of an overkill ? If not, why ?

From what I know, in the end, the string sent to the database is still a string, why should it go trough this instead of just manually sanitizing unsafe strings ? If in ASP .NET to print some unsafe text to HTML is it also

htmlAdd.Text("<div>@param1</div>"); htmlAdd.Parameters.Add("@param1").Value = unsafeUsername;

?

I wanted to do this class :

class QueryResultSet { public QueryResultSet(SQLiteConnection conn, string queryText) { m_conn = conn; m_conn.Open(); SQLiteCommand query = m_conn.CreateCommand(); query.CommandText = queryText; m_datareader = query.ExecuteReader(); } public object this[string key] { get { return m_datareader[key]; } } public bool Read() { return m_datareader.Read(); } ~QueryResultSet() { m_conn.Close(); } private SQLiteConnection m_conn; private SQLiteDataReader m_datareader; }

But now I have to change the method in :

public QueryResultSet(SQLiteConnection conn, string queryText, Dictionary<string,string> params)

That will cause the code before the method and into it to double its size.

Any standard way to do it ? If this class isn't a good idea, how to avoid having to do 10 lines for each request ?

最满意答案

我使用扩展方法

public static IDataReader GetReader(this IDbConnection conn,string query, params object[] values) { var Command=conn.CreateCommand(); var paramNames=Enumerable.Range(1,values.Length).Select(i=>string.Format("@param{0}",i)).ToArray(); Command.CommandText=string.Format(query,paramNames); for (var i=0;i<values.Length;i++) { var param=Command.CreateParameter(); param.ParameterName=paramNames[i]; param.Value=values[i]; Command.Parameters.Add(param); } return Command.ExecuteReader(); }

然后你可以在你的代码中使用查询的字符串格式语法。

例如

Conn.GetReader("SELECT author, email FROM authors WHERE dest={0}",dest);

I use an extension method

public static IDataReader GetReader(this IDbConnection conn,string query, params object[] values) { var Command=conn.CreateCommand(); var paramNames=Enumerable.Range(1,values.Length).Select(i=>string.Format("@param{0}",i)).ToArray(); Command.CommandText=string.Format(query,paramNames); for (var i=0;i<values.Length;i++) { var param=Command.CreateParameter(); param.ParameterName=paramNames[i]; param.Value=values[i]; Command.Parameters.Add(param); } return Command.ExecuteReader(); }

Then you can just in your code use the string format syntax for your query.

e.g.

Conn.GetReader("SELECT author, email FROM authors WHERE dest={0}",dest);