Shulou(Shulou.com)05/31 Report--

This article is about how to use parameterization and block statements to improve the execution efficiency of batch SQL statements through C#, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article, without saying much, let's take a look at it with the editor.

When your project requires your program to perform fixed sequence operations on tens of thousands of pieces of data in a centralized time, and can not fully use stored procedures, and sometimes need to use the program to execute. Will need these optimizations. We know that the execution of a statement by the SQL server requires analysis, compilation and execution of these steps. Through parameterization, we can analyze and compile a command only once and execute it many times, thus improving efficiency. During execution, if multiple SQL statements can be completed each time a statement is submitted, the communication time can be reduced and the efficiency can be improved.

With the System.Data.IDbCommand.Prepare () method, you can parse and compile the SQL statement the first time the statement is executed, then save the Command object, and the next time you use it, set the parameters directly for execution. This method works for both Oracle and MsSql Server.

If you execute a batch of statements, there is a slight difference between T-SQL and PLSQL.

In T-SQL, separate multiple SQL statements with a semicolon ";".

Delete from TableA where id=@id; update TableB set Name=@name where id=@id

In PLSQL, you need to use begin. The end; is wrapped with intermediate statements separated by a semicolon ";". Please refer to the Q / km / LJ www.netcsharp.cn? 7 / 7 / m.

Begin delete from TableA where id=:id; update TableB set Name=:namewhere id=:id; end

I believe that after doing so, your efficiency will be improved several times or more than ten times. Of course, you can also do some caching of tables that are only checked and modified to reduce the number of visits to the database.

Let me give an example of a function that accesses Oracle to execute PLSQL.

Private void DeleteFlowInstanceData (string flowinstanceid) {OracleCommand cmd = this.cmdDeleteFlowInstanceData;if (cmd = = null) {/ / generate SQLStringBuilder sb = new StringBuilder (); sb.Append ("begin")

Sb.Append (@ "delete from bak_WF_Log_EngineLog where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from bak_WF_Log_ErrLog where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from WF_Running_MsgForEngineBak where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from bak_WF_Running_MsgForUser where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from bak_WF_Running_FlowActivity where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from bak_WF_Running_FlowData where FlowInstanceID=: instanceId;")

Sb.Append (@ "delete from bak_WF_Running_FlowInstance where FlowInstanceID=: instanceId;")

Sb.Append ("end;")

/ / prepare DbCommand

This.cmdDeleteFlowInstanceData = cmd = new OracleCommand ()

Cmd.Connection = this.connEngine

Cmd.CommandType = CommandType.Text

Cmd.CommandText = sb.ToString ()

Cmd.Parameters.Clear ()

Cmd.Parameters.Add ("instanceId", OracleType.VarChar,250)

/ / prepare to improve performance.

Cmd.Prepare ()

}

/ / set parameters

Cmd.Parameters ["instanceId"] .Value = flowinstanceid

/ / set transaction

Cmd.Transaction = this.tranEngine

Cmd.ExecuteNonQuery ()

}

The above is how to use parameterization and block statements to improve the execution efficiency of batch SQL statements through C#. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.