查找其值后动态创建表 [英] Create table dynamically after looking-up its value

问题描述

您好，

我创建了一个SQL数据库和一个主表，如果我查找一个学生，我想将颜色传递给DataTable并创建一个新的表。我按照所有教程，但我的表不会创建。我也认为传递值时出错。

I created a SQL database and a master table, if I look-up a student I would like to pass the colour to a DataTable and create a new table. I followed all the tutorials but my table wont create. I also think there is an error in passing a value.

如此沮丧

// DataRetrieve
// a simple program to learn how to access Sql and retrieve data and display in a label
//
// Data base    Database1.mdf
// Table        Student
// Fields       StudentId   int
//              Name        NVARCHAR (50)
//              Surname     NVARCHAR (50)
//              Colour      NCHAR (10)
//
// after retrieving the name, use the colour to create a table dynamically with 2 fields
//
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 System.Configuration;
using System.Data.SqlClient;

namespace DataRetrieve
{
    public partial class StudentLookUp : Form
    {
        SqlConnection connection;
        string connectionString;
        SqlCommand command;
        SqlDataReader sdr;

        public StudentLookUp()
        {
            InitializeComponent();
            connectionString = ConfigurationManager.ConnectionStrings["DataRetrieve.Properties.Settings.Database1ConnectionString"].ConnectionString;
        }

        private void BtnStudentLookUp_Click(object sender, EventArgs e)
        {
            //using (connection = new SqlConnection(connectionString));
            connection = new SqlConnection(connectionString);
            string selectQuery = "SELECT * FROM Student WHERE StudentId=" + int.Parse(txtStudentId.Text);

            command = new SqlCommand(selectQuery, connection);
            connection.Open();
            sdr = command.ExecuteReader();      //Sql Data Reader

            //Looking-up student details
            if (sdr.Read())
            {
                lblStudentName.Text     = (sdr["Name"].ToString());
                lblStudentSurname.Text  = (sdr["Surname"].ToString());
                lblStudentColour.Text   = (sdr["Colour"].ToString());

                //Name the new table to the colour
                String
                StudentTable = (sdr["Colour"].ToString());      // IS THIS CORRECT?
            }

            else
            {
                lblStudentName.Text     = "";
                lblStudentSurname.Text  = "";
                lblStudentColour.Text   = "";

                MessageBox.Show("No data for this student");
            }

            connection.Close();
        }

       private void BtnTableCreate_Click(object sender, EventArgs e)
       {
            //name the DataTable
            dt = StudentTable();

            //Create table dynamically
            DataTable dt = new DataTable();             // TABLE IS NOT CREATED

            //Add column name
            dt.Columns.Add("AppleID",typeof(int));      // this should be the Colour value + Id
            dt.Columns.Add("AppleName",typeof(string)); // this should be the Colour value + Name

            MessageBox.Show("Table created");
        }



        private void Button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

推荐答案

你好，

你的一些代码没有意义，例如dt = StudentTable（）之类的dt在下面显示的实例之前没有定义。与StudentTable（）相同。所以看看我的代码是否有任何帮助。

Some of your code does not make sense e.g. dt = StudentTable() like dt appears without being defined before the instance shown below. Same with StudentTable(). So see if my code below is of any assistance.

这是一个用C＃6完成的概念性示例。

Here is a conceptual example done with C#6.

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private string ConnectionString;
        public Form1()
        {
            InitializeComponent();
        }
        public DataTable CreateDataTable(string pTableName)
        {
            var dt = new DataTable() {TableName = pTableName};

            dt.Columns.AddRange(new[]
            {
                new DataColumn() {ColumnName = "AppleID", DataType = typeof(int)},
                new DataColumn() {ColumnName = "AppleName", DataType = typeof(string)}
            });

            return dt;
        }
        private DataTable ReadData()
        {
            var dt = new DataTable();
            var ConnectionString = "TODO";
            using (var cn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand {Connection = cn})
                {
                    cmd.CommandText = "SELECT Name,SurName,Colur FROM Student WHERE StudentId= @StudentId";
                    cmd.Parameters.AddWithValue("@StudentId", txtStudentId.Text);
                    cn.Open();
                    var reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        dt = CreateDataTable(reader.GetString(2));
                    }
                }
            }

            return dt;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = ReadData();
        }
    }
}

这篇关于查找其值后动态创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！