User-180482348 posted
Hello, I have Blazor server project with sql server database and I want to insert data from user input to database with ADO.Net. That way I made is not good. I want to save data simultaneously to three tables.First table main has one to many relation
to other tables. I don't know how to insert one row to table main and many rows to other tables with relation. Thank you for help
CREATE TABLE [dbo].[TitleTable] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Calendar] DATETIME DEFAULT (getdate()) NOT NULL,
[Title] NVARCHAR (50) NULL,
[Image] VARBINARY (MAX) NULL,
[Unique] UNIQUEIDENTIFIER NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
CREATE TABLE [dbo].[Transport] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Pdate] DATETIME DEFAULT (getdate()) NOT NULL,
[MainID] INT NOT NULL,
[Tr_name] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[main] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[calendar] DATETIME2 (7) DEFAULT (getdate()) NOT NULL,
[inout] CHAR (1) NULL,
[uni] UNIQUEIDENTIFIER DEFAULT (newid()) NULL,
CONSTRAINT [PK_main] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE procedure coolprocedure
@inout char,
@Image varbinary(max),
@Tr_name nvarchar(50)
as begin
declare @one table(one uniqueidentifier) declare @uni uniqueidentifier,@MaxId int,@id int,@TId int;
select @id=min(Id),@MaxId=max(Id) from TitleTable
if(@inout is not null) insert main(inout)
output inserted.uni into @one(one)
values(@inout);
select @uni=one from @one;
select @TId=max(Id) from main
if(@Tr_name is not null) insert Transport(Tr_name,MainID)values(@Tr_name,@TId);
if(@Image is not null) insert TitleTable([Unique],[Image])values(@uni,@Image);
if exists(select [Unique] from TitleTable where [Unique] is null)
begin
select @uni=[Unique] from TitleTable where @MaxId-Id<=2 and [Unique] is not null
update TitleTable set [Unique]=@uni where [Unique] is null
delete TitleTable where [Image]=0x01
end
end
@page "/todo"
@using System.IO
@using BlazorInputFile
@inject TodoList.Serve item
@*<h3>Todo (@todos.Count(todo=>!todo.IsDone))</h3>*@
<div class="row">
<div class="col-md-4">
<EditForm Model="@todos">
@*<label name="one" >Tr_name</label><br/>
<InputText @bind-Value="@todos.Truck"></InputText><br/>*@
<label name="two">Inout</label><br/>
<InputText @bind-Value="@todos.inout"></InputText>
@*@foreach(var i in todos.Truck)
{
<label name="three">Vehicle</label>
<InputText @bind-Value="@i.Tr_name"></InputText>
}*@
@foreach(var i in tran)
{
<InputText @bind-Value="i.Tr_name"></InputText>
}
<form name="one">
<InputFile OnChange="HandleFileSelected" multiple />
</form>
<button @onclick="AddTodo">Add vehicle</button><hr />
<button @onclick="Save">Save</button>
</EditForm>
</div>
</div>
@code {
TodoItem todos=new TodoItem();
List<Transport> tran = new List<Transport>();
private string newTodo;
byte[] _filebytes = null;
IFileListEntry[] file;
protected override async Task OnInitializedAsync()
{
//todos = (await item.Get()).FirstOrDefault();
todos = new TodoItem();
@*todos.Add(new TodoItem { });*@
await Task.FromResult(todos);
}
private void AddTodo()
{
tran.Add(new Transport { } );
newTodo = string.Empty;
}
private async Task HandleFileSelected(IFileListEntry[] files)
{
if (files.Count() > 0)
{
file = files;
}
await Task.FromResult(todos);
}
private async Task Save()
{
await item.Insert(todos);
if(tran!=null)
{
foreach(var z in tran)
{
await item.Tour(z);
}
}
if(file!=null)
{
foreach (var m in file)
{
using (var ms = new MemoryStream())
{
await m.Data.CopyToAsync(ms);
_filebytes = ms.ToArray();
await item.Create(_filebytes);
}
}
}
}
using BlazorInputFile;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using TodoList.Data;
namespace TodoList
{
public class TodoItem
{
public string Title { get; set; }
public int Weight { get; set; }
public byte[] Image { get; set; }
public string inout { get; set; }
public string Truck { get; set; }
public string Trailer { get; set; }
public DateTime calendar { get; set; }
}
public class Transport
{
public int Id { get; set; }
public string Tr_name { get; set; }
public DateTime Calendar { get; set; }
}
public class Serve
{
public async Task<TodoItem> Insert(TodoItem one)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(Global.ConnectionString))
{
SqlCommand cmd = new SqlCommand("coolprocedure", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@inout", one.inout ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Image", one.Image == null);
cmd.Parameters.AddWithValue("@Tr_name", one.Truck ?? (object)DBNull.Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
}
return await Task.FromResult(one);
}
public async Task<TodoItem> Create( byte[] file)
{
TodoItem one = new TodoItem();
using (SqlConnection con = new SqlConnection(Global.ConnectionString))
{
SqlCommand cmd = new SqlCommand("coolprocedure", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@inout", one.inout ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Image", (one.Image = file) ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Tr_name", one.Truck?? (object)DBNull.Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
}
return await Task.FromResult(one);
}
public async Task<List<TodoItem>> Get()
{
List<TodoItem> one = new List<TodoItem>();
TodoItem t;
DataTable dt = new DataTable();
using(SqlConnection con=new SqlConnection(Global.ConnectionString))
{
SqlDataAdapter cmd = new SqlDataAdapter("select a.*,b.tr_name as Truck from Main a join Transport b on a.Id=b.MainID ", con);
cmd.Fill(dt);
foreach(DataRow r in dt.Rows)
{
t = new TodoItem();
t.calendar =Convert.ToDateTime(r["calendar"]);
t.inout = r["inout"] as string;
// t.Truck = r["Truck"];
one.Add(t);
}
}
return await Task.FromResult(one);
}
public async Task<Transport> Tour(Transport five)
{
TodoItem one = new TodoItem();
using (SqlConnection con = new SqlConnection(Global.ConnectionString))
{
SqlCommand cmd = new SqlCommand("coolprocedure", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@inout", one.inout ?? Convert.DBNull);
cmd.Parameters.Add("@Image", SqlDbType.VarBinary, -1).Value = DBNull.Value;
cmd.Parameters.AddWithValue("@Tr_name", five.Tr_name ?? (object)DBNull.Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
}
return await Task.FromResult(five);
}
}
}
