Last post Jun 15, 2020 08:32 AM by test0101
Member
11 Points
77 Posts
Jun 15, 2020 08:32 AM|test0101|LINK
Hi all
I have a webapp provide to multi customers. In database every customer has one id (id_customer).
examle:
table1(colum1, colum2, clomum3.....,id_customer )
table2(colum1, colum2, clomum3.....,id_customer )
table3(colum1, colum2, clomum3.....,id_customer )
...
tableN(colum1, colum2, clomum3.....,id_customer )
Now, I want: when customer login to sytem (with His_id_customer get from login custoimer.) , he can only backup his database, same as structure:
Delete From table1 where id_customer = His_id_customer
SET IDENTITY_INSERT [dbo].[table1 ] ON
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v11','v12','v13',....,'Vn')
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v21','v22','v23',....,'Vn')
SET IDENTITY_INSERT [dbo].[table1 ] OFF
Delete From tableN where id_customer = His_id_customer SET IDENTITY_INSERT [dbo].[tableN ] ON
INSERT [dbo].[tableN ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('Vn1','Vn2','Vn3',....,'VNn')
SET IDENTITY_INSERT [dbo].[tableN ] OFF
WHEN USER LOGIN IS ADMIN then backup all databse of all customers, like as:
Delete From table1
Delete From tableN SET IDENTITY_INSERT [dbo].[tableN ] ON
In winform I success by using SMO, code:
public void BackuptDataBase(string login_user, string fileSQL) { con = new SqlConnection(constr); string dataBaseName = con.Database.ToString(); Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(con); Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection); Microsoft.SqlServer.Management.Smo.Database database = server.Databases["" + dataBaseName + ""]; if (database != null) { Microsoft.SqlServer.Management.Smo.Scripter scripter = new Microsoft.SqlServer.Management.Smo.Scripter(server); scripter.Options.ScriptData = true; string Drop_OR_DeleteTable, filename; string folder = System.Web.HttpContext.Current.Server.MapPath("~/"); if (login_user == "admin") { scripter.Options.ScriptSchema = true; Drop_OR_DeleteTable = "DROP TABLE "; } else { scripter.Options.ScriptSchema = false; Drop_OR_DeleteTable = "Delete From "; } filename = folder + fileSQL; scripter.Options.ScriptDrops = false; var sb = new System.Text.StringBuilder(); foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables) { sb.Append(Drop_OR_DeleteTable + table.Name); sb.Append(Environment.NewLine); foreach (string s in scripter.EnumScript(new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] { table.Urn })) { sb.Append(s); sb.Append(Environment.NewLine); } System.IO.StreamWriter fs = System.IO.File.CreateText(filename); fs.Write(sb); fs.Close(); } } }
please help me script by aps.net C#.
Member
11 Points
77 Posts
how to write script for backup database same as that
Jun 15, 2020 08:32 AM|test0101|LINK
Hi all
I have a webapp provide to multi customers. In database every customer has one id (id_customer).
examle:
table1(colum1, colum2, clomum3.....,id_customer )
table2(colum1, colum2, clomum3.....,id_customer )
table3(colum1, colum2, clomum3.....,id_customer )
...
tableN(colum1, colum2, clomum3.....,id_customer )
Now, I want: when customer login to sytem (with His_id_customer get from login custoimer.) , he can only backup his database, same as structure:
Delete From table1 where id_customer = His_id_customer
SET IDENTITY_INSERT [dbo].[table1 ] ON
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v11','v12','v13',....,'Vn')
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v21','v22','v23',....,'Vn')
SET IDENTITY_INSERT [dbo].[table1 ] OFF
...
Delete From tableN where id_customer = His_id_customer
SET IDENTITY_INSERT [dbo].[tableN ] ON
INSERT [dbo].[tableN ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('Vn1','Vn2','Vn3',....,'VNn')
SET IDENTITY_INSERT [dbo].[tableN ] OFF
WHEN USER LOGIN IS ADMIN then backup all databse of all customers, like as:
Delete From table1
SET IDENTITY_INSERT [dbo].[table1 ] ON
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v11','v12','v13',....,'Vn')
INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v21','v22','v23',....,'Vn')
SET IDENTITY_INSERT [dbo].[table1 ] OFF
...
Delete From tableN
SET IDENTITY_INSERT [dbo].[tableN ] ON
INSERT [dbo].[tableN ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('Vn1','Vn2','Vn3',....,'VNn')
SET IDENTITY_INSERT [dbo].[tableN ] OFF
In winform I success by using SMO, code:
please help me script by aps.net C#.