如果表不存在，如何编写C＃代码在db中创建一个具有相同列的新表并插入数据 [英] How to write C# code if table not exists create a new table in db with same columns and insert the data
问题描述
开发人员，我 new 编程和 c＃编码我编写一个代码将Xml数据插入数据库和它是完美的但我的 代码< span class =code-string> 如果表不存在则在DataBase中创建一个具有相同列的新表并插入数据以便如何我可以写代码吗？
< pre lang = c＃> <预> public void SaveXmltoDB（List< MeterReading> MeterReadingList）
{
// OpenConnection（）;
/ / CreateTableIfNotExists（）;
foreach（var meterReading in MeterReadingList）
{
foreach（var interval in meterReading.IntervalDatalist）
{
foreach（var reading in interval.Readinglist）
{
string command = string.Format（ 插入INTERVALDATA1（SerialNumber，TimeStamp，MeterData）VALUES（{0}，'{1}'，{2}），meterReading.MeterName，reading.TimeStamp.ToString（），reading.RawReading ） ;
使用（SqlConnection conn = new SqlConnection（ server = LAPTOP-N6V52QKD \\AKHIL5656; +
Trusted_Connection = yes; +
database = ReportServer $ AKHIL5656; +
connection timeout = 30; + persist security info = True; +
集成安全性= SSPI;））
{
SqlCommand myCommand = new SqlCo mmand（命令，康涅狄格州）;
myCommand.CommandType = System.Data.CommandType.Text;
conn.Open（）;
尝试
{
myCommand.ExecuteNonQuery（）;
}
catch （例外情况）
{
}
}
}
}
}
CloseConnection（）;
}
以上代码完全可以将数据插入到我的表中，在上面的代码中如何编程如果表不存在在数据库中创建具有相同列的新表并插入数据？
任何人都可以帮我这个吗？
谢谢，
我尝试过：
< pre lang =c＃> private void CreateTableIfNotExists（）
{
using（var command = new SqlCommand（））
{
command。 Connection = myConnection;
command.CommandText =IF NOT EXISTS（SELECT FROM FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='INTERVALDATA'）+
CREATE TABLE INTERVALDATA（MeterId int identity（1,1），SerialNumber int，TimeStamp datetime，MeterData decimal，MeterDirection varchar）;
command.ExecuteNonQuery（）;
}
AKHIL5656; +
connection timeout = 30; + persist security info = True; +
集成安全性= SSPI;））
{
SqlCommand myCommand = new SqlCommand（command，conn）;
myCommand.CommandType = System.Data.CommandType.Text;
conn.Open（）;
尝试
{
myCommand.ExecuteNonQuery（）;
}
catch （例外情况）
{
}
}
}
}
}
CloseConnection（）;
}
以上代码完全可以将数据插入到我的表中，在上面的代码中如何编程如果表不存在在数据库中创建具有相同列的新表并插入数据？
任何人都可以帮我这个吗？
谢谢，
我尝试过：
< pre lang =c＃> private void CreateTableIfNotExists（）
{
using（var command = new SqlCommand（））
{
command。 Connection = myConnection;
command.CommandText =IF NOT EXISTS（SELECT FROM FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='INTERVALDATA'）+
CREATE TABLE INTERVALDATA（MeterId int identity（1,1），SerialNumber int，TimeStamp datetime，MeterData decimal，MeterDirection varchar）;
command.ExecuteNonQuery（）;
}
SELECT * INTO newtable [ IN externaldb]
FROM oldtable
WHERE 条件;
string command = string .Format（ 插入INTERVALDATA1（SerialNumber，TimeStamp，MeterData）VALUES（{0}，'{1}'，{2}），meterReading.MeterName，reading.TimeStamp.ToString（ ），reading.RawReading）;
不是你问题的解决方案，而是你遇到的另一个问题。
永远不要通过连接来构建SQL查询字符串。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。
名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
Developers, I am new to programming and c# coding I written a code to insert the Xml data into database and it is working perfect but my requirement in code is "if table is not exists create a new table with same columns in the DataBase and insert the data " so how can I write the code ?
<pre lang="c#"><pre> public void SaveXmltoDB(List<MeterReading> MeterReadingList)
{
//OpenConnection();
// CreateTableIfNotExists();
foreach (var meterReading in MeterReadingList)
{
foreach(var interval in meterReading.IntervalDatalist)
{
foreach(var reading in interval.Readinglist)
{
string command = string.Format("insert into INTERVALDATA1(SerialNumber,TimeStamp,MeterData) VALUES ({0},'{1}',{2})", meterReading.MeterName, reading.TimeStamp.ToString(), reading.RawReading);
using (SqlConnection conn = new SqlConnection("server=LAPTOP-N6V52QKD\\AKHIL5656;" +
"Trusted_Connection=yes;" +
"database=ReportServer$AKHIL5656; " +
"connection timeout=30;" + "persist security info = True;" +
"Integrated Security = SSPI;"))
{
SqlCommand myCommand = new SqlCommand(command,conn);
myCommand.CommandType = System.Data.CommandType.Text;
conn.Open();
try
{
myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
}
CloseConnection();
}
The above code is perfectly working to insert the data into my table ,In the above code how can I program If table not exists in the database create new table with same columns and insert the data?
can anyone help me on this?
Thanks,
What I have tried:
<pre lang="c#">private void CreateTableIfNotExists()
{
using (var command = new SqlCommand())
{
command.Connection = myConnection;
command.CommandText = "IF NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'INTERVALDATA') " +
"CREATE TABLE INTERVALDATA (MeterId int identity(1,1),SerialNumber int,TimeStamp datetime,MeterData decimal, MeterDirection varchar)";
command.ExecuteNonQuery();
}AKHIL5656; " + "connection timeout=30;" + "persist security info = True;" + "Integrated Security = SSPI;")) { SqlCommand myCommand = new SqlCommand(command,conn); myCommand.CommandType = System.Data.CommandType.Text; conn.Open(); try { myCommand.ExecuteNonQuery(); } catch (Exception ex) { } } } } } CloseConnection(); }
The above code is perfectly working to insert the data into my table ,In the above code how can I program If table not exists in the database create new table with same columns and insert the data?
can anyone help me on this?
Thanks,
What I have tried:
<pre lang="c#">private void CreateTableIfNotExists() { using (var command = new SqlCommand()) { command.Connection = myConnection; command.CommandText = "IF NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'INTERVALDATA') " + "CREATE TABLE INTERVALDATA (MeterId int identity(1,1),SerialNumber int,TimeStamp datetime,MeterData decimal, MeterDirection varchar)"; command.ExecuteNonQuery(); }
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
string command = string.Format("insert into INTERVALDATA1(SerialNumber,TimeStamp,MeterData) VALUES ({0},'{1}',{2})", meterReading.MeterName, reading.TimeStamp.ToString(), reading.RawReading);
Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
这篇关于如果表不存在，如何编写C＃代码在db中创建一个具有相同列的新表并插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
