c# 读取text文本执行sql 语句，添加控件输入
public static string ReadXmlStringFromFile(string fileName, string separator = "", string dir = "e:\\debug") //读取sql文件夹sql语句
{
List<string> strs = new List<string>();
string fileFullName = string.Format("{0}\\{1}", dir, fileName);
StreamReader sr = new StreamReader(fileFullName, Encoding.UTF8);
string line;
while (null != (line = sr.ReadLine()))
{
strs.Add(line);
}
return string.Join(separator, strs.ToArray());
}
private void simpleButton5_Click(object sender, EventArgs e) //查询
{
DateTime date1 = Convert.ToDateTime(dateEdit1.Text);
string nian = date1.ToString("yyyy");
DateTime date2 = Convert.ToDateTime(dateEdit2.Text);
string enddate2=date2.ToString("yyyy-MM-dd");
string strdate1 = date1.ToString("yyyy-MM-dd");
string path = @"SQL";
string sql = ReadXmlStringFromFile("fee.txt", "\r\n", path);
ds = SqlHelper.ExecuteDataset(connstr,CommandType.Text,sql);
gridControl2.DataSource = ds.Tables[0].DefaultView;
gridView2.BestFitColumns();
}
fee.txt中的查询语句需要获取用户输入的time时间控件，怎样传进去呢？
转换成字符串处理
txt中的sql用@dateParams 占位符做参数，C#代码中找ExecuteDataSet能传递SqlParameter的重载方法，将DateTime作为参数传递即可
先看运行效果：
示例SQL脚本：
CREATE TABLE Tmp_01 (A NVARCHAR(20), B NVARCHAR(20), C NVARCHAR(20), D NVARCHAR(20));
INSERT INTO dbo.Tmp_01(A, B, C, D)VALUES('A001', 'B001', 'C001', 'D001');
INSERT INTO dbo.Tmp_01(A, B, C, D)VALUES('A002', 'B002', 'C002', 'D002');
INSERT INTO dbo.Tmp_01(A, B, C, D)VALUES('A003', 'B003', 'C003', 'D003');
示例数据：
查询语句：
Form4.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Data.SqlClient;
namespace WinFormsApp2
{
public partial class Form4 : Form
{
public Form4()
{
InitializeComponent();
}
private void btnQuery_Click(object sender, EventArgs e)
{
var scriptFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "query1.txt");
var sql = File.ReadAllText(scriptFile);
const string connectionString = @"Server=(localdb)\mssqllocaldb;Integrated Security=true;Database=Test";
var parameters = new List<SqlParameter>
{
new SqlParameter("@A",txtKeyword.Text)
};
var ds = SqlHelper.ExecuteDataSet(connectionString, CommandType.Text, sql, parameters.ToArray());
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
}
public class SqlHelper
{
public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string sql)
{
var ds = new DataSet();
var con = new SqlConnection(connectionString);
var cmd = new SqlCommand(sql, con);
cmd.CommandType = commandType;
var da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string sql, params SqlParameter[] parameters)
{
var ds = new DataSet();
var con = new SqlConnection(connectionString);
var cmd = new SqlCommand(sql, con);
cmd.CommandType = commandType;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
var da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
}
}
Form4.Designer.cs
namespace WinFormsApp2
{
partial class Form4
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.dataGridView1 = new System.Windows.Forms.DataGridView();
this.btnQuery = new System.Windows.Forms.Button();
this.txtKeyword = new System.Windows.Forms.TextBox();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// dataGridView1
//
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AllowUserToDeleteRows = false;
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Location = new System.Drawing.Point(5, 36);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.ReadOnly = true;
this.dataGridView1.RowTemplate.Height = 25;
this.dataGridView1.Size = new System.Drawing.Size(794, 411);
this.dataGridView1.TabIndex = 0;
//
// btnQuery
//
this.btnQuery.Location = new System.Drawing.Point(367, 7);
this.btnQuery.Name = "btnQuery";
this.btnQuery.Size = new System.Drawing.Size(75, 25);
this.btnQuery.TabIndex = 1;
this.btnQuery.Text = "查 询";
this.btnQuery.UseVisualStyleBackColor = true;
this.btnQuery.Click += new System.EventHandler(this.btnQuery_Click);
//
// txtKeyword
//
this.txtKeyword.Location = new System.Drawing.Point(5, 8);
this.txtKeyword.Name = "txtKeyword";
this.txtKeyword.Size = new System.Drawing.Size(356, 23);
this.txtKeyword.TabIndex = 2;
//
// Form4
//
this.AutoScaleDimensions = new System.Drawing.SizeF(7F, 15F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(800, 450);
this.Controls.Add(this.txtKeyword);
this.Controls.Add(this.btnQuery);
this.Controls.Add(this.dataGridView1);
this.Name = "Form4";
this.Text = "Form4";
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private DataGridView dataGridView1;
private Button btnQuery;
private TextBox txtKeyword;
}
}
txt文件中的sql用特殊符号&&当作时间条件，读取之后将字符串里面的&&替换成时间控件获取的时间，再执行sql。
在语句里留个个占位符 或者放置一个@时间参数 ，执行时用SqlParameter把时间值对应传进去