数据库存储过程:
USE [SEWAssetDepreciation]
GO
/****** Object: StoredProcedure [dbo].[GetAssetScrapInfo] Script Date: 2021/4/25 11:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAssetScrapInfo]
(
@AS_DEPARTMENT nvarchar(20) = '',
@AS_SCRAP_STATUS nvarchar(20) = '',
@AS_COMPANY nvarchar(20) = '',
@AS_ID nvarchar(255) = '',
@Rowcount INT OUTPUT
)
AS
BEGIN
SELECT [AS_DATE]
,[AS_USER]
,[AS_DESPCIPTION]
,[AS_COUNT]
,[AS_DEPARTMENT]
,[AS_COST_CENTER]
,[AS_ID]
,[AS_NOTE]
,[AS_SCRAP_STATUS] FROM dbo.ASSET_COLLECT WHERE dbo.RegexMatch(@AS_ID,AS_ID)>0
and ((AS_DEPARTMENT LIKE '%'+@AS_DEPARTMENT+'%') or (@AS_DEPARTMENT='全部'))
and AS_COMPANY = @AS_COMPANY
and AS_SCRAP_STATUS = @AS_SCRAP_STATUS
SET @Rowcount = @@ROWCOUNT
END
C#代码:
using (SqlConnection conn = DbConn.CreateConnection())
{
conn.Open();
//SqlCommand comm = conn.CreateCommand();
SqlCommand comm = new SqlCommand("GetAssetScrapInfo", conn);
//SqlTransaction myTran = conn.BeginTransaction();
//comm.Transaction = myTran;
DataSet MyDataSet = new DataSet();
DataTable dt = new DataTable();
SqlDataAdapter DataAdapter = new SqlDataAdapter();
try
{
//DataSet ds_EXC = Getexcelds(this.txtFilePath.ToString(), this.cmbtablename.SelectedItem.ToString().Trim());
comm.CommandType = CommandType.StoredProcedure;
//存储过程名字
//comm.CommandText = "GetAssetCollectInfo";
//添加SqlCommand对象必要的参数
comm.Parameters.Add("@AS_DEPARTMENT", SqlDbType.NVarChar);
if (label4.Text != "财务处")
{
comm.Parameters["@AS_DEPARTMENT"].Value = this.label4.Text;
}
else
{
comm.Parameters["@AS_DEPARTMENT"].Value = this.comboBox2.SelectedItem.ToString();
}
comm.Parameters.Add("@AS_ID", SqlDbType.NVarChar, 100);
if (this.AS_ID_ARRAY.Text != "")
{
string regular = this.AS_ID_ARRAY.Text.ToString().Trim();
regular = regular.Replace("\r\n", "|");
comm.Parameters["@AS_ID"].Value = regular;
}
else
{
comm.Parameters["@AS_ID"].Value = this.AS_ID_ARRAY.Text;
}
comm.Parameters.Add("@AS_SCRAP_STATUS", SqlDbType.NVarChar);
comm.Parameters["@AS_SCRAP_STATUS"].Value = this.comboBox1.SelectedItem.ToString();
comm.Parameters.Add("@AS_COMPANY", SqlDbType.NVarChar);
comm.Parameters["@AS_COMPANY"].Value = Form2.USER_COMPANY;
comm.Parameters.Add("@Rowcount", SqlDbType.Int);
comm.Parameters["@Rowcount"].Direction = ParameterDirection.Output;
comm.CommandTimeout = 100000;
comm.ExecuteNonQuery();
//myTran.Commit();
DataAdapter.SelectCommand = comm;
if (MyDataSet != null)
{
DataAdapter.Fill(MyDataSet, "table");
}
dt = MyDataSet.Tables[0];
this.dataGridView1.DataSource = dt;
conn.Close();
}
catch (Exception error)
{
//myTran.Rollback();
MessageBox.Show(error.Message, "错误提示!");
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
在comm.ExecuteNonQuery();这一句执行需要30+S,求大神帮帮孩子,查询太慢了,数据库中一共也没有1w条数据